一、数据库为什么要建立索引?
建立索引为了防止全表扫描,加索引实现扫描部分数据。部分扫描和全表扫描的区别,拿新华字典为例,如果没有目录的话,当我们查找“做”这个词的时候需要从第一页查找到最后一页,就算在页数中间位置查找到了,还是需要继续查找到最后的,因为不知道后面是否还有这个信息;而索引的意思就是我们预先知道“做”这个字在拼音的Z区域,然后再根据目录查找,能够很快定位到“做”所在的页数,这样就大大减少查询时间。
二、如何建立索引
1、单列索引
1.1、主键索引:
主键是一种唯一性索引,但它必须指定为PRIMARY KEY,每个表只能有一个主键。
1.2、唯一索引:
索引列的所有值都只能出现一次,即必须唯一,值可以为空。
1.3、普通索引 :
基本的索引类型,值可以为空,没有唯一性的限制。
1.4、全文索引:
在使用模糊查询的时候有‘%123%’,‘%123’,‘123%’,三种用法,只有第三种%在后面才会走索引;如用到前面两种可以考虑使用全文索引来优化sql
2、联合索引
2.1在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例:
对列col1、列col2和列col3建一个联合索引
实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
2.2为什么要使用联合索引?
2.2.1减少开销。
建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
2.2.2覆盖索引。
对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机io操作。减少io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
2.2.3效率高。
索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sq