Mysql慢查询设置 和 建立索引

1 .mysql慢查询的设置

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

复制代码

修改后重启动mysql 

1.1  检察设置后的参数 

1. mysql> show variables like 'slow_query%';
2. +---------------------+--------------------------------+
3. | Variable_name       | Value                          |
4. +---------------------+--------------------------------+
5. | slow_query_log      | ON                             |
6. | slow_query_log_file | /usr/local/mysql/data/slow.log |
7. +---------------------+--------------------------------+

复制代码

1. mysql> show variables like 'long_query_time';
2. +-----------------+----------+
3. | Variable_name   | Value    |
4. +-----------------+----------+
5. | long_query_time | 1.000000 |
6. +-----------------+----------+

复制代码

参数 在windows 可能有少许差别,详细可以在客户端输入

1. show  variables like '%query_log%';

复制代码

1.2测试
执行一条慢查询SQL语句

1. mysql> select sleep(2);

复制代码

检察是否天生慢查询日志

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

复制代码

1.3接下来来看看 慢查询文件的记录 分析一下
我们取一条慢查询的日志下来 

1. # Time: 170913 15:59:32
2. # User@Host: root[root] @ localhost [127.0.0.1]  Id: 400609
3. # Query_time: 3.485729  Lock_time: 0.000154 Rows_sent: 10  Rows_examined: 391446
4. SET timestamp=1505289572;
5. 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
6. 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.建立索引
普通索引格式：

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

复制代码

好比 SELECT * FROM mytable WHERE category_id=1 AND user_id=2;
我们要优化这个SQL

1. CREATE INDEX mytable_categoryid on mytable(category_id);

复制代码

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

1. CREATE INDEX mytable_categoryid_userid ON mytable (category_id,user_id);

复制代码

唯一索引： 索引列的值必须唯一，但答应有空值

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

复制代码

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

1. CREATE UNIQUE INDEX addtime_index  ON dw_credit(addtime);

复制代码

还有就是全文索引

1. CREATE FULLTEXT INDEX index_content ON article(content)

复制代码

删除索引：

1. DROP INDEX index_name ON talbe_name;
2. show index from tblname;

复制代码

3.用explain一步步优化 和 mysqldumpslow 工具
我们现在有一个表叫dw_account_log ，里面大概有30万条数据，除了主键索引，没有任何索引

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

复制代码

用了大概0.5秒时间。
首先我们来explain一下

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

复制代码

 看样子是没有用到任何索引，我们来看看这些字段的含义
关于他的解析转到Mysql explain中列的解析 这篇文章去了。
很明显，这里情况是比较糟糕的，没有使用到任何索引，没有可以使用的索引，而且根本上是全表扫描 rows=245056，差不多整个表都扫描了一遍，如果数据量大了，真是不敢想象啊。
如果建一个联合索引会怎样呢

1. CREATE INDEX index_type_addip ON dw_account_log (type,addip);

复制代码

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

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

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



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。