⑩索引ProMax版

索引的概念

一、什么是索引

官方介绍索引是帮助MySQL高效获取数据的数据结构。简单来讲，数据库索引就像是书前面的目录，能加快数据库的查询速度。
事实上，索引是一种数据结构，用于帮助我们在大量数据中快速定位到我们想要查找的数据。

但对于海量数据来说，它的目录也是很大的，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

二、索引的种类

单列索引

一个索引只包含单个列，但一个表中可以有多个单列索引。

1、普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一 点。

2、唯一索引：索引列中的值必须是唯一的，但是允许为空值。

3、主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）。

组合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

全文索引

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。

三、索引的数据结构

索引的数据结构使用的是B+树，那为什么使用B+树而不是二叉查找树、平衡二叉树或者B树呢？
接下来，我们来讲讲B+树的由来。

二叉查找树

特点:
1.若它的左子树不空，则左子树上所有结点的值均小于它的根结点的值；
2.若它的右子树不空，则右子树上所有结点的值均大于它的根结点的值；
3.它的左、右子树也分别为二叉查找树。

二叉查找树的特点就是为了保证每次查找都可以这折半而减少IO次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这是我们不想见到的。

二叉平衡树

特点：
平衡二叉树不支持范围查询快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B树：改造二叉平衡树

特点：
1.B树的节点中存储着多个元素，每个内节点有多个分叉。
2.节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
3.父节点当中的元素不会出现在子节点中。
4.所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

• 一个节点可以存储多个关键字
• 节点下面，不像二叉树那样，只有两路了，可以有多路
  

B+树：改造B树

B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

B树：
非叶子节点和叶子节点都会存储数据。
B+树：
所有的数据都会出现在叶子节点。
叶子节点形成一个单向链表。
非叶子节点仅仅起到索引数据作用，具体的数据都是在叶子节点存放的

特点

• 遍历全表的速度特别快，不需要查询了子节点之后，再返回父节点进行查询
• 范围查询的时候，只要找到第一个节点就能很快找到后面的节点，也是不需要再返回父节点进行查询
  
  B+树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，所以放索引的磁盘块锁存放的索引数量是会跟这增加的，所以相对于B树来说，B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

四、Hash索引的特点

• Hash索引只能用于对等比较（=，in），不支持范围查询（between，<,>,…）。
• 无法利用索引完成排序操作
• 查询效率高，通常需要一次检索就可以查询到数据，但是通常也会有多次的检索（一个槽位通常由多个hash值的情况，已经组成了链表）

五、聚簇索引和非聚簇索引

聚簇索引

优点：
1.可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件，如果没有使用聚集索引，则每封邮件都可能导致一次磁盘IO。
2.数据访问更快，聚集索引将索引和数据保存在同一个B-Tree中，因此从聚集索引中获取数据通常比在非聚集索引中查找要快。
3.使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点：
1.聚簇数据最大限度地提高了IO密集型应用的性能，但如果数据全部放在内存中，则访问的顺序就没有那么重要了，聚集索引也没有什么优势了。
2.插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式，但如果不是按照主键顺序加载数据，那么在加载完成后最好使用optimize table命令重新组织一下表。
3.更新聚集索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
4.基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂的问题，当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作，页分裂会导致表占用更多的磁盘空间。
5.聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。
6.二级索引可能比想象的更大，因为在二级索引的叶子节点包含了引用行的主键列。
7.二级索引访问需要两次索引查找，而不是一次。

聚簇索引（Clustered Index）一般指的是主键索引（如果存在主键索引的话），聚簇索引也被称之为聚集索引。

聚簇索引在 InnoDB 中是使用 B+ 树实现的，比如我们创建一张 student 表，它的构建 SQL 如下：

以上 student 表中有一个聚簇索引（也就是主键索引）id，和一个非聚簇索引 class_id。

聚簇索引 id 对应的 B+ 树如下图所示：
在聚簇索引的叶子节点直接存储用户信息的内存地址，我们使用内存地址可以直接找到相应的行数据。

非聚簇索引

