mysql分析_Mysql分析

最新推荐文章于 2022-07-30 19:02:49 发布

weixin_39872893

最新推荐文章于 2022-07-30 19:02:49 发布

阅读量553

点赞数

文章标签： mysql分析

本文链接：https://blog.csdn.net/weixin_39872893/article/details/113604257

版权

锁机制

MySQL各存储引擎使用三种类型(级别)的锁定机制：行级锁定，页级锁定和表级锁定

Innodb的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁(表级锁定)的概念，也就有了意向共享锁和意向排他锁这两种。

总的来说就是Oracle锁定数据是通过需要锁定的某行记录所在的物理block上的事务槽上表级锁定信息，而Innodb的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称“NEXT-KEY locking”(间隙锁)，因为Query执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在

死锁解决办法：

当Innodb检测到系统中产生了死锁之后，Innodb会通过相应的判断来选这产生死锁的两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。实际上在Innodb发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小

当产生死锁的场景中涉及到不止Innodb存储引擎的时候，Innodb是没办法检测到该死锁的，这时候就只能通过锁定超时限制来解决该死锁了

系统锁定争用情况查询对于两种锁定级别，MySQL内部有两组专门的状态变量记录系统内部锁资源争用情况，我们先看看MySQL实现的表级锁定的争用状态变量：

mysql> show status like 'table%';

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 100 |

| Table_locks_waited | 0 |

+-----------------------+-------+

这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量说明如下：

●Table_locks_immediate：产生表级锁定的次数；

●Table_locks_waited：出现表级锁定争用而发生等待的次数；

两个状态值都是从系统启动后开始记录，没出现一次对应的事件则数量加1。如果这里的

Table_locks_waited状态值比较高，那么说明系统中表级锁定争用现象比较严重，就需要进一步分析为什么会有较多的锁定资源争用了。

对于Innodb所使用的行级锁定，系统中是通过另外一组更为详细的状态变量来记录的，如下：

mysql> show status like 'innodb_row_lock%';

+-------------------------------+--------+

| Variable_name | Value |

+-------------------------------+--------+

| Innodb_row_lock_current_waits | 0 |

| Innodb_row_lock_time | 490578 |

| Innodb_row_lock_time_avg | 37736 |

| Innodb_row_lock_time_max | 121411 |

| Innodb_row_lock_waits | 13 |

+-------------------------------+--------+

●Innodb_row_lock_current_waits：当前正在等待锁定的数量；

●Innodb_row_lock_time：从系统启动到现在锁定总时间长度；

●Innodb_row_lock_time_avg：每次等待所花平均时间；

●Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间；

●Innodb_row_lock_waits：系统启动后到现在总共等待的次数；

因为创建该表实际上就是告诉Innodb我们开始要监控他的细节状态了，然后Innodb就会将比较详细的事务以及锁定信息记录进入MySQL的error log中，以便我们后面做进一步分析使用。

Profiling的使用

Query Profiler来定位一条Query的性能瓶颈，，通过该工具可以获取一条Query在整个执行过程中多种资源的消耗情况，如CPU，IO，IPC，SWAP等，以及发生的PAGE FAULTS，

CONTEXT SWITCHE等等，同时还能得到该Query执行过程中MySQL所调用的各个函数在源文件中的位置

使用方式：

1、开启profiling参数

root@localhost : (none) 10:53:11> set profiling=1;

2、执行Query

... ...

3、获取系统中保存的所有Query的profile概要信息

root@localhost : test 07:47:35> show profiles;

+----------+------------+------------------------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------------------------+

| 1 | 0.00183100 | show databases |

| 2 | 0.00007000 | SELECT DATABASE() |

| 3 | 0.00099300 | desc test |

| 4 | 0.00048800 | show tables |

| 5 | 0.00430400 | desc test_profiling |

| 6 | 1.90115800 | select status,count(*) from test_profiling group by status |

+----------+------------+--------------------------------------

4、针对单个Query获取详细的profile信息。

在获取到概要信息之后，我们就可以根据概要信息中的Query_ID来获取某个Query在执行过程中

详细的profile信息了，具体操作如下：

root@localhost : test 07:49:24> show profile cpu, block io for query 6;

+----------------------+----------+----------+------------+--------------+---------------+

+----------------------+----------+----------+------------+--------------+---------------+

| starting | 0.000349 | 0.000000 | 0.000000 | 0 | 0 |

| Opening tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |

| System lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |

| Table lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |

| init | 0.000023 | 0.000000 | 0.000000 | 0 | 0 |

| optimizing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

| statistics | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

| preparing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

| Creating tmp table | 0.000035 | 0.000999 | 0.000000 | 0 | 0 |

| executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

| Copying to tmp table | 1.900619 | 1.030844 | 0.197970 | 347 | 347 |

| Sorting result | 0.000027 | 0.000000 | 0.000000 | 0 | 0 |

| Sending data | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |

| end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

| removing tmp table | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |

| end | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

| query end | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |

| freeing items | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |

| logging slow query | 0.000001 | 0.000000 | 0.000000 | 0 | 0 |

| logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

| cleaning up | 0.000002 | 0.000000 | 0.000000 | 0 | 0 |

+----------------------+----------+----------+------------+--------------+---------------+

索引：

在MySQL中，主要有四种类型的索引，分别为：B-Tree索引，Hash索引，Fulltext索引和RTree索引

B-Tree索引(使用最为频繁的索引类型)

