MySQL知多少

问题列表

MyIsam和Innodb的区别

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。 InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。 MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用 原因如下：

MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。 MyIsam一个是索引文件，另外一个是数据文件，其中索引文件中的索引指向数据文件中的表数据。查到索引就可以直接读取数据。而Innodb需要回表查询。因为聚集索引，索引放在叶子结点。

从逻辑角度分，MySQL有哪几种类型的索引

• 主键索引
• 唯一索引
• 普通索引(单列索引)
• 复合索引(多列索引)
• 全文索引
• 空间索引

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

# MySQL的主要索引类型
create table t_index (
    id int not null auto_increment,
    mobile char(11) not null default '',
    description varchar(256) not null default '',
    address varchar(256) not null default '',
    
    province varchar(32) not null default '',
    city varchar(32) not null default '',

    primary key (`id`) comment '主键索引',
    unique key(`mobile`) comment '唯一索引',
    fulltext(`description`) comment '全文索引',
    key(`address`) comment '单列索引',
    key(`province`, `city`) comment '复合索引'
) engine=innodb default charset utf8mb4;

主键和唯一索引的区别

MySQL查询时最终会选择几个索引？为什么？

一条SQL执行的时候只能使用一个索引。但是在发生索引合并的情况下，可以使用多个索引。

创建索引的时候要考虑那些东西？（你如何设计索引）

• 查询经常要用到的字段。
• 一般在where跟order by中涉及到的列建立索引。
• 需要连表(join操作)的字段。
• 需要去重的字段，唯一索引

注意

• 字段的区分度尽可能要高。
• 数据更新频繁的字段，不适合建索引。

什么情况下会发生明明创建了索引，但是执行的时候并没有通过索引呢?

• 存在数据类型隐式转化
• is null
• 使用了函数
• MySQL基于成本计算选择索引，如果走索引的成本跟全表扫描差不多就走全表扫描了。

MySQL的索引实现有哪几种数据结构？

MySQL中Innodb和MyIsam引擎支持B+tree索引，不支持 Hash 索引。但是Innodb引擎会监控对表的索引查找情况，如果符合条件，会自动建立哈希索引。用户无法显式的创建哈希索引。这个特性叫做自适应哈希索引。

MySQL中的Memory引擎可以显式的创建哈希索引。

1
2
3
4
5
6
7
8

use test;
create table t_hash_index (
    name varchar(32) not null default '',
    key using hash(`name`)
) engine=memory default charset utf8mb4;

use test;
show index from t_hash_index;

画出B+树的结构示意图

扩展阅读

查找算法串讲

• 为什么使用了索引就可以加快查找速度？如果不使用索引，就必须全表扫描，那么时间复杂度是O(n)。如果使用了索引，由于B+树是有序的，时间复杂度是O(lgN)。因此可以加快查找速度。
• 为什么MySQL不使用B树存储索引？B树跟B+树的一个区别是，B树在节点保存了主键以外的数据，而B+树只在叶子节点保存数据，其他节点只保存指针跟主键。因此使用B+树，每个磁盘块能保存的节点信息更多，降低索引树的高度，从而减少IO操作。另一方面，还可以减少内存的使用。原因是，系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一磁盘块中的数据会被一次性读取出来，而不是按需读取。查找过程中，即使被访问的行记录不是你要的，也会被读取到内存中。
• 为什么MySQL不使用二叉树存储索引？一方面是二叉树的高度比较高，需要的IO操作次数比较多。另一方面是二叉树不平衡，如果退化成链表的话，性能影响严重。
• 为什么MySQL不使用红黑树存储索引？
• 顺序查找的时间复杂度是多少？平均查找长度是多少？时间复杂度是O(n)，平均查找长度是(n+1)/2。

索引结构延伸问题串讲

• varchar类型的长度设置的太大会有什么影响？
• 为什么在MySQL中，推荐自增id作为主键？为什么使用uuid不好呢？
• 字符串索引跟数字类型的索引有什么区别？字符串索引在索引树上两个节点比较会比较慢，数字类型的索引会快一些?

B+树索引跟Hash索引的区别（比较）

聚集索引和非聚集索引的区别

事务的特点

一组操作要么全都执行，要么全都不执行。ACID：原子性、一致性、隔离性、持久性。

