MySQL基本架构

MySQL - 图1

大体来说MySQL分为server层和存储引擎层两部分。

  1. server层

server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等

  1. 存储引擎层

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL5.5.5版本开始成为了默认存储引擎。

  1. 从上图看出，不同的存储引擎共用一个server层，也就是从连接器到执行器的部分。

    连接器

MySQL

buffer pool（缓冲池）、insert buffer（插入缓存）、change buffer（写缓存）

MySQL中MyIsAM存储引擎和InnoDB存储引擎都是采用的B+树结构。不同的是前者是非聚集索引，后者主键是聚集索引，所谓聚集索引是物理地址连续存放的索引，在取区间的时候，查找速度非常快，但同样的，插入的速度也会受到影响而降低。聚集索引的物理位置使用链表来进行存储。

索引

  1. 索引在优化其中执行
  3. **索引的出现为了提高查询效率，常见的索引模型（哈希表、有序数组、搜索树）。**
  1. 哈希表
    哈希表是一种以键-值存储的数据结构，适用于等值查询场景，区间范围查询性能慢；
  2. 有序数组
    有序数组在等值查询和范围查询都有非常好的性能；只是对于查询很快，在插入操作上很慢；有序数组索引只适用于静态存储引擎。
  3. 搜索树

关于索引的一些问题

  1. 索引只能定位到page，page内部怎么进行定位行数据？
    答：内部有个有序数据，二分法
  2. “N叉树”的N值在mysql是否可以被调整？
    答：
  3. 怎么让mysql的myisam引擎支持事务？
    答：因为myisam不支持崩溃恢复，所以即便使用lock table硬实现也是问题很多，ACID里面原子性和持久性做不到；隔离性只能实现基本用不上的串行化；一致性在正常运行的时候依赖串行化，在异常崩溃的时候也不能保证。
  4. 全库只读，为什么不使用set global readonly=true的方式？
    答：一、在某些系统中readonly的值会被用来做其他逻辑，比如用来判断是主库还是备库；二、在异常处理的机制上有差异，如果FTWRL命令之后由于客户端异常断开，那么mysql会自动释放这个全局锁，整个库回到可以正常更新的状态，而将整个库被设置为readonly状态，这样会导致整个库长时间处于只读状态。
  5. 如何安全的给小表加锁？
    答：首先要解决长事务，事务不提交，就会一直占用着MDL锁。在mysql的information_schema库中innodb_trx表中，可以查看当前执行的事务

业务的更新不只是DML（增删改查），还有DDL（表结构操作），当库被全局锁锁上后，就不能做DDL操作。

InnoDB存储引擎索引模型

在InnoDB存储引擎中，表是根据主键顺序以索引的形式存放，这种存储方式的表被称为索引组织表。InnoDB使用的是B+树索引模型，所有的数据存储在B+树中。

B+ 树优点：

  • 能很好地配合磁盘的读写；
  • 减少单次查询的磁盘访问次数
  1. 索引被分为两类主键索引和非主键索引（又称二级索引）；
    主键索引，又称为聚簇索引（clustered index），主键索引的叶子节点内存储的是整行数据。
    非主键索引的叶子节点内容是主键的值，非主键索引又称为二级索引。
    回表：回到主键索引树搜索的过程被称为回表；

主键索引与非主键索引的区别？

主键索引直接通过主键得到所查询数据（只需一次查询），而非主键索引是先通过非主键索引在索引树上查询到对应主键，再通过主键查询到需要的值（比主键索引多遍历了一次索引树）。

索引种类：

  • 覆盖索引：常用的一种性能优化手段，通过非主键索引查询，非主键索引所在树的节点是该需要的值，则不需要再先查询出主键索引再通过主键索引查询出值，在查询上减少了一次查询，达到提高性能目的；
  • 最左前缀原则：最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符，在建立联合索引后，其一，顺序是优先考虑的原则，可根据索引字段使用率进行最左调整顺序。其二，还要考虑的原则是空间，如果联合索引中最左索引字段，占用空间太大时，可以考虑给后面索引字段单独建立索引；
  • 索引下推：mysql 5.6 之后引入了索引下推优化，可以在索引遍历过程中，对索引中包含字段优先做判断，直接过滤掉不满足条件的记录，达到减少回表次数

唯一索引与普通索引的区别？

  • 对于唯一索引，来说需要将数据页读入内存中，若没有冲突，则插入这个值，语句执行结束；
  • 对于普通索引，则是将更新记录change buffer（写缓冲），语句执行结束；
  • 将数据从磁盘读入内存涉及随机io的访问，是数据库里面成本最高之一，change buffer因为减少了随机磁盘访问，则对更新性能提升明显。
  • redo log 主要节省的是随机写磁盘IO消耗，change buffer 主要节省的是随机读磁盘IO消耗
  • MySQL中有两种存储索引统计方式，可以通过设置参数innodb_stats_persistent的值来选择：当设置为on时，表示统计信息会持久化，此时N默认为20，M默认为10；当设置为off时，表示统计信息只存储在内存中，此时N默认为8，M为16。N表示为innodb选择的默认数据页 和 当变更的数据数超过1/M时表示索引统计信息不会固定不变。

给字符串类型加索引

  • 使用前缀索引，能否提升性能，取决于前缀长度的定义；```mysql
  • 前缀索引例子： alter table xxx add index index2(email(6))
  • 通过select count(distinct email) as L from table 查询出值，依次select count(distinct left(email,4)）as L4,count(distinct left(email,5)）as L5,count(distinct left(email,6)）as L6,count(distinct left(email,7)）as L7 from SUser; 再通过自定义的损失区分度，挑选出前缀长度

使用前缀索引不足：当使用前缀索引时，当匹配到时回到ID索引再去查询并判断该字符是否符合；与不使用前缀索引的覆盖索引相比多了一次进行比较，覆盖索引直接匹配到回ID索引去查询，所以是否使用前缀索引要根据实际去考虑需要添加索引的字符串。

  2. - **使用倒序存储**，此方式是字符串前几位大多是一样的，区分在字符串后面几位，才使用此方式；
  3. - **使用hash字段**，使用这种方式需在表中新增一个字段，保存该字段的的校验码，使用crc32()函数将新增字段得到检验码存入数据库，在此校验码字段上增加索引，为防止生成的校验码出现相同的，在搜索时加上原字段进行筛选。
  4. - **倒序存储与hash字段的异同？**
  5.    - 共同点：都不支持范围查询，倒序存储的字段上创建索引是按照倒序字符串方式进行排序，已经不能进行范围查询了，hash字段方式只能使用等值查询；
  6.    - 异同点：
  7.       1. 从占用空间上讲，倒序存储是在主键索引上不会消耗空间，而hash字段存储，是在主键索引上增加了新的字段，主键索引节点上一条数据存储的长度增加，只增加4位，而倒序存储使用前缀索引如果长度大于4则比hash字段消耗空间；
  8.       2. 从CPU消耗上讲，倒序存储方式在读写的时候的都会调用一次reverse()函数，而hash函数会调用crc32()函数，这两个函数相比reverse()函数消耗CPU更小；
  9.       3. 从查询效率上看，使用hash字段方式查询性能相对稳定，因为crc32()函数计算出来的值重复的概率很低，则查询每次扫描行接近于1，而倒序存储使用的是最左前缀索引，方式，查询扫描的数据行不确定。
  11. <a name="5c45d6be"></a>
  12. #### 索引总结
  14. 1. 创建索引的方式
  15.    - 直接创建完整索引，比较占用空间；
  16.    - 创建前缀索引，节省空间，增加扫描次数，且不能使用覆盖索引；
  17.    - 倒序存储，在创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
  18.    - 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，不支持范围扫描。
  20. <a name="5be63ace"></a>
  21. ### mysql锁
  23. **mysql内的锁：全局锁、表级锁和行锁三类**
  25. <a name="3963c8eb"></a>
  26. #### 全局锁
  28. **全局锁：就是对整个数据库实例加锁；**
  30. 1. mysql提供了一个加全局读锁方法，命令：flush tables with read lock(FTWRL)，使用该命令后，当整个库处理只读状态时，之后其他线程下的语句会被阻塞：数据更新语句（数据增删改）、数据定义语句（建表、修改表结构）和更新事务的提交语句。
  31. 2. 全局锁使用场景：做全库的数据备份，把整库每个表都select出来存成文件。
  32. 3. 全局锁的缺点：若数据库是主从同步的，在做数据其中主库使用全局锁在做数据备份时会导致主从库同步数据延迟；
  34. <a name="73671222"></a>
  35. #### 表级锁
  37. 1. 表级锁有两种：表锁和元数据锁
  38. 2. 表锁的语法是 lock tables ..... read/write，释放锁的语法是unlock tables ...  ，也可以在客户端断开连接后主动释放；lock tables语法除了会限制别的线程读写外，也限制了本线程之后的操作。
  39. 3. 另外一种表级锁：MDL（metadata table），MDL不需要显示使用，在访问一个表的时候会被自动加上;<br />MDL的作用：保证读写的准确性（MDL是在mysql5.5版本加入的）；MDL是防止DDL和DML并发冲突<br />读锁之间不互斥，写锁之间是互斥的用来保证更新表结构的安全性。
  41. <a name="58e27516"></a>
  42. #### 行锁
  44. **带 lock in share mode的sql语句是当前读；**
  46. - 两阶段锁协议：在innodb事务中，行锁是在需要的时候才加上，在事务结束时才释放；
  47. - 死锁：当并发系统中出现不同线程出现循环资源依赖，涉及到的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态；解决死锁有两种策略：一、直接进入等待，等待超时，超时时间可由innodb_lock_wait_timeout来设置；二、发起死锁检测，发现死锁后，主动回滚锁链条中某一个事务，将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
  48. - 
  50. 1. mysql行锁是在引擎层由各个引擎实现的，但是myisam引擎不支持行锁；
  52. <a name="9f82401d"></a>
  53. #### 事务
  55. 1. begin/start transaction 此命令不是一个事物创建的开始，而是在执行完此命令后第一条操作Innodb表的语句，表示开始；
  56. 2. 马上启动一个事务的命令 start transaction with consistent snapshot;
  57. 3. mysql中的视图：
  58.    - 一个是view，他是一个用查询语句定义的虚拟表，在调用时执行查询语句并生成结果，创建视图的语法是create view ... ；
  59.    - 另一个是InnoDB在实现MVCC时用到一致性读视图（consistent read view），用于支持RC（read committed，读提交）和RR（repeatable read，可重复读）隔离级别的实现。
  60. 4. mysql中MVCC里快照的工作方式：
  61.    - 在innodb里面每个事务有一个唯一的事务ID，叫作transaction ID。他是开始时向innodb的事务系统中申请，按照申请顺序递增。在每次事务更新数据时，生成一个新的transaction ID 并赋值给这个数据版本的事务ID，记为row trx_id,旧的数据版本也会被保留，并且在新的数据版本中，能够有信息可以直接获取。
  62. 5. 事务视图的数据版本
  63.    - 自己更新的版本，可见
  64.    - 版本未提交，不可见
  65.    - 版本已提交，但是是在视图创建后提交的，不可见
  66.    - 版本已提交，而是在视图创建前提交的，可见
  68. <a name="4d38bbae"></a>
  69. #### 间隙锁
  71.     间隙锁是解决幻读问题；行锁之锁住行但是对于插入操作会在行锁间隙间进行操作，导致出现幻读。间隙锁之间不会冲突，
  73. <a name="c360e994"></a>
  74. #### 排序
  76. <a name="349e5b19"></a>
  77. ##### 全字段排序和rowid排序
  79.     全字段排序：是select 字段+ where字段 + order by字段
  81.     row_id排序：是order by 字段 + row_id；该排序会造成回表过多，回表会造成磁盘读，影响性能。
  83. <a name="f40a3dd4"></a>
  84. ##### mysql中排序算法
  86. - 归并排序算法
  87. - 优先队列排序算法
  88. - 随机排序算法
  90. <a name="2f0356c3"></a>
  91. #### 数据库调优
  93. 一个innodb表包含两部分：表结构定义和数据；在mysql 8.0版本之前，表结构是存在以 .frm为后缀文件里。在mysql 8.0版本，允许表结构定义放在系统表中。
  95. <a name="e1838045"></a>
  96. ##### 参数innodb_file_per_table
  98.     innodb_file_per_table 表数据既可以存在共享表空间里，也可以是单独的文件，由该参数决定；这个参数设置为off 表示 表的数据放在系统共享表空间，这个参数设置为on 表示每个innodb表数据存储在一个以 .ibd为后缀的文件中。推荐将该参数设置为on,一个表单独存储为一个文件更容易管理。
  100. <a name="f884b081"></a>
  101. ##### 参数sort_buffer_size
  103.     该参数是MySQL排序所需内存的大小，若排序的数量小于该参数值，则排序在内存中完成，若排序数量大于该参数值，则会利用磁盘临时文件进行辅助排序
  105. <a name="d2fb26e0"></a>
  106. ## 数据库原理
  108. <a name="9f82401d-1"></a>
  109. #### 事务
  111. <a name="b59c9e0f"></a>
  112. ##### 概念
  114.     事务指满足ACID特性的一组操作，可以通过commit提交一个事务，也可以通过rollback回滚一个事务；
  116. <a name="ACID"></a>
  117. ##### ACID
  119. - 原子性（Atomicity）：事务被视为不可分割的最小单元，事务的所有操作要么成功全提交，要么失败全回滚；回滚可以用回滚日志（undo log）实现，回滚日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作；
  120. - 一致性（consistency）：数据库在事务执行前后都保持一致性状态，在一致性状态下，所有事务对同一个数据读取结果相同；
  121. - 隔离性（isolation）：一个事务所做的操作在提交之前，对其他事务不可见；
  122. - 持久性（durability）：一旦事务提交，其所做的修改都会永远保存在数据库中。即使系统发生崩溃事务执行的结果也不能丢失；系统发生崩溃使用重做日志（redo log）进行恢复，从而实现持久性，重做日志记录的是数据页的物理修改。
  124. 事务ACID的关系
  127. - 只要满足一致性，事务的执行结果才正确；
  128. - 在无并发下，事务串行执行，隔离性一定满足，此时只要满足原子性就一定满足一致性；
  129. - 在并发下，多事务并行执行，事务不仅要满足原子性，还要满足隔离性，才能满足一致性；
  130. - 事务满足持久化性是为了应对系统崩溃下的数据恢复；
  132. ---
  134. <a name="be5205e7"></a>
  135. #### 并发一致性问题
  137. 在并发环境下，事务隔离性很难保证
  139. <a name="d0f48623"></a>
  140. ##### 丢失修改
  142. T1和T2两个事务都对一个数据进行修改，T1先，后T2，最后结果是T2把T1的数据覆盖掉
  145. <a name="a37ef20a"></a>
  146. ##### 读脏数据
  148. T1修改了一条数，T2随后读这个数据，如果T1撤销了修改，T2读取的数据就为脏数据
  151. <a name="d2a5a4d1"></a>
  152. ##### 不可重复读
  154. T2读取一条数据，T1对该数据进行修改，若T2再次读取这个数据，此时读取的结果和第一次读取的结果不同
  157. <a name="de18b0fb"></a>
  158. ##### 幻读
  160. T1读取某个范围的数据，T2在这个范围内插入一条数据，T1再次读取这个范围的数据，此时得到的结果与第一次读取的结果不同
  162. ---
  164. **产生并发不一致性问题的原因是破坏了事物的隔离性，解决方法是通过并发控制保证隔离性，并发控制可以使用封锁来实现，但是封锁操作需要用户自己控制。**
  166. <a name="75580fe8"></a>
  167. #### 锁
  169. <a name="ba78a9ff"></a>
  170. ##### 锁粒度
  172. 1. mysql提供了两种所粒度：行级锁和表级锁
  173. 2. 对于锁的使用，使用时应尽量缩小加锁数据范围，减少发生锁争用，则系统的并发程度越高
  174. 3. 锁需要消耗资源，锁的各种操作（获取锁、释放锁、检查锁状态）都会增加系统开销。因此锁的粒度越小系统开销越大
  175. 4. 在选择锁时，应在锁开销和并发之间做权衡
  177. <a name="ff2da1ca"></a>
  178. ##### 锁类型
  180. <a name="90109d41"></a>
  181. ###### 读写锁
  183. - 互斥锁（exclusive），简称X锁，又称为写锁；
  184. - 共享锁（shared），简称S锁，又称为读锁；
  186. 1. 对于以上两种锁有如下规定
  187.    - 一个事务对数据对象A加了X锁，就可以对A进行读取和更新操作，加锁期间其他事务不能对A加任和锁
  188.    - 一个事务对数据对象A加了S锁，可以对A进行读取操作，不能进行更新操作，加锁期间其他事务能对A加S锁，但不能加X锁
  189. 2. 锁的兼容关系<br />
  191. <a name="74305923"></a>
  192. ###### 意向锁
  194. 在锁粒度上，意向锁是表级锁；
  196. 意向锁在原来X/S锁上引入IX/IS都是表锁，用来表示一个事务想要在表中的某个数据行上加X锁和S锁。有以下规定：
  198. - 一个食物在获得某个数据行对象的S锁之前，必须先获得表的IS锁或者更强的锁；
  199. - 一个事务在获得某个数据行对象的X锁之前，必须先获得表的IX锁
  201. 通过引入意向锁，一个事务T对表A加X锁，需要先检查是否有其他事务对表A加了X/IX S/IS锁，若有，则加锁失败
  203. 锁的兼容关系：
  206. 解释如下：
  208. - 任意的IS IX锁之间都是兼容的，IX和IS都是表级锁；
  210. <a name="aca70728"></a>
  211. #### 封锁协议
  213. <a name="1761653f"></a>
  214. ##### 三级封锁协议
  216. <a name="30558730"></a>
  217. ###### 一级封锁协议
  219.     事务T要修改数据A时必须加X锁，直到T结束才能释放锁；这样可以解决丢失修改问题，因为不能同时有两个事务对同一数据进行修改，那么事务修改就不会被覆盖。
  222. <a name="9f3696dd"></a>
  223. ###### 二级封锁协议
  225.     在一级锁协议的基础上，在读取数据是加上S锁，读完立即释放S锁；这可以解决读脏数据问题，一个事务在对数据A进行修改，根据一级锁协议，回家X锁，那么就不能再加S锁了，所以不会读取数据。
  228. <a name="1761653f-1"></a>
  229. ###### 三级封锁协议
  231.     在二级锁协议基础上，在读取数据A时就加S锁，直到事务结束才能释放S锁；解决了不可重复读的问题，因为读A时，其他事务不能对A加X锁，从而使在读数据期间不能发生任何更改。
  234. <a name="031331f8"></a>
  235. ##### 两段锁协议
  237. 加锁和解锁分为两个阶段进行；
  239. 可串行化调度，通多并发控制，使并发执行的事务结果和某个单独执行事务的结果相同，串行执行事务互不干扰，不会出现并发不一致性问题。
  241. 满足两段锁协议,可以串行化调度
  243. ```mysql
  244. lock-x(A)...lock-s(B)...lock-s(C)...unlock(A)...unlock(C)...unlock(B)

