SQL优化前置知识之索引的正确使用姿势

该文的前置知识:SQL优化前置知识之优化前分析

索引失效

有的sql语句会不使用索引?在编写sql语句的时候应避免这些情况,一起来看看这些情况吧:

不遵从最左前缀规则

如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始,并且不跳过索引中的列。

最左前缀的原理来自于B+树对节点的排序规则:

摘自MySQL索引概述及索引的分类

  • 如果不是按照索引的最左列开始查找,则无法使用索引
  • 不能跳过索引中的列
  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找

验证最左前缀规则:

  1. 建立索引
create index idx_uname_pw_name on t_user(username,password,name);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
  1. 以最左前缀规则查询
mysql> explain select * from t_user where username = 'super';
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t_user | NULL       | ref  | idx_uname_pw_name | idx_uname_pw_name | 137     | const |    1 |   100.00 | Using index |
+----+-------------+--------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可见使用了索引

  1. 不以最左前缀规则查询
mysql> explain select * from t_user where name = '学生1';
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | idx_uname_pw_name | 564     | NULL |    6 |    16.67 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

可见此时type变为了"index",扫描索引树。

在索引上使用表达式

索引列上使用了表达式,如 where substr(a, 1, 3) = 'hhh',where a = a + 1,表达式是一大忌讳,再简单 MySQL 也不认。

有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查所有有订单记录的数据,再在程序中去筛选

哪怕是该字段没有建立索引,但不能保证以后不在这个字段上建立索引,所以可以这么说:不要在任何字段上进行操作。

mysql> explain select * from t_user where left(username,2) = 'ad';
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key               | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t_user | NULL       | index | NULL          | idx_uname_pw_name | 564     | NULL |    6 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+-------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

Note (Code 1003): /* select#1 */ select `test`.`t_user`.`id` AS `id`,`test`.`t_user`.`username` AS `username`,`test`.`t_user`.`password` AS `password`,`test`.`t_user`.`name` AS `name` from `test`.`t_user` where (left(`test`.`t_user`.`username`,2) = 'ad')

range 类型查询字段后面的索引无效

SQL优化前置知识之索引的正确使用姿势

最后一次只用到了两个索引
此时可以建一个只含前两个字段的索引
in()无效,in仍可以使用索引。

varchar类型不加单引号,造成索引失效

SQL优化前置知识之索引的正确使用姿势

由于在查询时,没有对字符串加单引号,MySQL的查询优化器,会自动的进行类型转换,造成索引失效。

尽量使用覆盖索引,避免select *

覆盖索引的概念,往下翻,索引的特殊应用->覆盖索引

SQL优化前置知识之索引的正确使用姿势

如果查询列,超出索引列,也会降低性能。

TIP:
using index :使用覆盖索引的时候就会出现
using where:在查找使用索引的情况下,需要回表去查询所需的数据
using index condition:查找使用了索引,但是需要回表查询数据​
using index ; using where:查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据

使用不等于时索引失效

SQL优化前置知识之索引的正确使用姿势

is (not) null 时有时索引失效

SQL优化前置知识之索引的正确使用姿势

失效的情况其实是MySQL评估使用索引比全表更慢,见最后一点。

like 以通配符开头会导致全表扫描

SQL优化前置知识之索引的正确使用姿势

使用 or 时索引失效

SQL优化前置知识之索引的正确使用姿势

如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。

其实还蛮好理解的,因为没有索引的列符合条件也满足or,自然要全表扫描。

in 走索引, not in 索引失效

SQL优化前置知识之索引的正确使用姿势

如果MySQL评估使用索引比全表更慢,则不使用索引。

这个东西就很玄学了,典型情况是查询的情况占了整表大多数。

索引的特殊应用

InnoDB AUTO_INCREMENT

概述

如果正在使用 InnoDB 表并且没有什么数据需要聚集,那么可以定义一个代理键作为主键,这种主键的数据应该与应用无关,最简单的方法是使用 AUTO_INCREMENT 自增列。这样可以保证数据行是按顺序写入的,对于根据主键做关联操作的性能也会更好。

最好避免随机的聚簇索引,特别是对于 IO 密集型应用,比如 UUID,它使得聚簇索引的插入变得完全随机,这是最坏的情况,使得数据没有任何聚集特性。

顺序主键的优点:

如果主键的值是顺序的,那么 InnoDB 会把每一条记录都存储在上一条记录的后面。当达到页的最大填充因子时,下一条记录就会写入新的页中。一旦数据按照这种顺序的方式加载,主键页就会近似于被顺序的记录填满,这也正是所期望的结果。

顺序主键的缺点:

