【MySQL学习-黑马】SQL语句分类&事务&存储引擎&索引

SQL语句分类

DDL：数据库、表操作

DML：数据表的增删改操作

DQL：数据表的查询操作

DCL：管理数据库用户，控制数据库的访问权限

事务

四大特性

并发事务

脏读：假设有一个事务A查询某个数据，此时有一个事务B，把这个数据修改，然后事务A读取到了事务B修改的数据，但是事务B发生了回滚，事务A读取到的数据就是脏数据。

不可重复读：假设有一个事务A查询到某个数据是1，然后事务B把这个数据修改成2并提交，事务A再次读取这个数据的时候变成了2，和之前不一致，这种现象就是不可重复读。

幻读：假设有一个事务A打算添加一条id=1的记录，先查询数据库，发现没有这条数据，事务B此时也添加一条id=1的数据并提交，事务A再执行添加操作就会添加失败，因为数据库已经存在id=1的数据了。这种现象就是幻读。

事务隔离级别

存储引擎

MySQL创建表时默认的存储引擎是InnoDB，其他的存储引擎还有MyISAM、Memory，之后一种存储引擎都会详细介绍。

InnoDB

MyISAM

Memory

面试题：InnoDB和MyISAM存储引擎的区别？

事务：InnoDB支持事务，MyISAM不支持；

锁机制：InnoDB支持行级锁，MyISAM支持表级锁；

外键：InnoDB支持外键，MyISAM不支持外键。

存储引擎的选择

存储引擎没有好坏之分，只需根据实际情况选择即可。

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。

• InnoDB :是Mysq|的默认存储引擎， 支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作,那么InnoDB存 储引擎是比较合适的选择。
• MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。但是这种存储引擎已经被MongoDB替代了。
• MEMORY: 将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。但是这种存储引擎已经被Redis替代了。
   

索引结构

二叉树

B-Tree

B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）

B-Tree 的数据插入过程动画参照：11. 进阶-索引-结构-Btree_哔哩哔哩_bilibili
演示地址：B-Tree Visualization

B+Tree

 演示地址：B+ Tree Visualization

与 B-Tree 的区别：

• 所有的数据都会出现在叶子节点
• 叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

Hash

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。

特点：

• Hash索引只能用于对等比较（=、in），不支持范围查询（betwwn、>、<、…）
• 无法利用索引完成排序操作
• 查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引

存储引擎支持：

• Memory
• InnoDB: 具有自适应hash功能，hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的

面试题

为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构？

回答可根据以下要点回答：

为什么比二叉树好？为什么比B-Tree好？为什么比Hash索引好？

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

索引分类

聚集索引选取规则：

• 如果存在主键，主键索引就是聚集索引
• 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引
• 如果表没有主键或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

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

二级索引的叶子节点下存储的是一行的id

聚集索引和二级索引的工作流程

假设有一条sql为select * from user where name = 'Arm'，这时一条以name为条件查询的sql语句，所以先走二级索引，通过二级索引找到这一行数据的id。然后再根据id，走聚集索引，找到这一行的数据。这种通过先走二级索引找到id，再通过id走聚集索引找到一行数据的行为，称之为回表查询。

面试题

第一题

第一条sql索引的效率会更高，因为一条sql是以主键id为查询条件，直接走聚集索引就能拿到这一行的数据。

而第二条需要走二级索引，再走聚集索引，涉及到回表查询，所以效率比第一条sql低。

第二题

可得公式：n * 8 + (n + 1) * 6 = 16 * 1024，其中 8 表示 bigint 占用的字节数，n 表示当前节点存储的key的数量，(n + 1) 表示指针数量（比key多一个）。算出n约为1170。

如果树的高度为2，那么他能存储的数据量大概为：1171 * 16 = 18736；
如果树的高度为3，那么他能存储的数据量大概为：1171 * 1171 * 16 = 21939856。

索引相关语法

查看某表的索引

select index from table_name;

删除某表的索引

drop index index_name from table_name;

创建索引

create [unique | fulltext ] index index_name on table_name(col_name1, col_name2...);

性能分析

查看SQL的执行频次

执行语句，com后面是7条横线

show [ global | session ] status like 'com_______';

返回如下信息，可以看到执行了select语句23次，insert语句1次

慢查询日志

查看慢查询是否有开启

show variables like 'slow_query_log';

配置完毕之后,通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log。

慢查询信息如图所示。

部分参数含义，来自chatGPT

profile详情

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

SELECT @@have. profiling;

查看profiling是否有打开

select @@profiling;

默认profiling是关闭的，可以通过set语句在session/global级别开启profiling:

SET profling= 1;

执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况

show profiles;

#查看指定query_ id的SQL语句各个阶段的耗时情况

show profile for query query_id;

#查看指定query_ id的SQL 语句CPU的使用情况

show profile cpu for query query_ id;

explain执行计划

简单来说就是再SQL语句前加一个explain

EXPLAIN执行计划各字段含义:

• id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下; id不同,值越大,越先执行)。
• 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：表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
   

查询结果行转列

查询行转列只能在命令行模式下使用，在Navicat使用会提示语法错误。

行转列只需要在sql的最后，加上\G即可，支持多行结果行转列。

最左前缀法则【重要】

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

假设现在有一个联合索引，以a、b、c三列组成联合索引，并分别执行以下sql：

1. select * from table where c= '3' and a = '1' and b = '2'

        a、b、c三列都会走索引。

2. select * from table where b = '2' and c= '3'

        b、c均不走索引。

3. select * from table where a = '1' and c = '3'

        a走索引、c不走索引。