不满足两段锁协议，但可以串行化调度

  1. lock-x(A)...unlock(A)...lock-s(B)...unlock(B)...lock-s(C)...unlock(C)

mysql 隐式与显示锁定

隐式锁定：mysql的innodb存储引擎采用两段锁协议，会根据隔离级别在需要的的时候自动加锁，并且所有的锁都是在同一时刻被释放；

显示锁定：

  1. SELECT ... LOCK In SHARE MODE;
  2. SELECT ... FOR UPDATE;

隔离级别

  • 未提交读（read uncommitted）：事务中修改，即使没有提交也可以被其他事务读取到；
  • 提交读（read committed）：一个事务只能读取到已经提交的事务所做的修改，一个事务在修改数据未提交之前不能被其他事务读取到；
  • 可重复读（repeatable read）：保证在同一个事务中多次读取同一数据的结果是一样的；
  • 可串行化（serializable）：强制事务串行执行，多个事务互不干扰，不会出现并发一致性问题，但是这种串行化隔离级别需要加锁实现，因为要使用加锁机制保证同一时间内只有一个事务提交，也就保证了事务的串行化。

多版本并发控制

多版本并发控制（multi-version concurrency control， MVCC）是MySQL的innodb存储引擎实现隔离级别的一种具体体现方式，用于实现提交读和可重复读隔离级别。未提交读隔离级别总是读取数据，在隔离级别上要求很低，不需要使用mvcc,可串行化隔离级别需要加锁实现，使用mvcc做不到。

