MySQL索引

mysql索引（一）索引的原理

之前在学习mysql的时候，对索引有了初步的了解。

但是，了解的不是很透，比如说，当时我就不知道，索引其实是一个数据结构。

一：索引介绍

1：为何要有索引?

一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

2：什么是索引？

索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）
索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据

3：对索引的误解

（1）：索引时不必越多越好，添加索引对查询会提高速度，对写入及删除会较低效率。因此需要找到一个创建索引的平衡点

（2）：索引太多，会影响磁盘的使用率，尽量删除一些不必要的索引。

二：索引的原理

1：索引的原理

索引就相当于是一本字典的查找目录。我们可以根据拼音查找，我们也可以根据偏旁部首之类的查找。

下面内容看不懂的同学也没关系，能明白上边这个目录的道理就行了。 那么你想，书的目录占不占页数，这个页是不是也要存到硬盘里面，也占用硬盘空间。你再想，你在没有数据的情况下先建索引或者说目录快，还是已经存在好多的数据了，然后再去建索引，哪个快，肯定是没有数据的时候快。

因为如果已经有了很多数据了，你再去根据这些数据建索引，是不是要将数据全部遍历一遍，然后根据数据建立索引。你再想，索引建立好之后再添加数据快，还是没有索引的时候添加数据快，索引是用来干什么的，是用来加速查询的，那对你写入数据会有什么影响，肯定是慢一些了，因为你但凡加入一些新的数据，都需要把索引或者说书的目录重新做一个，所以索引虽然会加快查询，但是会降低写入的效率。

2：索引的影响

1、在表中有大量数据的前提下，创建索引速度会很慢

2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

这里大概解释一下：

数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。

数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段…这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。

但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。

而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

3：磁盘IO读取

这个涉及到部分硬件的原理，这个不了解无所谓，只需要知道，磁盘I/O是很耗费资源和时间的，而且磁盘I/O是有瓶颈的。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。

具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

以上大概就是索引的原理部分的概念，文字稍多，但是基本上都是有例子解释。

索引这部分其实重在理解其原理，才能更好的使用。

mysql索引（二）索引的数据结构B+TREE

索引本质上是一种数据结构，让我们在查询数据的时候尽量减少磁盘I/O。

前边大概看了索引的原理。数据库的复杂性，以及读取磁盘时，磁盘I/O等。任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。

那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生

B+树大概就是上边这个玩意。

如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点，关于b+树后边会看到，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项（深蓝色所示，一个磁盘块里面包含多少数据，一个深蓝色的块表示一个数据，其实不是数据，后面有解释）和指针（黄色所示，看最上面一个，p1表示比上面深蓝色的那个17小的数据的位置在哪，看它指针指向的左边那个块，里面的数据都比17小，p2指向的是比17大比35小的磁盘块），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。

真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

B+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.索引字段要尽量的小：

通过上面的分析，我们知道IO次数取决于b+数的高度h或者说层级，这个高度或者层级就是你每次查询数据的IO次数，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；

而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。

这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。

这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2：索引的最左匹配特性：

简单来说就是你的数据来了以后，从数据块的左边开始匹配，在匹配右边的，知道这句话就行啦，我们继续学下面的内容。当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；

但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。

比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

B-Tree索引的特点

1、B-tree索引可以加快数据的查询速度

存储引擎不需要进行全表扫描来获得需要的数据，取而代之的是从索引的根节点开始进行搜索。然后根据指针逐层向下查找，通过比较节点页的值和有目标值就可以找到合适的指针进入下层节点，而这些指针实际上定义了子节点页中值的上限和下限。

2、B-tree索引更适合进行范围查询

因为前面说过，B-tree对索引是顺序组织存储的，所以就很适合进行查找范围数据。

B-tree索引的使用场景

1、 全值匹配的查询

指的是和索引中的所有列进行匹配，比如查询字段 name = ‘tom’;

2、匹配最左前缀的查询

