索引注意事项

(Updated: )
数据库

mysql索引失效情况

1.索引无法存储null值

2.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

3.对于多列索引，不是使用的第一部分，则不会使用索引

4.like查询以%开头

5.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

6.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

mysql索引常见注意事项

1.前导模糊查询无法命中索引

1
select * from test where b like '%asd%'

2.数据类型隐式转换无法命中索引

1
2
#设字段a为varchar 值为'1'
select * from test where a = 1

3.复合索引如果查询条件不包括索引最左边的字段，不能命中索引

1
2
#index(a,b)
select * from test where b = '666'

4.union in or都可以命中索引，建议用in

1
查询cpu消耗 or>in>union

5.or前面有索引，后面没索引，不会命中索引

6.负向条件无法命中索引

1
!=、<>、not in、not exists、not like

7.范围条件查询可以命中索引

1
<、<=、>、>=、between

8.范围列可以用到索引，但是范围列后面的列无法用到，查询条件中用到两个范围列，则只有第一个会用到索引

1
select * from test where a > 5 and b < 10

9.范围查询和等值查询同时存在，优先匹配等值查询的索引

1
select * from test where a = 1 and b < 10

10.执行计算不会命中索引

1
select * from test where a + 1 < 5

11.is null可以命中索引，但不是一种好的设计 推荐not null进行约束

mysql索引最左匹配原则的理解

创建student表，有三个字段，分别是id，name，cid

1
2
3
4
5
6
7
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_cid_INX` (`name`,`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

索引方面：id是主键，（name,cid）是一个多列索引

explain执行计划结果中的type字段有index和ref：

1.index

index：这种类型表示是mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个复合索引的一部分，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。

对于EXPLAIN SELECT * FROM student WHERE cid=1;这个语句

判断条件是cid=1,而cid是(name,cid)复合索引的一部分，没有问题，可以进行index类型的索引扫描方式。explain显示结果使用到了索引，是index类型的方式。

2.ref

这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一 一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。

以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：

mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

复合索引条件判断顺序

1
explain select * from student where cid = 1 and name = 'a';
1
explain select * from student where name = 'a' and cid = 1;

复合索引条件判断顺序不同，查询结果一样，都用到索引。

mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以mysql查询优化器会最终以这种顺序进行查询执行。

参考