Mysql索引
问题mysql的查询表记录数量的时候走的哪个索引? 我以为是走主键索引, 实际走的是二级索引
指定查询走主键索引耗时(0.815s)
SELECT count(*) FROM rel_group_user force index(primary)
不走主键索引耗时(0.422S)
SELECT count(*) FROM rel_group_user force index(rel_group_user_biz_user_id_index)
不指定索引时默认走的索引
可以看到走主键索引的时候效率比较差。那么是为什么呢。
平时我们检索一列的时候，基本上等值或范围查询，那么索引基数大的索引必然效率很高。但是在做count(*)的时候并没有检索具体的一行或者一个范围。那么选择基数小的索引对count操作效率会更高。在做count操作的时候，mysql会遍历每个叶子节点，所以基数越小，效率越高。mysql非聚簇索引叶子节点保存的主键ID，所以需要检索两遍索引。但是这里相对于遍历主键索引。及时检索两遍索引效率也比单纯的检索主键索引快。
聚蔟索引的叶子节点存储了完整的行数据,因为包含完整的行数据所以一个叶子节点存储的行数相比非聚蔟索引节点存储的行数要少
所以聚蔟索引的叶子节点数量要比非聚蔟索引的叶子节点要多,所以count(*)时走非聚蔟索引时要快
B-Tree
B-树有如下特点:
首先它是个搜索树 左子树都比 父结点小, 右子树都比父结点大
- 所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
- 任何一个关键字出现且只出现在一个结点中；
- 搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）；
- 在关键字全集内做一次查找,性能逼近二分查找；
B树的不足：
不利于范围查找(区间查找)，如果要找 0~100的索引值，那么B树需要多次从根结点开始逐个查找。 而B+树由于叶子结点都有链表，且链表是以从小到大的顺序排好序的，因此可以直接通过遍历链表实现范围查找。
B+ 树
- B+树内部有两种结点，一种是索引结点，一种是叶子结点。
- B+树的索引结点并不会保存记录，只用于索引，所有的数据都保存在B+树的叶子结点中。而B树则是所有结点都会保存数据。
- B+树的叶子结点都会被双向连成一条双向链表。叶子本身按索引值的大小从小到大进行排序。即这条链表是 从小到大的。多了条链表方便范围查找数据。
- B树的所有索引值是不会重复的，而B+树 非叶子结点的索引值最终一定会全部出现在 叶子结点中。
B-树和B+树的区别
1.B+树内节点不存储数据，所有 data 存储在叶节点导致查询时间复杂度固定为 log n。而B-树查询时间复杂度不固定，与 key 在树中的位置有关，最好为O(1)。
- B+树叶节点两两相连可大大增加区间访问性，可使用在范围查询等，而B-树每个节点 key 和 data 在一起，则无法区间查找。
3.B+树更适合外部存储。由于内节点无 data 域，每个节点能索引的范围更大更精确
1）B树的节点(根节点/父节点/中间节点/叶子节点)中没有重复元素，B+树有。
2）B树的中间节点会存储数据指针信息，而B+树只有叶子节点才存储。
3）B+树的每个叶子节点有一个指针指向下一个节点，把所有的叶子节点串在了一起。
- B+树的查询比较稳定,因为数据只存储在叶子节点, B-Tree查询不稳定,因为每个节点都存储着数据查询时可能在非叶子节点终止
MySQL为什么使用B+Tree
二叉搜索树(BST) 平衡树(AVL) 红黑树(RBT) 等数据结构也可以用来实现索引，但是文件系统及数据库系统普遍采用B-/+Tree作为索引结构，主要是因为索引数据量大不可能全部加载到内存中, 二叉搜索树(BST) 平衡树(AVL) 红黑树(RBT) 每个节点存储的数据量有限消耗的磁盘IO多。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。下面先介绍内存和磁盘存取原理，然后再结合这些原理分析B-/+Tree作为索引的效率。
为什么不用B-Tree
- 因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出）
指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；
- B-Tree的范围查找时,需要对树做中序遍历, 而B+Tree直接从叶子节点根据指针顺序遍历即可
在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k
而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。
下面几张图可以帮你理解最小存储单元：
文件系统中一个文件大小只有1个字节，但不得不占磁盘上4KB的空间。
磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。
在MySQL中我们的InnoDB页的大小默认是16k，数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。
InnoDB的一棵B+树可以存放多少行数据？
答案：约2千万
为什么是这么多？
那么现在需要计算出非叶子节点能存放多少指针？
其实这也很好算，假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节
我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。
那么可以算出一棵高度为2的B+树，能存放1170X16=18720条这样的数据记录。
根据同样的原理可以算出一个高度为3的B+树可以存放：1170 X1170X16=21902400条这样的记录。
所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。
在查找数据时，一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
当然也可以通过参数设置：
主键索引#
在MySQL中，索引属于存储引擎级别的概念。不同存储引擎对索引的实现方式是不同的，这里主要看下MyISAM和InnoDB两种存储引擎的索引实现方式。
MyISAM索引实现#
MyISAM引擎使用B+Tree作为索引结构时叶子节点的data域存放的是数据记录的地址。如下图所示：
由上图可以看出：MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，因此MyISAM的索引方式也叫做非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现#
InnoDB的主键索引也使用B+Tree作为索引结构时的实现方式却与MyISAM截然不同。InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶子节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
InnoDB存储引擎中的主键索引(primary key)又叫做聚集索引(clustered index)。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键(MyISAM可以没有)，如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
(详情见官方文档：dev.mysql.com/doc/refman/…)
聚集索引这种实现方式使得按主键搜索十分高效，直接能查出整行数据。
在InnoDB中，用非单调递增的字段作为主键不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单增的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，因而使用递增字段作为主键则是一个很好的选择。
非主键索引#
MyISAM索引实现#
MyISAM中，主键索引和非主键索引(Secondary key，也有人叫做辅助索引)在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。这里不再多加叙述。
InnoDB索引实现#
InnoDB的非主键索引data域存储相应记录主键的值。换句话说，InnoDB的所有非主键索引都引用主键的值作为data域。如下图所示：
由上图可知：使用非主键索引搜索时需要检索两遍索引，首先检索非主键索引获得主键(primary key)，然后用主键到主键索引树中检索获得完整记录。
那么为什么非主键索引结构叶子节点存储的是主键值，而不像主键索引那样直接存储完整的一行数据，这样就能避免回表二次检索？
这样做一方面节省了大量的存储空间，另一方面多份冗余数据，更新数据的效率肯定低下，另外保证数据的一致性是个麻烦事。
所以也就明白为什么不建议使用过长的字段作为主键，因为所有的非主键索引都引用主键值，过长的主键值会让非主键索引变得过大。
联合索引#
官方文档：dev.mysql.com/doc/refman/…
比如INDEX idx_book_id_hero_name (book_id, hero_name) USING BTREE，即对book_id, hero_name两列建立了一个联合索引。
A multiple-column index can be considered a sorted array, the rows of which contain values that are created by concatenating the values of the indexed columns.
联合索引是多列按照次序一列一列比较大小，拿idx_book_id_hero_name这个联合索引来说，先比较book_id，book_id小的排在左边，book_id大的排在右边，book_id相同时再比较hero_name。如下图所示：
了解了联合索引的结构，就能引入最左前缀法则：
If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to look up rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).
就是说联合索引中的多列是按照列的次序排列的，如果查询的时候不能满足列的次序，比如说where条件中缺少col1 = ?，直接就是col2 = ? and col3 = ?，那么就走不了联合索引，从上面联合索引的结构图应该能明显看出，只有col2列无法通过索引树检索符合条件的数据。
根据最左前缀法则，我们知道对INDEX idx_book_id_hero_name (book_id, hero_name)来说，where book_id = ? and hero_name = ?的查询来说，肯定可以走索引，但是如果是where hero_name = ? and book_id = ?呢，表面上看起来不符合最左前缀法则啊，但MySQL优化器会根据已有的索引，调整查询条件中这两列的顺序，让它符合最左前缀法则，走索引，这里也就回答了上篇《一文学会MySQL的explain工具》中为什么用show warnings命令查看时，where中的两个过滤条件hero_name、book_id先后顺序被调换了。
至于对联合索引中的列进行范围查询等各种情况，都可以先想联合索引的结构是如何创建出来的，然后看过滤条件是否满足最左前缀法则。比如说范围查询时，范围列可以用到索引(必须是最左前缀)，但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引.
局部性原理与磁盘预读
由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
当一个数据被用到时，其附近的数据也通常会马上被使用。
程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。
预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。
所以IO一次就是读一页的大小
