听说你不想只搬砖？——Mysql中索引相关和SQL优化

索引相关

索引是数据库优化最常用的手段之一，通过索引通常可以帮助用户解决大多数的MySQL性能优化问题。

以下的所有操作都只是抛砖引玉的作用，很多场景都是讲的大多数场景不是绝对的场景，具体场景还是需要具体分析，因为Mysql查询机制很优秀会选择“最优执行计划”也和索引的数据结构有关，请读者按着文章实际操作一次，才能更加贴切的了解到索引相关操作的奥秘，光学不写如竹篮打水。

避免索引失效

以下操作都是模拟操作，请自己手动操作一次加深印象

全值匹配

对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。

# 创建索引
create index_name on table_name(a,b,c);
# sql语句
select * from table_name where a=? and b = ? and c=?;
# explain查看sql执行状态...

---

最左前缀法则

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

如刚刚创建的索引列为a，b，c。a就是该索引中的最左前列。

以下将列出几种状况。大家最好都explain看下结果然后理解加深映像。

# 只查a 走索引
select * from table_name where a = ?;
# 只查 a,b  走索引
select * from table_name where a= ? and b = ?;
# 查 a,b,c 走索引 全值匹配中已经列过 略

# 只查b 不走索引 违背了最左前缀原则 最左前列为a
select * from table_name where b = ?;
# 查 b,c 不走索引，同只查b
# 只查c 不走索引，同只查b


# 只查 a,c中a走了索引 c没走索引 这里需要配合explain命令key_len字段 发现该查询出的key_len和只查a的ke_len相同，相当于用到的索引长度只用到了a的索引，并没有用到c的索引。 违背最左前缀原则，不允许跳过索引列。
select * from table_name where a = ? and c = ?;

# 交换顺序查 b,a 走索引 最左前缀原则和查询顺序无关。
select * from table_name where b = ? and a = ?;

最左前缀总结

联合索引时，建立索引就像是上楼。索引列从左到右，就像从1层爬到n层。人只能一层层的上楼。

当不包含a列时，就相当于不爬一层楼，直接爬第二层楼，第三层楼，这样是不会走索引的。

同样查a，c时，爬了一层楼，直接上第三层楼，也是上不上去的，只会用到a的索引。

---

使用索引注意事项

尽量使用覆盖索引

当使用select *时，就算where的查询符合索引规范，但是实际效率是比 select 索引字段效率低的。

# 使用 select * 查询 这种方式需要回表查询
select * from table_name where a = ?;
# 使用 select 索引列字段查询 该种方式就是覆盖索引
select a,b,c from table_name where a = ?;
# 使用 select 索引字段，加上其他字段 这种方式需要回表查询
select a,b,c,d from table_name where a = ?;

此时用explain主要关注点在extra扩展信息这块

using index : 使用覆盖索引的时候会出现

using where : 在查找使用索引的情况下，需要回表去查询所需的数据

using index condition : 查找使用了索引，但是需要回表查询数据

using index;using where : 查找使用了索引，但是需要的数据都在索引列中能找到，不需要回表查询了

如果一个索引包含所有需要的查询的字段的值，我们称之为覆盖索引。覆盖索引是非常有用的工具，能够极大的提高性能。因为，只需要读取索引，而无需读表，极大减少数据访问量

字符串不加单引号

a的字段为varchar类型

例如select * from table_name where a = 1

改成select * from table name where a = ‘1’

用or分割开的条件

如果or前的条件中的列有单列索引，不能是复合索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。

a,b,c的联合索引，d不是索引列，中间是or进行连接是不走索引的。

# 索引全部失效
explain select * from table_name where a = ? or d = ?;

范围查询

mysql 会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。范围列可以用到索引，但是范围列后面的列无法用到索引。即，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引

like语句的索引问题

如果通配符 % 不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀
在 like “value%” 可以使用索引，但是 like “%value%” 不会使用索引，走的是全表扫描

如果想解决%出现在开头这个问题使覆盖索引来解决这个问题。

a,b,c的联合索引

