《高性能MySQL》的重要知识点

一心猿

于 2023-12-08 13:50:19 发布

阅读量998

点赞数 16

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_41891244/article/details/134833698

版权

一、事务ACID

原子性

        一个事务必须被视为一个不可分割的工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。

一致性

        数据库总是从一个一致性状态转换到下一个一致性状态。

隔离性

        一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性

        一旦提交，事务所做的修改就会被永久保存到数据库中。此时即使系统崩溃，数据也不会丢失。

二、事务的隔离级别

READ UNCOMMITTED（未提交读）

在READ UNCOMMITTED级别，在事务中可以查看其他事务中还没有提交的修改。

READ COMMITTED（提交读）

一个事务可以看到其他事务在它开始之后提交的修改，但在该事务提交之前，其所做的任何修改对其他事务都是不可见的。

REPEATABLE READ（可重复读）

在同一个事务中多次读取相同行数据的结果是一样的。

SERIALIZABLE（可串行化）

SERIALIZABLE是最高的隔离级别。该级别通过强制事务按序执行，使不同事务之间不可能产生冲突。

隔离级别之间的利与弊

三、最优Mysql配置文件

基于mysql8的配置示例

不要让socket和.pid文件使用服务器编译的默认值；在不同的MySQL版本中有一些bug，使用默认值可能会导致问题，所以最好显式设置。
open_files_limit设置句柄尽可能大
innodb_log_buffer_size：控制缓冲区大小，通常不需要将缓冲区设置得太大。建议的范围是1～8M B
innodb_flush_method：允许配置InnoDB与文件系统的实际交互方式，它同时影响日志文件和数据文件。用的是类UNIX操作系统，并且RAID控制器有备用电池的写缓存，我们建议使用O_DIRECT。如果不是，则default或O_DIRECT都可能是最佳选择，具体取决于应用程序。
innodb_file_per_table选项允许你将InnoDB配置为每个表使用单独的文件。它将数据存储在数据库目录下的tablename.ibd文件中。这使得删除表时更容易回收空间
sync_binlog选项控制MySQL如何将二进制日志刷新到磁盘，默认值是1，意味着MySQL将执行刷新并保持二进制日志的持久性和安全性。强烈推荐将其设置为1，不建议设置为任何其他值。
可以设置参数 innodb_dedicated_server=ON来让MySQL自动探测服务器的内存大小，根据内存大小设置innodb_buffer_pool_size, innodb_log_file_size 和 innodb_flush_method 三个参数
变量thread_cache_size指定了MySQL可以保存在缓存中的线程数。其默认值为-1或auto-sized，通常不需要更改这个变量，除非服务器会收到很多连接请求。要检查线程缓存是否足够大，请查看Threads_created状态变量。应该尽量保持线程缓存足够大，以使每秒创建的新线程数少于10个，但通常很容易使这个数字低于每秒1个。
高性能事务需求的最佳配置是将innodb_flush_log_at_trx_commit设置为1，并将日志文件放在具有备用电池的写缓存和SSD的RAID卷上，这既安全又非常快。
skip_name_resolve此设置禁用另一个与网络和身份验证相关的陷阱：DNS查找。选项将在身份验证期间禁用DNS查找。但如果你这样配置了，则需要将所有基于主机名的授权转换为使用IP地址、通配符或特殊主机名“localhost”，因为基于主机名的账户将被禁用。
read_only和super_read_only：read_only选项可防止未经授权的用户对副本进行更改，副本应仅通过复制而不是从应用程序接收更改。我们强烈建议将副本设置为只读模式。
还有一个更严格的只读选项super_read_only，它甚至可阻止拥有SUPER权限的用户写入数据。启用此功能后，唯一可以将更改写入数据库的就是复制。我们也强烈建议启用super_read_only。它将防止你意外地使用管理员账户将数据写入只读副本，从而引起数据不同步。

四、schema设计与管理

选择优化数据类型

一般来说，尽量使用能够正确存储和表示数据的最小数据类型。
简单数据类型的操作通常需要更少的CPU周期。例如，整型数据比字符型数据的比较操作代价更，例如，一个是应该将日期和时间存储为MySQL的内置类型而不是字符串类型，另外一个是应该用整型数据存储IP地址。
通常情况下最好指定列为NOT NULL，除非明确需要存储NULL值。
由于额外的空间需求和计算成本，应该尽量只在对小数进行精确计算时才使用DECIMAL——例如，存储财务数据。但在一些大容量的场景，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可。
这些情况使用VARCHAR是合适的：字符串列的最大长度远大于平均长度；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
CHAR适合存储非常短的字符串，或者适用于所有值的长度都几乎相同的情况。例如，对于用户密码的MD5值，CHAR是一个很好的选择，它们的长度总是相同的。对于经常修改的数据，CHAR也比VARCHAR更好，因为固定长度的行不容易出现碎片。
谨慎使用BIT类型。当检索BIT（1）的值时，结果是一个包含二进制值0或1的字符串，而不是ASCII码的“0”或“1”。但是，如果在数字上下文中检索该值，则会将BIT字符串转换为数字。
存储通用唯一标识符（UUID）值，则应该删除破折号，或者更好的做法是，使用UNHEX()函数将UUID值转换为16字节的数字，并将其存储在一个BINARY（16）列中。可以使用HEX()函数以十六进制格式检索值。五、高性能索引

五、高新能索引

索引对如下类型的查询有效

全值匹配：全值匹配指的是和索引中的所有列匹配
匹配最左前缀
匹配列前缀:也可以只匹配某一列的值的开头部分
匹配范围值:例如，前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。
精确匹配某一列而范围匹配另外一列
只访问索引的查询：查询只需要访问索引，而无须访问数据行

索引失效

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

建索引的建议

将选择性最高的列放到索引最前列
索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。
如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。
如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。
删除重复和冗余索引：重复索引是指在相同的列上按照相同顺序创建的相同类型的索引
删除永远不使用的索引（查询从未使用的索引select * from sys.schema_unused_indexes）

六、查询性能优化

MySQL执行一个查询的过程：

1. 客户端给服务器发送一条SQL查询语句。
2. 服务器端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
4. 将结果返回给客户端。

慢查询基础：优化数据访问。确认应用程序是否在检索大量且不必要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。确认MySQL服务器层是否在分析大量不需要的数据行。
不是总是取出全部列，不要重复查询相同的数据，可以使用缓存
理想情况下扫描的行数和返回的行数应该是相同的
使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了。
有时候对于一个大查询，我们需要“分而治之”，将大查询切分成小查询，每个查询的功能完全一样，只完成一小部分，每次只返回一小部分查询结果。
可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。
MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。
COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某列的值的数量，也可以统计行数。在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。如果想要知道结果中的行数，应该始终使用COUNT（*）
根据条件统计某一列的写法count(column = ‘xxx’ or NULL)
确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到联接的顺序。当表A和表B用列c联接的时候，如果优化器的联接顺序是B、A，那么就不需要在B表的对应列上建索引。
确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程。
优化LIMIT和OFFSET子句：尽可能地使用索引覆盖扫描，而不是查询所有的行；。如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。
除非你确实需要服务器消除重复的行，否则一定要使用UNION AL。