mysql进阶-下

第六章 索引

学习本章节内容，我们最好能模拟一个数据量比较大的环境，我使用nodejs模拟了600多万条数据，大家可自行下载：

数据库表如下：

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  `nick_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户昵称',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '用户邮箱',
  `sex` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '用户性别（0男 1女 2未知）',
  `avatar` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '头像地址',
  `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '密码',
  `login_ip` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '最后登录IP',
  `login_date` datetime NULL DEFAULT NULL COMMENT '最后登录时间',
   `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT '测试文本',
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

select count(*) from ydl_user;  -- 5.429
select * from ydl_user where user_id = 1000000; -- 0.355s
select * from ydl_user where user_name = 'Jennifer Susan Johnson';   -- 4.715s

一、数据结构

一方面mysql的数据是存储在磁盘上的，另一方面还要满足对日常操作如【增删改查】的高效稳定的支持，我们当然可以采用更好的硬件来提升性能，但是选用合适的数据结构也很关键，innodb采用的是一种名为【b+树】的数据结构。

我们之前已经学习过innodb中的数据是以【行】为单位，存在一个个大小为16k的【页】中，刚才的b+树的作用就是按照一个的组织形式，将所有的【页】组织关联起来。

#1、B-树
我们要了解【B+树】，首先要了解一下【B-树】，这里的 B 表示 balance( 平衡的意思)，B-树是一种【多路自平衡的搜索树】，它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。下图是 B-树的简化图.

B-树有如下特点:

所有键值分布在整颗树中；
任何一个关键字出现且只出现在一个结点中；
搜索有可能在非叶子结点结束；
在关键字全集内做一次查找，性能逼近二分查找；
#2、B+树
【B+树】是【B-树】的变体，也是一种多路搜索树, 它与 B- 树的不同之处在于:

所有关键字存储在叶子节点
为所有叶子结点增加了一个双向指针
简化 B+树 如下图：

3、选型缘由
问题一：为什么在b-树或b+树中选择？

mysql数据模型更适合用这类数据结构，一条数据中通常包含【id】+【其他列数据】，我们可以很轻松的根据id组织一颗B+树。
我们知道innodb使用【页】（这是inndb管理数据的最小单位）保存数据，一页（16k），b+树中的每个节点都是一页数据。
问题二：为什么选择B+树？

相同的空间，不存放【整行数据】就能存【更多的id】，b+树能使每个节点能检索的【范围更大、更精确，极大的减少了I/O操作，保证b+树的层高较低，通常3到4层的层高就能支持百万级别的访问】。
Mysql是一种关系型数据库，【区间访问】是很常见的一种情况，B+树叶节点增加的双向指针，加强了区间访问性，可使用在范围区间查询的情况。
#4、发现索引
我们发现当使用id去查询数据时，效率很高，因为使用id可以利用B+树的特性，加速查询，请看以下两条sql的执行效率：

select * from ydl_user where id = 1                              -- 使用时间0.011s
select * from ydl_user where email = 'm.szi@xwsrnhp.pl'          -- 使用时间4.284s

我们发现，查询相同的记录，使用【id列】比使用【emil列】快了389倍，原因如下：

使用id列可以利用B+树的特性，由上自下查询。
使用email列只能从叶子节点进行【全表扫描】，一个一个的比较。
那么如果我想提升使用其他字段的查询效率，应该怎么做呢？

首先，我们应该想到的思路就是，按照这个逻辑再给其他的字段也创建一个这样的结构不就好了，如下：

但是我们会发现，如果我们不断的创建类似的结构，数据会保存很多次，1个G的数据可以膨胀为5G甚至10G，所以我们可以进行优化，在叶子节点中只【保存id】而不保存全部数据，查到id后再【回表】（回到原来的结构中根据id进行查询）查询整条记录，其结构如下：

其实这就是我们日常工作中经常创建的【索引】。

二、索引的分类和创建

#1、聚簇索引和非聚簇索引
我们在上边的例子中，【主键和数据】共存的索引被称之为【聚簇索引】，其他的，比如我们使用【姓名列+主键】建立的索引，可以称为【非聚簇索引】，或者【辅助索引】，或者【二级索引】，同时聚簇索引只有在innodb引擎中才存在，而在myIsam中是不存在的，如下图：

InnoDB使用的是【聚簇索引】，他会将【主键】组织到一棵B+树中，而【行数据】就储存在叶子节点上，若使用where id = 14这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

若对Name列进行条件搜索，且name列已建立【索引】，则需要两个步骤：

第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。
第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
如下图：

MyIsam使用的是【非聚簇索引】，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助列。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个【地址指向真正的表数据】，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

tips：

聚簇索引【默认使用主键】，如果表中没有定义主键，InnoDB 会选择一个【唯一且非空】的列代替。如果没有这样的列，InnoDB 会隐式定义一个主键【类似oracle中的RowId】rowid来作为聚簇索引的列。

如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyIsam占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

小问题：主键为什么建议使用自增id?

主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，出现页分裂，导致索引树调整复杂度变大，消耗更多的时间和资源。
聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但如果是自增的id，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
本章节中讲述了聚簇索引和二级键索引，对于【二级索引】而言，根据其不同的特性，我们又可以分为普通索引、唯一索引、复合索引等，接下来会一一讲解。
2、普通索引 （常规索引）(normal)
就是普普通通的索引，没有什么特殊要求，理论上任何列都可以当做普通索引，创建方式如下：

**第一步：**创建索引前先执行下列语句，观察执行时间：

select * from user where user_name ='Dorothy William Harris';  -- 整个执行时间为4.297s

第二步：创建user_name列的索引：

create index idx_user_name on user(user_name);   -- 整个索引创建时间为24.502s

**结论：**创建索引是一个很费时间的操作。

**第三步：**再次执行下列语句

select * from ydl_user where user_name ='Dorothy William Harris';   -- 执行时间0.031s

**结论：**创建索引后，我们的执行效率提升了138倍。

**第四部：**删除索引

drop index idx_user_name on ydl_user; 

其他创建索引的方法，如下：

（1）创建email列的索引，索引可以截取length长度，只使用这一列的前几个字符

create index idx_email on user(email(5));     --执行时间16.174s

重点：

有的列【数据量比较大】，使用前几个字符就能【很快标识】出来一行数据，那我们就可以使用这种方式建立索引，比如我们的邮箱，邮箱很多后缀是相同的我们完全可以忽略。
（2）使用修改表的方式添加索引

alter table user add index idx_email (email);

（3）建表时时，同时创建索引

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    index [indexName] (fieldName(length))
)

3、唯一索引（UNIQUE ）
对列的要求：索引列的值不能重复

创建表的同时，创建索引：

create table tbl_name(
    tid int,
    tname varchar(20),
    gender varchar(1),
    unique index unique_index_tname (tname)
)

独立的sql语句创建索引，我们的邮箱，用户名就应该创建唯一索引，姓名就应该是普通索引：

create unique index idx_email on user(email);

通过alter语句添加索引：

ALTER table mytable ADD UNIQUE [ux_indexName] (username(length))

唯一索引和主键的区别：

唯一索引列允许空值，而主键列不允许为空值。
主键列在创建时，已经默认为非空值 + 唯一索引了。
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。
唯一约束和唯一索引的区别：

唯一约束和唯一索引，都可以实现列数据的唯一，列值可以为null。
创建唯一约束，会自动创建一个同名的唯一索引，该索引不能单独删除，删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据唯一。
创建一个唯一索引，这个索引就是独立的索引，可以单独删除。
如果一个列上想有约束和索引，且两者可以单独的删除。可以先建唯一索引，再建同名的唯一约束。
4、多个二级索引的组合使用
记住一点：mysql在执行查询语句的时候一般只会使用【一个索引】，除非是使【用or连接的两个索引列】会产生索引合并。

我们针对某电商平台的检索功能做了优化，添加了三个索引，三个字段分别为【品牌】、【价格】、【销量】这三个的索引结构如下:

（1）品牌的索引结构：

（2）价格的索引结构：

（3）销量的索引结构：

针对以上的索引我们进行如下的查询，分析检索过程：

我们要检索品牌为阿玛尼（Armani）的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

**结论：**会使用一个索引。

我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

查询的步骤如下：

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id。

**第二步：**直接回表扫描，根据剩余条件检索结果。

**结论：**只会使用第一个索引。

我们要检索名称为阿玛尼（Armani），价格为26800，且销量在50以上的包包

查询的步骤如下：

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

**第二步：**直接回表扫描，根据剩余条件检索结果。

**结论：**只会使用第一个索引。

我们要检索名称为阿玛尼（Armani）或名称为LV的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，得到结果。

**结论：**我们经常听说，有or索引会失效，但是像这样的【type =‘Armani’ or type = ‘LV’】并不会，他相当于一个in关键字，会使用一个索引。

我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，进行缓存。

**第二步：**通过【价格索引】检索出价格在5万到7万之间的商品id，这是一个连接条件带有【or的查询】，所以需要和上一步的结果进行【并集】，得到结果。

**结论：**这个过程叫【索引合并】当检索条件有or但是所有的条件都有索引时，索引不失效，可以走【两个索引】。

我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id。

**第二步：**直接回表扫描，根据剩余条件检索结果。

**结论：**只会使用第一个索引。

我们要检索名称为阿玛尼（Armani）或价格大于8000，或【产地（该列无索引）】在北京的包包

**第一步：**优化器发现【产地列】无索引，同时连接的逻辑是【or】没有办法利用【索引】优化，只能全表扫描，索引失效。

**结论：**发生全表扫描，索引失效，条件中有没建立索引的列，同时关联条件是or。

#5、复合索引（联合索引）重要
当【查询语句】中包含【多个查询条件，且查询的顺序基本保持一致】时，我们推荐使用复合索引，索引的【组合使用】效率是低于【复合索引】的。

比如：我们经常按照A列、B列、C列进行查询时，通常的做法是建立一个由三个列共同组成的【复合索引】而不是对每一个列建立【普通索引】。

创建联合索引的方式如下：

alert table test add idx_a1_a2_a3 table (a1,a2,a3) 

-- 28.531s
create index idx_user_nick_name on ydl_user(user_name,nick_name,email(7));

复合索引的结构如下，复合索引会优先按照第一列排序，第一列相同的情况下会按照第二列排序，以此类推，如下图：

我们不妨把上边的图，转化为下边的表格，看起来会好一些：

认真阅读了上边的介绍和图形，我们再次思考以下几个问题：

我们要检索名称为阿玛尼（Armani）的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的商品id，回表查询，得到结果。

**结论：**会使用第一部分索引。

我们要检索名称为阿玛尼（Armani），价格在1万到3万之间的包包

查询的步骤如下：

**第一步：**通过【品牌索引】检索出所有阿玛尼的叶子节点。

**第二步：**在【满足上一步条件的叶子节点中】查询价格在1万到3万之间的包包的列，查询出对应的id，回表查询列数据。

**结论：**会使用复合索引的两个部分。

我们要检索名称为阿玛尼（Armani）或价格大于8000的包包

**第一步：**优化器发现我们并没有一个【价格列】的单独的二级索引，此时要查询价格大于8000的包，必须进行全表扫描。

**结论：**但凡查询的条件中没有【复合索引的第一部分】，索引直接【失效】，全表扫描。

我们要检索名称为阿玛尼（Armani），且价格大于8000，且【产地（该列无索引）】在北京的包包

**第一步：**通过【品牌索引】检索出所有阿玛尼的叶子节点。

**第二步：**在【满足上一步条件的叶子节点中】查询价格大于8000元的包包的叶子节点。

**第三步：**因为【产地列】无索引，但是是【and】的关系，我们只需要将上一步得到的结果回表查询，在这个很小的范围内，检索产地是不是北京即可。

**结论：**可以使用复合索引的两个部分。

我们要检索名称为阿玛尼（Armani）和LV之间，价格为在1万到3万的包包

查询的步骤如下：

**第一步：**通过【品牌索引】检索出所有阿玛尼和LV的所有叶子节点。

**第二步：**我们本想在第一步的结果中，快速定位价格的范围，但是发现一个问题，由于第一步不是等值查询，会导致后边的结果不连续，必须对【上一步的结果】全部遍历，才能拿到对应的结果。

**结论：**只会使用复合索引的第一个部分，这个就引出了【复合索引中特别重要的一个概念】-【最左前缀原则】。

**重点：**最左前缀原则：

（1）最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 ，如果建立（a,b,c,d）顺序的联合索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（2）=和in可以乱序，比如a = 1 and b < 2 and c = 3 ，咱们建立的索引就可以是（a,c,b）或者（c,a,b）。

**思考：**为什么联合索引的性能会比索引的组合使用效率高。
6、全文索引（FULLTEXT）
做全文检索（不如百度的搜索功能）使用的索引，但是这种场景，我们有更好的替代品，如：ElacticSearch，所以实际使用不多，只当了解。

使用 like + % 实现的模糊匹配有点类似全文索引。但是对于大量的文本数据检索，全文索引比 like + % 快 N 倍，速度不是一个数量级，但是全文索引可能存在【精度问题】。同时普通索引在使用like时如果%放在首位，索引会失效。

全文索引的版本支持

MySQL 5.6 以前的版本，只有 MyIsam 存储引擎支持全文索引。
MySQL 5.6 及以后的版本，MyIsam 和 InnoDB 存储引擎均支持全文索引。
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
使用全文索引的注意

使用全文索引前，搞清楚版本支持情况。
全文索引比 like + % 快 N 倍，但是可能存在精度问题。
如果需要全文索引的是大量数据，建议先添加数据，再创建索引。
对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。
（1）创建表时创建全文索引

create table ydlclass_user (    
    ..   
    FULLTEXT KEY fulltext_text(text)  
) 

（2）在已存在的表上创建全文索引

create fulltext index fulltext_text  on ydlclass_user(text);

本次创建用时143s：

（3）通过 SQL 语句 ALTER TABLE 创建全文索引

alter table ydlclass_user add fulltext index fulltext_text(text);

（4）直接使用 DROP INDEX 删除全文索引

drop index fulltext index on ydlclass_user;

（5）全文检索的语法

select * from ydlclass_user where match(text) against('高号便法还历只办二主厂向际');
 8、hash索引
hash索引是Memory存储引擎的默认方式，而且只有memory引擎支持hash索引，memory的数据是放在内存中的，一旦服务关闭，表中的数据就会丢失，我们可以使用如下的sql创建一张表:
~~~sql
CREATE TABLE `hash_user`  (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  `user_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户账号',
  ......
) ENGINE = Memory CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户信息表' ROW_FORMAT = Dynamic;

