彻底理解MySQL索引

it00zyq

已于 2022-02-13 15:22:44 修改

阅读量438

点赞数 3

分类专栏： MySQL专栏文章标签： mysql 索引数据库

于 2021-02-17 21:28:40 首次发布

本文链接：https://blog.csdn.net/weixin_43364551/article/details/113759165

版权

MySQL专栏专栏收录该内容

11 篇文章 6 订阅

订阅专栏

文章目录

一、索引的引入
二、添加测试数据
三、索引相关概念
四、操作索引SQL语句
五、索引优化规则

一、索引的引入

存在一张student表，包含id、name、gender共3个字段。
在这里插入图片描述
在没有创建任何索引的情况下，当我们执行SELECT * FROM student WHERE id = 12;时，会进行全表扫描，即从上往下依次匹配，就这条SQL语句来说，我们需要匹配12次才能找到我们要的记录。
当数据量达到百万级别时，如果查询时也是进行全表扫描，那么，可能需要进行百万次比较才能找到我们要的结果，这种查询效率无疑是我们无法忍受的。而合理地创建索引，就能避免这种低效率的全表扫描，加快查询速度。
以在id字段上创建索引为例，执行CREATE INDEX id_index ON student (id);，此时MySQL除了维护数据之外，还会以id字段为键值，创建一个B+树数据结构（还有其他数据结构，这里以B+树为例）
彻底理解B树和B+树！为何它们常用在数据库中？

在这里插入图片描述
此时，我们再执行SELECT * FROM student WHERE id = 12;时，只需要3次匹配就能找到结果。
简单来说，索引就是以索引字段为键值，创建排好序的数据结构，以此来加快查询速度。

二、添加测试数据

set global log_bin_trust_function_creators=TRUE;
-- 创建测试数据库 tmp
CREATE DATABASE tmp;
USE tmp;

CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ;

#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
);

#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

DELIMITER $$

#创建一个函数，名字 rand_string，可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str， 类型  varchar(100)
#默认给 chars_str 初始值   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'
 DECLARE chars_str VARCHAR(100) DEFAULT
   'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; 
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0; 
 WHILE i < n DO
    # concat 函数 : 连接函数mysql函数
   SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
   SET i = i + 1;
   END WHILE;
  RETURN return_str;
  END $$


 #这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(10+RAND()*500);
RETURN i;
END $$

 #创建一个存储过程， 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
 #autocommit = 0 含义: 不要自动提交
 SET autocommit = 0; #默认不提交sql语句
 REPEAT
 SET i = i + 1;
 #通过前面写的函数随机产生字符串和部门编号，然后加入到emp表
 INSERT INTO emp VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
  UNTIL i = max_num
 END REPEAT;
 #commit整体提交所有sql语句，提高效率
   COMMIT;
 END $$

 #添加8000000数据
CALL insert_emp(100001,8000000)$$

#命令结束符，再重新设置为;
DELIMITER ;

三、索引相关概念

3.1、什么是索引？

索引就是排好序的数据结构。MySQL索引的数据结构有两种，B+树还有Hash表，但是因为Hash表不支持范围查询，模糊查询，Order By语句等，所以一般选用B+树作为索引的数据结构，本文章讨论的大部分内容都是对B+树这种数据结构来说的。

3.2、聚集索引与非聚集索引

聚集索引：也称聚簇索引，索引与数据是不分离的，B+树的叶子节点包含了完整的数据记录。
非聚集索引：也称非聚簇索引，索引与数据是不分离的，B+树的叶子节点存储的是完整的数据记录在磁盘中地址或是目标数据记录的主键。
若非聚集索引的叶子结点存储的是目标数据记录的主键，且建立索引的字段不包括所有想要的字段时（非覆盖索引），则需要回表，也就是通过主键在以主键为索引组织的聚集索引中找到完整的数据记录。

3.3、InnoDB引擎下的索引

数据文件本身就是以主键为索引字段，按B+ Tree组织的索引文件。也就是说，InnoDB的主键索引就是聚集索引（叶结点包含了完整的数据记录）。
除主键索引外，其他索引都为非聚集索引，也称为二级索引，B+树的叶子结点存储的是主键的值。
一个InnoDB表有且只有一个的聚集索引。

