mysql优化基础篇

首先声明，非本人同意不得转载！！！

MySQL基础

一次数据访问，访问磁盘需要完成三个动作：

1、寻道：磁头移动到指定的磁道。

2、旋转：旋转磁盘，等待指定扇区从磁头下经过

3、传输：数据在磁盘与内存之间的传输

 

MySQL减少IO的预读操作，磁盘并非根据需要的数据读取，而是每次都会预读一部分数据。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

依据局部性原理：时间局部性、空间局部性、顺序局部性

 

MySQL中每个存储块称为一页（在许多操作系统中，页的大小通常为 4k）

 

数据库中一个表有N页，一页有N行。

 

锁粒度

一种提高共享资源并发性的方式就是让锁定对象更具有选择性。

尽量只锁定需要修改的部分数据，而不是所有的资源。

更理想的方式是，只对会修改的数据片进行精确的锁定。

任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

 

锁的各种操作，包括获得锁、检查锁是否已经解除、释放锁等，都会增加系统的开销。

 

另外注意：写锁比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入到写锁的前面）。

 

尽管存储引擎可以管理自己的锁，MySQL本身还是会使用各种有效的表锁来实现不同的目的。例如，服务器会为注入Alter Table之类的语句使用表锁，而忽略存储引擎的锁机制。

 

事务

 

#修改隔离级别

set session transaction isolation level READ UNCOMMITTED;

 

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

不可重复读：事务A两次执行查询会得到不同的结果，是因为其他事务在事务A执行过程中进行了提交，修改了某一行数据的值。关注于每一行的值有没有变化

幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。关注于行数有没有变化

 

死锁

InnoDB目前处理死锁的方法是，将持有最少行级排它锁的事务进行回滚（这是相对比较简单的死锁回滚算法）

 

事务日志

写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数存储引擎都是这样实现的，我们通常称为预写式日志，修改数据需要些两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。

 

多版本并发控制（MVCC）

MySQL中大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性能考虑，他们一般都同时实现了多版本并发控制（Multi-Version Concurrency Control，MVCC）.

Oracle等其他数据库也实现了MVCC，但是各自实现机制不尽相同，以为MVCC没有一个统一的实现标准。

 

可以认为MVCC就是行级锁的一个变种，但是他在很多情况下避免了加锁操作。

MVCC是通过保存数据在某个时间点的快照来实现的。也就是说，不管一个事务需要执行多长时间，每个事物看到的数据都是一致的。

根据事务开始时间的不同，不同事务对同一张表，在同一个时刻看到的数据可能是不一样的。

MVCC有很多种版本的实现，在InnoDB引擎下MVCC是通过在每行记录后面保存两个隐藏的列来实现的。

一列保存了行的创建时间（版本），一列保存了行的删除时间（版本）。存储的并不是实际的时间值，而是系统版本号。

每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询中每行记录的版本号进行比较。

比如在REPEATABLE TABLE隔离级别下：

Insert: 为新插入的每一行记录保存当前系统版本号作为行版本号。

Delete：为删除的每一行保存当前系统版本号作为删除版本号。

Update：插入一行新的记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号为原来的行作为删除版本号。

Select：

1.只查找版本号早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号）；这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的。

2.行的删除版本号要么没有，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除。

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

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

注意：需要周期性的整理，来真实的删除老的、过期的数据。

 

MyISAM引擎加锁并发特性：

concurrent_insert

0 : 不允许并发

1 ：（默认）如果表中没有被删除的行， 允许一个进程读数据，另一个线程从表尾写入数据。

2 ：不管有没有被删除的行， 都允许从表尾写入数据。