高性能mysql笔记

我就是我 2022-02-09 08:47 299阅读 0赞

# (一) mysql 架构 #

## 1.1 逻辑架构 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70]  
**第二层**:查询解析,分析,优化,缓存以及所有的内置函数(日期,时间,数学等等),以及所有的夸存储引擎的功能:存储过程、触发器、视图等  
**第三层**：包含存储引擎，存储引擎是负责数据的存储和提取的。存储引擎都实现了同样的接口，屏蔽了不同ing存储引擎的差异。  
存储引擎API 的底层函数,用于执行"开始事务"或者"根据主键提取一行记录"等操作  
**注意**:存储引擎不会去解析sql  1 ^1 1,不同存储引擎间不互相通信,只是简单响应上层服务器的请求  
 1 \_1 1 InnoDB 是一个例外,会解析外键定义,因为mysql服务器本身没有实现该功能

## 1.2 并发控制 ##

*  问题  
    多个查询需要在同一时刻修改数据  
    类比邮件,如果两封邮件同时到达同一个收件人服务器,入过不控制,会发生两封邮件内容相互追加
 *  读写锁  
    写操作: 加锁(独占锁),只能有一个线程在写  
    读操作:只能在没有写锁存在的情况下才能获取(共享锁),可多个线程读  
    **注**:**一个写锁或阻塞其他的写锁与读锁**
 *  锁粒度  
    含义:锁定的数据量越少,则系统的并发程度越高(比如jdk1.7 与jdk1.8 的hashmap 的实现区别)  
    3.1表锁(tabkle lock)  
    写操作对整张表加锁(锁开销小),会同时阻塞其他用户对该表的读、写操作  
    3.2 行锁（row lock）  
    特点: 最大程度支持并发处理(锁开销最大)  
    **注**:行级锁只在存储引擎实现
    
    ## 1.3 事务 ##

1.  含义  
    指的是一组原子性的sql查询,或者是说是一个独立的工作单元(单元内的sql要么都成功,要么全部失败)
2.  事物特性以及隔离级别  
    原子+一致+持久+隔离
3.  隔离级别:

*  read uncommitted
 *  read committed
 *  repeatble read(**mysql 默认的事务隔离级别**)
 *  serilizable　（完全牺牲并发性，只能串行操作，隔离级别最高）

1.  不考虑隔离性可能出现的问题:

详细参见另一篇博客  
https://blog.csdn.net/qq\_36922927/article/details/89326635

1.  死锁  
    含义:两个或多个
2.  事务日志  
    日志:顺序io  
    磁盘io:是随机io  
    预写日志:
3.  mysql 中的事务

*  auto commit
 *  set transaction isolation level (设置事务隔离级别)
 *  **同一个事务中使用多种存储引擎不可靠**(事务是在存储引擎中实现的)
 *  事务回滚,如果事务中包含非事务型表,那么该表不会回滚,也不会报错,可能有提示

## 1.4 多版本并发控制(MVCC) ##

Mysql,Oracle,PostgreSQL 等关系数据库都实现了MVCC(无统一标准,实现机制可能不同)

1.  含义:

*  可理解为是行级锁的变种
 *  根据事务开始的时间不同,每个事物对同一张表,同一时刻看到的数据可能是不一样的

1.  InnoDB 的MVCC

*  两个隐藏列,分别保存行的创建时间,行的过期时间(这里的时间不是实际时间值,而是指版本号)
 *  事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号比较

InnodDB 在repeatable read 隔离级别下的curd 操作逻辑如下

*  select  
    a. 只查找版本号 ≤ \\leq ≤ 当前事务版本号的行(也就是只查找版本早于当前事务的数据行)  
    查到的行 **要么是事务开始前存在的,要么是自身插入或者修改过的**  
    b. 行的删除版本要么未定义,要么大于当前事务版本号。这是**确保事务读取到的行，在事务开始之前未被删除**
 *  insert  
    为新插入的行以当前系统版本号作为行版本号
 *  update  
    **插入一行新纪录**，保存当前系统版本号作为行版本号，同时保存当前系统版本号到**原来的行**作为删除标识

