MySQL索引

一、索引

1.1什么是索引？

在关系数据库中，索引是一种数据结构，他将数据提前按照一定的规则进行排序和组织， 能够帮助快速定位到数据记录的数据，加快数据库表中数据的查找和访问速度。

像书籍的目录、文件夹、标签 、房号…. 都可以帮助我们快速定位，都可以视为索引。

能实现快速定位数据的一种存储结构，其设计思想是以空间换时间。

1.2索引的种类

在MySQL中索引是在存储引擎层实现的，而不是在服务器层实现的，所以不同存储引擎具有不同的索引类型和实现。常见的索引分类如下：

  • 按数据结构分类：B+tree索引、Hash索引、Full-text索引。
  • 按物理存储分类：聚集索引、非聚集索引。
  • 按字段特性分类：主键索引(PRIMARY KEY)、唯一索引(UNIQUE)、普通索引(INDEX)、全文索引(FULLTEXT)。
  • 按字段个数分类：单列索引、联合索引（也叫复合索引、组合索引）。

1.3常见的索引数据结构和区别

二叉树、红黑树、B树 、B+树
区别：树的高度影响获取数据的性能（每一个树节点都是一次磁盘I/O)

二叉树

特点：每个节点最多有两个子节,大在右，小在左 ，数据随机性情况下树杈越明显。

如果数据是按顺序依次进入：树的高度则会很高（就是一个链表结构）， 此时元素的查找效率就等于链表查询O(n)，数据检索效率将极为低下。

红黑树（平衡二叉树）

虽通过自旋平衡，子节点会自动分叉为2个分支，从而减少树的高度，当数据有序插入时比二叉树数据检索性能更佳. 但是如果 数据量过大，节点个数就越多，树高度也会增高（也就是树的深度越深），增加磁盘I/O次数，影响查询效率。

B树

B树的出现可以解决树高度的问题。之所以是B树，而并不是名称中”xxx二叉树”，就是它不再限制一个父节点中只能有两个子节点，而是允许 M 个子节点（M > 2）。不仅如此，B树的一个节点可以存储多个元素，相比较于前面的那些二叉树数据结构又将整体的树高度降低了。

B+树

B+tree 是在B树基础上的一种优化，其更适合做存储索引结构。在 B+tree 中，非叶子节点上仅存储键值，不存储数据；而所有数据记录均存储在叶子节点上，并且数据是按照顺序排列的。此外在 B+tree 中各个数据页之间是通过双向链表连接的。

B树和B+树的区别，Mysql为什么要选择B+树作为默认索引的数据结构

a. 非叶子节点上可以存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树也就会变得更矮更胖。这样一来我们查找数据进行磁盘I/O的次数就会大大减少，数据查询的效率也会更快。
b. 所有数据记录都有序存储在叶子节点上，就会使得范围查找，排序查找，分组查找以及去重查找变得异常简单。
c. 数据页之间、数据记录之间都是通过链表链接的，有了这个结构的支持就可以方便的在数据查询后进行升序或者降序操作。

1.4什么是聚簇索引与非聚集索引和区别？

按物理存储分类：InnoDB的存储方式是聚集索引，MyISAM的存储方式是非聚集索引。

聚簇索引
1、聚簇索引将数据存储在索引树的叶子节点上。
2、聚簇索引可以减少一次查询，因为查询索引树的同时就能获取到数据。
3、聚簇索引的缺点是，对数据进行修改或删除操作时需要更新索引树，会增加系统的开销。
4、聚簇索引通常用于数据库系统中，主要用于提高查询效率。

非聚簇索引（又称二级索引 / 辅助索引）
1、非聚簇索引不将数据存储在索引树的叶子节点上，而是存储在数据页中。
2、非聚簇索引在查询数据时需要两次查询，一次查询索引树，获取数据页的地址，再通过数据页的地址查询数据（通常情况下来说是的，但如果索引覆盖的话实际上是不用回表的）。
3、非聚簇索引的优点是，对数据进行修改或删除操作时不需要更新索引树，减少了系统的开销。
4、非聚簇索引通常用于数据库系统中，主要用于提高数据更新和删除操作的效率。

1.5二级索引

在MySQL中，创建一张表时会默认为主键创建聚簇索引，B+树将表中所有的数据组织起来，即数据就是索引主键所以在InnoDB里，主键索引也被称为聚簇索引，索引的叶子节点存的是整行数据。而除了聚簇索引以外的所有索引都称为二级索引，二级索引的叶子节点内容是主键的值。

