MySQL索引以及优化解决方案_mysql索引优化场景

一、数据库索引

1.1、索引介绍

1、索引介绍

“索引是帮助MySQL高效获取数据的数据结构”

例如字典里的目录，索引的目的就是为了让你能够快速查找数据

2、索引优势

• 提高数据检索的效率，降低数据库的IO成本
• 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3、索引劣势

索引实际上也是一张表，保存了主键和索引的字段，并且指向实体表的记录，所以索引也是需要占用空间的。在索引大大提高查询速度的同时，却会降低表的更新速度，在对表进行数据增删改查的同时，MySQL不仅要更新数据，还要保存索引文件。

1.2、索引数据结构

1、与B+Tree相关的几种二叉树

• 二叉树

每个节点至多只有两颗子树，左子树和右子树，次序不能颠倒。遍历是对树的一种最基本的运算，就是按一定的规则和顺序走遍二叉树的所有结点，使每一个结点都被访问一次。有前序、中序、后序遍历。

• 二叉查找树

在满足二叉树的条件下，左子树的节点值小于根的节点值，右子树的节点值大于根的节点值。

虽然可以排序，但是会出现极端情况，如当依次递增添加数据时，会一直在右边叠加。二叉搜索树甚至会变成顺序查找而不是二分查找。

• 平衡二叉树 AVL树

符合二叉查找树的条件，必须满足任何节点的两个子树的高度之差的绝对值不超过1。平衡二叉树的查询速度很快，但是维护一颗平衡二叉树的代价非常大，通常来说，需要1次或多次左旋和右旋来得到插入、更新和删除后树的平衡性。同时，随着平衡二叉树的数据的增多，高度会越来越高，大概1000条数据就有9 - 10层，那也就是说可能找一个数据需要9 -10次 IO。

• B-tree和B+tree

为了解决平衡二叉树高度过高导致的io问题，在每一个节点上多放些元素，从而降低平衡二叉树的高度，减少磁盘io。

B-tree :

多路搜索树，一颗m阶的B-Tree有这样的特性：每个节点最多有m个孩子，若根节点不是叶子节点，则至少有两个孩子。除了根节点和叶子节点外，其他每个节点至少有Ceil（m/2）个孩子（ceil向上取整）。 B-tree 关键字(索引)和数据(除索引外的其他列数据)是放在一起的，没有存储冗余关键字(索引)，同时通过指针指向孩子节点，关键字左边的孩子节点都比关键字小，关键字右边的孩子节点都比关键字大。

B-tree 通过多路搜索的方式大大的降低了树的高度，大大减少了查找一个数据的磁盘 IO，比如我要查找6这个元素的信息，只需要3次磁盘 IO 就能找到想要的数据。

B+tree（MySQL的索引底层数据结构）：

B+树是B-树的变体，也是一种多路搜索树，其定义基本与 B-tree 相同，存在以下几点不同之处：

非叶子结点的子树指针与关键字个数相同非叶子节点只存储关键字信息(即非叶子节点只存储索引，不存储除索引外的其他字段信息)。所有叶子节点之间都有指针相连，指向下一个叶子结点。

看到 B+tree 对比 B-tree 所有数据都存储在叶子节点中，非叶子结点只存储冗余索引，同时叶子结点之间使用双向循环链表链接

为什么MySQL使用B+树而不是B-树？

进一步降低树的高度，非叶子结点可以存储更多的索引，叶子节点间双向循环链表提高区间访问的性能，方便范围查找数据

2、Hash表

为什么MySQL大部分情况下使用的是B+树而不使用hash表？

因为hash表是对索引的 key 进行一次 Hash 计算就可以定位出数据存储的位置。仅能满足 “=” 和 “IN”，不支持范围查询。同时数据库大数据存储时容易产生Hash冲突，所以通常使用B+tree

3、hash索引和B+树索引的使用范围

