MySQL索引

索引入门

深入理解MySQL索引：底层数据结构与算法详解

索引基本介绍

• 索引（index）是MySQL中高效获取数据的树结构（有序)，数据库索引允许快速访问数据库表中的特定信息。没有索引，数据库系统必须对表中的每一行数据进行扫描，以找到匹配的行。这种全表扫描在数据量较小时尚可接受，但随着数据量的增加，性能将急剧下降
• 索引虽然可以提高查询效率，但它们也有成本。首先，索引需要额外的存储空间。其次，当对表中的数据进行增加、删除或修改操作时，索引也需要被更新，这会增加额外的写操作开销。因此，设计高效的索引策略需要在查询性能和维护成本之间找到平衡
• 创建索引就像建立图书馆的索引卡片系统，需要额外的空间和资源。在数据库中，这意味着需要更多的存储空间和时间来维护索引。
• 当你在图书馆中添加或移除书籍时，索引卡片也需要更新。同样，在数据库中，当你添加、修改或删除数据时，索引也需要更新，这会增加额外的工作。
• 对于一些小的表或者不常被查询的表，索引可能不会带来太大帮助，有时候甚至可能因为维护索引而降低性能。

索引优缺点：

• 优点：

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

• 缺点：

• 索引占用了数据库的空间 （磁盘便宜)
• 索引提高了查询的效率，降低了更新表（Insert，update，delete）的速度，因为增删改表也需要同时维护索引。 （查询的次数远大于增删改操作的次数)

思考:

1. 为什么索引可以加快数据排序

答:索引本身就是按照特定列的数值或者字符串进行排序的数据结构，如果数据库系统要对某一个列进行排序，如果该列上存在索引，数据库可以直接利用索引的排序顺序，而不需要进行全表扫描或者临时排序操作，从而加快数据的排序速度。这种方式可以在查询中利用索引的排序顺序，而无需对整个数据集进行排序操作，从而提高了排序的效率。

1. 为什么索引可以减少磁盘IO

答:索引可以减少磁盘I/O的主要原因是索引结构的设计。当数据库系统需要查询索引列的数值或字符串时，它可以先在索引中进行查找，而不是直接在数据表中进行搜索。由于索引通常比数据表小很多，因此在索引中进行查找所需的磁盘I/O操作也会更少。这可以减少数据库系统对磁盘的读取操作，从而降低了磁盘I/O的负担，提高了系统的性能。

索引结构选择的合理性：B+Tree, Hash, R-tree, Full-text

MySQL的索引是在存储引擎层中实现的，不同的存储引擎有不同的索引结构：

• B+Tree索引：最常见的索引类型，大部分引擎都支持 B+ 树索引
• Hash索引：底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效，不支持范围查询
• R-tree(空间索引）：是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
• Full-text(全文索引)：是一种通过建立倒排索引,快速匹配文档的方式。

**MySQL默认使用的索引底层数据结构是B+树** 我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

二叉搜索树：

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。

如果主键顺序插入：

• 如果数据是有序的，就会顺序插入，会形成一个链表，查询性能大大降低
• 如果数据量比较大，层级较深，检索速度慢

为了提高查询效率，就需要 减少磁盘IO数 。为了减少磁盘IO的次数，就需要尽量 降低树的高度 ，需要把 原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好

红黑树：

考虑到二叉排序树有这些缺点后，我们可能就会考虑使用二叉树进阶版红黑树，红黑树是一个自由平衡二叉树，解决了顺序插入数据形成单向链表的缺点，最终形成的数据结构也是一颗平衡的二叉树

• 解决二叉树的顺序插入后，树不平衡的问题
• 在大数据量的情况下，层级较深，检索速度较慢

B-Tree 多路平衡查找树：

以一颗最大度数为5的b-tree为例：

• 五个指针： 指针1（key<20），指针2（20<key<30），指针3（30<key<62），指针4（62<key<89），指针5（key>89）
• 四个key：20,30,62,89树的度数指一个结点的子节点个数中间元素向上分裂
• 一个结点可以包含2个以上的子节点，解决了红黑树 层级较深的问题
• B树中，非叶子节点和叶子结点都会存放数据，一页中可以放的指针和数据太少，IO次数变多

**B+树：**以一个最大度数为4的b+树为例：度数为4，key有三个，指针有四个

• 索引部分：仅仅起到索引数据的作用，不存储数据
• 数据存储部分，在其叶子节点中要存储具体的数据

与 B-Tree相比，区别：

• B+树所有的数据都会出现在叶子节点。
• 叶子节点形成一个单向链表。
• 非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的。

**MySQL优化后的B+ Tree：**注意：橙色的是页/块，存储索引和数据

• 蓝色框部分，索引部分，是非叶子结点，仅仅起到索引数据的作用，不存储数据。
• 绿色框部分，数据存储部分，是叶子结点，叶子节点中要存储具体的数据。
• MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能，利于排序。
• 为什么 InnoDB 存储引擎选择使用 B+tree 索引结构?

• 相对于二叉树，层级更少，搜索效率高；
• 对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储 的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
• 相对Hash索引，B+tree支持范围匹配及排序操作；

哈希索引：

采用hash算法 将键key换算成新的hash值，映射到对应槽位上，存储在hash表中。若产生冲突，可以采用拉链法（相同的key往后延申成链表），线性探测法（逐个找哈希表中的空闲位置），二次探测法（以二次函数为步长找哈希表中空闲位置），双重哈希法（对key再计算一次hash值）等解决冲突

特点：

• 只能用于等值查询较(=，in)，不支持范围查询（between，>，< ，…）
• 无法利用索引完成排序操作
• 查询效率高，通常(不存在hash冲突的情况)只需要一次检索就可以了，效率通常要高于B+tree索引

哈希索引不支持范围查询的主要原因是哈希函数的特性以及哈希索引的数据结构。 哈希函数将键映射到哈希表的特定位置，这意味着相同哈希值的键会被映射到相同的位置。因此，对于哈希索引而言，范围查询是非常困难的，因为哈希索引无法保证相邻的键被映射到相邻的位置。在哈希索引中，相邻的键通常会被映射到不同的哈希桶中，因此无法进行范围查询。 另外，哈希索引不会维护键的有序性，这也是导致它无法支持范围查询的原因之一。在哈希索引中，相同哈希值的键会被散列到不同的位置，因此无法按照键的顺序进行范围查询。 综上所述，哈希索引不支持范围查询的主要原因是哈希函数的特性导致相邻键的位置不确定，以及哈希索引无法维护键的有序性。因此，在需要进行范围查询的场景下，通常会选择B+树索引结构来实现。
存储引擎支持：在MySQL中，支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能，hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的

思考题:为什么InnoDB存储引擎选择使用B+tree索引结构?

• 相比二叉树：B+树在进行数据检索的时候层级比较少，效率更高
• 相比B树，B树不管是叶子节点还是非叶子节点都会存储数据，这样就会导致一页中存储的键值减少，指针跟着减少。如果要保存大量数据，只能增加树高，导致性能降低
• 相比哈希索引，B+树支持范围查找和排序操作

索引分类：主键/唯一/常规/全文索引，聚集/二级索引

索引分类

联合索引（创建索引时的字段顺序，注意：最左前缀法则：where后面有最左列，且中间跳过的话 后面的列都失效），前缀索引（根据索引的选择性，选出前缀长度n，create index column_idx on tablename(column(n)); ），单列索引

聚集索引&二级索引(辅助索引，非聚集索引)

在InnoDB存储引擎中，根据索引的存储形式，又分成以下两种:

• 如果存在主键，主键索引默认就是聚集索引
• 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。 （在字段上加了唯一约束的时候，会自动加上该字段的唯一索引)
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引

聚集索引，也叫主键索引，简单来说，mysql一张表的所有数据就是一个主键索引，索引的非叶子节点存储主键key，叶子节点存储具体的一行数据，当我们通过id查询数据时就通过此索引检索，若表没有设置主键，mysql会用一个隐藏字段row_id设置为主键索引。

• **聚集索引的叶子节点下挂的是这一行的数据 。**
• 二级索引的叶子节点下挂的是该字段值对应的主键值。

具体过程如下:

1. 由于是根据name字段进行查询，所以先根据name='Arm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
2. 由于查询返回的数据是*，所以此时，还需要根据主键值10，到聚集索引中查找10对应的记录，最终找到10对应的行row。
3. 最终拿到这一行的数据，直接返回即可。

回表查询： 这种先到二级索引中查找数据，找到主键值，然后再到聚集索引中根据主键值，获取数据的方式，就称之为回表查询。

思考题(重点)

**以下哪个SQL语句的执行效率会更高，为什么？（id为主键，name字段创建的有索引)**A select * from user where id = 10;B select * from user where name = ‘Arm’;答：**A 语句的执行性能要高于B 语句。**语句A直接访问聚集索引，只需要一次索引扫描，语句B需要先查询name字段的二级索引，从中查找主键。然后再进行聚集索引，也就是回表查询，获取一整行数据。因此语句A的执行效率会更高

InnoDB主键索引的B+Tree高度为多高？

聚集索引的叶子节点存储的是行数据

B+树的节点存储在页中，1页大小是16KB，假如一行的数据大小是1KB，那么一页最多存储16行

InnoDB指针占用6B，主键类型如果是int则占用4B，如果是bigint，则占用8B

• 高度为2:

假设节点存储n个键，那么就有n+1个指针，n8+(n+1)6=161024,算出n大概是1170。也就是说每个节点可以存储1170个键，即每个节点最多可以有1171个指针，可以存储117116=18736条记录

• 高度为3

1171117116=21939856，也就是说，如果树的高度为3，则可以存储 2200w 左右的记录。

索引语法：索引的创建，查看，删除

• 创建索引: create [unique|fulltext] index 索引名称 on 表名(index_col_name…)
• 查看索引: show index from 表名
• 删除索引:drop index 索引名称 on 表名

