索引优化实战

发表于 更新于 分类于 ， 本文字数： 1.9k 阅读时长 ≈ 6 分钟

索引失效

数据量太大的时候不如走全表扫描，不用回表

类型格式不同导致索引失效

如 varchar 类型，查询条件未加单引号，会导致索引失效

in 和 or 在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

索引下推

如使用 name like "dd%" and age = 3 时：

  • 5.6 之前会先查询 name 的二级索引获取到 主键 id，再去聚簇索引去筛选 age=3 获取数据
  • 5.6 之后会查询 name 的二级索引同时筛选 age（只会筛选联合索引的那几个字段）再获取到 主键 id，再去聚簇索引获取数据

like 大多数情况会走索引下推

MySQL 如何选择是否走索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
set session optimizer_trace = 'enabled=on',end_markers_in_json = on;
-- 开启 trace，影响性能，只能临时开启使用分析

-- 核心字段
"rows_estimation"
:[-- 预估表的访问成本
"table_scan":{ 				-- 全表扫描情况
	"rows":10000, 			-- 扫描行数
	"cost":2000					-- 查询成本
}]
"potential_range_indexes":[-- 查询可能使用的索引
"index":"PRIMARY" -- 主键索引
"index":"idx_name_age_position" -- 辅助索引  
]

常见 sql 深入优化

范围查询

1
2
3
4
5
select *
from dd
where name > ""
  and age > ""
  and; -- 此时 age 无法使用范围查询，因为 name 范围查询后数据为无序的了

order by 与 group by 优化

where 中联合索引都出现且为常量，order by 就会走索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- 联合索引 (name,age,idcard)
select *
from dd
where name = ""
  and idcard = ""
order by age;-- Extra 中为 Using index 使用了索引
select *
from dd
where name = ""
order by idcard; -- Extra 中为 Using filesort 使用了外部排序，因为跳过了 age，所以未使用索引
select *
from dd
where name = ""
order by age, idcard; -- 走索引
select *
from dd
where name = ""
order by idcard, age; -- 错序的，不走索引
select *
from dd
where name = ""
  and age = ""
order by idcard, age; -- age 定义了，走索引
select *
from dd
where name in ("", "")
order by age, idcard; -- 两个 name 的结果集放入临时表是无序的，不能走索引
select *
from dd
where name > ""
order by name; -- 可能不走索引, 如果数据量太大了的情况下就不会走索引
select name, age, idcard
from dd
where name > ""
order by name;
-- 走索引，因为不用回表，直接走索引
-- group by 和 order by 类似，group by 底层走的 order by，如果不排序，则使用 order by null

Using filesort

区别

  • using index 使用的二级索引，只加载部分数据到排序内存 sort_buffer
  • using filesort 使用的聚簇索引，加载所有数据到排序内存 sort_buffer(** 聚簇索引有表所有数据 **)

filesort 排序方式

  • 单路排序，把所有查询结果放入到内存直接通过排序字段排序
  • 双路排序，只把 id 和排序字段放入进入排序
1
2
3
4
5
6
7
-- trace
"filesort_summary"
:{
"sort_mode":"<sort_key,additional_fields>" -- 单路排序
"sort_mode":"<sort_key,packed_additional_fields>" -- 单路排序
"sort_mode":"<sort_key,rowid>" -- 双路排序
}

索引设计原则

  • 代码先行，索引后上，主体业务开发完，将所有 sql 查看来评估建立索引
  • 联合索引尽量覆盖条件，大部分情况 mysql 只会选择一个索引
  • 不要在小基数字段上建立索引，比如性别
  • 长字符采用前缀索引，列如 name(255) 时，创建索引 KEY index(name(20),age,idcard)
  • where 与 order by 冲突优先 where
  • 基于慢 sql 查询优化，默认执行超过 10 秒即记录到慢查询日志
  • 读多写少场景可以多设计几个索引，读多写多的情况一两个复杂索引就可以了，因为写入需要维护索引树

实战案例

因为用了两个范围查询，所以可以设计一个七天内登陆的标记字段

