MySQL浅谈

mysql> show engines;
复制代码

其他引擎详情请移步： dev.mysql.com/doc/refman/…

• InnoDB

InnoDB: The default storage engine in MySQL 5.7. InnoDB is a transaction-safe (ACID compliant) storage engine for MySQL that has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB row-level locking (without escalation to coarser granularity locks) and Oracle-style consistent nonlocking reads increase multi-user concurrency and performance. InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints. For more information about InnoDB, see Chapter 14, The InnoDB Storage Engine .

• 翻译：

innodb: mysql 5.7 中的默认存储引擎。innodb 是 mysql 的事务安全 (符合 acid) 存储引擎, 具有提交、回滚和崩溃恢复功能, 可保护用户数据。innodb 行级锁定 (不升级到更粗粒度锁) 和 oracle 类型一致使用非锁定读取，可提高多用户并发性和性能。innodb 将用户数据存储在聚集索引中, 以减少基于主键的常见查询的 I/O。为了保持数据完整性, innodb 还支持外键。有关 innodb 的详细信息, 请参阅第14章, inodb 存储引擎。

InnoDB存储数据结构 - B+树

• 为什么使用B+Tree?
  索引的常见模型常见的有，哈希表、有序数组、搜索树。
  哈希表，优点就是查询快，缺点是范围查询效率很低（因为无序）。适用于等值查询。
  有序数组，优点是等值查询，范围查询都非常快，缺点也很明显，就是插入效率太低，因为如果从总监插入，要移动后面所有的元素。 树结构，优点有序，并且多叉树可以减少磁盘I/O次数。
• B-Tree和B+Tree优缺点。
  B-Tree的结构和B+Tree结构类似，只是非叶子节点也会存储数据，而B+Tree只在叶子节点存储数据，虽然B-Tree可能在遍历到第二层时就可以得到数据返回，但是由于非叶子节点也会存储数据，导致没个数据页存储的索引更少，导致树的高度会很高，如果需要遍历的数据在叶子节点，则非常费时，所以查询性能不如B+Tree稳定。

InnoDB索引模型

CREATE TABLE `r` (
  `id` int NOT NULL primary key auto_increment,
  `k` int not null,
  `name` varchar(16),
   index(k)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制代码

在表中插入(1,10,"张三"),(2,20,"李四"),(3,30,"王五")。则索引如下图

主键索引的叶子节点存的是整行数据，非主键索引的叶子节点存的主键的值。

在InnoDB里，主键索引被称为聚簇索引或聚集索引（clustered index）,非主键索引被称为二级索引或辅助索引(secondary index)。 在InnoDB中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表成为索引组织表。每一个索引在InnoDB里对应一棵B+树。 聚簇索引生成规则：

1. When you define a PRIMARY KEY on your table, InnoDB uses it as the clustered index. Define a primary key for each table that you create. If there is no logical unique and non-null column or set of columns, add a new auto-increment column, whose values are filled in automatically.

定义主键用主键作为聚簇索引。

2. If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

没定义主键使用第一个唯一索引作为聚簇索引。

3. If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

基于主键索引和普通索引查询有什么区别？

1. 如果 sql 是 select * from r where id = 1; 即通过主键方式查询，只需要搜索主键这棵B+树。
2. 如果sql是 select * from r where k = 10; 即通过普通索引查询，需要先搜索普通索引k这棵B+树，拿到主键id=1,在用id=1再去搜索主键索引的B+树。这个过程叫做 回表 。

在分析一个sql语句：select * from r where k between 8 and 22;

1. 在k索引树上找到k=10的记录，取得id=1;
2. 在id索引树上找到id=1的对应的行记录data(回表);
3. 在k索引树上找到k=20的记录，取得id=2;
4. 在id索引树上找到id=2的对应的行记录data(回表);
5. 在k索引树取下一个值k=30,不满足，循环结束。

这个例子由于要查询的结果只有主键索引上面才有，所以不得不回表。那么如何避免回表？

覆盖索引

如果sql语句是：select id from r where k between 8 and 22，由于这时只需要查询id值，而id值已经在k索引树上了，所以不需要回表查询，索引k已经覆盖了我们的查询需求，称之为覆盖索引。

由于覆盖索引可以减少数的搜索次数，显著提高查询性能，所以使用覆盖索引是一个常用的优化手段。

场景：假设有一个市民表：

CREATE TABLE `citizen` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB
复制代码

是否有必要创建身份证号和姓名的联合索引？

根据业务来看，如果有根据身份证号查询姓名的高频需求，可以考虑创建身份证号和姓名的联合索引，避免回表提高查询的效率。

最左前缀原则

select * from citizen where name = "张三" ;
复制代码

这个肯定是可以用name索引的，如果要查询姓张的人，语句是

select * from citizen where name like '张%';
复制代码

这时也可以用上name的索引，查找到第一个以张开头的人，向后遍历直到不满足条件为止。

而如果要检索姓张，年龄10岁的男孩。

select * from tuser where name like '%张' and age=10 and ismale=1;
复制代码

这个在MySQL5.6以前是要根据查询到姓张的人开始一个一个回表去查询age是否满足10的，而5.6引入了 索引下推优化 (index condition pushdown)，可以在遍历中，对索引中包含的字段先判断，过滤掉不满足的记录，减少回表次数。