table(id,col1,col2,col3)// 表定义
    原行:
    (1,a,b,c,1,) //最后两列是版本号 // 假设当前系统版本号是100
    // 事务开始时,取当前系统版本号作为事务版本号,然后系统版本号自增
    操作 update table set col1 =f where id=1;// 此事务版本号为100 , 事务开始时,系统版本号自增为101
    结果:
    新增一条行记录:
    (1,f,b,c,101,);//当前系统版本号101 作为行标识(行版本号)
    修改原记录:当前系统版本号作为删除标志
    (1,a,b,c,1,101);//

*  delete  
    为删除的每一行保存当前系统版本号作为行版本号

**注**: InnoDB 的mvcc **只在Repeatable read ,read committed** 这两个事务级别下工作  
read uncommitted 总是读取最新的数据行,而不是符合当前事务版本的数据行  
serializable:对所有读取的行都加锁

## 1.5 mysql的存储引擎 ##

1.  InnoDB

*  设计用来处理大量的短期事务,短期事务大部分是正常提交的,很少被回滚
 *  性能和自动崩溃恢复特性,在非事务性存储场景也很流行
 *  表基于聚簇索引(主键查询性能很高,但是二级索引中必须包含主键列,如果主键列很大,那么其他索引都会很大)

1.  MyIsAM

*  全文索引,压缩,空间函数
 *  不支持事务和行级锁(只能表加锁(这也是性能瓶颈所在))
 *  延迟更新索引
 *  压缩表 (数据不再修改的数据表)
 *  执行表修复可能导致一些数据丢失

1.  存储引擎的选择-

*  除非需要用到innodb 不具备的特性,否则优先选择innodb
 *  除非万不得已,不要选择混合的存储影青
 *  事务,如果不需要事务,那么可以选择MyIsam
 *  备份:在线热备份,innodb
 *  崩溃恢复:MyIsAm 崩溃后损坏概率比InnoDB高很多,而且恢复速度慢

一些场景:

*  日志型应用:对插入速度要求高,考虑MyISAM
 *  读多写少,如果不介意MyISAM 的崩溃恢复问题(这个问题可以在测试环境拔掉电源测试),可以选择MyISAM
 *  订单处理:支持事务是必要选项,INNODB

# (二) 剖析mysql 查询 #

## 2.1 服务器负载 ##

**含义**:找出效率低下的查询,定位和优化"坏"查询,提高应用性能

工具:

*  慢查询日志:找出代价高的查询(慢查询日志开销低,精度高)
 *  通用日志:在查询请求到服务器时进行记录,所以不包含响应时间和执行计划等重要信息
 *  pt-query-digest:分析查询日志的工具,可以将慢查询日志生成剖析报告  
    剖析报告示例:  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 1]参数解读:

<table> 
 <thead> 
  <tr> 
   <th>参数</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Rank</td> 
   <td>排名,越慢越靠前</td> 
  </tr> 
  <tr> 
   <td>Query ID</td> 
   <td>对查询语句计算出的哈希指纹</td> 
  </tr> 
  <tr> 
   <td>V/M</td> 
   <td>方差均值比(离差指数),与查询对应的执行时间正相关</td> 
  </tr> 
  <tr> 
   <td>Item</td> 
   <td>相关查询的简写,至于那个? 是 替代了表名后面的分片标识(<strong>这里的分片是什么我暂时不清楚</strong>)</td> 
  </tr> 
 </tbody> 
</table>

剖析报告后面还有详细报告  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 2]  
顶部包含:查询执行的频率,平均并发度,以及查询性能最差的一次执行在日志文件中的字节偏移值  
接着是直方图:Query\_time distribution,查询时间分布

## 2.2 剖析单条查询 ##

1.  **show profile**:MySql 5.1以后的版本引入(默认是禁用的)

SET profiling =1;

启用后,在服务器上执行的所有sql语句都会测量其耗费的时间,其他一些查询状态变更的数据  
当一条查询提交到服务器时,此工具会记录剖析信息到一张临时表中,斌哥且给查询赋予一个从1开始的整数标识符  
执行:**show profile ;** 显示剖析结果  
mysql 默认的时间只会精确到两位小数  
但是 show frofile 是显示了 7位小数,精度是很高的

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 3]来查看下Query\_Id=6 的查询的具体数据

