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: 0

mysql> explain select * from test where name = '123' order by cts desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 99
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 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: 0

mysql> alter table test add index idx_cts(cts);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from test where name = '123' order by cts desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_name
          key: idx_name
      key_len: 99
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 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: 0

mysql> 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: 0

mysql> explain select * from test where name = '123' order by cts desc\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: idx_name_cts
          key: idx_name_cts
      key_len: 99
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)