MySQL数据库——索引

如果SQL查询比较慢，就会要给字段加索引。索引就像书的目录，可以提高查询效率。

索引的优点：加快查找的速度，加快分组和排序字段的速度，加快表和表之间连接的速度。

缺点：耗费空间，而且增删改的时候还要动态维护索引耗费时间。

索引的作用：数据是存在磁盘的，如果没有索引，查找的时候要把所有数据都一个一个放到内存读取。如果用了b+树索引就一层一层放到内存，大大提高了效率。b+树是索引数据和业务数据分离的，一般 高度在2到4。

什么时候创建索引：需要查询的字段，需要连接的字段，需要排序的字段。

什么时候不创建索引：where条件中用不到的字段，表记录较少的字段，需要经常增删改的字段，参与列计算的字段，区分度不高的字段。

 

 

索引的分类：主键索引、唯一索引、普通索引、组合索引、全文索引。

（1）主键索引：非空唯一索引，一个表只有一个主键索引。

在InnoDB中，主键索引的B+树包含表数据信息。PRIMARY KEY(key)

（2）唯一索引：可以有NULL值，但是不能重复。UNIQUE (key)

（3）普通索引：可以出现相同的索引内容。

（4）组合索引：对表上的多个列进行索引。

 

 

Q：主键是怎么选择的呢？

答：索引组织表有且只有一个主键。如果显示设置PRIMARY KEY，该设置的key为表的主键。如果没有设置，就从非空唯一索引中选择。如果没有非空唯一索引，就自动生成一个6字节的_rowid作为主键。

B+树是高度平衡的多叉搜索树。一个节点的长度是一页16K(磁盘上物理页的四倍，连续四块物理页)。

每次访问一个节点都是一次磁盘IO。 

聚集索引和辅助索引都是一棵B+树。

（1）聚集索引：按照主键构造的B+树，叶子节点存放具体行数据，数据也是索引的一部分。

select * from user where id >= 18 and id < 40

（2）辅助索引：叶子节点不包含行记录的全部数据。存储“索引+主键key”，即辅助索引的叶子节点中，除了用来排序的key，还包含一个bookmark，该书签存储了聚集索引的key。

select * from user where luckynum = 33;

实现索引的方式很多，常见的索引模型有哈希表、有序数组、搜索树。

哈希表这种结构适用于只有等值查询的场景，比如Memcached以及其他一些NoSQL，不适用范围查询。

有序数组在等值查询（二分法）和范围查询（二分法先找左）都非常优秀，但是更新数据麻烦得很，所以只适用于静态存储引擎。

二叉搜索树BST，等值查询是O(logN)，更新也是，但是要是平衡二叉树。

二叉搜索树效率高，但是实际却不使用，因为耗空间。索引不仅存在内存中，还要写到磁盘里面。树高有多少，就要访问多少次磁盘。为了访问尽量少的数据块，因此决定用N叉树替换。N叉树在读写上有性能优点，同时也适配磁盘的访问模式。跳表、LSM树等数据结构也被用于引擎设计。

 

关于InnoDB的索引模型。在InnoDB中，表都是根据主键顺序以索引形式存放，这种存储方式的表称为索引组织表。使用B+树。

索引结构是B+树，进行查找的时候现在根节点二分查找，然后递归往下查找，找到叶子节点，然后在叶子节点进行二分查找，找出key所对应的数据项。

哈希索引和B+树的区别：哈希是无序的，不支持范围查找，不支持模糊查询和最左前缀匹配，存在哈希冲突因此性能不稳定。

此外，从内存角度来说，哈希需要把数据全部加载到内存中，如果数据量大，将是一件很消耗内存的事情。而采用B+树，是基于按照节点分段加载，由此减少内存消耗。从业务场景来说，对于查找一个数值，哈希确实更快，但是数据库中经常查询多条数据，这时候由于B+树的有序性，而且叶子节点用链表相连，它的查询效率会比哈希快得多。

B+树相比B树的优势。

B+树业务数据都在叶子节点上，只需要扫一遍叶子节点就可以。B树的业务数据和索引数据不分离，在分支节点也存数据，因此需进行中序遍历扫描查询。所以B+树在区间查询的情况更有优势。

