mysql

MySQL 系列 常见问题

尽可能全面的覆盖 MySQL 涉及的内容

Posted by lichao modified on April 24, 2021

索引

添加索引时需要考虑的因素?

  1. 不是在所有的查询条件中出现的列都需要添加索引。在表中数据具有高选择性时，B+树索引才有意义（高选择性，使用show index命令结果中的 cardinality 列，表示索引中不重复记录数量的预估值）；
  2. 关注多张表之间的联接操作，关联的键需要加索引
  3. 在 OLAP 应用中，通常需要对时间字段进行索引，因为大多数统计需要根据时间维度进行数据的筛选。

注：Cardinality 的统计放在存储引擎层进行，通过采样的方式进行

自增索引有什么好处？

显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

  1. 自增主键的插入数据模式是递增插入。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。
  2. 由于每个非主键索引的叶子节点上都包含主键的值，主键长度越小，二级索引的叶子节点就越小，二级索引占用的空间也就越小。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。

有没有什么场景适合用业务字段直接做主键的呢？简单的KV场景适合：

  • 只有一个索引；
  • 该索引必须是唯一索引。

由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。这时候优先考虑“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

自增主键有什么特点？

只保证了自增id是递增的，但不保证是连续的。

不同的引擎对于自增值的保存策略不同:

  • MyISAM 引擎的自增值保存在数据文件中。
  • InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为MySQL重启前的值”，具体情况是：
    • 在MySQL 5.7及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值max(id)，然后将max(id)+1作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是11。但如果马上重启实例，重启后这个表的AUTO_INCREMENT就会变成10。也就是说，MySQL重启可能会修改一个表的AUTO_INCREMENT的值。
    • 在MySQL 8.0版本，将自增值的变更记录在了redo log中，重启的时候依靠redo log恢复重启之前的值。

自增主键不连续的可能原因：

  • 唯一键冲突是导致自增主键id不连续的第一种原因
  • 事务回滚也会产生类似的现象

优化器什么时候不使用索引?

  • 有时优化器没有选择扫描辅助索引，而是通过扫描聚集索引，这种情况多发生于范围查找、Join链接操作等情况下：
    • 当查询数据是整行信息而辅助索引不能覆盖时（需要回表查询），而且被查询数据量很多时（大于20%），选择聚集索引。
    • 对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查询的数据量是少量的。
  • 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能，但是可以选择全索引扫描。
    • 有数据类型转换，就需要走全索引扫描。在MySQL中，字符串和数字做比较的话，是将字符串转换成数字
    • 有隐式字符编码转换，就需要走全索引扫描

如何选择索引

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，称之为“基数”（cardinality）。 索引基数

从性能的角度考虑，InnoDB使用采样统计，默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。因此，上述两个索引显示的基数并不相同。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候(innodb_stats_persistent=on时默认10，反之16)，会自动触发重新做一次索引统计。

索引基数

使用索引查询一定能提高查询的性能吗？

通常，通过索引查询数据比全表扫描要快，也必须注意到它的代价。

  1. 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT，DELETE，UPDATE 将为此多付出 4、5 次的磁盘 I/O。 因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能，索引范围查询(INDEX RANGE SCAN)适用于两种情况:
  2. 基于一个范围的检索，一般查询返回结果集小于表中记录数的 30%
  3. 基于非唯一性索引的检索

为什么MySQL有时候会选择错索引？

相信很多用户都会有这个困扰，明明选择某个索引扫描的行数更少，为什么有时候DB还是选择了其他的索引甚至扫描了全表？原因有以下几点：

  • 表增删十分频繁，导致扫描行数不准确：

由于MySQL对于删除的数据只是标记删除，后台再通过purge线程扫描标记删除的记录去做真正的空间回收，回收下来的页面会在下次需要新分配页面时候优先使用。这些特点导致频繁大量增删的时候，预估的统计信息不准确，可能会选择错误的索引。解决该类问题的方法是强制触发统计信息的更新，即analyze table。这个操作只是触发重新采样更新统计信息，因此用户不用担心这个操作会影响DML操作。

  • 扫描的行数太多，优化器可能会走主键扫全表：

