MySQL调优学习笔记（一、MySQL基础）

目录：

• 并发控制：锁
• 事务
• 存储引擎
• 性能优化

并发控制：锁

MySQL并发控制的主要手段就是锁，锁主要分为两种：

1、共享锁（也叫读锁）。

2、排它锁（也叫写锁），写锁会阻塞其它的读锁与写锁。

———————————————————————————————————————————————————————

说道锁，那么就一定要了解锁的粒度，MySQL中锁的粒度大可分为三种（不同的存储引擎支持的锁粒度不一样，目前高版本的MySQL默认使用InnoDB引擎，它默认为行级锁）：

	开销	加锁速度	是否会出现死锁	并发读与性能
表级锁	小	快	不会	并发度低，性能最差
行级锁	大	慢	会	并发度高，性能最高
页级锁	介于表级锁与行级锁之间	介于表级锁与行级锁之间	会	介于表级锁与行级锁之间

 

 

 

———————————————————————————————————————————————————————

事务

1、事务的四大特性：

• 原子性：一个事物中的SQL要么全都执行，要么全不执行。
• 一致性：事务前后的数据需要保持一致，所有约束依然需要满足。
• 隔离性：一个事物的执行，不受另一个事务的影响。
• 持久性：事务执行完后，对数据的修改必须持久化到数据库中。

———————————————————————————————————————————————————————

2、事务的隔离级别：

• READ UNCOMMIT（未提交读）：即使事务没有commit，其它事务也能狗看到修改后的数据。
• READ COMMIT（已提交读/不可重复读）：事务从开始到结束，所做的任何修改都对其它事务不可见。
• REPEATABLE READ（可重复读，MySQL默认）：同一个事务中，读到的数据是一致的。
• SERIALIZABLE（序列化）：事务串行执行。

脏读：事务可以读取到其它未提交的数据。

不可重复读：因其它事务的修改对本身可见，所以会导致多次读取的值可能会不一样，故不能读取多次，被称为不可重复度。

幻读：事务A读1到5的数据，此时1-5会被事务A上锁，然后事务B插入一条数据6，并执行了commit。此时事务A如果再查询的话就会查到数据6，这就像幻觉一样，但MySQL通过MVCC使得数据6并不会被查询出来（虽然不会被查询出来，但却不能插入数据6，因为库里已经存在了数据6）。总得来说幻读就是当事务不独立执行时发生的一种现象，当第一个事务涉及到全表修改，第二个事务插入一行新数据，此时便会发生操作第一个事务的用户发现表中还有未修改的数据，就像发生幻觉一样。

———————————————————————————————————————————————————————

3、死锁：

死锁是指，多个两个及以上的事务对同一资源的占用，并请求锁定对方占用的资源。

如以下情况：

-- 事务一
start transaction;
update t_customers set name = '小明1' where id = 1;
update t_customers set name = '小华1' where id = 2;
commit;

-- 事务二
start transaction;
update t_customers set name = '小华2' where id = 2;
update t_customers set name = '小明2' where id = 1;
commit;

若两个事务都执行了第一条update语句，锁定了各自的资源后，那么执行第二条语句时就会出现死锁的情况。

而InnoDB目前处理死锁的方法是，将持有最少行级排它锁的事务进行回滚（这是相对比较简单的死锁回滚算法），如果数量相同，就会回滚最后尝试获取锁的事务。

———————————————————————————————————————————————————————

4、MVCC：多版本并发控制，可以认为MVCC就是行级锁的一个变种，但是他在很多情况下避免了加锁操作。

MVCC是通过保存数据在某个时间点的快照来实现的，因MVCC没有一个同意的标准，所以各自的实现都不一样。

在InnoDB引擎是通过在每列数据后添加两个隐藏的列来实现，一列保存行数据创建时间（版本号），一列是行数据删除时间（版本号）。

比如在REPEATABLE READ隔离级别下：

• Insert：为新插入的每一行记录保存当前系统版本号作为行版本号。
• Delete：为删除的每一行保存当前系统版本号作为删除版本号。
• Update：插入一行新的记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号为原来的行作为删除版本号。
• Select：
• 1、只查找版本号早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号）；这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。
• 2、行的删除版本号要么没有，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。

MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。因为READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。

而SERIALIZABLE则会对所有读取的行都加锁。

存储引擎

InnoDB引擎：InnoDB表基于聚簇索引建立，其索引结构和MySQL的其他存储引擎有很大差别，从磁盘读取数据时采用的可预测性预读。

Myisam引擎：在MySQL5.1版本之前，MyISAM是默认的存储引擎。MyISAM提供了大量的特性，包括全文检索、压缩、空间函数等，但MyISAM不支持事务和行级锁，而有一个缺陷就是崩溃后无法安全恢复虽然MyISAM有这样的缺陷，但并非一无是处。对于只读的数据，或者表比较小，可以忍受修复操作，则依然可以继续使用MyISAM引擎。

Memory引擎：在Memory表中，所有的数据都是保存在内存中，不需要进行磁盘I/O。重启时候会保留表结构，但数据会丢失。

性能优化

大致可从4个方面优化：

1、硬件

2、系统配置优化

3、数据表结构优化

4、SQL及索引优化