MySQL数据库语句、事务和索引调优

SQL语句调优

慢SQL语句的诱因

• 无索引、索引失效导致慢查询
• 锁等待（MDL锁、行锁）
• 等flush
• 不恰当的SQL语句
  使用不恰当的 SQL 语句也是慢 SQL 最常见的诱因之一。例如，习惯使用 <SELECT >，<SELECT COUNT()> SQL 语句，在大数据表中使用 <LIMIT M,N> 分页查询，以及对非索引字段进行排序等等。

优化SQL语句的步骤

通过 EXPLAIN 分析 SQL 执行计划

mysql> explain select * from dev where id > 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | dev   | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  692 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

• id：每个执行计划都有一个 id，如果是一个联合查询，这里还将有多个 id。
• select_type：表示 SELECT 查询类型，常见的有SIMPLE（普通查询，即没有联合查询、子查询）、PRIMARY（主查询）、UNION（UNION 中后面的查询）、SUBQUERY（子查询）等。
• table：当前执行计划查询的表，如果给表起别名了，则显示别名信息。 partitions：访问的分区表信息。
• type：表示从表中查询到行所执行的方式，查询方式是 SQL 优化中一个很重要的指标，结果值从好到差依次是：system > const > eq_ref > ref > range > index > ALL。
  system/const：表中只有一行数据匹配，此时根据索引查询一次就能找到对应的数据。
  eq_ref：使用唯一索引扫描，常见于多表连接中使用主键和唯一索引作为关联条件。
  ref：非唯一索引扫描，还可见于唯一索引最左原则匹配扫描。
  range：索引范围扫描，比如，<，>，between 等操作。
  index：索引全表扫描，此时遍历整个索引树。
  ALL：表示全表扫描，需要遍历全表来找到对应的行。
  possible_keys：可能使用到的索引。
  key：实际使用到的索引。
  key_len：当前使用的索引的长度。
  ref：关联 id 等信息。
  rows：查找到记录所扫描的行数。
  filtered：查找到所需记录占总扫描记录数的比例。
  Extra：额外的信息。

通过 Show Profile 分析 SQL 执行性能
通过 Show Profile 分析 SQL 执行性能.

常用的SQL优化

1.优化分页查询
利用子查询优化分页查询

2.优化 SELECT COUNT(*)
使用近似值
增加汇总统计

3.优化SELECT (*)

索引调优

• 覆盖索引调优
• 自增字段作主键
• 前缀索引
• 放置索引失效（最左匹配原则）

事务调优

并发事务带来的问题

1.数据丢失

2.脏读

3.不可重复读

4.幻读

事务隔离解决并发问题

InnoDB 实现了两种类型的锁机制：共享锁（S）和排他锁（X）。共享锁允许一个事务读数据，不允许修改数据，如果其他事务要再对该行加锁，只能加共享锁；排他锁是修改数据时加的锁，可以读取和修改数据，一旦一个事务对该行数据加锁，其他事务将不能再对该数据加任务锁。

未提交读（Read Uncommitted）：在事务 A 读取数据时，事务 B 读取数据加了共享锁，修改数据时加了排它锁。这种隔离级别，会导致脏读、不可重复读以及幻读。

已提交读（Read Committed）：在事务 A 读取数据时增加了共享锁，一旦读取，立即释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在读取数据时，事务 B 只能读取数据，不能修改。当事务 A 读取到数据后，事务 B 才能修改。这种隔离级别，可以避免脏读，但依然存在不可重复读以及幻读的问题。

可重复读（Repeatable Read）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了行级排他锁，直到事务结束才释放锁。也就是说，事务 A 在没有结束事务时，事务 B 只能读取数据，不能修改。当事务 A 结束事务，事务 B 才能修改。这种隔离级别，可以避免脏读、不可重复读，但依然存在幻读的问题。

可序列化（Serializable）：在事务 A 读取数据时增加了共享锁，事务结束，才释放锁，事务 B 读取修改数据时增加了表级排他锁，直到事务结束才释放锁。可序列化解决了脏读、不可重复读、幻读等问题，但隔离级别越来越高的同时，并发性会越来越低。

InnoDB 中的 RC 和 RR 隔离事务是基于多版本并发控制（MVCC） 实现高性能事务。一旦数据被加上排他锁，其他事务将无法加入共享锁，且处于阻塞等待状态，如果一张表有大量的请求，这样的性能将是无法支持的。
MVCC 对普通的 Select 不加锁，如果读取的数据正在执行 Delete 或 Update 操作，这时读取操作不会等待排它锁的释放，而是直接利用 MVCC 读取该行的数据快照（数据快照是指在该行的之前版本的数据，而数据快照的版本是基于 undo 实现的，undo 是用来做事务回滚的，记录了回滚的不同版本的行记录）。MVCC 避免了对数据重复加锁的过程，大大提高了读操作的性能。

锁具体实现算法

行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。record lock 是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁；next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

优化高并发任务

• 结合业务场景使用低级别的事务隔离
• 避免行锁升级为表锁
• 控制事务大小，减少锁定的资源量和锁定时间长度（避免长事务）
  

ACID

持久性： binlog + redo log 两阶段提交保证持久性
原子性： 事务的回滚机制 保证原子性 要么全部提交成功 要么回滚
一致性： undo log + MVCC 保证一致性 事务开始和结束的过程不会其它事务看到 为了并发可以适当破坏一致性