MySQL基础-存储引擎/索引/SQL优化

2022-05-23 本文已影响0人石头耳东

前言：本文均是基础内容，已掌握的建议跳过，你有更重要的内容需要学习。
MySQL-存储引擎
 MySQL-索引
 MySQL-SQL优化
相较于以前的文章有什么不同呢，算是复习。

SQL优化的尽头是索引！！！
索引的尽头是存储引擎！！！

零、本文纲要

一、存储引擎

InnoDB
MyISAM
Memory

二、索引

索引相关问题
SQL性能分析
索引使用
SQL提示
覆盖索引
前缀索引
单列索引/联合索引选择

三、SQL优化

插入数据(大批量数据插入)
主键优化
order by优化
group by优化
limit优化
count优化
update优化

一、存储引擎

-- 查看当前数据库支持的存储引擎
show engines;

1. InnoDB

① 特点

支持事务 / 行级锁 / 支持外键

② 文件

xxx.ibd
存储该表的表结构（frm-早期的、sdi-新版的）、数据和索引

innoDB引擎的每张表都会对应这样一个表空间文件
查看对应表空间文件参数：show variables like 'innodb_file_per_table';

查看表空间文件的指令
ibd2sdi xxx.ibd

③ 逻辑存储结构

表空间 / 段 / 区(64页) / 页(16KB) / 行

页是 InnoDB 存储引擎磁盘管理的最小单元，为了保证页的连续性，InnoDB 存储引擎每次从磁盘申请 4-5 个区。

2. MyISAM

① 特点

不支持事务 / 表级锁 / 不支持外键

② 文件

xxx.sdi：存储表结构信息
xxx.MYD: 存储数据
xxx.MYI: 存储索引

可以看到，不同于 InnoDB 存储引擎， MyISAM 存储引擎使用三个文件分别存储了表的内容。

3. Memory

① 特点

内存存放 / hash索引（默认）

② 文件

xxx.sdi：存储表结构信息

二、索引

1. 索引相关问题

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

思考题②： select * from tb_user where name = 'Jobs' ;的查询过程?

回表查询：
a、走name字段的二级索引查找到对应的主键；
b、走聚集索引拿到对应主键的row数据。

思考题③：存储2000W条数据，InnoDB主键索引的B+tree高度为多高呢?
假设：1行数据1KB，一页可以存储16行数据。InnoDB的指针大小为6字节，假设主键为Bigint8字节。

8 * n + 6 * (n + 1) = 16 * 1024 → n = 1069;
假设两层索引，(1169 + 1) * (1169 + 1) * 16 = 21902400;
B+tree的高度仅需3层，既可以存放2000W条数据。

思考题④：用户表含(id,username,password,status)四个字段，大数据量的情况下如何建立索引，优化SQL执行效率？
SQL: select id, username, password from tb_user where username = 'Jobs';

2. SQL性能分析

① 查询SQL频率

SHOW [SESSION|GLOBAL] STATUS

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

② 慢查询日志

SHOW VARIABLES LIKE 'slow_query_log';

> vim /etc/my.cnf
> # 插入下方数据
> # 1表示开启，0表示关闭
> slow_query_log=1
> # 慢查询的设定时间10s，可以根据实际需求调整
> long_query_time=10

慢查询日志位置：/var/lib/mysql/localhost-slow.log

③ profile详情

SELECT @@have_profiling ;
SET profiling = 1;

-- 查看每一条SQL的耗时基本情况
SHOW PROFILES;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
SHOW PROFILE FOR QUERY query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
SHOW PROFILE CPU FOR QUERY query_id;

④ explain

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

字段	含义
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 的值越大越好。

a、type

一般通过主键或者唯一索引查询，type类型会是const；
一般通过非唯一索引查询，type类型会是ref(注意：联合索引部分失效也是ref)；
一般对索引进行遍历的查询，type类型会是range/index；

3. 索引使用

① 联合索引-最左前缀法则，联合索引查询跳跃某一索引列，索引将会部分失效
② 联合索引-范围查询(>,<)右侧的列索引失效
注意：
a、范围查询该列的索引还是生效的；
b、范围查询的情形下使用(>=,<=)，则右侧索引也能生效；

③ 索引列-函数运算，索引失效
④ 索引列-隐式类型转换，字符串类型不加单引号''，索引失效
⑤ 索引列-模糊查询，左侧使用'%'如'%查询字段'，索引失效

⑥ or连接条件-一侧有索引、一侧无索引，索引失效
⑦ 数据分布影响-如果全表扫描更快，则不使用索引
比如：IS NULL / IS NOT NULL-如果全表扫描更快，则不使用索引

4. SQL提示

USE INDEX-建议使用指定索引
EXPLAIN SELECT * FROM tb_user USE INDEX(idx_user_pro) WHERE profession = '软件工程';

FORCE INDEX-强制使用指定索引
EXPLAIN SELECT * FROM tb_user FORCE INDEX(idx_user_pro) WHERE profession = '软件工程';

IGNORE INDEX-忽略使用指定索引
EXPLAIN SELECT * FROM tb_user IGNORE INDEX(idx_user_pro) WHERE profession = '软件工程';

5. 覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

当EXPLAIN某些查询的type类型一致(const、ref)，可以通过查看Extra额外信息来判断查询效率。

Extra	含义
Using where; Using Index	查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据
Using index condition	查找使用了索引，但是需要回表查询数据

6. 前缀索引

节约索引空间，减少磁盘IO压力，从而提高索引效率。

创建前缀索引的语法
CREATE INDEX idx_xxxx ON table_name(column_name(len)) ;

索引的选择性
SELECT COUNT(DISTINCT column_name) / COUNT() FROM table_name ;
SELECT COUNT(DISTINCT substring(column_name, offset, len)) / COUNT() FROM table_name ;

7. 单列索引/联合索引选择

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

三、SQL优化

1. 插入数据(大批量数据插入)

test.txt文件数据为
1,39890001,Jobs,2022-01-01,tech
2,39890002,Tom,2022-01-03,tech
...

Load指令，大批量数据插入
LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test
FIELDS TERMINATED BY ',' LINES STARTING BY '\n';

LOAD指令官方文档

2. 主键优化

回顾：
InnoDB逻辑存储结构表空间 / 段 / 区(64页) / 页(16KB) / 行
索引组织表：index organized table，跟据主键顺序组织存放

页面存储 / 页面合并 / 页面分裂
页面合并阈值设置官方文档
顺序插入可以减少频繁的合并分裂操作(merge-split behavior)，进而提升SQL效率。

3. order by优化

Extra	含义
Using filesort	通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sortbuffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
Using index	通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

创建升&降序索引(满足最左前缀法则)
CREATE INDEX idx_one_two ON table_name(index_one ASC ,index_two DESC);
查看默认磁盘缓冲区大小(默认256KB)
SHOW VARIABLES LIKE 'sort_buffer_size';

4. group by优化

索引的使用也是满足最左前缀法则的

5. limit优化

一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

覆盖索引 + 子查询优化
EXPLAIN SELECT * FROM table_name t1 ,
(SELECT index_name FROM table_name ORDER BY index_name LIMIT page,page_size) t2

WHERE t1.index_name = t2.index_name;

6. count优化

按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以尽量使用 count()。

7. update优化

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁 ,并且该索引不能失效，否则会从行锁升级为表锁。

四、结尾

以上即为MySQL基础-存储引擎/索引/SQL优化的全部内容，感谢阅读。

MySQL基础-存储引擎/索引/SQL优化

零、本文纲要

一、存储引擎

二、索引

三、SQL优化

四、结尾

猜你喜欢

热点阅读