如果一个索引只关联一个字段，则该索引称为单列索引如果一个索引关联多个字段，则该索引称为 联合索引/组合索引
**案例演示:**先来创建一张表 tb_user，并且查询测试数据。

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(255) 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', '[e-mail]', '软件工程', 23, '1',
                                          '6', '2001-02-02 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('曹操', '17799990001', '[e-mail]', '通讯工程', 33,
                                          '1', '0', '2001-03-05 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('赵云', '17799990002', '[e-mail]', '英语', 34, '1',
                                          '2', '2002-03-02 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('孙悟空', '17799990003', '[e-mail]', '工程造价', 54,
                                          '1', '0', '2001-07-02 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('花木兰', '17799990004', '[e-mail]', '软件工程', 23,
                                          '2', '1', '2001-04-22 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('大乔', '17799990005', '[e-mail]', '舞蹈', 22, '2',
                                          '0', '2001-02-07 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('露娜', '17799990006', '[e-mail]', '应用数学', 24,
                                          '2', '0', '2001-02-08 00:00:00');
 
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('程咬金', '17799990007', '[e-mail]', '化工', 38,
                                          '1', '5', '2001-05-23 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('项羽', '17799990008', '[e-mail]', '金属材料', 43,
                                          '1', '0', '2001-09-18 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('白起', '17799990009', '[e-mail]', '机械工程及其自动
                                          化', 27, '1', '2', '2001-08-16 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('韩信', '17799990010', '[e-mail]', '无机非金属材料工
                                          程', 27, '1', '0', '2001-06-12 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('荆轲', '17799990011', '[e-mail]', '会计', 29, '1',
                                          '0', '2001-05-11 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('兰陵王', '17799990012', '[e-mail]', '工程造价',
                                          44, '1', '1', '2001-04-09 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('狂铁', '17799990013', '[e-mail]', '应用数学', 43,
                                          '1', '2', '2001-04-10 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('貂蝉', '17799990014', '[e-mail]', '软件工程', 40,
                                          '2', '3', '2001-02-12 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('妲己', '17799990015', '[e-mail]', '软件工程', 31,
                                          '2', '0', '2001-01-30 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('芈月', '17799990016', '[e-mail]', '工业经济', 35,
                                          '2', '0', '2000-05-03 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('嬴政', '17799990017', '[e-mail]', '化工', 38, '1',
                                          '1', '2001-08-08 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('狄仁杰', '17799990018', '[e-mail]', '国际贸易',
                                          30, '1', '0', '2007-03-12 00:00:00');
 
 
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('安琪拉', '17799990019', '[e-mail]', '城市规划', 51,
                                          '2', '0', '2001-08-15 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('典韦', '17799990020', '[e-mail]', '城市规划', 52,
                                          '1', '2', '2000-04-12 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('廉颇', '17799990021', '[e-mail]', '土木工程', 19,
                                          '1', '3', '2002-07-18 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('后羿', '17799990022', '[e-mail]', '城市园林', 20,
                                          '1', '0', '2002-03-10 00:00:00');
 INSERT INTO tb_user (name, phone, email, profession, age, gender, status,
                      createtime) VALUES ('姜子牙', '17799990023', '[e-mail]', '工程造价', 29,
                                          '1', '4', '2003-05-26 00:00:00');

数据准备好了之后，接下来，我们就来完成如下需求： A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。通常索引名称是idx_表名_字段名

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);

索引优化

MySQL性能调优是指通过对MySQL数据库系统进行优化，以提高其执行速度、响应时间和资源利用率的过程。MySQL是一种常用的关系型数据库管理系统，因此，针对MySQL的性能调优主要集中在以下几个方面：
1、查询优化：通过分析和优化查询语句，包括使用合适的索引、避免全表扫描、优化JOIN操作等，以提高查询性能。
2、索引优化：合理设计和使用索引，包括选择合适的列作为索引、创建复合索引、删除不必要的索引等，以加快数据检索速度。
3、配置优化：调整MySQL的配置参数，如缓冲区大小、并发连接数、线程池大小等，以适应不同的工作负载和硬件环境。
4、内存管理：合理配置MySQL的内存使用，包括设置合适的缓冲池大小、排序缓冲区大小、临时表空间大小等，以提高内存利用率和减少磁盘IO。
5、存储引擎选择：根据应用需求选择合适的存储引擎，如InnoDB、MyISAM等，并针对不同存储引擎进行相应的优化。
6、数据库设计优化：合理设计数据库表结构、字段类型和关系，以减少数据冗余和提高查询效率。
以上只是MySQL性能调优的一些常见方面，具体的调优策略和方法需要根据具体情况进行分析和优化。通过综合考虑硬件、操作系统、网络和应用程序等因素，可以全面提升MySQL数据库的性能和可伸缩性。

性能分析

执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信 息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
--这里是七个下划线
SHOW GLOBAL STATUS LIKE 'Com_______';

• Com_delete: 删除次数
• Com_insert: 插入次数
• Com_select: 查询次数
• Com_update: 更新次数

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以查询为主，那么就要考虑对数据库的索引进行优化了。
**那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。 那假如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？ 我们可以借助于慢查询日志。 **
接下来，我们就来介绍一下MySQL中的慢查询日志。

慢查询日志

• **慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。 **
• MySQL的慢查询日志默认没有开启，我们可以查看一下系统变量 slow_query_log。

show variables like ‘slow_query_log’;

如果要开启慢查询日志，需要在MySQL的配置文件（/docker/mysql/conf/my.cnf）中配置如下信息：

#开启慢查询日志开关
slow_query_log=1
#设置慢查询日志的时间是2秒，sql语句执行时间超过2秒，就会被认为是慢查询，然后记录慢查询日志
long_query_time=2

配置完毕后，需要通过下面的指令重启mysql进行测试，然后查看慢查询日志文件中记录的信息之前我是用docker安装的MySQL，并且进行数据卷挂载，/docker/mysql/conf进行映射

然后再次查询发现慢查询开启

查看慢查询日志所在目录

本来慢查询文件是在/var/lib/mysql下面的，但是之前我们docker进行数据卷挂载，/docker/mysql/data和这个目录进行挂载，所以我们在这个目录就可以看到慢查询日志文件了

如果有查询语句超过慢查询时间的，那么这个慢查询日志就会记录下来，否则不记录。**(tail -f xxx-slow.log)**

我们准备一张表tb_sku，以及插入1kw条数据

CREATE TABLE `tb_sku` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品id',
  `sn` varchar(100) NOT NULL COMMENT '商品条码',
  `name` varchar(200) NOT NULL COMMENT 'SKU名称',
  `price` int(20) NOT NULL COMMENT '价格（分）',
  `num` int(10) NOT NULL COMMENT '库存数量',
  `alert_num` int(11) DEFAULT NULL COMMENT '库存预警数量',
  `image` varchar(200) DEFAULT NULL COMMENT '商品图片',
  `images` varchar(2000) DEFAULT NULL COMMENT '商品图片列表',
  `weight` int(11) DEFAULT NULL COMMENT '重量（克）',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `category_name` varchar(200) DEFAULT NULL COMMENT '类目名称',
  `brand_name` varchar(100) DEFAULT NULL COMMENT '品牌名称',
  `spec` varchar(200) DEFAULT NULL COMMENT '规格',
  `sale_num` int(11) DEFAULT '0' COMMENT '销量',
  `comment_num` int(11) DEFAULT '0' COMMENT '评论数',
  `status` char(1) DEFAULT '1' COMMENT '商品状态 1-正常，2-下架，3-删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

注意 : 由于1000w的数据量较大 , 如果直接加载1000w , 会非常耗费CPU及内存 ;
已经拆分为5个部分 , 每一个部分为200w数据 , load 5次即可 ;

-- 上传课程资料中的tb_sku*.sql脚本到linux服务器中, 
 /docker/mysql/data/sku_sql
 
 -- 在MySQL服务器的配置文件（my.cnf或my.ini）中，添加以下配置项：
 [mysqld]
 local_infile=1
 
 -- 使用mysql客户端连接mysql
 mysql --local-infile=1 -u root -p
 
 -- 进入到mysql中的，执行如下命令导入数据
 use db2 ;
 
 -- 根据课程资料中提供的建表语句创建数据库表
 
 -- 使用load data命令导入数据
 load data local infile '/var/lib/mysql/sku_sql/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
 load data local infile '/var/lib/mysql/sku_sql/tb_sku2.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
 load data local infile '/var/lib/mysql/sku_sql/tb_sku3.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
 load data local infile '/var/lib/mysql/sku_sql/tb_sku4.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
 load data local infile '/var/lib/mysql/sku_sql/tb_sku5.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n';
 
 -- 不创建索引进行查询
 mysql> select * from tb_sku where name = '华为Meta1000' ;
 1 row in set (17.04 sec)

MySQL导入数据报错：mysql 使用 load data local infile导入数据Loading local data is disabled(Error 3948)和Error 2068_load data local infile 不支持-DN博客可能报错ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option 解决办法

profile详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling 参数，能够看到当前MySQL是否支持profile操作：

SELECT @@have_profiling ; 
 select @@profiling;

SET profiling = 1;

开关已经打开了，接下来，我们所执行的SQL语句，都会被MySQL记录，并记录执行时间消耗到哪儿去了。 我们直接执行如下的SQL语句：

select * from tb_user;
select * from tb_user where id = 1;
select * from tb_user where name = '白起';

执行一系列的业务SQL的操作，然后通过如下指令查看指令的执行耗时：

-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;

查看指定SQL各个阶段的耗时情况 :

explain

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
查看SQL执行计划：使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。语法:

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

数据准备

CREATE TABLE `t_role` (
  `id` varchar(32) NOT NULL,
  `role_name` varchar(255) DEFAULT NULL,
  `role_code` varchar(255) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `t_user` (
  `id` varchar(32) NOT NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `user_role` (
  `id` int(11) NOT NULL auto_increment ,
  `user_id` varchar(32) DEFAULT NULL,
  `role_id` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_ur_user_id` (`user_id`),
  KEY `fk_ur_role_id` (`role_id`),
  CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into `t_user` (`id`, `username`, `password`, `name`) values('1','super','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','superadmin');
insert into `t_user` (`id`, `username`, `password`, `name`) values('2','admin','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','admin');
insert into `t_user` (`id`, `username`, `password`, `name`) values('3','zhangsan','$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui','张三');
insert into `t_user` (`id`, `username`, `password`, `name`) values('4','lisi','$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa','李四');
insert into `t_user` (`id`, `username`, `password`, `name`) values('5','wangwu','$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm','王五');
insert into `t_user` (`id`, `username`, `password`, `name`) values('6','zhaoliu','$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe','赵六');

INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');

INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '10'),(NULL, '2', '9'),(NULL, '2', '8'),(NULL, '3', '5'),(NULL, '4', '7'),(NULL, '5', '5') ;

各字段解释

table

展示这一行的数据是关于哪一张表的

id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。

（1） id 相同：表示加载表的顺序是从上到下。

explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;

（2） id 不同id值越大，优先级越高，越先被执行。

EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = ‘super’));

select_type

表示 SELECT 的类型，常见的取值，如下表所示：

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ； 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为 ： DERIVED
UNION RESULT	从UNION表获取结果的SELECT
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中

（1）UNION和UNION RESULT：

• **UNION：**对于包含UNION或者UNION ALL的查询语句，除了最左边的查询是PRIMARY，其余的查询都是UNION。
• **UNION RESULT：**UNION会对查询结果进行查询去重，MYSQL会使用临时表来完成UNION查询的去重工作，针对这个临时表的查询就是"UNION RESULT"。

UNION去重：

EXPLAIN 
SELECT * FROM t_user WHERE id = '1' 
UNION  
SELECT * FROM t_user WHERE id = '2';

UNION ALL不去重：

EXPLAIN 
SELECT * FROM t_user WHERE id = '1' 
UNION ALL
SELECT * FROM t_user WHERE id = '2';

（2）DERIVED：

MySQL8.0之前：

MySQL8.0之后：查询优化器将子查询优化成了关联查询

type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
system	表只有一行记录(等于系统表)，这是const类型的特例，一般不会出现
const	表示通过索引一次就找到了，const 用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。const于将 “主键” 或 “唯一” 索引的所有部分与常量值进行比较
eq_ref	类似ref，区别在于使用的是唯一索引，主键的关联查询，关联查询出的记录只有一条。
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树， 通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

说明：
结果值从最好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL比较重要的包含：system > const > eq_ref > ref > range > index > ALL SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，最好是 const级别。（阿里巴巴开发手册要求）

• **ALL：**全表扫描。Full Table Scan，将遍历全表以找到匹配的行

EXPLAIN SELECT * FROM t_user;

• **index：**全索引扫描。当使用覆盖索引，但需要扫描全部的索引记录时

EXPLAIN SELECT username FROM t_user;

EXPLAIN SELECT id FROM t_user;

• **range：**只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

EXPLAIN SELECT * FROM t_user WHERE id > ‘2’;

• **ref：**通过普通二级索引列与常量进行等值匹配时

EXPLAIN SELECT * FROM user_role WHERE user_id = ‘1’;

• **eq_ref：**连接查询时通过主键或不允许NULL值的唯一二级索引列进行等值匹配时

EXPLAIN SELECT * FROM t_user u, user_role ur WHERE u.id = ur.user_id;

• **const：**根据主键或者唯一二级索引列与常数进行匹配时

EXPLAIN SELECT * FROM t_user WHERE id = ‘1’;

EXPLAIN SELECT * FROM t_user WHERE username = ‘super’;

• **system：**MyISAM引擎中，当表中只有一条记录时。（这是所有type的值中性能最高的场景）

CREATE TABLE t(i int) Engine=MyISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;

key

• possible_keys表示执行查询时可能用到的索引，一个或多个。 查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
• keys表示实际使用的索引。如果为NULL，则没有使用索引。

EXPLAIN SELECT id FROM t1 WHERE id = 1;

key_len

表示索引使用的字节数，根据这个值可以判断索引的使用情况，检查是否充分利用了索引，针对联合索引值越大越好。如何计算：

1. 先看索引上字段的类型+长度。比如：int=4 ; varchar(20) =20 ; char(20) =20
2. 如果是varchar或者char这种字符串字段，视字符集要乘不同的值，比如utf8要乘 3，如果是utf8mb4要乘4，GBK要乘2
3. varchar这种动态字符串要加2个字节
4. 允许为空的字段要加1个字节

-- 部门表
CREATE TABLE `t_dept` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR(30) DEFAULT NULL,
 `address` VARCHAR(40) DEFAULT NULL,
 `CEO` INT(11),
 PRIMARY KEY (`id`)
);
 
-- 员工表
CREATE TABLE `t_emp` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(20) DEFAULT NULL,
 `age` INT DEFAULT NULL,
 `deptId` INT DEFAULT NULL,
`empno` INT NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_dept_id` (`deptId`)
);

-- 插入数据
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(1,'华山','华山', 2);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(2,'丐帮','洛阳', 4);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(3,'峨眉','峨眉山', 6);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(4,'武当','武当山', 8);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(5,'明教','光明顶', 9);
INSERT INTO t_dept(id,deptName,address, CEO) VALUES(6,'少林','少林寺');

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(1,'风清扬',90,1,100001);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(2,'岳不群',50,1,100002);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(3,'令狐冲',24,1,100003);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(4,'洪七公',70,2,100004);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(5,'乔峰',35,2,100005);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(6,'灭绝师太',70,3,100006);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(7,'周芷若',20,3,100007);

INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(8,'张三丰',100,4,100008);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(9,'张无忌',25,5,100009);
INSERT INTO t_emp(id,NAME,age,deptId,empno) VALUES(10,'韦小宝',18,NULL,100010);

-- 创建索引
CREATE INDEX idx_age_name ON t_emp(age, `name`);
-- 测试1
EXPLAIN SELECT * FROM t_emp WHERE age = 30 AND `name` like 'ab%';
-- 测试2
EXPLAIN SELECT * FROM t_emp WHERE age = 30;

rows

MySQL认为它执行查询时实际从索引树中查找到的行数。值越小越好。

-- 如果是全表扫描，rows的值就是表中数据的估计行数
EXPLAIN SELECT * FROM t_emp WHERE empno = 100001;

-- 如果是使用索引查询，rows的值就是预计扫描索引记录行数
EXPLAIN SELECT * FROM t_emp WHERE deptId = 1;

extra

包含不适合在其他列中显示但十分重要的额外信息。通过这些额外信息来理解MySQL到底将如何执行当前的查询语句。MySQL提供的额外信息有好几十个，这里只挑比较重要的介绍。

• **using temporary：**使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 union、order by 和 group by； 效率低

• **Using where：**使用了where，但在where上有字段没有创建索引。也可以理解为如果数据从引擎层被返回到server层进行过滤，那么就是Using where。

EXPLAIN SELECT * FROM t_emp WHERE name = ‘风清扬’;

• Using filesort：

在对查询结果中的记录进行排序时，是可以使用索引的，如下所示：

EXPLAIN SELECT * FROM t_user ORDER BY id;

如果排序操作无法使用到索引，只能在内存中（记录较少时）或者磁盘中（记录较多时）进行排序（filesort），如下所示：

EXPLAIN SELECT * FROM t_user ORDER BY name;

• Using index：使用了覆盖索引，表示直接访问索引就足够获取到所需要的数据，不需要通过索引回表

EXPLAIN SELECT id, username FROM t_user;

Explain 执行计划中各个字段的含义:

• 查看当前数据库是否支持profiles select @@have_profiling ;
• 查看当前数据库是否打开了 profiling select @@profiling;
• 开启profiling SET profiling = 1;
• 使用指令查看当前会话指令的执行耗时

• show profiles; 查看每一条SQL的耗时基本情况
• show profile for query query_id; 查看指定query_id的SQL语句各个阶段的耗时情况
• show profile cpu for query query_id; 查看指定query_id的SQL语句CPU的使用情况

• explain执行计划：其它的都是关于时间上的优化，explain是关于执行顺序的优化。EXPLAIN 或 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;直接在select语句之前加上关键字Explain获取的信息：

• id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序

• id相同，执行顺序从上到下
• d不同，值越大，越先执行)。

• select_type：表示 SELECT 的类型，常见的取值有:

• SIMPLE，简单表，即不使用表连接 或者子查询
• PRIMARY，主查询，即外层的查询
• UNION，UNION 中的第二个或者后面的查询语句
• SUBQUERY，SELECT/WHERE之后包含了子查询）

• type：表示连接类型，性能由好到差的连接类型为：NULL、system、const、 eq_ref、ref、range、 index、all
• possible_key：显示可能应用在这张表上的索引，一个或多个
• key：实际使用的索引，如果为NULL，则没有使用索引。
• key_len：表示索引字段最大可能长度，并非实际长度，不损失精确性的前提下越短越好
• rows：MySQL认为必须要执行查询的行数，innodb引擎的表中是一个估计值， 并不总是准确
• filtered：表示返回结果的行数占需读取行数的百分比，值越大越好
• extra：额外字段

索引使用：单列索引，联合索引，前缀索引

验证索引效率

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

可以看到即使有1000w的数据,根据id进行数据查询,性能依然很快，因为主键id是有索引的。 那么接

下来，我们再来根据 sn 字段进行查询，执行如下SQL：

SELECT * FROM tb_sku WHERE sn = ‘100000003145001’;

我们可以看到根据sn字段进行查询，查询返回了一条数据，结果耗时 15.67sec，就是因为sn没有索

引，而造成查询效率很低。

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

下查询耗时情况。

create index idx_sku_sn on tb_sku(sn);

创建索引以后，我们继续执行相同sql语句进行查询

SELECT * FROM tb_sku WHERE sn = ‘100000003145001’;

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

量级的。

最左前缀法则

联合索引:一个索引包含多个列

如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，

并且不跳过索引中的列。如果跳跃某一列，索引将会部分失效(后面的字段索引失效)。

以 tb_user 表为例，我们先来查看一下之前 tb_user 表所创建的索引。

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

对于最左前缀法则指的是，查询时，最左边的列，也就是profession必须存在，否则索引全部失效。而且中间不能跳过某一列，否则该列后面的字段索引将失效。 接下来，我们来演示几组案例，看一下具体的执行计划：

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 = ‘软件工程’;

以上的这三组测试中，我们发现只要联合索引最左边的字段 profession存在，索引就会生效，只不过索引的长度不同。 而且由以上三组测试，我们也可以推测出**profession字段索引长度为768、age字段索引长度为2、status字段索引长度为4。**

而通过上面的这两组测试，我们也可以看到索引并未生效，原因是因为不满足最左前缀法则，联合索引最左边的列profession不存在

上述的SQL查询时，存在profession字段，最左边的列是存在的，索引满足最左前缀法则的基本条

件。但是查询时，跳过了age这个列，所以后面的列索引是不会使用的，也就是索引部分生效，所以索

引的长度就是768

当执行SQL语句:

explain select * from tb_user where age = 31 and
status = '0' and profession = '软件工程'；

是否满足最左前缀法则，走不走上述的联合索引，索引长度？

可以看到，是完全满足最左前缀法则的，索引长度774，联合索引是生效的。

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

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

# 1. 为表中的字段：profession、age、status创建联合索引
create index  pro_age_sta_idx on tb_user(profession,age,status);

# profession、age、status三个字段都使用到了索引，与查询时的位置顺序无关 57
explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程';

范围查询

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

explain select * from tb_user where profession = ‘软件工程’ and age > 30 and status= ‘0’;

当范围查询使用> 或 < 时，走联合索引了，但是索引的长度为770，就说明范围查询右边的status字段是没有走索引的。

当范围查询使用>= 或 <= 时，走联合索引了，但是索引的长度为54，就说明所有的字段都是走索引的。所以，在业务允许的情况下，尽可能的使用类似于 >= 或 <= 这类的范围查询，而避免使用 > 或 <

索引失效的情况

计算，函数导致索引列失效

在索引列进行计算，使用函数，都会导致索引列的失效

phone字段是单列索引

当根据phone字段进行等值匹配查询时, 索引生效。

explain select * from tb_user where phone = ‘17799990015’;

当根据phone字段进行函数运算操作之后，索引失效。 explain select * from tb_user where substring(phone,10,2) = ‘15’;

不等于(!= 或者<>)索引失效

字符串类型字段不加引号，索引失效

explain select * from tb_user where phone = '17799990015';
explain select * from tb_user where phone = 17799990015; # 索引失效

经过上面两组示例，我们会明显的发现，如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。

LIKE以%开头索引失效

由于下面查询语句中，都是根据profession字段查询，符合最左前缀法则，联合索引是可以生效的，
我们主要看一下，模糊查询时，%加在关键字之前，和加在关键字之后的影响。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程'; # 索引失效
explain select * from tb_user where profession like '%工%';  # 索引失效

经过上述的测试，我们发现，在like模糊查询中，在关键字后面加%，索引可以生效。而如果在关键字前面加了%，索引将会失效。

or连接条件

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

当or连接的条件，左右两侧字段都有索引时，索引才会生效。

目前来说age字段是没有索引的

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

如果这个时候，我们对age字段建立索引

create index idx_user_age on tb_user(age);

建立了索引之后，我们再次执行上述的SQL语句，看看前后执行计划的变化。

explain select * from tb_user where id = 10 or age = 23;
explain select * from tb_user where phone = '17799990017' or age = 23;

最终，我们发现，当or连接的条件，左右两侧字段都有索引时，索引才会生效。

全表扫描效率比索引效率高

如果MySQL评估使用索引比全表更慢，则不使用索引。

explain select * from tb_user where phone >= '17799990005';  #索引失效 因为大部分数据都满足这个条件
explain select * from tb_user where phone >= '17799990015';  #索引生效

经过测试我们发现，相同的SQL语句，只是传入的字段值不同，最终的执行计划也完全不一样，这是为什么呢？

因为MySQL在查询时，会评估使用索引的效率与走全表扫描的效率，如果走全表扫描更快，则放弃索引，走全表扫描。 因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。

is null 与 is not null

explain select * from tb_user where profession is null; #索引生效
explain select * from tb_user where profession is not null; #索引失效(大部分数据的profession字段是非空的)

查询时MySQL会评估，走索引快，还是全表扫描快，如果全表扫描更快，则放弃索引走全表扫描。 因此，is null 、is not null是否走索引，得具体情况具体分析，并不是固定的。

sql提示

目前tb_user表的索引情况如下

把之前测试所使用的索引删除

drop index idx_user_age on tb_user;
drop index idx_email on tb_user;

然后执行sql

explain select * from tb_user where profession = ‘软件工程’;

查询走了联合索引。

执行SQL，创建profession的单列索引：

create index idx_user_pro on tb_user(profession);

创建单列索引后，再次执行A中的SQL语句，查看执行计划，看看到底走哪个索引。

我们可以借助MySQL的sql提示来指定sql查询的时候，使用哪一个索引

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

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

1. explain select * from tb_user use index(idx_user_pro) where profession = ‘软件工程’;

ignore index ： 忽略指定的索引。

2. explain select * from tb_user ignore index(idx_user_pro) where profession = ‘软件工程’;

force index ： 强制使用索引

3. explain select * from tb_user force index(idx_user_pro) where profession = ‘软件工程’;

覆盖索引

**覆盖索引是指 查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到 。尽量使用覆盖索引，减少使用select ***
来看下面的sql执行计划

explain select id, profession from tb_user where profession = '软件工程' and age =31 and status = '0' ;
explain select id,profession,age, status from tb_user where profession = '软件工程'and age = 31 and status = '0' ;
explain select id,profession,age, status, name 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. 前两条使用了覆盖索引，后两条sql语句没有使用覆盖索引
2. use index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
3. null:没有使用覆盖索引，需要回表查询
4. tb_user表中有一个联合索引idx_user_pro_age_sta，该索引关联了三个字段

profession、age、status，而这个索引也是一个二级索引，所以叶子节点下面挂的是这一行的主键id。所以当我们查询返回的数据在id、profession、age、status 之中，则直接走二级索引直接返回数据了。如果超出这个范围，就需要拿到主键id，再去扫描聚集索引，再获取额外的数据了，这个过程就是回表。而我们如果一直使用select * 查询返回所有字段值，很容易就会造成回表查询（除非是根据主键查询，此时只会扫描聚集索引）。

5. 结合上图实际情况

• 由于第一条SQL要查找的字段都是使用了索引，所以其值在二次索引直接得到
• 由于第二条SQL要查找的字段“name”没有使用索引，所以该字段值需要回表查询得到

覆盖索引&回表查询 过程 举例说明

• id是主键，是一个聚集索引。
• name字段建立了普通索引，是一个二级索引（辅助索引)
  :::info

1. 执行SQL : select * from tb_user where id = 2;

• 根据id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。

2. 执行SQL：selet id,name from tb_user where name = ‘Arm’;

• **虽然是根据name字段查询，查询二级索引，但是由于查询返回在字段为 id，name，在name的二级索引中，这两个值都是可以直接获取到的，因为覆盖索引，所以不需要回表查询，性能高**

3. 执行SQL：selet id,name,gender from tb_user where name = ‘Arm’;

**由于在name的二级索引中，不包含gender，所以，需要两次索引扫描，也就是需要回表查询，性能相对较差一点。** :::
:::danger
面试题：一张表，有四个字段（id, username, password, status），由于数据量大，需要对以下SQL语句进行优化，该如何进行才是最优方案？
select id, username, password from tb_user where username=‘pkq’;
解：给username和password字段建立联合索引，则不需要回表查询，直接覆盖索引
:::

前缀索引

概念介绍

前缀索引:就是对文本的前几个字符建立索引（具体是几个字符在建立索引时指定）
为什么不对整个字段建立索引呢？一般来说使用前缀索引，可能都是因为整个字段的数据量太大，没有必要针对整个字段建立索引，前缀索引仅仅是选择一个字段的部分字符作为索引，这样一方面可以节约索引空间，另一方面则可以提高索引效率，当然很明显，这种方式也会降低索引的选择性。
比如说，当字段类型为字符串（varchar，text，longtext等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法

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

例子:为tb_user表的email字段，建立长度为5的前缀索引。

create index idx_email_5 on tb_user(email(5));

索引选择性

关于索引的选择性（Index Selectivity），它是指不重复的索引值（也称为基数 cardinality)和数据表的记录总数的比值，取值范围在 [0,1] 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。
那有小伙伴要问了，是不是选择性越高的索引越好呢？当然不是！索引选择性最高为 1，如果索引选择性为 1，就是唯一索引了，搜索的时候就能直接通过搜索条件定位到具体一行记录！这个时候虽然性能最好，但是也是最费空间的，这不符合我们创建前缀索引的初衷。
我们一开始之所以要创建前缀索引而不是唯一索引，就是希望能够在索引的性能和空间之间找到一个平衡，我们希望能够选择足够长的前缀以保证较高的选择性（这样在查询的过程中就不需要扫描很多行），但是又希望索引不要太过于占用存储空间。
那么我们该如何选择一个合适的索引选择性呢？索引前缀应该足够长，以便前缀索引的选择性接近于索引的整个列，即前缀的基数应该接近于完整列的基数。
首先我们可以通过如下 SQL 得到全列选择性：

