【MySQL索引】4索引优化

索引优化

1 关联查询优化

左连接LEFT JOIN

LEFT JOIN 右边是我们的关键点,一定需要建立索引 .这里是book的card 字段，type建不建索引无所谓。

ALTER TABLE book ADD INDEX Y ( card); #【被驱动表】，可以避免全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM `type` LEFT JOIN book ON type.card = book.card;

内连接INNER JOIN

换成 inner join（MySQL自动选择驱动表 ，一般选取小表为驱动表）
被驱动表加上索引即可。

小结

保证被驱动表的JOIN字段已经创建了索引

需要JOIN 的字段，数据类型保持绝对一致。

LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。

INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略。

能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)

不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询。

衍生表建不了索引

2 子查询优化

3 排序优化

order by 优化FileSort ,但使用FileSort不一定慢

INDEX a_b_c(a,b,c)
order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC
如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c
不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

例子：

4 GROUP BY优化

5 优化分页查询

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

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 2000000,10)
a
WHERE t.id = a.id;

2 该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

6 优先考虑覆盖索引

7 对字符串添加前缀索引的考虑

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。前面已经讲过区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

8 索引下推

Index Condition Pushdown(ICP)是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。

使用前，存储层多返回了需要被index filter过滤掉的整行记录
使用ICP后，直接就去掉了不满足index filter条件的记录，省去了他们回表和传递到server层的成本。
ICP的 加速效果 取决于在存储引擎内通过 ICP筛选 掉的数据的比例

9 普通索引 vs 唯一索引

1. 普通索引和唯一索引应该怎么选择？其实，这两类索引在查询能力上是没差别的，主要考虑的是对 更新性能 的影响。所以，建议你 尽量选择普通索引 。
2. 在实际使用中会发现， 普通索引 和 change buffer 的配合使用，对于 数据量大 的表的更新优化还是很明显的。
3. 如果所有的更新后面，都马上 伴随着对这个记录的查询 ，那么你应该 关闭change buffer 。而在其他情况下，change buffer都能提升更新性能。
4. 由于唯一索引用不上change buffer的优化机制，因此如果 业务可以接受 ，从性能角度出发建议优先考虑非唯一索引。但是如果"业务可能无法确保"的情况下，怎么处理呢？

首先， 业务正确性优先 。我们的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。这种情况下，本节的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，给你多提供一个排查思路。
然后，在一些“ 归档库 ”的场景，你是可以考虑使用唯一索引的。比如，线上数据只需要保留半年，然后历史数据保存在归档库。这时候，归档数据已经是确保没有唯一键冲突了。要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

10 其他

EXISTS IN

不太理解哪种情况下应该使用 EXISTS，哪种情况应该用 IN

• 使用 EXISTS 的场景：

测试是否存在：当你只关心某个条件是否在子查询中存在时，EXISTS 是一个更自然的选择。
子查询结果集较大：当子查询返回的数据集较大时，EXISTS 可以避免不必要的全表扫描，因为一旦找到符合条件的行，就会终止子查询。

• 使用 IN 的场景：

具体匹配值：当你需要检查某个具体的值是否存在于子查询的结果集中时，IN 是更适合的选择。
子查询结果集较小：如果子查询返回的结果集较小，使用 IN 可以是一个简单而有效的方法。

COUNT(*) COUNT(具体字段)

SELECT COUNT(*) 、 SELECT COUNT(1) 和
SELECT COUNT(具体字段) ，使用这三者之间的查询效率是怎样的？

性能总结
COUNT(*)：最常用且效率最高的方法，大多数现代数据库系统对此做了专门的优化。

COUNT(1)：在性能上与 COUNT(*) 基本相同，大多数数据库系统会将它们处理得一样快。

COUNT(具体字段)：可能会更慢，特别是在字段上没有索引时。它只计算非 NULL 的字段值，因此可能需要扫描整个表来完成计数。

选择指南
如果需要计算表中的所有记录数，不考虑 NULL 值，请使用 COUNT(*)。

如果需要计算某个字段的非 NULL 值的数量，请使用 COUNT(具体字段)。

对于性能要求较高的查询，优先考虑 COUNT(*) 或 COUNT(1)，因为它们在绝大多数情况下效率相当，并且有数据库系统的优化支持。

SELECT(*)

在表查询中，建议明确字段，不要使用 * 作为查询的字段列表，推荐使用SELECT <字段列表> 查询。
原因：
① MySQL 在解析的过程中，会通过 查询数据字典 将"*"按序转换成所有列名，这会大大的耗费资源和时间。
② 无法使用 覆盖索引

LIMIT 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果你可以确定结果集只有一条，那么加上 LIMIT 1 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。
如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 LIMIT 1 了。

多使用COMMIT

只要有可能，在程序中尽量多使用 COMMIT，这样程序的性能得到提高，需求也会因为 COMMIT 所释放的资源而减少。

COMMIT 所释放的资源：
回滚段上用于恢复数据的信息
被程序语句获得的锁
redo / undo log buffer 中的空间
管理上述 3 种资源中的内部花费