【总结】mysql调优

一.事务

1.事务的特性

（1）原子性（Atomicity），可以理解为一个事务内的所有操作要么都执行，要么都不执行。
（2）一致性（Consistency），可以理解为数据是满足完整性约束的，也就是不会存在中间状态的数据，比如你账上有400，我账上有100，你给我打200块，此时你账上的钱应该是200，我账上的钱应该是300，不会存在我账上钱加了，你账上钱没扣的中间状态。
（3）隔离性（Isolation），指的是多个事务并发执行的时候不会互相干扰，即一个事务内部的数据对于其他事务来说是隔离的。
（4）持久性（Durability），指的是一个事务完成了之后数据就被永远保存下来，之后的其他操作或故障都不会对事务的结果产生影响。

2.事务的并发问题

（1）脏读：一个事务读取到另一个事务没有提交的数据
（2）不可重复读：同一事务中，两次读取同一数据，得到的内容不同
（3）幻读：同一事务中，用同样的操作读取两次，得到的记录数不同

3.事务的隔离级别

（1）读未提交：事务A可以读取到事务B未提交的数据
（2）读已提交：事务A只能读取其它事务已提交的数据（避免了脏读）
（3）可重复读：事务A只保存另一个事务做更新操作前的状态，保证另一个事务update时，当前事务的查询结果是一致的（避免了不可重复读）（mysql默认）
（4）序列化：事务串行化顺序执行（避免脏读 不可重复读 幻读）

二.锁

1.锁的类型

mysql锁的类型：全局锁，表锁，行锁，间隙所

1.1 全局锁

全局锁就是对整个数据库实例加锁
Mysql提供了一个加锁的语句：Flush tables with read lock (FTWRL)。它能使整个实例上面，只读，所有的写和更新，都会被阻塞。全局锁的使用经典的使用场景是做全局的数据备份使用

1.2 表锁

普通的表锁,MDL
（1）普通的表锁
普通的表锁也是分读锁与写锁，数据库提供语句操作：lock tables … read/write，使用unlock tables进行释放锁。具体注意的点是：加了普通的表锁之后，对当前加锁线程接下来的数据库操作，都是有影响的。
举个例子：如果A线程使用语句lock tables t1 read, t2 write; 这个语句，那么，其他线程写t1和读写t2都会被阻塞；同时线程A再进行unlock之前，也只能读t1和读写t2，连写t1都是不被允许的。自然也不能访问其他的表.
在没有出现行锁之前，都是通过表锁进行并发控制的，上面例子可见，影响面还是太大，限制太严格了

（2）元数据锁(MDL)
元数据锁主要是面向DML和DDL之间的并发控制，如果对一张表做DML增删改查操作的同时，有一个线程在做DDL操作，不加控制的话，就会出现错误和异常。元数据锁不需要我们显式的加，系统默认会加。

1.3 行锁

读锁（共享锁），写锁（排它锁）
行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

【总结】：innodb什么时候加表锁？什么时候加行锁？
只有在你增删改查时匹配的条件字段带有索引时，innodb才会使用行级锁，在你增删改查时匹配的条件字段不带有索引时，innodb使用的将是表级锁。

三.sql优化步骤

1.通过show status命令了解各种SQL的执行频率

show status like 'com_%'; //查询（增删改查等执行的次数.com_commit/rollback了解提交和回滚的情况，回滚频繁意味着编码可能存在问题）
show status like 'slow_query'; //查询慢查询的次数

2.定位执行效率较低的sql

在my.cnf 里 通过 log-slow-queries[=file_name]开启慢日志查寻

3.通过EXPLAIN分析较低SQL的执行计划

1.id

select识别符。我理解的事sql执行的顺序的标识
（1）id相同时，执行顺序由上至下
（2）如果是子查询，id序号会递增，id越大优先级越高，越先被执行

2.select_type

表示查询中每个select子句的类型

select_type查询类型	说明
SIMPLE	简单的 select 查询,不使用 union 及子查询
PRIMARY	最外层的 select 查询
SUBQUERY	在select或者where中包含了子查询，子查询中的第一个 select 查询,不依赖于外部查询的结果集
DEPENDENT SUBQUERY	子查询中的第一个 select 查询,依赖于外部 查询的结果集
DERIVED	用于 from 子句里有子查询的情况。MySQL 会递归执行这些子查询, 把结果放在临时表里
UNION	UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
DEPENDENT UNION	UNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集
UNCACHEABLE UNION	UNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询

特别关注 DEPENDENT SUBQUERY ，会严重消耗性能，不会进行子查询，会先进行外部查询,生成结果集,再在内部进行关联查询。子查询的执行效率受制于外层查询的记录数

3.table

显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的）

4.type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”
常用的类型有： ALL、index、range、 ref、eq_ref、const、system（从左到右，性能从差到好）

type	说明
ALL	MySQL将遍历全表以找到匹配的行
index	遍历索引树
range	只检索给定范围的行，使用索引来选择行
ref	查找条件列使用了索引而且不为主键和unique。就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描
ref_eq	使用了主键或者唯一性索引进行查找
const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时（where后面是逐渐或者唯一索引）
system	表中只有一行

5.possible_keys

预测用到的索引

6.key

实际用到的索引

7.key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度

8.ref

哪些列或者常量被用于查找索引列上的值（只有当type为ref的时候，ref这列才会有值）

explain select * from t1, t2 where t1.col1 = t2.col1 and t1.clo2 = 'ab';

ref的值为: t2.col1 ,"ab"

9.rows

估算的找到所需的记录所需要读取的行数

10.Extra

包含MySQL解决查询的详细信息
(1)Using filesort
mysql的排序方法主要分为两大类，一种是排序的字段是有索引的，因为索引是有序的，所以不需要另外排序，另一种是排序的字段没有索引，所以需要对结果进行排序，在这种情况下才会如上图所示显示一个Using filesort

eg：select * from a where a1="" order by a2; //a2没有建索引

（2）using temporary：性能损耗大，用到临时表（常见于group by）

eg：select * from a where a1="" group by a2

（3）using where：表明虽然用到了索引，但是没有索引覆盖，产生了回表。

select name,age from a where age="" //name需要回表查询

（4）using index：索引覆盖，查询的内容可以直接在索引中拿到

select age from a where age="" //索引覆盖

四.sql常用的优化方式

1.索引优化

注意索引失效的情况
（1）or连接的条件不是每一个列都有索引，这时有索引的列也会失效
（2）复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用
（3）like查询是以%开头（以%结尾，索引可以使用）
（4）存在索引列的数据类型隐形转换，则用不上索引（比如列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引）
（5）where 子句里对索引列上有数学运算，用不上索引
（6）where 子句里对有索引列使用函数，用不上索引
（7）如果mysql估计使用全表扫描要比使用索引快,则不使用索引（比如数据量极少的表）

2.设计调优

（1）数据量大，可以分库分表
（2）主从同步，读写分离

3.业务优化

根据具体业务场景进行优化