合理的使用memory引擎可以极大的提升性能，针对memory引擎的特点重启丢失），我们最好在其中存储一些公共的、常用的、不经常发生改变的数据，比如一些字典数据、配置数据等。同时，这些数据最好持久化在一些其他的地方，比如配置文件、其他的表，在程序启动的时候，主动的进行加载，我们可以使用如下sql，将一张表的数据加载到内存中：

insert into hash_user select * from ydl_user where user_id < 2000000;

我们在执行的过程种，可能有如下错误：

他告诉我，这个表使用的内存满了，放不下了，我们只需要调节下边两个参数，修改配置文件重启即可：

tmp_table_size = 4096M
max_heap_table_size = 4096M

基础工作完成，写几个sql语句尝试一下，我们发现真的一个字：快。

我们执行一下的sql

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.189s

创建一个hash索引

create index hash_idx_user_name using hash on hash_user(email);

再次查询

select * from hash_user where email = 'i.jnoyelrsg@rpnglcvh.museum'  -- 0.017s

也有不错的效果。

关于hash索引需要了解的几点：

hash是一种key-value形式的数据结构。实现一般是数组+链表的结构，通过hash函数计算出key在数组中的位置，然后如果出现hash冲突就通过链表来解决。当然还有其他的解决hash冲突的方法。hash这种数据结构是很常用的，比如我们系统使用HashMap来构建热点数据缓存，存取效率很好。
即使是相近的key，hash的取值也完全没有规律，索引hash索引不支持范围查询。
hash索引存储的是hash值和行指针，所以通过hash索引查询数据需要进行两次查询（首先查询行的位置，然后找到具体的数据）。
hash索引查询数据的前提就是计算hash值，也就是要求key为一个能准确指向一条数据的key，所以对于like等一类的匹配查询是不支持的。
只要是只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引。

