Mysql中的索引

在我们工作中最常用的存储引擎就是InnoDB，所以我们具体来说一下InnoDB的索引

(mysql中的存储引擎:InnoDB,MylSAM,MRG_MyISAM,Archive,CSV,Federated,Memory,NDB )

InnoDB 存储引擎支持以下几种常见的索引：B+树索引、哈希索

引，其中比较关键的是 B+树索引

实际上我们在创建索引选择索引方法的时候，可以看到，列表显示的是BTREE，HASH。这里的BTREE实际上是B+TREE

MySQL :: MySQL Internals Manual :: 22.2.1.1 Fil Header

这是官方给出的解释，说意思是我们经常忽略相互指向的细节此功能允许 InnoDB在叶与叶之间导航，而无需备份到根级别。这是您在经典 B 树中找不到的复杂性，这就是为什么InnoDB应该将其称为 B+ 树的原因。

HASH索引

这里稍后讲解B+树和B树，先说一下HASH。（这个HASH可以理解成我们JAVA中的HashMap， 也是key，value形式。hashCode 是通过索引列的值计算的）

为什么我们工作中很少用到HASH呢？

看下面这个3个场景：

hash索引的缺点：

1、hash 表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行 order by 时，hash 值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了a和 b 也可以查询的，如果使用 hash 表，组合索引会将几个字段合并hash，没办法支持部分索引；

4、当数据量很大时，hash 冲突的概率也会非常大

B+TREE

B+树索引就是传统意义上的索引，这是目前关系型数据库系统中查找最常用和最为有效的索引。B+树索引的构造类似于二叉树，根据键值（Key Value）快速找到数据。注意 B+树中的 B 不是代表二叉(binary)，而是代表平衡(balance)，因为 B+树是从最早的平衡二叉树演化而来，但是 B+树不是一个二叉树。在了解 B+树索引之前先要知道与之密切相关的一些算法与数据结构，这有助于我们更好的理解 B+树索引的工作方式，因为 B+树是通过二叉查找树，再由平衡二叉树，B 树演化而来。

二叉树

每个节点至多只有二棵子树，左子树和右子树，次序不能颠倒。逻辑上二叉树有五种基本形态：空二叉树、只有一个根结点的二叉树、只有左子树、只有右子树、完全二叉树（特例为满二叉树）。遍历是对树的一种最基本的运算，所谓遍历二叉树，就是按一定的规则和顺序走遍二叉树的所有结点，使每一个结点都被访问一次，而且只被访问一次，有前序、中序、后序遍历。

树的前序遍历、中序遍历、后序遍历详解 - 星朝 - 博客园(前序、中序、后序遍历方式)

二叉查找(搜索)树

二叉查找树首先肯定是个二叉树，除此之外，还规定，在二叉查找树中，左子树的节点值总是小于根的节点值，右子树的节点值总是大于根的节点值，也就是左子树节点值<根的节点值<右子树节点值。因此可以通过中序遍历得到节点值的排序输出。

Binary Search Tree Visualization

，

但是这种数据结构是有缺点的，如果你递增或者递减的数据永远比你小，那这个就变成了链型结构

如果：

平衡二叉树（AVL-树）

平衡二叉树的定义如下:首先符合二叉查找树的定义，其次必须满足任何节点的两个子树的高度最大差为 1。

平衡二叉树的查找性能是比较高的，但不是最高的，只是接近最高性能。最好的性能需要建立一棵最优二叉树，但是最优二叉树的建立和维护需要大量的操作，因此，用户一般只需建立一棵平衡二叉树即可。平衡二叉树的查询速度的确很快，但是维护一棵平衡二叉树的代价是非常大的。通常来说，需要 1 次或多次左旋和右旋来得到插入、更新和删除后树的平衡性

动态示例:AVL Tree Visualzation

一旦左右两颗子数高度差超过1时,就会发生旋转,这样就解决了二叉查找树链表的问题,但是这个树就有个缺陷就是高度是无限增加的,高度越高效率越低

这里是具体旋转帖子:图示讲解AVL平衡二叉树的左旋和右旋_江城的博客-CSDN博客_二叉树左旋和右旋

B树

B 树又叫平衡多路查找树。一棵m阶的B 树 (m叉树)的特性如下：

• B树中所有节点的孩子节点数中的最大值称为B树的阶，记为M（重点）
• 树中的每个节点至多有M棵子树 ---即：如果定了M，则这个B树中任何节点的子节点数量都不能超过M

• 若根节点不是终端节点，则至少有两棵子树
• 除根节点和叶节点外，所有点至少有m/2棵子树（上溢）

