大表分页查询
在单表数据量较大的情况下,使用limit offset rows中的offset很大时,会出现效率问题:
优化后:
mysql> select * from test a inner join (select id from testwhere val=4 limit 300000,5) b on a.id=b.id;
+---------+-----+--------+---------+
| id | val | source | id |
+---------+-----+--------+---------+
| 3327622 | 4 | 4 | 3327622 |
| 3327632 | 4 | 4 | 3327632 |
| 3327642 | 4 | 4 | 3327642 |
| 3327652 | 4 | 4 | 3327652 |
| 3327662 | 4 | 4 | 3327662 |
+---------+-----+--------+---------+
5 rows inset (0.38 sec)
时间相差很明显。
为什么会出现上面的结果?我们看一下select * from test where val=4 limit 300000,5;的查询过程:
查询到索引叶子节点数据。根据叶子节点上的主键值去聚簇索引上查询需要的全部字段值。
需要查询300005次索引节点,查询300005次聚簇索引的数据,最后再将结果过滤掉前300000条,取出最后5条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上,而有300000次随机I/O查询到的数据是不会出现在结果集当中的。
开启慢查询日志
在配置文件 my.cnf 中的 [mysqld] 一行下边添加两个参数:
slow_query_log = 1
slow_query_log_file=/var/lib/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1
其中,slowquerylog = 1 表示开启慢查询;slowquerylogfile 表示慢查询日志存放的位置;longquerytime = 2 表示查询 >=2 秒才记录日志;logqueriesnotusing_indexes = 1 记录没有使用索引的 SQL 语句。
常用参数如下:
-s:排序方式,后边接着如下参数
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录书
at:平均查询时间
-t:返回前面多少条的数据
-g:翻遍搭配一个正则表达式,大小写不敏感
案例:
获取返回记录集最多的10个sql
mysqldumpslow -s r -t 10 /var/lib/mysql/slow-query.log
获取访问次数最多的10个sql
mysqldumpslow -s c -t 10 /var/lib/mysql/slow-query.log
获取按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/slow-query.log
explain
使用 MySQL 提供的 explain 查看 SQL 执行计划情况(关联表,表查询顺序、索引使用情况等)
mysql> explain select * from category;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | category | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
字段解释:
- id:select 查询序列号。id相同,执行顺序由上至下;id不同,id值越大优先级越高,越先被执行
- select_type:查询数据的操作类型,其值如下:
- simple:简单查询,不包含子查询或 union
- primary:包含复杂的子查询,最外层查询标记为该值
- subquery:在 select 或 where 包含子查询,被标记为该值
- derived:在 from 列表中包含的子查询被标记为该值,MySQL 会递归执行这些子查询,把结果放在临时表
- union:若第二个 select 出现在 union 之后,则被标记为该值。若 union 包含在 from 的子查询中,外层
select 被标记为 derived - union result:从 union 表获取结果的 select
-
table:显示该行数据是关于哪张表
-
partitions:匹配的分区
-
type:表的连接类型,其值,性能由高到底排列如下:
- system:表只有一行记录,相当于系统表
- const:通过索引一次就找到,只匹配一行数据
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键或唯一索引扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行。用于=、< 或 > 操作符带索引的列
- range:只检索给定范围的行,使用一个索引来选择行。一般使用between、>、<情况
- index:只遍历索引树
- ALL:全表扫描,性能最差
注:前5种情况都是理想情况的索引使用情况。通常优化至少到range级别,最好能优化到 ref
-
possible_keys:指出 MySQL 使用哪个索引在该表找到行记录。如果该值为 NULL,说明没有使用索引,可以建立索引提高性能
-
key:显示 MySQL 实际使用的索引。如果为 NULL,则没有使用索引查询
-
key_len:表示索引中使用的字节数,通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好 显示的是索引字段的最大长度,并非实际使用长度
-
ref:显示该表的索引字段关联了哪张表的哪个字段
-
rows:根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数,数值越小越好
-
filtered:返回结果的行数占读取行数的百分比,值越大越好
-
extra:包含不合适在其他列中显示但十分重要的额外信息,常见的值如下:
- using filesort:说明 MySQL 会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。出现该值,应该优化
SQL - using temporary:使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by
和分组查询 group by。出现该值,应该优化 SQL - using index:表示相应的 select 操作使用了覆盖索引,避免了访问表的数据行,效率不错
- using where:where 子句用于限制哪一行
- using join buffer:使用连接缓存
- distinct:发现第一个匹配后,停止为当前的行组合搜索更多的行
数据库表结构设计
-
使用可以存下数据最小的数据类型
-
使用简单的数据类型。int 要比 varchar 类型在mysql处理简单
-
尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int
-
尽可能使用 not null 定义字段,因为 null 占用4字节空间
-
尽量少用 text 类型,非用不可时最好考虑分表
-
尽量使用 timestamp 而非 datetime
-
单表不要有太多字段,建议在 20 以内
适合使用索引的场景
-
主键自动创建唯一索引
-
频繁作为查询条件的字段
-
查询中与其他表关联的字段
-
查询中排序的字段
-
查询中统计或分组字段