非聚簇索引在 InnoDB 引擎中，也叫二级索引，以上面 student 表为例，在 student 中非聚簇索引 class_id 对应 B+ 树如下图所示：
从上图我们可以看出，在非聚簇索引的叶子节点上存储的并不是真正的行数据，而是主键 ID，所以当我们使用非聚簇索引进行查询时，首先会得到一个主键 ID，然后再使用主键 ID 去聚簇索引上找到真正的行数据，我们把这个过程称之为回表查询。

总结
在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询，因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个：

• 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。
• 聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。
• 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。

六、回表查询

回表查询，实际查询两次，通过辅助索引（普通索引）实现的。因为辅助索引叶子节点不存放记录数据，只存放普通值和对应记录主键值

例如：表t中有四条记录

1, shenjian, m, A
3, zhangsan, m, A
5, lisi, m, A
9, wangwu, f, B

对应的主键索引（聚集索引），和辅助索引（普通索引）如下

查询语句如下:

create index idx_t_name on t(name);
select * from t where name='lisi';

执行流程：
1、先进行一次B+Tree查找，通过普通索引查找lisi对应的数据（叶子节点中）
2、再通过叶子节点中，data域中保存的lisi对应的主键值，进行一次B+Tree查找，找到对应记录行
3、将数据查找出来

这就是回表查询，先定位主键值，再定位行记录，它的性能较扫一遍索引树更低，应为两次查找Tree，磁盘IO较多。
也就是说一般情况下，只要使用普通索引，并且select 字段不属于索引，单次普通索引Tree查找，无法获取满足的数据，会进行第二次Tree查找

七、覆盖索引

覆盖索引定义：MySQL 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。

1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到

假设你定义一个联合索引

CREATE INDEX idx_name_age ON user(name,age);

查询名称为 liudehua 的年龄：

mysql> select name, age from user where name = 'liudehua';

上述语句中，查找的字段 name 和 age 都包含在联合索引 idx_name_age 的索引树中，这样的查询就是覆盖索引查询。

八、索引的优点和缺点

一、索引的优点

1、创建索引可以大幅提高系统性能，帮助用户提高查询的速度；

2、通过索引的唯一性，可以保证数据库表中的每一行数据的唯一性；

3、可以加速表与表之间的链接；

4、降低查询中分组和排序的时间。

二、索引的缺点

1、索引的存储需要占用磁盘空间；

2、当数据的量非常巨大时，索引的创建和维护所耗费的时间也是相当大的；

3、当每次执行CRU操作时，索引也需要动态维护，降低了数据的维护速度。

三、总结

1）在查询中很少使用的列不应该创建索引。
因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

2、数据值很少的表格也不应该创建索引。
因为，当数据太少的时候，全盘搜索可能都比索引查找还快，就没有必要创建索引了，反而还会降低磁盘空间和性能。

3、定义为text、image和bit数据类型的列不应该创建索引。
因为，这种列要么是数据相当大要么是数据相当少。

4、当修改性能远大于检索性能时不应该创建索引。
因为，修改性能和检索性能是相矛盾的，两者只会一增一减。

九、索引的最左匹配原则

建立联合索引时会遵循最左匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配
例如：

为user表中的name、address、phone列添加联合索引
ALTER TABLE user ADD INDEX index_three(name,address,phone);
所以，下面的三个SQL语句都可以命中索引
SELECT * FROM user WHERE address = ‘北京’ AND phone = ‘12345’ AND name = ‘张三’;

SELECT * FROM user WHERE name = ‘张三’ AND address = ‘北京’;

SELECT * FROM user WHERE name = ‘张三’;

这三条SQL语句在检索时分别会使用以下索引进行数据匹配 (name,address,phone) (name,address) (name)
索引字段出现的顺序可以是任意的，MySQL优化器会帮我们自动的调整where条件中的顺序
如果联合索引中最左边的列不在查询条件中，则不会命中索引
SELECT * FROM user WHERE address = ‘北京’ ;

十、索引的建立原则

