Mysql 索引性能分析

1.查看CRUD次数

show global status like 'Com_______'（7个下划线）  

show global status like 'Com_______'

2.慢SQL分析

SET GLOBAL slow_query_log = 'ON';-- 设置慢SQL日志记录开启
SET GLOBAL long_query_time = 2;  -- 设置执行超过 2 秒的查询为慢查询

开启并设置后会自动将超过2秒的SQL语句记录到日志文件中

 Windows查询慢SQL文件位置

SHOW VARIABLES LIKE 'slow_query_log_file';

执行后没有显示完整的慢SQL文件位置，如下

找到 MySQL 数据目录

 SHOW VARIABLES LIKE 'datadir';

最后进行一个拼接

慢查询日志的完整路径为：

C:\ProgramData\MySQL\MySQL Server 8.0\Data\DESKTOP-73VTQL9-slow.log

3.查看执行一条SQL的耗时情况

show profiles;

执行一条查询语句后，拿到查询ID

查看指定查询ID的执行情况 

show PROFILE for query 60; 

这里可以看到执行耗费的时间更长

4.Explain分析查询的执行计划

用于查询语句之前，返回SQL执行计划

EXPLAIN SELECT * FROM users WHERE age > 30;

5. 最左前缀法则

联合索引，查询从最左列开始才能生效，跳列会导致索引失效

创建联合索引idx_user_pro_age_sta，包含列profession,age,status

create index idx_user_pro_age_sta on tb_user(profession,age,status); 

 如下几种情况判断索引是否生效

select * from perfession='软件工程' and age=31 and status='0'; //全部走联合索引

select * from age=31 and status='0' and perfession='软件工程'; //全部走联合索引（and识别优化）

select * from perfession='软件工程' and age=31; //走profession,age索引

select * from perfession='软件工程';  //走profession索引

select * from age=31 and status='0'; //不走索引（违反最左前缀法则）

select * from perfession='软件工程' and status='0'; // 走profession索引,status不走索引

6.索引失效的几种情况（本质上是 B+树索引的排序和存储方式决定）

1.使用 < 或 > 时，右侧索引失效

select * from perfession='软件工程' and age>31 and status='0';

优化：使用<= 或 >=

select * from perfession='软件工程' and age >= 31 and status='0';

2.函数进行运算， 索引失效

SELECT * from tb_user WHERE SUBSTR(phone,10,2)=35;

3.字符串类型不加引号，索引失效 

SELECT * FROM tb_user WHERE phone=19919991999; // 索引失效

SELECT * FROM tb_user WHERE phone='19919991999'; // 索引有效

 4.like 模糊匹配，%在左，索引失效

SELECT * FROM tb_user WHERE profession LIKE '软件%' // 索引有效

SELECT * FROM tb_user WHERE profession LIKE '%软件' // 索引失效

SELECT * FROM tb_user WHERE profession LIKE %'软件%' // 索引失效

5.or  前有索引，后无索引，索引失效

SELECT * FROM tb_user WHERE id=10 or age=20; // 索引失效

优化：使用 or 时，都加索引

create index idx_user_age on tb_user(age);

SELECT * FROM tb_user WHERE id=10 or age=20; // 创建索引后索引生效

 7.Mysql自动评估

 在执行查询时，优化器会根据  综合评估数据分布，决定使用索引还是全表扫描。

若 索引成本 < 全表成本，选择索引；否则全表扫描。