MySQL 面试问题分析总结

背景介绍

最近面试了比较多，发现很多公司喜欢问如下问题：

• MySQL 的InnoDB与MyIsam的区别
• MySQL 如何与redis同步缓存
• MySQL 索引命中分析
• 分库分表
• MySQL锁的原理
• 为什么使用B+ 树，而不哈希索引
• B+树原理

准备工作

构建一个测试数据库，并且设计一个测试表，需要了解如下一些基本的工具

use db;
CREATE TABLE `user_with_innodb` (
                            `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户表id',
                            `username` varchar(50) NOT NULL COMMENT '用户名',
                            `password` varchar(50) NOT NULL COMMENT '用户密码，MD5加密',
                            `email` varchar(50) DEFAULT NULL,
                            `phone` varchar(20) DEFAULT NULL,
                            `role` int(4) NOT NULL COMMENT '角色0-管理员,1-普通用户',
                            `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
                            `update_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '最后一次更新时间',
                            PRIMARY KEY (`id`),
                            UNIQUE KEY `user_name_unique` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8;

我们需要使用到的一些随机生成数据的小工具，分别给出了demo如下：

select uuid()；
select SUBSTRING_INDEX(uuid(), "-", 1);
select concat("name_", SUBSTRING_INDEX(uuid(), "-", 1));
select concat(SUBSTRING_INDEX(uuid(), "-", 1), "@example.com");
select rand() * 10> 5 as role;
select CONVERT("13766880000", unsigned int) as phone_digit;

如何使用我们上面的小工具插入数据到表中呢？可以参考如下方式

INSERT INTO `user_with_innodb` (
                                username,
                                password,
                                email,
                                phone,
                                role)
                                select concat("name_", SUBSTRING_INDEX(uuid(), "-", 1)),
                                       uuid(),
                                       concat(SUBSTRING_INDEX(uuid(), "-", 1), "@example.com"),
                                       CONVERT("13766880000", unsigned int)  + LAST_INSERT_ID(),
                                       rand() * 10> 5;

使用工具函数，当然不是为了插入一条数据而已，因此我们接下来是使用循环不断的插入大量的数据到表中，这样才会方便我们接下来的测试

delimiter #
create procedure load_foo_test_data()
begin
declare v_max int unsigned default 1000;
declare v_counter int unsigned default 0;

start transaction;
while v_counter < v_max do
    select "test loop";
end while;
commit;
end #

call load_foo_test_data();

批量随机插入大量测试数据

delimiter #
create procedure load_foo_test_data()
begin
declare v_max int unsigned default 1000;
declare v_counter int unsigned default 0;

start transaction;
while v_counter < v_max do
      INSERT INTO `user_with_innodb` (
                                username,
                                password,
                                email,
                                phone,
                                role)
                                select concat("name_", SUBSTRING_INDEX(uuid(), "-", 1)),
                                       uuid(),
                                       concat(SUBSTRING_INDEX(uuid(), "-", 1), "@example.com"),
                                       CONVERT("13766880000", unsigned int)  + LAST_INSERT_ID(),
                                       rand() * 10> 5;
end while;
commit;
end #

call load_foo_test_data();

直接允许如上循环会一直阻塞，并且我们打开另外的一个窗口也无法查询到任何一条数据被插入，分析
原因可能是我们没有将v_counter 自增，导致无法跳出循环。通过 show full processlist 查看当前运行中的事物，然后通过 kill <ID>就可以杀死当前阻塞的事物了。

mysql> show full processlistG;
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 757796
  State: Waiting on empty queue
   Info: NULL

如上可以看到我们的id是第一行。
修复后的代码如下：

delimiter #
create procedure load_user_test_data()
begin
    declare v_max int unsigned default 1000;
    declare v_counter int unsigned default 0;

    start transaction;
    while v_counter < v_max do
            set v_counter = 1 + v_counter;
            INSERT INTO `user_with_innodb` (
                username,
                password,
                email,
                phone,
                role)
            select concat("name_", SUBSTRING_INDEX(uuid(), "-", 1)),
                   uuid(),
                   concat(SUBSTRING_INDEX(uuid(), "-", 1), "@example.com"),
                   CONVERT("13766880000", unsigned int)  + LAST_INSERT_ID(),
                   rand() * 10> 5;
        end while;
    commit;
end #

call load_user_test_data();

InnoDB 与MyIsam 对比

先看看网上比较优秀的答案来自 SO

InnoDB has row-level locking, MyISAM can only do full table-level locking.
InnoDB has better crash recovery.
MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

对应的中文意思大概是：

1. InnoDB支持行锁，而MyISAM不支持
2. InnoDB有非常好的宕机恢复数据能力
3. MyISAM一直都支持全文搜索，而InnoDB在5.6版本以后才支持
   4.InnoDB实现事物、外键、关系等

了解了这些区别以后，我们可以试着去一一验证了，在第一个terminal中打终端执行如下命令

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_with_innodb where username="name_1be71e3a" for update;
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| id       | username      | password                             | email                | phone       | role | create_time         | update_time         |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| 25471368 | name_1be71e3a | 1be71e44-3abe-11eb-9a64-9184e1dca9bb | 1be71e45@example.com | 13792351367 |    0 | 2020-12-10 16:02:55 | 2020-12-10 16:02:55 |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
1 row in set (0.00 sec)

打开第二个终端执行如下命令

 select * from user_with_innodb where username="name_1be71e3a" for update;

都卡在如图:

在第一个终端执行commit 才能看到第二个查询被释放。

mysql> commit;

现在，我们查询其他row的数据，看看会不会阻塞我们第二个terminal。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user_with_innodb where username="name_1be71c00" for update;
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| id       | username      | password                             | email                | phone       | role | create_time         | update_time         |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| 25471367 | name_1be71c00 | 1be71c0a-3abe-11eb-9a64-9184e1dca9bb | 1be71c0b@example.com | 13792351366 |    0 | 2020-12-10 16:02:55 | 2020-12-10 16:02:55 |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
1 row in set (0.00 sec)

现在去看第二个terminal发现，并不会阻塞等待第一个terminal的commit

mysql> select * from user_with_innodb where username="name_1be71e3a" for update;
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| id       | username      | password                             | email                | phone       | role | create_time         | update_time         |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
| 25471368 | name_1be71e3a | 1be71e44-3abe-11eb-9a64-9184e1dca9bb | 1be71e45@example.com | 13792351367 |    0 | 2020-12-10 16:02:55 | 2020-12-10 16:02:55 |
+----------+---------------+--------------------------------------+----------------------+-------------+------+---------------------+---------------------+
1 row in set (0.00 sec)

这就验证了我们的锁是行锁，不会造成全面的阻塞等待。当然即便是行锁我们也可以使用 LOCK IN SHARE MODE 模式。