mysql数组索引_mysql之索引

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

索引数据模型

一个表的数据行数越多，那么对应的索引文件其实也是会很大的，实际上也是需要存储在磁盘中的，而不能全部都放在内存中，所以我们在考虑选用哪种数据结构模型时，可以换一个角度思考，哪个数据结构更适合从磁盘中读取数据，或者哪个数据结构能够提高磁盘的IO效率。

可以用于提高读写效率的数据结构很多，常用的有哈希表、有序数组、完全平衡二叉搜索树、B树、B+树等数据结构。

MySQL中的索引是B+树实现的，MySQL为何选择使用B+树？得看看其他数据结构实现的索引的特点：

哈希索引

哈希的思路很简单，把值放在数组里。用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。

比如给name字段建立hash索引的情况下，首先数据库底层会计算name字段各行值对应的hash值作为数组下标，其中可能会有hash冲突，存储对应的那一行数据的地址。

当直接执行select * from users where name = 'tom';的时候，数据库会计算'tom'的hash值，得到数组下标，然后直接从数据中取出数据并拿到锁对应的那一行数据的地址，进而在数据表文件中查询那一行数据。

但是当执行select * from users where name > 'tom';时，索引将不再起作用。

因为哈希表并不是有序的，可以快速的精确查询，但是不支持范围查询。所以只适合做等值查询，区间查询的效率很低！好像字典里的索引去掉了排序。

另外多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。按顺序遍历，找到。

因为不是有序的，插入速度会很快，只需要往后追加。但缺点是哈希索引做区间查询的速度会很慢的，索引将不再起作用，相当于全表扫描了。

所以， 哈希表这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎也是这样的道理。

有序数组索引

就是按照索引字段的顺序保存的数组，用二分法就可以快速得到，这个时间复杂度是 O(log(N)) 。支持范围查询。

但是，在需要更新数据的时候或插入一个记录就必须得挪动后面所有的记录，成本太高。

只适用于静态存储引擎，比如你要保存的是 2017 年某个城市的所有人口信息，这类不会再修改的数据。

二叉搜索树索引

二叉树数据结构1

二叉树数据结构2

二叉搜索树的特点是：每个节点的左儿子小于父节点，父节点又小于右儿子。这样如果你要查ID_card_n2 的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是 O(log(N)) 。

当然为了维持 O(log(N)) 的查询复杂度，你就需要保持这棵树是平衡二叉树。为了做这个保证，更新的时间复杂度也是 O(log(N)) 。

多叉树索引

树可以有二叉，也可以有多叉。多叉树就是每个节点有多个儿子，儿子之间的大小保证从左到右递增。比完全平衡二叉搜索树要 "矮"！

二叉树是搜索效率最高的，但是实际上大多数的数据库存储却并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

一棵 100 万节点的平衡二叉树，树高 20 。一次查询可能需要访问 20 个数据块【一层是一块？】。在机械硬盘时代，从磁盘随机读一个数据块需要 10ms左右的寻址时间。也就是说，对于一个 100万行的表，如果使用二叉树来存储，单独访问一个行可能需要 20 个 10 ms 的时间，这个查询可真够慢的。

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，我们就不应该使用二叉树，而是要使用 “N 叉 ” 树。这里， “N 叉 ” 树中的 “N” 取决于数据块的大小。

以 InnoDB 的一个整数字段索引为例，这个 N 差不多是 1200 。这棵树高是 4 的时候，就可以存1200 的 3 次方个值，这已经 17 亿了。考虑到树根的数据块总是在内存中的，一个 10 亿行的表上一个整数字段的索引，查找一个值最多只需要访问 3 次磁盘。其实，树的第二层也有很大概率在内存中，那么访问磁盘的平均次数就更少了。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

如，B树、B+树。

B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是 为了提高范围查找的效率。

同样的元素，B+树的表示要比B树要 "胖"，原因在于所有的叶子结点包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

非叶子节点也会冗余一份所有的中间节点元素，它们是最大(或最小)元素。

参考

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。

数据都存储在 B+ 树中，每一个索引在 InnoDB 里面对应一棵 B+ 树。

其他索引模型

跳表、 LSM 树等数据结构也被用于引擎设计中

