Mysql性能优化

看我七十二变
76 阅读14分钟

一、Mysql 逻辑架构

image.png

 

Mysql的逻辑架构图

MySQL逻辑架构整体分为三层，最上层为客户端层，并非MySQL所独有，诸如：连接处理、授权认证、安全等功能均在这一层处理。

MySQL大多数核心服务均在中间这一层，包括查询解析、分析、优化、缓存、内置函数(比如：时间、数学、加密等函数)。所有的跨存储引擎的功能也在这一层实现：存储过程、触发器、视图等。

最下层为存储引擎，其负责MySQL中的数据存储和提取。和Linux下的文件系统类似，每种存储引擎都有其优势和劣势。中间的服务层通过API与存储引擎通信，这些API接口屏蔽了不同存储引擎间的差异。

二、索引

1、索引的优点

a):大大减少了服务器需要扫描的数据量;

b):可以帮助服务器避免排序和临时表;

c):可以将随机I/O变为顺序I/O;

2、索引的缺点

a):虽然索引大大提高了查询速度，但同时也会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件（增删改数据都会改变平衡树各节点中的索引数据内容，破坏树结构）;

b):建立索引会占用磁盘空间，如果在一个大表上创建了多种组合索引，索引文件的会膨胀很快;

查看索引大小 ：

use information_schema;

SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'test' and table_name='demo';

查看表的大小：

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data  from TABLES where table_schema='test' and table_name='demo';

3、索引的结构及原理

a):二叉查找树

二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值;

image.png

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次;

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造；

image.png

 

b):平衡二叉树（AVLTree）

在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1;

平衡因子 = 左子树的高度 - 右子树的高度；

image.png

 

c):平衡多路查找树（B-Tree）

和平衡二叉树的不同：

平衡二叉树节点最多有两个子树，而 B 树每个节点可以有多个子树，M 阶 B 树表示该树每个节点最多有 M 个子树

平衡二叉树每个节点只有一个数据和两个指向孩子的指针，而 B 树每个中间节点有 k-1 个关键字和 k 个子树（ k 介于阶数 M 和 M/2 之间，M/2 向上取整）

B 树的所有叶子节点都在同一层，并且叶子节点只有关键字，指向孩子的指针为 null

image.png

平衡二叉树和 B 树对比图

  如何查找数据：

1、从根节点开始，如果查找的数据比根节点小，就去左子树找，否则去右子树

2、和子树的多个关键字进行比较，找到它所处的范围，然后去范围对应的子树中继续查找

3、以此循环，直到找到或者到叶子节点还没找到为止

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

image.png

3阶的B-Tree

模拟查找关键字29的过程：

1.根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

2.比较关键字29在区间（17,35），找到磁盘块1的指针P2。

3.根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】

4.比较关键字29在区间（26,30），找到磁盘块3的指针P2。

5.根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】

6.在磁盘块8中的关键字列表中找到关键字29

 

d):B+Tree

B-Tree 结构图 中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率；

在B+Tree中，所有数据记录节点都是按照 键值大小 顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低 B+Tree 的高度；

B+Tree相对于B-Tree有几点不同：

1.非叶子节点只存储键值信息。

2.所有叶子节点之间都有一个链指针。

3.数据记录都存放在叶子节点中。

优势：

使用索引查找的时候先将数据结构读取到主存中，由于B+Tree的所有节点不存数据只存key，因此与B-Tree相比，使用B+Tree每次就能将更多的节点读取到内存中，也就能做更少的I/O操作；

image.png

B+Tree

4、索引的类型

a):普通索引

最基本的索引，它没有任何限制；

b):唯一索引

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

c):主键索引

一种特殊的唯一索引，一个表只能有一个主键，不允许有空值

d):组合索引

指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；

e):全文索引

主要用来查找文本中的关键字，而不是直接与索引中的值相比较；

三、查询优化

1、查询如何地执行

image.png

查询执行路径

a):客户端发送一条查询给服务器;

b):服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段;

c):服务器进行SQL解析、预处理，再由优化器生成对应的执行计划;

d):根据执行计划，调用存储引擎的API来执行查询;

e):讲结果返回给客服端;

2、查询优化器

最主要的工作目标是尽可能的使用索引，并且要使用条件最严格的索引来尽可能多、尽可能快得排除不符合索引的数据行。

explain 执行计划

image.png

type:

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref;

Extra:

Using temporary，表示需要创建临时的表以满足需求，通常是因为GROUP BY的列没有索引，或者GROUP BY和ORDER BY的列不一样，也需要创建临时表，建议添加适当的索引;

Using filesort，表示无法利用索引完成排序，也有可能是因为多表连接时，排序字段不是驱动表中的字段，因此也没办法利用索引完成排序，建议添加适当的索引;

Using where，通常是因为全表扫描或全索引扫描时（type 列显示ALL 或 index），又加上了WHERE条件，建议添加适当的索引;

参考文档：cloud.tencent.com/developer/a…

3、sql语句的优化

a):应尽量避免在 where 子句中使用!=、<>操作符、对字段进行 null 值判断、使用 or 来连接条件、对字段进行表达式操作、对字段进行函数操作、not in;

b):对于like语句，以%开头的不会使用索引，以%结尾会使用索引;

c):应该用小结果集驱动大结果集，同时把复杂的 JOIN 查询拆分成多个query，因为 JOIN 多个表，可能导致更多的锁定和堵塞。可以用join代替复杂的子查询;

d):尽量避免大事务操作，提高系统并发能力;

e):count()、group by()、limit();

4、索引的优化

a):最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整;

b): =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式;

c):只要列中含有NULL值，就最好不要在此例设置索引，组合索引如果有NULL值，此列在使用时也不会使用索引;

