Mysql调优之使用mysql慢查询日志优化sql语句及表索引

Mysql调优之使用mysql慢查询日志优化sql语句及表索引

一，用慢查询日志找出耗时语句，并优化

# 查看mysql系统慢查询变量配置（能看到慢查询日志是否开启，日志路径等）

SHOW VARIABLES LIKE '%slow%';

SHOW VARIABLES LIKE '%slow%';

查询结果示例：
Variable_name    Value
log_slow_admin_statements    OFF
log_slow_slave_statements    OFF
slow_launch_time    2
slow_query_log    OFF
slow_query_log_file    E:\\deploy\\mysql-5.7.17-winx64\\data\\WIN-0UEGEHC41P1-slow.log

 

# 开启mysql慢查询日志

SET GLOBAL slow_query_log=ON; # 默认值是 OFF

# 查看慢查询时间（默认值是10.000000，即10s）
SHOW VARIABLES LIKE "long_query_time";

慢查询日志里面主要字段：
timestamp：时间戳
Time：语句执行时间点
连接字符串信息。
Query_time: 查询时间
Lock_time: 锁定时间
Rows_sent：发送数据行数
Rows_examined：检查数据行数

 

#查看一共执行过几次慢查询：
SHOW GLOBAL STATUS LIKE '%slow%';

通过慢查询日志找到耗时唱的语句，示例：SELECT * FROM myTableName WHERE source_sha1='熊仔其人' AND service_type=0; 那么继续用 EXPALIN 命令分析该条语句。

# MySQL 提供了一个 EXPALIN 命令，可以用于对 SELECT 语句 的执行计划进行分析，并详细的输出分析结果，供开发人员进行针对性的优化。我们想要查询一条sql有没有用上索引，有没有全表查询，这些都可以通过explain这个命令来查看。通过explain命令，我们可以深入了解到MySQL的基于开销的优化器，还可以获得很多被优化器考虑到的访问策略的细节以及运行sql语句时哪种策略预计会被优化器采用。explain的使用十分简单，通过在查询语句前面加一个explain关键字即可。（例如查看查看SQL是否使用索引）

EXPLAIN SELECT * FROM myTableName WHERE source_sha1='熊仔其人' AND service_type='0';

#查看该表数据量：

SELECT COUNT(*) FROM myTableName;

# 在 MySQL 中，可以使用 SHOW INDEX 语句查看表中已存在的索引。

SHOW INDEX FROM myTableName;

 

---

二，根据慢查询语句条件字段创建索引，删除无效索引

 

# 针对慢查询，一般做法是针对where条件后面的字段创建索引。

ALTER TABLE myTableName ADD INDEX idx_souce;  # 用add index_name 可以创建 PRIMARY KEY
ALTER TABLE myTableName ADD INDEX idx_source_sha1_service_type (source_sha1, service_type); 

创建索引命令格式：

### add index mysql 语句

# 1.PRIMARY  KEY(主键索引)
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  )

# 2.UNIQUE(唯一索引)
mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` )

# 3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )

# 4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )

# 5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

 

创建索引

alter table table_name add index_name using btree('column_name',...,'column_name');

create unique index index_name on table_name('column_name');

两者区别：

（1）add index_name 支持一次多个索引创建alter table table_name add index_name using btree('column_name',...,'column_name')，add index_name using btree('column_name',...,'column_name')

（2）add index_name 可以创建 PRIMARY KEY

（3）create index 创建时必须制定index_name，alter 可以使用默认名

 

删除无效索引：

# 索引删除
alter table table_name drop index index_name;

【完】