1、表的主键、外键必须有索引；
2、数据量超过1000的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上，最好是有连续性的字段；
6、索引应该建在数据类型小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
正确选择复合索引中的主列字段，一般是选择性较好的字段；
复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
8、频繁进行数据操作的表和字段，不要建立太多的索引；
9、单表创建索引不要过多，最好不要超过6个；
10、使用字符串创建索引时候最好使用前缀创建索引，且不适合排序操作
11、创建复合索引时将使用度最高的字段放在最左边
12、不要对一个字段出现在多个索引中,不要定义冗余或重复的索引
13、为经常需要排序、分组和联合操作的字段建立索引
14、尽量减少like，但不是绝对不可用，”xxxx%” 是可以用到索引的
15、 尽量把>,<用>=和<=替换 ，因为后者是可以走复合索引的

十一、索引的失效

1、字段类型不匹配导致的索引失效
2、被索引字段使用了表达式计算
3、被索引字段使用了内置函数
4、like 使用了 %X 模糊匹配
5、索引字段不是联合索引字段的最左字段
6、or 分割的条件，如果 or 左边的条件存在索引，而右边的条件没有索引，不走索引
7、in、not in 可能会导致索引失效
8、mysql估计使用全表扫描要比使用索引快,则不使用索引

十二、sql优化

Sql执行顺序

(8) SELECT(9) DISTINCT column,…
选择字段 、去重

(6) AGG_FUNC(column or expression),…
聚合函数

(1) FROM [left_table]
选择表

(3) <join_type> JOIN <right_table>
链接

(2) ON <join_condition>
链接条件

(4) WHERE <where_condition>
条件过滤

(5) GROUP BY <group_by_list>
分组

(7) HAVING <having_condition>
分组过滤

(10) ORDER BY <order_by_list>
排序

(11) LIMIT count OFFSET count;
分页

基础Sql优化

• 查询SQL尽量不要使用select *，而是具体字段
• 使用varchar代替char
• 尽量使用数值替代字符串类型
• 查询尽量避免返回大量数据
• 使用explain分析你SQL执行计划
• 是否使用了索引及其扫描类型
• 创建name字段的索引
• 优化like语句
• 字符串怪现象
• 索引不宜太多，一般5个以内
• where限定查询的数据
• 避免在索引列上使用内置函数
• 避免在where中对字段进行表达式操作
• 避免在where子句中使用!=或<>操作符
• 去重distinct过滤字段要少
• where中使用默认值代替null

高级SQL优化

• 批量插入性能提升
• 批量删除优化
• 伪删除设计
• 提高group by语句的效率
• 复合索引最左特性
• 排序字段创建索引
• 删除冗余和重复的索引
• 不要有超过5个以上的表连接
• inner join 、left join、right join，优先使用inner join
• in子查询的优化
• 尽量使用union all替代union

十三、数据库优化

避免回表

在InnoDB的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢？

使用索引覆盖，举个例子：现有User表（id(PK),name(key),sex,address,hobby…）

如果在一个场景下，select id,name,sex from user where name =‘zhangsan’;这个语句在业务上频繁使用到，而user表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不是使用单一索引，而是使用联合索引（name，sex）这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
这就是一个典型的使用覆盖索引的优化策略减少回表的情况。

联合索引的使用

联合索引，在建立索引的时候，尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间，还可以更容易的使用到索引覆盖。试想一下，索引的字段越多，是不是更容易满足查询需要返回的数据呢。比如联合索引（a_b_c），是不是等于有了索引：a，a_b，a_b_c三个索引，这样是不是节省了空间，当然节省的空间并不是三倍于（a，a_b，a_b_c）三个索引，因为索引树的数据没变，但是索引data字段的数据确实真实的节省了。

联合索引的创建原则，在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面，频繁使用代表索引利用率高，区分度高代表筛选粒度大，这些都是在索引创建的需要考虑到的优化场景，也可以在常需要作为查询返回的字段上增加到联合索引中，如果在联合索引上增加一个字段而使用到了覆盖索引，那建议这种情况下使用联合索引。
联合索引的使用：

考虑当前是否已经存在多个可以合并的单列索引，如果有，那么将当前多个单列索引创建为一个联合索引。
当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。