开发需要懂得MYSQL－查询优化整理

二楼
135 阅读5分钟

索引

索引定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

索引结构

  1. B+Tree

    1. 一般的二叉树容易出现单排二叉树，导致效率低下
    2. AVL树，红黑树虽然查询效率高，但是树太高了，需要多次IO
    3. B-树虽然树高变小了一些，单数数据存储在每个节点，有时需要跨层访问
    4. B+树数据存在叶子节点，所有数据在同一层，一次IO能加载更多的非叶子节点加快查询效率，叶子节点用链表连接还方便做范围查询
    5. B+Tree第一层第二层加载在内存里，一个page约能存储1000个KEY，所以内存中约存储16k*1000 = 16M

  2. Hash MySQL最常用存储引擎InnoDB和MyISAM都不支持HASH索引,虽然在navicat等工具里创建索引可以选择hash，但其实还是B+Tree

SQL执行顺序

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

explain分析

explain SQL的结果最重要的大概就是type和Extra了

  1. type
    • ALL - 全表扫描
    • index - 其实也是全表扫描，只不过先扫索引，再回表，所以天然有排序，而ALL需要usingfilesort,如果没有排序需求，效率差不多(index + Using Index - 索引覆盖，不用回表)
    • range - 范围索引扫描，相比index,限定了范围，效率肯定更高一些
    • ref - 使用了索引，但是索引不是主键或unique，所以找到结果后，还需要继续小范围扫描
    • ref_eq - 跟ref相比，索引是唯一的，所以找到了就找到了
    • const - 如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。
    • system - 表里只有一条数据，一般不会出现
  2. Extra
    • using index - 覆盖索引不回表
    • using where - Using where只是过滤元组，和是否读取数据文件或索引文件没有关系(据说)
    • using filesort - 文件排序
    • Using temporary - 创建了临时表
    • using index for-group by - 使用松散索引分组
    • using where using index - 使用紧凑索引分组

orderBy优化

当对sql进行order by排序的时候，需要尽可能的使用索引排序，如果无法使用索引排序的话，mysql就会使用文件排序。

文件排序出现的几种情况

  1. order by 字段不是索引字段
  2. order by 字段是索引字段，但是 select 中没有使用覆盖索引，如：select * from staffs order by age asc;
  3. order by 中同时存在 ASC 升序排序和 DESC 降序排序，如：select a, b from staffs order by a desc, b asc;
  4. order by 多个字段排序时，不是按照索引顺序进行 order by，即不是按照最左前缀法则，如：select a, b from staffs order by b asc, a asc;

单/双路排序

  1. 双路排序（又叫回表排序模式）: 先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
  2. 单路排序: 是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序(sort buffer默认1M)

如果查询的列字段大于max_length_for_sort_data变量，(或包含text / blob类型列)则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。

单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。

MYSQL使用BUFFER内部快速排序算法，外部多路归并排序算法

groupBy优化

  1. 在利用索引时，group by可根据索引，即可对数据分组，此时完全不用去访问表的数据值（索引健对应的数据）。这种实现方式就是利用松散索引。
  2. 当group by引用的字段无法构成所建索引的最左前缀索引时，也就是说group by不能利用索引时。如何where语句（如果有的话）弥补了这种差距，比如：group by引用的字段为（c2，c3），而索引为（c1，c2，c3）。此时如果where语句限定了c1=a（某一个值），那么此时mysql的执行过程为先根据where语句进行一次选择，对选出来的结果集，可以利用索引。这种方式，从整体上来说，group by并没有利用索引，但是从过程来说，在选出的结果中利用了索引，这种方式就是紧凑索引。
    这种方式，mysql的执行计划为using where,use index。而松散索引的执行计划为using index for-group by。
  3. 如果mysql如论如何都不能利用索引时，此时mysql将读取所有的数据建立临时表，对文件进行排序，完成分组操作
avatar
文章
阅读
粉丝