深度解析MySQL数据库索引是什么？有什么用？怎么用？

create table tb_user(
id int primary key auto_increment comment '主键',
name varchar(50) not null comment '用户名',
phone varchar(11) not null comment '手机号',
email varchar(100) comment '邮箱',
profession varchar(11) comment '专业',
age tinyint unsigned comment '年龄',
gender char(1) comment '性别 , 1: 男, 2: 女',
status char(1) comment '状态',
createtime datetime comment '创建时间'
) comment '系统用户表';
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');

A. name 字段为姓名字段，该字段的值可能会重复，为该字段创建索引。

CREATE INDEX idx_user_name ON tb_user(name); 

B. phone 手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。

CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);

C. 为 profession 、 age 、 status 创建联合索引。

CREATE INDEX idx_user_pro_age_sta ON tb_user(profession,age,status);

D. 为 email 建立合适的索引来提升查询效率。

CREATE INDEX idx_email ON tb_user(email);

完成上述的需求之后，我们再查看 tb_user 表的所有的索引数据。

show index from tb_user;

5.SQL性能优化

我们学习索引主要就是为了去优化一些SQL语句，那我们该用什么指标去评判一条SQL是否需要优化呢？主要有以下四点：

• SQL执行频率
• 慢查询日志
• profile详情
• explain

其中前三点用的比较少，我就不展开讲了，我们直接说第四点的explain

explain的语法如下：

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

具体详情在以下文章链接：

MySQL中explain关键字的用法以及每个字段的含义-CSDN博客

6.索引使用

6.1验证索引效率

在讲解索引的使用原则之前，先通过一个简单的案例，来验证一下索引，看看是否能够通过索引来提升 数据查询性能。在演示的时候，我们还是使用之前准备的一张表 tb_sku , 在这张表中准备了 1000w 的记录。

可以看到即使有 1000w 的数据 , 根据 id 进行数据查询 , 性能依然很快，因为主键 id 是有索引的。 那么接 下来，我们再来根据 sn 字段进行查询，执行如下 SQL ：

SELECT * FROM tb_sku WHERE sn = '100000003145001'; 

我们可以看到根据 sn 字段进行查询，查询返回了一条数据，结果耗时 20.78sec ，就是因为 sn 没有索 引，而造成查询效率很低。

那么我们可以针对于 sn 字段，建立一个索引，建立了索引之后，我们再次根据 sn 进行查询，再来看一 下查询耗时情况。

创建索引：

create index idx_sku_sn on tb_sku(sn) ;

然后再次执行相同的 SQL 语句，再次查看 SQL 的耗时。

SELECT * FROM tb_sku WHERE sn = '100000003145001'; 

我们明显会看到， sn 字段建立了索引之后，查询性能大大提升。建立索引前后，查询耗时都不是一个数 量级的

6.2最左前缀法则

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

例如：在 tb_user 表中，有一个联合索引，这个联合索引涉及到三个字段，顺序分别为：profession，age，status。

创建索引时的语法为：

create index idx_user_pro_age_sta on tb_user(profession , age , status);

则有：

explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0';（索引生效）
explain select * from tb_user where profession = '软件工程' and age = 31;（索引生效）
explain select * from tb_user where profession = '软件工程';（索引生效）
explain select * from tb_user where age = 31 and status = '0';（索引不生效）
explain select * from tb_user where status = '0'; （索引不生效）
explain select * from tb_user where profession = '软件工程' and status = '0';（索引部分生效，跳过了age，status索引没生效）
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';（索引生效，SQL会自动排列）

注意 ： 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是

第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

6.3范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';（范围查询右边的status字段是没有走索引的）
explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';（当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引
的。）

所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <。

6.4索引失效情况

1. 不要在索引列上进行运算操作，不然索引会失效

explain select * from tb_user where phone = '17799990015';（索引生效）
explain select * from tb_user where substring(phone,10,2) = '15'; （索引不生效，变成全表扫描）

1. 字符串不加引号也会导致索引生效

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';（索引生效）
explain select * from tb_user where profession = '软件工程' and age = 31 and status = 0;（索引失效，变成全表扫描）

1. 模糊查询，尾部模糊索引生效，头部模糊索引失效

explain select * from tb_user where profession like '软件%';（索引生效）
explain select * from tb_user where profession like '%工程';（索引失效，变成全表扫描）
explain select * from tb_user where profession like '%工%';（索引失效，变成全表扫描）

1. or链接条件

用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

explain select * from tb_user where id = 10 or age = 23;（age无索引，整条语句都不走索引）
explain select * from tb_user where phone = '17799990017' or age = 23;（age无索引，整条语句

1. 数据分布影响

当MySQL中的评估器评估使用索引会比全表扫描更慢，则不适用索引

select * from tb_user where phone >= '17799990005';（出来所有数据，不走索引走全表扫描）
select * from tb_user where phone >= '17799990015';（出来超过一半的数据，不走索引走全表扫描）

6.5 SQL提示

当一个表中字段有多个索引的时候，选择哪种索引进行查询往往是通过MySQL的优化器进行考虑，当我们想人为的选择某个索引时，这时就需要我们去进行SQL提示。

语法如下（都跟在from 表名 之后）：

1）use index ： 建议MySQL使用哪一个索引完成此次查询（仅仅是建议，mysql内部还会再次进行评估）。

explain select * from tb_user use index(idx_user_pro) where profession = '软件工程';

2）ignore index ： 忽略指定的索引。

explain select * from tb_user ignore index(idx_user_pro) where profession = '软件工程';

3）force index ： 强制使用索引。

explain select * from tb_user force index(idx_user_pro) where profession = '软件工程';

6.6 覆盖索引（避免回表查询）

覆盖索引就是查询使用到了索引，改索引返回的字段刚好就是需要的select后面想要的字段

例如：select id,age from tb_users where age = 18;（在age中建立了单列索引）

这时候这个sql语句是比 select * from tb_users  where age = 18;性能要好的，因为第一条SQL在使用了age索引之后，拿到age索引返回的id和age就足够了，不需要回聚焦索引再查一次（回表查询）。

一张表, 有四个字段(id, username, password, status), 由于数据量大, 需要对

以下SQL语句进行优化, 该如何进行才是最优方案:

select id,username,password from tb_user where username = 'itcast';

答案: 针对于 username, password建立联合索引, sql为:

create index idx_user_name_pass on tb_user(username,password);

这样可以避免上述的SQL语句，在查询的过程中，出现回表查询。

6.7前缀索引（解决长文本值构建索引导致索引结果过于庞大问题）

当字段为字符串时，里面的值可能为长文本，如果直接将这些值去构建索引，那索引会变得很大，查询时会浪费大量磁盘IO，影响查询效率。此时就可以截取一部分的值作为索引，去节省大量索引空间，语法为：

create index idx_xxxx on table_name(column(n)) ;

这时候就涉及到需要截多少的问题，可以根据索引的选择性来决定，选择性为不重复的索引值和数据库的记录总数的比值，越接近1效率越高。s

select count(distinct email) / count(*) from tb_user ;（未截取）
select count(distinct substring(email,1,5)) / count(*) from tb_user ;（已截取）

6.8单列索引和联合索引

我们在建立索引的时候尽量选择建立联合索引，因为建立单列索引之后，回表查询的概率会比较高，查询性能会相对较低。

语法如下：

create unique index idx_user_phone_name on tb_user(phone,name);

使用联合索引的情况如下：

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

联合索引的结构图如下：

7.索引设计原则

1）针对于数据量较大，且查询比较频繁的表建立索引。

2). 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3). 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4). 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5). 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6). 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7). 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。