高性能Mysql

最新推荐文章于 2024-06-25 13:30:00 发布

一剑丶飘香

最新推荐文章于 2024-06-25 13:30:00 发布

阅读量2.4k

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/asas043/article/details/110546959

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

选取书中特别有代表性的点来讲：

一：Mysql逻辑架构

mysql的架构解析：

当一个sql请求从客户端请求过来，先经过连接池，连接池如果有空闲的线程，则可以直接用，如果没有就新创建一个建立连接，如果是查询类的sql，会先检查是否命中缓存，有缓存则直接返回缓存结果给客户端，如果没有命中缓存，则经过解析器解析sql语法，会解析出关键字和非关键字，解析sql是否合法，不合法会直接报错终止线程，合法的话会进入优化器，优化器会为sql生成最优的执行计划，例如选择最合适的索引，生成执行计划后调用存储引擎的API，让存储引擎开始工作，即到文件系统下进行数据的读取和写入，并最终把结果返回给客户端，如果是查询类sql还会把结果缓存下来。

二：事务

事务的ACID特性
- 原子性（atomicity），事务要么全部提交，要么全部回滚
- 一致性（consistency），数据库总是从一个一致性的状态转换到另外一个一致性的状态
- 隔离性（isolation），通常一个事务所做的修改在最终提交以前，对其他事务是不可见的（也有例外，隔离性最低等级未提交读对其他事务是可见的）
- 持久性（durability），一旦事务提交，则其所做的修改就会永久保存到数据库中。
  注：实际应用中，要实现ACID全部特性非常难，一个兼容ACID的数据库系统，需要做很多复杂但可能用户并没有觉察到的工作，才能确保ACID的实现。平时根据你自己业务是否需要事务，来选择合适的存储引擎。innodb支持事务，myisam不支持事务，但大部分场景都是用innodb的，mysql5.5以后的版本都默认innodb引擎
隔离级别
- ACID特性中的隔离性分四个等级，由低到高（级别越高，安全性越高，级别越低，并发能力越好，性能开销低）：
  - READ UNCOMMITTED（未提交读）
    - 将事务设置为未提交读等级
    - 事务A
    - 事务B
    - 结论：事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录，造成脏读现象。未提交读是最低的隔离级别，很少应用到
  - READ COMMITTED（提交读）
    - 将事务设置为提交读等级
    - 事务A
    - 事务B
    - 结论：已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务B更新了数据前后，事务A在两次查询的数据不一致
  - REPEATABLE READ（可重复读）
    - 实验略。可重复读隔离级别解决了脏读和不可重复读问题，它只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题。幻读就是事务A提交事务后再次查询，如果其他事务插入新行，那么会出现新行产生幻读问题。(注意是可能，因为数据库对隔离级别的实现有所差别)。mysql是默认这种隔离级别。
  - SERIALIZABLE（可串行化）
    - 实验略。它通过强制事务串行执行，比如在事务A执行过程中会禁止其他事务插入数据，避免了前面说的幻读的问题。SERIALIZABLE会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。
事务死锁
- 死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。多个事务同时锁定同一个资源时，也会产生死锁。举例：事务A先更新id=1再更新id=2,事务B同时先更新id=2再更新id=1，两个事务互相占有对方资源，然后请求对方资源，产生死锁。

三、数据类型

数据类型选择
- 更小的通常更好。
  - 应该尽量使用可以正确存储数据的最小数据类型。例如能用smallint尽量不用int，因为更小的数据类型通常更快，它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
- 简单就好
  - 简单数据类型的操作通常需要更少的CPU周期，例如不用字符串，而是用更简单的整型存储IP地址
- 尽量避免NULL
  - 当可为NULL的列被索引时，每个索引记录需要一个额外的字节，因此可为NULL的列会使用更多的存储空间。
几种重要的数据类型
- 整型
  - 整数取值范围
  - MySQL可以为整数类型指定宽度，例如INT（11），对于存储和计算来说，INT（1）和INT（5）是相同的，区别在于字段类型加入 zerofill 参数后，值1234在INT(1)显示为1234，在INT(5)显示为01234，即不够宽度在前面补0