B+树非叶子节点只存key值，所以以页为单位的索引可以存放更多的节点，即同样大小的节点，B+树相对于B树能有更多的分支，这使得这棵树更加矮胖，查询时做的IO操作次数就更少。
B+树的查询效率更加稳定，都是从根节点到叶子节点的路，路径长度相同。

最左匹配原则：如果sql语句用到了组合索引的最左边索引，就可以利用组合索引去匹配。当遇到范围查询（大于＞，小于＜，between，like）就会停止匹配，后面的字段都不会用到索引。

 

可以根据叶子节点的内容，B+树索引类型分为主键索引和非主键索引。

主键索引的叶子节点存整行数据，也叫聚簇索引。比如select * from T where ID=500；只需搜索ID这棵B+树。

聚集索引是一棵树，使用表的主键构造主键索引树，同时叶子节点存放整张表的记录。聚集索引叶子节点的存储逻辑连续，使用双向链表连接，叶子节点按照主键顺序排序。

非主键索引的叶子节点存主键的值，也叫二级索引。比如select * from T where k=5；需要先搜索k索引树，得到ID的值为500，再到ID索引树搜索一次。这个过程被叫做回表。

回表很麻烦的。比如执行select * from T where k between 3 and 5，在下面的结构中，需要执行几次树的搜索操作，会扫描多少行？

 过程：首先在k索引树找到k=3取得ID=300，再去ID索引树查到R3。接下来k=5，k=6。由于查询结果所需要的数据只在主键索引上，所以不得不回表。可不可以经过索引优化，避免回表过程？答案就是覆盖索引。覆盖索引其实就是联合索引。由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。关于覆盖索引：对于二级索引，只用从索引列中就能取得，不需要回表进行对主键索引的二次查询，即要查询的列被所使用的索引覆盖。不是所有类型的索引都可以成为覆盖索引，覆盖索引要存储索引列的值，而哈希索引、全文索引不存储索引列的值，所以使用B+树做覆盖索引。

比如在市民信息表上，将身份证号和名字建立联合索引。现在有一个高频请求，根据市民的身份证号查询他的名字，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不需要回表查询整行记录，减少语句的执行时间。

B+树这种索引结构，可以利用索引的最左前缀来定位记录。最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符。

为了维护B+树的索引有序性，需要做索引维护。当插入的时候，数据页满了，就要新申请页，然后挪动部分数据过去，就是页分裂，也影响数据页的利用率。当删除的时候，利用率很低之后，就要将数据页做合并。

哪些场景下应该使用自增主键，哪些场景下不应该。自增主键是指自增列上定义的主键。AUTO_INCREMENT。

 

索引设计原则：区分度越高越好，使用短索引，不是越多越好，利用最左前缀原则。

索引失效：组合索引不用最左边字段的时候，%开头的like查询，列类型发生隐式转换，判断索引列是否不等于某个值，对多个索引列进行运算（如or连接等）

前缀索引是指对文本或字符串的前几个字符建立索引，索引长度更短，查询速度更快。

innodb存储引擎是mysql默认的事务型存储引擎，基于聚集索引建立。内部做了很多优化，比如能自动在内存中创建自适应hash索引，加速读操作。适用于需要事务支持，并且具有较高的并发读写频率的场景。

innodb和myisam的区别（是否支持）：行级锁，事务和崩溃恢复，外键，mvcc，聚集索引。

当表有近千万的数据查询比较慢的时候，要限定数据的范围（比如控制在一个月内），也可以通过数据库读写分离的拆分方案（主库负责写从库负责读），也可以通过分库分表的方式。数据量大，添加索引，主从分离，分库分表。

主从同步的作用：读写分离；主服务器生成实时数据，从服务器分析数据；数据备份。

垂直分表，根据业务进行划分，行记录会变小，那么数据页就可以存更多记录，在查询时减少io次数。但是会出现主键冗余，表连接join操作，还是可能存在单表数据量大的问题。

水平分表，根据一定规则范围划分。单表的数据库变少了，同时表结构相同。还是可能出现分表事务一致性问题，跨节点的join性能表，分片在扩容的时候需要迁移。