比如为ａ列和ｂ列设置联合索引，只要联合索引的第一列（ａ列）符合查询条件，索引就会被用到，若只是第二列（ｂ列）符合条件则不会被用到该索引。

３、匹配列前缀的查询

只匹配某一列的值的开头部分

4、匹配范围值

5、精准匹配某一列并范围匹配另外一列

6、只访问索引的查询

在这里指的就是覆盖索引，即只需要访问索引，而无需访问数据行

7、用于查询中的order by 操作

索引树中的节点是有序的。一般来说，若B-Tree可以按照某种方式查找到该值，那么也可以用这种方式用于排序。所以，如果 order by 子句中满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

B-Tree索引的限制

1、 若不是按照索引的最左列开始查找，则无法使用该索引

比如建立联合索引（name 、phone_num），若搜索phone_num则无法使用该索引

2、使用索引时，不能跳过索引中的列

比如建立联合索引（name 、phone_num 、addr），若搜索name和addr 则无法使用该索引只能使用那么过滤

3、not in 和 <> 操作无法使用该索引

4、若查询中有某个列的范围查询，则其右边的所有列都无法使用索引

注意：存储引擎用不同的方式使用B-Tree索引，性能也各有不同，各有优劣。例如，MyISAM使用前缀压缩的技术使得索引更小，但InnoDB则按照原数据格式进行存储。
MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据逐渐引用被索引的行

以上大概简单的了解了一下索引的B+树数据结构。

mysql索引（三）聚集索引与非聚集索引（辅助索引）

Mysql中常用的两个存储引擎innodb和mysiam的索引是不同的。

聚集索引就是以主键创建的索引

非聚集索引就是除了主键以外的索引。

非聚集索引也叫做二级索引，不用纠结那么多名词，将其等价就行了。非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

关于mysiam和innoDB的特性及区别，我在下边放了一张图：

在数据库中，B+树的高度一般都在2到4层，这也就是说查找某一个键值的行记录时最多只需要2到4次IO，这倒不错。因为当前一般的机械硬盘每秒至少可以做100次IO，24次的IO意味着查询时间只需要0.02~0.04秒。

数据库中的B+树索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。

聚集索引与辅助索引相同的是：不管是聚集索引还是辅助索引，其内部都是B+树的形式，即高度是平衡的，叶子结点存放着所有的数据。

聚集索引与辅助索引不同的是：辅助索引的叶子节点不包含行记录的全部数据

一：非聚集索引（辅助索引）

Mysiam创建数据表的时候，会在磁盘上生成三个.frm.MYD.MYI结尾的三个文件，frm结尾的是表结构，MYD结尾的是数据文件，MYI结尾的就是索引文件，也就是说索引也是存在硬盘上的。

叶子节点存放的是对应的那条数据的主键字段的值，除了包含键值以外，每个叶子节点中的索引行中还包含一个书签（bookmark），其实这个书签你可以理解为是一个{‘name字段’，name的值，主键id值}的这么一个数据。

该书签用来告诉InnoDB存储引擎去哪里可以找到与索引相对应的行数据。如果我们select 后面要的是name，我们直接就可以在辅助索引的叶子节点找到对应的name值，比如：select name from tb1 where name=‘xx’；

这个xx值你直接就在辅助索引的叶子节点就能找到，这种我们也可以称为覆盖索引。如果你select后面的字段不是name，例如：select age from tb1 where name=‘xx’；

也就是说，我通过辅助索引的叶子节点不能直接拿到age的值，需要通过辅助索引的叶子节点中保存的主键id的值再去通过聚集索引来找到完整的一条记录，然后从这个记录里面拿出age的值，这种操作有时候也成为回表操作，就是从头再回去查一遍，这种的查询效率也很高，但是比覆盖索引低一些，再说一下昂，再辅助索引的叶子节点就能找到你想找的数据可称为覆盖索引。

二：聚集性索引

InnoDB 在 B+ 树数据结构中索引又分为聚集索引和辅助索引

