1.1 寻找慢查询
定义:我们将超过指定时间的SQL语句查询称为“慢查询”。
1、在mysql日志中开启慢查询日志
1. 修改配置文件 在 my.ini 增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log日志记录( slow_query_log)
2. 在mysql命令行中执行命令开启
2、分析慢查询日志
1. 直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句,来分析sql慢查询语句
2. 例如:执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000
3. 得到如下结果: 显示结果分析:
table | type | possible_keys | key |key_len | ref | rows | Extra EXPLAIN列的解释:
table 显示这一行的数据是关于哪张表的
type 这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
rows 显示需要扫描行数
key 使用的索引
1.2 常见慢查询优化
1、索引没起作用的情况
1)使用LIKE关键字的查询语句
1. 在使用LIKE关键字进行查询的查询语句中,如果匹配字符串的第一个字符为“%”,索引不会起作用。
2. 只有“%”不在第一个位置索引才会起作用。
2)使用多列索引的查询语句
1. MySQL可以为多个字段创建索引,一个索引最多可以包括16个字段。
2. 对于多列索引,只有查询条件使用了这些字段中的第一个字段时,索引才会被使用。
2、优化数据库结构
1.对于字段比较多的表,如果有些字段的使用频率很低,可以将这些字段分离出来形成新表。
2. 增加中间表,对于需要经常联合查询的表,可以建立中间表以提高查询效率。
通过建立中间表,把需要经常联合查询的数据插入到中间表中,然后将原来的联合查询改为对中间表的查询,以此来提高查询效率。
SELECT * FROM tag
JOIN tag_post ON tag_id = tag.id
JOIN post ON tag_post.post_id = post.id
WHERE tag.tag = 'mysql'; 分解为: SELECT * FROM tag WHERE tag = 'mysql';
SELECT * FROM tag_post WHERE tag_id = 1234;
SELECT * FROM post WHERE post.id in (123,456,567);
分解关联查询举例