对于高并发工作负载,在 InnoDB 中按主键顺序插入可能会造成明显的争用。主键的上界会成为热点。因为所有的插入都在这里,所以并发插入可能导致锁竞争。另一个热点可能是AUTO_INCREMENT 锁机制,可能需要重新设计表或应用。

AUTO-INC锁机制

AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁。

在最简单的情况下,如果一个事务正在向表中插入值,则任何其他事务必须等待对该表执行自己的插入操作,以便第一个事务插入的行的值是连续的。

InnoDB 会在内存里保存一个计数器用来记录 AUTO_INCREMENT 的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果一行一行的插入数据则没有什么问题,但是如果大量的并发插入就废了,表锁会引起 SQL 堵塞,不但影响效率,而且可能会瞬间达到 MAX_CONNECTION 而崩溃。

插入类型:

  1. simple inserts
    simple inserts 指的是那种能够事先确定插入行数的语句,比如 INSERT/REPLACE INTO 等插入单行或者多行的语句,语句中不包括嵌套子查询。此外,INSERT INTO … ON DUPLICATE KEY UPDATE 这类语句也要除外。
  2. bulk inserts
    bulk inserts 指的是事先无法确定插入行数的语句,比如 INSERT/REPLACE INTO … SELECT,
    LOAD DATA 等。
  3. mixed-mode inserts
    指的是 simple inserts 类型中有些行指定了 auto_increment 列的值,有些行没有指定,比如:INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);另外一种 mixed-mode inserts 是 INSERT … ON DUPLICATE KEY UPDATE 这种语句,可能导致分配的 auto_increment 值没有被使用。

innodb_autoinc_lock_mode 配置选项控制用于自动增量锁定的算法。 它允许您选择如何在
可预测的自动递增值序列和插入操作的最大并发性之间进行权衡,配置语法:

innodb_autoinc_lock_mode=x,x可取值:

  • 0,传统的 auto_increment 机制。这种模式下所有针对 auto_increment 列的插入操作都会加AUTO-INC 锁,分配的值也是一个个分配,是连续的,正常情况下也不会有间隙(当然如果事务 rollback 了这个 auto_increment 值就会浪费掉,从而造成间隙)。
  • 1,这种情况下
    • 针对 bulk inserts 才会采用 AUTO-INC 锁这种方式
    • 针对 simple inserts,则直接通过分析语句,获得要插入的数量,然后一次性分配足够的 auto_increment id,只会将整个分配的过程锁住。。当然,如果其他事务已经持有了AUTO-INC锁,则simple inserts需要等待.
    • 针对 Mixed-mode inserts:直接分析语句,获得最坏情况下需要插入的数量,然后一次性分配足够的 auto_increment id,只会将整个分配的过程锁住。保证同一条 insert 语句中新插入的 auto_increment id 都是连续的,语句之间是可能出现auto_increment 值的空隙的。比如 mixed-mode inserts 以及 bulk inserts 中都有可能导致一些分配的 auto_increment 值被浪费掉从而导致间隙。
  • 2,这种模式下任何类型的 inserts 都不会采用 AUTO-INC 锁,性能最好。这种模式是来一个分配一个,而不会锁表,只会锁住分配 id 的过程,和 innodb_autoinc_lock_mode = 1 的区别在于,不会预分配多个。但是在 replication 中当 binlog_format 为 statement-based 时(简称 SBR statement-based replication)存在问题,因为是来一个分配一个,这样当并发执行时,“Bulk inserts”在分配时会同时向其他的 INSERT 分配,会出现主从不一致(从库执行结果和主库执行结果不一样),因为 binlog 只会记录开始的 insert id。可能会在同一条语句内部产生 auto_increment 值间隙。

覆盖索引

如果一个索引包含了所有需要查询字段的值,就称为覆盖索引。

覆盖索引的优点:
1. 索引条目远少于数据行大小,如果只需要读取索引,则 MySQL 就会极大地减少数据访问了,这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对 IO 密集型应用也有帮助,因为索引比数据更小,更容易全部放入内存中。
2. 因为索引是按照列值顺序存储的,对于 IO 密集型的范围查询会比随机从磁盘读取每一行数据的 IO 次数会少得多
3. InnoDB 的二级索引在叶节点中保存了行的主键值,如果二级索引是覆盖索引,则可以避免对主键聚簇索引的二次查询

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引都不存储索引列的值,所以 MySQL 只能使用 B 树索引做覆盖索引。当发起一个索引覆盖查询时,在 EXPLAIN 的 Extra 列可以看到 Using index 的信息。InnoDB 的二级索引的叶子节点都包含了主键的值,这意味着 InnoDB 的二级索引可以有效利用这些额外的主键列来覆盖查询。

