在数据库中合理的使用索引是提升mysql数据库的一种高效和快捷的方式,但是在索引的使用上在我的使用中发现有很多坑,因为自己之前没有认识到,所以来总结一下
索引的介绍
索引是一种特殊的文件,其中包含着对数据表中的所有记录的引用指针
添加索引的条件
字段中存储的内容重复性不能过高,比如性别,颜色等这些可区分性很低的字段
字段会经常性的用作查询语句。 因为创建索引也是需要存储的空间的,而且创建了索引会造成insert等语句的速度变慢
字段更新的斌率不高的字段适合添加索引。数据的更新会带来索引的更新。
索引的类型
普通索引 :
key
。唯一的作用就是加快查询的速度主键索引 :
primary key
。字段具备唯一性 一张数据表中只有一个唯一索引 :
unique key
。联合索引 :
key(a,b,c)
。外键索引 : 在我现在的认识中,就是用来维护数据表之间的相关性的,并且会导致数据的写入等操作的速度过慢,所以。。好像没啥用(对于较大的项目)
全文索引 :
FULLTEXT(column1, column2)
mysql5.6以前的InnoDB表不支持。使用:WHERE MATCH(column) AGAINST('search_content')
索引在使用上的注意点
索引字段上使用
WHERE DAY(column)='' 或
WHERE column*2=100
这种运算,索引不会被使用到在索引的字段上 使用
NOT IN
,<>
,!=
这些运算符的时候,执行explain会使用到索引,但是这些操作是不被推崇的,因为就算是用到了索引速度也不会很快.而且在mysql的5.6版本以前这种方式就算执行explain
操作也是是用不到索引的在索引字段上使用
like
或regexp
操作的时候,%
的通配符不能放在要查找的字符串的左侧(可以想象使用索引的时候就是在查字典,比如想要找到'mysql'这个单词,需要从m开始,然后是y,所以查询的顺序就是从左往右的)-
关于联合索引的一些注意事项:
如果给一张表添加的一组联合索引如下:
key(name,email,age)
,mysql在添加联合索引的时候以‘最左前缀’的形式进行索引的添加,那么在进行查询select *
的时候[name] [name,email] [name,email,age]
这三组查询条件都是可以使用到这个组合索引的,但是这并不是重点如果现在使用
explain select * from table where age=11
会发现索引并没有被使用到。 可是执行explain select name,email from table where age=11
.会发现这个索引被使用到了。 这种方式叫做索引覆盖,在执行explain语句的时候,会发现extra一栏中卫'Using Index',如果存储引擎使用的是InnoDB,二级索引也存储了primary key的值,如果用过索引去访问primary key的值,也可以访问到
还有就是 关于添加联合索引还是单列索引的问题。如果字段都被添加成单列的索引,相比于联合索引的话,会增加数据库的IO的等待
索引的确可以加快mysql在查询时候的速度。但是在数据进行新增及更新等操作的时候,也需要对应的维护索引关系(但是也有配置可以在数据:,不深入展开)
在使用多个条件进行数据的查询的时候,有网上的很多说法都是mysql中单次sql的查询只能使用到一个索引(这个是错误的!!) 一条sql语句,针对一张表的查询,多个条件之间使用
and
拼接的话,索引在mysql内部会被执行 union的操作,索引是可以使用到的! 但是!如果条件之间使用or
进行条件的拼接的话,那么如果想要该语句的索引有效就必须保证每个被or
连接的条件都可以使用到索引。
索引的长度
比如果我现在想要给 用户表的用户家庭住址字段添加索引,该字段:address
的类型为varchar(255) ,对整个字段建立索引的话肯定是不合理的,这个时候需要为该字段的前n个值建立索引。可以使用
select count(distinct substring(字段,1,结束位置)) from 表
对比一下表中的总数据看一下该n值得选择性,用来确定索引的长度