这是我参与2022首次更文挑战的第24天，活动详情查看：2022首次更文挑战

具体问题，具体分析。

参考文章：

mp.weixin.qq.com/s/S4cz0_AuH…

zhuanlan.zhihu.com/p/387475772

SQL

以表为例：

CREATE TABLE `test1` (
  `id` bigint(20) NOT NULL,
  `code` varchar(30) NOT NULL,
  `age` int(11) NOT NULL,
  `name` varchar(30) NOT NULL,
  `height` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_code_age_name` (`code`,`age`,`name`) USING BTREE,
  KEY `idx_height` (`height`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

索引

没加索引

这个加索引就可以了。

索引没生效

可以通过 explain 查看sql是否走了某个索引：explain中的key字段，可以看到使用的是哪个索引。

explain中的key_len

explain中，key_len可以判断索引的利用率。

key_len为以下三个得和：

占用字节长度（以下单位都是字节）：
- 字段标注长度 * 3(这个3和字符集有关，utf8对应的是3)
是否存储NULL值？是则+1,否则为0
存储变长字段？如果是，那么+2
- InnoDB存储变长字段的实际长度，可能用1或者2；但explain是引擎层的功能，因此这里就直接取2

不同类型，占用的字节长度如下(n为选取字符集对应的字节数)：

字段类型	占用字节数
char(n)	3n
varchar(n)	3 n + 2
tinyint	1
smallint	2
int	4
bigint	8
date	3
timestamp	4
datetime	8

在上述表中，执行sql：

explain select * from test1 where code = '001' and age=18 and name='张飞' ;

可以得出结果为 188，推算如下：

188 = 3 * (30) + 2 + 4 + 3 * 30 + 2

对应是：code + age + name。

索引没生效的情况

通过explain可以看到走的哪个索引，通过key_len列，可以看到联合索引的利用效率。

索引失效(或者说部分失效)情况如下：

不满足最左前缀原则

这个没什么好说的，通过sql可以直观看出来。
索引使用不充分

比如：
- 联合索引中，只使用到了前面的索引列
- 查询的结果列中包括了索引以外的其他字段（这个会回表）
联合索引上，之前的列上使用了比较
- 这个其实还好，因为会有索引下推的功能（在extra列可以看到Using index condition且没有Using Where，但key_len小于索引列长度），但也只能说是部分利用到索引
索引列上使用了计算、函数
- 这个就没什么好说的了，使用了计算和函数就不能作准了。
这部分我们可以参考下面的：
```
EXPLAIN
select * from test1 
where code = '123'
and age/10 = 100
and name = '123'
```
key key_len extra
idx_code_age_name 92 Using index condition

结合上面所说的key_len的计算方式，可以得知SQL执行的大致逻辑：
- 先从联合索引上，通过code，取得对应的age和name
- 随后，在引擎层再判断 age/10=100和name= '123'
- 最后，把满足上述判断的索引叶子节点的页面上的id，拿出来回表查出所有数据。
函数也是类似：
```
EXPLAIN
select * from test1 
where code = '123'
and ABS(age) = 100
and name = '123'
```
key key_len extra
idx_code_age_name 92 Using index condition

通过上述实验可以得知：
- 如果使用了计算、函数，那么对于使用的列而言，并不会命中任何索引。
因此，最好是用一个字段存下来，否则会造成查询慢。

key	key_len	extra
idx_code_age_name	92	Using index condition

key	key_len	extra
idx_code_age_name	92	Using index condition

like查询左模糊（like '%123'）

这个没什么好说的，索引是根据列从左到右的数据，根据排序规则来排序的。

也就是说，如果是右模糊，实际上就相当于是一个范围查询；如果是左模糊，就无法使用索引进行查询。

例子：

SQL type key_len extra
select * from test1 where code like '123%' and age = 100 and name = '123' range 188 Using index condition
select * from test1 where code like '%123' and age = 100 and name = '123' ALL null Using where

or的使用

or其实相当于是若干or语句中结果的join，可以参见索引合并。
选错索引

这个没什么好说的，执行计划可能会选择别的索引，这和执行计划的判断方式有关。

SQL	type	key_len	extra
select * from test1 where code like '123%' and age = 100 and name = '123'	range	188	Using index condition
select * from test1 where code like '%123' and age = 100 and name = '123'	ALL	null	Using where

这里参考的博客中，还有一个是在非null字段上使用 is null 和 is not null，其实这里可以这么解释：

在非null字段上，使用 is null，相当于查不到数据，直接返回了。

在非null字段上，使用 is not null ,相当于查询所有该字段数据。

对于null字段，分析和上面也类似。

sql优化

参考的博客中，提到的大部分都是很实用的一些小技巧。

SQL语句相关

多用limit，增量查询，高效分页

这里指向的都是和数据量有关的问题，如果查询数据过多确实容易导致查询变慢，这种情况限制大小可以有效缓解。

批量操作

如果执行相同类型的操作(比如：update、insert)，我们就尽量让这些东西，通过批量的方式来运行，这样的好处是可以尽量减少服务和mySQL服务器之间重复连接所耗费的时间。

in值不宜太多

因为in的方式，sql的实际执行是把in中的值作为若干个等于去处理的，那么如果in里的值太多。。。

连接相关

这里就包括：

小表驱动大表
用union all 代替union
- 这个的原因是union会排序，而union all并不会
用连接代替子查询
join表不宜过多
join尽量走索引

这部分的原因参见掘金上的mySQL小册。

字段相关

避免使用select *

因为非群簇索引，索引的最底层数据上会带有对应的群簇索引的id，如果查找了非该索引中的数据 ，就会通过这个id，回到群簇索引中再查找或执行下一步判断（这个操作称为回表）。

选择合理字段类型

字段的大小会影响我们从表中取数据这个操作，因此我们的字段要在满足使用条件下，尽可能地小一些，来加速我们的查找。

索引相关

其实上面也右这部分的内容，比如join尽量走索引。

提升group by效率

这个也是尽量走索引的逻辑。

也参见小册。

索引优化

这个就见仁见智了，大部分都是具体情况具体分析，通过explain来分析。

控制索引数量

毕竟索引也是单独的B+树，索引数量多了，不管是插入、更新，都会影响到sql的执行效率。

其他的

用逻辑删除，代替物理删除

毕竟mysql中，删除并不是实时删除，而是通过后续的线程定时删除；况且，该定时删除的也不是马上就把数据删掉了。因此，不如留下来，后面再自己做truncate。(见参考2)

[优化]SQL性能优化笔记