mysqlB+tree

什么是索引：
    相当于一本书的目录，能让我们快速的找到对应的数据。
    索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。
    我们建立了索引后，比如id，我们能够将id与磁盘地址做一个映射，
        能够加速的找到磁盘地址对应的数据行，而不是全表扫描。
为什么要用索引：
    1.索引能极大的减少存储引擎需要扫描的数据量。
    2.索引，可以把随机io变成顺序io。
    3.索引可以帮助我们在进行分组，排序等操作时，避免使用临时表。
二叉查找树：
    右侧倾斜，相当于链表，数据量一大，查询速度会变慢。
    使用时间戳和主键自增字段做索引是最常见的，
    所以数据量一大的时候并不能提高搜索效率，
    随着表的增长，树越来越倾斜，查询效率越来越低，
    所以建议使用平衡二叉查找树或完全平衡二叉查找树。
平衡二叉树：
    1.子节点和父节点的高度差不能超过1。
    2.如果节点倾斜，会做一个左旋，右旋的操作，有一个平衡因子的概念。
    这是就是关键字索引，不足：
        1.它太深了：
            数据的深度决定了它的io操作磁盘的次数，io又是非常耗时的。
        2.它太小了：
            每一个磁盘块（节点/页）保存的数据量太小了，
                操作系统与磁盘交互的单位是以页为单位，
                一次交换加载了4k的数据回来。
            没有很好的利用操作磁盘io的数据交换特性（4k，ssd：4k对齐），
            也没有利用好磁盘io的预读能力（空间局部性原理），
            从而带来的频繁的io操作。（备注： oracle从早年的面向记录后来转向面向数据块，
                            数据获得极大提升，后来又把备份以及恢复功能也改成了面向数据块，
                            从而实现了FlashBack以及后来其他的高级功能）
多路平衡查找树，B-Tree(二三树)：
    绝对平衡树，
    解决：1.路数越多，高度越低
      2.比如一个节点定义4kb，比如一个4个字节的int类型的索引，我们一次可以拿到1024个关键字拿回来，就可以解决数据存储量小的问题。
      （备注：mysql的一个页（节点）大小的定义是16kb，很好的利用了预读能力。mysql最小单位是页（磁盘块））
mysql B+Tree:
    1.B+树节点关键字搜索采用左闭合区间（<= 包含边界）    。
    2.B+树非叶子节点不保存数据相关信息，只保存关键字和叶子节点的引用。
    3.B+树关键字对应的数据保存在叶子节点中。
        优点：节省空间（节点中只保存关键字，不保存数据了）。
    4.B+树叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系。
        优点：做排序（线性指向下一个节点的头，形成一个链表。周围查找优势）
为什么选用B+Tree:
    1.B+树是B树的升级版（多路平衡查找树），它拥有B树的优势
    2.B+树的扫库扫表能力更强，（B树也相当于随机io,B+树叶子节点均为索引）
    3.B+树的磁盘读写能力更强。（内部节点只保存具体数据的指针，空间利用的更好，内部节点的空间更小）
    4.B+树的排序能力更强。
    5.B+树的查询效率更加稳定。
        和B树不同于，B树把数据保存在节点上，B+树内部节点存储指针，B树io操作只需要找到就可以直接返回， 
        B+树采用闭合区间，需要根据顺序查找到指针找到对应的具体数据，
        比如一亿条数据，B+树的查询效率是差不多的，因为可能需要查询几千上万层才能找到数据，
        而B树不一样，运气好第二层就找到了，运气不好可能需要找到一万层，效率不稳定。
MySQL B+Tree索引体系形式-Myisam：
    生成三个文件：
        1.表定义文件.frm。
        2.数据文件.MYD。
        3.索引文件.MYI。
    MYI与MYD关联关系：
        MYI B+Tree保存索引磁盘地址，相当于指针，根据这个指针找到MYD对应的数据行。
MySQL B+Tree索引体系形式-innodb:
    生成两个文件：
        1.表定义文件.frm。
        2.数据data文件.ibd。
    它将索引和数据行绑定到一起。（聚集索引：数据库表行中数据的物理顺序与键（索引）的顺序相同）
    没有索引的话它会自动创建一个6位int型隐藏的索引（表空间里，看不到）
    辅助索引：
        辅助索引最后保存一个主键索引的指针，扫完这个Tree再扫描主键索引的Tree。
    1.innodb认为最常用的是主键索引，它会放弃一些东西，宁愿扫描完成一次之后再扫描一次主键索引，这是innodb设计的初衷。
    2.为了防止数据的变化。

番外篇：干货
    1.离散型越高，选择性越好。
    2.最左匹配原则：对索引中关键字进行计算（对比），一定是从左往右依次进行，且不可跳过。
        所有like语句不能使用第一个%，这样就违法了最左匹配原则。
    3.联合索引：
        单例索引是特殊的联合索引，
        1.经常用的列优先（最左匹配原则）
        2.选择性（离散度）高的列优先（离散度高原则）
        3.宽度小的列优先（最少空间原则）
    4.不能让索引重复。（比如有单列索引，再创建复合索引）
    5.覆盖索引：如果查询列可通过节点中的关键字直接返回，则该索引称之为覆盖索引。
        比如（创建了id索引：select id from user）
        优点：覆盖索引可减少数据库io的操作，将随机io变成顺序io，可提高查询的性能。
总结：
    1.索引列的数据长度能少就少。
        节省空间，能存储的关键字越多，路数越多，深度越短，io的操作就越少。
    2.索引一定不是越多越好，越全越好，一定是最合适的。
        影响插入，删除
    3.匹配列前缀
        like abc% 能用到索引。
        like %abc% ， like %abc 用不到索引。
    4.where条件中not in和<>操作无法使用索引。
    5.匹配范围值，order by group by 也可用到索引 。
        B+树的结构本来就是有序的
    6.多用指定列查询，只返回自己想得到的数据列，少用select *
    7.联合索引中如果不是按照索引最左列开始查询将无法用到索引（最左匹配原则）。
    8.联合索引中最左前列精确条件匹配，后面并范围匹配另外一列可以用到索引。
    9.联合索引中前面范围匹配，后面所有的列都无法使用索引。
亮点：
    like abc% 有时候也不会使用索引，在离散性高的时候，
    比如：我测试过，通过for循环添加了十万条数据 张三1，张三2，张三3，……
    like 张三%是不会使用索引的，like 张三1% 也不会，导致全表扫描。（查看查询状态:explain）
    like 张三11111%会使用，因为离散性高了，选择就好了，路数越多，深度越短，io的操作越少。

mySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。如果技术高超，还可以使用MySQL+API自己做一个引擎。下面介绍几种数据库引擎：
    ISAM：ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不 支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
    MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。
    HEAP：HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
    InnoDB：InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者 或者两者，那你就要被迫使用后两个引擎中的一个了。
    如果感觉自己的确技术高超，你还能够使用MySQL+API来创建自己的数据库引擎。这个API为你提供了操作字段、记录、表格、数据库、连接、安全帐号的功能，以及建立诸如MySQL这样DBMS所需要的所有其他无数功能。深入讲解API已经超出了本文的范围，但是你需要了解MySQL+API的存在及其可交换引擎背后的技术，这一点是很重要的。估计这个插件式数据库引擎的模型甚至能够被用来为MySQL创建本地的XML提供器（XML provider）。（任何读到本文的MySQL+API开发人员可以把这一点当作是个要求。）