索引 - 简书

一、主键索引（PRIMARY KEY）:是一种特殊的唯一索引，不允许有空值。一般在建表的时候同时创建主键索引。（一个表只能有一个主键索引）

二、唯一索引（UNIQUE）:唯一索引的值必须唯一，但允许有空值

建立索引：ALTER TABLE 表名 ADD UNIQUE(字段名称)

三、普通索引（INDEX）:最基本的索引，本索引没有任何限制

建立索引：ALTER TABLE 表名 INDEX 索引名称(字段名称)

四、组合索引（INDEX）:一个索引包含多个列，多用于避免回表查询

建立索引：ALTER TABLE 表名 ADD INDEX 索引名称（字段名称1，字段名称2，... 字段名称n）

五、全文索引（FULLTEXT）:是目前搜索引擎使用的一种关键技术

ALTER TABLE 表名 ADD FULLTEXT(字段名)

show index from 表名

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

①适合索引的列是出现在where子句中的列
②更新十分频繁的字段上不宜建立索引:因为更新操作会变更B+树，重建索引。这个过程是十分消耗数据库性能的

③区分度不大的字段上不宜建立索引（类似于性别这种区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。返回数据的比例在30%以外的情况下，优化器不会选择使用索引）

④业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。虽然唯一索引会影响INSERT速度，但是对于查询的速度提升是非常明显的。另外，即使在应用层做了非常完善的校验控制，只要没有唯一索引，在并发情况下，依然有脏数据产生。

⑤在多表关联时，要保证关联字段上一定有索引（外键字段要加索引）

innoDB引擎使用的是B+Tree

如果MySQL预估计使用索引比全表扫描还慢，则不会使用索引

（返回数据的比例是重要指标，比例越低越容易命中索引。范围为30%,返回数据额比例在30%以内）

前导模糊查询不能命中索引（例如SELECT * FROM user WHERE name LIKE '%s%')

非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询

数据类型出现隐式转换的时候不会命中索引，特别是当前列类型是字符串，一定要将字符串常量值用引号引起来。
（例如：SELECT * FROM user WHERE name='1' (√)
SELECT * FROM user WHERE name=1 (×)）

复合索引情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中符合索引

union 、in 、or都能够命中索引，建议使用in

用or分割开的条件，如果or前条件中列有索引，而后面的列中没有索引，没有涉及到的索引都不会被用到

负条件查询不能使用索引，可以优化为in查询

数据库执行计算或函数不会命中索引

索引