# 使用覆盖索引解决
select a,b,c from table_name where a like '%哈哈%';

不要在列上进行运算

如果查询条件中含有函数或表达式，将导致索引失效而进行全表扫描
例如 select * from user where YEAR(birthday) < 1990
可以改造成 select * from users where birthday <’1990-01-01′

索引不会包含NULL列

只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL 值，那么这一列对于此复合索引就是无效的。所以在数据库设计时不要让字段的默认值为 NULL

选择区分度高的列作为索引

尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度就是 0。一般需要 join 的字段都要求区分度 0.1 以上，即平均 1 条扫描 10 条记录。

Mysql不使用索引，全表扫描

当我们某个字段的值区分度分常低的时候，Mysql底层会判断发现，走了索引和全表扫描性能差距不大，甚至全表扫描速度比走索引还快时，Mysql就不会走索引。

场景： user表中总共有100条数据，age为索引列，其中age=23的有99条，age=22的有1条。 索引区分度=0.02=2/100。

因为age=23数据占99%占绝大多数，Mysql不如走全表扫描速度快点。

age=22占1%，Mysql走索引要快点，所以走索引。

# Mysql会判断不走索引
explain select * from user where age = 23;

# 当查询age=22的时候走索引
explain select * from user where age = 22;

IS NULL 和 IS NOT NULL 有时走索引。

和Mysql不使用索引，全表扫描的原理差不多。

当NULL值占比很大时，where a is null 不走索引全表扫描。is not null大概率会走索引，具体要看占数据比例。Mysql会自己判断。

当NOT NULL值占比大时，where a is not null不走索引全表扫描。is null 大概率会走索引，具体看占数据比例。Mysql会自己判断。

in 走索引 not in 有时不走索引

# 走索引
select * from table_name where id in (1,2,3);
# 不走索引
select * from table_name where id not in(1,2,3);

这时我们在实际查询中发现怎么not in也走索引了？

具体问题还是要具体分析的。

因为在很多场景下，NOT IN或<>两类操作使用二级索引的成本远超于全表扫描的成本，查询优化器按照成本选择"最优执行计划"，导致查询不走二级索引。但不能因此就彻底判断NOT IN或<>两类操作不能走索引。

---

单列索引和复合索引的选择

尽量使用复合索引，少使用或尽量不用单列索引

创建复合索引就相当于创建了三个索引

例如复合索引a,b,c 创建了如下三个索引

1. a
2. a + b
3. a + b + c

如果创建了3个单列索引，Mysql会选择最优的一个索引使用，不会用到全部索引。

Mysql怎么去选择最优索引呢？ 这涉及到上面说的选择区分度高的字段加索引。区分度越高的索引查询出的数据越少，查询速度越快。

以下讲解几种情况。

场景：表中两个字段有单列索引 name和mobile 表中10条数据。

name有5条数据都为’测试’，其余的数据都不相同。

mobile有9条数据都为’12345678901’，剩下一条为’12345678902’

# 这个和最左前缀原则无关，因为都是单列索引 最终选择了name的索引
explain select * from table_name where name = '测试' and mobile = '12345678901';

# 当mobile='12345678902' 最终选择mobile索引
explain select * from table_name where name = '测试' and mobile = '12345678902';

---

查看索引的使用情况

# 查看当前会话的索引使用情况
show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 74    |
+-----------------------+-------+
6 rows in set (0.00 sec)

# 查看全局的索引使用情况
show global status like 'Handler_read%';
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| Handler_read_first    | 1785153      |
| Handler_read_key      | 2650399364   |
| Handler_read_next     | 104781425    |
| Handler_read_prev     | 55082598     |
| Handler_read_rnd      | 14893605     |
| Handler_read_rnd_next | 244848860571 |
+-----------------------+--------------+
6 rows in set (0.00 sec)

分析这几个值，我们可以查看当前索引的使用情况：

Handler_read_first：

索引中第一条被读的次数。如果较高，它表示服务器正执行大量全索引扫描；例如，SELECT col1 FROM foo，假定col1有索引（这个值越低越好）。