7、空间索引（SPATIAL）
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。这是在地理位置领域使用的一种索引，其他场景用的很少，所以不需要深入学习。

三、explain的用法

explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。

explain的使用很简单，只需要在目标sql前加上这个关键字就可以了：

执行explain会产生以下11列内容，如下：

1、id字段
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

（1） id相同

id如果相同，可以认为是一组，执行顺序从上至下，如下查询语句：

explain select * from student s, scores sc where s.id = sc.s_id

（2） id不同

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

explain select * from student where age > (
	select age from student where name = '连宇栋'
);

（3）id部分相同部分不同

id如果相同，可以认为是一组，从上往下顺序执行在所有组中，id值越大，优先级越高，越先执行例子：

explain 
select * from student s, scores sc where s.id = sc.s_id
union
select * from student s, scores sc where s.id = sc.s_id;

2、select_type字段
（1）SIMPLE

简单查询，不包含子查询或Union查询的sql语句。

（2）PRIMARY

查询中若包含任何复杂的子部分，最外层查询则被标记为主查询。

（3） SUBQUERY

在select或where中包含子查询。

（4）UNION

若第二个select出现在uion之后，则被标记为UNION。

（6）UNION RESULT

从UNION表获取结果的合并操作。

#3、type字段
最好到最差备注：掌握以下10种常见的即可NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL

