数据库索引

1、什么是索引？

数据库索引(在MySql中也叫键key)是数据库加快查询速度的的一种数据结构。用户可以根据应用环境的需要在基本表上建立一个或多个索引，以提供多种存取路径，加快查找速度。关系数据库系统在执行查询操作时会自动选择合适的索引作为存取路径，用户不必也不能显示的选择索引。

2、索引的类型

索引可以分为五种类型：

①、**主键索引(PRIMARY KEY)：**主键索引在表创建时会自动创建在主键字段上，它是一种特殊的唯一索引，一张表只允许存在一个主键索引；

②、**唯一索引(UNIQUE)：**唯一索引要求添加索引字段的值必须唯一，但是允许该字段存在空值。唯一索引的创建方式：ALTER TABLE 表名 ADD UNIQUE 索引名称(添加索引字段名称)

③、**普通索引(INDEX)：**最基本的索引，它对于要添加索引的字段没有限制。创建方式：ALTER TABLE 表名 ADD INDEX 索引名称(添加索引字段名称)

④、组合索引(INDEX)：组合索引就是一个索引包含有多个字段的组合，使用组合索引多是为了防止回表查询。创建方式ALTER TABLE 表名 ADD INDEX 索引名称(添加索引字段名称1,添加索引字段名称2,添加索引字段名称1)

⑤、全文索引(FULLTEXT)：全文检索，是目前搜索引擎使用的一种关键技术，它主要进行分词查询。创建方式：创建方式ALTER TABLE 表名 ADD FULLTEXT 索引名称(添加索引字段名称)

3、索引的查看

可以使用SHOW INDEX FROM 表名来查看当前表的索引；还可以使用explain 执行的Sql语句来查看当前Sql语句执行的策略；

4、索引的优缺点

优点：通过建立索引可以极大地提高在数据库中获取所需信息的速度，大大减小了服务器需要扫描的数据量，提高服务器处理相关搜索请求的效率，还可以帮助服务器避免排序和临时表。

缺点：1、降低了表的更新速度(INSERT、DELETE和UPDATE)，因为更新表时MySql数据库不仅要保存数据，还需要保存索引文件。

2、在数据库中创建的索引需要占用一定的物理存储空间，这其中就包括数据表所占的数据空间以及所创建的每一个索引所占用的物理空间，如果在一个大表上创建了多种组合索引，索引文件的体积会膨胀的很快。

3、如果表中的某个类存在大量的重复数据，那么为它建立索引就没有太大的实际效果(如该字段是性别的话设计索引就没有什么实际效果)。

4、当表中的数据量非常小时，全表扫描更加高效。

5、索引设计的原则

1、适合索引的列是出现在WHERE子句中的，及查询语句的条件字段更加适合作为索引列；

2、表的更新操作非常频繁的字段不适合建立索引，因为更新操作会变更B+树(B+树索引具有动态平衡的优点)，重建索引，这个过程是十分消耗数据库性能的；

3、区分度不大的字段上不建议建立索引，类似于性别这样区分度不大的字段，建立索引的意义不大。因为不能有效过滤数据，性能和全表扫描相当。另外当返回数据的比例在30%以上时，优化器不会选择使用索引；

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

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

6、索引存储类型

InnoDB引擎使用的是B+ Tree，B+树索引是将索引属性组织成B+树形式，B+树的叶节点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。

7、索引优化规则

1、如果MySql估计使用的索引比全表查询还要慢时，则不会使用索引；返回数据的比例是重要的指标,比例越低越容易命中，这个范围值是30%，下面的优化内容都是建立在返回数据的比例在30%以内的基础上的。

2、前道模糊查询不能够命中索引（WHERE xx LIKE %x%），非前导模糊查询则可以使用索引（WHERE xx LIKE x%）；

3、数据类型出现隐式转换时不会命中索引，及…WHERE xx=1(xx的类型为varchar时会存在隐式转换，所以不会命中索引)，当列类型为字符串时一定要将字符常量用引号引起来及…WHERE xx = ‘1’；

4、复合索引的情况下，查询条件不包含索引列最左边的部分(不满足最左原则),不会命中复合索引；

5、union、 in、 or都能够命中索引,建议使用in；
6、用or分割开的条件,如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到；
7、负向条件查询（!=）不能使用索引,可以优化为in查询；

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