在MVCC思想下，写操作更新最新版本快照，而读操作还是读取的旧版本快照，没有互斥关系；在MVCC中事务的修改操作（增删改）会为数据行新增一个版本快照。脏读和不可重复读根本原因是因为事务读取到其他事务未提交的修改。在mvcc中规定了只能读取已经提交的快照。

版本号
  • 系统版本号SYS_ID：是一个递增的数字，每开始一个新事务，系统版本号就会自动递增；
  • 事务版本号TRX_ID：食物开始时的系统版本号。

Undo日志

多版本是指多个版本的快照，快照存储在undo日志中，该日志通过回滚指针ROLL_PTR把一个数据行的所有快照连接起来。

ReadView

MVCC维护了一个ReadView结构，主要包括了当前系统未提交的事务列表TRX_IDs{TRX_ID_1,TRX_ID_2,…….}，还有该列表的最小值TRX_ID_MIN和最大值TRX_ID_MAX；

在进行读操作时，根据快照的TRX_ID与TRX_ID_MIN和TRX_ID_MAX之间的关系，从而判断快照是否可以使用：

  • TRX_ID < TRX_ID_MIN，表示该数据行快照时是在所有未提交事务前进行更改，可以使用；
  • TRX_ID > TRX_ID_MAX，表示该数据行快照是在事务启动后被更改的，不可使用；
  • TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX，需要根据隔离级别进行判断：
    • 提交读：若TRX_ID在列表中，表示该数据行快照的事物还未提交，则不能使用，反之，则可；
    • 可重复读：不可以使用

