700字范文 > 正确使用索引（sql优化） limit分页优化执行计划慢日志查询

正确使用索引（sql优化） limit分页优化执行计划慢日志查询

时间：2018-12-07 19:09:01

查看表相关命令

- 查看表结构

desc 表名

- 查看生成表的SQL

showcreatetable表名

- 查看索引

showindexfrom表名

使用索引和不使用索引

由于索引是专门用于加速搜索而生，所以加上索引之后，查询效率会快到飞起来。

# 有索引

正确使用索引

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

即使建立索引，索引也不会生效的情况：

- like '%xx'

select*fromtb1wherenamelike'%cn';

- 使用函数

select*fromtb1wherereverse(name) ='zhangqiye';

- or

select*fromtb1wherenid =1oremail ='seven@';

特别的：当or条件中有未建立索引的列才失效，以下会走索引

select*fromtb1wherenid =1orname='seven';

select*fromtb1wherenid =1oremail ='seven@'andname='zhangqiye'

- 类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然...

select*fromtb1wherename=999;

- !=

select*fromtb1wherename!='zhangqiye'

特别的：如果是主键，则还是会走索引

select*fromtb1wherenid !=123

- >

select*fromtb1wherename>'zhangqiye'

特别的：如果是主键或索引是整数类型，则还是会走索引

select*fromtb1wherenid >123

select*fromtb1wherenum>123

-orderby

selectemailfromtb1orderbynamedesc;

当根据索引排序时候，选择的映射如果不是索引，则不走索引

特别的：如果对主键排序，则还是走索引：

select*fromtb1orderbyniddesc;

- 组合索引最左前缀

如果组合索引为：(name,email)

name and email -- 使用索引

name -- 使用索引

email -- 不使用索引

其他注意事项

-避免使用select *

-count(1)或count(列) 代替 count(*)

-创建表时尽量时 char 代替 varchar

-表的字段顺序固定长度的字段优先

-组合索引代替多个单列索引（经常使用多个条件查询时）

-尽量使用短索引

-使用连接（JOIN）来代替子查询(Sub-Queries)

-连表时注意条件类型需一致

-索引散列值（重复少）不适合建索引，例：性别不适合

limit分页

无论是否有索引，limit分页是一个值得关注的问题

mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。

直接用limitstart,count分页语句

select*fromproductlimitstart,count

当起始页较小时，查询没有性能问题，我们分别看下从10，100，1000，10000开始分页的执行时间（每页取20条），如下：

select*fromproductlimit10,200.016秒

select*fromproductlimit100,200.016秒

select*fromproductlimit1000,200.047秒

select*fromproductlimit10000,200.094秒

我们已经看出随着起始记录的增加，时间也随着增大，这说明分页语句limit跟起始页码是有很大关系的，那么我们把起始记录改为40w看下（也就是记录的一般左右） select*fromproductlimit400000,20 3.229秒

再看我们取最后一页记录的时间

select*fromproductlimit866613,20 37.44秒

难怪搜索引擎抓取我们页面的时候经常会报超时，像这种分页最大的页码页显然这种时

间是无法忍受的。

从中我们也能总结出两件事情：

1）limit语句的查询时间与起始记录的位置成正比

2）mysql的limit语句是很方便，但是对记录很多的表并不适合直接使用。

对limit分页问题的性能优化方法

利用表的覆盖索引来加速分页查询

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。另外Mysql中也有相关的索引缓存，在并发高的时候利用缓存就效果更好了。

在我们的例子中，我们知道id字段是主键，自然就包含了默认的主键索引。现在让我们看看利用覆盖索引的查询效果如何：

这次我们之间查询最后一页的数据（利用覆盖索引，只包含id列），如下：

selectidfromproductlimit866613,20 0.2秒

相对于查询了所有列的37.44秒，提升了大概100多倍的速度

那么如果我们也要查询所有列，有两种方法，一种是id>=的形式，另一种就是利用join，看下实际情况：

SELECT*FROMproductWHEREID>=(selectidfromproductlimit866613,1)limit20

查询时间为0.2秒，简直是一个质的飞跃啊，哈哈

另一种写法SELECT*FROMproductaJOIN(selectidfromproductlimit866613,20)bONa.ID=b.id查询时间也很短，赞！

其实两者用的都是一个原理嘛，所以效果也差不多

执行计划

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY| <derived2> | ALL | NULL| NULL | NULL | NULL | 9 | NULL || 2 | DERIVED| tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+

结果字段说明

id：查询顺序标识

select_type

查询类型

SIMPLE 简单查询

PRIMARY 最外层查询

SUBQUERY映射为子查询

DERIVED 子查询

UNION联合

UNION RESULT 使用联合的结果

table：正在访问的表名

type

查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

ALL （必须进行优化）

全表扫描，对于数据表从头到尾找一遍

select * from tb1;

特别的：如果有limit限制，则找到之后就不在继续向下扫描

select * from tb1 where email = 'seven@'

select * from tb1 where email = 'seven@' limit 1;

虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。

INDEX

全索引扫描，对索引从头到尾找一遍

select nid from tb1;

RANGE

对索引列进行范围查找

select * from tb1 where name < 'zhagsan';

between and

> >= < <= 操作

注意：!= 和 > 符号

INDEX_MERGE

合并索引，使用多个单列索引搜索

select * from tb1 where name = zhangsan' or nid in (11,22,33);

REF

根据索引查找一个或多个值

select * from tb1 where name = 'seven';

EQ_REF

连接时使用primary key 或 unique类型

select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;

CONST

常量

表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。

select nid from tb1 where nid = 2 ;

SYSTEM

系统

表仅有一行(=系统表)。这是const联接类型的一个特例。

select * from (select nid from tb1 where nid = 1) as A;

possible_keys：可能使用的索引

key：真实使用的

key_len：MySQL中使用索引字节长度

rows

mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值

慢日志查询

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

MySQL 慢查询的相关参数解释

slow_query_log ：是否开启慢查询日志，1表示开启，0表示关闭。

log-slow-queries ：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log

long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，

这样日志信息就会被写入到mysql.slow_log表中。MySQL数据<br>库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。

日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需<br>要能够获得更高的系统性能，那么建议优先记录到文件。

开启慢查询

找到 MySQL 的配置文件，f （Windows为 my.ini ），在 [mysqld]下增加下面几行：

slow_query_log=1