mysql索引优化

学习资料:

慢Sql、索引失效、不会优化？这套MySQL优化课程帮你解决！ | 图灵学院诸葛 | 哔哩哔哩_bilibili

sql优化的方法

索引概述

索引就是方便数据查询的结构

常用索引结构

二叉树

理论上可以二分查找

但未必平衡极端情况下就是链表

红黑树

通过算法实现自旋的平衡二叉树

红黑树的变色与旋转 - 知乎 (zhihu.com)

B树(B-树)

B即Balanced,平衡树的意思,不局限于二叉(红黑树被发明初被称为二叉B树,symmetric binary B-trees)

换而言之就是自平衡多叉树

B树的说明：

B树的阶: 节点的最多子节点个数。比如2-3树的阶是3, 2-3-4树的阶是4 B-树的搜索，从根结点开始，对结点内的关键字(有序)序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点重复，直到所对应的儿子指针为空，或已经是叶子结点关键字集合分布在整颗树中，即叶子节点和非叶子节点都存放数据. 搜索有可能在非叶子结点结束其搜索性能等价于在关键字全集内做一-次

B+树

B树虽然查询效率很高但它对区域的查询(大于10小于100之类的)并不友好(B树的数据在树里,每次判断都需要回溯上去)

B+树是B树的变形,它把数据全都顺序存储到了叶子节点在树中只有指向叶子的主键

阶的判别同B树,MySQL中使用的是16阶B+树

顺便如果一个表没有主键,MySQL会使用不重复的键创建B+树如果没有不允许重复的列则会创建一个rowId来创建

Hash索引

以hash作为索引 key-行数据的形式存储

hash的优势

无论存储空间(O(n)没有额外的空间消耗)还是查询消耗时间(不考虑hash冲突是O(1))

hash冲突

因为hash的key并不保证唯一,所以可能会有一个key对应多行,此时会把多个value以顺序表都存储在key上

hash的劣势

查询性能受hash冲突率影响，性能不稳定
只能通过等值匹配的方式查询，不能范围查询
结构存储上没有顺序，查询时排序无法支持在数据库软件应用场景日益复杂的今天，简单的hash表已经很难满足关系型数据库的基础查询功。

聚集索引

非聚集索引(又叫二级索引) 通常将索引和数据存放在不同的文件索引中携带的是数据的指针(位置指向)

而聚集索引则是索引直接指向数据

在MySQL中只有主键索引是聚集索引其他都是非聚集索引

非聚集(二级)索引实例

建一个简单的表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13


CREATE DATABASE `testBase`;

USE `testBase`;

CREATE TABLE `users` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

insert  into `users`(`id`,`name`,`age`) values (0,'a',123),(1,'b',11),(2,'c',33),(3,'d',1),(4,'e',2),(5,'f',3);

建立一个age的二级索引

1

ALTER TABLE users ADD INDEX index_age(age);

此时MySQL中存在两个索引(下图数据并不对应表的数据,偷懒直接用了课程的图)

一个是主键id的聚集索引它的叶子节点携带整个行的数据

另一个就是刚刚创建的age二级索引它只携带了主键id 其他的数据例如

1

select * from users where age = 11;

它会去查找 age = 11的节点的id 再根据id去主键索引里进行回表查询其他数据

覆盖索引

1

select id from users where age = 11;

不同于上面的查询所有只查询age二级索引表中存在的id属性的话就不必回表查询能提高查询效率就叫覆盖索引

因此可以创建组合索引提高查找效率

特别的：

当一次查询返回多条数据按照原本的思路应当每条数据拿到主键(假设索引指向主键) 之后都需要回表

mysql在5.6版本之后使用了索引下推(index condition pushdown,ICP) 不再一条数据回一次表而是把所有主键一起带到主键索引里统一查询(todo:这个理解可能有误)

单列、组合索引

单列索引和组合索引的区别就是在索引中是否只有一个索引列

比如上面的

1

ALTER TABLE users ADD INDEX index_age(age);

就是一个单列索引可以查询到的数据是 age携带主键id

而下面这个就是age 携带了name和id的组合索引

1

ALTER TABLE users ADD INDEX index_all(`age`,`name`,`id`);

这个索引是根据age建立的因此如果查询条件不包含age则不会触发该索引(最左前缀原则)

当一个age对应多个数据时它会按照其他的属性(先name再id)进行(默认asc)排序在8.0之后可以指定字段desc

mysql 8.0之后允许了逆序索引

mysql 文档中对于索引定义中，ASC 和 DESC 的描述

MySQL < 8.0A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage.

Currently, they are parsed but ignored; index values are always stored in ascending order.

MySQL >= 8.0A key_part specification can end with ASC or DESC to specify whether index values are stored in ascending or descending order. The default is ascending if no order specifier is given. ASC and DESC are not permitted for HASH indexes. As of MySQL 8.0.12, ASC and DESC are not permitted for SPATIAL indexes.

