MYSQL——

摘要

主要是分享在数据千万级别下的是SQL的优化问题。

参考MySQL 对于千万级的大表要怎么优化？ - 知乎

面试官：来说说MySQL的常见优化方案…… - 知乎

问题场景的SQL使用

为了对下面列举的一些优化进行测试，下面针对已有的一张表进行说明。

• 表名：order_history
• 描述：某个业务的订单历史表
• 主要字段：unsigned int id，tinyint(4) int type
• 字段情况：该表一共37个字段，不包含text等大型数据，最大为varchar(500)，id字段为索引，且为递增。
• 数据量：5709294
• MySQL版本：5.7.16 线下找一张百万级的测试表可不容易，如果需要自己测试的话，可以写shell脚本什么的插入数据进行测试。以下的 sql 所有语句执行的环境没有发生改变，下面是基本测试结果：

select count(*) from orders_history;

三次查询时间分别为：

• 8903 ms
• 8323 ms
• 8401 ms

解决方法一：一般分页查询

最基本的分页方式：

SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT ... 

子查询的分页方式：

随着数据量的增加，页数会越来越多，查看后几页的SQL就可能类似：

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10

一言以蔽之，就是越往后分页，LIMIT语句的偏移量就会越大，速度也会明显变慢。此时，我们可以通过子查询的方式来提高分页效率，大致如下：

SELECT * FROM articles WHERE  id >=  
(SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10 

JOIN分页方式:经过测试，join分页和子查询分页的效率基本在一个等级上，消耗的时间也基本一致。

SELECT * FROM `content` AS t1   
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2   
WHERE t1.id  (pageNum*10) LIMIT M
适应场景: 适用于数据量多的情况(元组数上万)
原因: 索引扫描,速度会很快. 有朋友提出: 因为数据查询出来并不是按照pk_id排序的，所以会有漏掉数据的情况，只能方法3
方法3: 基于索引再排序 
语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M
适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER BY后的列对象是主键或唯一所以,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)
原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待...).
方法4: 基于索引使用prepare 
第一个问号表示pageNum，第二个？表示每页元组数
 
语句样式: MySQL中,可用如下方法: PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (？* ？) ORDER BY id_pk ASC LIMIT M
适应场景: 大数据量
原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。
方法5: 利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描 
比如: 读第1000到1019行元组(pk是主键/唯一键).
 
SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20
 
方法6: 利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组.  
比如(id是主键/唯一键,蓝色字体时变量)
 
利用子查询示例:
 
SELECT * FROM your_table WHERE id