前言：本文均是基础内容，已掌握的建议跳过，你有更重要的内容需要学习。
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';
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优化的全部内容，感谢阅读。
网友评论