每碰到一个新数据库，我们需要先关注它的数据模型，这样才能从理论上分析出这个数据库的适用场景。

在 MySQL 中，索引是在存储引擎层实现的，所以并没有统一的索引标准，即不同存储引擎的索引的工作方式并不一样。而即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

索引类型

主键索引

对于主健，oracle/sql server/mysql等都会自动建立唯一索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引( clustered index )。

主键索引是唯一索引的特殊类型。= 唯一索引+ not null

普通索引、辅助索引

由关键字KEY或INDEX定义的索引。唯一任务是加快对数据的访问速度。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引( secondary index )。

唯一索引

不允许两行具有相同的索引值。

例外情况是，如果该字段被设置为允许NULL值，则插入该字段的值可以包含多个NULL值。

它们的一些比较：

(1)对于主健/unique constraint ， oracle/sql server/mysql等都会自动建立唯一索引；

(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；

(3)主健可作外健，唯一索引不可；

(4)主健不可为空，唯一索引可；

(5)主健也可是多个字段的组合；

(6)主键与唯一索引不同的是：

a.有not null属性；

b.每个表只能有一个。

(7)主键索引比普通索引得查询速度快。因为普通索引树上并没有完整得数据，而是先找到主键后，再到主键索引树上去获取所需得数据，这个操作被称为回表。当你没有设主键或者主键突然被删除时，会自动建立一个主键rowid，保证回表等功能的正常运行。

FULLTEXT 全文索引

用于在一篇文章中，检索文本信息的。

复合索引

在多个列上建立索引,这种索引叫做复合索引(组合索引)。同时有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引。能用窄索引不用宽索引！

复合索引在数据库操作期间所需的开销更小,可以代替多个单一索引。

索引维护

注意不要过多用索引,否则对表更新的效率有很大的影响,因为在操作表的时候要化大量时间花在创建索引中！

例如，B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护。有时需要逻辑上挪动后面的数据，空出位置。

如果插入数据的所在的数据页已经满了，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。在这种情况下，性能自然会受影响。

除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50% 。

当然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做页合并。合并的过程，可以认为是分裂过程的逆过程。

自增主键的必要性

1.主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

2.索引的维护的主要代价是由于有序性的维护引起的，如果每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂，这样就可以减少索引的维护代价。

“ 尽量使用主键查询 ” 原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

思考：从性能和存储空间方面考量，自增主键往往是更合理的选择，哪些场景下应该使用自增主键，而哪些场景下不应该？

自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。

重建索引

索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

ALTER TABLE T DROP INDEX k;

ALTER TABLE T ADD INDEX(k);

索引优化

覆盖索引

思考： 基于主键索引和普通索引的查询有什么区别？

mysql> create table T(

id int primary key,

k int not null,

name varchar(16),

index (k))engine=InnoDB;

如果语句是 select * from T where ID=500 ，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；

如果语句是 select * from T where k=5 ，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID的值为 500 ，再到 ID 索引树搜索一次。这个过程称为回表(每个辅助索引都包含了主键列)。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。

覆盖索引可以避免回表过程，减少树的搜索次数，显著提升查询性能!

例如，create index idx1 on table1(col1,col2,col3)

select col1,col2,col3 from table1 where col1= A and col2= B and col3 = C

实际使用场景：

如：身份证号是市民的唯一标识。也就是说，如果有根据身份证号查询市民信息的需求，我们只要在身份证号字段上建立索引就够了。

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

当然，索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。这正是业务 DBA ，或者称为业务数据架构师的工作。

最左前缀原则

可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。

思考：在建立联合索引的时候，如何安排索引内的字段顺序？

首先，区分度最高的放在最左边。

如果我们创建了(username,sex,age)的复合索引，那么其实相当于创建了：

(username,sex,age)，(username,sex)、(username,age)、(username)四个索引，这被称为最佳左前缀特性。

你的 SQL 语句的条件是 where name like ‘ 张 %’。这时，你也能够用上这个索引，查找到第一个符合条件的记录，然后向后遍历，直到不满足条件为止。

mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。(而且第一个索引必须是等值匹配)

如果只是顺序问题，会得到优化

另外，需要考虑索引的复用能力。

因为可以支持最左前缀，所以当已经有了 (a,b) 这个联合索引后，一般就不需要单独在 a 上建立索引了。

因此， 第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

如果既有联合查询，又有基于 a 、 b 各自的查询呢？

查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b) 、(b) 这两个索引。

