mysql8_二级索引顺序-优快云博客

本文链接：https://blog.youkuaiyun.com/Markland_l/article/details/125326863

本文详细探讨了MySQL数据库中的索引优化技巧，包括全值匹配、最佳左前缀法则、范围条件的放置、避免函数操作索引列、使用覆盖索引、避免不等于操作、处理NULL值以及LIKE查询的注意事项。此外，还分析了OR关键字的使用、索引合并、连接查询的执行原理，以及分区表和分库分表的优缺点。通过对索引的深入理解和合理利用，可以显著提升数据库查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

复习

索引下推
- icp
mmr

高性能的索引使用策略

不在索引列上做任何操作
- select * from s1 where order_no +1 = 1
  - 这种索引不会生效
  - 本质上语句是select * from s1 where order_no = 0，但是mysql是感知不到的，不会自动解析方程式
- select * from s1 where upper(order_no) = 1
  - 这种对索引字段上加上函数也是不行的
- 等号的左边只能有索引字段，右边怎么写无所谓
尽量全值匹配
- select * from s1 where insert_time = ‘2021-03-22 18:37:46’ and order_status = 0 and expire_time = ‘2021-03-22 18:35:14’
  - 联合索引 u_idx_day_status (insert_time, order_status, expire_time)中有三个字段，尽量三个字段都要用到
  - 假设在where条件中交换字段的位置，没有按联合索引的顺序，仍然是会走索引的，查询优化器会优化的，不会理会你写where的字段顺序，只会关注你有没有这几个字段
最佳左前缀法则
- 假设不满足全值匹配，无法用到联合索引中的全部字段，尽量使用第一个字段
范围条件放最后
- 这个也是针对联合索引说的
- select * from order_exp_cut where insert_time>‘2021-03-22 18:23:42’ and
  
  insert_time<‘2021-03-22 18:35:00’;
  - 对于联合索引 u_idx_day_status，可以用到insert_time列的部分
- select * from order_exp_cut where insert_time>‘2021-03-22 18:23:42’ and
  
  insert_time<‘2021-03-22 18:35:00’ and order_status > -1;
  - 对于联合索引 u_idx_day_status，只能用到insert_time的部分，无法用到order_status 部分，因为在insert_time列扫描范围内的数据，不能保证order_status 列是有序的
  - select * from order_exp_cut where insert_time=‘2021-03-22 18:34:55’ and
    
    order_status=0 and expire_time>‘2021-03-22 18:23:57’ and
    
    expire_time<‘2021-03-22 18:35:00’ ;
    
    这种范围查询，就可以把所有字段的列的数据都用起来，在insert_time=‘2021-03-22 18:34:55’ and order_status=0这段查询条件查询出来的数据中expire_time是有顺序的
    
    explain出来key_len是13
  - select * from order_exp_cut where insert_time=‘2021-03-22 18:34:55’ and
    
    order_status>-1 and expire_time=‘2021-03-22 18:35:14’
    
    explain出来key_len是8
- 通过explain出来的key_len字段就可以看出联合索引中字段的利用情况
覆盖索引尽量用
- 三星索引最重要的那颗星是宽索引星
- 二级索引的大小一定小于聚簇索引，mysql读取是按页读取的，一页是16kb，假设二级索引的大小是8字节，聚簇索引是80字节，在一页二级索引可以存放10倍于聚簇索引的数据，这样在内存中的查找更快
- 可以避免回表，二级索引文件中是先按二级索引列排序，然后再按主键索引列排序，这样通过二级索引找到主键id，然后根据主键id回表，这个id一定是无序的，所以一定是随机io，顺序io远远优于随机io
- select * from order_exp_cut where insert_time=‘2021-03-22 18:34:55’ and
  
  order_status=0 and expire_time>‘2021-03-22 18:23:57’ and
  
  expire_time<‘2021-03-22 18:35:00’ ;
  - explain出来extra是using index condition
- select expire_time,id from order_exp_cut where insert_time=‘2021-03-22 18:34:55’ and
  
  order_status=0 and expire_time>‘2021-03-22 18:23:57’ and
  
  expire_time<‘2021-03-22 18:35:00’ ;
  - explain出来extra是using where, using index
