开发所应该知道的MySQL


MySQL 中 varchar 与 char 的区别?varchar(50) 中的 50 代表的涵义?

金额(金钱)相关的数据,选择什么数据类型?

一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 MySQL 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15?

生产数据,不建议进行物理删除记录。

如何选择合适的存储引擎?

  1. 是否需要支持事务。
  2. 对索引和缓存的支持。
  3. 是否需要使用热备。
  4. 崩溃恢复,能否接受崩溃。
  5. 存储的限制。
  6. 是否需要外键支持。

目前开发已经不考虑外键,主要原因是性能

目前,MySQL 默认的存储引擎是 InnoDB ,并且也是最主流的选择。主要原因如下:

在 MySQL5.1 以及之前的版本,默认的存储引擎是 MyISAM ,但是目前已经不再更新,且它有几个比较关键的缺点:

InnoDBMyISAM 
事务支持不支持
存储限制64TB
锁粒度行锁表锁
崩溃后的恢复支持不支持
外键支持不支持
全文检索5.7 版本后支持支持

为什么 SELECT COUNT() FROM table 在 InnoDB 比 MyISAM 慢?

对于 SELECT COUNT(*) FROM table 语句,在没有 WHERE 条件的情况下,InnoDB 比 MyISAM 可能会慢很多,尤其在大表的情况下。因为,InnoDB 是去实时统计结果,会全表扫描;而 MyISAM 内部维持了一个计数器,预存了结果,所以直接返回即可。

什么是索引?

索引,类似于书籍的目录,想找到一本书的某个特定的主题,需要先找到书的目录,定位对应的页码。

MySQL 中存储引擎使用类似的方式进行查询,先去索引中查找对应的值,然后根据匹配的索引找到对应的数据行。

索引有什么好处?

  1. 提高数据的检索速度,降低数据库IO成本:使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
  2. 降低数据排序的成本,降低CPU消耗:索引之所以查的快,是因为先将数据排好序,若该字段正好需要排序,则正好降低了排序的成本。

索引有什么坏处?

  1. 占用存储空间:索引实际上也是一张表,记录了主键与索引字段,一般以索引文件的形式存储在磁盘上。
  2. 降低更新表的速度:表的数据发生了变化,对应的索引也需要一起变更,从而减低的更新速度。否则索引指向的物理数据可能不对,这也是索引失效的原因之一。

索引的使用场景?

实际场景下,MySQL 分区表很少使用,对于特大型的表,更常用的是“分库分表”,目前解决方案有 Sharding Sphere、MyCAT 等等。

索引的类型?

索引,都是实现在存储引擎层的。主要有六种类型:

常用的全文索引引擎的解决方案有 Elasticsearch、Solr 等等。最为常用的是 Elasticsearch 。

MySQL 索引的“创建”原则?

因为复合索引的基数会更大。

MySQL 索引的“使用”注意事项?

注意,column IS NULL 也是不可以使用索引的。

以下三条 SQL 如何建索引,只建一条怎么建?

WHERE a = 1 AND b = 1
WHERE b = 1
WHERE b = 1 ORDER BY time DESC

想知道一个查询用到了哪个索引,如何查看?

EXPLAIN 显示了 MYSQL 如何使用索引来处理 SELECT 语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句。

使用方法,在 SELECT 语句前加上 EXPLAIN 就可以了。

MySQL 有哪些索引方法?

在 MySQL 中,我们可以看到两种索引方式:

实际场景下,我们基本仅仅使用 B-Tree 索引。

什么是 B-Tree 索引?

B-Tree 是为磁盘等外存储设备设计的一种平衡查找树。因此在讲 B-Tree 之前先了解下磁盘的相关知识。

B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree,首先定义一条记录为一个二元组 [key, data] ,key 为记录的键值,对应表中的主键值,data 为一行记录中除主键外的数据。对于不同的记录,key值互不相同。

一棵 m 阶的 B-Tree 有如下特性:

  1. 每个节点最多有 m 个孩子。
    • 除了根节点和叶子节点外,其它每个节点至少有 Ceil(m/2) 个孩子。
    • 若根节点不是叶子节点,则至少有 2 个孩子。
  2. 所有叶子节点都在同一层,且不包含其它关键字信息。
  3. 每个非叶子节点包含 n 个关键字信息(P0,P1,…Pn, k1,…kn)
    • 关键字的个数 n 满足:ceil(m/2)-1 <= n <= m-1
    • ki(i=1,…n) 为关键字,且关键字升序排序。
    • Pi(i=0,…n) 为指向子树根节点的指针。P(i-1) 指向的子树的所有节点关键字均小于 ki ,但都大于 k(i-1) 。

B-Tree 中的每个节点根据实际情况可以包含大量的关键字信息和分支,如下图所示为一个 3 阶的 B-Tree:

模拟查找 key 为 29 的过程:

分析上面过程,发现需要 3 次磁盘 I/O 操作,和 3 次内存查找操作。由于内存中的 key 是一个有序表结构,可以利用二分法查找提高效率。而 3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素。B-Tree 相对于 AVLTree 缩减了节点个数,使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率。

什么是 B+Tree 索引?

B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用 B+Tree 实现其索引结构。

下面这一段,非常关键。

从上一节中的 B-Tree 结构图中可以看到,每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,如果 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小,当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率。在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度。

B+Tree 相对于 B-Tree 有几点不同:

将上一节中的 B-Tree 优化,由于 B+Tree 的非叶子节点只存储键值信息,假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示:

可能上面例子中只有 22 条数据记录,看不出 B+Tree 的优点,下面做一个推算:

B+Tree 有哪些索引类型?