InnoDB创建数据表的时候，会在磁盘上生成.frm.idb结尾的两个文件。

索引存储在.idb结尾的数据文件中，InnoDB引擎的表，它的索引和数据都在同一个文件里面，所以我一直强调，使用InnoDB存储引擎的时候，每建一个表，就需要给一个主键，是因为这个主键是InnoDB存储引擎的.idb文件来组织存储数据的依据或者说方式，也就是说InnoDB存储引擎在存储数据的时候默认就按照索引的那种树形结构来帮你存。

这种索引，我们就称为聚集索引，也就是在聚集数据组织数据的时候，就用这种索引。

InnoDB这么做就是为了加速查询效率，因为你经常会遇到基于主键来查询数据的情况，并且通常我们把id字段作为主键，第一点是因为id占用的数据空间不大，第二点是你经常会用到id来查数据。

如果你的表有两个字段，一个id一个name，id为主键，当你查询的时候如果where后面的条件是name=多少多少，那么你就没有用到主键给你带来的加速查询的效果（需要主键之外的辅助索引），如果你用where id=多少多少，就会按照我们刚才上面说的哪种树形结构来给你找寻数据了（当然不仅仅有这种树形结构的数据结构类型），能够快速的帮你定位到数据块。

这种聚集索引的特点是它会以id字段作为依据，去建立树形结构，但是叶子节点存的是你表中的一条完整记录，一条完整的数据。

聚集索引的特性：

1. InnoDB存储引擎表示索引组织表，即表中数据按照主键顺序存放。而聚集索引（clustered index）就是按照每张表的主键构造一棵B+树，同时叶子结点存放的即为整张表的行记录数据，也将聚集索引的叶子结点称为数据页。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。同B+树数据结构一样，每个数据页都通过一个双向链表来进行链接。

2. 如果未定义主键，MySQL取第一个唯一索引（unique）而且只含非空列（NOT NULL）作为主键，InnoDB使用它作为聚簇索引。

3. 如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

4. 由于实际的数据页只能按照一棵B+树进行排序，因此每张表只能拥有一个聚集索引。在多少情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在B+树索引的叶子节点上直接找到数据。此外由于定义了数据的逻辑顺序，聚集索引能够特别快地访问针对范围值得查询。

三：关于索引的建议

1：单表索引不超过六个

2： 不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。

3：用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

mysql索引（四）索引的数据结构HASH

我们常用的索引数据结构比较多的是B+TREE。

还有另一种索引数据结构是hash，但是innoDB、mysiam数据引擎不支持hash数据结构。

不同的存储引擎支持的索引类型也不一样：

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；

Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

这个玩意，mysiam和innodb是不支持的，所以，一般情况下用不上了解就好。

一：hash算法复杂度

哈希算法时间复杂度为O(1)，且不只存在于索引中，每个数据库应用中都存在该数据结构。

二：HASH索引特性

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点

1、 hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、 对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、 hash索引包括键值、hash码和指针 。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。然而，hash索引也是存在其限制的：

三：hash索引的限制

1、 Hash索引必须进行二次查找

使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。
　　
2、hash索引不能用于外排序
hash索引存储的是hash码而不是键值，所以无法用于外排序
　　
3、hash索引不支持部分索引查找也不支持范围查找

只能用到等值查询，不能范围和模糊查询
　　
4、hash索引中的hash码的计算可能存在hash冲突

当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）

上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。

四：创建自定义hash索引

若存储引擎不支持hash索引，又想拥有hash索引所带来的性能提升，则可以模拟InnoDB一样创建哈希索引。

思路也比较简单，就是在B-tree基础上创建一个伪哈希索引。这和真正的hash索引不是一回事，因为还是采用B-Tree进行查找，但是它使用的是hash值而不是键本身进行查找。只需要在查询的where子句中手动指定使用hash函数即可。下面举个简单的例子：