在数据行快照不可使用的情况下，需要沿着undo log 的回滚指针ROLL_PTR找到下个快照，进行上面判断。

快照读和当前读

快照读

MVCC的select操作是快照中的数据，不需要进行加锁。

  1. select * from xxx

当前读

MVCC会对数据库进行修改的操作（insert 、update、delete）需要进行加锁，从而读取最新的数据。

在进行select 操作时，可以强行进行加锁

  1. select * from xxx where ? lock in share mode   s锁
  2. select * from xxx where ? for update    x锁

next-key locks

next-key locks是mysql的innodb存储引擎的一种锁实现。在可重复读隔离级别下，使用mvcc+next-key locks解决幻读问题。

record locks

锁定一个记录上的索引；

gap locks

锁定索引之间的间隙，但不包括索引本身。例如

  1. select c from t where c between 10 and 20 for update

next-key locks

next-key locks 是record locks和gap locks的结合，他锁定了一个前开后闭区间。

范式

MySQL性能优化

为什么要优化？

优化前需要考虑会带来哪些影响

优化可能带来的问题
  • 优化不一定是单纯环境下进行，还有可能是一个复杂的生产环境；
  • 任何技术可以解决一个问题，但必然会带来其他问题的风险；
  • 对于优化而带来的问题，控制在可接受范围之内；
  • 保持现状或出现更差的情况都是优化失败

