mysql 常见优化

一、 使用延迟查询优化 limit [offset], [rows]

经常出现类似以下的 SQL 语句:

SELECT * FROM film LIMIT 100000, 10

offset 特别大!

LIMIT 能很好地解决分页问题，但如果 offset 过大的话，会造成严重的性能问题，原因主要是因为 MySQL 每次会把一整行都扫描出来，扫描 offset 遍，找到 offset 之后会抛弃 offset 之前的数据，再从 offset 开始读取 10 条数据，显然，这样的读取方式问题。

可以通过延迟查询的方式来优化

假设有以下 SQL,有组合索引（sex, rating）

SELECT <cols> FROM profiles where sex='M' order by rating limit 100000, 10;

则上述写法可以改成如下写法

SELECT <cols> 
  FROM profiles 
inner join
(SELECT id form FROM profiles where x.sex='M' order by rating limit 100000, 10)
as x using(id);

这里利用了覆盖索引的特性，先从覆盖索引中获取 100010 个 id，再丢充掉前 100000 条 id，保留最后 10 个 id 即可，丢掉 100000 条 id 不是什么大的开销，所以这样可以显著提升性能

二、 利用 LIMIT 1 取得唯一行

数据库引擎只要发现满足条件的一行数据则立即停止扫描，，这种情况适用于只需查找一条满足条件的数据的情况（如查询是否存在时，可以使用limit 1，而非count(*)）。

三、 注意组合索引，要符合最左匹配原则才能生效

假设存在这样顺序的一个联合索引“col_1, col_2, col_3”。这时，指定条件的顺序就很重要。

 SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;
 SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;
 SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;

前面两条会命中索引，第三条由于没有先匹配 col_1，导致无法命中索引， 另外如果无法保证查询条件里列的顺序与索引一致，可以考虑将联合索引 拆分为多个索引。

四、使用 LIKE 谓词时，只有前方一致的匹配才能用到索引（最左匹配原则）

 SELECT * FROM SomeTable WHERE col_1 LIKE '%a';
 SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';
 SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';

上例中，只有第三条会命中索引，前面两条进行后方一致或中间一致的匹配无法命中索引

五、 简单字符串表达式

模型字符串可以使用 _ 时， 尽可能避免使用 %, 假设某一列上为 char(5)

不推荐

SELECT 
    first_name, 
    last_name,
    homeroom_nbr
  FROM Students
 WHERE homeroom_nbr LIKE 'A-1%';

推荐

SELECT first_name, last_name
homeroom_nbr
  FROM Students
 WHERE homeroom_nbr LIKE 'A-1__'; --模式字符串中包含了两个下划线

六、 如有必要，使用 force index() 强制走某个索引

SELECT col from orders FORCE INDEX(order_no) WHERE order_no = 'xxxxx' ;

七、批量插入，速度更快

推荐用

INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a'),(2,3,'b');

不推荐用

INSERT INTO TABLE (id, user_id, title) VALUES (1, 2, 'a');
INSERT INTO TABLE (id, user_id, title) VALUES (2,3,'b');

注：本文来自 IT牧场 公众号