1
2
3
4
5
6
7
8
9
10
11
12
例如创建如下一张表:

CREATE TABLE users(
  id INT NOT NULL,
  name VARCHAR(20) NOT NULL,
  age INT NOT NULL,
  PRIMARY KEY(id)
);
新建一个以age字段的二级索引:

ALTER TABLE users ADD INDEX index_age(age);
MySQL会分别创建主键id的聚簇索引和age的二级索引:

在MySQL中主键索引的叶子节点存的是整行数据，而二级索引叶子节点内容是主键的值.

1.6回表

讲完二级索引，接下来讲一讲如何使用二级索引查找数据。这里假设对age字段创建了一个索引，例如执行下面这条sql 则需要进行回表:

1
SELECT * FROM users WHERE age=35;

由于查询条件是age= ‘35’，所以会走age索引
整个过程大致分为以下几个步骤，从根节点开始，21<35 定位右边存储指针，在索叶子节点找到35的第一条记录，也就是id=9的那条，由于是select ，还要查其它字段，此时就会根据id=9到聚簇索引（主键索引）中查找其它字段数据，这个查找过程前面说了很多次了，这个根据id=4到聚簇索引中查找数据的过程就被称为*回表

1.7覆盖索引

上一节说当执行select * from user where age = 35;这条sql的时候，会先从索引页中查出来age = 35;对应的主键id，之后再回表，到聚簇索引中查询其它字段的值。那么当执行下面这条sql，又会怎样呢？

1
select id from `user` where age = 35;

这次查询字段从select *变成select id，查询条件不变，所以也会走age索引。所以还是跟前面一样了，先从索引页中查出来age = 35;对应的主键id之后，惊讶的发现，sql中需要查询字段的id值已经查到了，那次此时压根就不需要回表了，已经查到id了，还回什么表。而这种需要查询的字段都在索引列中的情况就被称为覆盖索引，索引列覆盖了查询字段的意思。当使用覆盖索引时会减少回表的次数，这样查询速度更快，性能更高。所以，在日常开发中，尽量不要select * ，需要什么查什么，如果出现覆盖索引的情况，查询会快很多。

1.8单列索引

1
ALTER TABLE test.`user` ADD INDEX(`name`);

假设，我们现在对name字段加了一个普通非唯一索引，那么name就是索引列，同时name这个索引也就是单列索引，mysql会根据name字段的值进行排序，这里我假设张三排在李四前面，当索引列的值相同时，就会根据id排序，所以索引实际上已经根据索引列的值排好序了。

1.9联合索引

1
ALTER TABLE `test`.`user` ADD INDEX(`name`, `age`, `id`);

除了单列索引，联合索引其实也是一样的，只不过索引页存的数据就多了一些索引列。先以name排序，name相同时再以age排序，如果再有其它列，依次类推，最后再以id排序。相比于只有name一个字段的索引来说，索引页就多存了一个索引列。

最左前缀原则
顾名思义是最左优先，以最左边的为起点任何连续的索引都能匹配上。如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。当创建(a,b,c)复合索引时，想要索引生效的话，只能使用 a和ab、ac和abc三种组合！

二、优化


关于SQL优化方法，包括5点
1）创建索引减少扫描量；
2）调整索引减少计算量；
3）索引覆盖（减少不必访问的列，避免回表查询）；
4）干预执行计划；
5）SQL改写

