MySql笔记（一）基础及索引原理

磁盘的存储原理

• 以机械硬盘为例，数据是存储在盘片上的，当MySQL应用程序调用内核提供的IO函数进行IO操作时内核会找到对应的驱动程序驱动磁头，由于不同的数据也可能分布不同磁道的不同扇区上，因此，需要驱动磁头进行寻道，找到对应的位置扫描数据，把对应的数据加载到内存中，这也是一次IO操作的大概流程，而进行寻道操作便是这次操作最为耗时的部分。
• MySQL的innodb中有一个参数叫做innodb_page_size,他的值为16384，表示它一次性可以读取4×4k数据，这就是innodb中的页，也是它存储数据的最小单位，在B＋树中每一个节点就存储为一页，每次IO操作也会读取一页的数据。
• 因此，MySQL进行查询操作时候的性能，主要就是看它进行了多少次磁盘IO，如果数据量大或者在磁盘上过于分散而占用了多个磁道，那么就需要多次IO操作，如大数据量的全表扫描。那么，在大数据的表中寻找特定的一些数据，便可以通过一些（索引）数据结构指定这些数据的查询地址，直接访问对应的地址，这样就能避免全表扫描。

索引中的数据结构

• 普通的二叉树
  我们可以使用树来建立索引，最简单的二叉树，由于平衡性完全不能保障，极端情况下可能会使其成为一条链表，所以这个是不行的。
  因为二叉树的深度每深一层，意味着需要多进行一次IO操作，所以要深度尽可能的小，那么就需要需要保证一定的平衡性，如avl树，红黑树等，都能保证一定的平衡性，那么问题又来了，虽然平衡性得到了保障，但是每一次IO操作都只读取节点上的一个键，而MySQL一次IO操作可以读取4×4k的数据，只读取一个键显然太浪费了，那么如果一个节点中包含多个键，这样的话，不仅可以读满一个innodb_page_size，而且树的深度也可以大大降低，所以就引出了B树。

• B树
  B树是一个n叉树，数据库中的B树，每个节点中都包含多个指针、数据和键值，且一个键只出现在一个节点中，他的叶节点的指针为空，且都有相同的深度，节点中的键值从左到右依次递增，但是由于数据直接存储在节点中，如果数据占用比较大，每次IO操作读取的节点的键的数量就会更受限制，因此，索引建立时，往往需要更深的深度，这就是B树存在的问题。那么，如果说只有叶子节点中才存放数据，则前面的节点中便能存放更多的键，树的深度会低，而这就是B+树。
  如图是一般数据结构中的B树（图中每个节点没有显示data）：
  

• B+树
  相对于B树，在innodb中的B+树的非叶子节点不存储数据，只存储键值，这样便能够保证树的最小深度，并且所有的叶子节点都有一个双向链指针，这样可以在比如使用索引区间查询时，如果查询的区间分布在几个磁盘块上，可以直接通过指针访问到后面一个磁盘块，而不需要再次通过索引访问到这个磁盘块，这样可以提高性能。
  如图是一般数据结构中的B+树（图中叶子节点没有显示data和双向指针）：
  

• Hash
  目前MySQL上的innodb支持两种索引结构，一种是B+树，一种是Hash，那么，这两种数据结构的索引有什么优劣呢，而通过哈希表来建立索引，由于哈希表是直接通过键的hashcode来确定存储位置的，所以，哈希表的查询效率是O(1)，效率比B+树要高，但是如果存在很多相同的键值，便会产生大量的哈希冲突，导致查询效率降低，不过这不是主要的问题，而是当需要通过索引进行区间查找时，由于B+树都满足了键值都是从左到右依次增大的规律，因此，在区间查找时可以利用这个规律通过比较键大小来进行查找，而哈希表本身只使用键的hashcode来查找数据，和键值的大小没有任何关系，因此，通过访问hashcode的映射地址遍历访问一遍数据还不如直接进行全表扫表。使用模糊查询类似。

MyIsam和Innodb引擎

• MyIsam存储引擎
  MyIsam存储引擎支持全文索引，不支持事务、行级锁和外键。其使用的是非聚集索引，索引在叶子节点上存储的是数据的地址，并没有直接存放数据，因此可以在文件中看到，MyIsam有.MYI文件用来存储索引，有.MYD用来存储数据，因此在通过索引访问一条数据的时候，先通过.MYI文件访问到该数在.MYD文件中的位置，再从.MYD文件中获取数据。
• Innodb存储引擎
  Innodb存储引擎不支持全文索引，支持事务、行级锁和外键。其使用的是聚集索引，主键索引在叶子节点上存储的是该键对应的行数据，非主键索引在叶子节点上存储的是对应的主键值。因此在使用非主键索引访问该行的数据时，首先要通过非主键索引查询得到主键值，再通过主键索引查询得到数据。

Innodb中的各种索引

• 主键索引
  在Innodb中，一个表一定要有一个聚集索引，因为数据行在存储的时候需要按照一定的顺序从小到大排列，而当没有指定主键时，会自动使用一个没有重复的列作为排列的依据进行聚集，如果没有符合要求的列，innodb会自动添加一列来作为聚集索引，或者当指定了主键后，会将主键作为聚集索引，再物理磁盘中进行重新排序。所以，在修改主键或者添加新的一行数据的时候，有可能需要对索引结构进行重新调整，因此常被修改的字段不推荐被定义为主键。

• 非主键索引
  在Innodb中，除了主键索引以外的所有索引都是非聚集索引，他们的索引顺序与数据行在磁盘中的存储顺序没有对应关系，因此，在修改一行的非主键索引时，数据行的数据不需要改变。

• 联合索引
  通过两个或以上字段建立的索引就是联合索引，联合索引的原理：
  联合索引的大小比较规则是，先比较第一个字段的ASCII码值，如果通过第一个字段已经比较出大小了，便不会再比较后面的字段，如果第一个字段ASCII码值相同，再比较第二个字段的ASCII码值，依次类推，只有前面的字段ASCII码值相同，才会比较后面的字段。所以说，一个联合索引，后面字段的比较一定是建立在前面字段比较的基础上来进行的。
  因此，这里有一个最左前缀原则：在联合索引中，不能跨越左边的索引去比较右边的索引。
  建立name、age、grade三个字段的联合索引，主键为生日，如图所示（理解意思就好）：
  

• 覆盖索引
  如果查询查询结果是主键或者是联合索引中的字段，那么就不用回表操作，可以直接返回结果，因为该结果可以直接通过查询条件中的索引或者该联合索引获取到，这样可以减少IO操作的次数。

一些问题

• 为什么推荐使用自增主键
  1.在innodb中，int类型的索引可以直接比较大小，而其他类型如string类型的索引，在比较的时候需要对每个字符转换为ASCII码进行比较，这里就在比较之前都存在一些运算，效率更低。
  2.由于主键是一个聚集索引，磁盘中的数据行是依据他的索引顺序进行排列的，如果他不是一个自增索引，可能新插入的数据的主键值比目前最大的主键值要小，那么就要对索引和数据行存储位置进行重排序，这样会消耗大量的性能，而使用自增主键，新插入的数据直接放到最后面即可，就不需要进行重排序了。