事务的隔离等级

事务相当于SQL执行过程的命名空间，沙盒机制。相关实验

隔离等级越高，数据越安全，数据一致性得到更高的保障。但是同时并发能力越差。

MySQL 通过锁机制来保证事务的隔离性。锁机制使得在对数据库进行并发访问时，可以保障数据的完整性和一致性。

事务的是如何实现的

MySQL 使用 redo log 来保证事务的持久性，通过锁来保证事务的隔离性。

行锁和表锁的区别

表锁开销小，锁表快，但高并发下性能低。 行锁开销大，锁表慢，但高并发下相比之下性能更高。

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效（或者查询的没有走索引），否则都会从行锁升级为表锁

如何加锁

InnoDB 实现了两种类型的行级锁：

共享锁（也称为 S 锁）：允许事务读取一行数据。 可以使用 SQL 语句 select * from tableName where … lock in share mode; 手动加 S 锁。

独占锁（也称为 X 锁）：允许事务删除或更新一行数据。 可以使用 SQL 语句 select * from tableName where … for update; 手动加 X 锁。

什么是死锁？MySQL是如何处理死锁的？

死锁 死锁是指两个或两个以上的进程在执行过程中，由于竞争资源或者由于互相等待而造成的一种阻塞的现象。

1
2

# 查看死锁
show engine innodb status\G ;

Innodb实现

InnoDB 引擎采取的是 wait-for graph 等待图的方法来自动检测死锁，如果发现死锁会自动回滚一个事务。

如何优化死锁问题

• 配置innodb_lock_wait_timeout 参数，减小锁定时间;
• 减小锁定范围。合理设计索引，让 InnoDB 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他 Query 的执行。
• 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录。
• 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
• 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少 MySQL 因为实现事务隔离级别所带来的附加成本。

乐观锁和悲观锁分别是如何实现的？

悲观锁，指的是对数据被外界修改持保守态度。在数据处理的整个过程中，将数据处于锁定状态。一般是通过数据库提供的锁机制来实现。悲观锁能够保证数据的独占性，但是加锁和独占数据会造成性能问题。

乐观锁，通过数据的版本记录来实现。比如，可以在代码中为每一行添加一个作为版本号的字段。MySQL中的事务隔离性正是通过MVCC来实现的。

MySQL中的多版本并发控制实现(MVCC)

在InnoDB中，会在每行数据后添加两个额外的隐藏的值来实现MVCC，这两个值一个记录这行数据何时被创建，另外一个记录这行数据何时过期（或者被删除）。 在实际操作中，存储的并不是时间，而是事务的版本号，每开启一个新事务，事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下：

SELECT时，读取创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。 INSERT时，保存当前事务版本号为行的创建版本号 DELETE时，保存当前事务版本号为行的删除版本号 UPDATE时，插入一条新纪录，保存当前事务版本号为行创建版本号，同时保存当前事务版本号到原来删除的行

MVCC实现机制扩展阅读

描述贵公司所使用的的数据库架构

主从复制延迟有哪些原因？

从主从复制的过程来看，主库提供bin log，从库消费bin log。如果生产的日志远大于消费日志的速度，就会出现主从复制延迟。

• 主库在短时间内存在大量的写入操作
• 从库的配置低于主库
• 从库的负载远高于从库
• 主库和从库之间存在网络故障或者网络抖动
• 从库事务锁定时间过长

如何解决主从复制延迟？

从产生的原因逐个解决：

业务角度

• 操作增加消息队列，削峰填谷，减少数据波动
• 将不重要的操作放在夜间执行
• 先写入Redis，然后再将Redis的数据写入数据库

MySQL优化

• 增加从服务器，分散读操作的压力
• 从库和主库保持一样的配置
• 从库降低安全性，不必每秒都写入磁盘。innodb_flush_log_at_trx_commit 参数指定了 InnoDB 在事务提交后的日志写入频率。innodb_flush_log_at_trx_commit 设为0
• 从库不写bin log，减轻负载
• 基于语句的复制，切换到基于行的复制，

一条SQL的生命周期，SQL提交到执行的全过程

MySQL跟PG相比，有哪些优势？劣势？

那些年，优化过的慢SQL