假设主键索引扫描行数是10W行，而普通索引需要扫描5W行，这种情况就会遇到优化器选择了扫描行数更多的主键索引。因为考虑到只有扫描主键才能拿到真正的records，不需要回表。而普通索引是需要先拿到主键值，再根据主键值获取对应的数据，这个过程优化器选择索引时需要计算的一个成本。因此这时候优化器是有可能会选择扫描主键而不是我们认为更加合适的某个二级索引的。

普通索引和唯一索引应该怎么选择？

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响，所以尽量选择普通索引。

原因是写缓存在唯一索引中不生效

在实际使用中，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

如何避免MySQL选错索引？

  • Force Index

在MySQL中提供了force index来强制优化器使用这个索引。使用方法：select * from table_name force index (idx_a) where a = 100; 当优化器没有正确选择索引时是可以使用这种方案来解决。

  • 去掉作用重复的索引

删掉误选的索引，简单粗暴，很多索引建立其实也是给优化器的一个误导，直接删掉即可。

  • 改写SQL

修改SQL语句，主动引导MySQL使用期望的索引，一般情况这种做法比较考验用户对于系统的了解程度，建议先在类生产环境上验证后在正式修改上线。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

-- 创建表
mysql> CREATE TABLE `t` (
    `id` int(11) NOT NULL,
    `a` int(11) DEFAULT NULL,
    `b` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `a` (`a`),
    KEY `b` (`b`)
) ENGINE=InnoDB;

-- 定义测试数据存储过程
mysql> delimiter ;
CREATE PROCEDURE idata ()
BEGIN

DECLARE i INT ;
SET i = 1 ;
WHILE (i <= 100000) DO
 INSERT INTO t
VALUES
 (i, i, i) ;
SET i = i + 1 ;
END
WHILE ;
END;
delimiter ;

-- 执行存储过程，插入测试数据
mysql> CALL idata ();

当执行以下查询的时候，就会发现MySQL并没有选择过滤性更好的a，而是选择了需要扫描更多行的b。这是因为由于需要进行字段b排序，虽然索引b需要扫描更多的行数，但本身是有序的，综合扫描行数和排序，优化器选择了索引b，认为代价更小。

1
2
3
4
5
6

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows  | Extra                              |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+
|  1 | SIMPLE      | t     | range | a,b           | b   | 5       | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+-----+---------+------+-------+------------------------------------+

修改方案1：通过force index强制走索引a，纠正优化器错误的选择。这个方法最为简单有效但是过于死板，并不通用，且索引名称更变语句也需要变。

1
2
3
4
5
6

mysql> explain select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | range | a             | a   | 5       | NULL |  999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+

修改方案2：调整索引，对表的每一列都创建索引无疑是一种很蠢的行为，不但会导致写入性能下降，数据成本增加，还会误导优化器。对于这种场景，一个合理的联合索引无疑是更好的选择。

1

ALTER TABLE `t` DROP INDEX `a`, DROP INDEX `b`, ADD INDEX `ab` (`a`,`b`) ;

修改方案3：引导 MySQL 使用我们期望的索引，按b,a排序，优化器需要考虑a排序的代价。

1
2
3
4
5
6

mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b,a limit 1;
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | t     | range | a,b           | a   | 5       | NULL |  999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+-----+---------+------+------+----------------------------------------------------+

锁

如何在应用层面实现唯一性检查？

可以通过 InnoDB 存储引擎的 next-key locking 机制在应用层面实现唯一性的检查。

1

 select * from table where col = XXXX lock in share mode

如果用户通过索引查询一个值，并对该行加上一个 SLock，即使查询的值不存在，其锁定的也是一个范围，因此如没有返回任何行，那么新插入的值一定是唯一的。

如何减少锁冲突？

  • 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
  • 可以考虑通过将一行改成逻辑上的多行来减少锁冲突。例如账户记录可以考虑放在多条记录上，比如10个记录，账户总额等于这10个记录的值的总和，这样每次要给账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。

事务

事务日志有那些？

  • 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。
  • 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。
  • 慢查询日志：设置一个阈值，将运行时间超过该值的所有 SQL 语句都记录到慢查询的日志文件中。
  • 二进制日志：记录对数据库执行更改的所有操作。
  • 中继日志：中继日志也是二进制日志，用来给 slave 库恢复
  • 事务日志：重做日志 redo 和 回滚日志 undo

InnoDB 的可重复度隔离级别能解决缓读问题吗? 如何解决的？

