数据库索引

一条SQL如何进行优化呢？

这个问题很多同学都会抢答说优化索引呀。

这个回答对也不对。什么时候可以添加索引呢？什么地方添加索引可以有效提升查询速度？我添加了索引，为什么效率并没有什么变化呢？本文将涉及这些问题。

在关系型数据库中，如果有上万甚至上亿条记录，在查找记录的时候，想要获得非常快的速度，就需要使用索引。

索引是关系型数据库中对某一列或多个列的值进行预排序的数据结构。
通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

为什么说回答对也不对呢？
索引不是万能的

索引的优点

1. 大大加快数据的查询速度
2. 唯一索引可以保证数据库表每一行数据的唯一性
3. 加速表的连接时间

索引的缺点

1. 创建、维护索引是要耗费时间的，所以，索引的数量不能太多
2. 索引是一种数据结构，会占据磁盘空间
3. 对表进行更新操作时，索引也要动态维护，降低了维护速度

那什么时候不建议添加索引呢？

• 在数据表中的数据行数比较少的情况下，比如不到 1000 行，是不需要创建索引的。正如上面说的，索引是有开销的，为了夏天吃雪糕，也没必要买了冷库，你说是吧。
• 当数据重复度大，比如高于 10% 的时候，也不需要对这个字段使用索引。比如学生表中性别这个字段，如果数据的比例是1：1，每次查询的时候就需要先访问一半的索引，然后再访问一半的数据表，这样加起来的开销比不使用索引可能还要大。
• 频繁更新的字段不一定要创建索引。
• WHERE 条件（包括 GROUP BY、ORDER BY）里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

索引的分类

从功能逻辑上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引和全文索引。

主键索引和唯一索引的区别：

• 主键创建后一定包含一个唯一索引，唯一索引不一定就是主键
• 唯一索引列允许为空值，主键索引不允许为空值
• 主键在创建时，已经默认为空值+唯一索引了
• 主键可以被其他表引用为外键，而唯一索引不能
• 一个表最多只能创建一个主键，但是可以创建多个唯一索引
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等

全文索引（FULLTEXT）
如果希望通过关键字的匹配来进行查询过滤

SELECT * FROM student WHERE name like ‘%王’

这种模糊查询，在文本比较少的时候，还是可以使用的，但是如果是在大量的文本数据检索，还是需要使用文本检索，效率更高，但是存在精度问题。

需要注意的是，只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引
而且只能对英文进行全文索引
我们可以使用ES和Solr专门的全文搜索引擎

按照物理实现方式，索引可以分为 2 种：聚集索引和非聚集索引。
MySQL 的 InnoDB 使用的是聚集索引，而非聚集索引的代表就是 MyISM，也是 Mysql 中常见的搜索引擎。

什么是聚集索引

聚集索引就需要提到 B+ tree 这种数据结构，我们建表的时候一般会加一个主键，传统意义上的表格的话，数据就是无序的存放在磁盘之中，如果建立了一个主键，就像有了一根线，将数据进行了串联，其实存储结构就从整齐的排列变成了树形的结构，是一种平衡树，而不是二叉树。整个表就变成了一个索引，就是所谓的聚集索引。一张表只能有一个主键，只能有一个聚集索引，主键的作用就是将表的数据格式转换成了平衡树的格式存在磁盘中。

什么是非聚集索引

非聚集索引和聚集索引一样，同样是采用平衡树作为索引的数据结构。索引树结构的各节点的值来自于表中的索引字段。表中多个字段加上索引，就会出现多个独立的索引结构，每个索引结构之间互相不存在关联关系。每次给字段建立一个新的索引值，字段中的数据就会被复制出来一份，用于生成索引。因此，给表添加索引，会增加表的体积，占用磁盘存储空间。

聚集索引和非聚集索引的区别在于，通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以查到记录对应的主键值，在使用主键的值通过聚集索引查找到需要的数据。

联合主键
联合主键其实就是多个列组合在一起创建的索引，需要注意的是最左原则。
当我们使用了联合索引 (user_id, user_name) 的时候，在 WHERE 子句中对联合索引中的字段 user_id 和 user_name 进行条件查询，或者只对 user_id 进行查询，效率基本上是一样的。当我们对 user_name 进行条件查询时，效率就会降低很多，这是因为根据联合索引的最左原则，user_id 在 user_name 的左侧，如果没有使用 user_id，而是直接使用 user_name 进行条件查询，联合索引就会失效。

什么时候可以添加索引

1. 字段的数值有唯一性的限制，比如用户名
2. 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
3. 需要经常 GROUP BY 和 ORDER BY 的列
4. UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引
5. DISTINCT 字段需要创建索引
6. 做多表 JOIN 连接操作时，创建索引需要注意以下的原则
   首先，连接表的数量尽量不要超过 3 张
   其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。
   最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致

同时有 GROUP BY 和 ORDER BY 的情况：

实际上多个单列索引在多条件查询时只会生效一个索引（MySQL 会选择其中一个限制最严格的作为索引），所以在多条件联合查询的时候最好创建联合索引。
因为在进行 SELECT 查询的时候，先进行 GROUP BY，再对数据进行 ORDER BY 的操作，所以建立联合索引的时候，需要注意联合索引的顺序，是会带来效率的不同的。

什么情况下索引失效

1. 如果索引进行了表达式计算，则会失效
2. 如果对索引使用函数，也会造成失效
3. 在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效
4. 当我们使用 LIKE 进行模糊查询的时候，前面不能是 %
5. 索引列尽量设置为 NOT NULL 约束。
6. 我们在使用联合索引的时候要注意最左原则

如何查找“不经常使用的“索引

可以查看下MySQL中的performance_schema.table_io_waits_summary_by_index_usage 数据表，它表明了每个索引进行统计的 I/O 等待事件，其中 COUNT_STAR 代表了事件的次数。过滤掉一些系统表，查看下数据表中有哪些索引不经常使用，具体的 SQL 语句：

SELECT   OBJECT_SCHEMA, 
				 OBJECT_NAME, 
				 INDEX_NAME,
				 COUNT_STAR
FROM     performance_schema.table_io_waits_summary_by_index_usage
WHERE  INDEX_NAME IS NOT NULL
				AND COUNT_STAR = 0
				AND OBJECT_SCHEMA != 'mysql' 
				AND OBJECT_SCHEMA !='performance_schema'