优化的需求
  • 稳定性和业务可持续性，比性能更加重要；
  • 优化不可避免涉及到变更，变更就会带来风险；
  • 所有优化都需要由业务需要驱动的；

优化由谁参与

数据库管理员、业务部门代表、应用程序架构师、应用程序设计人员、应用程序开发人员、硬件及系统管理员、存储管理员等与业务相关人员共同参与

优化思路

优化什么

在数据库优化上有两个主要方面：即安全与性能；

  • 安全，数据可持续性
  • 性能，数据的高性能访问

优化的范围
  1. 存储、主机和操作系统方面：
    • 主机架构稳定性；
    • I/O规划及配置；
    • Swap交换分区；
    • OS内核参数和网络问题；
  2. 应用程序方面：
    • 应用程序稳定性；
    • SQL语句性能；
    • 串行访问资源；
    • 性能欠佳会话管理；
    • 应用是否适合使用MySQL；
  3. 数据库优化方面：
    • 内存；
    • 数据库结构（物理和逻辑）；
    • 实例配置；

优化维度

数据库优化维度：硬件、系统配置、数据库表结构、SQL及索引

MySQL - 图2

优先选择：

  • 优化成本：硬件 > 系统配置 > 数据库表结构 > SQL及索引
  • 优化效果：硬件 < 系统配置 < 数据库表结构 < SQL及索引

