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'; -- 不走索引
-- 查询3EXPLAIN SELECT * FROM employee WHERE name='Joye'; -- 走索引
以上查询只有查询3才走索引。最佳左前缀法则可以理解成火车的车头、中间车厢、车尾的关系。策略三、不在索引列上做任何操作在索引列上计算、函数、类型转换等会导致索引失效,转向全表扫描。-- 查询1EXPLAIN SELECT FROM employee WHERE name='Joye'; -- 走索引
-- 查询2EXPLAIN SELECT FROM employee WHERE LEFT(name,4)='Joye'; -- 不走索引
-- 查询3EXPLAIN SELECT * FROM employee WHERE age2 = 13; -- 不走索引
在索引字段上使用函数、任何计算表达式均会导致索引失效。策略四、尽量多用覆盖索引尽量使用覆盖索引(只访问索引列的查询,查询列和索引列一致),减少select开销。全部数据直接通过索引就能获取到,大大提高查询效率。-- 查询1
EXPLAIN SELECT age,pos FROM employee WHERE name = 'joye'; -- 走索引
-- 查询2EXPLAIN SELECT pos FROM employee WHERE name = 'joye'; -- 走索引
-- 查询3
EXPLAIN SELECT age,add_time FROM employee WHERE name = 'joye'; -- 不走索引
策略五、范围条件放最后查询优化器不会使用索引中范围条件右边的列,所以范围条件放最后能被主动采用。-- 查询1EXPLAIN SELECT FROM employee WHERE NAME='joye' AND age=22 AND pos='manager'; -- 走索引
-- 查询2EXPLAIN SELECT * FROM employee WHERE NAME='joye' AND age>22 AND pos='manager';-- 不充分走索引
-- 查询3EXPLAIN SELECT FROM employee WHERE NAME='joye' AND pos='manager' AND age>22; -- 充分走索引策略六、不等于(! <>) 要慎用mysql在使用不等于(!= 或者 <>)的时候无法使用索引,导致全表扫描-- 查询1
EXPLAIN SELECT FROM employee WHERE name != 'joye'; -- 不走索引
-- 查询2EXPLAIN SELECT * FROM employee WHERE NAME <>'joye'; -- 不走索引
若要使用不等号,尽量采用覆盖索引;-- 查询3EXPLAIN SELECT name,age,pos FROM employee WHERE NAME != 'joye'; -- 走索引
-- 查询4EXPLAIN SELECT NAME,age,pos FROM employee WHERE NAME <> 'joye'; -- 走索引
策略七、IN/NOT IN要慎用
-- 查询1EXPLAIN SELECT FROM employee WHERE name IN('joye','9000'); -- 不走索引
-- 查询2EXPLAIN SELECT FROM employee WHERE name NOT IN('joye','9000'); -- 不走索引
查询1和查询2不走索引的原因是IN/NOT IN 匹配让索引失效,转向全表扫描 。若需要使用IN/NOT IN,则同时尽量采用覆盖索引或就使用场景使用JOIN连表方式-- 查询3EXPLAIN SELECT age,pos FROM employee WHERE name IN('joye','9000'); -- 走索引
-- 查询4EXPLAIN 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
-- 查询3EXPLAIN SELECT FROM employee2 WHERE NAME IS NULL; -- 走索引
策略九、LIKE查询要小心LIKE以通配符开头(‘%abc ’)mysql索引失效会变成全表扫描操作。-- 查询1
EXPLAIN SELECT * FROM employee WHERE name LIKE '%july%' -- 不走索引
-- 查询2EXPLAIN SELECT FROM employee WHERE name LIKE '%july' -- 不走索引
-- 查询3EXPLAIN SELECT FROM employee WHERE name LIKE 'july%'-- 走索引
策略十、字符类型字段加引号字符窜不加引号会导致索引失效。-- 查询1EXPLAIN SELECT * FROM employee WHERE name = 9000; -- 不走索引
-- 查询2EXPLAIN SELECT FROM employee WHERE name = '9000' -- 走索引策略十一、OR改UNION 效率高WHERE查询或子查询条件中使用OR,会导致索引失效,转向全表数据扫描。-- 查询1EXPLAIN SELECT FROM employee WHERE name='joye' OR name='andy3'; -- 不走索引
-- 查询2
EXPLAINSELECT * FROM employee WHERE name='joye'UNIONSELECT * FROM employee WHERE name='andy3'; -- 走索引
总结以上仅为SQL语句优化领域的关键优化指标和技巧。在具体项目的优化中,我们可能会综合使用以上多个策略和手段完成一个SQL的优化;如何用好这些策略完全取决于我们在项目实战中循序渐进的优化、尝试、摸索、总结。以后会在项目实践中,分享更多综合性大数据问题优化实战案例,请继续关注!