mysql 调优-sql 优化
连接查询
原理涉及到 index nested-loop join ， block nested-loop join ，join buffer size ，hash join（mysql8）等，感觉没必要记那么多
需要知道会一次性把驱动表的数据加载到内存中（如果 join buffer size 放得下），然后循环每个驱动表去对比被驱动表的数据就好了
- 保证被驱动表的JOIN字段已经创建了索引
- 需要 JOIN 的字段，数据类型保持绝对一致，不然会隐式转型会导致索引失效
- LEFT JOIN 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数
- INNER JOIN 时，MySQL会自动将 小结果集的表选为驱动表 。选择相信MySQL优化策略
- 能够直接多表关联的尽量直接关联，不用子查询。(减少查询的趟数)
- 不建议使用子查询，建议将子查询SQL拆开结合程序多次查询，或使用 JOIN 来代替子查询
子查询
能不使用就不使用，可以想办法使用连接查询替换子查询
- 如果需要会产生临时表，临时表的创建和销毁需要额外的资源
- 临时表的结果集不会有索引
- 子查询结果集越大，性能就越差
order by
order by 的 explain Extrl 中会有两种情况，一种是使用索引 index，一种是 FileSort
FileSort 不能使用索引，所以只能把数据加载到内存中排序，如果结果集过多，只能放到磁盘进行IO来排序
- 当数据量过大哪怕 order by 的列有索引，也可能不会走索引
- 前提是查的列和order by 的列不同和没有（意味着不能索引覆盖，每个 select 的别的字段都需要回表）
- 当数据量很大，就算有索引，也要每条数据都回表，这个代价也很大，优化器可能就会直接不适用该索引，把所有的数据都放进内存
- order by 多个字段
- 因为索引是有顺序的，如果要降序，所有的字段都要降序才会走索引。不能一些升序一些降序
分页
-
比如 limit 200000, 10。mysql 会查询 200010 条数据丢弃前 200000，只要最后10条
-- 优化思路1（id不自增） select * from t_user t1 inner join (select id from t_user order by id limit 200000, 10) t2 on t1.id = t2.id -- 优化思路2（id自增，且步长为1） select * from t_user where id > 200000 limit 10 -- 注意上面两种方式 EXPLAIN 的 orws 会很大，如果把范围定死能解决 ... where id > ... and id < ...
索引覆盖和下推
其他优化
- EXISTS 和 IN（遵循小表驱动大表原则）
- 驱动表是小表，就用 EXISTS；驱动表是大表就用 IN
- COUNT（前提是 Mysql 和 没有 null 字段）
- 如果是 MyISAM ，复杂度是 O(1)
- 如果是 InnoDB，复杂度是O(N)
- COUNT(1) 和 COUNT(*) 差不多
- COUNT(列)，不要用ID，因为是聚簇索引占用空间比较多，会大于COUNT(二级索引列)。即使用 COUNT(1)，MySQL 也会找一列占用空间小的二级索引列来统计
- SELECT *
- 把 * 会转化成具体的字段，到系统表中查这个表有哪些字段
- 不能使用覆盖索引
- LIMIT 1
- 首先 LIMIT 会全表扫描，如果有了 limit 1，就不会全表了，找到一条就OK
- 如果 where 列有唯一索引，就可以不需要 limit 1 了
