mysql加索引及索引失效的情况

前言：B+TREE索引的本质是多路绝对平衡查找树，磁盘指针，相当于书的目录，索引不是越多越好。

一：如何加索引

1.PRIMARY  KEY（主键索引）
        mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
        mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
3.INDEX(普通索引)
        mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
        mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
        mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

二：索引失效的情况

1.like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2.or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3.组合索引，不是使用第一列索引，索引失效。

4.数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。

5.在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。

6.在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

7.对索引字段进行计算操作、字段上使用函数。

8.就是你用explain分析时候里面的rows字段，如果它和rows总数有关系。
如果这个行数超过了总数的30%！！！！mysql就认为索引失效！！！

三：EXPLAIN 查看sql执行计划（possible key:可能命中的索引；key:实际命中的索引）

EXPLAIN select * from sr_main where mhzsfz = '330127199210021719' ; -- 0.227s
EXPLAIN select * from sr_main where mhzsfz = '330127199210021719' and sys_scbj = 0 and sys_spzt = 1; -- 0.142s

索引的等级：system>const>eq_ref>ref>range >index>ALL

type为range以上才是有效索引

select_type :查询类型

simple简单查询 primary 主查询 UNION 第二个或者后面的查询语句。SUBQUERY : 子查询中的第一个select]

table ：输出结果的表

type:查询级别

type＝ALL　全表扫描，

type＝index 索引全扫描，遍历整个索引来查询匹配的行
type=range 索引范围扫描，常见于　<,<=,>,>=,between,in等操作符。
　　例
　　　　explain select * from adminlog where id>0 ,
　　　　explain select * from adminlog where id>0 and id<=100
　　　　explain select * from adminlog where id in (1,2)

type=ref 使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行。ref还经常出现在JOIN操作中
type=eq_ref 类似于ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中有一条记录匹配；简单来说，说是多表连接中使用　主建或唯一健作为关联条件
type=const/system 单表中最多有一个匹配行。主要用于比较primary key [主键索引]或者unique[唯一]索引,因为数据都是唯一的，所以性能最优。条件使用=。
type=NULL　不用访问表或者索引，直接就能够得到结果

 

4、possible_keys : 可能使用的索引列表.

5、key : 实现执行使用索引列表
6、key_len : 索引的长度
7、ref : 显示使用哪个列或常数与key一起从表中选择行。
8、row : 执行查询的行数，简单且重要，数值越大越不好，说明没有用好索引

四：索引建立的原则（针对b+tree数据结构，如innodb存储引擎）

1.B+Tree数据结构为，多路绝对平衡查找树，数据存储在叶子节点（普通b-tree数据存储在磁盘块中，一个磁盘块默认大小为16kb），树结构越矮胖，查找效率越高；

2.索引长度能少则少，因为加入分子为16kb，分母越小，树的枝干越多（矮胖原则）

3.索引不是越多越好，越全越好，一定要建立合适的，因为索引本身也消耗资源和性能

4.like 999%不一定命中索引（还得看索引的离散性，越是唯一越能命中，选择性越好），但是like '%999%'和‘%99999’一定不能命中索引

5.where条件中的not in和<>无法使用索引

6.匹配范围值，order by也可命中索引

7.多列指定查询，少用select *

8.联合索引（多列索引）如果不是按照索引最左列开始查找，无法使用索引。

9.联合索引中精确匹配最左前列并范围匹配另外其他列可以用到索引

10联合索引如果查询中有个列的范围查询，则其右边的所有列都无法使用索引。