优化工具

  1. 数据库层面
    检查问题常用工具：```shell mysql mysqladmin:mysql客户端，可进行管理操作 mysqlshow:功能强大的可以查看shell命令 show [session | global] variables: 查询数据库参数信息 show [session | global] status:查询数据库的状态信息 information_schema : 获取元数据的方法 show engine innodb status : innodb引擎的所有状态 show processlist : 查看当前所有连接session状态 explain : 获取查询语句的执行计划 show index from table：查询表内索引信息 slow-log : 记录慢查询语句 mysqldumpslow : 分析slow log文件 参数 -s，是order的顺序 al，平均锁定时间 ar，平均返回记录时间 at，平均查询时间（默认） c，计数 l，锁定时间 r，返回记录 t，查询时间 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

    1. <br />不常用工具（）：```shell
    2. zabbix : 监控主机、系统、数据库（部署zabbix平台）
    3. pt-query-digest: 分析慢日志
    4. mysql slap : 分析慢日志
    5. sysbench : 压力测试工具
    6. mysql profiling ： 统计数据库整体状态的工具
    7. performance schema：MySQL性能状态统计的数据
    8. workbench：管理、备份、监控、分析、优化工具
    9. prometheus + grafana +node-export : 监控平台
    10. druid : ali数据连接池做监控

  2. 数据库层面解决问题

    • 一般应急调优思路：针对突然的业务办理卡顿，无法进行正常业务处理，需要立马解决的场景。

      1. 1、show processlist;
      2. 2、explain select id,name from stu where name='clsn'; #ALL id name age sex
      3. show index from table;
      4. 3、通过执行explain判断索引（有没有、合不合理）或者语句本身问题；
      5. 4、show status like '%lock%'; #查询锁状态
      6. kill session_id; #杀掉有问题的进程

    • 常规条有思路：针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了

      1. 1、查看slowlog，分析slowlog，分析出查询慢的语句；
      2. 2、按照一定优先级，一个一个排查完所有慢语句；
      3. 3、分析top SQL，进行explain调试，查看语句执行时间；
      4. 4、调整索引或语句本身。

  3. 系统层面

    • CPU方面：shell vmstat、sar、top、htop、nmon、mpstat

    • 内存shell free、ps -aux

    • IO设备（磁盘、网络）shell iostat、ss、netstat、iptraf、iftop、lsof

    • vmstat命令返回值1、procs:r显示有多少进程正在等待CPU时间。b显示处于不可中断的休眠进程数量。在等I/O。 2、memory：swpd显示被交换到磁盘的数据块的数量。free 未被使用的数据块，buff用户缓冲数据块，cache用于操作系统的数据块的数量。 3、swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。si和so最好为0； 4、io：每秒从设备中读入bi的写入到设备bo的数据块的数量，反映了磁盘的I/O。 5、system：显示了每秒发生中断的数量（in）和上下文交换（cs）的数量。 6、cpu：显示用于运行用户代码，系统代码，空闲，等待I/O的CPU时间。

    • iostat命令说明：实例命令：iostat -dk 1 5 　　　　 iostat -d -k -x 5 （查看设备使用率（%util）和响应时间（await）） 1）tps：该设备每秒的传输次数。“一次传输”意思是“一次I/O请求”。多个逻辑请求可能会被合并为“一次I/O请求”。 2）iops ：硬件出厂的时候，厂家定义的一个每秒最大的IO次数 3）"一次传输"请求的大小是未知的。 4）kB_read/s：每秒从设备（drive expressed）读取的数据量； 5）KB_wrtn/s：每秒向设备（drive expressed）写入的数据量； 6）kB_read：读取的总数据量； 7）kB_wrtn：写入的总数量数据量；这些单位都为Kilobytes。

  4. 系统层面问题解决
    在生产中服务器中一般认为服务器的CPU不超过90%都是没有问题；
    但是不能排除以下的状况：

    • CPU负载过高，IO负载低
      • 内存不够
      • 磁盘性能差
      • SQL问题，去排查SQL
      • IO出问题了（磁盘到临界了、raid设计不好、raid降级、锁、在单位时间内tps过高）
      • tps过高，大量的小数据IO、大量的全表扫描。
    • IO负载高，CPU负载低
      • 大量小的IO写操作；autocommit，产生大量小IO；IO/PS，磁盘的一个定值，硬件出厂时厂家定义的一个每秒最大IO次数；
      • 大量大的IO写操作，SQL问题的几率比较大
    • IO和CPU负载都很高
      • 硬件不够或SQL存在问题

