索引(Index)

SQL语句优化

MySQL大表优化

一、什么是索引？

索引是帮助 MySQL 高效获取数据的数据结构。
在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据指定的顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

若要对有大量记录的表进行查询，第一种方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，如此会造成大量磁盘 I/O 操作，消耗大量数据库系统时间；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的 ROWID(相当于页码)快速找到表中对应的记录。索引是一种数据结构(平衡树非二叉)，即B 树/B+ 树，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序事件。

二、为什么索引能提高查询速度

MySQL 的基本存储结构是页(记录都存在页里边)：

• 各个数据页可以组成一个双向链表
• 每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

所以说，如果写select * from tab where name = 'xxx'这样没有进行任何优化的 sql 语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页。
2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表。

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O(n)。

索引做了什么可以让查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到 id 为 8 的记录简要步骤：

很明显：没有用索引是需要遍历双向链表来定位对应的页，现在通过目录就可以很快地定位到对应的页上了！(二分查找，时间复杂度近似为O(logn))。其实 MySQL 底层结构就是B+树。

三、创建索引的原则

索引要占用存储空间，建立索引的时候有一定的规则可循。

1️⃣最左前缀原则

一般在 where 条件中有两个及以上字段时，会建复合索引。MySQL 中的索引可以以一定顺序引用多个列，这种索引叫做复合索引。对于复合索引，要遵守最左前缀原则，该原则和 B+ 树中的“最左前缀原理”有关。什么是“最左前缀原则”？

对于该表，如果按照 name 字段来建立索引的话，采用 B+ 树的结构，大概的索引结构如下：

如果要进行模糊查找，查找 name 以“张”开头的所有人的 ID，即 sql 语句为：

select ID from table where name like '张%'

由于在 B+ 树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100 的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。也就是说，当找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。而这种定位到最左边，然后向右遍历寻找的方式，就是最左前缀原则。

一般，复合索引是一个有序元组，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数。另外，单列索引可以看成联合索引元素数为 1 的特例。如：
[id,name,age,school]，相当于创建了(id)、(id,name)、(id,name,age)和(id,name,age,school) 四个索引。直接用 id，或者 id、name 或 id、name、age 这样的顺序可以命中索引；id、school 只有 id 部分用到了索引；name、school 无法使用该索引。所以在创建复合索引的时候一定要注意索引字段顺序：①常用的查询字段放在最前面。②需要考虑字段值去重之后的个数，较多的放前面。

复合索引失效示例：

1. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件才能保证系统使用该索引，否则该索引将不会被使用。如：where name="xxp" and age=18 and school ="wg"; 按照最左匹配原则，该条件就无法走索引，因为首先必须有 id。

2. 如果条件都用上了，但是顺序不同，现在的查询引擎会自动优化为匹配复合索引的顺序，这样是能够命中索引的。但是应尽可能的让字段顺序与索引顺序相一致。如：where name="xxp" and id=1 and age<18 使用的索引仍然为(id,name,age)组合。

3. MySQL 会从左至右匹配，直到遇到范围查找(>、<、like、between)就停止。如：

select * from tab where id=1 and name="xxp" and age<18 and school="wg";

实际用到的索引为(id,name,age)。因为遇到了 age<18 就停止了，school 列就没有用上。

2️⃣不冗余原则：尽量扩展索引、不要新建索引
目前主要索引有：FULLTEXT、HASH、BTREE
好的索引可以提高查询效率，不好的索引不但不会起作用，反而给 DB 带来负担。
基于 BTREE 结构，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时 DB 系统也要消耗资源去维护。
基于最左前缀原则，尽量在原有基础上扩展索引，不要新增索引。能用单索引，不用联合索引；能用窄索引，不用宽索引；能复用索引，不新建索引。

3️⃣最大选择性原则

一般两种情况不建议建索引：