SELECT COUNT(DISTINCT column_name) / COUNT(*) FROM table_name;

比如
select count(distinct email) / count() from tb_user ;

然后再通过如下 SQL 得到某一长度前缀的选择性：
SELECT COUNT(DISTINCT SUBSTRING(column_name, pos,prefix_length)) / COUNT() FROM table_name;

• substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)

创建前缀索引

create index idx_email_5 on tb_user(email(5));

前缀索引查询流程

前缀索引的查询流程：

• 查询字符串的前N个字符串去索引表中查找 获取到对应前缀的主键ID，找到后回表去主键表中获取到整行数据（整个字符串），对比是否与查找字符串相等
• 若在二级索引的表中 下一条数据的前缀N和当前查找的前缀N不等，则直接返回；
• 若相等，则获取下一条数据的主键ID，回表去主键表中获取整行数据，查看字符串是否和查询字符串相等；

总结

索引失效情况

• 索引失效情况：

• 不遵循最左前缀法则(联合索引)：没有有从索引的最左列开始，联合索引失效；中间跳过了索引的中间字段，则该字段后的联合索引都失效
• 范围查询(联合索引)：联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效
• 索引列运算：在索引列上进行运算操作， 索引将失效
• 字符串不加引号：字符串类型字段使用时，不加引号，索引将失效
• 模糊查询：尾部模糊匹配，索引不会失效；头部模糊匹配，索引失效。
• or连接条件：or左右两侧的字段必须都有索引，若左有 右无 则左的索引失效
• 数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引

索引设计原则

• 针对于数据量较大，且查询比较频繁的表建立索引
• 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
• 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
• 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
• 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间， 避免回表，提高查询效率。
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
• 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时，它可以更好地确定哪个索引最有效地用于查询。
• 单列索引：即一个索引只包含单个列在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时建议建立联合索引， 而非单列索引。