SQL语句优化

MySQL大表优化

一、什么是索引？

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

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

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

二、为什么要建立索引？

一个没有索引的数据库表就相当于一本没有索引的新华字典，当想找出其中一个汉字的时候，无异于寻找 MH370 碎片。为指定的字段创建索引之后，当根据条件查找数据的时候，数据库引擎就可以利用查找算法(二分查找法)高效的查出来。

三、创建索引的原则

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

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️⃣不冗余原则
• 尽量扩展索引、不要新建索引
mysql目前主要索引有：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的优势在于范围查找。

六、B树

1. 定义任意非叶子结点最多只有M个儿子；且M>2；
2. 根结点的儿子数为[2, M]；
3. 除根结点以外的非叶子结点的儿子数为[M/2, M]；
4. 每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5. 非叶子结点的关键字个数=指向儿子的指针个数-1；
6. 非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7. 非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1], K[i])的子树；
8. 所有叶子结点位于同一层；

七、B-树

B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

B-树的特性：

1. 关键字集合分布在整颗树中；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束；
4. 其搜索性能等价于在关键字全集内做一次二分查找；
5. 自动层次控制；

由于限制了除根结点以外的非叶子结点，至少含有M/2个儿子，确保了结点的至少利用率，其最底搜索性能为：

其中，M为设定的非叶子结点最多子树个数，N为关键字总数；
所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题；
由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；删除结点时，需将两个不足M/2的兄弟结点合并；

八、为什么用 B+ 树做索引而不用哈希表做索引

1️⃣B+ 树是 B- 树 的变体，也是一种多路搜索树

1. 其定义基本与 B- 树同，除了：
2. 非叶子结点的子树指针与关键字个数相同；
3. 非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
4. 为所有叶子结点增加一个链指针；
5. 所有关键字都在叶子结点出现；

2️⃣B+的特性

1. 所有关键字都出现在叶子结点的链表中(稠密索引)，且链表中的关键字恰好是有序的；
2. 不可能在非叶子结点命中；
3. 非叶子结点相当于是叶子结点的索引(稀疏索引)，叶子结点相当于是存储(关键字)数据的数据层；
4. 更适合文件索引系统。

3️⃣原因

1. 哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而 B+ 树则可以通过最左前缀原则快速找到对应的数据。
2. 如果要进行范围查找，例如查找 ID 为 100 ~ 400 的人，哈希表同样不支持，只能遍历全表。
3. 索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

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

如表，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. 非聚集索引表示数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。非聚集索引检索效率比聚集索引低，但对数据更新影响较小。

十、为什么建议使用主键自增的索引？

对于这棵主键索引的树： 如果插入 ID = 650 的一行数据，那么直接在最右边插入就可以了：

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

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