索引优化
索引优化
单表优化案例
创建表
- 建表 SQL
|
|
- 表中的测试数据
|
|
查询案例
- 查询category_id为1且comments 大于1的情况下,views最多的article_id。
|
|
- 此时 article 表中只有一个主键索引
|
|
- 使用 explain 分析 SQL 语句的执行效率:
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
|
|
- 结论:
- 很显然,type是ALL,即最坏的情况。
- Extra 里还出现了Using filesort,也是最坏的情况。
- 优化是必须的。
开始优化:新建索引
- 创建索引的 SQL 命令
|
|
- 在 category_id 列、comments 列和 views 列上建立联合索引
|
|
- 再次执行查询:type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
|
|
- 分析:
- 但是我们已经建立了索引,为啥没用呢?
- 这是因为按照B+Tree索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序comments,如果遇到相同的 comments 则再排序 views。
- 当comments字段在联合索引里处于中间位置时,因为
comments>1
条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的views部分进行检索,即 range 类型查询字段后面的索引无效。
- 将查询条件中的
comments > 1
改为comments = 1
,发现 Use filesort 神奇地消失了,从这点可以验证:范围后的索引会导致索引失效
|
|
删除索引
- 删除索引的 SQL 指令
|
|
- 删除刚才创建的 idx_article_ccv 索引
|
|
再次创建索引
- 创建索引的 SQL 指令
|
|
- 由于 range 后(
comments > 1
)的索引会失效,这次我们建立索引时,直接抛弃 comments 列,先利用 category_id 和 views 的联合索引查询所需要的数据,再从其中取出comments > 1
的数据(我觉着应该是这样的)
|
|
- 再次执行查询:可以看到,type变为了ref,Extra中的Using filesort也消失了,结果非常理想
|
|
- 为了不影响之后的测试,删除该表的 idx_article_ccv 索引
|
|
两表索引优化
创建表
- 建表 SQL
|
|
- class 表中的测试数据
|
|
- book 表中的测试数据
|
|
查询案例
- 实现两表的连接,连接条件是 class.card = book.card
|
|
- 使用 explain 分析 SQL 语句的性能,可以看到:驱动表是左表 class 表
|
|
- 结论:
- type 有 All ,rows 为表中数据总行数,说明 class 和 book 进行了全表检索
- 即每次 class 表对 book 表进行左外连接时,都需要在 book 表中进行一次全表检索
添加索引:在右表添加索引
- 添加索引的 SQL 指令
|
|
- 在 book 的 card 字段上添加索引
|
|
- 测试结果:可以看到第二行的type变为了ref,rows也变成了优化比较明显。
|
|
- 分析:
- 这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有(左表示全量数据,圆规固定一脚是左表,右表转动需要建立索引),所以右边是我们的关键点,一定需要建立索引。
- 左表连接右表,则需要拿着左表的数据去右表里面查,索引需要在右表中建立索引
添加索引:在右表添加索引
- 删除之前 book 表中的索引
|
|
- 在 class 表的 card 字段上建立索引
|
|
- 再次执行左连接,凉凉~~~
|
|
- 别怕,我们来执行右连接:可以看到第二行的type变为了ref,rows也变成了优化比较明显。
|
|
- 分析:
- 这是因为RIGHT JOIN条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。
- class RIGHT JOIN book :book 里面的数据一定存在于结果集中,我们需要拿着 book 表中的数据,去 class 表中搜索,所以索引需要建立在 class 表中
- 为了不影响之后的测试,删除该表的 idx_article_ccv 索引
|
|
三表索引优化
创建表
- 建表 SQL
|
|
- phone 表中的测试数据
|
|
查询案例
- 实现三表的连接查询:
|
|
- 使用 explain 分析 SQL 指令:
|
|
- 结论:
- type 有All ,rows 为表数据总行数,说明 class、 book 和 phone 表都进行了全表检索
- Extra 中 Using join buffer ,表明连接过程中使用了 join 缓冲区
创建索引
- 创建索引的 SQL 语句
|
|
- 进行 LEFT JOIN ,永远都在右表的字段上建立索引
|
|
- 执行查询:后2行的type都是ref,且总rows优化很好,效果不错。因此索引最好设置在需要经常查询的字段中。
|
|
Join 语句优化的结论
将 left join 看作是两层嵌套 for 循环
-
尽可能减少Join语句中的NestedLoop(嵌套勋魂)的循环总次数;
-
永远用小结果集驱动大的结果集(在大结果集中建立索引,在小结果集中遍历全表);
使用小表(商品类别)驱动大表(商品),因为小表是全量,大表是部分,所以需要在大表上建立索引。
-
优先优化NestedLoop的内层循环;
-
保证Join语句中被驱动表上Join条件字段已经被索引;
-
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer的设置;
索引失效
数据准备
|
|
索引失效的情况
- 全值匹配我最爱。
- 最佳左前缀法则。
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
- 索引中范围条件右边的字段会全部失效。
- 尽量使用覆盖索引(只访问索引的查询,索引列和查询列一致),减少
SELECT *
。 - MySQL在使用
!=
或者<>
的时候无法使用索引会导致全表扫描。 is null
、is not null
也无法使用索引。like
以通配符开头%abc
索引失效会变成全表扫描。- 字符串不加单引号索引失效。
- 少用
or
,用它来连接时会索引失效。
最佳左前缀法则
案例
|
|
概念
最佳左前缀法则:如果索引是多字段的复合索引,要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的字段。
口诀:带头大哥不能死,中间兄弟不能断。
索引列上不计算
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
案例
|
|
我们发现以上两条SQL的执行结果都是一样的,但是执行效率有没有差距呢???
通过分析两条SQL的执行计划来分析性能。
由此可见,在索引列上进行计算,会使索引失效。
口诀:索引列上不计算。
范围之后全失效
案例
|
|
查看上述SQL的执行计划
由此可知,查询范围的字段使用到了索引,但是范围之后的索引字段会失效。
口诀:范围之后全失效。
覆盖索引尽量用
在写SQL的不要使用SELECT *
,用什么字段就查询什么字段。
|
|
口诀:查询一定不用*。
不等会失效
|
|
is null,is not null 也无法使用索引
- is null,is not null 会导致索引失效:key = null 表示索引失效
|
|
like
百分加右边
|
|
口诀:like
百分加右边。
百分加左边
如果一定要使用%like
,而且还要保证索引不失效,那么使用覆盖索引来编写SQL。
|
|
口诀:覆盖索引保两边。
如果还需要其他列的内容,可以先把id查找出来,再用id去查找某一条具体的数据,这样就可以使用索引覆盖不回表了。
字符串不加单引号索引失效
- 正常操作,索引没有失效
|
|
- 如果字符串忘记写
''
,那么 mysql 会为我们进行隐式的类型转换,但凡进行了类型转换,索引都会失效
|
|
使用 or 连接,会导致索引失效
5.5以上版本,使用or 是range查询
|
|
小结
假设index(a,b,c)
Where语句 | 索引是否被使用 |
---|---|
where a = 3 | Y,使用到a |
where a = 3 and b = 5 | Y,使用到a,b |
where a = 3 and b = 5 | Y,使用到a,b,c |
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 | N,没有用到a字段 |
where a = 3 and c = 5 | 使用到a,但是没有用到c,因为b断了 |
where a = 3 and b > 4 and c = 5 | 使用到a,b,但是没有用到c,因为c在范围之后 |
where a = 3 and b like ‘kk%’ and c = 4 | Y,a,b,c都用到 |
where a = 3 and b like ‘%kk’ and c = 4 | 只用到a |
where a = 3 and b like ‘%kk%’ and c = 4 | 只用到a |
where a = 3 and b like ‘k%kk%’ and c = 4 | Y,a,b,c都用到 |
like 后面以常量开头,比如 like ‘kk%’ 和 like ‘k%kk%’ ,可以理解为就是常量,注意区分范围查询,like ‘kk%‘的type虽然也是range,但是后面的索引列照样可以命中。
%kk
,前面有%
等于查所有,因为%表示任意。
kk%
, 用到了索引
索引优化面试题
创建表
- 建表 SQL
|
|
- test03 表中的测试数据
|
|
- test03 表中的索引
|
|
问题:我们创建了复合索引idx_test03_c1234,根据以下SQL分析下索引使用情况?
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
-
即全值匹配
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4'; +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition | +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
-
mysql 优化器进行了优化,所以我们的索引都生效了
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1'; +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition | +----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
-
c3 列使用了索引进行排序,并没有进行查找,导致 c4 无法用索引进行查找
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'; +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition | +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
-
mysql 优化器进行了优化,所以我们的索引都生效了,在 c4 时进行了范围搜索
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3'; +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition | +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
-
c3 列将索引用于排序,而不是查找,c4 列没有用到索引
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
- 和上面一样
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
-
因为索引建立的顺序和使用的顺序不一致,导致 mysql 动用了文件排序
-
看到 Using filesort 就要知道:此句 SQL 必须优化
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
-
只用 c1 一个字段索引,但是c2、c3用于排序,无filesort
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
-
出现了filesort,我们建的索引是1234,它没有按照顺序来,32颠倒了
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2; +----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort | +----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;
-
用c1、c2两个字段索引,但是c2、c3用于排序,无filesort
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
-
c5不是组合索引列,和 c5 没啥关系
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
-
注意查询条件 c2=‘a2’ ,我都把 c2 查出来了(c2 为常量),我还给它排序作甚,所以没有产生 filesort
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
-
顺序为 1 2 3 ,没有产生文件排序
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where | +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+ 1 row in set (0.00 sec)
-
-
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;
-
group by 表面上叫分组,分组之前必排序,group by 和 order by 在索引上的问题基本是一样的
-
Using temporary; Using filesort 两个都有
-
1 2 3 4 5 6 7
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2; +----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+ | 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using temporary; Using filesort | +----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+ 1 row in set (0.01 sec)
-
group的本质是排序,分组之前必排序,group by和order by排序的算法和优化的原则是一样的,区别是group by有having
-
group by报错是sql_mod配置需要改一下,full group 要求select只能有group by的字段,不能包含别的。
-
group by 基本上都需要进行排序,但凡使用不当,会有临时表产生
-
建议
- 对于单键索引,尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠左越好。
- 在选择组合索引的时候,尽量选择可以能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
总结
索引优化
建与不建,如何建(组合>单值)