最佳左前缀匹配原则

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。在 MySQL 建立联合索引时会遵守最佳左前缀匹配原则：

最左优先，在检索数据时从联合索引的最左边开始匹配。
对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

当我们需要关注联合索引的使用情况,可以关注explain中key_len

主键插入顺序

当我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插;而如果我们插入的主键值忽小忽大的话，则可能会造成页面分裂和记录移位。(想想我们之前所说的聚集索引)

保证主键单调递增,是非常有必要的;

不过自增id在分布式场景下,并不是最佳选择

自增id,若业务之后分库,则自增id只能保证局部唯一性!

业务安全性不高,很容易让人根据/user/1,猜出接口,进而更容易被爬取数据!

交互多一次,当你需要在插入前得到id,还需要到数据库进行交互一次!

主键,最好是业务无关的,且单调递增的;

mysql的UUID = 时间+UUID版本（16字节）- 时钟序列（4字节） - MAC地址（12字节）

你可以使用雪花id,也可以使用mysql中的uuid;

SET @uuid = UUID(); 

// uuid 36字节,唯一无序; 
// uuid_to_bin(@uuid) 32字节,去除短横杠
// uuid_to_bin(@uuid,TRUE) 全局唯一,且单调有序
SELECT @uuid,uuid_to_bin(@uuid),uuid_to_bin(@uuid,TRUE);

// 二进制的数据在命令行中并不是可读的,我们可以使用 HEX() 函数将它转为十六进制字符串
SELECT @uuid, HEX(UUID_TO_BIN(@uuid)),  HEX(UUID_TO_BIN(@uuid, 1));

索引失效

计算函数类型转换导致索引失效

SELECT * FROM user WHERE user.name LIKE 'abc%'; // 走索引
SELECT * FROM user WHERE LEFT(user.name, 3) = 'abc'; // 无法走索引

SELECT * FROM user WHERE no = 100; // 走索引
SELECT * FROM user WHERE no + 1 = 101;// 无法走索引

# 你可以强制使用 SQL_NO_CACHE 来避免命中缓存,以模拟更真实线上场景
SELECT SQL_NO_CACHE <search_column> FROM <table> where <condition>

为什么使用函数和计算无法走索引

因为索引保存的是索引字段的原始值，而不是经过(函数)计算后的值，自然就没办法走索引了。

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

举个例子,我们希望对 length(name)能使用索引,则可以建立如下索引

alter table user add key idx_name_length (length(name));

隐式类型转换导致索引失效

# 隐式类型转换name本身是作为字符串保存的
SELECT * FROM user WHERE name = '123'; / 走索引
SELECT * FROM user WHERE name = 123; // 无法走索引
# 你可以简单的认为 类型转换使用了函数 CAST(name AS signed int ) = 123

为什么此处的隐式转换无法走索引?

这其实和排序规则有关; 索引是按照(排序规则)进行排序的,针对字符串的排序规则和数字的排序方式是不一样的;

想想你在使用java时,对字符串和数字的排序方式!

对于字符串 "10" < "9" (字典排序规则);对于数字 10 > 9 (数值比较)

字符串和数字比较(如此处的 name = 123);mysql会强制按数值的方式进行比较,则相当于对name加上了类型转换函数,查询方式即为 CAST(name AS signed int ) = 123;则此刻是无法走索引的

# 比如id作为主键,是big_int
select * from t_user where id = '1'; // 走索引

为什么 id = '1'则可以走索引

在mysql中,对于字符串和数字的比较,是将字符串转换成数字,然后进行比较;也就是最终执行的还是id=1的查询;

其他的索引失效

不等于（!= 或者<>）索引失效
is null可以使用索引，is not null无法使用索引
like 以通配符 % 开头索引失效
OR 前后存在非索引的列，索引失效

or 索引失效举例

OR前后的两个条件中的列都是索引时，查询中才使用索引

# name存在索引,而age不存在索引;则此sql无法走索引
SELECT SQL_NO_CACHE * FROM user WHERE name = 'lilei' or age = 10;