在 B+Tree 中,根据叶子节点的内容,索引类型分为主键索引非主键索引

辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,需要进过两步:

另外,InnoDB 通过主键聚簇数据,如果没有定义主键,会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个主键作为聚簇索引。

聚簇索引的注意点有哪些?

聚簇索引表最大限度地提高了 I/O 密集型应用的性能,但它也有以下几个限制:

什么是索引的最左匹配特性?

当 B+Tree 的数据项是复合的数据结构,比如索引 (name, age, sex) 的时候,B+Tree 是按照从左到右的顺序来建立搜索树的。

这个是非常重要的性质,即索引的最左匹配特性。

MyISAM 索引实现?

MyISAM 索引的实现,和 InnoDB 索引的实现是一样使用 B+Tree ,差别在于 MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

1)主键索引:

MyISAM引 擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM主键索引的原理图:

2)辅助索引:

在 MyISAM 中,主索引和辅助索引在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示:

MyISAM 的索引方式也叫做“非聚集”的,之所以这么称呼是为了与InnoDB 的聚集索引区分。

MyISAM 索引与 InnoDB 索引的区别?

MySQL 的四种事务隔离级别

事务就是对一系列的数据库操作(比如插入多条数据)进行统一的提交或回滚操作,如果插入成功,那么一起成功,如果中间有一条出现异常,那么回滚之前的所有操作。

这样可以防止出现脏数据,防止数据库数据出现问题。

事务的特性指的是?

  1. 原子性 Atomicity :一个事务(transaction)中的所有操作,或者全部完成,或者全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被恢复(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样。即,事务不可分割、不可约简。
  2. 一致性 Consistency :在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设约束触发器级联回滚等。
  3. 隔离性 Isolation :数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable)。
  4. 持久性 Durability :事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

事务的并发问题?

实际场景下,事务并不是串行的,所以会带来如下三个问题:

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表。

MySQL 事务隔离级别会产生的并发问题?

事务定义了四种事务隔离级别,不同数据库在实现时,产生的并发问题是不同的。

不同的隔离级别有不同的现象,并有不同的锁定/并发机制,隔离级别越高,数据库的并发性就越差。

MySQL InnoDB 采用 MVCC 来支持高并发,实现结果如下表所示:

事务隔离级别脏读不可重复读幻读
读未提交(read-uncommitted)
读已提交(read-committed)
可重复读(repeatable-read)是(x)
串行化(serializable)

其实 RR 也是可以避免幻读的,通过对 select 操作手动加 行X锁(SELECT … FOR UPDATE 这也正是 SERIALIZABLE 隔离级别下会隐式为你做的事情),同时还需要知道,即便当前记录不存在,比如 id = 1 是不存在的,当前事务也会获得一把记录锁(因为InnoDB的行锁锁定的是索引,故记录实体存在与否没关系,存在就加 行X锁,不存在就加 next-key lock间隙X锁),其他事务则无法插入此索引的记录,故杜绝了幻读。

MySQL 的锁机制

MySQL 的共享锁和排他锁,就是读锁和写锁。

锁的粒度?

什么是悲观锁?什么是乐观锁?

1)悲观锁

它指的是对数据被外界(包括本系统当前的其他事务,以及来自外部系统的事务处理)修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。悲观锁的实现,往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性,否则,即使在本系统中实现了加锁机制,也无法保证外部系统不会修改数据)。

在悲观锁的情况下,为了保证事务的隔离性,就需要一致性锁定读。读取数据时加锁,其它事务无法修改这些数据。修改删除数据时也要加锁,其它事务无法读取这些数据。

2)乐观锁

相对悲观锁而言,乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现,以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销,特别是对长事务而言,这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁,大多是基于数据版本( Version )记录机制实现。何谓数据版本?即为数据增加一个版本标识,在基于数据库表的版本解决方案中,一般是通过为数据库表增加一个 “version” 字段来实现。读取出数据时,将此版本号一同读出,之后更新时,对此版本号加一。此时,将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对,如果提交的数据版本号大于数据库表当前版本号,则予以更新,否则认为是过期数据。

乐观锁,实际就是通过版本号,从而实现 CAS 原子性更新。

什么是死锁?

多数情况下,可以认为如果一个资源被锁定,它总会在以后某个时间被释放。而死锁发生在当多个进程访问同一数据库时,其中每个进程拥有的锁都是其他进程所需的,由此造成每个进程都无法继续下去。简单的说,进程 A 等待进程 B 释放他的资源,B 又等待 A 释放他的资源,这样就互相等待就形成死锁。

虽然进程在运行过程中,可能发生死锁,但死锁的发生也必须具备一定的条件,死锁的发生必须具备以下四个必要条件:

下列方法有助于最大限度地降低死锁:

MySQL 中 InnoDB 引擎的行锁是通过加在什么上完成(或称实现)的?为什么是这样子的??

InnoDB 是基于索引来完成行锁。例如:SELECT * FROM tab_with_index WHERE id = 1 FOR UPDATE

MySQL 查询执行顺序

MySQL 查询执行的顺序是:

(1)     SELECT
(2)     DISTINCT <select_list>
(3)     FROM <left_table>
(4)     <join_type> JOIN <right_table>
(5)     ON <join_condition>
(6)     WHERE <where_condition>
(7)     GROUP BY <group_by_list>
(8)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

什么是 MVCC ?

多版本并发控制(MVCC),是一种用来解决读-写冲突的无锁并发控制,也就是为事务分配单向增长的时间戳,为每个修改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。 这样在读操作不用阻塞写操作,写操作不用阻塞读操作的同时,避免了脏读和不可重复读。