不等于要慎用
- 就相当于是全表扫描，即使是所有字段，mysql最终也可能还是走全表扫描了
Null/Not 有影响
- mysql在处理null字段的逻辑是非常分裂的
  - 建表order_no默认都是non null
    - select * from s1 where order_no is null
      - explain出来extra是impossible
    - select * from s1 where order_no is not null
      - explain出来是全表扫描，没有走order_no的索引，因为要回表，并且是查所有的数据，所以mysql认为还不如直接全表扫描
  - 建表order_no默认都是null
    - select * from s1 where order_no is null
      - explain出来使用了ref类型的索引访问
    - select * from s1 where order_no is not null
      - explain出来是全表扫描，没有走order_no的索引，因为要回表，并且是查所有的数据，所以mysql认为还不如直接全表扫描
  - is not null 容易导致索引失效，is null 则会区分被检索的列是否为 null，如果
    
    是 null 则会走 ref 类型的索引访问，如果不为 null，也是全表扫描。
Like查询要当心
- 不要写出类似"%adb"，这样会导致索引失效
- 如果一定要写这种"%adb"，可以尝试利用联合索引
  - select * from s1 where order_no like ‘%_6S’
  - select order_status,expire_time from s1 where insert_time like '%18:35:09"
    - insert_time是联合索引的最左字段，可以直接读取索引的内容，explain出来type字段是index，extra部分是using where, using index
字符类型加引号
- select * from s1 where order_no =6
  - 因为order_no是字符串，但是等值匹配是个数字，mysql查询优化器会尝试把order_no转换成数字后再比较，所以隐含了对order_no进行了类型转换
- 可以和"不在索引列上做任何操作"归为一类
使用or关键字要注意
- explain SELECT * FROM order_exp WHERE order_no = ‘DD00_6S’ OR order_no= ‘DD00_9S’;
  - 相当于mysql对两个单值范围之内进行扫描
- explain SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;
  - expire_time是索引，order_note没有索引，mysql必须要回表才能检验另一个条件是否满足
- explain SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ OR order_no = ‘DD00_6S’;
  - 两个条件字段都是索引，explain出来type是index_merge，extra是using union，mysql将这一条语句拆分了两条，并使用union合并
  - SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ union SELECT * FROM order_exp WHERE order_no = ‘DD00_6S’;
OR改UNION效率高
- SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;
- 可修改为SELECT * FROM order_exp WHERE expire_time= ‘2021-03-22 18:35:09’ union SELECT * FROM order_exp WHERE order_note = ‘abc’;
使用索引扫描来做排序和分组
- b+树天然就是有序的
ASC、DESC别混用
- 如果一条sql语句既有升序，又有降序，mysql只好查询全表，并在内存中进行文件排序了
尽可能按主键顺序插入行
- 主键id是自增的，尽量不要使用uuid作为主键，还是跟b+树的存储结构相关的，b+树是从小到大排好的，uuid是无序的，插入的时候既是一个随机io，而且插入的位置是不确定的，插在数据的中间部分，就可能会造成页分裂和页合并，同时uuid也是比较长的，一页只有16kb，无形中一条数据的存储量就大，影响存放量，占用空间大
优化Count查询
- 统计某个列的数量，或者统计行数
- count(*)
  - 统计这个表里面所有的行数
- count(order_no)
  - 统计order_no这个列有多少行
- 如果有一个字段允许为null
  - test表中共有4条数据，2条为null数据，2条有值
  - select count(*) from test
    - 4行
  - select count(c1) from test
    - 2行
  - 有null字段时，统计全表和统计列的结果是不一样的
- count是精确统计，要访问大量的行，很难优化，如果要优化，只能主要从架构方面去考虑，在不需要精确统计的时候，能不能使用估计值替代，比如分页后总量，没必要是一个精确值，可以单独利用一张汇总表，或者redis缓存系统缓存这个总数
- null算什么
  - 1.一个不确定的值，null =null和null!=null , 返回都是false
    - where条件中有1中的条件时是一个不确定的值
  - 2.null代表没有，所有的null算一份
  - 3.null值完全无意义，统计时完全不统计
    - 统计count(列)时，会完全忽略null值
  - mysql的底层执行原理中，在统计索引中的不重复数据时，也存在着如何对待null值的问题，专门弄了一个变量innodb_stat_method，也有上述三种值的逻辑，却省时意义又变成了第二种
    - 5.7.22版本修改这个值无效，这个值被写死在了mysql代码里面