1. 一两千条记录，没必要建索引，查询做全表扫描就行。因为不是建了就一定会走索引，执行计划会选择一个最优的方式，MySQL 辅助索引的叶子节点并不直接存储实际数据，只是主建 ID，再通过主键索引二次查找，如此一来全表扫描可能效率更高。
2. 索引选择性较低的情况。
   所谓选择性(Selectivity)，是指不重复的索引值(也叫基数，Cardinality)与表记录数(#T)的比值。Index Selectivity = Cardinality / #T
   显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由 B+ 树的性质决定的。

选择区分度高的列做索引。什么是区分度高的字段呢？count(distinct 字段)/count(*)，当然最大就是 1，也就是唯一索引。这个值越大，查询的效率越高。当这个值小到一定的程度的时候，数据库就会放弃索引进行全表扫描。创建复合索引，需要注意把区分度最大的放到最前面，也就是值越大的放前面。

假设一个表有一百万的数据，其中有一个性别的字段。然后为该字段建了一个索引，自以为查询性能提升 n 倍。其实，就相当于把一百万条数据按照性别分别放到两个箱子里面，每箱子里面的性别都是一样的，索引起不了任何作用，二分查找也用不上，只能用暴力算法解决，全表扫描。相反，可以为身份证号码建立唯一索引，这样可以从头到尾用二分查找法查找，非常高效。

四、索引类型

1️⃣普通索引【normal】：使用字段关键字建立的索引，主要是提高查询速度。
2️⃣唯一索引【unique】：加速查询 + 列值唯一(可以有null)+ 表中可以有多个
3️⃣主键索引【primary】：加速查询 + 列值唯一(不可以有null)+ 表中只有一个
4️⃣复合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
5️⃣全文索引【full text】：对文本的内容进行分词，进行搜索。在比较老的版本中，只有 myisam 引擎支持全文索引，在 innodb5.6后引擎也支持全文索引，在 mysql 中全文索引不支持中文。一般使用 sphinx 集合coreseek 来实现中文的全文索引。
6️⃣spatial：空间索引。

ps.索引合并，使用多个单列索引组合搜索

覆盖索引，select 的数据列只从索引中就能够取得，不必读取数据行。换句话说查询的列被所建的索引覆盖。

五、索引方法

MySQL 目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

1. 全文索引~FULLTEXT
   目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决where name like “%word%"这类针对文本的模糊查询效率较低的问题。

2. HASH
   由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。

3. BTREE
   BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。

4. RTREE
   RTREE在MySQL很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。相对于BTREE，RTREE的优势在于范围查找。

六、主键索引和非主键索引有什么区别

如表，ID 为主键：

主键索引和非主键索引的示意图如下：

其中 R 代表一整行的值。

由图看出，二者的区别是：主键索引的叶子节点存放的是整行数据，而非主键索引的叶子节点存放的是主键的值。主键索引也被称为聚簇索引，非主键索引也被称为二级索引。

1️⃣根据这两种结构进行查询，看看区别：

1. 执行select * from table where ID = 100，即主键查询的方式，只需要搜索 ID 这棵 B+ 树。
2. 执行select * from table where k = 1，即非主键的查询方式，则先搜索 k 索引树，得到 ID = 100，再到 ID 索引树搜索一次，这个过程也被称为回表。

2️⃣聚集索引和非聚集索引的区别：

1. 聚集索引表示表中存储的数据按照索引的顺序存储，检索效率比非聚集索引高，但对数据更新影响较大。(比如主键索引)
2. 非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

七、索引下推(index condition pushdown)

满足最左前缀原则的时候，最左前缀可以用于在索引中定位记录。不符合最左前缀的部分，会怎么样呢？

以上表的联合索引(name, age)为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁人”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张 %' and age=10;

最左前缀，所以搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID100。避免了全表扫描。然后判断其他条件是否满足。

MySQL 5.6 之前，只能从 ID100 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。InnoDB 在(name,age)索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。此例，只需要对 ID100、ID600 这两条记录回表取数据判断，就只需要回表 2 次。

八、建议使用主键自增的索引

对于这棵主键索引的树：

如果插入 ID = 650 的一行数据，那么直接在最右边插入就可以了：

但是如果插入的是 ID = 350 的一行数据，由于 B+ 树是有序的，那么需要将下面的叶子节点进行移动，腾出位置来插入 ID = 350 的数据，这样就会比较消耗时间。如果刚好 R4 所在的数据页已经满了，需要进行页分裂操作，这样会更加糟糕。

但是，如果主键是自增的，每次插入的 ID 都会比前面的大，那么每次只需要在后面插入就行， 不需要移动位置、分裂等操作，这样可以提高性能。也就是为什么建议使用主键自增的索引。