今天来系统地学习一下MySQL，主要有以下知识点：

  • InnoDB引擎
  • 索引
  • 锁

MyISAM和InnoDB区别

MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。

两者的对比：

  1. 是否支持行级锁: MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
  2. 是否支持事务和崩溃后的安全恢复： MyISAM强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
  3. 是否支持外键：MyISAM不支持，而InnoDB支持。
  4. 是否支持MVCC：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。

索引

基础知识

Mysql的基本存储结构是页(记录都存在页里边)

各个数据页可以组成一个双向链表
而每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。
所以说，如果我们写 select * from use where username='wugui' 这样没有进行任何优化的sql语句，默认会这样做：

  1. 定位到记录所在的页
  2. 需要遍历双向链表，找到所在的页
  3. 从所在的页内中查找相应的记录

由于不是根据主键查询，只能遍历所在页的单链表了。
很明显，在数据量很大的情况下这样查找会很慢！

原理

索引底层结构就是B+树，B+树是为磁盘或其它直接存储辅助设备设计的一种平衡二叉树。

那么索引是如何加快检索速度的呢？

总结

  • 非叶子结点不保存数据，只用来索引，所有数据都保存在叶子节点。
  • 所有的数据都在叶子节点，各叶子结点形成了一个有序的双向链表。为什么要有序呢？其实是为了范围查询。比如说 select * from Table where id > 1 and id < 100; 当找到1后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。
  • 一般情况下，数据库的B+树的高度一般在2~4层，这就是说找到某一键值的行记录最多需要2到4次磁盘IO，相当于0.02到0.04s。

哈希索引

除了B+树之外，还有一种常见的是哈希索引。

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

哈希索引有好几个局限(根据他本质的原理可得)：

  • 哈希索引也没办法利用索引完成排序
  • 不支持最左匹配原则
  • 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
  • 不支持范围查询

聚集和非聚集索引

简单概括

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

区别

  • 聚集索引在叶子节点存储的是表中的数据
  • 非聚集索引在叶子节点存储的是主键和索引列
  • 使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据

非聚集索引在建立的时候也未必是单列的，可以多个列来创建索引。

覆盖索引

在创建多列索引中也涉及到了一种特殊的索引-->覆盖索引

我们前面知道了，如果不是聚集索引，叶子节点存储的是主键+列值
最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢
覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

比如说：
现在我创建了索引 (username,age)，在查询数据的时候： select username,age from user where username = 'wugui' and age = 20。

很明显地知道，我们上边的查询是走索引的，并且，要查询出的列在叶子节点都存在！所以，就不用回表了~

所以，能使用覆盖索引就尽量使用吧~

索引最左匹配原则

参考

MySQL教程
数据库两个神器索引和锁