联合索引优化

  • 在联合索引中，第一个字段就使用范围查找，结果集可能较大回表效率不高就不会走索引，直接全表扫描，使用覆盖索引就会走联合索引
  • 条件in和or在表数据量大的情况下会走索引，表数据量小的情况下就不会走索引
  • like AA% 条件过滤语句 ，会大概过滤出结果集，数据量大就不会走索引，数据量小就会走索引
  • 索引下推：在联合索引中，第一个字段使用范围查找，会将第一个字段查询出的结果集再根据后面字段过滤一下，减少回表次数
    • SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =’manager’
    • 如上sql，使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

  • 对于innodb引擎的表索引下推只能用于二级索引，可以减少回表次数

    filesort文件排序方式


  • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
  • 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
  • MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
  • 如果 字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；

  • 如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模·式。

    索引设计原则

  • 代码先行，索引后上

  • 联合索引尽量覆盖条件
  • 不要在小基数字段上建立索引
  • 长字符串我们可以采用前缀索引
  • where与order by冲突时优先where

  • 基于慢sql查询做优化

    Order by与Group by优化

  • 尽量对order by进行排序的字段上建立索引，如果进行排序的字段没有索引，就会出现use filesort进行，比索引效率较低

  • 尽量使用到Using index。
    • order by语句使用索引最左前列。
    • 使用where子句与order by子句条件列组合满足索引最左前列。

  • 对于group by的优化如果不需要排序的可以加上order by null禁止排序

    trace工具

  • 对于如何选择索引使用，可以用trace工具查看，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭

    分页查询优化

    常用分页查询语句

    :::info select * from employees limit 10000,10;
    :::

  • 执行原理：读取10010行数据，然后舍弃掉前10000行记录，越往后查询数据越多，效率越低

    根据自增且连续的主键排序的分页查询

    :::info select * from employees where id > 90000 limit 5;
    :::

  • 因为主键连续递增，可以使用id进行条件过滤，会走id索引，大部分id都连续不了，这种情况很少用

    根据非主键字段排序的分页查询

    :::info select * from employees ORDER BY name limit 90000,5; :::

  • 这种查询可能会遍历多个索引树查找未被索引字段，成本可能比全表扫描高，就会使用全表扫描

  • 可以优化成先通过name索引查询出id值，再通过主键索引查询数据值，sql如下 :::info select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
    :::

    Join关联查询优化

    mysql的表关联常见有两种算法

  • Nested-Loop Join 算法

  • Block Nested-Loop Join 算法

    嵌套循环连接 Nested-Loop Join(NLJ) 算法

  • 一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

    in和exsits优化

    常见sql

  • 小表驱动大表，就是数据量小的表驱动数据量大的表

  • in：当B表的数据集小于A表的数据集时，in优于exists :::info select from A where id in (select id from B)
    等价于
    for(select id from B){
    select     from A where A.id = B.id
    }
    :::

  • exists：当A表的数据集小于B表的数据集时，exists优于in

  • EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换

  • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比 :::info select from A where exists (select 1 from B where B.id = A.id)
    等价于:
    for(select     from A){
    select * from B where B.id = A.id
    }
    :::

    count(*)查询优化

    常见统计数据量的sql

  • 统计字段值不为null的数据行 :::info EXPLAIN select count(1) from employees;
    EXPLAIN select count(id) from employees;
    EXPLAIN select count(name) from employees;
    EXPLAIN select count(*) from employees;
    :::

  • 字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)

  • 字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)
  • 原因是主键索引存储数据值比非主键索引值要多，索引字段有索引时查询会快一点
  • count(name),将name字段取到内存中，然后扫描使用计数器进行累加
  • count(1),不用将name字段取到内存中，直接扫描索引树进行累加
  • count() 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count()

  • sql执行过程中会计算所有cost成本，选择成本最小，最适合的索引进行查询

    常见优化

  • MyIsam引擎表会维护数据数据量，不需要实时计算

  • InnoDB引擎表因为有MVCC，查询时需要实时计算
  • show table status 可以看到表的大概数据量
  • 将总数维护到Redis里
    • 插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性

  • 增加数据库计数表

    • 插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作

      选择正确的数据值类型

  • 确定合适的大类型：数字、字符串、时间、二进制；

  • 确定具体的类型：有无符号、取值范围、变长定长等。
  • 尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。