Handler_read_key：

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

Handler_read_next ：

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

Handler_read_prev：

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

Handler_read_rnd ：

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

Handler_read_rnd_next：

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

---

SQL优化

优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

对一张表插入很多行数据时

尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接，关闭等消耗。使得效率比分开执行的单个insert语句快。

原始方式：

insert into table_name values('1','a');
insert into table_name values('2','b');
insert into table_name values('3','c');

优化后方案：

insert into table_name values('1','a'),('2','b'),('3','c');

在事务中进行数据插入

减少多次自动提交事务的连接

start transaction;
insert into table_name values('1','a');
insert into table_name values('2','b');
insert into table_name values('3','c');
commit;

数据主键有序插入

# 以下是无序的
insert into table_name values('2','b');
insert into table_name values('1','a');
insert into table_name values('3','c');

---

优化order by 语句

explain查看执行计划的extra，using index效率最高 using filesort效率低

1. 首先order by的字段必须都是有索引的，其次要使用覆盖索引返回，不然会using filesort效率低
2. order by的顺序和联合索引的顺序必须相同
3. order by的字段要么都是升序，要么都是降序

filesort优化

当我们无法避免filesort优化时，还是需要对filesort进行优化

两种排序方式

1. 两次扫描算法：MySQL 4.1之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区sort buff中排序，如果sort buff不够，则在临时表temporary table中存储排序结果。完成排序后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
2. 一次扫描算法：一次性去除满足条件的字段，然后在排序区sort buff中排序后直接输出结果集。排序内存开销大，但是排序效率比两次扫描算法高。

具体优化方式

MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小，来判定是否哪种排序方法，如果max_length_for_sort_data更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高max_length_for_sort_data和sort_buffer_size系统变量，来增大排序区的大小，提高排序效率。

---

优化group by语句

由于Group By实际上同样会进行排序操作，而且与ORDER BY相比，GROUP BY主要是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

1. 如果查询包含group by但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序。如下：

   # 这样执行 explain时 extra会提示using filesort
   select age, count(*) from table_name group by age;
   
   # 优化后
   select age, count(*) from table_name group by age order by null;
   

2. 创建对应字段的索引来提高group by的效率

---

优化嵌套查询

MySQL4.1之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另外一个查询当中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，而且写起来很容易。但是，有些情况下，子查询时可以被更高效的连接（JOIN）替代。

---

优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

建议使用union替换or

# 替换前 
select * from table_name where a = 1 or b = '测试';

# 使用union 优化
select * from table_name where a = 1 union select * from table_name where b = '测试'

# 使用union替换后可以发现相比or explain出来后 or 的type为range union的为两个效率更好的type

---

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头疼的问题就是limit 2000000,10

此时需要MySQL排序前2000010条记录，仅仅返回2000000-2000010的记录，其记录丢弃，查询排序的代价非常大。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原标查询所需要的其他列内容。

explain select * from table_name t0,(select id from table_name order by id limit 2000000,10) t1 where t0.id = t1.id;

优化思路二

该方案适用于主键自增的表（不能出现id断层，也不能出现其他条件查询），可以把limit查询转换成某个位置的查询。

explain select * from table_name where id > 2000000 limit 10;

---

使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

USE INDEX

在查询语句表明的后面，添加use index来提供希望MySQL去参考（仅仅是一个参考，并不是强制性的，最终选择还是看Mysql选择的最优查询方案）的索引表，就可以让MySQL不再考虑其他可用索引

当explain查看执行计划时，possible_keys有多个时可以使用。

explain select * from table_name use index(index_name);

IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index

explain select * from table_name ignore index(index_name);

FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用force index。因为use index只是一个参考，当Mysql判断参考的查询索引不够快时，会选择其他索引查询。

explain select * from table_name force index(index_name);

放在最后

本文根据学习B站【黑马程序员】2020最新MySQL高级教程（求职面试必备）p47-p66整理笔记，有需要的同学可以前去系统的学习。