• 所有的叶子结点都位于同一层。
• 

动态示例:B-Tree Visualization

这里是具体旋转帖子:B树详解 - 简书

这是B+树:B+ Tree Visualization

具体可以看到 B+数的叶子节点是有个指针指向的. 为什么这么设计?

这里用了局部性原理的利用。

所谓局部性原理，是指当一个数据被使用时，其附近的数据有较大概率在短时间内被使用。当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内。B树将键相近的数据存储在同一个节点，当访问其中某个数据时，数据库会将该整个节点读到缓存中；当它临近的数据紧接着被访问时，可以直接在缓存中读取，无需进行磁盘IO；换句话说，B树的缓存命中率更高。

Mysql索引

索引创建策略

我们在定义表结构的时候要显式的指定列的类型，以整数类型为例，有TTNYINT、NEDUMNT、INT、BIGTNT 这么几种，它们占用的存储空间依次递增，我们这里所说的类型大小指的就是该类型表示的数据范围的大小。能表示的整数范围当然也是依次递增，如果我们想要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用 INT 就不要使用 BIGINT，能使用 NEDIUMINT 就不要使用 INT，这是因为:数据类型越小，在查询时进行的比较操作越快（CPU 层次) 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/0 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。这个建议对于表的主键来说更加适用，因为不仅是聚簇索引中会存储主键值，其他所有的二级索引的节点处都会存储一份记录的主键值，如果主键适用更小的数据类型，也就意味着节省更多的存储空间和更高效的 I/0。

索引的选择性/离散性

创建索引应该选择选择性/离散性高的列。索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从1/N 到 1 之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑正确的情况下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。

比如这张表:

我们去怎么选择创建索引呢?

计算任务id 占比数

SELECT count(DISTINCT task_id)/count(*) cnt FROM task_coll;

计算组织机构的占比

SELECT count(DISTINCT ORGAN_ID)/count(*) cnt FROM task_coll;

很明显,task_coll表选择task_id 作为索引列更好

聚集索引/聚簇索引

InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。因此聚集索引的一个优点就是：通过过聚集索引能获取完整的整行数据。另一个优点是：对于主键的排序查找和范围查找速度非常快。如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

这个地方要说一下磁盘的读写:

这是我们磁盘的结构,可以看到他是扇区和柱面的,读取数据是根据磁头去扫描扇区,读取的时候盘面就会旋转,那怎么样的读取效率会高呢?

顺序读,顺序写的效率是最高的.

一般主键都是用int类型,尽量避免使用uuid,因为mysql读取数据是读取一个页的数据16k,如果使用uuid时候就意味着你在存储的时候mysql会进行一次多余的计算,去比较字符串大小.比较int类型大小的效率肯定是比字符串大小是快的

辅助索引/二级索引

