MySQL常见问题汇总

以下描述建立在以下表结构中：

create table test (  id bigint primary key auto_increment,  name varchar(32),  cts datetime);

Dupicate Key

假定业务场景，用户登录时已知id=1,name是和请求相关的变量，要求写入test表中，如果test表中之前不存在数据则写入，如果已存在数据则将原数据返回。
1.1 没办法的办法

事务A 事务B start transaction; start transaction; insert into test(id, name, cts) values(1, ‘abc’, now()); insert into test(id, name) values(1, ‘def’, now()); commit; error.duplicate key. rollback

程序捕获到DuplicateKeyException返回，接口可用，但名称、创建时间不准；程序报错，数据准确但接口不可用。
1.2 没办法的办法 2.0

事务A 事务B start transaction; start transaction; select * from test where id = 1 lock in share mode; select * from test where id = 1 lock in share mode; insert into test(id, name, cts) values(1, ‘abc’, now()); commit dead lock; rollback

问题同上，结果同上。但是对普通索引仍然适用。

那么问题来了，我能不能再DuplicateKeyException的时候查询一下返回呢？少年，我觉得你很有想法，但是你如果耿直的去查询，肯定是查不到的（Read Repeatable）。

那么这个问题真的无解么？不不不，MySQL那么流行一定 是有它的原因的！https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
1.3 MySQL推荐的解决方法

事务A 事务B insert into test(id, name, cts) values(1, ‘abc’, now()); insert into test(id, name) values(1, ‘def’, now()); commit; error.duplicate key. select * from test where id = 1 lock in share mode commit

接口可用且数据准确。更多探索：数据库死锁示例
2.常见慢查询
2.1 最常见的慢查询

mysql> alter table test add index idx_name(name);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from test where name = '123' order by cts desc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test   partitions: NULL         type: refpossible_keys: idx_name          key: idx_name      key_len: 99          ref: const         rows: 1     filtered: 100.00        Extra: Using index condition; Using filesort1 row in set, 1 warning (0.00 sec)mysql> alter table test drop index idx_name;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

懵逼脸，我明明走的索引，为什么还有filesort？（https://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html）

其实这个解释后的SQL是这样的，查询使用了where语句（索引），但是排序无法走索引.

那我们尝试给cts加个索引不就可以了吗？少年，你很有想法，我们来实践一把。

mysql> alter table test add index idx_name(name);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table test add index idx_cts(cts);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from test where name = '123' order by cts desc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test   partitions: NULL         type: refpossible_keys: idx_name          key: idx_name      key_len: 99          ref: const         rows: 1     filtered: 100.00        Extra: Using index condition; Using filesort1 row in set, 1 warning (0.00 sec)mysql> alter table test drop index idx_name;Query OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> alter table test drop index idx_cts;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

为什么还是filesort？ 这是因为mysql只会挑一个最合适的索引啊！（https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-queries.html，https://dev.mysql.com/doc/refman/5.7/en/generated-column-index-optimizations.html）

那么我们再来尝试一下～

mysql> alter table test add index idx_name_cts(name, cts);Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> explain select * from test where name = '123' order by cts desc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test   partitions: NULL         type: refpossible_keys: idx_name_cts          key: idx_name_cts      key_len: 99          ref: const         rows: 1     filtered: 100.00        Extra: Using where; Using index1 row in set, 1 warning (0.00 sec)mysql> alter table test drop index idx_name_cts;Query OK, 0 rows affected (0.03 sec)Records: 0  Duplicates: 0  Warnings: 0

生效了有木有，但是这个索引是如何选出来的呢？可以看这篇官方文档：https://dev.mysql.com/doc/refman/5.7/en/controlling-optimizer.html。

那我们有什么方法自己指定索引吗？毕竟看官方文档好烦！果然是同道中人，我们可以通过force index强制指定使用的索引。

mysql> explain select * from test force index(primary) where name = '123' order by cts desc\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: test   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 1     filtered: 100.00        Extra: Using where; Using filesort1 row in set, 1 warning (0.00 sec)