高性能mysql（二）——优化查询

查询优化，索引优化，库表机构优化需要齐头并进，一个不落！

查询的生命周期：

• 客户端发送一条查询给服务器
• 服务器先检查缓存，命中缓存则立即返回，否则进入下一阶段
• 服务器进入sql解析，预处理，再由优化器生成对应的执行计划
• mysql根据执行计划，调用存储引擎的api来执行查询
• 将结果返回给客户

查询为什么慢？

• 很多原因：
  • 在查询的生命周期中，如网络，CPU计算，生成统计信息和执行计划，锁等待都会导致变慢
  • 尤其是向存储引擎检索数据的调用操作
  • 还有一些不必要操作，如重复相同的操作。
• 最基本的原因：访问的数据太多！分析低效查询的2个有效步骤！
  • 客户端请求了太多没必要的数据行，或者列，增加网络开销，和cpu与内存的损耗
    • 常见错误select *，只取自己需要的列！
  • mysql为了返回结果，扫描了太多没必要的行
    • 扫描的行和返回的行，最好的情况当然是扫描行数和返回的行数一样，一般在1：1和10：1之间会比较好。
    • 扫描的行和使用的方式（访问的类型），即访问数据的成本，不同的访问类型成本也不一样
      • 通过explain查看type列，sys—>const—>eq_ref—>ref—>range—>index—>all，无非是没用索引，或者没用到高效的索引
    • where的三种使用方式
      • 在索引中使用where条件过滤不匹配的记录，在存储引擎层实现，效率最好
      • 覆盖索引（useing index），直接从索引中取数据，在服务器层完成，但不需要回表查询
      • 从数据表中返回数据，然后在服务器层过滤不满足条件的数据（using where)

优化特定类型的查询

• 优化max和min
  • 使用索引的max和min，可以直接找到索引的头和尾，将整个表达式当成一个常数对待
• count()的优化
  • 统计某一列值的数量（不统计null）
  • 统计结果集的行数，count（*）
  • myisam的神话：只有不加任何where条件的count（）才非常快，如果知道某一列不能为null，count（列值）会优化成count（）
  • 简单优化：适用于myisam表的这个count（*）特别快的特性
    • select count（） from city where id>5;优化后：select(select count() from city )-count(* ) from city where id<=5;
    • 查询某列不同值 的数量：select count(color=‘blue’ or null),count(color=‘red’ or null) from items;
• 优化关联查询
  • 保证on连接的列上有索引，一般来说只需要在关键书序的第二个表的相应列上创建索引。如A和B在列C上关联，如果优化器关联顺序是B，A，则在A的列C上创建索引即可。
  • 避免join太多表，join会产生临时表，建议不超过5个。
• 优化子查询
  • 尽量不适用子查询，尤其是in+子查询。
    • 子查询的结果集无法使用索引，通常子查询会产生临时表，不管是内存中还是存盘上的都无法使用索引，导致CPU和I/O损耗较高
  • 尽量使用关联查询代替子查询
• 优化group by和distinct
  • 标识列（自增列）作为分组条件，往往别其他列效率更好
  • group by结果集会隐式排序。按照指定按照分组字段排序，如果不关系排序，可以使用order by null。8.0之后就不在支持隐式排序了。
• 优化order by
  • 如果进行关联查询+order by，尽量使用第一个表的列做排序的条件。这样在关联处理第一个的时候就进行文件排序，explain的extra字段会有using filesort
  • 除此之外的情况，mysql都会先把关联结果放在一个临时表中，然后在所有关联结束后在进行文件排序。extra会有using temporay，using filesort。
• 优化limit
  • 尽量使用覆盖索引，使用延迟关联，让mysql扫描尽量少的页面。
  • 把limit转换为已知位置的范围查询
  • 避免使用offset。记录上一次读取数据的位置，直接从该位置扫描。
• 优化union
  • Mysql总是创建并填充临时表的方式来执行union
  • 通常把where，limit，order by的条件下沉到子句中。
  • 尽量使用union all，除非明确需要消除重复的行。
• 使用用户自定义变量