image-20230314175531376

分页查询优化

1
2
select *
from dd limit 100000,10;

实际查询的是 1-100010 条记录，再舍弃前 10w 条记录

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

1
2
3
select *
from dd
where id > 100000 limit 10;

连续且自增 id 才能这样使用，中间不能丢数据

根据非主键字段分页排序

1
2
3
select *
from dd
order by name limit 100000,10;

数据量过大，鉴于非主键索引需要回表，所以 mysql 不会走索引，而是全表扫描，所以排序也走的 filesort 文件排序

1
2
3
select *
from dd e
         inner join (select id from dd order by name limit 100000,10) ed on e.id = ed.id;

使用了索引，且走索引排序

关联表优化

aa 表和 bb 表一样，主键索引 id，非主键索引 cid，且 aa 表 1w 条数据，bb 表 100 条

嵌套循环连接 NLJ

inner 内连接，主键索引条件，走的 ** 磁盘扫描 **

1
2
3
select *
from aa inner bb
on aa.id = bb.id

匹配过程：

  • bb 全表扫描所有数据
  • aa 是主键索引扫描，用的 bb 的 id 去查询 aa 的 id，

aa 只查询了 100 次，加上 bb 的一百次，即一共查询 200 次

基于块的嵌套循环连接 BNL

inner 内联连接，无索引，走的 ** 内存对比 **

1
2
3
select *
from aa
         inner join bb on aa.cid == bb.cid

匹配过程：

  • bb 数据放入 join_buff 中，如果 bb 数据过大，join_buff 会分段放入
  • 将 aa 每一行取出与 join_buff 数据对比
  • 返回满足 join 条件数据

此时 ** 磁盘扫描 ** 了 bb 表 100 次，aa 的扫描次数为 1w=1w 次，即一共 1w 零 1 百次

但是 join_buff 会在 ** 内存 ** 做 100*1w=100w 次对比，如果没有索引走的 NLJ，则需要 ** 磁盘扫描对比 **，即扫描 100w 零 100 次

sql 优化

  • 小表驱动大表
  • 尽量在关联字段添加索引
  • straight_join 同 inner join，但会指定让前表作为驱动表，inner join 是 mysql 自己判断谁是小表

小表定义

参与关联的数据更少才是小表

in 和 exsites 优化

原则还是小表驱动大表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 当 b 查出来数据集小于 a 时，in>exists
select *
from a
where id in (selcet id from b);
-- 等价于
for(select id from b){
select *
from a
where a.id = b.id }

-- 当 a 小于 b 时，exists>in
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 }

exists 最好还是替换成 join

count(*) 优化

1
2
3
4
count(1)
、count(id)
、count(name)
、count(*)
  • 字段有索引，count(*)=count(1)>count(name)>count(id)，二级索引比主键索引数据少，同时 count(1) 会计算二级索引更快，会走二级索引，同时不会取
    name，直接计数
  • 字段无索引，count(*)=count(1)>count(id)>count(name)，同上，走主键索引

count(*) 是专门做了优化，不取值直接按行累加

常用优化方法

mysql 自己维护行数

myisam 会将总行数存入磁盘

innodb 因为有 mvcc 机制，每次事务都会实时查询

show table status
将总数维护到 Redis 里
添加数据库计数表

阿里 MySQL 规范

强制

  • 业务上有唯一特性的字段，即使是组合字段，也必须创建唯一索引
  • 超过三表以上禁止 join，尽量在 java 执行
  • varchar 字段上建立索引，必须制定索引长度，没必要全建索引
  • like 必须走右模糊，左 / 全模糊走搜索引擎
  • 禁止使用存储过程

推荐

  • 超过 500 万行或者单表超过 2G，才推荐分库分表
  • in 操作能避免就避免，in 后面集合元素数量控制在 1000 内

字段类型的选择

  • 大类型，数字、字符串、时间、二进制
  • 具体类型，有无符号、取值范围、变长定长
  • 无符号，可以取消负数范围，扩大正数范围
  • int(11)，为显示宽度，即补零用的