所以，在8.0之前的版本中, DESC 是无效的，索引 (a ASC, b DESC, c DESC) 等于 (a ASC, b ASC, c ASC)，故而无法使用整个联合索引进行排序。

联合索引的优点

减少开销例如建立一个联合索引(a,b,c) 能包括(a),(a,b),(a,c),(a,b,c)四个索引的功能减少了写开销与存储开销
覆盖索引直接查询到数据不必回表这能减少很多io操作是主要的性能提升手段之一
多条件查询效率高相比于单列索引当索引条件指向多个数据时组合索引对更多列进行了排序查询效率也会更高

缺点是:

需要消耗物理存储空间(相比只存主键的单列会多很多)
创建和维护需要消耗时间且该时间会随数据量增加

索引的优缺点

优点:

提高检索效率
降低排序成本

缺点:

创建和维护需要消耗时间且该时间会随数据量增加
需要消耗物理存储空间
降低增删改效率增删需要维护表所有的索引改需要维护涉及到的索引

索引优化的最佳实践

测试用例

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


-- 创建表
CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
-- 声明了组合索引
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
 
-- 插入3条数据
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

使用存储过程插入10万条数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15


-- 使用存储过程 插入10w条数据
drop procedure if exists insert_emp;
delimiter &&
create procedure insert_emp()
begin
declare i int;
set i=1;
while(i<=100000)do
insert into employees(name,age,position) values(CONCAT('zhangsan',i),i,'dev');
set i=i+1;
end while;
end&&
delimiter ;

call insert_emp;

全值匹配

保证查询的所有属性都在联合索引里面完全不需要回表操作这是最优的情况

