第二节 优化sql语句--优化select语句(where子句部分)

数据库的核心应用是执行sql语句

本调优指南旨在帮助你提升各种类型的mysql语句的执行性能

select类型的查询执行数据库中的所有查找操作，调优select语句是sql优化的重中之重，目标是页面访问达到压秒级别.

包括CREATE TABLE...AS SELECT, INSERT INTO...SELECT, delete中的where子句

优化目标：

• 让select ... where查询更快，首先检查是否能通过新增索引来解决，为了节省磁盘空间，应该让少量的索引尽量覆盖所有的查询需求
• 分步调优
• 尽量不要出现全表扫描，尤其是大表
• analyze table更新统计信息(影响执行计划)
• 配置参数(特定存储引擎)，索引技术，调优技术
• 只读事务调优
• 看执行计划
• 调整IBP
• 尽管内存够用，也要调优你的sql，使其查询占用更少的内存
• 处理锁问题，避免影响其他会话的查询速度

8.2.1.1 WHERE Clause Optimization

包含select、delete、update中的where子句

• At one time, a scan was used based on whether the best index spanned more than 30% of the table, but a fixed percentage no longer determines the choice between using an index or a scan. The optimizer now is more complex and bases its estimate on additional factors such as table size, number of rows, and I/O block size

• In some cases, MySQL can read rows from the index without even consulting the data file. If all columns used from the index are numeric, only the index tree is used to resolve the query.  --索引覆盖

• Before each row is output, those that do not match the HAVING clause are skipped.

查询非常快的例子

SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;

覆盖索引

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT MAX(key_part2) FROM tbl_name GROUP BY key_part1;

通过索引不用执行额外的排序操作

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;

8.2.1.2 Range Optimization

单列索引范围扫描