show profile for query 6;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 4]  
发现并不是按照耗时时间排序的,我想知道的是耗时较多的是哪些步骤  
以下步骤格式化输出:

SET @query_id=6;
    SELECT
    	state,
    	SUM(DURATION) AS Total_R,
    	ROUND(
    		100 * SUM(duration) / (
    			SELECT
    				SUM(duration)
    			FROM
    				information_schema.PROFILING
    			WHERE
    				QUERY_ID =@query_id
    		),2)AS Pct_R,
    COUNT(*) as Calls,
    SUM(duration)/COUNT(*) as "R/Call"
    from information_schema.PROFILING
    where QUERY_ID=@query_id
    GROUP BY STATE
    ORDER BY Total_R DESC;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 5]以上是单表查询,没有发现特别耗时的条目。

1.  show status  
    **计数器**,显示某些活动如读索引的频繁程度,但无法给出消耗多少时间  
    参数很多,以后用到再来细究  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 6]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 7]**注**:**show status 会区分内存临时表和磁盘临时表  
    这点在explain 中是不能区别的**  
    下图中:  
    磁盘临时表:Created\_tmp\_disk\_table 0个  
    总的临时表:Created\_tmp\_tables 6  
    内存临时表: 总的临时表数-磁盘=6  
    未使用索引的读:Handler\_read\_rnd\_next  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 8]

## 2.3 诊断间歇性问题 ##

含义:间歇性问题是指系统偶尔停顿或者慢查询

## 计数器表 ##

为了统计网站的访问数

1.  单独的表来统计数据，只有一行数据  
    那么对于每一个想更新这一行的事务来说，这条记录上都有一个全局的互斥锁，变为串行，并发性能堪忧
2.  为了提高把并发性能，可以设置多行  
    预先设置100行，每个事务随机选取一行来更新  
    所有行数据累加即得最终结果

如果不想预先生成行，可以使用 on duplicate key  
![在这里插入图片描述][20190718201846590.png]  
3. 如果为了减少表数据的行数  
可以使用定时任务，将数据统计到地0行，然后删除其他行

## 加快alter table ##

alter table 操作可能导致服务停止，耗时  
解决方案：

1.  先在不提供服务的数据库上进行alter 操作，完成后，和主库切换
2.  “影子拷贝”，建立一张和源表无关的表，然后重命名，删除操作来交换两张表
3.  不是所有的alter table 都会导致表重建，alter 对应的列即可  
    ![在这里插入图片描述][20190718203837300.png]

![在这里插入图片描述][20190718203827729.png]

# 索引基础 #

## B\_Tree 索引 ##

名字是Btree ，但是底层实现可能是B+,T\_Tree 结构存储这种索引  
B\_Tree:

1.  所有值是按顺序存储的，每一个叶子到根的距离相同
2.  指针实际上定义子节点页中值得上限和下限
3.  最终存储引擎要么找到对应的值，要么不存在

### 如何能使用上索引 ###

满足最左原则即可

索引列必须按照建立索引的顺序使用，不能跳过，如果跳过，那么只能在跳过列之前的索引生效

. 如果不是按照索引的最左列开始查找，则无法使用索引  
. 不能跳过索引列  
. 如果有某个列的范围查询，则其右边的列无法使用索引优化查询

## hash 索引（Memory 殷勤支持 ##

hash 表实现  
索引=hash 值+数据行的指针  
注意：

1.  hash 索引只包含哈希值和行指针
2.  hash 索引数据不是按照索引值顺序存储，故无法用于排序
3.  hash 索引只支持等值查询 =，IN(),<>  
    不支持任何范围查询，例如where price》100
4.  访问hash 速度快，除非hash 冲突很多，如果有哈市冲突，存储引擎必须便利链表的所有行指针，逐行进行比较
5.  如果hash 冲突很多，一些索引维护操作的代价很高。删除一行，需要遍历对应hash 值链表的每一行，找到并删除对应行的引用，冲突越多，代价越大