示例表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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 '入职时间',
  `remark` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (2, '张三', 18, 'beijing', '2023-06-11 20:51:35', '测试');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (3, '张三', 23, 'shanghai', '2023-06-11 20:51:35', '测试2');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (1, '李四', 20, 'shanghai', '2023-06-11 20:51:35', '测试3');
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (5, '王五', 19, 'beijing', '2023-06-12 14:32:15', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (4, '赵六', 22, 'shenzheng', '2023-06-12 14:33:00', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (6, '赵六', 24, 'beijing', '2023-06-12 14:37:50', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (7, '刘七', 20, 'shanghai', '2023-06-12 14:38:27', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (8, '刘七', 22, 'beijing', '2023-06-12 14:38:41', NULL);
INSERT INTO `test`.`employees`(`id`, `name`, `age`, `position`, `hire_time`, `remark`) VALUES (9, '王九', 9, 'shanghai ', '2023-06-12 14:40:17', NULL);

2.1全值匹配

MySQL全值匹配是指在使用复合索引时，查询条件要包含索引的所有列，才能最大程度地利用索引。

1
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';

2.2最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

1
2
3
4
5
EXPLAIN SELECT * FROM employees WHERE name= '张三' and age = 18 AND position = 'beijing' ; 
-- 带头大哥不能死
EXPLAIN SELECT * FROM employees WHERE age = 18 AND position = 'beijing';
-- 中间兄弟不能断
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND position = 'beijing';

2.3不在索引列操作

不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

1
2
EXPLAIN SELECT * FROM employees WHERE name = '张三';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = '张三';

2.4存储引擎不能使用索引中范围条件右边的列

范围查询会使后面字段无序，造成部分索引失效

1
2
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';
EXPLAIN SELECT * FROM employees WHERE name= '张三' AND age > 18 AND position ='beijing';

2.5使用覆盖索引

尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句

1
EXPLAIN SELECT name,age FROM employees WHERE name= '张三' AND age = 18 AND position ='beijing';

2.6不等空值还有or，索引失效要少用

mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描< 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

1
EXPLAIN SELECT * FROM employees WHERE name != '张三';

is null,is not null 一般情况下也无法使用索引

1
EXPLAIN SELECT * FROM employees WHERE name is null

少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引，详见范围查询优化

1
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

2.7Like百分写最右

1
EXPLAIN SELECT * FROM employees WHERE name like '张%'

2.8字符串不加单引号索引失效

1
2
EXPLAIN SELECT * FROM employees WHERE name = '1000'; 
EXPLAIN SELECT * FROM employees WHERE name = 1000;

三、实践

3.1准备工作

3.1.1创建student表
1
2
3
4
5
6
7
8
9
DROP TABLE IF EXISTS student;
CREATE TABLE student (
  id int(10) NOT NULL AUTO_INCREMENT COMMENT '序号',
  student_id INT NOT NULL COMMENT '学号',
  name varchar(20) COMMENT '姓名',
  department varchar(20) COMMENT '院系',
  remarks varchar(400) COMMENT '备注',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.1.2创建score表
1
2
3
4
5
6
7
8
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (
   id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '序号',
   student_id INT NOT NULL COMMENT '学号',
   course_name VARCHAR(50) NOT NULL COMMENT '课程名称',
   score INT NOT NULL COMMENT '分数',
   remarks varchar(400) COMMENT '备注'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
3.1.3添加索引
1
ALTER TABLE student ADD index idx_name_department (name, department);
3.1.4插入数据
1
2
3
4
5
6
7
8
9
10
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('刘零',1,'美术','备注0');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('郑一',2,'土木','备注1');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('吴二',3,'数学','备注2');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('张三',4,'中文','备注3');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('李四',5,'英语','备注4');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('王五',6,'美术','备注5');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('钱六',7,'土木','备注6');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('孙七',8,'数学','备注7');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('赵八',9,'英语','备注8');
INSERT INTO `student` (`name`,`student_id`,`department`,`remarks`) values ('周九',10,'数学','备注9');

3.2sql改写

3.2.1避免使用select *

select * 走全表扫描，没有用到任何索引，查询效率非常低；查询列都是索引列那么这些列被称为覆盖索引。这种情况下查询的相关字段都能走索引，索引查询的效率相对较高。

1
2
EXPLAIN select * from student where name like 'mock_name%';--不走索引
EXPLAIN select name, department from student where name like 'mock_name%';--走索引
3.2.2小表驱动大表

小表驱动大表就是指用数据量较小、索引比较完备的表，然后使用其索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。比如说student表有30条数据，scores表有80w条数据。

小表驱动大表

1
2
EXPLAIN
select * from student left join scores on student.id = scores.student_id;

大表驱动小表

1
2
EXPLAIN
select * from scores left join student on student.id = scores.student_id;

Join Buffer（连接缓冲区）是优化器用于处理连接查询操作时的临时缓冲区。简单来说当我们需要比较两个或多个表的数据进行Join操作时，Join Buffer可以帮助MySQL临时存储结果，以减少磁盘读取和CPU负担，提高查询效率。需要注意的是每个join都有一个单独的缓冲区。
Block nested-loop join（BNL算法）会将驱动表数据加载到join buffer里面，然后再批量与非驱动表进行匹配；如果驱动表数据量较大，join buffer无法一次性装载驱动表的结果集，将会分阶段与被驱动表进行批量数据匹配，会增加被驱动表的扫描次数，从而降低查询效率。所以开发中要遵守小表驱动大表的原则。

3.2.3用连接查询代替子查询

mysql需要在两张表以上获取数据的方式有两种：第一种通过连表查询获取，第二种通过子查询获取。模拟一个真实场景，同样student表有30条数据，scores表有80w条数据，我们想查看学号小于15的学员各科分数信息：

1
2
3
4
5
6
7
8
9
ALTER TABLE scores ADD index idx_student_id (student_id);

EXPLAIN
SELECT
	(SELECT student.NAME FROM student WHERE student.id = scores.student_id),
	scores.course_name,
	scores.score
FROM
	scores;

因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。因此，使用连接查询可以减少数据库查询的次数，提高查询的效率。
连接查询可以更好地利用数据库索引，提高查询的性能。子查询通常会使用临时表或内存表，而连接查询可以直接利用表上的索引。这意味着连接查询可以更快地访问表中的数据，减少查询的资源消耗。对于大型数据集，使用连接查询通常比使用子查询更高效。子查询通常需要扫描整个表，而连接查询可以利用索引加速读取操作。

1
2
3
4
5
6
7
EXPLAIN
SELECT
	student.NAME,
	scores.course_name,
	scores.score
FROM
	student inner JOIN scores ON student.id = scores.student_id;

使用连接查询可以更快地执行查询操作，减少数据库的负载，提高查询的性能和效率

3.2.4提升group by效率

创建索引：如果你使用group by的列没有索引，那么查询可能会变得很慢。因此，可以创建一个或多个适当的索引来加速查询。

1
select remarks from scores group by remarks;
3.2.5批量操作

批量插入或批量删除数据，比如说现在需要将1w+数据插入到数据库，大家是一条一条处理还是批量操作呢？建议是批量操作，逐个处理会频繁的与数据库交互，损耗性能。提供批量插入数据的方法

1
insert into order(id,code,user_id)  values(123,'001',100),(124,'002',100),(125,'003',101);
3.2.6使用limit

提高查询效率：一个查询返回成千上万的数据行，不仅占用了大量的系统资源，也会占用更多的网络带宽，影响查询效率。使用LIMIT可以限制返回的数据行数，减轻了系统负担，提高了查询效率。
避免过度提取数据：对于大型数据库系统，从数据库中提取大量的数据可能会导致系统崩溃。使用LIMIT可以限制提取的数据量，避免过度提取数据，保护系统不受影响。
优化分页查询：分页查询需要查询所有的数据才能进行分页处理，这会浪费大量的系统资源和时间。使用LIMIT优化分页查询可以只查询需要的数据行，缩短查询时间，减少资源的浪费。
简化查询结果：有时我们只需要一小部分数据来得出决策，而不是整个数据集。使用LIMIT可以使结果集更加精简和易于阅读和理解。限制行数非常有用，因为它可以提高查询性能、减少处理需要的时间，并且只返回我们关心的列。

3.2.7用union all代替union

union all：获取所有数据但是数据不去重，包含重复数据；
union：获取所有数据且数据去重，不包含重复数据；

1
2
3
select id,name,department from student
union all
select id,student_id,score from scores

那么union all与union如果当然它业务数据容许出现重复的记录，我们更推荐使用union all，因为union去重数据需要遍历、排序和比较，它更耗时，更消耗cpu资源，但是数据结果最完整。

3.2.8join表不宜过多

查询效率下降：多表JOIN查询数据对比时间边长
系统负载增加：JOIN操作需要进行大量的计算，因此会导致系统负载增加。
维护难度加大：在一个连接了多个表的查询中，如果需要修改其中一个表的结构或内容，就可能会需要同时修改其他表的结构或内容。

总结

SQL优化是提高数据库性能的重要方法，在实际开发中我们的SQL要尽量遵守以下几点原则，避免留下技术债：
1、减少数据扫描
2、返回更少数据
3、减少交互次数
4、减少服务器CPU及内存开销

MySQL索引
https://xiong-hai.github.io/2023/08/24/MySQL索引/
作者
Xiong-Hai
发布于
2023年8月24日
许可协议