（1） NULL

MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引，比如通过id没有找到例子：

explain select min(id) from student;

（2）system

表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略，我也没有实测出来。

explain select * from mysql.proxies_priv

我实测一个只有一行记录的系统表，同样是all。

（3） const

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

explain select * from student where id = 1;

4. eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描例子：

被驱动表使用主键索面，结果唯一

explain select * from scores sc left join student s on s.id = sc.s_id

5. ref

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，返回所有匹配某个单独值的行，然而可能会找到多个符合条件的行，应该属于查找和扫描的混合体例子：

explain select * from student where name = '白杰'
explain select * from student s left join scores sc on s.id = sc.s_id

6. ref_or_null

类似ref，但是可以搜索值为NULL的行例子：

explain select * from student s where name = '白杰' or name is null

7. index_merge

表示使用了索引合并的优化方法例子：

explain select * from student where id = 1 or name ='李兴';

8. range

只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现between、<>、in等的查询。例子：

explain select * from student where id between 4 and 7;

9. index（全索引扫描）

Full index Scan，Index与All区别：index只遍历索引树，通常比All快因为索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。例子：

explain select name from student;

10. ALL（全表扫）

Full Table Scan，将遍历全表以找到匹配行例子：

explain select * from student;

4、table字段
表示数据来自哪张表