InnoDB 存储引擎对于行的查询采用 Next-key Lock锁定算法。采用 Next-key Lock锁定技术称为 Next-key Locking，其设计的目的是为了解决幻读。利用这种锁定技术，锁定的不是单个值，而是一个范围。

为什么要避免长事务？

  1. 长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能访问数据库里面的任何数据，所以这个事务提交之前，数据库里面它可能用到的回滚记录 undo log 都必须保留，这就会导致大量占用存储空间。
  2. 除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库。

事务是如何通过日志来实现的？

事务日志是通过 redo 和 innodb 的存储引擎日志缓冲（Innodb log buffer）来实现的，当开始一个事务的时候，会记录该事务的 lsn(log sequence number) 号; 当事务执行时，会往 InnoDB 存储引擎的日志的日志缓存里面插入事务日志；当事务提交时，必须将存储引擎的日志缓冲写入磁盘（通过innodb_flush_log_at_trx_commit来控制），也就是写数据前，需要先写日志。这种方式称为“预写日志方式”

MySQL binlog 的几种日志录入格式以及区别？

  • Statement: 每一条会修改数据的 sql 都会记录在 binlog 中。
  • Row: 不记录 sql 语句上下文相关信息，仅保存哪条记录被修改。
  • Mixedlevel： 是以上两种 level 的混合使用，一般的语句修改使用 statment 格式保存 binlog，如一些函数，statement 无法完成主从复制的操作，则采用 row 格式保存 binlog, MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式，也就是在 Statement 和 Row 之间选择一种。

MySQL 数据库如何避免数据丢失（例如在数据库宕机时）？

采用 write ahead log策略，即当事务提交时，先做重做日志，再修改页。当由于发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。

二次写：在对缓冲池的脏页进行刷盘前

ACID 的含义及事务隔离级别、引发的问题？

默认可重复读

使用next-key locking的策略来避免幻读

SQL

varchar 与 char 的区别以及 varchar(50) 中的 50 代表的含义

char 是一种固定长度的类型，varchar 则是一种可变长度的类型。

在 file sort 时，varchar 会按照 fixed_length 进行内存分配。假如有一个文本字段最多存放 50 个字符，varchar(50)和 varchar(200) 存储 “hello” 所占空间一样，但后者在排序时会消耗更多内存，因为 order by col 采用 fixed_length 计算 col 长度。

int（20）中 20 的含义

是指显示字符的长度 但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001 ~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0 20表示最大显示宽度为20，但仍占4字节存储，存储范围不变；

float 和 double 的区别

  • FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
  • DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节

drop、delete 与 truncate 的区别

SQL 中的 drop、delete、truncate 都表示删除，但是三者有一些差别：

  • delete 和 truncate 只删除表的数据不删除表的结构；
  • 速度, 一般来说: drop> truncate >delete；
  • delete 语句是 dml,这个操作会放到 rollback segement中,事务提交之后才生效;
  • 如果有相应的trigger,执行的时候将被触发. truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger.

drop、delete与 truncate 分别在什么场景之下使用：

  • 不再需要一张表的时候，用 drop
  • 想删除部分数据行时候，用 delete，并且带上 where 子句
  • 保留表而删除所有数据的时候用 truncate

join 有什么问题？

  • 如果可以使用 Index Nested-Loop Join 算法，也就是说可以用上被驱动表上的索引，其实是没问题的；

  • 如果使用 Block Nested-Loop Join 算法，扫描行数就会过多。尤其是在大表上的 join 操作，这样可能要扫描被驱动表很多次，会占用大量的系统资源。所以这种join尽量不要用。

  • 如果是 Index Nested-Loop Join 算法，应该选择小表做驱动表；
  • 如果是 Block Nested-Loop Join 算法：
    • 在 join_buffer_size 足够大的时候，是一样的；
    • 在 join_buffer_size 不够大的时候（这种情况更常见），应该选择小表做驱动表。

所以，这个问题的结论就是，总是应该使用小表做驱动表。所以，更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

CATALOG
    FEATURED TAGS
    redis go mysql algorithm distribute jvm network spring java kafka mongo mq concurrent index design_pattern io rocket_mq store netty os concurrency nosql mesh etcd graph hbase kitex lang zookeeper
    FRIENDS