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 < ...
  

索引覆盖和下推

mysql 调优-利用索引覆盖和下推

其他优化

• 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 了