• 关系型数据库中，索引大多采用B+树来作为存储结构，而全文搜索引擎的索引则主要采用hash的存储结构
• 等值查询的情况，hash索引有绝对优势，只需要经过一次算法即可找到响应的键值（键值唯一）。
• 对于范围查询，不能用hash索引，hash索引仅满足“=”、“in”查询；
• hash索引无法被用来进行数据的排序操作
• 对于组合索引，hash索引也无法利用

4、 索引数据结构

• Btree

会将（key，value）封装成节点，然后插在二叉树里。key一般是这条数据的值，value是这个值所在的物理地址。

如果没有索引，要查找一条数据，需要一条一条的遍历所有数据；而有了索引后，只需要不断的和根节点的key比较，判断在左节点还是有节点，会比之前那样遍历效率高很多

二叉树数据结构的弊端：当极端情况下，数据递增插入时，会一直向右插入，形成链表，查询效率会降低

• MySQL常用的索引数据结构有BTree索引（Myisam普通索引），B+Tree索引（Innodb普通索引），Hash索引（memory存储索引）等

1.3、使用场景

5、索引使用场景

哪些情况需要创建索引？

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引（where后面的语句）
• 查询中与其他表关联的字段，外键关系建立索引
• 多字段查询下倾向创建组合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

哪些情况不推荐创建索引？

• 表记录太少没必要
• 经常增删改的表，因为建立索引会降低更新表的速度
• where条件里用不到的字段不建立索引

6、索引分类

• 主键索引

表中的列设定为主键后，数据库会自动建立主键索引；

单独创建和删除主键索引语法：

创建： alter table 表名 add primary key(字段)；

删除：alter table 表名 drop primary key;

• 唯一索引

表中的列创建了唯一约束时，数据库会自动建立唯一索引

单独创建和删除唯一索引语法：

创建：alter table 表名 add unique 索引名（字段）;

删除：drop index 索引名 on 表名;

• 单值索引

即一个索引只包含单个列，一个表可以有多个单值索引

建表时可随表一起建立单值索引

单独创建和删除：

创建：alter table 表名 add index 索引名（字段）;

删除：drop index 索引名 on 表名

• 复合索引

即一个索引包含多个列

建表时可随表一起建立复合索引

单独创建和删除复合索引：

创建：create index 索引名 on 表名（字段1，字段2）;

删除：drop index 索引名 on 表名

#随表建立索引，主键索引，唯一索引，单值索引，复合索引
create table customer(
 id int(10),
 customer_no varchar(20),
 customer_name Varchar(20),
 primary key(id),
 unique idx_customer_no(customer_no),
 key idx_customer_name(customer_name),
 key idex_customer_no_name(customer_no,customer_name)
 

)

#创建主键索引
alter table customer add primary key(id);
#删除
alter Table customer drop primary key;

#创建唯一索引
alter table customer add unique idx_customer_no(customer_no);
drop index idx_customer_no on customer;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

测试发现，建立索引和没建立索引，查询速度显著提升。

7、Explain

使用Explain关键字可以模拟优化器执行SQL语句。 explain+SQL语句

重要字段名

• id

select查询的序列号，表示查询中执行select子句或操作表的顺序

id相同时，执行顺序由上向下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，先被执行

id相同和不同都存在时，id相同的可以理解为一组，从上往下顺序执行，所有组中，id值越高优先执行

• select_type

常见的值有

SIMPLE:简单的select查询，查询中不包含子查询或者UNION

PRIMARY：查询中若包含任何复杂的子部分，最外层查询标记为Primary

DERIVED:在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中。

SUBQUERY:在SELECT或WHERE列表中包含了子查询

• type

从最好到最差： system>const>eq_ref>ref>range>index>all 。一般来说，最好保证查询能达到range级别，最好能达到ref

System：表只有一行列表，系统表。这是const类型的特例，平时不会出现

Const：表示通过索引一次就找到了，用于比较primary key 或者 unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref:唯一性索引扫描，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。联表查询

ref:非唯一性索引扫描，返回匹配某个单独值的所有行。本质也是一种索引访问。

