SQL优化⼀键命中索引
项⽬开发中sql⼤家经常⽤到，表的索引也是，这些sql的运⾏性能是怎样的你知道么?中索引啦没?命中哪个索引?索引中有哪些是⽆效索引? 哪些会⾛索引，哪些必定不会⾛索引.
Mysql中是通过来分析低效sql的执⾏计划. 命令的使⽤很简单.(ps: 以下测试数据库为tc_test，放了53278条数据，主键为pripid-bigint；列uniscid-varchar；列regno-varchar，其中后⾯uniscid-regno两个为联合索引,enttype-varchar普通索引))
实例 : EXPLAIN SELECT * FROM tc_test WHERE regno ='1'
执⾏结果:
执⾏的每项说明:
1. 这是SELECT的查询序列号
2. select_type就是select的类型，有：
1. SIMPLE：简单SELECT(不使⽤UNION或⼦查询等)
2. PRIMARY：最外⾯的SELECT.
3. UNION：UNION中的第⼆个或后⾯的SELECT语句
exists英文
4. DEPENDENT UNION：UNION中的第⼆个或后⾯的SELECT语句，取决于外⾯的查询
边框简笔画手绘简单又大方
5. UNION RESULT：UNION的结果
6. SUBQUERY：⼦查询中的第⼀个SELECT
7. DEPENDENT SUBQUERY：⼦查询中的第⼀个SELECT，取决于外⾯的查询
8. DERIVED：导出表的SELECT(FROM⼦句的⼦查询)
3. table：显⽰这⼀⾏的数据是关于哪张表的
4. type：这列最重要，显⽰了连接使⽤了哪种类别，有⽆使⽤索引，是使⽤Explain命令分析性能瓶颈的关键项之⼀
ALL index range ref eq_ref const,system NULL
mysql面试题sql优化从左到右，性能由最差到最好.
1. type=ALL 全表扫描
2. type = index 索引全扫描，遍历整个索引来查询匹配的⾏
3. type=range 索引范围扫描，常见于　<,<=,>,>=,between,in等操作符.
1. 例:EXPLAIN SELECT * FROM tc_test WHERE pripid >145000100002926688,
4. type=ref 使⽤⾮唯⼀索引或唯⼀索引的前缀扫描，返回匹配某个单独值的记录⾏。ref还经常出现在JOIN操作中
5. type=eq_ref 类似于ref，区别就在使⽤的索引是唯⼀索引，对于每个索引键值，表中有⼀条记录匹配；简单来说，说是多表连
接中使⽤　主建或唯⼀健作为关联条件
6. type=const/system 单表中最多有⼀个匹配⾏。主要⽤于⽐较primary key [主键索引]或者unique[唯⼀]索引，因为数据都是唯排序算法的一般选择规则
⼀的，所以性能最优。条件使⽤=。
1. 例: EXPLAIN SELECT * FROM tc_test WHERE pripid = 145000100002926688
7. type=NULL  不⽤访问表或者索引列表，直接就能得到结果
1. 例: explain select 1 from dual 类型type 还有其他值　如ref_or_null : 与ref 类似，区别在于条件中包含对NULL的查
询.  index_merge : 索引合并优化, unique_subquery : in的后⾯是⼀个主键字段的⼦查询。index_subquery: 与
unique_subquery 类似，区别在于in的后⾯是查询⾮唯⼀索引字段的⼦查询
5. possible_keys: 指出MySQL能使⽤哪个索引在该表中到⾏
6. key：显⽰MySQL实际决定使⽤的键（索引）。如果没有选择索引，键是NULL
7. key_len：索引的长度
8. ref：显⽰使⽤哪个列或常数与key⼀起从表中选择⾏
8的ascii码
9. row：显⽰MySQL认为它执⾏查询时必须检查的⾏数.数值越⼤越不好，说明没有⽤好索引
oncreate方法
10. Extra：该列包含Mysql解决查询的详细信息.
1. Not exists
2. range checked for each record (没有到合适的索引)
3. using index 只使⽤索引树中的信息⽽不需要进⼀步搜索读取实际的⾏来检索表中的信息。就是建议取索引列。这样就可以不要
通过索引去实际表中数据了。直接返回索引列的数据。⼀次查询。否则就是索引表查⼀次，实际表中查⼀次。
4. using temporary 为了解决查询，MySQL需要创建⼀个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的
GROUP BY和ORDER BY⼦句时
⽆效索引：数据变化不⼤的列。如XX类型，是否有效，项⽬ID等列的索引都是⽆效的。这些⽆效索引还是影响Insert 、Update、Delete 语句的性能。因为这些语包的执⾏都要对索引表进⾏更新。⼜因为这些表的值变化不⼤，数据库很难为他们合理分配索引。所以影响语句的性能。
IN,OR 是否会⾛索引: ⼀条SQL会不会⾛索引⼀个看条件使⽤的运算符，另⼀个看有没有索引。所以SQL会不会⾛索引和IN.OR,group by 没有关系。
什么运算符不⾛索引:    <,>,!=
联合索引怎么能命中索引?
1. 使⽤联合索引的全部索引键，可触发索引的使⽤.
1. 例如: EXPLAIN SELECT * FROM tc_test WHERE UNISCID ='91450103775956630Y' AND
regno='450100000017535'
2.
2. 使⽤联合索引的前缀部分索引键，如“key_part_1 <op>常量”，可触发索引的使⽤.
1. 例: EXPLAIN SELECT * FROM tc_test WHERE UNISCID ='91450103775956630Y'
2.
3. 使⽤部分索引键，但不是联合索引的前缀部分，如“key_part_2 <op>常量”，不可触发索引的使⽤.
1. 例:EXPLAIN SELECT * FROM tc_test WHERE regno='450100000017535'
2.
4. 使⽤联合索引的全部索引键，但索引键不是AND操作，不可触发索引的使⽤.
1. 例: EXPLAIN SELECT * FROM tc_test WHERE UNISCID ='91450103775956630Y' OR
regno='450100000017535'
2.
OR 有个点要注意下：就or前后两个条件都要有索引 整个SQL才会使⽤索引。只要有⼀个条件没索引
那么整个SQL都不使⽤索引。如果出现or的⼀个条件没有索引时，建议使⽤union  , 如果可以⽤union all 代替union 那就更好了.(ps：最⼤的坑点，如果数据库的类型和你传⼊的类型不⼀致，也不会⾛索引...)
例:EXPLAIN SELECT * FROM tc_test WHERE enttype ='2'
EXPLAIN SELECT * FROM tc_test WHERE enttype =2
下⾯给⼤家总结30条优化sql箴⾔:
1. 应尽量避免在 where ⼦句中使⽤!=或<>操作符，否则将引擎放弃使⽤索引⽽进⾏全表扫描。
2. 对查询进⾏优化，应尽量避免全表扫描，⾸先应考虑在 where 及 order by 涉及的列上建⽴索引。
3. 应尽量避免在 where ⼦句中对字段进⾏ null 值判断，否则将导致引擎放弃使⽤索引⽽进⾏全表扫描。如:
1. select id from t where num is null
2. 可以在num上设置默认值为0，确保表中num列没有null值，然后这样查询:  select id from t where num =0
4. 尽量避免在 where ⼦句中使⽤ or 来连接条件，前后都得有索引否则可能导致引擎放弃使⽤索引⽽进⾏全表扫描(上⾯已有例⼦).
5. 下⾯的查询也将导致全表扫描：(不能前置百分号)
1. 例:select id from t where name like ‘%product_no%’    若要提⾼效率可以考虑全⽂索引
2. 最好改为: select id from t where name like 'product_no%'
6. 对于in , not in 要慎⽤.  例 :  EXPLAIN SELECT * FROM tc_test WHERE pripid NOT IN (1,2,3)
7.应尽量避免在 where ⼦句中对字段进⾏表达式操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如：
EXPLAIN SELECT * FROM tc_test WHERE pripid/2=100
应改为: select * from t where pripid=200
8. 应尽量避免在where⼦句中对字段进⾏函数操作，这将导致引擎放弃使⽤索引⽽进⾏全表扫描。如:
EXPLAIN SELECT pripid FROM tc_test WHERE SUBSTRING(enttype,1,2)='41' –enttype以41开头的pripid
应改为: expalin select pripid from tc_test where pripid like '41%'
9. 不要在 where ⼦句中的“=”左边进⾏函数、算术运算或其他表达式运算，否则系统将可能⽆法正确使⽤索引
10. 在使⽤索引字段作为条件时，如果该索引是复合索引，那么必须使⽤到该索引中的第⼀个字段作为条件时才能保证系统使⽤该索引，否则该索引将不会被使 ⽤，并且应尽可能的让字段顺序与索引顺序相⼀致。
11. 很多时候⽤ exists 代替 in 是⼀个好的选择：
select num from a where num in(select num from b)
⽤下⾯的语句替换：
select num from a where exists(select 1 from b where num=a.num)
12. 并不是所有索引对查询都有效，SQL是根据表中数据来进⾏查询优化的，当索引列有⼤量数据重复时，SQL查询可能不会去利⽤索引，如⼀表中有字段 sex，male、female⼏乎各⼀半，那么即使在sex上建了索引也对查询效率起不了作⽤
13. 索引并不是越多越好，索引固然可以提⾼相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update
时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况⽽定。⼀个表的索引数较好不要超过6个，若太多则应考虑⼀些不常使⽤到的列上建的索引是否有必要.
14. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，⼀旦该列值改变将导致整个表记录的顺序的调整，会耗费相当⼤的资源。若应⽤系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为clustered 索引
15.尽量使⽤数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个⽐较字符串中每⼀个字符，⽽对于数字型⽽⾔只需要⽐较⼀次就够了!
16. 尽可能的使⽤ varchar/nvarchar 代替 char/nchar ，因为⾸先变长字段存储空间⼩，可以节省存储空间，其次对于查询来说，在⼀个相对较⼩的字段内搜索效率显然要⾼些。
17. 任何地⽅都不要使⽤ select * from t ，⽤具体的字段列表代替“*”，不要返回⽤不到的任何字段。
18. 尽量避免向客户端返回⼤数据量，若数据量过⼤，应该考虑相应需求是否合理。
19. 尽量避免⼤事务操作，提⾼系统并发能⼒.
20. MySql的⼦查询实现的⾮常糟糕。最糟糕的⼀类查询是WHERE条件中包含IN()的⼦查询语句。
应该尽可能⽤关联替换⼦查询，可以提⾼查询效率。
优化在路上，我们在前⾏~
作者简介：就职于甜橙⾦融信息技术部，负责java后端开发⼯作，喜欢研究新的技术服务于业务需求，保证服务的⾼并发,⾼可⽤,⾼性能.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。