innodb 的自适应hash 索引：  
innnodb 在察觉到某些索引使用率很高，自动在B-tree 上建立一层hash 索引，快速定位到节点

创建自定义hash 索引

比如：在存储大量URL,根据URL查找，如果使用B+Tree 存储URL,内容很大，可以存储一个索引列（hash）

hash 列可以使用触发器来在插入数据时，自动插入hash 值

![在这里插入图片描述][20190718221138316.png]  
![在这里插入图片描述][20190718221249408.png]

## 空间数据索引（R-Tree） ##

可以用作地理数据存储，无需前缀查询，从所有维度来索引数据

## 全文索引 ##

查找的是文本中的关键词，而不是直接比较琐索引的值

## 索引的优点 ##

1.  减少服务器需要扫描的数据量
2.  避免排序和临时表
3.  将随机io变为顺序IO

如果数据表数据小，全表扫描效率更高  
中，大型表，索引非常有效  
特大型表。建立和使用索引的代价随之增长，可以使用分区技术查出需要的一组数据，而不是一条一条记录的去匹配  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 9]

### 聚簇索引 ###

聚簇索引不是一种单独的索引类型，而是一种数据存储方式。具体细节依赖于实现方式，但是Innodb 的聚簇索引实际在同一个结构中保存了B-Tree索引和数据行。  
数据行实际上存放在索引的叶子页中。  
“聚簇”表示数据和相邻的减值紧凑的存放在一起。因为无法同时把数据行存放在两个不同的地方，所以**一个表只能有一个聚簇索引**。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 10]聚集的优点：

1.  相关数据存储在一起，如电子邮箱实现时，可以将ID 来聚集数据，只需要从磁盘读取少数数据也就能获取某个用户的全部邮件。如果不适用聚簇，可能每封邮件发生一次IO
2.  数据访问更加快（相对于非聚集索引）
3.  使用覆盖索引扫描的查询可以直接使用叶节点的主键值  
    缺点：

### 覆盖索引 ###

使用索引直接获取列的数据，这样不需要读取数据行。如果一个索引包含（或者是覆盖）所有要查询的字段的值，称之为“覆盖索引”  
好处：

1.  索引条目通常远小于数据行大小，如果只需要读取索引，那么mysql 就会极大的减少数据访问量
2.  索引通常是按照列值顺序存储的（至少在单个页内是如此）
3.  一些存储引擎如MyIsam,在内存中只缓存索引，数据则依赖操作系统来缓存，因此访问数据需要一次系统调用

— 未完待续

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70]: /images/20220209/9c0a11cde1954f4cab4e176d7bfb50ce.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 1]: /images/20220209/2f852f8192aa4031b316fb4b9efcef5f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 2]: /images/20220209/6150b4ba8edd4048a43a3e6ee5e533e2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 3]: /images/20220209/74d1d17421b74b1b9b1f1958f262109e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 4]: /images/20220209/d5ad04a9a30c42af8387e1dc66f26674.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 5]: /images/20220209/1184d0c2d9034e48b36d5d7b008bbcc3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20190503170941234.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 7]: /images/20220209/3baf4b873dab4039b0d133713b04c5fa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 8]: /images/20220209/9bd7019a814a4b11a02b6c33f5010da1.png
[20190718201846590.png]: /images/20220209/af797bf2b32e4e5e81f2af205b4ebe42.png
[20190718203837300.png]: /images/20220209/86aaaa7e13f344caa890cd6ed9c1321e.png
[20190718203827729.png]: /images/20220209/419a292ee26b4a07a59c0a4b505525c6.png
[20190718221138316.png]: /images/20220209/06c0f5b461a8488ba8610c618aea1173.png
[20190718221249408.png]: /images/20220209/c75e0ec74ead40a1b1a05ceac17ccd6e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 9]: /images/20220209/3b9909f1dc2a42d5874028eb3e186cbd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2OTIyOTI3_size_16_color_FFFFFF_t_70 10]: /images/20220209/1ca5505cad7d45bfaba1b85a9a29ec88.png