- 所以字段里面最好非null，因为它的含义很多
- 最好的开源数据库是bg，mysql的源码写的并不好
优化limit分页
- select * from s1limit 10000,10;
  - mysql会把10010条都取出来，然后把最好10条返回，把前10000条都扔了
  - 如何优化，从sql角度去改，select * from (select id from s1 limit 10000,10) b, s1 a where a.id = b.id
  - 从业务上优化 select * from s1 where id>10000 order by id limiti 10，但是这种写法需要前端配合，把id值传回

分区表

分区表的原理
- 根据一定规则，mysql把一个大表拆分一系列的小表，我们在访问的时候就像在访问一个表一样，这些小表在物理层面上是独立的，完全存在的，这是mysql给你实现的分表，在存储引擎层完成的，sql层和service层都是不知道的
- 在磁盘上都是一个一个独立的文件
- 怎么用
  - create table test (
    
    order_date datetime not null,
    
    )engine = innodb
    
    partition by range(year(order_date))(
    
    partition p_2010 values less than (2010),
    
    partition p_2011 values less than (2011),
    
    partition p_2012 values less than (2012),
    
    partition p_other values less than maxvalue);
- 分区表有没有好处
  - 1.一颗b+树大概三到四层，如果数据量超过2000万，那么高度上到五层、六层或者更高，通过分区表，降低单表数据量，降低树的高度，查询更快
  - 2.物理上是独立的，可以单独把一个表删除
  - 3.物理上是独立的，可以利用磁盘阵列，把一个分区表放在一个单独的磁盘上，充分利用磁盘
- 分区表的限制
  - 分区表的个数不能超过1024个，这在mysql源码里已经写死了
  - 如果分区键中有主键或者唯一索引的列，那么所有主键列和唯一索引
    
    列都必须包含进来。
- sql层和servic层无感知，
分区表的类型
- range分区，分区键是整数，利用year函数转换时间戳
  - 见上述示例
