MySQL索引

1、索引

索引(MySQL中也叫"key键")：是存储引擎快速查询记录的一种数据结构

2、索引存储类型

InnoDB引擎使用的是B+Tree

3、索引类型

（1）主键索引(PRIMARY KEY)：值必须唯一，不允许有空值，一般是在创建表的同时创建主键索引，一个表只能有一个主键索引（2）唯一索引(UNIQUE)：唯一索引列的值必须唯一，但允许有空值 ALTER TABLE 表名 ADD UNIQUE(字段名称)（3）普通索引(INDEX)：最基本的索引，没有任何限制 ALTER TABLE 表名 ADD INDEX 索引名称(字段名称)（4）组合索引(INDEX)：一个索引包含多个列，多用于避免回表查询 ALTER TABLE 表名 ADD INDEX 索引名称(字段名称1，字段名称2，字段名称N)（5）全文索引(FULLTEXT)：大文本中进行全文检索，是目前搜索引擎使用的一种关键技术 ALTER TABLE 表名 ADD INDEX FULLTEXT(字段名称)

4、查看指定表的索引

show index from 表名；

5、索引的优缺点

优点：索引大大减小了服务器需要扫描的数据量，可以帮助服务器避免排序和临时表缺点：①降低了更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件②建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果在一个大表上创建多种组合索引，索引文件会膨胀很快③如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果④对于非常小的表，大部分情况下全表扫描更高效

6、索引的设计原则

（1）适合索引的列是出现在where子句的列（2）更新十分频繁的字段不宜建立索引：因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的（3）区分度不大的字段不宜建立索引：类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外返回数据的比例在30%以外的情况下，优化器不会选择使用索引（4）业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响insert的速度，但是对于查询的速度提升是非常明显的。即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发的情况下，依然有脏数据产生（5）多表关联时，要保证关联字段上一定有索引（外键字段要加索引）

7、 索引优化规则

（1）如果MySQL估计使用索引比全表扫描还慢，则不会使用索引。【返回数据的比例是重要指标，比例越低越容易命中索引。记住这个范围值：30%】（2）前导模糊查询不能命中索引【select * from user where name like '%s%'】（3）非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询（4）数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来（5）复合索引的情况下，查询条件不包含索引列最左边部分(不满足最左原则)，不会命中复合索引（6）union,in,or都能够命中索引，建议使用in（7）用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到（8）负向条件查询不能使用索引，可以优化为in查询（9）数据库执行计算或函数不会命中索引