高并发，高性能，高可用MySQL 实战-怎么查询速度更快

怎么查询速度更快

where查询太慢怎么办

1. 覆盖索引

• 覆盖索引指的是：查询语句从执行到返回结果均使用的是同一个索引
• 覆盖索引可以有效减少回表
• using index在查询计划里面就叫索引覆盖
  

是否会使用覆盖索引，看下面的例子： 第一个会使用，第二个不会使用
explain select inventory_id, store_id, film_id from inventory where store_id = 1;
explain select inventory_id, store_id, film_id, last_update from inventory where store_id = 1;

何时使用覆盖索引，当我们的一个表中经常要查询的数据就那么两个字段，如果我们给这两个字段建立联合索引，就可以避免二次回表，提升查询性能

总结，重点：

• 覆盖索引（using index）, 覆盖索引通过取消回表操作，提升查询效率（本质B+树的搜索从2次变为1次，提升查询效率）
• 若数据的查询不只使用了一个索引，则不是索引覆盖
• 可以通过优化sql语句或优化联合索引来使用索引覆盖

有更合适的索引不走，怎么办

1. 如何确定用哪条索引

• mysql在选取索引的时候，会参考索引的基数
• 基数是mysql估算的，反应这个字段有多少种取值
• 选取几个页算出取值的平均值，在乘以页数，即为基数

索引基数的应用
索引基数就是数据列所包含的不同值的数量

2. 强制使用索引
   在sql语句后面加上 force index 强制使用索引

3. 优化索引

• analyze table 可以重新统计索引信息
• 重新统计索引信息时，会重新计算索引的基数

总结，重点：

• 根据索引的基数（cardinality），可以判断索引性能的好坏
• 使用force index 强制使用索引
• analyze table重新统计索引信息，修复基数信息
• 索引基数越大，工作效率越好

count()这么慢怎么办

1. count()函数用来统计计算结果集中不为null的个数

• 首先存储引擎查询出结果集
• server层逐个结果判断是否为null, 不为null则+1

2. count(非索引字段)时，server层需要判断每个数据是否为null

• 而且查询本身无法使用覆盖索引，理论上最慢

3. count(索引字段）可以覆盖，依然需要每次判断字段是否为null

• count(主键）同理，虽然主键默认非空，但是依然需要每次判断是否为null

count(索引字段)已经比count(非索引字段)效率要高了，因为不需要回表，但是还是需要每次判断字段是否为null

4. count(1)只有扫描索引树，没有解析数据行的过程，理论更快
   但是server层依然需要判断1是否为null

5. count(*) 一般用来返回数据表行数

• MyISAM存储引擎中，count(*)直接返回数据库中记录的数据表行数
• 由于InnoDB支持事务，所以数据库中不记录数据表的行数
• mysql专门优化了count(*)函数，直接返回索引树中数据的个数

总结：

• count(非索引字段)，不会使用覆盖索引，最慢
• count(索引字段), 会使用覆盖索引，但是会挨个判断索引字段的值是否为null, 不太快
• count(1), 会挑一个索引字段，使用覆盖索引，但是还是会挨个判断1是否为null, 不太快
• count(), mysql专门对count()做了优化，会挑一个索引字段，使用覆盖索引，并且直接返回索引中数据的个数，不会挨个判断是否为null, 最快

order by 这么慢，怎么办？

1. mysql排序前会将查询的结果放到中间结果集
   
2. 回表生成完整结果集
   
3. 最高效，索引覆盖
   
   总结：
4. mysql排序一般要需要生成中间结果集、排序、回表的过程
5. 索引覆盖是最高效的需要处理排序的方式

随机选取这么慢怎么办

order by rand()原理

1. 查询流程需要生成两个临时表
   
2. 为什么会这么慢
   
3. 解决方案-临时方法

select max(film_id), min(film_id) into @M,@N from film;
set @X = floor((@M-@N+1)*rand()+@N);
select title, description from film where film_id >= @X limit 1;

4. 解决方案-业务方案

select film_id, title, description from film limit 1 offset 850;
-- 或,它们两个是等价的
select film_id, title, description from film limit 850, 1

总结：

带头大哥丢了怎么办

索引下推

1. select * from inventory_3 where store_id in (1,2) and film_id=3;
   为什么扫描行数非常少？
   因为使用了索引下推

2. select film_id from inventory_3 where film_id=3;
   由于store_id和film_id使用的联合索引，只通过filem_id过滤数据，带头大哥没了，所以会扫描整个表

3. 松散索引扫描
   

总结：

1. 联合索引可以加速一些特殊查询场景
2. 索引下推可以大大减少回表的次数
3. 松散索引扫描可以打破左侧原则，解决带头大哥丢失问题，它的效率肯定没有直接走索引快，但是如果没有松散索引扫描就会全表扫描就麻烦了

明明有索引就是不走，怎么办

1. explain select * from film where film_id + 1 = 100;
   虽然film_id是主键，但是对索引字段进行了函数操作，优化器会放弃这个索引
   explain select * from rental where month(rental_date) = 5;
   这个sql也会进行全表扫描，使用了month函数，没有走索引

2. 时间函数的优化方法

explain select * from rental where month(rental_date) = 5;

explain select * from rental where rental_date BETWEEN "2005-5-1" and "2005-6-1" or
rental_date BETWEEN "2006-5-1" and "2006-6-1";

第二条语句只扫描了一千多行，第一条语句全表扫描一万多行

3. 字符串与数字比较
   

4. 隐式字符编码转换
   mysql中utf8和utf8mb4字段进行比较时，会把utf8转换为utf8mb4
   

总结：在mysql中，对索引字段做函数操作，优化器会放弃索引
这种情况可能包括：时间函数、字符串和数字转换、字符编码转换
解决方案：时间函数转区间，数字强转字符串，高级编码转低级

分页查询这么慢，怎么办？

1. 偏移量大时，效率低
   explain select film_id, title, description from film order by title limit 900,10;
   发生了扫表，如何优化

• 向想办法走索引覆盖
• 得到所需数据ID
• 根据所需数据ID，得到最终结果集

explain select film_id, title, description from film order by title limit 90,10

explain select f.film_id, f.title, f.description from film as f inner join (
	select film_id from film order by title limit 90,10
) as m
on f.film_id=m.film_id

下面这条sql要比上面的sql快很多倍，第二条扫表201行，第一条全表扫描

小结：

慢查询的怀疑方向：

1. 索引设计有问题
2. sql语句有问题
3. 数据库选错索引