比如：当我们需要存储大量的URL，并需要根据URL进行搜索查找。若用B-Tree来存储URL，存储的内容就会很大。此时的查询语句就是：

select id from url where url = "www.baidu.com";

若删除原来的url列上的索引，而新增一个被索引的url_crc列，使用crc32做hash函数，则可以使用如下方式查询：

select id from url where url = "www.baidu.com" and url_crc=CRC32("www.baidu.com");

这样做的话，性能就会有很大提升，因为mysql优化器会使用这个选择性高而体积很小的基于url_crc列的多音来完成查找。即使有多个记录相同的索引值，查找仍然很快，只需要根据hash值做快速的整数比较就能找到索引条目，然后一一返回对应的行。

五：Hash缺点

1、需要维护hash值，可以手动维护，也可以使用触发器实现。
2、若数据表非常大的话，CRC32（）会出现大量hash冲突，则可以自己实现一个64位的hash函数，这个自定义的hash函数要返回整数而不是字符串，因为范围整数，对此效率更高。一个简单的办法就是使用MD5（）函数返回值的一部分来作为自定义的hash函数。但是这可能比自己写一个hash算法性能要差一些。

以上大概就是hash索引的基本内容。

再说一次，这个玩意他不支持mysiam和innodb，所以，可能是用的不多。

mysql索引（五）联合索引

Mysql索引大概有五种类型：

普通索引(INDEX)：最基本的索引，没有任何限制
唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
全文索引(FULLTEXT )：可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。
联合（组合）索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

这里我们先来看联合索引（组合索引）。

比较简单的是单列索引（b+tree）。这个就不做解释。

遇到多条件查询时，不可避免会使用到多列索引。

我们使用一个例子来理解联合索引的使用方法：

我们来创建一个表，里边有五个字段c1,c2,c3,c4,c5。这个数据表有一个组合索引(c1,c2,c3,c4)

创建数据表：

MariaDB [test]> CREATE TABLE t(
    -> c1 CHAR(1) not null,
    -> c2 CHAR(1) not null,
    -> c3 CHAR(1) not null,
    -> c4 CHAR(1) not null,
    -> c5 CHAR(1) not null
    -> )ENGINE myisam CHARSET UTF8;
Query OK, 0 rows affected (0.09 sec)

添加联合索引：

MariaDB [test]> alter table t add index c1234(c1,c2,c3,c4);
Query OK, 0 rows affected (0.00 sec)              
Records: 0  Duplicates: 0  Warnings: 0

添加几条数据：

MariaDB [test]> insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2'),('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

接下来我们使用MySql Explain开始分析我们各种情况的查询语句是否用到了联合索引。且用到了联合索引中的那几个元素。

1：效率最高，同时走四个索引

（1）：按顺序写

explain select * from t where c1 = '1' and c2 = '1' and c3 = '1' and c4 = '1';

（2）：不按顺序写，经过mysql的优化，也是走全部索引的

explain select * from t where  c3 = '1' and c4 = '1' and c1 = '1' and c2 = '1';

2：最左前缀原则

（1）：不走索引

explain select * from t where c2 = '1' and c3 = '1' and c4 = '1';

因为组合索引遵循最左前缀原则，而，我们的组合索引第一个字段是c1，如果我们的where查询条件中没有c1这个筛选条件，那么mysql默认认为我们不希望通过索引查询。

（2）：覆盖部分索引

我们可以对比上边两次查询的结果，

explain select * from t where c1 = '1' and c4 = '1';

只走了C1索引，因为组合索引遵循最左前缀原则。

explain select * from t where c1 = ‘1’ and c2 = ‘1’;
这条查询语句走了C1和C2两个索引，同样，这个也是最左前缀原则的结果。

通过上边的对比，我们在使用联合索引的时候需要注意索引的使用顺序问题。

3：当查询条件中有范围查询及模糊查询的情况

（1）：第一个字段使用模糊查询

explain select * from t where c1 like '3';