MySQL中的B-Tree索引的物理文件大多都是以Balance Tree的结构来存储的，也就是所有实际需要的数据都存放于Tree的Leaf Node

在Innodb存储引擎中，存在两种不同形式的索引，一种是Cluster形式的主键索引(Primary

Key)，另外一种则是和其他存储引擎(如MyISAM存储引擎)存放形式基本相同的普通B-Tree索引，这种索引在Innodb存储引擎中被称为Secondary Index

在Primary Key中，Leaf Nodes存放的是表的实际数据，不仅仅包括主键字段的数据，还包括其他字段的数据，整个数据以主键值有序的排列。而Secondary Index则和其他普通的B-Tree索引没有太大的差异，只是在Leaf Nodes出了存放索引键的相关信息外，还存放了Innodb的主键值。

Full-text索引

Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

索引的好处

1在数据库中个表的某个字段创建索引，所带来的最大益处就是将该字段作为检索条件的时候可以极大的提高检索效率，加快检索时间，降低检索过程中所需要读取的数据量

2降低数据的排序成本

弊端

1同步更新索引，带来的IO量和调整索引所致的计算量

2索引还会带来存储空间资源消耗的增长

事务

Read UnCommited，Read Commited，RepeatableRead和Serializable这四种事务隔离级别

Innodb在事务隔离级别方面支持的信息如下：

1. READ UNCOMMITTED

常被成为Dirty Reads(脏读)，可以说是事务上的最低隔离级别：在普通的非锁定模式下

SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads(一致性读)；

2. READ COMMITTED

这个事务隔离级别有些类似Oracle数据库默认的隔离级。属于语句级别的隔离，如通过

SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE来执行的请求仅仅锁定索引记录，而不锁定之前的间隙，因而允许在锁定的记录后自由地插入新记录。当然，这与Innodb的锁定实现机制有关。如果我们的Query可以很准确的通过索引定位到需要锁定的记录，则仅仅只需要锁定相关的索引记录，而不需要锁定该索引之前的间隙。但如果我们的Query通过索引检索的时候无法通过索引准确定位到需要锁定的记录，或者是一个基于范围的查询，InnoDB就必须设置next-key或gap locks来阻塞其它用户对范围内的空隙插入。Consistent Reads的实现机制与Oracle基本类似：每一个Consistent Read，甚至是同一个事务中的，均设置并作为它自己的最新快照。

这一隔离级别下，不会出现Dirty Read，但是可能出现Non-Repeatable Reads(不可重复读)和Phantom Reads(幻读)。

3. REPEATABLE READ

REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。SELECT ... FOR UPDATE, SELECT... LOCK IN SHARE MODE, UPDATE,和DELETE，这些以唯一条件搜索唯一索引的，只锁定所找到的索引记录，而不锁定该索引之前的间隙。否则这些操作将使用next-key锁定，以next-key和gap locks锁定找到的索引范围，并阻塞其它用户的新建插入。在Consistent Reads中，与前一个隔离级相比这是一个重要的差别：在这一级中，同一事务中所有的Consistent Reads均读取第一次读取时已确定的快照。这个约定就意味着如果在同一事务中发出几个无格式(plain)的SELECTs，这些SELECT的相互关系是一致的。在REPEATABLE READ隔离级别下，不会出现Dirty Reads，也不会出现Non-Repeatable Reads，但是仍然存在Phantom Reads的可能性。

4. SERIALIZABLE

SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，Phantom Reads也不会出现。

监控

我们可以通过执行“SHOW INNODB STATUS”命令来获取比较详细的系统当前Innodb性能状态，如下：

sky@localhost : example 03:11:19> show innodb status\G

所以Innodb存储引擎为我们设计了一个比较奇怪的方式来持续获取该信息并输出到MySQL Error Log中。

实现方式就是通过创建一个名为innodb_monitor，存储引擎为Innodb的表，够奇特吧，如下：CREATE TABLE innodb_monitor(a int) ENGINE=INNODB;

当我们创建这样一个表之后，Innodb就会每过15秒输出一次Innodb整体状态信息，也就是上面所展示的信息到Error Log中。我们可以通过删除该表停止该Monitor功能，如下：

DROP TABLE innodb_monitor;

除此之外，我们还可以通过相同的方式打开和关闭

innodb_tablespace_monitor,innodb_lock_monitor,innodb_table_monitor这三种监控功能

数据切分

就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库(主机)上面，以达到分散单台设备负载的效果。数据的切分同时还可以提高系统的总体可用性，因为单台设备Crash之后，只有总体数据的某部分不可用，而不是所有的数据。

数据的切分(Sharding)根据其切分规则的类型，可以分为两种切分模式。一种是按照

不同的表(或者Schema)来切分到不同的数据库(主机)之上，这种切可以称之为数据的

垂直(纵向)切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某

种条件拆分到多台数据库(主机)上面，这种切分称之为数据的水平(横向)切分

weixin_39872893

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql分析_Mysql分析

锁机制MySQL各存储引擎使用三种类型(级别)的锁定机制：行级锁定，页级锁定和表级锁定Innodb的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁(表级锁定)的概念，也就有了意向共享锁和意向排他锁这两种。总的来说就是Oracle锁定数据是通过需要锁定的某行记录所在的物理block上的事务槽上表级锁定信息，而Innodb...
复制链接

扫一扫

mysql分析_Mysql分析

“相关推荐”对你有帮助么？