#5、possible_keys字段
显示可能应用在这张表中的索引，一个或多个查询涉及到的字段若存在索引，则该索引将被列出，但不一定被实际使用

#6、key字段
实际使用到的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表

#7、key_len字段
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度在不损失精确度的情况下，长度越短越好key_len显示的值为索引字段最大的可能长度，并非实际使用长度即key_len是根据定义计算而得，不是通过表内检索出的

#8、ref字段
哪些列或常量被用于查找索引列上的值

#9、rows字段
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

#10、partitions字段
匹配的分区

#11、filtered字段
它指返回结果的行占需要读到的行(rows列的值)的百分比

#12、Extra字段
该列包含不适合在其它列中显示，但十分重要的额外信息，我们列举几个例子：

（1）Using filesort

只要使用非索引字段排序，就会出现这样的内容。

（2）Using temporary

使用了临时表保存中间结果，MySQL在对结果排序时使用临时表，常见于排序order by 和分组查询group by例子：

（3）Using where

使用了where条件例子：

（4）impossible where

where子句的值总是false，不能用来获取任何数据：

explain select * from student where name = '白洁' and name = '李兴';

（5）Select tables optimized away

SELECT操作已经优化到不能再优化了（MySQL根本没有遍历表或索引就返回数据了）例子：

