Mysql中有哪些索引和各自的用处想必大家都很清楚了吧!

项目开发中sql大家经常用到,表的索引也是,这些sql的运行性能是怎样的你知道么?中索引啦没?命中哪个索引?索引中有哪些是无效索引? 哪些会走索引,哪些必定不会走索引.

Mysql中是通过explain神奇命令来分析低效sql的执行计划. 命令的使用很简单.(ps: 以下测试数据库为tc_test,放了53278条数据,主键为pripid-bigint;列uniscid-varchar;列regno-varchar,其中后面uniscid-regno两个为联合索引,enttype-varchar普通索引))

实例 : EXPLAIN SELECT * FROM tc_test WHERE regno ='1'

执行结果: 

索引命中率 sql索引命中_字段

执行的每项说明:

  1. 这是SELECT的查询序列号
  2. select_type就是select的类型，有：
  1. SIMPLE：简单SELECT(不使用UNION或子查询等)
  2. PRIMARY：最外面的SELECT.
  3. UNION：UNION中的第二个或后面的SELECT语句
  4. DEPENDENT UNION：UNION中的第二个或后面的SELECT语句，取决于外面的查询
  5. UNION RESULT：UNION的结果
  6. SUBQUERY：子查询中的第一个SELECT
  7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询
  8. DERIVED：导出表的SELECT(FROM子句的子查询)
  1. table:显示这一行的数据是关于哪张表的
  2. type:这列最重要，显示了连接使用了哪种类别,有无使用索引，是使用Explain命令分析性能瓶颈的关键项之一

ALL

index

range

ref

eq_ref

const,system

NULL

  1. 从左到右,性能由最差到最好.
  1. type=ALL 全表扫描
  2. type = index 索引全扫描,遍历整个索引来查询匹配的行
  4. type=range 索引范围扫描,常见于　<,<=,>,>=,between,in等操作符.
    1. 例:EXPLAIN SELECT * FROM tc_test WHERE pripid >145000100002926688,
  6. type=ref 使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
  7. type=eq_ref 类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件
  9. type=const/system 单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。 
    1. 例: EXPLAIN SELECT * FROM tc_test WHERE pripid = 145000100002926688
  12. type=NULL  不用访问表或者索引列表,直接就能得到结果
    1. 例: explain select 1 from dual 类型type 还有其他值　如ref_or_null : 与ref 类似，区别在于条件中包含对NULL的查询.   index_merge : 索引合并优化, unique_subquery : in的后面是一个主键字段的子查询。index_subquery: 与unique_subquery 类似,区别在于in的后面是查询非唯一索引字段的子查询
  1. possible_keys: 指出MySQL能使用哪个索引在该表中找到行
  2. key:显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL
  3. key_len:索引的长度
  4. ref:显示使用哪个列或常数与key一起从表中选择行
  5. row:显示MySQL认为它执行查询时必须检查的行数.数值越大越不好，说明没有用好索引
  6. 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'
  1. 使用联合索引的前缀部分索引键，如“key_part_1 <op>常量”，可触发索引的使用.
  1. 例: EXPLAIN SELECT * FROM tc_test WHERE UNISCID ='91450103775956630Y'
  1. 使用部分索引键，但不是联合索引的前缀部分，如“key_part_2 <op>常量”，不可触发索引的使用.
  1. 例:EXPLAIN SELECT * FROM tc_test WHERE regno='450100000017535'
  1. 使用联合索引的全部索引键，但索引键不是AND操作，不可触发索引的使用.
  1. 例: EXPLAIN SELECT * FROM tc_test WHERE UNISCID ='91450103775956630Y' OR regno='450100000017535'

OR 有个点要注意下:就or前后两个条件都要有索引 整个SQL才会使用索引。只要有一个条件没索引那么整个SQL都不使用索引。如果出现or的一个条件没有索引时,建议使用union   , 如果可以用union all 代替union 那就更好了.(ps:最大的坑点,如果数据库的类型和你传入的类型不一致,也不会走索引...)

例:EXPLAIN SELECT * FROM tc_test WHERE enttype ='2'

索引命中率 sql索引命中_子查询_02

EXPLAIN SELECT * FROM tc_test WHERE enttype =2

索引命中率 sql索引命中_索引命中率_03

 

下面给大家总结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
  1. 尽量避免在 where 子句中使用 or 来连接条件，前后都得有索引否则可能导致引擎放弃使用索引而进行全表扫描(上面已有例子).
  2. 下面的查询也将导致全表扫描：(不能前置百分号)
  1. 例:select id from t where name like ‘%product_no%’     若要提高效率可以考虑全文索引
  2. 最好改为: select id from t where name like 'product_no%'      
  1. 对于in , not in 要慎用.   例 :  EXPLAIN SELECT * FROM tc_test WHERE pripid NOT IN (1,2,3)

索引命中率 sql索引命中_子查询_04

 

     7.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

EXPLAIN SELECT * FROM tc_test WHERE pripid/2=100

索引命中率 sql索引命中_索引命中率_05

应改为: select * from t where pripid=200

索引命中率 sql索引命中_字段_06

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%'

索引命中率 sql索引命中_索引命中率_07

索引命中率 sql索引命中_字段_08

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后端开发工作,喜欢研究新的技术服务于业务需求,保证服务的高并发,高可用,高性能.