基础优化

优化思路
  • 定位问题：硬件—>系统—>应用—>数据库—>架构（高可用、读写分离、分库分表）

硬件相关优化就需要升价硬件，购买性能更强的服务器。

系统优化
  • swap: MySQL尽量避免使用swap，阿里云的服务器中默认为swap为0；
  • IO：raid、no lvm、ext4或xfs、ssd、IO调度策略

  • swap调整：shell /proc/sys/vm/swappiness的内容改成0（临时） /etc/sysctl. conf上添加vm.swappiness=0（永久）

  • IO调度策略```shell

    echo deadline>/sys/block/sda/queue/scheduler 临时修改为deadline

    1. <br />永久修改```shell
    2. vi /boot/grub/grub.conf
    3. 更改到如下内容:
    4. kernel /boot/vmlinuz-2.6.18-8.el5 ro root=LABEL=/ elevator=deadline rhgb quiet

系统参数调整

  • linux系统内核参数优化shell vim/etc/sysctl.conf net.ipv4.ip_local_port_range = 1024 65535：# 用户端口范围 net.ipv4.tcp_max_syn_backlog = 4096 net.ipv4.tcp_fin_timeout = 30 fs.file-max=65535：# 系统最大文件句柄，控制的是能打开文件最大数量

  • 用户限制参数（MySQL可以不设置以下配置）```shell vim/etc/security/limits.conf

  • soft nproc 65535
  • hard nproc 65535
  • soft nofile 65535
  • hard nofile 65535 ```

数据库参数优化

  • 调整thread_concurrency：# 并发线程数量个数 sort_buffer_size：# 排序缓存 read_buffer_size：# 顺序读取缓存 read_rnd_buffer_size：# 随机读取缓存 key_buffer_size：# 索引缓存 thread_cache_size：# (1G—>8, 2G—>16, 3G—>32, >3G—>64)

  • 连接层（基础优化）
    设置合理的连接客户和连接方式max_connections # 最大连接数，看交易笔数设置 max_connect_errors # 最大错误连接数，能大则大 connect_timeout # 连接超时 max_user_connections # 最大用户连接数 skip-name-resolve # 跳过域名解析 wait_timeout # 等待超时 back_log # 可以在堆栈中的连接数量

  • SQL层（基础优化）query_cache_size： 查询缓存 >>> OLAP类型数据库,需要重点加大此内存缓存，但是一般不会超过GB。 对于经常被修改的数据，缓存会立马失效。 我们可以实用内存数据库（redis、memecache），替代他的功能。

  • 存储引擎层（innodb基础优化参数）default-storage-engine innodb_buffer_pool_size # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70% innodb_file_per_table=(1,0) innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中 binlog_sync Innodb_flush_method=(O_DIRECT, fdatasync) innodb_log_buffer_size # 100M以下 innodb_log_file_size # 100M 以下 innodb_log_files_in_group # 5个成员以下,一般2-3个够用（iblogfile0-N） innodb_max_dirty_pages_pct # 达到百分之75的时候刷写 内存脏页到磁盘。 log_bin max_binlog_cache_size # 可以不设置 max_binlog_size # 可以不设置 innodb_additional_mem_pool_size #小于2G内存的机器，推荐值是20M。32G内存以上100M

分布式事务