索引

1.什么是索引

数据库除了数据外,还维护者满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,也就是一种快速查找数据库中内容的工具,目的是为了提高查找的效率

2.索引分类

单值索引、唯一索引、复合索引

3.索引失效

1)like以通配符开头(‘%abc’)会导致索引失效,违反最左前缀法则

2)在索引列上做任何操作(计算、函数、类型转换),会导致索引失效而转向全表扫描

3)存储引擎不能使用索引中范围条件右边的列,举例:select id,name from student where id > 50 and name = ‘张三’,会导致name索引失效

4)尽量使用覆盖索引,不要select *

5)MySQL在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描,理由也很简单,B+Tree叶子节点用指针相连且是排好序的,这种数据结构只能解决有序的定值查询,像不等于这种无法利用索引查询。

6)IS NULL、IS NOT NULL无法使用索引,理由同上

7)字符串不加单引号索引失效

隐式转换–>函数操作

8)用or连接时会导致索引失效

最左前缀法则:

在创建了多列索引的情况下,查询从索引的最左前列开始且不能跳过索引中的列。最佳左前缀法则就是说如果创建了多个索引,在使用索引时要按照创建索引的顺序来使用,不能缺少或跳过,当然如果只使用最左边的索引列,也就是第一个索引是可以的。

为什么要遵循最左前缀法则:

联合索引一个节点上有两个键值对

doris 前缀索引截断 什么是前缀索引_数据结构

a是有序排序的,b是在a字段有序的前提下在a的后边有序排列的(相对有序),加入不遵循最佳做前缀法则,就是没有a直接去查找b,而没有a的b是没有顺序的

为什么在使用不等式的时候会失效,是因为当你确定的是a的范围的时候,a的范围虽然确定了但b的顺序是无序的,所以你无法在B+树上用二分查找查找到该精确值

like后边的百分号如果放到两边或者左边都是不走索引的 放到右边的话某些情况下会走索引 因为字母在排序的时候是先按照第一个字母去作比较,然后按照第二个去比 字符串的尾部是没有顺序的like失效的原理就是和最佳做前缀类似

对于优化器来说,如果等号两边的数据类型不一致,则会发生隐式转换。

例如,explain select * from evt_sms where phone = 13020733815;这条SQL语句就会变为explain select * from evt_sms where cast(phone as signed int) = 13020733815;

由于对索引列进行了函数操作,从而导致索引失效。

SQL优化的三个点:

  • 最大化利用索引;
  • 尽可能避免全表扫描;
  • 减少无效数据的查询;

sql的语句执行顺序

FROM
<表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN<join, left join, right join…>
<join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

SQL优化策略

1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描

2.尽量避免使用in 和not in,会导致引擎走全表扫描。

3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描

4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描

5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。

6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。

7. 查询条件不能用 <> 或者 !=

8. where条件仅包含复合索引非前置列

9. 隐式类型转换造成不使用索引

10. order by 条件要与where中条件一致,否则order by不会利用索引进行排序

11. 正确使用hint优化语句

11. 正确使用hint优化语句

SELECT语句其他优化

1. 避免出现select *

首先,select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数,将指定列名以取代select *。2. 避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3.多表关联查询时,小表在前,大表在后。

在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。

例如:表1有50条数据,表2有30亿条数据;如果全表扫描表2,你品,那就先去吃个饭再说吧是吧。

4. 使用表的别名

当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。

5. 用where字句替换HAVING字句

避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。

where和having的区别:where后面不能使用组函数

6.调整Where字句中的连接顺序

前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。

where和having的区别:where后面不能使用组函数

6.调整Where字句中的连接顺序

MySQL采用从左往右,自上而下的顺序解析where子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。