d):尽量使用短索引;

5、表的优化

a):数字类型：建议区分开 TINYINT（1个字节） / INT（4个字节） / BIGINT（8个字节） 的选择，因为三者所占用的存储空间也有很大的差别，能确定不会使用负数的字段，建议添加unsigned定义;

b):字符类型：非万不得已不要使用 TEXT 数据类型，其处理方式决定了他的性能要低于char或者是varchar类型的处理。定长字段，建议使用 CHAR 类型，不定长字段尽量使用 VARCHAR，且仅仅设定适当的最大长度，而不是非常随意的给一个很大的最大长度限定，因为不同的长度范围，MySQL也会有不一样的存储处理;

c):时间类型：int()时间戳；

d):ENUM 类型：不建议使用，0与‘0’有非常大的区别，enum类型对于php等弱语言类型的支持很差，弱语言类型打引号和不打引号的值可能是同一类型；

e):decimal：通常用户保存带有小数点的数据，比如金钱，但是需要额外的空间和开销；

6、分表分区

分表常用方法：垂直分割、水平分割

水平拆分常用的方案：hash取模和range范围方案

1.hash :

优点：订单数据可以均匀的放到那4张表中，这样此订单进行操作时，就不会有热点问题；

热点的含义：就是对订单进行操作集中到1个表中，其他表的操作很少；

比如订单表，一般用户操作订单数据，都会集中到这段时间产生的订单。如果这段时间产生的订单 都在同一张订单表中，那就会形成热点，那张表的压力会比较大；

缺点：将来的数据迁移和扩容，会很难；

2.range:

优点：不需要做数据迁移;

缺点：有热点问题,这段时间产生的订单都会集中到此张表中，这个就导致1表过热，压力过大，而其他的表没有什么压力;

hash是可以解决数据均匀的问题，range可以解决数据迁移问题，那我们可以不可以两者相结合呢？利用这两者的特性呢？

方案设计：

image.png

关键点：

1.id=0～4000万肯定落到group01组中。

2.group01组有3个DB，那一个id如何路由到哪个DB？

3.根据hash取模定位DB，那模数为多少？模数要为所有此group组DB中的表数，上图总表数为10。为什么要去表的总数？而不是DB总数3呢？

4.如id=12，id%10=2；那值为2，落到哪个DB库呢？这是设计是前期设定好的，那怎么设定的呢？

核心流程：

image.png

有些服务器的性能高，存储高，就可以安排多存放些数据，有些性能低的就少放点数据。如果我们取模是按照DB总数3，进行取模，那就代表着【0，4000万】的数据是平均分配到3个DB中的，那就不能够实现按照服务器能力适当分配了

image.png

分区：

1.冷热分离：表非常大且只在表的最后部分有热点数据，冷数据根据分区规则自动归档;

2.定期淘汰历史数据：按时间写入，历史数据可淘汰，可快速删除，空间可快速回收;

3.优化查询：在where字句中包含分区列时，分区可以大大提高查询效率，减少缓存开销、减少IO开销;

4.统计性能提升：在涉及sum()和count()这类聚合函数的查询时，可以在每个分区上面并行处理，最终只需要汇总所有分区得到的结果;

5.跨多个磁盘来分散数据查询，来获得更大的查询吞吐量;

MySQL的分区规则：

范围 ：PARTITIONED BY RANGE COLUMNS

列表 ：PARTITION BY LIST COLUMNS

HASH：PARTITION BY HASH

KEY ：PARTITION BY KEY

子分区：SUBPARTITION BY XXX

参考文档：cloud.tencent.com/developer/a…

四、硬件优化（待定）

cpu 磁盘

五、Mysql 8.0特性（待定）

1、隐藏索引

索引可以被“隐藏”和“显示”。当一个索引隐藏时，它不会被查询优化器所使用。也就是说，我们可以隐藏一个索引，然后观察对数据库的影响。如果数据库性能有所下降，就说明这个索引是有用的，于是将其“恢复显示”即可；如果数据库性能看不出变化，说明这个索引是多余的，可以删掉了；（注意：不能对主键设置隐藏索引）

如果一个表中没有指定明确的主键，但对一个 NOT NULL 的列设为了 UNIQUE 索引，那么也不能对这个列设置隐藏索引，因为此时这个列是此表的隐性主键；

2、UTF-8 编码

数据库的默认编码将改为 utf8mb4，这个编码包含了所有 emoji 字符；MySQL 中的 utf8 编码并不是真正的 UTF-8，而是阉割版的，最长只有3个字节。当遇到占4个字节的 UTF-8 编码，例如 emoji 字符或者复杂的汉字，会导致存储异常；

3、窗口函数（Window Functions）

window functions 是对一组数据进行计算，与使用 group by 时不同，不会进行单行的结果输出，而是与每条记录相关联；

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

1.聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条；

2.聚合函数也可以用于窗口函数中；

参考文档：cloud.tencent.com/developer/a…

4、降序索引

如果一个查询，需要对多个列进行排序，且顺序要求不一致。在这种场景下，要想避免数据库额外的排序-“filesort”，只能使用降序索引；

如果只对单个列进行排序，降序索引的意义不是太大，无论是升序还是降序，升序索引完全可以应付;

5、JSON 强化

JSON_TABLE() :创建了一个 JSON 数据的关系型视图。它将 JSON 数据结果映射成关系数据库中的行和列。用户可以将该函数的返回结果当作一个普通的表，使用 SQL 进行查询;

JSON_ARRAYAGG():用于生成 JSON 数组;

JSON_OBJECTAGG():用于生成 JSON 对象;

avatar
文章
阅读
粉丝