MySQL事务、索引和SQL优化

最新推荐文章于 2024-04-08 21:36:18 发布

三印

最新推荐文章于 2024-04-08 21:36:18 发布

阅读量371

点赞数 2

分类专栏：数据库学习数据研发文章标签：数据库 Mysql 事务与索引 SQL优化

本文链接：https://blog.csdn.net/zehui6202/article/details/103993950

版权

数据库学习同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

数据研发

6 篇文章 0 订阅

订阅专栏

为完善MySQL基础博文，另外开一片博文，讲一讲事务机制、索引和一些优化技巧。谈到事务，不免需要揪出MySQL中的引擎，常用的引擎有Myisam和Innodb，后者才具有事务机制(Innodb引擎)。并且，在Mysql5.7版本之后，默认引擎是Innodb，通常情况下选Innodb是正确的(这些概念性的东西，应聘的时候都会有所涉及，深浅略有不同)。

关于事务

概念：数据库系统的一个逻辑单元，由有限个操作序列组成，执行完成之后，数据库将由一种状态变化到另外一种状态
特性ACID：
- 原子性(Automicity)：即整个事务是最小的一个执行单元，不可再分
- 一致性(Consistency)：即事务执行前后的状态变化是一致的
- 隔离性(Isolation)：即不同的事务之间不存在影响，是分离的两个事件
- 持久性(Durability)：即事务执行完之后，结果是持久的，哪怕发生当机，结果仍然是事务完成之后的状态
ACID事务特性，能够很好地保证单个事务的数据准确性。但是，如果多个事务共同操作一个数据库时，由于某些原因，就会产生一些问题。如脏读、不可重复读、幻读等等
- 脏读：即一个事务在修改数据库中数据时，但是没有提交。此外，另外一个事务也在访问数据库，使用了这部分数据；由于另一个数据的修改，导致两次读取结果不一致，产生的现象成为脏读(读未提交的隔离级别下发生)
- 不可重复读：第一个事务存在多次读同一数据的情况，然后第二个事务对上述提到的数据进行了修改，导致第一个事务第一次读取与第二次读取的结果不一样，称为不可重复读(读提交隔离级别下发生)
- 幻读：第一个事务存在多次读同一数据的情况，然后第二个事务在上述范围进行了数据插入或者删除，导致第一个事务第一次读取与第二次读取的结果不一样，称为幻读(可重复隔离级别下发生)
- 幻读和不可重复读的区别：不可重复读在于主要原因是修改，而幻读的原因在于新增和删除
克服产生脏读、幻读等问题的方法是提高事务的隔离级别。事务隔离级别有，但是隔离级别越高，会导致锁的范围越大，最后造成锁全表，降低数据库并发处理能力
- 读未提交(隔离级别最低，并行度最高)
- 读提交：克服脏读现象
- 可重复读：克服脏读、幻读
  - 数据可以分为两种读取：一种为快照读、另一种是当前读。mysql默认是快照读
    - 当前读的方式有如下这些
      - select ... lock in share mode
      - select ... for update
      - insert
      - update
      - delete
    - 快照读的方式
      - select * from tablename
    - mysql如何解决幻读问题？mysql针对快照读，在rr隔离级别下通过mvcc(多版本控制)解决幻读问题；针对当前读，采用间隙锁 + 索引锁的方式解决幻读问题(next-key技术)
      - 快照读：mysql会为每一条数据，隐式加上两个字段，一个是创建版本号赋值，另一个是删除版本号赋值。在快照读的状态下，表的数据发生变化即会制作成一个新的版本。select时读取数据的规则为：创建版本号<=当前事务版本号，删除版本号为空或>当前事务版本号。
      - 通过MVCC机制，虽然让数据变得可重复读，但我们读到的数据可能是历史数据，不是数据库最新的数据
      - 而读取数据库最新版本数据的方式，叫当前读 (current read)
      - 当前读：locks由record locks(行锁) 和 gap locks(间隙锁），读取数据的附近记录也加锁，保证能够读取到最新数据，附近数据不被修改
        比如某张表的主键是id，然后需要选择id>3的所有数据
        select * from tablename where id>3
        通过间隙锁机制，会在索引id>3后所有的数据数据上加锁，保证数据的唯一性，解决幻读问题
- 序列化(隔离级别最高，并行度最低)：克服脏读、不可重复读、幻读，但是并行效率低、性能差，一般不太使用

关于索引

数据库的索引，可以看成是图书的目录，目的是提高查询速度，在MySQL中，索引的数据结构是B+Tree、B-Tree，索引的使用是为了提高I/O的访问效率。但是，索引不是越多越好(冗余将导致查询变慢)，而要恰到好处
索引与主键的区别，比如主键与唯一索引的区别(面试问了几次)。这里需要注意的是“主键是一种约束”，并且包含了唯一索引的功能，不允许有空值和重复，而唯一索引不允许重复，但是允许存在空值；其次，一张表只允许一个主键，而可以存在多个唯一索引(根据查询效率设定)
引擎对索引的影响(B+Tree为例)
- Innodb引擎
  - 在树的非叶子节点，存储指向数据的key的值，而myisam引擎则会存储指向数据的指针，这点还是有区别的
  - 在树的叶子节点，存储数据的值；这个存储形式也称之为“聚簇索引”
  - 关于回表操作
    - 假设查询的数据有两列，第一列id是主键，第二列是name，没有索引。那么在select id,name from table where name='test'时
      - 第一步通过普通索引找到 test行的id
      - 第二步基于第一步的id值，走主键索引去查具体的值，这个过程称之为回表，相当于走两边索引
      - 克服的方法是索引覆盖，即在一棵索引树上能够获取所有的列数据，则无需回表
- Myisam引擎
  - 在树的节点，存储的是指向数据key值的地址，通过地址访问，获得最终值
  - 这种存储形式也叫“非聚簇索引”
如何看索引使用被使用
- 使用 explain 查看查询语句分析结果，再去选择优化手段。以下图为例，主要分析以下几个字段
  - type——连接方式，有all、index、range等等，靠右的查询速度更快
  - key——使用的索引
  - rows——扫描表的行数
  - Extra——数据获取方式：Using index表明从索引中获取数据

触发器与存储过程的区别

存储过程完成特定功能的一组sql语句集
触发器是一种特殊类型的存储过程，是否执行取决于事件影响

关于SQL优化

SQL优化，不得不提一下数据是如何从仓库中到展示界面的，具体过程如下：

客户端发送一条查询给服务器
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段
服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询
将结果返回给客户端

如果希望SQL查询更快地返回结果，可以从两个方面解决。一是生成更优的执行计划(主要的优化目的)，二是返回更少的数据给客服端降低数据传输时间（这部分主要看字段的类型设计和业务，不太可控），所以下文主要讲第一个方面的优化

SQL优化涉及的方面非常多，如表结构设计是否合理、查询语句是否合理等
- 表结构设计主要考察以下几点
  - 数仓建模，即数据仓库层级划分；一般可以分为ODS、DWD、DWS和ADS几层
    - ODS是原始数据采集层，可以因为该层的报表数据是最贴近现实场景的数据
    - DWD(detail warehouse)，该层是对采集层数据进行抽取(E)、转换(T)、L(加载)而来，提升数据质量和划分数据域
    - DWS汇总层，基于小口径、单域的明细数据，进行数据汇总、指标统计等报表开发
    - ADS应用层，基于DWS的粒度报表进行二次开发，提供数据产品，如用户画像，对外应用指标等数据
  - 分区表
    - 如果一般为小时级/天级任务，可以根据相应的时间级别建立分区，查询时根据分区范围降低查询压力
    - 根据数据域划分，建立分区，例如视频节目，可以划分为电影、动漫、电视剧等
  - 分库分表
  - 目的：降低单库单表的存储压力，增加数据库的扩展性和吞吐量
    - 分库分表常见的有：单库单表、单库多表、多库多表
      - 单库单表：一个数据库下存储一张表，比较常见，适用于极小数据量情况
      - 单库多表-1：考虑到mysql单表存储数量在500w，为了存储更多的数据和拥有更好的扩展性，一般可以将一个库下的单表拆分，可以垂直拆分(如有主键id字段)，然后根据 id除以3的余数进行分表，(part-table1、part-table2、part-table3)等
      - 单库多表-2：上述是单库多表的水平划分，也有垂直划分。根据数据域的不同，进行分表。但是建议有一个外键，实现垂直表之间的关联
  - 主从表复制
    - 为了提高数据库的吞吐能力，防止在一张表上同时进行读/写，提出了读写分离的方法，即主表负责写数据，从表负责读数据，那这个主从一致是如何实现的呢？
      - 首先在主库中，需要开启二进制的功能，然后针对主库的增、删、改等操作都会被记录在bin-log中，然后有一个二进制日志转中继的过程
      - 其次，从库定时负责从主库中复制中继日志，然后进行解析、执行操作，实现从库与主库的数据一致
      - 最后，上述过程也会涉及到以下几个问题
        1、开启二进制日志功能的主库消耗
        2、中继日志传输、从库解析、操作中继日志的时间消耗，存在从库与主库数据的刷新延时问题
        3、主库崩了之后影响从库的数据一致性
    - 同步复制
      - 即所有从库完成复制操作之后，此次任务完成(复制效率低)
    - 异步复制
      - 主库的操作写入二进制日志后，此次任务完成(数据不一致)
    - 半同步复制
      - 在一主多从的加沟中，有一个从库完成了复制操作，此次任务完成(折中效果)
- SQL语句优化
  - 多用explain分析执行计划，调整查询的方式
  - 少用Select * 语句，采用最小原则，需要什么字段写明什么字段，如select id from table
  - 差异性统计少用distinct、count(distinct(*))，可以使用开窗函数row_number() over (partitioned by column1 order by column2)
  - 常常出现在where 后面的字段，记得加上索引
  - 一张表的数据倘若非常大，最好能设计成分区表，或者能进行拆分；即时间分区或者域分区
  - like、rlike、in 等语句少用，比如select id from table where id in(1,2)，可以改写成select id from table where id = 1 or id = 2
  - 如果遇到多列索引的情况，如（col1，col2，col3）是一个联合索引；那么需要遵循最左匹配要求，where col=, col2=;类似的写法，如果写 where col2=,col3=,则不会走索引
  - 考虑是否开启慢日志查询(慢日志查询本身会进行时间消耗)