MySQL一周从入门到精通Day4

索引的使用

---

1. 慢查询日志

MySQL的日志类型

• 日志用于记录数据库的运行情况，以及用户对数据库执行的各类操作。发生故障，可以根据日志分析解决问题
• 日志类型：重做/回滚/二进制/错误/慢查询/一般查询/中继日志

开启慢查询日志

• 慢查询日志用于记录MySQL中 响应时间超过指定阈值的语句。
• 参数：
  • slow_query_log 是否开启,
  • slow_query_log_file 存储路径,
  • long_query_time 阈值,
  • log_queries_not_using_indexes 未使用索引的查询,
  • log_output 日志存储方式，默认file
• 可以通过命令临时设置，也可以通过修改配置永久设置

---

#查看是否开启慢日志
show variables like 'slow%';
#临时开启慢日志
set slow_query_log = 'ON';
#设置阈值
set long_query_time = 1;
#慢日志文件路径
show variables like '%datadir%';

---

实操

#登录
mysql -h localhost -u root -p
#查看是否开启慢日志
show variables like '%slow%';
#临时开启慢日志
set slow_query_log = 'ON';
//发现报错，原因是这是个全局变量
set global slow_query_log = 'ON';
#设置阈值
show varibles like '%long%';
set long_query_time = 1;

#慢日志输出到文件
show variables like '%log_output%';
#慢日志文件路径
show variables like '%datadir%';

#慢日志输出到表
use mysql;
show tables;

#模拟一个超过阈值的sql语句
select sleep(11);
//在C:\ProgramaData\sqlserver8.0\Data\mysql 根据自己的安装目录，里面的slow_log打开，会看到记录

---

2. 查询分析器explain

什么是explain

• 用于查看SQL语句的执行计划。SQL语句前加explain，会显示将如何处理该语句。
• 包括：分析表的读取顺序/ 数据读取操作类型/ 哪些索引可以使用/ 哪些索引被实际使用/ 表之间的引用/ 每张表有多少行被优化器查询

---

explain使用

• 只要在SQL语句前加explain命令。
• 比如，explain select * from employee where name ='张三' \G
• explain select * from employee where id =1 \G
• explain结果解析
  标红的几个比较重要

---

实操

#登录
mysql -h localhost -u root -p
#选择数据库
use mydb;
#查看表
show tables;
select * from employee;

#查询字段
select * from employee where name ='张三';
select * from employee where id = 5;

#我们加上explain后看一下具体过程
explain select * from employee where name ='张三';
explain select * from employee where id = 5;
//可以看出 从连接类型type 索引key 扫描行数rows 百分比filters
//都可以看出，索引查询效率更高

---

3. 索引的基本使用

---

索引及其作用

• 一种特殊的数据结构，对数据库表中一列或者多列的值进行排序，类似图书的目录，可以提升查询效率。全表扫描效率很低

常见索引种类

• 普通：加速查询
• 唯一：列的值为一，允许有空值
• 主键：特殊的唯一索引，不允许空值。一般建表自动创建主键索引
• 复合：多列值
• 全文索引：文本内容进行分词索引

索引的基本使用

创建索引

• 创建普通索引create index indexName on tableName(columnName(length));
• 创建唯一索引create unique index indexName on tableName(columnName(length));
• 创建复合索引create index indexName on tableName(columnName1，columnName2,...);

删除索引 drop index [indexName] on tableName;
查看索引 show index from tableName;

---

索引的实战经验

• 选择区分度高的列建立索引
• 每次查询每张表只能使用一个索引
• 避免对索引列进行计算

---

实操

#登录
mysql -h localhost -u root -p
#选择数据库
use mydb;
#查看表
show tables;
select * from employee;

#查看索引
show index from employee\G
//发现对主键默认建了一个索引
#查看详细信息
explain select * from employee where name = '张三' \G
//发现type为all，就是全扫描

#创建索引
create index idx_name on employee(name);
explain select * from employee where name = '张三' \G
//效率提升

#删除索引
drop index idx_name on employee;

---

---

4.复合索引前导列特性

---

• 在MySQL中，如果创建了复合索引（name,salary,dept）,就相当于创建了三个索引 （name）,(name,salary), (name,salary,dept)。
• 因此，我们要把常用查询条件的列放在最左边，所以又叫最佳左前缀特性

---

实操

#登录
mysql -h localhost -u root -p
#选择数据库
use mydb;
#查看表
show tables;
select * from employee;

#查看索引
show index from employee\G;
//发现对主键默认建了一个索引
#查看详细信息
explain select * from employee where name = '张三' \G
//发现type为all，就是全扫描

#创建复合索引
create index idx_name_salary_dept on employee(name,salary,dept);
#查询
explain select * from employee where name = '张三' \G
//效率提升，只扫描一行
explain select * from employee where name = '张三' and salary = '5000' \G
explain select * from employee where name = '张三' and salary ='5000' and dept = '部门A'\G

#部门A
explain select * from employee where dept = '部门A' \G
#薪水
explain select * from employee where salary = '5000' \G

---

---

5.覆盖索引

---

• 又称索引覆盖，即select的数据列从索引中就能得到，不必读取数据行，也就是只要扫描索引就可以得到结果。
• MySQL的查询优化器会在执行查询前判断，是否有一个索引覆盖所有的查询列
• 不是所有类型都能作为覆盖索引，覆盖索引必须要存储索引列的值。

---

- 如何判断使用了覆盖索引？

• 在查询分析器explain的extra列可以看到 using index

---

实操

#登录
mysql -h localhost -u root -p
#选择数据库
use mydb;
#查看表
show tables;
select * from employee;

#查看索引
show index from employee\G
//发现复合索引，就是我们上一节建立的。name salary dept ，相当于有4个索引

#查看表信息
select * from employee;
#查询
select * from employee where name = '张三';
explain select * from employee where name = '张三' \G
//发现extra为null，没有覆盖索引

#查询id
explain select name from employee where name = '张三' \G
//发现extra为using index
explain select name from employee \G

//发现extra为using index
explain select name,sex from employee \G
//发现extra为null

---