（2）：第一个字段使用模糊查询并且其后边还有查询条件的时候

explain select * from t where c1 like '3' and c2 = '1' and c3 = '1' and c4 = '1';

从上边的查询结果我们可以看出，第一个字段使用模糊查询对之后的查询条件使用索引是没有影响的。

（3）：使用between关键字范围查询

explain select * from t where c1 between '1' and '3' and c2 = '1' and c3 = '1' and c4 = '1';

全索引匹配。

（4）：使用“>”“<”进行范围查询

explain select * from t where c1 > '3' and c2 = '1' and c3 = '1' and c4 = '1';

使用 > < 的时候，会对索引产生影响，通过上边的查询结果我们可以发现当第一个字段使用范围查询之后，后边的条件便不会再走索引了。

explain select * from t where c1 = '1' and c2 > '1' and c3 = '1' and c4 = '1';

这次就是走两个索引C1和C2。

以上大概就是联合索引的基本使用。

mysql索引（六）主键索引

Mysql索引大概有五种类型：

普通索引(INDEX)：最基本的索引，没有任何限制
唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
全文索引(FULLTEXT )：可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。
联合（组合）索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

这里我们来看下主键索引（特殊的唯一索引）。

主键索引一般建议使用数据表的自增唯一主键来作为主键索引使用。

主键索引，简称主键，原文是PRIMARY KEY，由一个或多个列组成，用于唯一性标识数据表中的某一条记录。一个表可以没有主键，但最多只能有一个主键，并且主键值不能包含NULL。

在MySQL中，InnoDB数据表的主键设计我们通常遵循几个原则：

1、采用一个没有业务用途的自增属性列作为主键；
2、主键字段值总是不更新，只有新增或者删除两种操作；
3、不选择会动态更新的类型，比如当前时间戳等。

在MyISAM引擎中，唯一索引除了key值允许存在NULL外，其余的和主键索引没有本质性区别。也就是说，在MyISAM引擎中，不允许存在NULL值的唯一索引，本质上和主键索引是一回事。

而在InnoDB引擎中，主键索引和辅助索引的区别就很大了。主键索引会被选中作为聚集索引，而唯一索引和普通辅助索引间除了唯一性约束外，在存储上没本质区别。

从查询性能上来说，在MyISAM表中主键索引和不允许有NULL的唯一索引的查询性能是相当的，在InnoDB表通过唯一索引查询则需要多一次从辅助索引到主键索引的转换过程。InnoDB表基于普通索引的查找代价更高，因为每次检索到结果后，还需要至少再多检索一次才能确认是否还有更多符合条件的结果，主键索引和唯一索引就不需要这么做了。

关于主键索引，我能想到的基本上就是这些了。这个没有sql的验证。

单纯的就是我自己在这叨叨的一些我理解的一些知识点。

mysql索引（七）唯一索引

Mysql索引大概有五种类型：

普通索引(INDEX)：最基本的索引，没有任何限制
唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
全文索引(FULLTEXT )：可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。
联合（组合）索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

这里我们来看下唯一索引。

之前我们看了主键索引，他是一种特殊的唯一索引，二者的区别是，主键索引不能有空值，但是唯一索引可以有空值。

一：基本使用

1：唯一索引可以是单列，也可以是多列，下面我们来看下创建语句：

# 创建单列唯一索引

alter table sc add unique (name);
# 创建多列唯一索引

alter table sc add unique key `name_score` (`name`,`score`);

2：删除唯一索引语句：

alter table sc drop index name;

在这里插入代码片

二：唯一索引作用

1：最大的所用就是确保写入数据库的数据是唯一值。

单列唯一值基本上就是主键。

常用的一般都是多列的唯一索引，比如：当前商品，一个用户只能购买一件。我们将用户id及商品id列设置成唯一索引。那么就可以避免一个用户出现重复购买的情况。

示例：

创建数据表：

