【MySQL进阶】 存储引擎 索引

😊😊作者简介😊😊 ： 大家好，我是南瓜籽，一个在校大二学生，我将会持续分享Java相关知识。
🎉🎉个人主页🎉🎉 ： 南瓜籽的主页
✨✨座右铭✨✨ ： 坚持到底，决不放弃，是成功的保证，只要你不放弃，你就有机会，只要放弃的人，他肯定是不会成功的人。

✨✨存储引擎✨✨

🍉🍉存储引擎介绍🍉🍉

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。

🍉🍉存储引擎特点🍉🍉

1、 InnoDB

1). 介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在 MySQL 5.5 之后，InnoDB是默认的MySQL 存储引擎。
2). 特点
DML操作遵循ACID模型，支持事务；行级锁，提高并发访问性能；支持外键FOREIGN KEY约束，保证数据的完整性和正确性；
3). 文件
xxx.ibd：xxx代表的是表名，innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm-早期的 、sdi-新版的）、数据和索引。

2、MyISAM

1). 介绍
MyISAM是MySQL早期的默认存储引擎。
2). 特点
不支持事务，不支持外键，支持表锁，不支持行锁，访问速度快
3). 文件
xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

3、Memory

1). 介绍
Memory引擎的表数据时存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为
临时表或缓存使用。
2). 特点
内存存放
hash索引（默认）
3).文件
xxx.sdi：存储表结构信息

🍉🍉存储引擎区别与特点🍉🍉

🍉🍉存储引擎选择🍉🍉

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

✨✨索引✨✨

🍎🍎索引介绍🍎🍎

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
在无索引情况下，在我们执行查询语句的时候默认会全表扫描，性能很低

🍎🍎索引特点🍎🍎

🍎🍎索引结构🍎🍎

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

下面是不同存储引擎对索引结构的支持情况:

注: 我们平常说的索引，默认是B+树结构的索引。

🍎🍎索引分类🍎🍎

在MySQL数据库，将索引的具体类型主要分为以下几类：主键索引、唯一索引、常规索引、全文索引。

聚集索引和二级索引

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

聚集索引选取规则

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

🍎🍎索引语法🍎🍎

1. 创建索引

-- 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (
index_col_name,... ) ;

2. 查看索引

-- 查看索引
SHOW INDEX FROM table_name ;

3. 删除索引

-- 删除索引
DROP INDEX index_name ON table_name ; 1

🍎🍎索引使用🍎🍎

1. 最左前缀法则

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

注: 最左前缀法则中指的最左边的列，是指在查询时，联合索引的最左边的字段(即是
第一个字段)必须存在，与我们编写SQL时，条件编写的先后顺序无关。

2. 范围查询

联合索引中，出现范围查询(>,<)，范围查询右侧的列索引失效，所以我们要尽量使用>= 或者 <=才不会出现索引失效的情况。

❗❗索引失效情况❗❗

注: 等值匹配查询 时，索引生效

例子:

explain select * from tb_user where name= '张三';

explain关键字我们在后面会有讲解😊

1. 在索引列上进行运算 (包括函数运算操作) 操作， 索引将失效。
例: explain select * from tb_user where substring(phone,10,2) = ‘15’;

2. 字符串类型字段使用时，不加引号，索引将失效。如果字符串不加单引号，对于查询结果，没什么影响，但是数据库存在隐式类型转换，索引将失效。
例: explain select * from tb_user where phone = ‘17799990015’; – 索引生效
explain select * from tb_user where phone = 17799990015; – 索引不生效

3. 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
例： explain select * from tb_user where name like ‘张%’;

4. 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
例: explain select * from tb_user where id = 10 or age = 23; – age没有建立索引，此时索引失效

5. 如果MySQL评估使用索引比全表更慢，则不使用索引。
例: explain select * from tb_user where profession = ‘软件工程’;
tips: – 数据库中的大部分专业都是计算机科学与技术，此时MySQL会进行评估，评估后会发现不走索引的情况下会更快，此时索引失效。

🍎🍎SQL提示🍎🍎

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

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

🍎🍎覆盖索引🍎🍎

尽量使用覆盖索引，减少select *。 那么什么是覆盖索引呢？ 覆盖索引是指 查询使用了索引，并
且需要返回的列，在该索引中已经全部能够找到 。

通过执行计划我们可以发现，主要的差异在Extra列中

🍎🍎前缀索引🍎🍎

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

语法:

create index idx_xxxx on table_name(column(n)) ; -- n为索引长度

前缀长度:

-- 可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，
-- 索引选择性越高则查询效率越高， 唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;

前缀索引的查询流程:

🍎🍎单列索引与联合索引🍎🍎

单列索引：即一个索引只包含单个列。
联合索引：即一个索引包含了多个列。

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

🍎🍎索引设计原则 🍎🍎

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

✨✨SQL性能分析✨✨

🍒🍒SQL执行频率🍒🍒

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

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

介绍:

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

通过上述指令，我们可以查看到当前数据库到底是以查询为主，还是以增删改为主，从而为数据
库优化提供参考依据。 如果是以增删改为主，我们可以考虑不对其进行索引的优化。 如果是以
查询为主，那么就要考虑对数据库的索引进行优化了。

🍒🍒慢查询日志🍒🍒

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

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

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2

配置完成之后，通过以下命令重启MySQL服务器进行测试，重启之后，慢查询日志已经打开了

systemctl restart mysqld

慢查询日志的缺点:

我发现，在慢查询日志中，只会记录执行时间大于等于 2s 的SQL，其他执行较快的SQL是不会进行记录的。

🍒🍒profile🍒🍒

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

– Windows中显示是 YES 或者是 NO

– Linux中显示 是 1 或者 0

我们可以通过set 语句 在 session / global 级别开启 profiling:

Linux 中:

SET PROFILING = 1;

Windows 中:

SET PROFILING  = TRUE;

执行一系列的业务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🍒🍒

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行
过程中表如何连接和连接的顺序。

语法:

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

各个字段的含义: