MySQL-索引优化

MySQL
IT学习笔记
发布日期:   2024-09-11
文章字数:   3k
阅读时长:   11 分
阅读次数:  
  • 索引优化原则
  • SQL 索引优化
  • 索引设计规范
  • SQL 优化
  • 数据类型选择

索引优化原则

  • 联合索引第一个字段用范围不会走索引
  • 强制走索引：最终查找效率不一定比全表扫描高，因为回表效率不高
  • 覆盖索引优化
  • in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描
  • like KK% 一般情况都会走索引
  • 索引下推：可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表
  • MRR (Disk-Sweep Multi-Range Read) 多范围读取
    • 即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作

SQL 索引优化

  • 覆盖索引优化
  • trace工具
    • set session optimizer_trace="enabled=on",end_markers_in_json=on;
    • SELECT * FROM information_schema.OPTIMIZER_TRACE;
  • Order by与Group by：也要按照联合索引的顺序
    • Mysql8以上才有降序排序，否则会产生 Using filesort，因为默认升序
    • 对于排序来说，多个相等条件也是范围查询
    • MySQL支持两种方式的排序filesort和index
      • Using index是指MySQL扫描索引本身完成排序
      • order by满足两种情况会使用Using index
        • order by语句使用索引最左前列
        • 使用where子句与order by子句条件列组合满足索引最左前列
    • 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
    • 如果order by的条件不在索引列上，就会产生Using filesort
    • 能用覆盖索引尽量用覆盖索引
    • group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则
    • group by如果不需要排序的可以加上order by null禁止排序
    • where高于having，能写在where中的限定条件就不要去having限定了
  • filesort文件排序方式：
    • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
    • 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具可以看到sort_mode信息里显示< sort_key, rowid >
    • 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式：小于用单路；大于用双路

索引设计规范

索引设计原则

  • 代码先行，索引后上
  • 联合索引尽量覆盖条件
  • 不要在小基数字段上建立索引
  • 长字符串我们可以采用前缀索引（前20个字符）
  • where与order by冲突时优先where
  • 基于慢sql查询做优化
  • 尽量利用一两个复杂的多字段联合索引，抗下80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证大数据量表的查询尽可能多的都能充分利用索引

索引设计策略

  • 索引列的类型尽量小
  • 利用索引选择性（索引值的离散程度）和前缀索引
  • 只为用于搜索、排序或分组的列创建索引
  • 合理设计多列索引
  • 尽可能设计三星索引（覆盖索引）：索引中所包含了这个查询所需的所有列
  • 主键尽量是很少改变的列
  • 避免创建冗余和重复索引
  • 删除未使用的索引

SQL 优化

  • 分页查询优化
    • 根据自增且连续的主键排序的分页查询（主键空缺，导致结果不一致）
    • 根据非主键字段排序的分页查询
      • 让排序时返回的字段尽可能少：比如用inner join
      • select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
  • Join关联查询优化
    • 嵌套循环连接 Nested-Loop Join(NLJ) 算法：优化器一般会优先选择小表做驱动表，被驱动表的关联字要走索引
      • 一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集
      • 如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ
    • 基于块的嵌套循环连接 Block Nested-Loop Join(BNL) 算法：被驱动表的关联字段没索引（用BNL磁盘扫描次数少很多）
      • 把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比
        • 要是 join_buffer 放不下，就分段放
      • Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法
  • 对于关联sql的优化
    • 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
    • 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间
      • straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序
      • straight_join只适用于inner join，并不适用于left join，right join
        • left join，right join已经代表指定了表的执行顺序
      • 尽可能让优化器去判断
  • 小表定义：两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表
  • in和exsits优化：小表驱动大表
    • in：当B表的数据集小于A表的数据集时，in优于exists
      • select * from A where id in (select id from B)
    • exists：当A表的数据集小于B表的数据集时，exists优于in
      • select * from A where exists (select 1 from B where B.id = A.id)
      • 将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
      • EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
      • EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
      • EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
  • count(*)查询优化
    • 字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)
      • count(字段)统计走二级索引，二级索引存储数据比主键索引少
    • 字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)
    • count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点
    • count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)
    • 对于count(id)，mysql最终选择辅助索引而不是主键聚集索引：因为二级索引相对主键索引存储数据更少
  • 查询mysql自己维护的总行数
    • myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
    • innodb存储引擎的表mysql不会存储表的总记录行数（因为有MVCC机制），查询count需要实时计算
    • 表总行数的估计值：show table status
  • 将总数维护到Redis里
  • 增加数据库计数表

数据类型选择

  • MySQL数据类型选择：尽量用更小的数据类型，字段避免使用NULL
    1. 确定合适的大类型：数字、字符串、时间、二进制
    2. 确定具体的类型：有无符号、取值范围、变长定长等
  • 数值类型
    • 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍
    • 建议使用TINYINT代替ENUM、BITENUM、SET
    • 避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT
      • INT显示宽度：这里的长度并非是存储的最大长度，而是显示的最大长度
        • 在查询结果前填充0时，命令中加上ZEROFILL就可以实现
      • 如果类型是 TINYINT 但是存入值大于255，如500，那么MySQL会自动保存为TINYINT类型的最大值255
    • DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置
    • 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作
    • 整数通常是最佳的数据类型，因为它速度快，并且能使用 AUTO_INCREMENT 自增
  • 日期和时间
    • MySQL能存储的最小时间粒度为秒
    • 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd
    • 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串
    • 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认，MySQL会自动返回记录插入的确切时间
    • TIMESTAMP是UTC时间戳，与时区相关
    • DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么
    • 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题
    • 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐它
  • 字符串
    • 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHA
    • CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合
      • 那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题，影响到计算的准确性和完整性
    • 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联
    • BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关
    • BLOB和TEXT都不能有默认值
文章作者: 钱不寒
文章链接: https://jxch.github.io/2024/09/11/architect/mysql/mysql-suo-yin-you-hua/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 钱不寒 !
MySQL
赏
  目录