MariaDB [test]> CREATE TABLE t(
    -> c1 CHAR(1) not null,
    -> c2 CHAR(1) not null,
    -> c3 CHAR(1) not null,
    -> c4 CHAR(1) not null,
    -> c5 CHAR(1) not null
    -> )ENGINE myisam CHARSET UTF8;
Query OK, 0 rows affected (0.09 sec)

添加几条数据：

MariaDB [test]> insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2'),('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

创建唯一索引：

MariaDB [test]> alter table t add unique key `name_score` (`c2`,`c3`);
Query OK, 5 rows affected (0.01 sec)              
Records: 5  Duplicates: 0  Warnings: 0

写入重复数据失败：

MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1','1','1','1','1');
ERROR 1062 (23000): Duplicate entry '1-1' for key 'name_score'

2：提高查询速度

使用index索引时,经常导致慢查询,耗时2秒左右,遇忙时更有达到5秒的

改用unique之后,查询耗时在0.0003秒 基本可以忽略不计

三：唯一索引和主键索引的具体区别

1：唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。
2：可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。
3：唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。
4：建立主键的目的是让外键来引用.
5： 一个表最多只有一个主键，但可以有很多唯一键

四：存在唯一键冲突时，避免策略

1：使用insert ignore语句

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据.

MariaDB [test]> insert ignore into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2');
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
MariaDB [test]> select * from t;
+----+----+----+----+----+
| c1 | c2 | c3 | c4 | c5 |
+----+----+----+----+----+
| 1  | 1  | 1  | 1  | 1  |
| 2  | 2  | 2  | 2  | 2  |
| 3  | 3  | 3  | 3  | 3  |
| 4  | 4  | 4  | 4  | 4  |
| 5  | 5  | 5  | 5  | 5  |
+----+----+----+----+----+
5 rows in set (0.00 sec)

Sql执行成功，但是我们查询表的数据，发现并没有写入成功。是因为唯一索引已经存在。跳过了这条写入的命令。

2：使用replace into语句

replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。

使用replace into，你必须具有delete和insert权限

示例：

MariaDB [test]> replace into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2');
Query OK, 2 rows affected (0.00 sec)

# 先执行删除，再执行写入
 
MariaDB [test]> select * from t;
+----+----+----+----+----+
| c1 | c2 | c3 | c4 | c5 |
+----+----+----+----+----+
| 1  | 1  | 1  | 1  | 2  |
| 2  | 2  | 2  | 2  | 2  |
| 3  | 3  | 3  | 3  | 3  |
| 4  | 4  | 4  | 4  | 4  |
| 5  | 5  | 5  | 5  | 5  |
+----+----+----+----+----+
5 rows in set (0.00 sec)

3：使用insert on duplicate key update语句

如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。

使用insert into，你必须具有insert和update权限

如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0

示例：

MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2') on duplicate key update c2 = 5;
Query OK, 2 rows affected (0.00 sec)
 
MariaDB [test]> select * from t;
+----+----+----+----+----+
| c1 | c2 | c3 | c4 | c5 |
+----+----+----+----+----+
| 1  | 5  | 1  | 1  | 2  |
| 2  | 2  | 2  | 2  | 2  |
| 3  | 3  | 3  | 3  | 3  |
| 4  | 4  | 4  | 4  | 4  |
| 5  | 5  | 5  | 5  | 5  |
+----+----+----+----+----+
5 rows in set (0.00 sec)

这里需要注意一下：

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，
如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，
然后对该记录加上X(排他锁)，最后进行update写入。

如果有两个事务并发的执行同样的语句，
那么就会产生death lock（死锁）

因此在使用的时候尽量避免：

尽量对存在多个唯一键的table使用该语句

在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

4：总结

这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。

insert ignore能忽略重复数据，只插入不重复的数据。

replace into和insert … on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。

mysql索引（八）全文索引

Mysql索引大概有五种类型：

