索引

MySQL的基本存储结构是页:

页的中间有一部分专门用来存储行记录

各个数据页之间组成一个双向链表，每个数据页里面的记录又组成单向链表。

mysql底层结构是采用B+树。

在默认的情况下，我们会为id创建索引，每个数据页存储的是相关的数据项，也可能是目录项

B+索引和hash索引： 

   B+树是多路平衡树，从根节点到叶子节点的高度差值不超过1，如果我们对这棵树增删改查的话，会破坏他原有的结构，就需要我们做额外的操作来平衡这颗二叉树，因此建索引会降低我们增删改的速度。

   哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。(无法利用索引排序，不支持最左匹配原则，大量重复键效率低，也不支持范围查询)

   主流的还是使用B+树索引比较多，对于哈希索引（hash索引只能用于一些=的查询），InnoDB是自适应哈希索引的（hash索引的创建由InnoDB存储引擎引擎自动优化创建，我们干预不了）

 

以某一列建立索引实际上就是对这个列的数据重新排序，但他会覆盖本身的以id为索引吗？

答：

    之前理解错了，并不是说对整条数据来进行重新排，只是把每一条数据的id和这一列的值取出来，重新建一个索引记录。也就是：    

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

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

区别：

   聚集索引在叶子节点存储的是表中的数据

   非聚集索引在叶子节点存储的是主键和索引列

使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)

但是总进行这样回表的操作实际上也是很慢的，这里我们可以通过覆盖索引解决这个问题，简单来说查询的列与索引对应，这样我们就可以直接从非聚集索引中取出我们所要查的数据不用再进行回表的操作了。覆盖索引通常来讲就建立多列索引（联合索引）。

 

这里又要引出一个问题，多个单列索引和联合索引的区别？

答：首先多个单列索引肯定是会生成多个索引树的，但联合索引只会生成一个索引树。对于联合索引有一个这样通俗的理解：利用索引中的附加列，您可以缩小搜索的范围，但使用一个具有两列的索引 不同于使用两个单独的索引。复合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果您只知道名不姓，电话簿将没有用处。所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

此问题参考：https://blog.csdn.net/Abysscarry/article/details/80792876

 

上面的解释也就解释了索引的最左匹配原则：

索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。

如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。

因此，列的排列顺序决定了可命中索引的列数。

例子：

如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。(很简单：索引命中只能是相等的情况，不能是范围匹配)，第一个范围查询我们还是可以查的，但之后的就不能按索引的方式来查询了。

 

在我们的mysql中，它也会根据最左匹配原则和列的排列顺序尽可能多的匹配更多的索引列：

例子：

  如有索引(a, b, c, d)，查询条件c > 3 and b = 2 and a = 1 and d < 4与a = 1 and c > 3 and b = 2 and d < 4等顺序都是可以的，MySQL会自动优化为a = 1 and b = 2 and c > 3 and d < 4，依次命中a、b、c。

 

关于建立索引的好处：

1.能够提高数据检索的效率，降低数据库的IO成本。

2.每个索引中的数据都是按照索引键键值进行排序后存放的，当Query 语句中包含排序操作时，如果排序字段和索引键字段刚好一致，那么就可以加快很多速度。

3.查询语句中有分组操作，分组之前是需要排序的，那么如果根据索引列来分组，就可以减少排序的这个操作。

 

判断是否需要建立索引：

1.较频繁的作为查询条件的字段应该创建索引

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

3.更新非常频繁的字段不适合创建索引

4.太长的列是不适合建立索引的，但是可以只建立部分索引，比如以前十位为索引。

 

加索引后的锁：（重点）

读不加锁（由于MVCC机制的存在），但是在串行化的隔离级别下面（读也会锁住）

根据主键来查（锁住主键索引的那条记录）

根据非主键索引来查，额外的索引树的那条记录会锁住，主键索引树的那条记录也会锁住

根据一个没建索引的字段来查，会锁住整个表。

https://www.jianshu.com/p/13f5777966dd

 

如何在我们的SQL语句中判断我们的索引是否生效：

在实际的生产环境中，通过慢查询日志，发现执行时间很长的sql语句

explain的使用：

explain select *from where id='123'

会有一个type类型，可以通过这个key类型查看实际使用的索引，若没有使用索引，显示为null，如果使用全表索引，就是ALL

什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢？ 

查询优化器 一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。 在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。 优化过程大致如下： 1、根据搜索条件，找出所有可能使用的索引 2、计算全表扫描的代价 3、计算使用不同索引执行查询的代价 4、对比各种执行方案的代价，找出成本最低的那一个

关于一条sql语句在mysql中是如何执行的：https://www.cnblogs.com/smallJunJun/p/11081468.html

什么时候使用全表扫描比用索引扫描效率更高？

 1.数据表是在太小了，做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。 

 2.当查询一些没有合适索引字段的。（引擎会自动转为全表扫描）

 3.可以用Select * from t1 force(index_for_column)可以强制使用索引

 

 全表扫描可以顺序读取磁盘，一次读一大块。

 索引很可能只能随机读取磁盘，一次读一条。

 优化器基于代价模型，选它认为更快的方式执行。

 当使用非主键索引找到主键，再去读取主键索引树，则读取磁盘的次数是查询包含的记录数T，而如果表扫描则读取磁盘的次数是存储记录的块数B，如果T>B 的话索引就没有优势了。对于大多数数据库来说，这个比例是10%（oracle，postgresql等），即先对结果数量估算，如果小于这个比例用索引，大于的话即直接表扫描。

对于外键加索引的问题：

当对父表进行更新的时候，如果在子表中的外键没有使用索引，则在更新的过程中整个子表将被锁定，而往往实际上并不需要锁定整个子表，而仅仅需要锁定子表中的几条记录。这样就会大大影响数据库访问的并发性，甚至有可能造成死锁的情况。

当删除父表中的记录时，如果在子表中的外键没有使用索引则当执行该操作时会对子表进行全表的扫描，而事实上这个全表的扫描是不需要的。更坏的情况是，如果删除多个父表中的记录，每删除一条记录则会进行一次全表扫描，可想而知，对于性能的影响是多么的大！
对于父表和子表的连接查询，情况也是类似的。当进行这种连接查询时，如果不对外键使用索引则会发现查询的速度大大降低。
在mysql中，好像默认是对外键加索引的。

  

  1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配。

  2，尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少。

  3，索引列不能参与计算，尽量保持列“干净”。比如，FROM_UNIXTIME(create_time) = '2016-06-06' 就不能使用索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP('2016-06-06')。

  4，尽可能的扩展索引，不要新建立索引。比如表中已经有了a的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。

  5，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行SQL时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。

 

参考：

https://juejin.im/post/5b55b842f265da0f9e589e79