这是我参与11月更文挑战的第12天，活动详情查看：2021最后一次更文挑战
不走索引的原因
1. 函数操作
select * from t1 where date(c) ='2019-05-21'; 改为： select * from t1 where c>='2019-05-21 00:00:00' and c<='2019-05-21 23:59:59';
对索引字段做函数操作时，优化器会放弃使用索引。
2. 隐式转换
select * from t1 where a=1000; 等价于： select * from t1 where cast(a as signed int) =1000;
改为：select * from t1 where a='1000';
3. 模糊查询
select * from t1 where a like '%1111%'; 改为： select * from t1 where a like '1111%';
如果条件只知道中间的值，需要模糊查询去查，那就建议使用ElasticSearch或其它搜索服务器。
4. 范围查询
select * from t1 where b>=1 and b <=2000; 改为： select * from t1 where b>=1 and b <=1000; select * from t1 where b>=1001 and b <=2000;
优化器会根据检索比例、表大小、I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大， 优化器将不走索引。
5. 计算操作
select * from t1 where b-1 =1000; 改为： select * from t1 where b =1000 + 1;
对索引字段做运算将使用不了索引。
一般需要对条件字段做计算时，建议通过程序代码实现，而不是通过MySQL实现。如果在MySQL中计算 的情况避免不了，那必须把计算放在等号后面。
总结
- 应该避免隐式转换
- like查询不能以%开头
- 范围查询时，包含的数据比例不能太大
- 不建议对条件字段做运算及函数操作
优化数据导入
有大批量导入时，推荐一条insert语句插入多行数据。
因为批量导入大部分时间耗费在客户端与服务端通信的时间，所以多条 insert 语句合并提交可以减少客户端与服务端通信的时间，并且合并提交还可以减少数据落盘的次数。
根据测试，总结了加快批量数据导入有如下方法：
- 一次插入多行的值；
- 关闭自动提交，多次插入数据的 SQL 一次提交；
- 调整参数，innodb_flush_log_at_trx_commit 和 sync_binlog 都设置为0（当然这种情况可能会丢数据）。
让order by、group by查询更快
order by
MySQL 中的 Filesort 并不一定是在磁盘文件中进行排序的，也有可能在内存中排序，内存排序还是磁盘排序取决 于排序的数据大小和 sort_buffer_size 配置的大小。
order by优化技巧
添加合适索引
- 排序字段添加索引。排序字段添加索引。extra里的字段using index(有索引)，using filesort(无索引)
- 多个字段排序的情况，如果要通过添加索引优化，得注意排序字段的顺序与联合索引中列的顺序要一致。因此，如果多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句。
- 先等值查询再排序的优化。对于先等值查询再排序的语句，可以通过在条件字段和排序字段添加联合索引来优化此类排序语句。
去掉不必要的返回字段
- 查询所有字段不走索引的原因是：扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高， 所以优化器放弃使用索引。
几种无法利用索引排序的情况
使用范围查询再排序 ASC 和 DESC 混合使用将无法使用索引
group by
默认情况，会对 group by 字段排序，因此优化方式与 order by 基本一致，如果目的只是分组而不用排序，可以指 定 order by null 禁止排序。
根据自增且连续主键排序的分页查询
select * from t1 limit 99000,2; 改写为： select * from t1 where id >99000 limit 2;
这种改写得满足以下两个条件：
- 主键自增且连续
- 结果是按照主键排序的
查询根据非主键字段排序的分页查询
select * from t1 order by a limit 99000,2;
扫描整个索引并查找到没索引的行的成本比扫描全表的成本更高，所以优化器放弃使用索引。
其实关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：
select * from t1 f inner join (select id from t1 order by a limit 99000,2)g on f.id = g.id;
总结
两种分页查询场景的优化：
- 根据自增且连续主键排序的分页查询优化
- 查询根据非主键字段排序的分页查询优化
对于其它一些复杂的分页查询，也基本可以按照这两个思路去优化，尤其是第二种优化方式。第一种优化方式需要主键连续，而主键连续对于一个正常业务表来说可能有点困难，总会有些数据行删除的，但是占用了一个主键 id。
count
当 count() 统计某一列时，比如 count(a)，a 表示列名，是不统计 null 的。
而 count(*) 无论是否包含空值，都会统计。
对于 MyISAM 引擎，如果没有 where 子句，也没检索其它列，那么 count(*) 将会非常快。因为 MyISAM 引擎会把表的总行数存在磁盘上。
而 InnoDB 并不会保留表中的行数，因为并发事务可能同时读取到不同的行数。所以执行 count(*) 时都是临时去计算的，会比 MyISAM 引擎慢很多。
对比 MyISAM 引擎和 InnoDB 引擎 count(*) 的区别，可以知道：
- MyISAM 会维护表的总行数，放在磁盘中，如果有 count(*) 的需求，直接返回这个数据
- 但是 InnoDB 就会去遍历普通索引树，计算表数据总量
在前面我们知道 count( ) 无论是否包含空值，所有结果都会统计。 而 count(1)中的 1 是恒真表达式，因此也会统计所有结果。 所以 count(1) 和 count( ) 统计结果没差别。
count()优化
show table status
这个值是个估算值，可能与实际值相差 40% 到 50%。
用 Redis 做计数器
通过 Redis 计数的方式，获取表的数据量比 show table status 准确，并且速度也比较快。
增加计数表
计数这一步操作我们用 MySQL 中一张 InnoDB 表来代替。 而数据写入操作和计数操作都放在一个事务中
