MySQL的故事——查询性能优化

查询性能优化

---

一、查询优化器的提示(hint)

HIGH_PRIORITY和LOW_PRIORITY
这个提示告诉MySQL，当多个语句同时访问某一个表时，哪些语句的优先级相对高些，哪些相对低些
DELAYED
这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入。
STRAIGHT_JOIN
这个提示可以放在SELECT关键字之后，也可以放在任何两个关联表的名字之间。第一个用法是让查询中的所有表按照在语句中出现的顺序进行关联。第二个用法则是固定其前后两个表的关联顺序。
SQL_SMALL_RESULT和SQL_BIG_RESULT
只对select语句有效。它们告诉优化器group by或者distinct查询如何使用临时表及排序。
SQL_BUFFER_RESULT
这个提示告诉优化器将查询结果放入一个临时表，然后尽可能快地释放表锁。
SQL_CACHE和SQL_NO_CACHE
告诉MySQL这个结果集是否应该缓存在查询缓存中。
SQL_CALC_FOUND_ROWS
它会让MySQL返回的结果集包含更多的信息。
FOR UPDATE和LOCK IN SHARE MODE
使用该提示会对符合查询条件的数据加行锁。
USE INDEX、IGNORE INDEX和FORCE INDEX
这几个提示会告诉优化器使用或者不使用哪些索引来查询记录（例如，在决定关联顺序的时候使用哪个索引。）
optimizer_search_depth
这个参数控制优化器在穷举执行计划时的限度。
optimizer_prune_level
该参数是默认打开的，这让优化器会根据需要扫描的行数来决定是否跳过某些执行计划。
optimizer_switch
这个变量包含了一些开启/关闭优化器特性的标志位。

二、优化特定类型的查询

优化count()查询
count()可以统计某个列的数值，也可以统计行数。
在统计列值时要求列是非空的(不统计NULL)。count(*)统计行数。
SELECT
COUNT(color = ‘blue’ OR NULL) AS blue,
COUNT(color = ‘red’ OR NULL) AS red
FROM
items

SELECT
sum(IF(color = ‘blue’, 1, 0)) AS blue,
sum(IF(color = ‘red’, 1, 0)) AS red
FROM
items

SELECT
sum(color = ‘blue’) AS blue,
sum(color = ‘red’) AS red
FROM
items

优化limit分页
偏移量非常大的时候，例如LIMIT 10000,20可能需要查询到10020条结果，然后返回20条。
可以使用书签记录上一次查询的位置，那么下次查询就会从书签位置开始扫描。
select * from user where id>10000 limit 10000,20

使用用户自定义变量
定义用户自定义变量：SET @one :=1;
SET@last_week :=CURRENT_DATE-INTERVAL 1 WEEK;
不过在编写SQL语句使用用户自定义变量时需要注意，在对变量赋值和读取变量可能是在查询的不同阶段，所以最好把变量的赋值和读取写在一起，或者要搞明白SQL语句的执行过程。

---