explain select min(id) from student;

（6）no matching row in const table

explain select * from student where id < 100 and id > 200;

四、使用索引的问题

设计好MySql的索引可以让你的数据库飞起来。但是，不合理的创建索引同样会产生很多问题？我们在设计MySql索引的时候有一下几点注意：

#1、哪些情况下适合建索引
频繁作为where条件语句查询的字段
关联字段需要建立索引
分组，排序字段可以建立索引
统计字段可以建立索引，例如count()，max()等
小案例：还记得在学习临时表时，分析过group by的执行流程吗（分组字段没有索引）？有了索引之后的分组执行流程如下：

直接使用索引信息，统计每个组的人数，直接返回。

#2、哪些情况下不适合建索引
频繁更新的字段不适合建立索引

where条件中用不到的字段不适合建立索引

表数据可以确定比较少的不需要建索引

数据重复且发布比较均匀的的字段不适合建索引（唯一性太差的字段不适合建立索引），例如性别，真假值

参与列计算的列不适合建索引，索引会失效

#3、能用复合索引的要使用复合索引
#4、null值也是可以走索引的，他被处理成最小值放在b+树的最左侧
#5、使用短索引
对字符串的列创建索引，如果可能，应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

#6，排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列创建复合索引。

#7、MySQL索引失效的几种情况
如果条件中有or，即使其中有条件带索引也不会使用走索引，除非全部条件都有索引
复合索引不满足最左原则就不能使用全部索引
like查询以%开头
存在列计算

explain select * from student where age = (18-1)

如果mysql估计使用全表扫描要比使用索引快，则不使用索引，比如结果的量很大
存在类型转化

– 索引不失效

explain select * from student where age = '18'  
explain select * from ydl_user where login_date = '2008-05-31 17:20:54'

– 索引失效 本来是字符串，你使用数字和他比较

explain select * from student where gander = 1

第七章 锁机制

锁是为了保证数据库中数据的一致性，使各种【共享资源】在被访问时变得【有序】而设计的一种规则。

MysQL中不同的存储引擎支持不同的锁机制。 InoDB支持【行锁】，有时也会升级为表锁，MyIsam只支持表锁。

【表锁】的特点就是开销小、加锁快，不会出现死锁。锁粒度大，发生锁冲突的概率小，并发度相对低。

【行锁】的特点就是开销大、加锁慢，会出现死锁。锁粒度小，发生锁冲突的概率高，并发度高。

今天我们讲锁主要从InnoDB引擎来讲，因为它既支持行锁、也支持表锁。

一、InnoDB的锁类型

InnoDB的锁类型主要有读锁(共享锁)、写锁(排他锁)、意向锁和MDL锁。

#1、s锁
读锁（共享锁，shared lock）简称S锁。一个事务获取了一个数据行的读锁，其他事务也能获得该行对应的读锁，但不能获得写锁，即一个事务在读取一个数据行时，其他事务也可以读，但不能对该数行增删改的操作。

**注：**读锁是共享锁，多个事务可以同时持有，当有一个或多个事务持有共享锁时，被锁的数据就不能修改。

简而言之：就是可以多个事务读，但只能一个事务写。

读锁是通过【select… lock in share mode】语句给被读取的行记录或行记录的范围上加一个读锁,让其他事务可以读，但是要想申请加写锁，那就会被阻塞。

事务一：

begin;
select * from ydl_student where id = 1 lock in share mode;

事务二：

begin;
update ydl_student set score = '90' where id = 1;