普通索引(INDEX)：最基本的索引，没有任何限制
唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
全文索引(FULLTEXT )：可用于 MyISAM 表，mysql5.6之后也可用于innodb表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时和空间。
联合（组合）索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

这里看下全文索引。

全文索引，平时用到的不多，字数较多的文章查询基本上我们就使用中文分词器来进行分词查询，而不是使用全文索引来查询。全文索引的效率还是低。

但是相较于文字较少的数据，使用全文索引的效率要比使用like进行模糊查询的效率要高。

一：基本使用

1：建表的时候创建全文索引

create table fulltext_test (
    id int(11) NOT NULL AUTO_INCREMENT,
    content text NOT NULL,
    tag varchar(255),
    PRIMARY KEY (id),
    FULLTEXT KEY content_tag_fulltext(content,tag)  // 创建联合全文索引列
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2：在已存在的表上创建全文索引

（1）：使用create语句创建索引

create fulltext index content_tag_fulltext
    on fulltext_test(content,tag);

（2）：使用alter语句创建索引

alter table fulltext_test
    add fulltext index content_tag_fulltext(content,tag);

3：修改全文索引

修改全文索引有点麻烦，想修改的话，直接删了重新创建就好。

4：删除全文索引

（1）：直接使用 DROP INDEX 删除全文索引

drop index content_tag_fulltext
    on fulltext_test;

（2）：通过 SQL 语句 ALTER TABLE 删除全文索引

alter table fulltext_test
    drop index content_tag_fulltext;

5：使用全文索引

语法：

MATCH (columnName) AGAINST ('string')

示例：

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')
 
-- 当查询多列数据时：
-- 建议在此多列数据上创建一个联合的全文索引，否则使用不了索引的。
 
SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

注意： match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列。如果想要对某一列使用全文索引，请单独为该列创建全文索引。

二：测试全文索引

创建数据表：

create table test (
    id int(11) unsigned not null auto_increment,
    content text not null,
    primary key(id),
    fulltext key content_index(content)
) engine=MyISAM default charset=utf8;

写入几条数据：

insert into test (content) values ('a'),('b'),('c');
insert into test (content) values ('aa'),('bb'),('cc');
insert into test (content) values ('aaa'),('bbb'),('ccc');
insert into test (content) values ('aaaa'),('bbbb'),('cccc');

查看数据表中的数据：

MariaDB [test]> select * from test;
+----+---------+
| id | content |
+----+---------+
|  1 | a       |
|  2 | b       |
|  3 | c       |
|  4 | aa      |
|  5 | bb      |
|  6 | cc      |
|  7 | aaa     |
|  8 | bbb     |
|  9 | ccc     |
| 10 | aaaa    |
| 11 | bbbb    |
| 12 | cccc    |
+----+---------+
12 rows in set (0.00 sec)

我们现在使用全文索引来查询表中数据：注意我查询语句所对应的结果集的区别：

MariaDB [test]> select * from test where match(content) against('a');
Empty set (0.01 sec)
 
MariaDB [test]> select * from test where match(content) against('aa');
Empty set (0.00 sec)
 
MariaDB [test]> select * from test where match(content) against('aaa');
Empty set (0.00 sec)
 
MariaDB [test]> select * from test where match(content) against('aaaa');
+----+---------+
| id | content |
+----+---------+
| 10 | aaaa    |
+----+---------+
1 row in set (0.00 sec)

通过对比，我们可以看到只有select * from test where match(content) against(‘aaaa’);查出了数据，其余三条sql并没有查出数据。

这是因为mysql的全文索引是有最小搜索长度和最大搜索长度限制的。

三：全文索引最小搜索长度和最大搜索长度

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

1：使用以下命令可以查看当前数据库的配置：

show

 variables 
like
 
'%ft%'
;

可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;
 

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引，而刚刚搜索的只有 aaaa 的长度大于等于 4。

2：配置最小搜索长度

需要修改mysql的配置文件来修改。

一般的mysql的配置文件是在/etc/my.cnf中。追加以下内容：

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

然后重启 MySQL 服务器，然后，需要修复一下目前已有的全文索引，否则，刚刚的修改不会生效。

我这里建议，就不要修复了，直接删除重建就好了。

四：两种全文索引

1：自然语言的全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。上面提到的，测试表中必须有 4 条以上的记录，就是这个原因。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

2：布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

1：+ 必须包含该词

2： -必须不包含该词

3： 提高该词的相关性，查询的结果靠前

4： 降低该词的相关性，查询的结果靠后

5： (*)星号 通配符，只能接在词后面

对于上面提到的问题，可以使用布尔全文索引查询来解决，使用下面的命令，a、aa、aaa、aaaa 就都被查询出来了。

select * test where match(content) against(‘a*’ in boolean mode);

五：注意

1：使用全文索引前，搞清楚版本支持情况；

2： 全文索引比 like + % 快 N 倍，但是可能存在精度问题；

3：如果需要全文索引的是大量数据，建议先添加数据，再创建索引；

4：对于中文，可以使用 MySQL 5.7.6 之后的版本，或者第三方插件。

mysql索引（九）索引合并

索引合并是mysql底层为我们提供的智能算法。了解索引合并的算法，有助于我们更好的创建索引。

索引合并是通过多个range类型的扫描并且合并它们的结果集来检索行的。仅合并来自单个表的索引扫描，而不是跨多个表的索引扫描。合并会产生底层扫描的三种形式：unions（合并）、intersections（交集）、unions-of-intersections（先取交集再合并）。

以下四个例子会产生索引合并：

1、SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;
2、SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;
3、SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col;
4、SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);