range：只检索给定范围的行，使用一个索引来选择行。between,<,>,in 等范围扫描索引。不用扫描全部索引，比all好

index：与all区别是index类型只遍历索引树，比all’快。都是读全表，但是index从索引中读取，all从硬盘读

all：遍历全表

• prossible_keys

显示可能应用在这张表的索引。（理论上）

• key

查询中实际使用的索引，为null则没使用索引

• ref

显示索引的哪一列被使用了，哪些列或常量被用于查找索引列上的值

• rows

rows列显示MySQL认为它执行查询时必须检查的行数，一般越少越好

• extra

一些常见的重要的额外信息

1.4、索引优化

8、查询优化

8.1、索引失效

• 要遵循最佳左前缀法则：如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列
• 不能在索引列上做任何计算、函数等操作，否则会导致索引失效而转向全表扫描。
• 不能使用索引中范围条件右边的列
• 使用不等于时无法使用索引
• is null 可以使用索引，但是is not null 无法使用索引
• like以通配符开头会使索引失效导致全表扫描
• 字符串不加单引号索引会失效
• 使用or连接时索引失效，使用and会自动调整顺序为最左前列

create table students(
	id int primary key AUTO_INCREMENT comment "主键id",
	sname varchar (24)comment "学生姓名",
	age int comment "年龄",
	score int comment "分数",
	time timestamp comment "入学时间"
	);
	
	INSERT INTO students(sname,age,score,time) VALUES ("小明",22,89,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小红",22,89,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小绿",22,89,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小黑",22,89,now());
	
	-- 加复合索引
	ALTER table students add index idx_sname_age_score(sname,age,score);
	
	-- 索引失效情况
EXPLAIN	SELECT * FROM students where sname="小明"AND age = 22 AND score = 89;
-- 索引遵循最左前缀法则，指的是查询从索引的最左前列开始并且不能跳过中间的列。下面的例子，没有从最左列sname开始，所以结果并没有应用上索引，是all，索引失效

-- 不从最左边索引开始，索引失效
EXPLAIN	SELECT * FROM students where  age = 22 AND score = 89;
-- 跳过中间索引，虽然用上了索引，但是key_len低，效率低。使用到了sname，失效score
EXPLAIN	SELECT * FROM students where  sname="小明" AND score = 89;
	
	-- 在索引上加函数处理，索引失效
	EXPLAIN SELECT * from students WHERE LEFT(sname,2) = "小明";
	
	-- 使用索引中范围条件右边的列导致索引失效，age后面的索引才会失效
	EXPLAIN SELECT * from students WHERE sname="小明" AND age >20;
	
	-- 不等于，索引失效，导致全表扫描
	EXPLAIN SELECT * FROM students WHERE sname !="小明";
	-- is not null 索引失效 
	explain select * from students where sname is null;
	explain select * from students where sname is not null;
	
	-- like以通配符开头,导致索引失效，全盘扫描。但是如果是“明%”这种通配符在后面的是可以使用索引的
	explain select * from students where sname like "%明";
	
	-- 字符串不加单引号会导致索引失效
	explain select * from students where sname = 123;
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

建议：

• 对于单值索引，尽量选择针对当前查询字段过滤性（检索频率跟高）更好的索引；
• 对于组合索引，当前where查询中过滤性更好的字段在索引字段顺序中位置越靠前越好
• 对于组合索引，尽量选择能够包含在当前查询中 where字句中更多字段的索引；
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

8.2、索引优化

排序优化

• 尽量避免使用 Using FileSort方式排序
• order by 语句能使用索引最左前缀或使用where子句与order by 字句条件组合满足索引最左前列
• where子句中如果出现索引范围查询会导致 order by 索引失效。对于排序来说，多个相等条件也是范围查询
• 如果where使用索引的最左前缀定义为常量，则order by能使用索引

单表查询优化

-- 单表查询优化
CREATE TABLE IF NOT EXISTS article(
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	author_id INT(10) NOT NULL ,
	category_id INT (10) NOT NULL,
	views INT(10) NOT NULL,
	comments INT(10) NOT NULL,
	title VARBINARY(255)NOT NULL,
	content TEXT NOT NULL
	);
	
	INSERT INTO article (author_id,category_id,views,comments,title,content) VALUES 
	(1,1,1,1,'1','1'),
	(2,2,2,2,'2','2'),
	(1,1,3,3,'3','3');
	
	
	#1.查询category_id 为1的，且comments大于1 的情况下，views最多的id和author_id的信息
	explain select id,author_id
	from article
	where category_id = 1 and comments >1 ORDER BY views desc limit 1;
	
	#2.优化--建立索引
	alter table article add index idx_ccv(category_id,comments,views);
	#3.再次测试,因为comments是范围查询，所以order by后面的views索引失效，排序还是用的using filesort
		explain select id,author_id
	from article
	where category_id = 1 and comments >1 ORDER BY views desc limit 1;
	
	#4.再次优化，重新创建索引，可以把comments不加索引，只给category_id和views复合索引
	drop index idx_ccv on article;
	alter table article add index idx_cv(category_id,views);
	
# 再次测试  .用到了索引，type是ref，索引都生效了。
	explain select id,author_id
	from article
	where category_id = 1 and comments >1 ORDER BY views desc limit 1;
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

关联查询优化

内连接时，mysql会自动把小结果集（数据量小的）选为驱动表，所以大表的字段最好加上索引。左外连接时，左表会全表扫描（要得到左表的每一个数据），所以右边大表字段最好加上索引，同理右外连接也是一样。我们最好保证被驱动表上的字段建立了索引。

	create table if not EXISTS class(
	id int (10) AUTO_INCREMENT,
	card int (10),
	primary key (id)
	);
	
	create table if not EXISTS book(
	bookid int (10) AUTO_INCREMENT,
	card int (10),
	primary key (bookid)
	);
	
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO class(card) VALUES (FLOOR(1+(RAND()*20)));
	
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	INSERT INTO book(card) VALUES (FLOOR(1+(RAND()*20)));
	
	
	# 1.联表查询 
	explain select *
	from class 
	left join book
	on class.card = book.card;
	
	# 优化。左外连接（一般会把数据量小的放在连接表，也就是驱动表，数据量大的是被连接表，可以建立索引优化），左边表会全表扫描无法避免，可以给右表加上索引
	# 建立索引
	alter table book add index idx_card(card);
	
	#再测试。
		explain select *
	from class 
	left join book
	on class.card = book.card;
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

分组优化

create table students(
	id int primary key AUTO_INCREMENT comment "主键id",
	sname varchar (24)comment "学生姓名",
	age int comment "年龄",
	score int comment "分数",
	time timestamp comment "入学时间"
	);
	
	INSERT INTO students(sname,age,score,time) VALUES ("小明",22,100,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小红",23,80,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小绿",24,80,now());
	INSERT INTO students(sname,age,score,time) VALUES ("小黑",23,70,now());
	
	-- 分组优化
	-- 把学生表里所有的名字分组。using filesort
	explain select count(*),sname from students GROUP BY sname;
	-- 优化  using index
	alter table students add index idx_sas(sname,age,score);
		explain select count(*),sname from students GROUP BY sname;
		
		-- 范围查询使score索引失效，还是有usingfilesort
		explain select count(*),sname 
		from students 
		where sname="小明" and age > 22
		GROUP BY score;
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

9、慢查询日志

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过 long_query_time值的SQL，则会被记录到慢查询日志中。可以由它来查看哪些SQL超出了我们最大忍耐时间值。
• 注意：非调优场景下，一般不建议启动改参数，慢查询日志支持将日志记录写入文件，开启慢查询日志会带来一定的性能影响。

	-- 慢查询日志
		-- 查看是否开启
		show variables like '%slow_query_log%';
		-- 开启
		set global slow_query_log = 1;
		-- 设置时间 s
		set global long_query_time = 3;
		-- 查看设置的时间
		show variables like 'long_query_time%';
1
2
3
4
5
6
7
8
9