Mysql 基础知识 查询优化

如何查询优化

1. 优化数据访问，不返回不必要的数据，只返回必要的列（SELECT id, name）和必要的行数（LIMIT number）
2. 查询尽量覆盖索引，避免回表的发生
3. 优化数据存储，较大的字段单独存储在一个表中
4. 将复杂的查询拆分成多个简单的查询，因为Mysql的链接时比较轻量级的
5. 最好使用自增ID代替OFFSET逻辑查询
6. 比较复杂的查询语句最好先使用 EXPLAIN 进行分析一遍
7. 如果多个表是完全无关系的话，将记录和列名最少的表，写在最后，然后依次类推。也就是说：选择记录条数最少的表放在最后。
8. 如果多个表是有关系的话，将引用最多的表，放在最后，然后依次类推。也就是说：被其他表所引用的表放在最后。

SQL语句执行流程

1. 建立连接，权限验证
2. 查询缓存，如果命中则直接返回
3. 分析器：对 SQL 语句进行分析
4. 优化器：对 SQL 查询的逻辑进行优化
   1. 关联表（join）的顺序可能会变
   2. outer join可能会变成内连接
   3. 优化条件表达式， 例如 5=5 AND a>5被简化成a>5
   4. 优化MAX\MIN， 如果是MAX(索引），那么直接拿B+树的第一条或者最后一条即可。
   5. 当发现某个查询或者表达式的结果是可以提前计算出来的时候，就会优化成常数
   6. 索引覆盖，如果只要返回索引列，就不会走到最底层去。
   7. 子查询优化
   8. 提前终止查询（例如LIMIT）
   9. 等值传播： join中可能把左表的where 拿给右表一起用
   10. IN(1,2,3,4,5,6)这个条件， 并不是简单遍历判断， 会先排序，然后用二分去判断是否存在。
5. 执行器：执行查询语句查询结果

范式和反范式

范式和反范式各有利弊，需要根据实际情况权衡。

范式化的目标是尽力减少冗余列，节省空间。

• 范式化的优点是：

  • 减少冗余列，要写的数据就少，写操作的性能提高；
  • 检索列数据时，DISTINCT 或 GROUP BY 操作减少。

• 范式化的缺点是：增加关联查询。

反范式化的目标是适当增加冗余列，以避免关联查询。

反范式化的缺点是：

• 冗余列增多，空间变大，写操作性能下降；
• 检索列数据时，DISTINCT 或 GROUP BY 操作变多；