InnoDB引擎下，数据文件需要以主键为索引字段组织构成B+树，那么为什么我们在建立InnoDB表时也可以不设置主键呢？
点此查看相关面试题

主键索引为聚集索引，叶子结点存储的是具体的数据：
在这里插入图片描述
非主键索引叶子结点存储的是主键的值，为非聚集索引：

3.4、MyIsam引擎下的索引

在这里插入图片描述

3.5、索引的代价

索引大大加快了查询速度，那么会有什么代价呢？
每建立一个索引，MySQL引擎就需要多维护了一个排好序的数据结构，肯定需要占用磁盘空间的，而且当数据量大时，这个空间还不小，还有就是当数据发生增删改时，需要实时维护这些排好序的数据结构，相应地，增删改操作的效率会降低。在一般的项目中，查询操作占到了90%以上，所以索引的优化还是非常有必要的。
由于每建立一个索引，MySQL就需要多维护一个排好序的数据结构，所以我们应该遵循索引的最左匹配原则，尽量建立同时适用于多种查询的联合索引，减少或不要建立只满足一种查询的单值索引。

3.6、索引的类型

主键索引：当一个字段为主键时，自动创建了一个主键索引
唯一索引：字段信息具有唯一性的可以创建唯一索引，对于唯一索引，查找到第一个满足条件的记录后，就会停止继续检索
普通索引：普通索引在找到满足的条件的第一个记录后，需要继续匹配下一个记录，直到遇到不满足条件的记录才会停止
全文索引：用于在一段文字中查找词语的场景，一般不会使用Mysql的全文索引，而是使用全文搜索框架Elasticsearch等来实现

3.7、索引的最左匹配原则

学生信息表：
在这里插入图片描述
建表SQL：

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `student_no` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学号',
  `student_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  `gender` tinyint(2) DEFAULT NULL COMMENT '性别，1-男，2-女',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

建立联合索引：

ALTER TABLE `student` 
ADD INDEX `idx_no_name_age`(`student_no`, `student_name`, `age`) USING BTREE;

该联合索引的建立是先按student_no字段进行排序，再按student_name字段进行排序，最后按age字段进行排序，所以，当你建立该联合索引后，相当于建立以下两个索引：

ALTER TABLE `student` 
ADD INDEX `idx_no`(`student_no`) USING BTREE;

ALTER TABLE `student` 
ADD INDEX `idx_no_name`(`student_no`, `student_name`) USING BTREE;

这就是最左前缀原则；我们来检查一下：
在这里插入图片描述

explain各个字段的含义：

id: 序列号（这个不重要）
select_type: 查询的类型，有simple、union、primary等
table: 查询语句所用的表
type：扫描类型，好->差：system、const、eq_ref、ref、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL
possible_keys：可能使用的索引，即哪个索引包含查询条件中的字段；
keys：查询时真正使用的索引
key_len：mysql使用的索引长度
ref ref：使用哪列或常数与key一起从表中选择行。
rows ：mysql 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引。
extra： mysql解决查询的详细信息

explain各字段详细说明

四、操作索引SQL语句

4.1、添加索引

# 方式1
CREATE [UNIQUE] INDEX 索引名称 ON 表名(字段1 [,字段2,...]);
# 方式2
ALTER TABLE 表名 ADD [UNIQUE] INDEX 索引名称 (字段1 [,字段2,...]);

# 添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(字段1 [,字段2,...]);

4.2、删除索引

# 方式1
DROP INDEX 索引名称 ON 表名;
# 方式2
ALTER TABLE 表名 DROP INDEX 索引名称;

# 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

4.3、查询索引

SHOW INDEX FROM 表名;
SHOW KEYS FROM 表名;

4.4、修改索引

并没有专门用于修改索引的语句，只能通过删除和新增来实现索引的修改

五、索引优化规则

联合索引的最左匹配原则。
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
存储引擎无法使用索引中范围条件后面的列
尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），非必要情况下不要使用SELECT *语句。
MySQL在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null，is not null无法使用索引
like以通配符开头，MySQL索引会失效，所以最好使用后匹配，如like 'abc%'。
字符串不加单引号可能导致索引失效
少用or或in，用它查询时，MySQL不一定使用索引，MySQL内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引