- list分区，分区键是整数
  - 类似于枚举
  - CREATE TABLE h2 (
    
    c1 INT,
    
    c2 INT
    
    PARTITION BY LIST(c1) (
    
    PARTITION p0 VALUES IN (1, 4, 7),
    
    PARTITION p1 VALUES IN (2, 5, 8));
  - 我们看见 range 和 List 都是整数类型分区，其实 range 和
    
    List 也支持非整数分区，但是要结合 COLUMN 分区，支持整形、日期、字符串，
- hash分区
  - CREATE TABLE emp (
    
    id INT NOT NULL,
    
    ename VARCHAR(30),
    
    hired DATE NOT NULL DEFAULT ‘1970-01-01’
    
    separated DATENOT NULL DEFAULT ‘9999-12-31’,
    
    job VARCHAR(30) NOT NULL,
    
    store_id INT NOT NULL)
    
    PARTITION BY HASH (store_id) PARTITIONS 4;
  - 这里我们创建了一个基于 store_id 列 HASH 分区的表，表被分成了 4 个分区，
    
    如果我们插入的记录 store_id=234，则 234 mod 4 = 2，这条记录就会保存到第二
    
    个分区。虽然我们在 HASH（）中直接使用的 store_id 列，但是 MySQL 是允许基
    
    于某列值返回一个整数值的表达式或者 MySQL 中有效的任何函数或者其他表达
    
    式都是可以的
- key分区
  - CREATE TABLE emp (
    
    id INT NOT NULL,
    
    ename VARCHAR(30),
    
    hired DATE NOT NULL DEFAULT '1970-01-01
    
    separated DATENOT NULL DEFAULT ‘9999-12-31’,
    
    job VARCHAR(30) NOT NULL,
    
    store_id INT NOT NULL)
    
    PARTITION BY KEY (job) PARTITIONS 4;
  - 上面创建了一个基于 job 字段进行 Key 分区的表，表被分成了 4 个分区。
    
    KEY ()里只允许出现表中的字段。
- 复合分区/子分区
不建议mysql分区表
- MySQL 分区表用的极少，更多的是自己分库分表。
  
  分库分表除了支持 MySQL 分区表的水平切分以外，还支持垂直切分，把一
  
  个很大的库（表）的数据分到几个库（表）中，每个库（表）的结构都相同，但
  
  他们可能分布在不同的 mysql 实例，甚至不同的物理机器上，以达到降低单库（表）
  
  数据量，提高访问性能的目的。
  - 水平切分和垂直切分
    
    https://blog.youkuaiyun.com/Danny1992/article/details/121406399
- 两者对比起来
  
  1）分区表，分区键设计不太灵活，如果不走分区键，很容易出现全表锁，导致并发降低
  
  2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
  
  3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写
  
  了一个 sql，都不确定 mysql 是怎么操作的，不太可控，不易调整
  
  4）分区表无论怎么分，都是在一台机器上，天然就有性能的上限。，自己做的分库分表，可以无限水平扩展机器，而这种关于分库分表的优化就对应之前调优金字塔里面的架构调优

5.mysql的底层执行原理

单表访问之索引合并

MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况，在这些特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge

索引合并分类

Intersection合并

等值匹配可以使用索引合并
- SELECT * FROM order_exp WHERE order_no = ‘a’ AND expire_time = ‘b’;
  - 把这两个二级索引列对应的主键，取交集，然后再回表查
  - 但是最终是否会使用索引合并，mysql会做成本计算来决定
二级索引列是范围查询时，无法使用索引合并
- SELECT * FROM order_exp WHERE order_no> ‘a’ AND insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’;
  - order_no是范围查询
联合索引缺失字段，无法使用索引合并
- SELECT * FROM order_exp WHERE order_no = ‘a’ AND insert_time = ‘a’;
  - insert_time 是联合索引，但是只有一个字段
主键列可以是范围匹配
- SELECT * FROM order_exp WHERE id > 100 AND insert_time = ‘a’;
  - 二级索引等值匹配取到的主键是有序的，但是范围查询取到的主键是无序的
  - 把一批主键排序后回表，在mysql中称为ROR
交集合并生效的要求是回表时主键id有序，但是满足索引交集合并的条件，也不一定就采用，还要根据成本计算的结果判断，如果回表成本太大也不会用

Union合并

SELECT * FROM order_exp WHERE insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ OR (order_no = ‘a’ AND expire_time = ‘b’);
与上面交集合并的条件是一样的，一是二级索引等值匹配，二是主键列范围匹配

sort-union合并

SELECT * FROM order_exp WHERE order_no< ‘a’ OR expire_time> ‘z’
- order_no< ‘a’ 和 expire_time> 'z’取出来id都是无序的，取出来mysql先排一次序后，再合并
比union合并多了一次排序的过程

联合索引替代Intersection索引合并

SELECT * FROM order_exp WHERE order_no= ‘a’ OR expire_time= ‘z’;
用到索引合并时性能还不能满足时，考虑建立一个联合索引，可以省去索引合并的性能

连接查询

连接的本质

连接的本质就是把各个连接表中的记录都取出来依次匹配的组合加入结果集并返回给用户。
e1
- 有m1和n1字段，共三条数据，1,2,3
e2
- 有m2和n2字段，共三条数据，2,3,4
select * from e1,e2
- 就是3X3，得到9条数据

连接过程简介

连接查询中的过滤条件可以分成两种
SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < ‘d’;
- 涉及单表的条件
  - e1.m1 > 1 和 e2.n2 < ‘d’
- 涉及两表的条件
  - e1.m1 = e2.m2;

驱动表

连接查询中第一个被查到的表
对驱动表的访问只有1次
本质上就是一个单表访问
- SELECT * FROM e1, e2 WHERE e1.m1 > 1 AND e1.m1 = e2.m2 AND e2.n2 < ‘d’;
- 假设先查e1，使用全表查询去查e1.m1，满足条件的有两条，再拿e1表查出来的数据去e2表中查，去查询两次
  - SELECT * FROM e1, e2 WHERE 2 = e2.m2 AND e2.n2 < ‘d’;
  - SELECT * FROM e1, e2 WHERE 3 = e2.m2 AND e2.n2 < ‘d’;