[!quote] 索引索引是高效获取数据的数据结构，MySQL 中的索引有多种，默认是 B +Tree 索引
优点
提高搜索的效率
优化了 ORDER BY 操作
缺点
会占用存储空间
降低了 INSERT，UPDATE，DELETE 的效率【~~因为修改数据时需要重新维护数据结构~~】
查询叶子节点的过程时，每访问一个节点，都会触发一次磁盘 IO 操作，所以当树的深度很深时，会导致查询性能下降

索引分类

[!hint] 按物理存储方式分
聚集索引：数据行的排列顺序 = 实际存储的物理顺序 = 索引顺序
一个表只能有一个聚集索引【~~因为数据行只能有一种物理顺序~~】
聚集索引通常用于主键
一定会有聚集索引
如果表中有主键，则主键索引就是聚集索引
如果表中没有主键，则唯一索引就是聚集索引
如果表中没有主键，没有唯一约束，则会自动生成聚集索引
二级索引 / 辅助索引：查找时，数据库先通过二级索引找到指针，再通过指针找到实际数据行【~~所以叫二级索引~~】
二级索引不会影响表的物理排列顺序
聚集索引的 B + Tree 结构的叶子节点【Key 是主键 id，Value 是行数据】，二级索引的 B + Tree 结构的叶子节点【Key 是具体字段的数据，Value 是对应的主键 id】

[!hint] 按功能分
主键索引：当我为一张表创建主键后，MySQL 会自动帮我们为主键字段创建一个主键索引【~~主键索引是所有索引中性能最高的~~】
通常是聚集索引，但也可以是非聚集索引
唯一索引：当我为一张表创建唯一约束后，MySQL 会自动帮我们为该字段创建一个唯一索引
常规索引：就是自己创建的索引，没有任何约束
全文索引：它允许用户搜索文本中的关键词，而不是搜索具体的值【~~通常用于大型文本字段（文章，书籍）~~】

理解了聚集索引，和二级索引，我们就可以判断 SELECT 语句的查询效率

[!quote] 回表查询
sql
SELECT * FROM user
WHERE name = 'grennteck';
回表查询就是使用二级索引根据 'greenteck' 找到对应的主键 id，再使用聚集索引根据主键 id 找到行数据

索引结构

B + Tree 索引：最常用的索引结构【~~InnoDB，MyISAM，Memory~~】

MySQL 中对 B + Tree 做了优化，将最后所有叶子节点的单向链表改为了双向链表

Hash 索引：性能很高，但是不支持范围查询，因为哈希表无序【~~Memory~~】

R-Tree 空间索引：主要用于地理空间【~~MyISAM~~】

Full-Text 全文索引：快速匹配文档【~~InnoDB，MyISAM~~】

还需要重看黑马 MySQL 数据库入门到精通，2h 轻松掌握 mysql 检索优化技术的 3 - 6 集

操作

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名 ……);

sql

// 单列索引
CREATE INDEX idx_emp_name ON emp(name);
CREATE UNIQUE INDEX idx_emp_name ON emp(name);
CREATE FULLTEXT INDEX idx_emp_name ON emp(name);

// 联合索引
CREATE INDEX idx_emp_name ON emp(name,age,gender);

[!hint] 联合索引的顺序是有讲究的

查看索引

SHOW INDEX FROM 表名

删除索引

DROP INDEX 索引名 ON 表名;

问题

InnoDB 主键索引的 B + Tree 高度为多高呢？

SQL 性能分析

查看各语句执行频率

[!hint] 当该数据库大多数语句是 SELECT 语句时，才需要优化

SHOW [GLOBAL|SESSION] STATUS 查看服务器状态信息
- SHOW [GLOBAL|SESSION] STATUS LIKE 'Com_______'; 专门查看各种语句执行的次数

sql

SHOW GLOBAL STATUS LIKE 'Com_______';

---
|Variable_name|Value|
|---|---|
Com_binlog,0
Com_commit,1
Com_delete,0
Com_import,0
Com_insert,2
Com_repair,0
Com_revoke,0
Com_select,482
Com_signal,0
Com_update,0
Com_xa_end,0

慢查询日志

当我们知道了这个数据库的 SELECT 语句很多，但我们还需要知道具体哪个 SELECT 语句耗时，这时候我们就需要查看慢查询日志了

[!quote] 慢查询日志慢查询日志中记录了所有执行时间超过指定参数【~~默认是 10s~~】的所有 SQL 语句，默认不开启，在 /var/lib/mysql/localhost-slow.log 下

查看慢查询日志是否开启

sql

SHOW VARIABLES LIKE 'slow_query_log';

全局开启慢查询日志【修改配置文件】

sql

# 开启：在/etc/my.cnf文件中添加
slow_query_log=1
# 设置慢查询的时间参数
long_query_time=2

在当前会话开启慢查询日志

sql

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

profile

[!quote] profile profile 文件中记录了所有语句的执行耗时情况，比慢查询日志更加全面

开启 profile
- SELECT @@have_profiling; 查看当前数据库是否支持 profile
- SELECT @@profiling; 查看 profile 是否开启
- SET profiling = 1; 开启 profile
查看耗时
- SHOW profiles; 查看之前所有语句的执行耗时
- SHOW profile FOR QUERY 查询id 根据查询 id 【~~profile 文件中的 id~~】，展示某个条 SQL 语句在各个阶段的耗时情况

EXPLAIN

[!quote] EXPLAIN
可以显示多表查询语句中的具体执行流程【~~先查哪个表，再查哪个表 ……~~】
可以显示是否使用了索引，以及使用的是哪个索引

EXPLAIN 查询结果中的字段解释：

id 查询的标识符【id 越大，越先执行，id 相同的，行数靠前的先执行】
select_type 查询类型【SIMPLE、PRIMARY、UNION ……】
table 当前行对应的表
partitions 表分区信息
type 连接类型，性能从上到下越来越差
- NULL 不访问表时，~~这是理想状态~~
- system
- const 根据主键索引，或唯一索引查询时
- eq_ref
- ref 使用非唯一索引进行查找
- range
- index
- ALL 全表扫描
possible_keys 可能用到的索引
key 实际使用的索引
key_len 使用的索引的长度，越短越好【~~与字段存储的实际数据有关~~】
ref 索引列上使用的常量
rows 需要查询的行数【~~是一个预估值~~】
filtered 被 WHERE 子句过滤掉的行的百分比，越大越好【100% 说明返回了 0 行】
Extra 额外信息【Using filesort、Using temporary ……】

sql

# 测试（查询role为ADMIN的用户的用户名）的执行流程
EXPLAIN  
SELECT *  
FROM user  
WHERE user_id in (SELECT user_id  
                  FROM user_role  
                  WHERE role_id in (SELECT role_id  
                                    FROM role  
                                    WHERE role_name = 'ADMIN'));

---
1,SIMPLE,<subquery2>,,ALL,,,,,,100,
1,SIMPLE,user,,eq_ref,PRIMARY,PRIMARY,4,<subquery2>.user_id,1,100,
2,MATERIALIZED,role,,ALL,PRIMARY,,,,2,50,Using where
2,MATERIALIZED,user_role,,ALL,,,,,3,33.33,Using where; Using join buffer (hash join)

# 分析得出，role表先执行，user_role表再执行，再执行<subquery2>子查询，最后是user表

索引分类 ​

索引结构 ​

操作 ​

创建索引 ​

查看索引 ​

删除索引 ​

问题 ​

SQL 性能分析 ​

查看各语句执行频率 ​

慢查询日志 ​

profile ​

EXPLAIN ​