索引下推概念
索引下推,是针对联合索引的;
对于联合索引(name,age,job)，正常情况按照最左前缀原则，SELECT * FROM user WHERE name like 'LiLei%' AND age = 22 AND job ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。
在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。
MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。
我们可以从执行计划中的 Extra=Using index condition 使用了索引下推功能
Mysql如何选择合适的索引
索引的离散率
为什么有时候,即使我们创建了二级索引,但是执行查询时,却没有走索引?
索引的离散度过低,查询二级索引之后(还需要回表),不如直接查询聚集索引,查询速度更快!
trace分析
一、什么是trace工具
MySQL5.6版本开始，推出了对SQL的跟踪工具trace，通过使用trace，用户可以查看MySQL优化器对SQL语句的分析结果，以及生成了怎样的执行计划。
二、trace工具的使用
1、开启/关闭trace工具
开启：set session optimizer_trace="enabled=on",end_markers_in_json=on;
关闭：set session optimizer_trace="enabled=off";
开启trace工具会对MySQL的性能造成一定的影响，所以建议在需要分析SQL语句的执行计划生成过程时才开启trace工具；
2、执行需要进行分析的SQL语句
select name, age, job from user where name = "lilei" and age > 20 and job = "manage";
3、查询执行计划
执行SQL语句：SELECT * FROM information_schema.OPTIMIZER_TRACE;
执行轨迹,通过json描述;建议你能有一个能观察json的工具;

执行成本详情.json

JSON复制代码

重点观察 sql优化阶段 join_optimization

rows_estimation // 访问成本的计算
// 查询访问聚集索引的成本计算
"table_scan": {
                /* 全表扫描预估扫描651300条记录，成本是227957*/
                "rows": 651300,
                "cost": 227957
               },
// 可能使用到的索引,以及其对应的执行成本
"potential_range_indexes" // 可能使用到的索引

之后还会再次说明执行成本的计算规则;

索引的优化

join的优化问题

驱动表根据此表中的数据(连接条件) 查询被驱动表;

使用join经常出现在主表(驱动表)和从表(被驱动表)间;如账户表和交易流水表(1个账户可以有多条交易流水);

交易流水表的数据明显是远大于账户的;(一般来说数据量较少的为成为驱动表)

特别需要注意的是，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤(filtered列)，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

连接类型

join连接算法

假若被驱动表不存在索引,采用

Simple Nested-Loop Join（简单嵌套循环连接,简称SNLJ）

Block Nested-Loop Join（块嵌套循环连接,简称BNLJ）

BNLJ相关参数

① block_nested_loop 是否开启块嵌套循环,默认开启

② join_buffer_size 默认配置256K,你可以配置成4G大小

从 MySQL 的 8.0.20 版本开始将废弃 BNLJ，因为从 MySQL 8.0.18 版本开始就加入了 hash join，默认都会使用 hash join。

Hash Join 是做大数据集连接时的常用方式，优化器使用两个表中较小（相对较小）的表利用 Join Key 在内存中建立散列表，然后扫描较大的表并探测散列表，找出与 Hash 表匹配的行

hash 需要在内存中建立hash表,会占用更多内存;但是hash查询的效率是最快的;

在全局或者会话级别设置服务器系统变量 optimizer_switch 中的 hash_join=on 或者 hash_join=off 选项。默认为 hash_join=on。
join_buffer_size,同样也控制着hash join的内存

假若被驱动表存在索引,采用

lndex Nested-Loop Join（索引嵌套循环连接）

开销统计	简单嵌套循环连接	索引嵌套循环连接
join比较次数	A * B	A * 索引层高
读取记录数	A + A * B	A + B
回表读取次数	无需回表	B

join算法实战演练

根据Explain分析得出读取记录数

mysql> explain select * from t1 join t2 on t1.b=t2.b  join t3 on t1.b=t3.b where t1.a<21;
+----+-------------+-------+------+---------------+------+----------+-------------+
| id | select_type | table | type | possible_keys | rows | filtered | Extra       |
+----+-------------+-------+------+---------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | ALL  | a             |  100 |    20.00 | Using where |
|  1 | SIMPLE      | t3    | ALL  | NULL          |  200 |    10.00 | Using where |
|  1 | SIMPLE      | t2    | ALL  | NULL          | 1000 |    10.00 | Using where |
+----+-------------+-------+------+---------------+------+----------+-------------+

