1.什么是索引?
索引(在MySQL中也叫“键key”)是存储引擎快速找到记录的一种数据结构,在MySQL中默认使用的是InnoDB引擎,InnoDB引擎使用的是B+Tree。
2.索引类型
- 主键索引(PRIMARY KEY)
它是一种特殊的唯一索引,不允许有空值。一般是在建表的时候同时创建主键索引。注意:一个表只能有一个主键索引。 - 唯一索引(UNIQUE)
唯一索引列的值必须唯一,但允许有空值。ALTER TABLE 表名 ADD UNIQUE(字段名称)
- 普通索引(INDEX)
最基本的索引,它没有任何限制ALTER TABLE 表名 ADD INDEX(字段名称)
- 组合索引(INDEX)
一个索引包含多个列,多用于避免回表查询ALTER TABLE 表名 ADD INDEX(字段名称1,字段名称2,字段名称n.)
- 全文索引(FULLTEXT)
全文检索,是目前搜索引擎使用的一种关键技术ALTER TABLE 表名 ADD FULLTEXT(字段名称)
3.查看指定表的索引
SHOW INDEX FROM 表名;
4.索引的优缺点
- 优点:
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表 - 缺点:
1.降低更新表的速度,如对表进行INSER、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存索引文件。
2.建立索引会占用磁盘空间的索引文件,一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件会膨胀很快。
3.如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
4.对于非常小的表,大部分情况下简单的全表扫描更高效。
5.索引设计的原则
- 适合索引的列是出现在while子句中的列。
- 更新十分频繁的字段上不宜建立索引;因为更新操作会变更B+树,重建索引。这个过程是十分消耗数据库性能的。
- 区分度不大的字段上不宜建立索引;类似于性别这种区分度不大的字段,建立索引的意义不大。因为不能有效过滤,性能和全表扫描相当。另外返回数据的比例在30%以外的情况下,优化器不会选择使用索引。
- 业务上具有唯一特性的字段,即使是多个字段组合也必须建成唯一索引,虽然唯一索引会影响insert速度,但是对于查询的速度提升是十分明显的。另外,即使在应用层做了非常完善的校验控制,只要没有唯一索引,在并发的情况下,依然有脏数据产生。
- 多表关联时,要保证关联字段上一定有索引。(外键字段要加索引)
6.索引优化规则
- 如果MySQL估计使用索引比全表扫描还慢,则不会使用索引。返回数据的比例是一个重要的指标,比例越低越容易命中索引。记住这个范围值:30%,后面所讲的内容都是建立在返回数据的比例在30%以内的基础上。
- 前导模糊查询不会命中索引。
SELECT * FROM user WHERE name LIKE '%s%'
- 非前导模糊查询则可以使用索引,可优化为使用非前导模糊查询。
SELECT * FROM user WHERE name LIKE 's%'
- 数据类型出现隐式转换的时候不会命中索引,特别是当列类型是字符串,一定要将字符常量值用引号引起来。
- 复合索引的情况下,查询条件不包含索引列最左边部分(不满足最左原则),不会命中复合索引。
- union、in、or 都能命中索引,建议使用in。
- 用or分割开的条件,如果or前的条件中列有索引,而后面的列中没有索引,那么涉及到的索引都不会被用到。
- 负向条件查询不能使用索引(where中条件判断为不等于),可以优化为in查询。
- 数据库执行计算或函数不会命中索引。