使用索引进行排序

MySQL 有两种可以生成有序的结果:通过排序操作;按索引顺序扫描。如果 EXPLAIN 出来的 type 列的值为 index,则说明 MySQL 使用了索引顺序扫描来做排序。

扫描索引本身是很快的,但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机 IO,因此按索引顺序读取数据的速度通常要比顺序地全表扫描要慢,尤其是在 IO 密集型的工作负载时。只有当索引的列顺序和 ORDER BY 子句的顺序完全一致,并且所有列的排序方向(降序或升序,索引默认是升序)都一样时,MySQL 才可以使用索引来对结果做排序。如果查询需要关联多张表,则只有当 ORDER BY 子句引用的字段全部为第一张表时,才能使用索引做排序。ORDER BY 子句和查找型索引的限制是一样的,都需要满足索引的最左前缀的要求。有一种情况下 ORDER BY 子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。

前缀压缩索引

MyISAM 通过前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中。默认只压缩字符串,但通过参数调整也能对整数进行压缩。

MyISAM 压缩每个索引块的方法时,先完全保存索引块的第一个值,然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分,把这部分存储起来即可。压缩块使用更少的情况,代价是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值,所以 MyISAM 查找时无法在索引块使用二分查找而只能从头开始扫描。

冗余和重复索引

冗余索引:MySQL 允许在相同列上创建多个索引。MySQL 需要单独维护重复的索引,并且优化器在优化查询时也需要逐个地进行考虑,这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引(完全相同),应该避免这样创建重复索引,发现以后也应该立即移除。

冗余索引和重复索引有一些不同。如果创建了索引(A,B),又创建了索引(A)就是冗余索引,索引(A,B)也可以当做索引(A)来使用。但是如果再创建索引(B,A),就不是冗余索引。另外,其他不同类型的索引也不会是 B 树索引的冗余索引。

冗余索引通常发生在为表添加新索引的时候。例如,有人可能会增加一个新的索引(A,B)而不是扩展已有的索引(A),还有一种情况是将一个索引扩展为(A,PK),对于 InnoDB 而言 PK 已经包含在二级索引中了,所以这也是冗余的。

大多数情况下都不需要冗余索引,应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引,因为扩展已有的索引会导致其变得太大,从而影响其他使用该索引的查询的性能。

例如,现在在整数列上有一个索引,需要额外增加一个很长的 VARCHAR 列来扩展该索引,那性能可能会急剧下降。可以使用一些工具来找出冗余和重复的索引。

索引重用

现有索引(A,B,C),如果要使用索引,那么 where 中必须写为 A=a and B = b and C = c。如果没有对 B 的筛选,还想使用索引,怎么绕过最左前缀匹配呢?

假设 B 是一个选择性很低的列,只有 b1 和 b2 两种取值,那么查询可以写为 A = a and B in(b1,b2) and C = c

避免多个范围条件

对于范围条件查询,MySQL 无法再使用范围列后面的其他索引列了,但是对于多个等值条件查询(in ...)则没有这个限制。

假设有索引(A,B),查询条件为 A > a and B < b,那么此时无法同时使用 A 和 B 的复合索引,只能用到 A 的索引。一定要用的话可以考虑将 A 转为 in(a1,a2...)。

查看索引使用情况

show status like 'Handler_read%';   

show global status like 'Handler_read%';    

这两条语句可以查看mysql索引的使用情况:

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 40    |
| Handler_read_key      | 43    |
| Handler_read_last     | 0     |
| Handler_read_next     | 306   |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 18832 |
+-----------------------+-------+
7 rows in set (0.00 sec)

Handler_read_first:索引中第一条被读的次数。如果较高,表示服务器正执行大量全索引扫描(这个值越低越好)。

Handler_read_key:如果索引正在工作,这个值代表一个行被索引值读的次数,如果值越低,表示索引得到的性能改善不高,因为索引不经常使用(这个值越高越好)。

Handler_read_next :按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列,该值增加。

Handler_read_prev:按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY ... DESC。

Handler_read_rnd :根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高,意味着运行效率低,应该建立索引来补救。

Handler_read_rnd_next:在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/sql%e4%bc%98%e5%8c%96%e5%89%8d%e7%bd%ae%e7%9f%a5%e8%af%86%e4%b9%8b%e7%b4%a2%e5%bc%95%e7%9a%84%e6%ad%a3%e7%a1%ae%e4%bd%bf%e7%94%a8%e5%a7%bf%e5%8a%bf/