高性能Mysql

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

一:Mysql逻辑架构

 

mysql的架构解析:

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

二:事务

  1. 事务的ACID特性
    • 原子性(atomicity),事务要么全部提交,要么全部回滚
    • 一致性(consistency),数据库总是从一个一致性的状态转换到另外一个一致性的状态
    • 隔离性(isolation),通常一个事务所做的修改在最终提交以前,对其他事务是不可见的(也有例外,隔离性最低等级未提交读对其他事务是可见的)
    • 持久性(durability),一旦事务提交,则其所做的修改就会永久保存到数据库中。
      注:实际应用中,要实现ACID全部特性非常难,一个兼容ACID的数据库系统,需要做很多复杂但可能用户并没有觉察到的工作,才能确保ACID的实现。平时根据你自己业务是否需要事务,来选择合适的存储引擎。innodb支持事务,myisam不支持事务,但大部分场景都是用innodb的,mysql5.5以后的版本都默认innodb引擎
  2. 隔离级别
    • ACID特性中的隔离性分四个等级,由低到高(级别越高,安全性越高,级别越低,并发能力越好,性能开销低):
      • READ UNCOMMITTED(未提交读)
        • 将事务设置为未提交读等级
        • 事务A
        • 事务B
        • 结论:事务B更新了一条记录,但是没有提交,此时事务A可以查询出未提交记录,造成脏读现象。未提交读是最低的隔离级别,很少应用到
      • READ COMMITTED(提交读)
        • 将事务设置为提交读等级
        • 事务A
        • 事务B
        • 结论:已提交读隔离级别解决了脏读的问题,但是出现了不可重复读的问题,即事务B更新了数据前后,事务A在两次查询的数据不一致

      • REPEATABLE READ(可重复读)

        • 实验略。可重复读隔离级别解决了脏读和不可重复读问题,它只允许读取已提交记录,而且在一个事务两次读取一个记录期间,其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如,当一个事务可以找到由一个已提交事务更新的记录,但是可能产生幻读问题。幻读就是事务A提交事务后再次查询,如果其他事务插入新行,那么会出现新行产生幻读问题。(注意是可能,因为数据库对隔离级别的实现有所差别)。mysql是默认这种隔离级别。

      • SERIALIZABLE(可串行化)

        • 实验略。它通过强制事务串行执行,比如在事务A执行过程中会禁止其他事务插入数据,避免了前面说的幻读的问题。SERIALIZABLE会在读取的每一行数据上都加锁,所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别,只有在非常需要确保数据的一致性而且可以接受没有并发的情况下,才考虑采用该级别。
  3. 事务死锁
    • 死锁是指两个或者多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。多个事务同时锁定同一个资源时,也会产生死锁。举例:事务A先更新id=1再更新id=2,事务B同时先更新id=2再更新id=1,两个事务互相占有对方资源,然后请求对方资源,产生死锁。

三、数据类型

  1. 数据类型选择
    • 更小的通常更好。

      • 应该尽量使用可以正确存储数据的最小数据类型。例如能用smallint尽量不用int,因为更小的数据类型通常更快,它们占用更少的磁盘、内存和CPU缓存,并且处理时需要的CPU周期也更少。

    • 简单就好
      • 简单数据类型的操作通常需要更少的CPU周期,例如不用字符串,而是用更简单的整型存储IP地址

    • 尽量避免NULL

      • 当可为NULL的列被索引时,每个索引记录需要一个额外的字节,因此可为NULL的列会使用更多的存储空间。

  2. 几种重要的数据类型
    • 整型

      • 整数取值范围
      • 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所在时区的时间,如果储存时的时区和检索时的时区不一样,那么拿出来的数据也不一样。
  3. 范式反范式的优点和缺点
    • 范式的优点

      • 范式化的更新操作通常比反范式化要快。
        当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
        范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
        很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。

    • 范式的缺点
      • 通常需要关联。稍微复杂一些的查询语句在符合范式的schema上都可能需要至少一次关联,也许更多。这不但代价昂贵,也可能使一些索引策略无效

    • 反范式的优点
      • 反范式化的schema因为所有数据都在一张表中,可以很好地避免关联。

        如果不需要关联表,则对大部分查询最差的情况——即使表没有使用索引——是全表扫描。当数据比内存大时这可能比关联要快得多,因为这样避免了随机I/O(14)。

        单独的表也能使用更有效的索引策略。

    • 反范式的缺点
      • 当MySQL需要扫描表字段的索引,对于每一行找到的数据,将需要到表里检查数据是不是符合条件。如果只有一小部分符合条件是效率低下

    • 通过是范式化和反范式化两者混用

四、索引

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

  1. B+tree索引
    • B-tree是从最简单的二叉树进化到平衡二叉树,再进化到多路平衡查找树,即B-tree,最后进化到B-tree改进版B+tree,innodb采用的就是B+tree
    • 索引
    • B-tree的非叶子节点只存储键值信息,这样节省了磁盘块的空间,可以实现更多路,使树的高度更小,加快查找速度
    • 所有叶子节点之间都有一个链指针。
    • 数据记录都存放在叶子节点中
  2. 索引命中情况
      • 全值匹配
        • 全值匹配指的是和索引中的所有列进行匹配

      • 匹配最左前缀

        • 只使用索引的第一列

      • 匹配列前缀
        • 只匹配索引列的值的开头部分

      • 匹配范围值
        • 只使用了索引的第一列进行范围查找

      • 精确匹配某一列并范围匹配另外一列

  3. 索引命中限制
    • 如果不是按照索引的最左列开始查找,则无法使用索引

    • 不能跳过索引中的列

    • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找

  4. 高性能的索引策略

    • 独立的列,索引列不能是表达式的一部分,也不能是函数的参数。WHERE actor_id + 1 = 5;是一个表达式

    • 选择合适的索引列顺序

      • 以下面的查询为例:

      • 是应该创建一个(staff_id,customer_id)索引还是应该颠倒一下顺序?如下所示,

        根据经验法则,应该将索引列customer_id放到前面,因为对应条件值的customer_id数量更小。

      •  

  5. 聚簇索引
    • 下图展示了聚簇索引中的记录是如何存放的。注意到,叶子页包含了行的全部数据,但是节点页只包含了索引列。

    • 如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相距甚远。

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值