江苏龙网

  1. 主页 > 生活百科 > >

SQL优化最干货总结 - MySQL( 二 )

mysql

如果是子查询，可以用exists代替 。详情见《MySql中如何用exists代替in》如下：
-- 不走索引select * from A where A.id in (select id from B);-- 走索引select * from A where exists (select * from B where B.id = A.id); 
3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描 。如下：
SELECT * FROM t WHERE id = 1 OR id = 3优化方式：可以用union代替or 。如下：
SELECT * FROM t WHERE id = 1   UNIONSELECT * FROM t WHERE id = 3 
4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描 。如下：
SELECT * FROM t WHERE score IS NULL优化方式：可以给字段添加默认值0，对0值进行判断 。如下：
SELECT * FROM t WHERE score = 0 
5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描 。
【SQL优化最干货总结 - MySQL】可以将表达式、函数操作移动到等号右侧 。如下：
-- 全表扫描SELECT * FROM T WHERE score/10 = 9-- 走索引SELECT * FROM T WHERE score = 10*9 
6. 当数据量大时，避免使用where 1=1的条件 。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描 。如下：
SELECT username, age, sex FROM T WHERE 1=1优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and 。
7. 查询条件不能用 <> 或者 !=
使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件 。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替 。
8. where条件仅包含复合索引非前置列
如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引 。
select col1 from table where key_part2=1 and key_part3=29. 隐式类型转换造成不使用索引
如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引 。
select col1 from table where col_varchar=123; 10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序
-- 不走age索引SELECT * FROM t order by age; -- 走age索引SELECT * FROM t where age > 0 order by age;对于上面的语句，数据库的处理顺序是：
第一步：根据where条件和统计信息生成执行计划，得到数据 。
第二步：将得到的数据排序 。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引 。如果是，则可以利用索引顺序而直接取得已经排好序的数据 。如果不是，则重新进行排序操作 。
第三步：返回排序后的数据 。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作 。
这个结论不仅对order by有效，对其他需要排序的操作也有效 。比如group by 、union 、distinct等 。
11. 正确使用hint优化语句
MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引 。一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息 。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划 。
1、USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引 。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
2、IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint 。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
3、FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为Hint 。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引 。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引 。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引 。
例如：
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;


推荐阅读


上一篇：正岩水仙茶盒多少钱,白茶多少钱盒

下一篇：一篇文章带你搞懂Python中的类