背景
全球访问量最大的 20 家网站, 绝大多数使用 MySQL, 有两个特例的 live.com 和 bing 是微软旗下的网站. 它使用的是 MSSQL, 并不是他们使用不了 MySQL, 而是他要支持自己的数据库. 毫无疑问 MySQL 是当今非常流行的关系数据库之一, 不仅因为其绝大多数版本的开源, 而且支持多存储引擎, 快速, 稳定地运行于各种服务器环境. MySQL 查询分析优化引擎作为其中核心模块的一部分, 占有举足轻重的地位, 因此我们今天了解和研究它就非常重要了.
MySQL 服务器操作层架构
MySQL 语句的优化就发生 MySQL Server 服务器架构的操作层, 这层具体的执行流程是:
这层主要的功能是: SQL 语句的解析, 优化, 缓存的查询, MySQL 内置函数的实现, 跨存储引擎功能(所谓跨存储引擎就是说每个引擎都需提供的功能(引擎需对外提供接口)), 例如: 存储过程, 触发器, 视图等.
1. 如果是查询语句(select 语句), 首先会查询缓存是否已有相应结果, 有则返回结果, 无则进行下一步(如果不是查询语句, 同样调到下一步);
2. 解析查询, 创建一个内部数据结构(解析树), 这个解析树主要用来 SQL 语句的语义与语法解析;
3. 优化: 优化 SQL 语句, 例如重写查询, 决定表的读取顺序, 以及选择需要的索引等. 这一阶段用户是可以查询的, 查询服务器优化器是如何进行优化的, 便于用户重构查询和修改相关配置, 达到最优化. 这一阶段还涉及到存储引擎, 优化器会询问存储引擎, 比如某个操作的开销信息, 是否对特定索引有查询优化等.
十一大优化策略
演示准备
用以上演示道具来说明十一经典优化策略如下:
策略一, 索引字段尽量全值匹配
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name`='joye';-- 走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `name`='joye' AND age=25; -- 走索引
-- 查询 3
EXPLAIN SELECT * FROM employee WHERE `NAME`='joye' AND age=25 AND pos='dev'; -- 走索引
以上三种查询方式, 查询 3 的效率最高, 索引的使用程度也最高. 在表中建立索引后, 能用索引的要尽量都要用上.
策略二, 最佳左前缀法则
如果创建的索引为复合索引, 要遵守最左前缀法则. 查询从索引的最左前列开始并且不要跳过索引中的列.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE age=20 AND pos='dev'; -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE pos='dev'; -- 不走索引
-- 查询 3
EXPLAIN SELECT * FROM employee WHERE `name`='Joye'; -- 走索引
以上查询只有查询 3 才走索引. 最佳左前缀法则可以理解成火车的车头, 中间车厢, 车尾的关系.
策略三, 不在索引列上做任何操作
在索引列上计算, 函数, 类型转换等会导致索引失效, 转向全表扫描.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name`='Joye';-- 走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE LEFT(`name`,4)='Joye'; -- 不走索引
-- 查询 3
EXPLAIN SELECT * FROM employee WHERE `age`*2 = 13; -- 不走索引
在索引字段上使用函数, 任何计算表达式均会导致索引失效.
策略四, 尽量多用覆盖索引
尽量使用覆盖索引(只访问索引列的查询, 查询列和索引列一致), 减少 select 开销. 全部数据直接通过索引就能获取到, 大大提高查询效率.
-- 查询 1
EXPLAIN SELECT age,pos FROM employee WHERE `name` = 'joye'; -- 走索引
-- 查询 2
EXPLAIN SELECT pos FROM employee WHERE `name` = 'joye'; -- 走索引
-- 查询 3
EXPLAIN SELECT age,add_time FROM employee WHERE `name` = 'joye'; -- 不走索引
策略五, 范围条件放最后
查询优化器不会使用索引中范围条件右边的列, 所以范围条件放最后能被主动采用.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `NAME`='joye' AND age=22 AND pos='manager'; -- 走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `NAME`='joye' AND age>22 AND pos='manager';-- 不充分走索引
-- 查询 3
EXPLAIN SELECT * FROM employee WHERE `NAME`='joye' AND pos='manager' AND age>22; -- 充分走索引
策略六, 不等于(! <>) 要慎用
MySQL 在使用不等于 (!= 或者 <>) 的时候无法使用索引, 导致全表扫描
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name` != 'joye'; -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE NAME <>'joye'; -- 不走索引
若要使用不等号, 尽量采用覆盖索引;
-- 查询 3
EXPLAIN SELECT `name`,age,pos FROM employee WHERE NAME != 'joye'; -- 走索引
-- 查询 4
EXPLAIN SELECT NAME,age,pos FROM employee WHERE NAME <> 'joye'; -- 走索引
策略七, IN/NOT IN 要慎用
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name` IN('joye','9000'); -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `name` NOT IN('joye','9000'); -- 不走索引
查询 1 和查询 2 不走索引的原因是 IN/NOT IN 匹配让索引失效, 转向全表扫描 . 若需要使用 IN/NOT IN, 则同时尽量采用覆盖索引或就使用场景使用 JOIN 连表方式
-- 查询 3
EXPLAIN SELECT age,pos FROM employee WHERE `name` IN('joye','9000'); -- 走索引
-- 查询 4
EXPLAIN SELECT age,pos FROM employee WHERE `name` NOT IN('joye','9000'); -- 充分使用索引
策略八, NULL/NOT NULL 有影响
索引字段为 null 和 not null 对索引的影响, 可能导致索引失效(分情况).
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name` IS NULL; -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `name` IS NOT NULL; -- 不走索引
这时设置 `name` 字段允许为 null
-- 查询 3
EXPLAIN SELECT * FROM employee2 WHERE NAME IS NULL; -- 走索引
策略九, LIKE 查询要小心
LIKE 以通配符开头('%abc')MySQL 索引失效会变成全表扫描操作.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name` LIKE '%july%' -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `name` LIKE '%july' -- 不走索引
-- 查询 3
EXPLAIN SELECT * FROM employee WHERE `name` LIKE 'july%'-- 走索引
策略十, 字符类型字段加引号
字符窜不加引号会导致索引失效.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name` = 9000; -- 不走索引
-- 查询 2
EXPLAIN SELECT * FROM employee WHERE `name` = '9000' -- 走索引
策略十一, OR 改 UNION 效率高
WHERE 查询或子查询条件中使用 OR, 会导致索引失效, 转向全表数据扫描.
-- 查询 1
EXPLAIN SELECT * FROM employee WHERE `name`='joye' OR `name`='andy3'; -- 不走索引
-- 查询 2
- EXPLAIN
- SELECT * FROM employee WHERE `name`='joye'
- UNION
SELECT * FROM employee WHERE `name`='andy3'; -- 走索引
总结
以上仅为 SQL 语句优化领域的关键优化指标和技巧. 在具体项目的优化中, 我们可能会综合使用以上多个策略和手段完成一个 SQL 的优化; 如何用好这些策略完全取决于我们在项目实战中循序渐进的优化, 尝试, 摸索, 总结. 以后会在项目实践中, 分享更多综合性大数据问题优化实战案例, 请继续关注!
来源: http://blog.51cto.com/14815984/2512163