MySQL数据库优化（一）

一、索引

1、分类：

• 单值索引 （index）： 一个表可以有多个单值索引
• 唯一索引（unique index）： 不能重复
• 复合索引 ： 多个列构成的索引

2、创建索引

方式一：

create 索引类型 索引名 on 表名（字段名）

• 单值索引创建：create index dept_index on tb(dept);
• 唯一索引创建：create unique index dept_index on tb(dept);
• 复合索引：create index dept_name_index on tb(dept, name);

方式二：

alter table 表名 add 索引类型 索引名（字段名）;

如：alter table tb add index dept_index(dept);

注意：如果一个字段是primary key，则该字段默认就是主键索引！！！

3、删除索引：

drop index 索引名 on 表名

例如：drop index deot_index on tb

4、显示索引

show index from 表名

5、慢查询SQL排查

（1）检查是否开启了慢日志查询

show variables like '%sloq_query_log%';

（2）临时开启慢查询日志（当MySQL服务重启时会失效）

set global slow_query_log = 1;

（3）永久开启慢查询日志（永久有效）

在/etc/mydefault.cnf中追加配置：

打开/etc/mydefault.cnf，在[mysqld]下添加一下两行：

slow_query_log=1 #开启慢查询日志
slow_query_log_file=/var/lib/mysql/localhost-slow.log #慢查询日志存放路径

6、慢查询阈值

（1）查看慢查询时间阈值

show variables like '%long_query_time%';

（2）临时设置时间阈值

set global long_query_time = 5; #将慢查询时间阈值设置为5s
exit; #需要重新登录MySQL之后，该设置才能生效。但重启以后就失效了

（3）永久设置时间阈值

在/etc/mydefault.cnf中追加配置：

打开/etc/mydefault.cnf，在[mysqld]下添加一行：

long_query_time=5

7、定位由于慢查询原因的SQL语句

（1）查询超过时间阈值的SQL语句条数

`show global status like '%slow_queries%';`

（2）查询具体超过查询阈值时间的SQL语句

（a）可以查看慢查询日志存放的文件，在这个文件中有具体的语句以及详细信息。

（b）通过mysqldumpslow工具查看慢SQL ，可以通过一些过滤条件 快速查找出需要定位的慢SQL

mysqldumpslow语法：mysqldumpslow 各种参数 慢查询日志文件路径