- 实数
  - FLOAT和DOUBLE类型支持使用标准的浮点运算进行近似计算。
  - DECIMAL类型用于存储精确的小数。
  - 浮点类型由于取值范围的影响，会导致实际保存与我们期望的不符合，FLOAT类型整数超过6位就会有误差，所以金钱相关的尽量用DECIMAL类型
  - DECIMAL因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL。但在数据量比较大的时候，可以考虑使用BIGINT代替DECIMAL，将需要存储的货币单位根据小数的位数乘以相应的倍数即可
- 字符串类型
  - VARCHAR类型
    - 优点：用于存储可变长字符串，越短的字符串使用越少的空间，VARCHAR节省了存储空间，所以对性能也有帮助。
    - 缺点：VARCHAR需要使用1或2个额外字节记录字符串的长度，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作，会产生碎片
    - 使用场景：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储。
  - CHAR类型
    - 使用场景：CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片。对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR（1）来存储只有Y和N的值，如果采用单字节字符集(5)只需要一个字节，但是VARCHAR（1）却需要两个字节，因为还有一个记录长度的额外字节。
- 日期和时间类型
  - DATETIME和TIMESTAMP的比较
    - TIMESTAMP比DATETIME占空间小。DATETIME是使用8个字节的存储空间，TIMESTAMP是使用4个字节的存储空间。
    - TIMESTAMP的范围比DATETIME小得多。DATETIME能保存大范围的值，从1001年到9999年，精度为秒。TIMESTAMP只能表示从1970年到2038年，但这种范围已经可以满足大部分日常使用。
    - TIMESTAMP显示方式比DATETIME更灵活。DATETIME存什么显示什么，TIMESTAMP存储的是时间戳，显示mysql所在时区的时间，如果储存时的时区和检索时的时区不一样，那么拿出来的数据也不一样。
范式反范式的优点和缺点
- 范式的优点
  - 范式化的更新操作通常比反范式化要快。
    当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据。
    范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。
    很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。
- 范式的缺点
  - 通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效
- 反范式的优点
  - 反范式化的schema因为所有数据都在一张表中，可以很好地避免关联。
    
    如果不需要关联表，则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多，因为这样避免了随机I/O(14)。
    
    单独的表也能使用更有效的索引策略。
- 反范式的缺点
  - 当MySQL需要扫描表字段的索引，对于每一行找到的数据，将需要到表里检查数据是不是符合条件。如果只有一小部分符合条件是效率低下
- 通过是范式化和反范式化两者混用

四、索引

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

B+tree索引
- B-tree是从最简单的二叉树进化到平衡二叉树，再进化到多路平衡查找树，即B-tree，最后进化到B-tree改进版B+tree，innodb采用的就是B+tree
- B-tree的非叶子节点只存储键值信息，这样节省了磁盘块的空间，可以实现更多路，使树的高度更小，加快查找速度
- 所有叶子节点之间都有一个链指针。
- 数据记录都存放在叶子节点中
索引命中情况
- - 全值匹配
    - 全值匹配指的是和索引中的所有列进行匹配
  - 匹配最左前缀
    - 只使用索引的第一列
  - 匹配列前缀
    - 只匹配索引列的值的开头部分
  - 匹配范围值
    - 只使用了索引的第一列进行范围查找
  - 精确匹配某一列并范围匹配另外一列
索引命中限制
- 如果不是按照索引的最左列开始查找，则无法使用索引
- 不能跳过索引中的列
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找
高性能的索引策略
- 独立的列，索引列不能是表达式的一部分，也不能是函数的参数。WHERE actor_id + 1 = 5;是一个表达式
- 选择合适的索引列顺序
  - 以下面的查询为例：
  - 是应该创建一个（staff_id，customer_id）索引还是应该颠倒一下顺序?如下所示，
    
    根据经验法则，应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小。
聚簇索引
- 下图展示了聚簇索引中的记录是如何存放的。注意到，叶子页包含了行的全部数据，但是节点页只包含了索引列。
- 如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。