卡住了，说明程序被阻塞，确实加了锁。
s锁是可以被多个事务同时获取的，我们在两个不同的事务中分别对同一行数据加上s锁，结果都可以成功，如下图：

2、x锁
写锁，也叫排他锁，或者叫独占所，简称x锁（exclusive）。一个事务获取了一个数据行的写锁，既可以读该行的记录，也可以修改该行的记录。但其他事务就不能再获取该行的其他任何的锁，包括s锁，直到当前事务将锁释放。【这保证了其他事务在当前事务释放锁之前不能再修改数据】。

**注：**写锁是独占锁，只有一个事务可以持有，当这个事务持有写锁时，被锁的数据就不能被其他事务修改。

（1）一些DML语句的操作都会对行记录加写锁。

事务一：

begin;
update ydl_student set score = '90' where id = 1;

事务二：

begin;
update ydl_student set score = '88' where id = 1;

卡住了，说明程序被阻塞，确实加了锁。但是，我们发现其他事务还能读，有点不符合逻辑，这是应为mysql实现了MVCC模型，后边会详细介绍。

（2）比较特殊的就是select for update，它会对读取的行记录上加一个写锁，那么其他任何事务不能对被锁定的行上加任何锁了，要不然会被阻塞。

事务一：

begin;
select * from ydl_student where id = 1 for update;

事务二：

begin;
update teacher set name = 'lucy2' where id = 1;

卡住了，说明加了锁了。

（3）x锁是只能被一个事务获取，我们在两个不同的事务中分别对同一行数据加上x锁，发现后者会被阻塞，如下图：

3、记录锁（Record Lock）
记录锁就是我们常说的行锁，只有innodb才支持，我们使用以下四个案例来验证记录锁的存在：

（1）两个事务修改【同一行】记录，该场景下，where条件中的列不加索引。

事务一：

begin;
update ydl_student set score = '77' where name = 'jack';

事务二：

begin;
update ydl_student set score = '80' where name = 'jack';

发现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（2）两个事务修改同表【不同行】记录，此时where条件也不加索。

事务一：

begin;
update ydl_student set score = '76' where name = 'hellen';

事务二：

begin;
update ydl_student set score = '66' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，表被【锁】住了。

（3）两个事务修改【同一行】记录，where条件加索引

事务一：

begin;
update ydl_student set score = '99' where name = 'jack';

事务二：

begin;
update ydl_student set score = '79' where name = 'jack';

现事务二卡住了，只有事务一提交了，事务二才能继续执行，很明显，这一行数据被【锁】住了。

（4）两个事务修改同表【不同行】记录，此时where条件加索。

事务一：

begin;
update ydl_student set score = '77' where name = 'hellen';

事务二：

begin;
update ydl_student set score = '77' where name = 'jack';

发现都可以顺利修改，说明锁的的确是行。

**证明：**行锁是加在索引上的，这是标准的行级锁。

#4、间隙锁（GAP Lock）
间隙锁帮我们解决了mysql在rr级别下的一部分幻读问题。间隙锁锁定的是记录范围，不包含记录本身，也就是不允许在某个范围内插入数据。

间隙锁生成的条件：

1、A事务使用where进行范围检索时未提交事务，此时B事务向A满足检索条件的范围内插入数据。

2、where条件必须有索引。

第一步把teacher表的id的4改成8

事务一：

begin;
select * from ydl_student where id between 3 and 7 lock in share mode;

事务二：

begin;
insert into ydl_student values (5,'tom',66,'d');

发现卡住了，第一个事务会将id在3到7之间的数据全部锁定，不允许在缝隙间插入。

事务三：

begin;
insert into ydl_student values (11,'tom',66,'d');

插入一个id为11的数据，竟然成功了，因为11不在事务一的检索的范围。

#5、记录锁和间隙锁的组合（next-key lock）
临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含【索引记录】，又包含【索引区间】。

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