对于辅助索引(SecondaryIndex，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉InnoDB存储引擎哪里可以找到与索引相对应的行数据。因此InnoDB存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

回表

辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引通过主键回表去寻找值，回表次数越少越快。回表必定会进行IO

联合索引/复合索引

前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。千万要注意一点，建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以每个列则建立B+树，有几个列就有几个B+树，比如，index(note)、index(b)，就分别对note,b两个列各构建了一个索引。index(note,b)在索引构建上，包含了两个意思：

1、先把各个记录按照 note 列进行排序。

2、在记录的 note 列相同的情况下，采用 b 列进行排序

覆盖索引/索引覆盖

不是索引，是索引的一种行为

既然多个列可以组合起来构建为联合索引，那么辅助索引自然也可以由多个列组成。InnoDB存储引擎支持覆盖索引(coveringindex，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。所以记住，覆盖索引并不是索引类型的一种。

索引是多个列构成，如果组合列中含有该数据，就不需要去聚簇索引中去回表，减少IO操作

比如:

通过组织机构查询一级物业,可以看到这个查询时并没有回表

任何程序优化都是, 空间去换取时间或者时间去换取空间

时间换取空间: 索引确实可以加快我们查询的速度,但是在插入的时候,就会对索引列的b+树进行维护.这样就会占用磁盘的空间.相对的如果你需要插入的效率从而减少索引列的维护,这样就会减少磁盘占用的空间.这就是空间换取时间

在比如:

控制下载人数最多允许10个人进行下载,有两个方案

方案一: 定义一个局部的原子类,每次有请求下来对原子类进行加一,当原子类数量大于10的时候,就不允许下载了

方案二: 定义一个线程池,corePool是10,当前请求进来就进入这个线程池中,进行执行程序.超过10个进行排队

Sql性能优化

对于架构调优，在系统设计时首先需要充分考虑业务的实际情况，是否可以把不适合数据库做的事情放到数据仓库、搜索引擎或者缓存中去做；然后考虑写的并发量有多大，是否需要采用分布式；最后考虑读的压力是否很大，是否需要读写分离。对于核心应用或者金融类的应用，需要额外考虑数据安全因素，数据是否不允许丢失。

作为金字塔的底部的架构调优，采用更适合业务场景的架构能最大程度地提升系统的扩展性和可用性。在设计中进行垂直拆分能尽量解耦应用的依赖，对读压力比较大的业务进行读写分离能保证读性能线性扩展，而对于读写并发压力比较大的业务在 MySQL 上也有采用读写分离的大量案例。

作为金字塔的底部，在底层硬件系统、SQL 语句和参数都基本定型的情况下，单个 MySQL 数据库能提供的性能、扩展性等就基本定型了。但是通过架构设计和优化，却能承载几倍、几十倍甚至百倍于单个 MySQL 数据库能力的业务请求能力。

对于 MySQL 调优，需要确认业务表结构设计是否合理，SQL 语句优化是否足够，该添加的索引是否都添加了，是否可以剔除多余的索引等等。最后确定系统、硬件有哪些地方需要优化，系统瓶颈在哪里，哪些系统参数需要调整优化，进程资源限制是否提到足够高；在硬件方面是否需要更换为具有更高 I/O 性能的存储硬件，是否需要升级内存、CPU、网络等。

如果在设计之初架构就不合理，比如没有进行读写分离，那么后期的 MySQL和硬件、系统优化的成本就会很高，并且还不一定能最终解决问题。如果业务性能的瓶颈是由于索引等 MySQL 层的优化不够导致的，那么即使配置再高性能的 I/O 存储硬件或者 CPU 也无法支撑业务的全表扫描。

查询性能优化

前面的章节我们知道如何设计最优的库表结构、如何建立最好的索引，这些对于高性能来说是必不可少的。但这些还不够—还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。

请求了不需要的数据

有些查询会请求超过实际需要的数据，然后这些多余的数据会被应用程序丢弃。这会给 MySQL 服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的 CPU 和内存资源。比如:

1.总是取出全部列 :每次看到 SELECT*的时候都需要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能不是必需的。取出全部列，会让优化器无法完成索引覆盖扫描这类优化,还会为服务器带来额外的 I/O、内存和 CPU 的消耗。因此，一些 DBA 是严格禁止 SELECT *的写法的，这样做有时候还能避免某些列被修改带来的问题。什么时候应该允许查询返回超过需要的数据？如果这种有点浪费数据库资源的方式可以简化开发，因为能提高相同代码片段的复用性，如果清楚这样做的性能影响，那么这种做法也是值得考虑的。如果应用程序使用了某种缓存机制，或者有其他考虑，获取超过需要的数据也可能有其好处，但不要忘记这样做的代价是什么。获取并缓存所有的列的查询，相比多个独立的只获取部分列的查询可能就更有好处。

2.重复查询相同的数据:不断地重复执行相同的查询，然后每次都返回完全相同的数据。比较好的方案是，当初次查询的时候将这个数据缓存起来，需要的时候从缓存中取出，这样性能显然会更好

比如: 这个业务是从数据库中获取字典数据,这个字典数据很少变动.因此这里我们做了一个缓存

Explain 执行计划

通过使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道MySQL 是如何处理你的 SQL 语句的。分析查询语句或是表结构的性能瓶颈，总的来说通过 EXPLAIN 我们可以：

• 表的读取顺序
• 数据读取操作的操作类型

• 哪些索引可以使用
• 哪些索引被实际使用

• 表之间的引用
• 每张表有多少行被优化器查询

id： 在一个大的查询语句中每个 SELECT 关键字都对应一个唯一的 id

select_type： SELECT 关键字对应的那个查询的类型

table：表名partitions：匹配的分区信息

type：针对单表的访问方法

possible_keys：可能用到的索引

key：实际上使用的索引

key_len：实际使用到的索引长度

ref：当使用索引列等值查询时，与索引列进行等值匹配的对象信息

rows：预估的需要读取的记录条数

filtered：某个表经过搜索条件过滤后剩余记录条数的百分比

Extra：—些额外的信息

type

我们前边说过执行计划的一条记录就代表着 MySQL 对某个表的执行查询时的访问方法/访问类型，其中的 type 列就表明了这个访问方法/访问类型是个什么东西，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >

unique_subquery > index_subquery > range > index > ALL

出现比较多的是 system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到 range 级别，最好能达到 ref。