Mysql慢查询设置 和 建立索引

1 .mysql慢查询的设置

slow_query_log = ON   //或
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 2

修改后重启动mysql 

1.1  查看设置后的参数 

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

参数 在windows 可能有少许不同,具体可以在客户端输入

show  variables like '%query_log%';

1.2测试

执行一条慢查询SQL语句

mysql> select sleep(2);

查看是否生成慢查询日志

ls /usr/local/mysql/data/slow.log

1.3接下来来看看 慢查询文件的记录 分析一下

我们取一条慢查询的日志下来 

# Time: 170913 15:59:32
# User@Host: root[root] @ localhost [127.0.0.1]  Id: 400609
# Query_time: 3.485729  Lock_time: 0.000154 Rows_sent: 10  Rows_examined: 391446
SET timestamp=1505289572;
select pre_forum_thread.tid, pre_forum_thread.author,pre_forum_thread.subject,pre_forum_thread.heats  from  pre_forum_thread left join pre_forum_post 
on pre_forum_thread.tid=pre_forum_post.tid where pre_forum_thread.closed = 0 and pre_forum_post.position=1 ORDER BY pre_forum_thread.heats DESC ,pre_forum_thread.dateline DESC LIMIT 10;

Time: 这里表示17年的9月13

User@HOst ： 就是相关用户和阻击信息了

Query_time：查询用了多少时间

Lock_time：锁定了多少时间  

Rows_sent：返回了多少记录

Rows_examined：扫描了多少行

下面就是我们长长的 SQL语句了

2.建立索引

普通索引格式：

CREATE INDEX  索引名  ON 表名 (字段名); 

比如 SELECT * FROM mytable WHERE category_id=1 AND user_id=2;

我们要优化这个SQL

CREATE INDEX mytable_categoryid on mytable(category_id);

但是很明显这个 还用到一个user_id的字段 ，你一定会想到再帮user_id再建一个索引，可以，但是这不是一个最佳的方案

下面这个叫 多重索引

CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

唯一索引： 索引列的值必须唯一，但允许有空值

CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

例子：表名 dw_credit ，为addtime添加唯一索引,

CREATE UNIQUE INDEX addtime_index  ON dw_credit(addtime);

还有就是全文索引

CREATE FULLTEXT INDEX index_content ON article(content)

删除索引：

DROP INDEX index_name ON talbe_name;
show index from tblname;

3.用explain一步步优化 和 mysqldumpslow 工具

我们现在有一个表叫dw_account_log ，里面大概有30万条数据，除了主键索引，没有任何索引

select * from dw_account_log where type ='recharge' and addip='127.0.0.1';

用了大概0.5秒时间。

首先我们来explain一下

explain select * from dw_account_log where type ='tender' and addip='127.0.0.1';

 看样子是没有用到任何索引，我们来看看这些字段的含义

关于他的解析转到Mysql explain中列的解析 这篇文章去了。

很明显，这里情况是比较糟糕的，没有使用到任何索引，没有可以使用的索引，而且基本上是全表扫描 rows=245056，差不多整个表都扫描了一遍，如果数据量大了，真是不敢想象啊。

如果建一个联合索引会怎样呢

CREATE INDEX index_type_addip ON dw_account_log (type,addip);

索引创建成功了，我们再来explain一下

 现在情况已经比较好了，使用了刚刚建立的索引，而且只扫描了104行 ，type是ref ，不是最糟糕的all ，速度也快了不少。

对于left join ，inner join 这类，我们可以再他们连接的节点处 建立索引