这时候，我们要考虑的原则就是空间了。

索引下推

在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

在 MySQL 5.6 之前，只能一条条记录回表，到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化( index condition pushdown) ， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

放弃使用索引而进行全表扫描的情况

1.在没有建索引的情况下，数据库查找某一条数据，就必须进行全表扫描了，对所有数据进行一次遍历，查找出符合条件的记录。

2.where 子句中对字段进行 null 值判断。导致引擎放弃使用索引而进行全表扫描。

如：select id from t where num is null，可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：select id from t where num=0。

3.where 子句中使用!=或<>操作符。因为优化器将无法通过索引来确定将要命中的行数,因此需要搜索该表的所有行。

4.where 子句中使用 or 来连接条件，如：select id from t where num=10 or num=20，可以这样查询：select id from t where num=10 union all select id from t where num=20

5.in 和 not in，对于连续的数值，能用 between 就不要用 in 了。

6.使用非打头字母搜索

7.在 where 子句中对字段进行表达式操作

8.在where子句中对字段进行函数操作

9.在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算

10.复合索引情况下，使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

https://blog.csdn.net/qq_36312376/article/details/80983410

唯一索引与普通索引的选择依据是什么

查询的情况

对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB 。

当然，如果满足条件的这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key ，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

update的情况

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。

change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

但 change buffer 只限于用在普通索引的场景下，而不适用于唯一索引。

因为对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存 才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

思考：普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。 反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记 录在 change buffer ，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说， change buffer 反而起到了副作用，此时应该关闭 change buffer 。

这两类索引在查询能力上 是没差别的，主要考虑的是对更新性能的影响。所以，建议你尽量选择普通索引。

优化器可能会选用不适合的索引

优化器为了找到一个最优的执行方案，并用最小的代价去执行语句，会挑选认为合适的索引。

然而，有些场景下，比如高并发不断地删除历史数据和新增数据的情况下， MySQL可能会选错索引。

判断扫描行数的时候出问题时会导致选错索引

扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。

扫描行数是怎么判断的？

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根 据统计信息来估算记录数。

这个统计信息就是索引的区分度。显然，一个索引上不同的值越多，这个索引的区分度就越 好。而一个索引上不同的值的个数，我们称之为 “ 基数 ” ( cardinality )。也就是说，这个基数越 大，索引的区分度越好。

MySQL 采样统计，得到索引的基数 cardinality，即 InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值的个数，得到一个平均 值，然后乘以这个索引的页面数，就得到了这个索引的基数(当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计)。在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择。计算结果往往都不是很精准。

调整方法

analyze table t 命令

如果只是索引统计不准确，导致 MySQL 选错索引，执行 analyze table t，可以用来重新统计索引信息，之后再select，优化器会选择更好的索引。

在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。

force index(index1)

找不出优化器选为什么择了执行时间更长的索引，可以使用 SELECT * FROM table1 force index(index1) 的语句强迫使用更好的索引，起到了 “ 矫正 ” 的作用。

字符串类型字段创建索引的邪术

前缀索引

MySQL 是支持前缀索引的，即可以定义字符串的一部分作为索引，如alter table SUser add index index2(email(6));，创建的 index2索引里面，对于每个记录都是只取前 6 个字节，占用的空间会更小，带来的损失是，可能会增加额外的记录扫描次数。

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。可以先统计索引上有多少个不同的值，来判断要使用多长的前缀。

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5% 。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6 、 L7 都满足，你就可以 选择前缀长度为 6 。

select count(distinct email) as L from SUser;

select

count(distinct left(email,4) ) as L4,

count(distinct left(email,5) ) as L5,

count(distinct left(email,6) ) as L6,

count(distinct left(email,7) ) as L7

from SUser;

对覆盖索引的影响

使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀 索引时需要考虑的一个因素。

倒序索引

比如身份证号，后几位更有区分度

对hash值建索引