对于 t1表作为驱动表,扫描行数为 100,扇出区为 100 *0.2=20;

t3扫描行数为 200,则实际join比较次数为 20 * 200;

t2扫描行数为 1000,则实际join比较次数为 20 * 1000;

实际扫描总行数=100+4000+20000=24100。

join小结

永远使用小结果集驱动大结果集
为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）。
增大 join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）。
减少驱动表不必要的字段查询（字段越少，join buffer 所缓存的数据就越多）。

order by 和 group by的优化问题

1、在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序：

① 在 lndex 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。

② FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

2、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

order by满足两种情况会使用Using index。

order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。

如执行 where name = 'constant' order by job; (整体满足了联合索引,排序也是按照索引排序)

3、能用覆盖索引尽量用覆盖索引

4、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

order by 时规则不一致，索引失效（联合索引的顺序错，索引失效；方向反，索引失效）

如 order by name, job desc; (job按反序排列)

与索引的排序方式不同，从而使用Using filesort排序。Mysql8.0版本后有降序索引(自行查阅)可以支持这种排序方式。

排序小结

INDEX a_b_c(a,b,c) order by 能使用索引最左前缀

满足索引最左前缀,使用索引

ORDER BY a,b,c
WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c

无法使用索引

ORDER BY a DESC,b DESC,c DESC
WHERE a = const AND b > const ORDER BY c 使用了范围查询,无法使用索引
WHERE a in (...) ORDER BY b,c 对于排序来说，多个相等条件in也是范围查询,无法使用索引
ORDER BY a ASC,b DESC,c DESC;排序不一致
WHERE b = const ORDER BY c;丢失a索引
WHERE a = const ORDER BY c;丢失b索引
WHERE a = const ORDER BY a,d;d不是索引的一部分

filesort单双路排序

单路排序：从磁盘一次性读取查询需要的所有列，按照order by列在sort_buffer中对它们进行排序。

双路排序（又叫回表排序模式）:1.先从磁盘中取排序字段,在 buffer 进行排序2.然后再回表从磁盘取其他字段。

单双路排序如何确认?

根据trace中的sort_mode可以确定

单路排序显示为<sort_key, additional_fields>或者<sort_key, packed_additional_fields>

双路排序显示为<sort_key, rowid>

双路排序是,第一次取出的字段为排序字段以及主键id;(毕竟是要回表到聚集索引的,笑)

重要参数解释

1.max_length_for_sort_data: 由此数值决定,排序采用何种排序方式,默认值1024byte;

当总查询字段大小 > max_length_for_sort_data ,使用双路排序

当总查询字段大小 < max_length_for_sort_data ,使用单路排序

2.sort_buffer_size: 此数据值决定,排序时使用的内存大小,默认1Mb;此参数是针对每个进程 (connection) 的 1M-8M 之间调整;

提示:

如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增

大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整;

Order by 时 select * 是一个大忌，最好只查询需要的字段。

1.过多的字段,导致字段总和过大,进而进行双路排序;

2.两种算法的数据都有可能超出 sort_buffer_size的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些，所以要适当提高 sort_buffer_size。

分页查询优化问题

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 1000000,10，此时需要 MySQL 排序前 1000010 条记录，但仅仅返回 1000000 - 1000010 之间的记录，其他记录丢弃，查询排序的代价非常大。

主要优化思路有2种

1.针对主键单调递增的,可以使用主键来排序

select * from user where id > 1000000 limit 10;

根据你上一次查询返回的最大id,利用聚集索引,获取10条数据

2.针对使用二级索引,或者非递增的主键

SELECT * FROM user u inner join 
	(SELECT id FROM user ORDERBY id LIMIT 1000000, 10) ut
on u.id = ut.id;

select * from user u inner join 
	(select id from user order by name limit 1000000, 10) ut
on u.id = ut.id;

主要思路是减少参与排序的字段

覆盖索引

覆盖索引,你当前的查询,只需要根据二级联合索引,则可以全部查询出结果,而不需要回表

前缀索引,请自行查阅

count(*) count(1) 和 count()

在 InnoDB 引擎中，如果采用 COUNT(具体字段) 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 COUNT(*) 和 COUNT(1) 来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。

mysql索引失效以及优化原则