索引合并有以下已知的局限性：

1、如果查询语句包含一个带有严重AND/OR嵌套的复杂的WHERE子句而MySQL没有选择最佳计划，那么可以尝试使用以下的标志符转换：

(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)

2、索引合并不适用于全文索引。

在 EXPLAIN 语句输出的信息中，索引合并在type列中表现为“index_merge”，在这种情况下，key列包含使用的索引列表。

索引合并访问方法有几种算法，表现在 EXPLAIN 语句输出的Extra字段中：

Using intersect(...)
Using union(...)
Using sort_union(...)

下面将更详细地描述这些算法。优化器根据各种可用选项的成本估计，在不同的索引合并算法和其他访问方法之间进行选择。

Index Merge Intersection算法

Index Merge Intersection算法对所有使用的索引执行同步扫描，并生成从合并的索引扫描接收到的行序列的交集。

这种算法适用于当WHERE子句被转换成多个使用AND连接的不同索引key上的范围条件，且条件是以下两种之一：

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN。

二、InnoDB表主键上的任何范围条件。

例子：

1.SELECT * FROM innodb_table
WHERE primary_key < 10 AND key_col1 = 20;
2.SELECT * FROM tbl_name
WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;

Index Merge Union算法

该算法类似于Index Merge Intersection算法，适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，且条件是以下三种之一：

一、这种形式的N部分表达式，索引正好包括N个字段（所有索引字段都被覆盖），N>=1，N如果大于1就是复合索引：

key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN。

二、InnoDB表主键上的任何范围条件。

三、符合Index Merge Intersection算法的条件。

例子：

1.SELECT * FROM t1
WHERE key1 = 1 OR key2 = 2 OR key3 = 3;
2.SELECT * FROM innodb_table
WHERE (key1 = 1 AND key2 = 2)
OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;

Index Merge Sort-Union算法

该算法适用于当WHERE子句被转换成多个使用OR连接的不同索引key上的范围条件，但是不符合 Index Merge Union算法的。Index Merge Sort-Union和Index Merge Union算法的区别在于，Index Merge Sort-Union必须首先获取所有行的行id并在返回任何行之前对它们进行排序。

例子：

1.SELECT * FROM tbl_name
WHERE key_col1 < 10 OR key_col2 < 20;
2.SELECT * FROM tbl_name
WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;