对于下面四条语句(使用[Explain](# Explain推荐阅读)来判别它的查询方式)

1
2


-- 索引的声明
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE

1
2
3
4
5
6
7
8
9


-- 上面两个都是全值匹配 没有回表
explain select * from `employees` where `name` = 'LiLei' and `age` = 22 and `position`='dev';
explain select * from `employees` where `name` = 'LiLei' and `age `= 22;

-- 匹配了name 但dev进行了回表(因为索引中age先于position排序 age不确定的话,position就是无序的)
explain select * from `employees` where `name` = 'LiLei' and `position`='dev';

-- 它甚至没有符合最左前缀原则,它是对照组
explain select * from `employees` where `position`='dev';

减少函数计算

面对计算不能保证结果总是符合原本值的排序所以不能使用索引

下面两个查询的意思是一样的但前者无法使用索引 idx_name_age_position

1
2
3
4


-- 没有使用索引
EXPLAIN SELECT * FROM `employees` WHERE	LEFT(`name`,2)='张三';
-- 使用了索引
EXPLAIN SELECT * FROM `employees` WHERE	`name` LIKE '张三%';

特别的日期的date函数还是比较常用的可以使用范围来代替date函数

1
2


-- 建立date的索引
ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;

1
2
3
4


-- 没有使用索引
explain select * from `employees` where date(`hire_time`) = '2018-09-30';
-- 使用了索引
explain select * from `employees` where `hire_time` >= '2018-09-30 00:00:00' and `hire_time` < '2018-10-01 00:00:00';

todo:

我尝试了一下为计算结果创建索引，结果失败了
1
ALTER TABLE `employees` ADD INDEX `idx_hire_na` (LEFT(`name`,2)) USING BTREE;
报错：

查询：ALTER TABLE employees ADD INDEX idx_hire_na (left(name,2)) USING BTREE错误代码： 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’left(name,2)) USING BTREE’ at line 1

网上好像也没有看到相关文章,也可能是查找的关键词错了

**特殊的:**string(var)类型应当加上引号否则会进行类型转换

范围查询之后会失效

1
2
3
4
5


-- position回表了
explain select * from employees where `name` = 'LiLei' and `age` < 30 and `position` = 'dev';

-- 完全使用索引
explain select * from employees where `name` = 'LiLei' and `age` = 30 and `position` = 'dev';

范围查询(非等值查询或者or或者涉及null的is null和is not null)的结果是一组数据而不是一个数据

后面的索引只在前面一个值相等时才有序

面对一组数据后面的值都相当于是无序的

关于is null

null可以被看做一个特殊的值直觉是不影响索引的但实际测试它等待结果是Using where;Using index

我不知道这个extra是什么意思网络上的结果也与之不同但我想我们可以认为 is null会导致索引失效

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19


CREATE TABLE `t_union_index` (
 
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
 
  `a` BIGINT(255) DEFAULT NULL,
 
  `b` BIGINT(255) DEFAULT NULL,
 
  `c` BIGINT(255) DEFAULT NULL,
 
  PRIMARY KEY (`id`),
 
  KEY `index_b` (`b`) USING BTREE
 
) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `opt_db`.`t_union_index` (`id`, `a`, `b`, `c`) VALUES (NULL, '1', NULL, '1');
INSERT INTO `opt_db`.`t_union_index` (`id`, `a`, `b`, `c`) VALUES (NULL, '1', NULL, '1');

EXPLAIN SELECT id FROM t_union_index WHERE b IS NULL;

使用force index (索引名) 可以强制使用索引

todo:

存疑:按照网课的意思应该是加了force index就不需要回表了但不管是他的演示还是我自己的尝试结果都是回表了的

1
2


-- position回表了
explain select * from employees force index (`idx_name_age_position`) where `name` > 'LiLei' and `age` = 30 and `position` = 'dev';

1688315167106

like使用后模糊

比如’三%‘比’%三’要快很多如果不得不使用前模糊可以尝试直接将数据反着存比如张三存成三张取到值之后再翻转

如果要使用’%三%‘或者对’%三’和’三%‘都有需求的话可以正反各存一列(吗?感觉可行但是存储开销很大,而且对于已存在的表随便改数据真的好吗)

或者使用ElasticSearch

其他

Explain推荐阅读

MySQL5.6之后进行了索引下推的优化包括explain也有比较大的改动

(18条消息) MySQL 5.7 优化：Explain 执行计划近万字详解_Roc.Chang的博客-CSDN博客

ElasticSearch(ES)

这个没看先放着

(18条消息) ElasticSearch搜索引擎结合Mysql数据库，查询mysql数据_es搜索引擎结合mysql_幻秋秋秋丘的博客-CSDN博客

go-elasticSearch实战篇，带你学会go开发Es - 掘金 (juejin.cn)

trace工具

MySQL5.6版本后提供了对SQL的跟踪工具trace,通过使用trace可以让我们明白optimizer如何选择执行计划的(此功能默认关闭)

参数详解推荐阅读这篇文章

1

set optimier_trace="enabled=on",end_markers_in_JSON=on;

1
2
3


select * from `employees` where `name` = '张三' AND `age` = 18;
-- 查询之后查看语句分析
select * from `information_schema`.`OPTIMIZER_TRACE`;

1
2


-- 查询结束后关闭 trace
SET optimizer_trace="enabled=off";

得到

  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
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234


{
  "steps": [
    {
	  // 第1阶段 sql格式化
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
			// 语句用了select * 对于 * 它在这个阶段将所有值取出来了 顺便取了别名
        	// 所以不写select * 而是写全值能提高 查询效率(虽然只有一点点)
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where ((`employees`.`name` = '张三') and (`employees`.`age` = 18)) limit 0,1000"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
	// 第2阶段 sql优化
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
		// condition_processing 查询优化器
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((`employees`.`name` = '张三') and (`employees`.`age` = 18))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`employees`.`name` = '张三') and multiple equal(18, `employees`.`age`))"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`employees`",
                "field": "name",
                "equals": "'张三'",
                "null_rejecting": true
              },
              {
                "table": "`employees`",
                "field": "age",
                "equals": "18",
                "null_rejecting": true
              }
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
				// 估算扫描成本
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {	// 全表扫描
                    "rows": 99910,	// 扫描行数
                    "cost": 34970.5	// 查询成本
                  } /* table_scan */,
                  // 查询可用索引
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",	// 主键索引 下面的是手动建的两个索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position",
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_hire_time",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "skip_scan_range": {
                    "potential_skip_scan_indexes": [
                      {
                        "index": "idx_name_age_position",
                        "usable": false,
                        "cause": "query_references_nonkey_column"
                      }
                    ] /* potential_skip_scan_indexes */
                  } /* skip_scan_range */,
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "张三 <= name <= 张三 AND 18 <= age <= 18"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,	// 是否使用覆盖索引
                        "in_memory": 0.00931677,
                        "rows": 1,
                        "cost": 0.61,			// 使用索引的查询成本
                        "chosen": true			// 是否使用该索引 是
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */,
                  "chosen_range_access_summary": {
                    "range_access_plan": {
                      "type": "range_scan",
                      "index": "idx_name_age_position",
                      "rows": 1,
                      "ranges": [
                        "张三 <= name <= 张三 AND 18 <= age <= 18"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 1,
                    "cost_for_plan": 0.61,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                // 选择最佳路径
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_name_age_position",
                      "rows": 1,
                      "cost": 0.35,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "idx_name_age_position"
                      } /* range_details */,
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 1,
                "cost_for_plan": 0.35,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`employees`",
                "original_table_condition": "((`employees`.`age` = 18) and (`employees`.`name` = '张三'))",
                "final_table_condition   ": null
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      // 第3阶段 SQL执行
      "join_explain": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_explain */
    }
  ] /* steps */
}

trace可分析如下语句：

SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE
IF
RETURN
CALL