目录
索引是什么
索引是对数据库表中一个或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构
你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整本书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引
1.索引加快数据库的检索速度
2.索引降低了插入、删除、修改等维护任务的速度
3.唯一索引可以确保每一行数据的唯一性
4.通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
5.索引需要占物理和数据空间
索引有哪些结构/索引常见的模型
1. 哈希表：一种以键-值（key-value）存储数据的结构，我们只要输入待查找的值即key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置。如果出现hash冲突，则在冲突的value位置使用链表进行连接。
适用场景：等值查询，如Memcached及其他一些NoSQL引擎
2. 有序数组
适用场景：只适用于静态存储引擎。用于等值查询和范围查询（ID值必须是递增的）
3. 搜索树：左子节点小于父节点、父节点小于右子节点。Innodb使用B+树，为什么数据库使用B+树作为索引？
问：为什么采用B+树？这和Hash索引比较起来有什么优缺点吗？
答：因为Hash索引底层是哈希表，哈希表是一种以key-value存储数据的结构，所以多个数据在存储关系上是完全没有任何顺序关系的，所以，对于区间查询是无法直接通过索引查询的，就需要全表扫描。所以，哈希索引只适用于等值查询的场景。而B+树是一种多路平衡查询树，所以他的节点是天然有序的（左子节点小于父节点、父节点小于右子节点），所以对于范围查询的时候不需要做全表扫描
1、哈希索引适合等值查询，但是无法进行范围查询
2、哈希索引没办法利用索引完成排序
3、哈希索引不支持多列联合索引的最左匹配规则
4、如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题
B+树索引
我们举个例子，假设我们有一个主键列为ID的表，表中有字段k，并且在k上有索引。
表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)，两棵树的示例示意图如下。
主键索引的叶子节点存的是整行数据。在InnoDB里，主键索引也被称为聚簇索引（clustered index）。
非主键索引的叶子节点内容是主键的值。在InnoDB里，非主键索引也被称为二级索引（secondary index）。
PS：主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
基于主键索引和普通索引的查询有什么区别？
- 如果语句是select * from T where ID=500，即主键查询方式，则只需要搜索ID这棵B+树；
- 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程称为回表。
也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。
索引维护
对于主键不是递增的表，在插入数据时，如新插入数据ID值为400，则需要逻辑上挪动后面的数据，空出位置。
而更糟的情况是，如果R5所在的数据页已经满了，根据B+树的算法，这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂。
除了性能外，页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约50%。当
然有分裂就有合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程。
数据库有哪些索引
在MySql数据库中，有四种索引：聚集索引（主键索引）（聚簇索引）、普通索引、唯一索引以及全文索引（FUNLLTEXT INDEX）
索引又可分为聚簇索引和非聚簇索引两种
唯一索引
一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
CREATE UNIQUE CLUSTERED INDEX myclumn_cindex ON mytable(mycolumn)
聚簇索引与非聚簇索引
可以理解为主键索引与普通索引
| 聚簇索引：是对磁盘上实际数据重新组织以按指定的一个或多个列的值排序的算法。特点是存储数据的顺序和索引顺序一致，且一个表只能有一个聚簇索引，因为物理存储只能有一个顺序。主键索引一般都是聚簇索引 非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚簇索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引； 一般情况下主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引。因为物理存储只能有一个顺序。 聚簇索引的叶子节点就是数据节点（Innodb的B+树的主键对应的数据节点），而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针。 聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。 相比之下，聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。 建立聚簇索引的语句： CREATE CLUSTER INDEX index_name ON table_name(column_name1,...);
|
问：主键索引查询只会查一次，而非主键索引一定需要回表查询多次吗？
答：通过覆盖索引也可以只查询一次
覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。
当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。
如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。
当我们通过SQL语句：select key2 from covering_index_sample where key1 = 'keytest';的时候，就可以通过覆盖索引查询，无需回表。
注：如果这个覆盖索引是一个前缀索引，那么它依然需要回表，因为系统并不确定前缀索引的定义是否截断了完整信息。
问：以下重建索引的步骤是否存在问题？
重建普通索引 k
alter table T drop index k; alter table T add index(k);重建主键索引
alter table T drop primary key; alter table T add primary key(id);
答：重建索引k的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，可以用这个语句代替 ： alter table T engine=InnoDB。
全文索引
| 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 select * from 表名 where 标题 like '%xxx%' or 内容 like '%xxx%' or 作者 like '%xxx%'; 这种搜索效率无比底下 全文索引是为了使得“关键词搜索”功能更加的高效能。 我们有这么一张数据表： 文章id 文章标题 文章内容 1 超级塞亚人 我是超级塞亚人我喜欢吃苹果，我不是233大国的人，也不是地球人 2 我233大国威武，我233大国13亿人，我233大国 3 我喜欢游泳 游泳有很多好方法 4 动画片 我儿子喜欢看动画片，尤其是七龙珠，因为里面有塞亚人，而且塞亚人喜欢吃苹果,他们不是地球人 5 运动 我喜欢运动，喜欢跑步，喜欢游泳，喜欢健身，喜欢xxoo 6 打炮 我是一个二战的老兵，这是我的回忆录，我最幸福的时光就是在233大国吃着苹果打炮 7 。。。 8 。。。 9 。。。 然后，根据以上的文章内容，如果建立了一个索引文件（这里忽略索引文件的数据结构，仅仅以一种易于理解的方式呈现）： 关键词 文章id 塞亚人 1,4 苹果 1,4,6 233大国 1,2,6 地球 1,4 游泳 3,5 七龙珠 4 喜欢 1,4,5,6 那么当我想搜索 “塞亚人”的时候，这个索引文件直接告诉我在文章id为1和4的文章里有这个词。 这个索引文件就是“全文索引”。 如何使用全文索引和分词的方式来帮助优化你的搜索呢？ 需要工作的程序：索引程序，分词程序，数据库。 工作原理： 1、索引程序从数据库读取数据，比如上面例子中的数据表，索引程序通过sql语句：select 文章id,文章标题，文章内容 from 文章表.获得文章的相关数据 2、索引程序对需要索引的内容进行“分词”，而这里的分词就是调用分词程序啦！ 3、索引程序对分好词的一个个词条加入索引文件。 在你写的代码里，原来到数据库－－－－like %xxx%－－－－－的语句就变成了到索引文件里去查找，从而找到相应的数据（这点相信你已经理解啦！） 创建全文索引的两种方法： 1.在建表语句中
2.在已知表中 ALTER TABLE article ADD FULLTEXT INDEX fulltext_article(title,content); 具体如何使用全文索引呢？ 不用全文索引时的写法：SELECT * FROM article WHERE content LIKE ‘%查询字符串%’; 使用全文索引：SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’); 注意： 1、MySql自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx（斯芬克斯）/Coreseek技术来处理中文。 2、使用MySql自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySql全文索引所能找到的词默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。 3、如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。 |
索引的最左前缀原则
在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
索引下推
Index Condition Pushdown (ICP) ，Mysql 5.6添加，用于优化数据查询。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。
用下面这种场景进行介绍
假设有如下查询语句：select * from tuser where name like '张%' and age=10 and ismale=1; （有联合索引 name,age）
我们知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”
以下是Mysql 5.6 之前的查询流程：
以下是Mysql 5.6 时的查询流程：（使用了索引下推）
InnoDB在(name,age)索引内部就判断了age是否等于10，对于不等于10的记录，直接判断并跳过。在我们的这个例子中，只需要对ID4、ID5这两条记录回表取数据判断，就只需要回表2次。
使用索引一定能提高查询性能吗？
通常,通过索引查询数据比全表扫描要快，但是我们也必须注意到它的代价.
索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.
索引不但会使得插入和修改的效率降低，而且在查询的时候，有一个查询优化器，太多的索引会让优化器困惑，可能没有办法找到正确的查询路径，从而选择了慢的索引。
索引范围查询(INDEX RANGE SCAN)适用于两种情况:
1.基于一个范围的检索,一般查询返回结果集小于表中记录数的30%
2.基于非唯一性索引的检索
3.直接晋升为覆盖索引，避免多次查表
哪些情况下设置了索引但是无法使用
根本原因是查询优化器决定不使用索引：
一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
有时候查询语句没有按照索引的要求来也会导致无法使用索引，如下：
- 建立组合索引，where条件单字段。INDEX(a,b,c)，当条件为a或a,b或a,b,c或a,c时都可以使用索引，但是当条件为b,c时将不会使用索引。也就是说不是使用的第一部分，则不会使用索引。如果是INDEX(a,b)，即使查询的where是b,a，由于sql优化器的优化作用，会把b,a换成a,b，这样就可以走索引了。如果是index(a,b,c),查询是(a,b,c,d)不会走索引
- 条件中用or，即使其中有条件带索引，也不会使用索引查询（这就是查询尽量不要用or的原因，用in）（注意：使用or，又想索引生效，只能将or条件中的每个列都加上索引，这样查询时每个列都会单独使用它们自己的索引）
- like的模糊查询的模糊词在字符串前面，比如以%或_开头，索引失效。
- 在使用不等于(is null、is not null、!= 、<>)的时候无法使用索引会导致全表扫描。
- 类型错误，如字段类型为varchar，where条件用number。
- 对索引应用内部函数，这种情况下应该建立基于函数的索引。
- 索引列不能是表达式(id+1=5)的一部分，也不能是函数的参数
- 如果MySQL预计使用全表扫描要比使用索引快，则不使用索引
哪些情况下需要设置索引、哪些情况下不需要
| 需要： 1).主键自动建立唯一索引 不需要： 1).表记录太少 4).where条件里用不到的字段不创建索引 |
什么情况下应该使用组合/联合索引而非单独索引
| 假设有条件语句A=a AND B=b，如果A和B是两个单独的索引，在AND条件下只有一个索引起作用，对于B则要逐个判断，而如果使用组合索引(A, B)，只要遍历一棵树就可以了，大大增加了效率。但是对于A=a OR B=b，由于是 或 的关系，因而组合索引是不起作用的，此时可以使用单独索引，这个时候，两个索引可以同时起作用。 在建立联合索引的时候，如何安排索引内的字段顺序？ 评估标准是：索引的复用能力。因为可以支持最左前缀，所以当已经有了(a,b)这个联合索引后，一般就不需要单独在a上建立索引了。 因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。 那么，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、(b) 这两个索引。 这时候，我们要考虑的原则就是空间了。也就是说，如果b的大小是比较小的，如boolean、int类型， 那么可以再多建立一个b索引 下面通过一个例子来加深理解
|
MySQL中索引是如何组织数据的存储的
| 假如有如下数据表：
对于表中每一行数据，索引中包含了last_name、first_name、dob列的值，下图展示了索引是如何组织数据存储的。
可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。 |
普通索引和唯一索引，应该怎么选择？
| 在介绍这两者的区别之前，我们先来介绍change buffer： 什么是change buffer？ 1. 如果数据页在内存中（buffer pool中时）就直接更新 1、从磁盘读入数据页到内存（老版本的数据页）； 2、从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页； 3、写redo log。这个redo log包含了数据的变更和change buffer的变更。 而唯一索引的更新就不能使用 change buffer：对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一性约束。那么必须将数据页读入内存才能判断。比如，要插入(4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录 都已经读入内存中了，那直接更新内存会更快，没有必要使用change buffer了。 所以，只有普通索引才能使用change buffer，考虑使用普通索引还是唯一索引，如果能保证不会数据重复，那么最好使用普通索引（可以使用change buffer，且两类索引查询能力没有区别） 注意：不是所有的场景用change buffer都能加速： 1. 设想一个对于写多读少的业务来说，change buffer 记录的变更越多越划算，例如账单类日志类 2. 反过来，一个业务的更新模式是写入之后马上会做查询，change buffer里的内容不多，由于马上做查询要访问数据页，这样的io次数不会减少 如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据？ 虽然写入时只更新了内存，但是在事务提交的时候，change buffer的操作也会记录到redo log，所以崩溃恢复的时候，change buffer也能找回来，即数据可以找回来。 所以普通索引和唯一索引，应该怎么选择？ 查询时：两种索引查询性能几乎没差别 更新时：大部分场景下，因为有change buffer的存在，普通索引的更新速度会比唯一索引的快（特别适用于写多读少的场景）（如果所有的更新后面，都马上伴随着对这个记录的查询，那么应该关闭change buffer） |
扩展阅读：
https://mp.weixin.qq.com/s/9yeModGuGvDu5S0bW9sU6w
3080
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