第一句sql，因为有a作为查询条件，所以符合最左前缀法则。b和c列也有作为查询条件，所以联合索引的指定列还是连续的，b和c也可以走索引。只要sql的查询条件中包含联合索引里的列，无论顺序如何，所有列都会走索引。

第二句sql，因为查询条件中不包含a列，所以不符合最左前缀法则。

第三句sql，因为有a作为查询条件，所以符合最左前缀法则，a可以走索引。但是sql中不包含b列的查询条件，索引的联合是由abc列连续地组成，所以c列也因为缺少b列，导致索引的列不连续而不走索引。所以，如果查询条件中缺少联合索引中间的某一列作为查询条件，那么后续的列作为查询条件也不会走索引。

在使用范围查询时，也需要注意索引失效。如果查询条件中的某一列使用了大于小于，那么联合索引中后续的列会失效。如果使用大于等于或者小于等于，后续的列就能使用到索引。

比如，有一个索引，涵盖了a, b, c三列，然后sql中where a = 'xxx' and b > 20 and c = 'xxx'，只有a和b列能够走索引，c不会走。如果把 b>20 改为 b >= 20，c才会索引。

索引失效场景【重要】

情况一：索引列运算、使用函数等

比如 select * from t_student where age + 1 = 15

        select * from t_student where substring(phone, 10, 2)

情况二：字符串不加引号。无论是索引只指定一个列，还是联合索引，都会失效 

比如 select * from t_student where phone = 13512345678

情况三：模糊查询

如果是头部模糊匹配，索引失效，比如

        select * from t_student where phone like '%123'

如果是尾部模糊匹配，索引有效，比如

        select * from t_student where phone like '123%'

情况四：使用or连接条件

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

以 select * from t_student where name = '123' or stuno = '1' 为例：

如果name建立了索引，而stuno没有建立索引，那么name也不会走索引。

如果name和stuno都建立了索引，那么两个查询条件都会走索引。

情况五：受数据分布影响

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

假设现在有这么一张表，表中共23条数据，有10条数据age=20，13条数据age=18

执行以下sql

1. select * from student where age > 17

这条sql不走索引，因为表中所有数据age > 17，mysql会认为直接全表扫描，比”多此一举“地走索引效率更高。

2. select * from student where age < 21

这条sql会走索引，因为表中没有一条数据符合age > 21，mysql会认为没必要全表扫描，走索引效率更高。

3. select * from student where age > 19

这条sql会走索引，因为表中符合age > 19的数据，少于全表的一半，走索引的效率比全表扫描更高。

4. select * from student where age < 19

这条sql不走索引，因为表中符合age < 19的数据，大于全表的一半，全表扫描的效率比走索引更高。

SQL提示 sql查询时使用指定索引 

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

语法就是在查询语句的基础上加个 use/ignore/force index(idx_name)，比如

        select * from student use index(idx_age) where age > 10

use：建议使用指定，是否真的使用还需要mysql权衡

ignore：强制不使用指定索引

force：强制使用指定索引

覆盖索引&回表查询

尽量使用覆盖索引(查询使用了索引，并且需要返回的列,在该索引中已经全部能够找到)，减少select *。
 

假设现在有一个联合索引idx_abc，以a、b、c列作为索引，然后执行以下sql语句：

1. select id from table where a = '1' and b = '2' and c = '3'

2. select id, a, b, c from table where a = '1' and b = '2' and c = '3'

3. select id, a, b, c, d from table where a = '1' and b = '2' and c = '3'

4. select * from table where a = '1' and b = '2' and c = '3'

以上四句sql的查询条件都一样，不同点在于查询的结果返回的列，逐一分析：

第一条sql，执行效率最高。因为走了联合索引，该联合索引也是二级索引，其叶子节点存放的就是id，而sql查询结果需要返回的正是id，无需再回表查询。

第二条sql，执行效率高。因为在走联合索引的过程中，就直接有了a、b、c三列的数据，当走到这个叶子节点的时候，也拿到了id，正好满足sql查询返回的结果，无需回表查询。像这种查询结果需要的列，被联合索引的列包含的，就叫覆盖索引。

第三条sql，执行效率较低。因为查询返回的结果列中，d列的数据不在联合索引中，走完联合索引找到对应的id之后，还需要回表查询，根据这个id走聚集索引，找到这一行的数据取出d列的数据。

第四条sql，执行效率最低。原因和第三条sql一样。

前缀索引

当字段类型为字符串(varchar, text等) 时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

单列索引&联合索引 的选择

结论：尽量选择联合索引，性能更高。

举个例子说明：

情况一：现有两个单列索引 idx_a 和 idx_b，然后执行SQL：

        select id, a, b from table where a = '1' and b = '2'

分析：虽然a和b列都有各自的索引，但是mysql只会使用其中的一个索引，mysql优化器会评估哪个索引的效率更高，然后选择该索引完成本次查询。这样必然会导致回表查询，比如mysql选择使用了idx_a，这个索引必然没有b列的数据，还是需要使用聚集索引找到b列的数据。

情况二：现有两个单列索引 idx_a 和 idx_b，以及一个联合索引 idx_a_b，然后执行SQL：

        select id, a, b from table where a = '1' and b = '2' 

分析：当存在联合索引和各自的单列索引时，但是mysql还是会有可能使用单列索引，性能依旧不如直接使用联合索引的高。

情况三：现有联合索引idx_a_b，然后执行SQL：

        select id, a, b from table where a = '1' and b = '2' 

分析：这种情况性能是最高的，联合索引中包含 a, b的数据，无需回表查询。

结论：尽量选择联合索引，性能更高。

索引设计原则【重要】

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