MySQL进阶之路「本文已参与好文召集令活动，点击查看：后端、大前端双赛道投稿，2万元奖池等你挑战！」前言之前一家公

「本文已参与好文召集令活动，点击查看：后端、大前端双赛道投稿，2万元奖池等你挑战！」

前言

之前一家公司面试，问到了mysql的优化问题，面试官问的很细，当时的我有些问题回答的并不是很完善，后面利用休息的时间，翻了些资料，总结了些mysql优化的方案和感想。

面试经历

面试官：你之前项目有用过mysql吗？谈一下对mysql的了解。

我：（还好哥早已经背好了），Mysql事务有四大特性主要包含：原子性（Atomicity）、一致性（Consistent）、隔离性（Isalotion）、持久性(Durable)。

1、原子性(Atomicity) 　　事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。

2、一致性(Consistency) 　　事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

3、隔离性(Isolation) 　一个事务的执行不能被其他事务干扰。

4、持续性/永久性(Durability) 一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

面试官：之前你还提到事务的隔离级别，你能说一说吗？

我：（还好这个也背了一点）·未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据 ·提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读) ·可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读 ·串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞。

面试官：能说下myisam 和 innodb的区别吗？

我：（心里一惊，啥玩意？问这么深，记忆有点模糊啊，厚着脸皮回答）innodb是基于索引建立的，myisam不支持外键，并且索引和数据是分开存储的。

面试官：说下mysql的索引有哪些吧，聚簇和非聚簇索引又是什么？

我：（完了，破功了）这个了解的不是很多。。。

复盘

后来面试官又问了几个问题，感觉自己回答的也不是很满意，因为这家公司业务的数据量比较多，对数据库优化这一块要去比较高，所有遗憾没有被看中。后来自己有时间回顾的时候，去查了一些资料。

mysql进阶一：索引——聚簇和非聚簇索

索引按照数据结构来说主要包含B+树和Hash索引。

假设我们有张表，结构如下：

create table user(
    id int(10) not null,
  age int(10) not null,
  primary key(id),
  key(age)
);

B+树是左小右大的顺序存储结构，节点只包含id索引列，而叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引，一张表只能有一个聚簇索引。假设没有定义主键，InnoDB会选择一个唯一的非空索引代替，如果没有的话则会隐式定义一个主键作为聚簇索引。

图片.png

这是主键聚簇索引存储的结构，那么非聚簇索引的结构是什么样子呢？非聚簇索引(二级索引)保存的是主键id值，这一点和myisam保存的是数据地址是不同的。

mysql进阶二：覆盖索引和回表

覆盖索引指的是在一次查询中，如果一个索引包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询。而要确定一个查询是否是覆盖索引，我们只需要explain sql语句看Extra的结果是否是“Using index”即可。

explain select * from user where age=1; //查询的name无法从索引数据获取
explain select id,age from user where age=1; //可以直接从索引获取

mysql进阶三：幻读

还是拿上面的user表举例子，假设我们插入两条数据，他们实际上应该长这样。

图片.png

这时候假设小明去执行查询，此时current_version=3

select * from user where id<=3;

同时，小红在这时候开启事务去修改id=1的记录，current_version=4

update user set name='张三三' where id=1;

执行成功后的结果是这样的

图片.png

如果这时候还有小黑在删除id=2的数据，current_version=5，执行后结果是这样的。

图片.png

由于MVCC的原理是查找创建版本小于或等于当前事务版本，删除版本为空或者大于当前事务版本，小明的真实的查询应该是这样

select * from user where id<=3 and create_version<=3 and (delete_version>3 or delete_version is null);

所以小明最后查询到的id=1的名字还是'张三'，并且id=2的记录也能查询到。这样做是为了保证事务读取的数据是在事务开始前就已经存在的，要么是事务自己插入或者修改的。

mysql进阶四：设置隔离级别

我们可以通过以下语句查看当前数据库的隔离级别，通过下面语句可以看出我使用的 MySQL 的隔离级别是 REPEATABLE-READ，也就是可重复读，这也是 MySQL 的默认级别。

# 查看事务隔离级别 5.7.20 之后
show variables like 'transaction_isolation';
SELECT @@transaction_isolation

# 5.7.20 之后
SELECT @@tx_isolation
show variables like 'tx_isolation'

+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------

修改隔离级别的语句是：set [作用域] transaction isolation level [事务隔离级别]，SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}。

mysql> set global transaction isolation level read committed;

mysql进阶五：索引下推、查询优化

MySQL 5.6中，Index Condition Pushdown（索引下推）引入了索引下推优化，默认开启，使用SET optimizer_switch = ‘index_condition_pushdown=off’;可以将其关闭。官方文档中给的例子和解释如下： people表中（zipcode，lastname，firstname）构成一个索引

SELECT * FROM people WHERE zipcode=‘95054’ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;

如果没有使用索引下推技术，则MySQL会通过zipcode='95054’从存储引擎中查询对应的数据，返回到MySQL服务端，然后MySQL服务端基于lastname LIKE '%etrunia%'和address LIKE '%Main Street%'来判断数据是否符合条件。如果使用了索引下推技术，则MYSQL首先会返回符合zipcode='95054’的索引，然后根据lastname LIKE '%etrunia%'筛选出符合条件的索引后再返回到MySQL服务端，然后MySQL服务端基于address LIKE '%Main Street%'来判断数据是否符合条件，这样返回给MySQL服务端的索引数又会减少。有了索引下推优化，可以在有like条件查询的情况下，减少回表次数。

总结&感悟

现在回想起来，对于一些公司，对于底层的知识还是比较看中的，以前对于mysql的认识最多也就是索引的优化，其他的更深的知识比较匮乏。通过这次学习，让自己有了对mysql的另一种看法。虽然现在工作上暂时还用不上，这些知识，但是可以作为你进阶的一层的台阶。“活到老，学到老”。共勉！