#6、MDL锁
MySQL 5.5引入了meta data lock，简称MDL锁，用于保证表中元数据的信息。在会话A中，表开启了查询事务后，会自动获得一个MDL锁，会话B就不可以执行任何DDL语句，不能执行为表中添加字段的操作，会用MDL锁来保证数据之间的一致性。

元数据就是描述数据的数据，也就是你的表结构。意识是在你开启了事务之后获得了意向锁，其他事务就不能更改你的表结构。MDL锁都是为了防止在事务进行中，执行DDL语句导致数据不一致。

#7、死锁问题
发生死锁的必要条件有4个，分别为互斥条件、不可剥夺条件、请求与保持条件和循环等待条件：

互斥条件，在一段时间内，计算机中的某个资源只能被一个进程占用。此时，如果其他进程请求该资源，则只能等待。
不可剥夺条件，某个进程获得的资源在使用完毕之前，不能被其他进程强行夺走，只能由获得资源的进程主动释放。
请求与保持条件，进程已经获得了至少一个资源，又要请求其他资源，但请求的资源已经被其他进程占有，此时请求的进程就会被阻塞，并且不会释放自己已获得的资源。
循环等待条件，系统中的进程之间相互等待，同时各自占用的资源又会被下一个进程所请求。例如有进程A、进程B和进程C三个进程，进程A请求的资源被进程B占用，进程B请求的资源被进程C占用，进程C请求的资源被进程A占用，于是形成了循环等待条件，如图1-7所示。
我模拟了一个死锁场景，如下：

image-20220517205641323
image-20220517205641323
InnoDB使用的是行级锁，在某种情况下会产生死锁问题，所以InnoDB存储引擎采用了一种叫作等待图（wait-for graph）的方法来自动检测死锁，如果发现死锁，就会自动回滚一个事务。

image-20220517205123881
image-20220517205123881
在MySQL中，通常通过以下几种方式来避免死锁。

尽量让数据表中的数据检索都通过索引来完成，避免无效索引导致行锁升级为表锁。
合理设计索引，尽量缩小锁的范围。
尽量减少查询条件的范围，尽量避免间隙锁或缩小间隙锁的范围。
尽量控制事务的大小，减少一次事务锁定的资源数量，缩短锁定资源的时间。如果一条SQL语句涉及事务加锁操作，则尽量将其放在整个事务的最后执行。

二、表锁

1、对于InnoDB表，在绝大部分情况下都应该使用【行级锁】，因为事务和行锁往往是我们之所以选择InnoDB表的理由。但在个另特殊事务中，也可以考虑使用表级锁。

第一种情况是：事务需要更新【大部分或全部数据】，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间锁等待和锁冲突，这种情况下可以考虑使用表锁来提高该事务的执行速度。

第二种情况是：事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。这种情况也可以考虑一次性锁定事务涉及的表，从而避免死锁、减少数据库因事务回滚带来的开销。

2、在InnoDB下 ，主动上表锁的方式如下：

lock tables teacher write,student read;
select * from teacher;
commit;
unlock tables;

使用时有几点需要额外注意：

使用【LOCK TALBES】虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能感知MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。

在用LOCAK TABLES对InnoDB加锁时要注意，事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。

表锁的力度很大，慎用。

三、从另一个角度区分锁的分类

#1、乐观锁
乐观锁大多是基于数据【版本记录机制】实现，一般是给数据库表增加一个"version"字段。

读取数据时，将此版本号一同读出，

更新时，对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

事务一：

select * from ydl_student where id = 1;

事务二：

select * from ydl_student where id = 1;
update ydl_student set score = 99,version = version + 1 where id = 1 and version = 1;

commit;
事务一：

update ydl_student set score = 100,version = version + 1 where id = 1 and version = 1;
commit;

发现更新失败，应为版本号被事务二、提前修改了，这使用了不加锁的方式，实现了一个事务修改期间，禁止其他事务修改的能力。

#2、悲观锁
总有刁民想害朕

悲观锁依靠数据库提供的锁机制实现。MySQL中的共享锁和排它锁都是悲观锁。数据库的增删改操作默认都会加排他锁，而查询不会加任何锁。此处不赘述。