数据库随笔（详解事务、索引、SQL调优）

于 2021-11-25 20:06:10 发布
阅读量460 收藏
点赞数 2
分类专栏： 技术分享 文章标签： sql 数据库 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41879936/article/details/121546031
版权

一、事务

  • 事务就是一组原子性的SQL查询，或者说是一个独立的单元。事务内的语句，要么全部执行成功，要么全部执行失败。
  • 操作：
    • 开启事务：start transaction
    • 回滚：rollback
    • 提交事务：commit
    • 实例：account表
idnamebalance
1张三1000
2李四1000
/*
业务需求：张三给李四转账500元。
完成转账步骤：
①张三账户余额-500
update account set balance=balance-500 where name='张三';
②李四账户余额+500
update account set balance=balance+500 where name='李四';
若不使用事务，直接进行操作。可能会出现的问题：
	张三账户余额减了500，但李四账户上500并未到账（即语句①执行成功，但语句②执行失败。）
*/

/*
为解决此问题，我们可以使用事务,将①②语句放在一个事务中执行
*/	
--开启事务
start transaction;
--张三账户余额-500
update account set balance=balance-500 where name='张三';
--李四账户余额+500
update account set balance=balance+500 where name='李四';
--发现出问题了，回滚事务
rollback;
--发现执行没有问题，提交事务
commit;

ACID（事务四大特征）

    • 原子性（A atomicity）
      • 一个事务必须被视为一个不可分割的最小单元，整个事务中所有的操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性。
      • 个人理解：未开启事务时，每一条SQL语句为一个最小单位；开启事务后，事务中的多条SQL语句被视为一个最小单位。
    • 一致性（C consistency）
      • 数据库总是从一个一致性的状态转换到另外一个一致性的状态。
      • 即：如上例，张三李四转账前后钱的总和都得是2000，不可能凭空多也不可能凭空少（事务操作前后数据总量不变）。
    • 隔离性（I isolation）
      • 通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的。（多个事务之间，相互独立）
      • 即：如上例，语句①执行完后，再开启一个事务，在该事务中查询张三账户余额，查到的余额还是1000.
    • 持久性（D durability）
      • 一旦事务提交，则其所做的修改就会永久保存到数据库中。这⾥里里持久性是个有点模糊的概念，实际上持久性也分很多不不同的级别。

事务提交的两种方式

    • 自动提交（MySQL默认是自动提交的）
      • 一条DML（增、删、改）语句会自动提交一次事务。
    • 手动提交（Oracle默认是手动提交的）
      • 需要实现开启事务（输入命令：start transaction），再提交(输入命令：commit)

修改事务的默认提交方式

    • 查看事务的默认提交方式
--查看事务的默认提交方式
select @@autocommit;  -- 1代表自动提交，0代表手动提交
--修改默认提交方式
set @@autocommit=0;

事务的隔离级别（了解）

  1. 概念：
    1. 多个事务之间是相互独立的。但是如果多个事务，操作同一批数据，则会引发一些问题，设置不同的隔离级别可以解决这些问题
  2. 存在问题
    1. 脏读：一个事务读取到另一个事务中没有提交的数据。
    2. 不可重复读（虚读）：在同一个事务中，两次读取到的数据不一样。
    3. 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，但第一个事务查询不到自己的修改。
  3. 隔离级别（小级别->大级别）
  • read uncommitted：读未提交
    • 可能出现的问题：脏读、虚读、幻读
  • read committed：读已提交（Oracle数据库默认隔离级别）
    • 可能出现的问题：虚读、幻读
  • repeatable read：可重复读（MySQL数据库默认隔离级别）
    • 可能出现的问题：幻读
  • serializable：串行化（一般在项目开发中基本不用该级别）
    • 可以解决所有问题
  • 注：隔离级别从小到大安全性越来越高，但效率越来越低
隔离级别脏读不可重复读幻读
读未提交可能可能可能
读已提交不可能可能可能
可重复读不可能不可能可能
串行化不可能不可能不可能
  • 数据库查询隔离级别
    sql select @@tx_isolation;
  • 数据库设置隔离级别
    sql set global（或session） transaction isolation level 级别字符串;

二、索引

  • 认识索引

索引就好比书的目录页与书的正文内容之间的关系，为了方便查找书中的内容，通过对内容建立索引形成⽬目录。因此，首先你要明白的一点就是，索引它也是一个文件，它是要占据物理空间。

  1. 索引是按照特定的数据结构把数据表中的数据放在索引文件中，以便于快速查找。
  2. 索引存在于磁盘中，会占据物理空间。
  • 索引类型
  1. FULLTEXT
    全文索引，目前只有MylSAM引擎支持。。其可以在CREATE TABLE ，ALTERTABLE ，CREATE INDEX 使用，不过⽬目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引。全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE“%word%"这类针对文本的模糊查询效率较低的问题。
  2. HASH
    由于HASH的唯一（几乎100%的唯一）及类似键值对的形式，很适合作为索引。HASH索引可以一次定位，不需要像树形索引那样逐层查找,因此具有极高的效率。但是，这种高效是有条件的，即只在“=”和“in”条件下高效，对于范围查询、排序及组合索引仍然效率不高。
  3. BTREE
    BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中（二叉树），每次查询都是从树的入口root开始，依次遍历node，获取leaf。这是MySQL里默认和最常用的索引类型。
  • 索引种类
    • 主键索引：MySQL中主键必须唯一且不能有空值，因此在主键上的索引也是唯一索引。一个表上的唯一索引可以有多个，但主键只有一个。
    • 唯一索引：唯一索引顾名思义，索引必须唯一，唯一索引中允许有控制出现。
    • 普通索引
    • 组合索引 INDEX(A,B,C)
  • 索引操作
    • 创建索引
--创建普通索引
create index index_name on table_name(col_name);
--创建唯一索引
create unique index index_name on table_name(col_name);
--创建普通组合索引
create index index_name on table_name(col_name_1,col_name_2);
--创建唯一组合索引
create unique index index_name on table_name(col_name_1,col_name_2);

通过修改表结构创建索引

alter table table_name add index index_name(col_name);
  • 创建表时直接指定索引
creat table table_name(
    id int not null,     
    col_name varchar(16) not null,     
    index index_name(col_name) 
);
  • 删除索drop index index_name on table_name;

存储引擎中索引的实现

​ 首先需要明白索引和存储引擎之间有何关系

  1. 在MySQL中，索引是在存储引擎中实现的。
  2. 不同的存储引擎可能支持不同的索引类型。
  3. 不同的存储引擎对同一种索引类型可能有不同的实现方式

InnoDB存储引擎

  • B+树
    • 特点如上图
    • B+树索引类型（都是从根节点开始查找）
      • 聚簇索引：通过主键进行索引，叶子节点存放具体数据。
      • 非聚簇索引：通过非主键进行索引查询。叶子节点存聚簇索引中的主键id。使用非聚簇索引时，先查到id，然后回表去B+树根据主键id再查具体数据。

  • 和B树的区别
      • B树真实数据存储位置：叶子节点或叶子节点的父节点
      • B+树真实数据存储位置：数据只存放在叶子节点中。
      • B+树结构可以将树的层数（高度）控制在3层。而B树就不好说了...在做范围取值时（即：如要查询年龄在18岁到30岁之间的人），效率不如B+树高。因为B+树的叶子节点之间是一个双向链表。
    • 最左匹配原则，举个例子：
      • 若使用（a,b,c）这组字段：
        • 能够用到的索引：
          • a
          • a,b
          • a,b,c
        • 不能用的索引：b,c；b；c；
        • 即,必须从根节点开始向下查找.
  • MyISAM索引实现
  • 结构也使用B+树，但是叶子节点不存放具体数据，存放的是真实存放的物理空间地址。而真正的数据通过hash散列存放在别的物理空间中。

三、慢SQL

MySQL执行原理

  • 解析：词法解析->语法解析->逻辑计划->查询优化->物理执行计划
    检查是否存在可用查询缓存结果，如果没有或者缓存失效，则调用mysql_execute_command执行。
  • 执行：检查用户、表权限->表上加共享数据->取数据到query cache->取消共享读锁

慢查询

  • 查询MySQL的操作信息show status 显示全部MySQL操作信息
show status like "com_delete%";--获得MySQL的删除次数
show status like "com_select%";--获得MySQL的查询次数
show status like "uptime";--获得MySQL服务器运行时间
show status like "connections";--获得MySQL连接次数
show [session|global] status like ... --如果不写[session|global],默认是session会话，只取出当前窗口的执行，如果想看所有（即MySQL从启动到现在，则应该使用global）
show variables like "%slow%";--是否开启慢查询
show status like "%slow%";--查询慢查询SQL状况
show variables like "long_query_time";--慢查询时间
  • 慢查询开启设置
mysql>show variables like 'long_query_time';--默认情况下，MySQL认为10秒才是一个慢查询
mysql>set long_query_time=1;--修改慢查询时间，只能当前会话有效
mysql>set global slow_query_log='ON';--启用慢查询，加上global,不然会报错
  • explain sql语句
explain select * from 表名;

  • 概要描述：
    • Extra:执行情况的描述和说明
    • filtered:按表条件过滤的行百分比
    • rows:扫描出的行数(估算的行数)
    • ref:列与索引的比较
    • key_len:索引字段的长度
    • key:表示实际使用的索引
    • possible_keys:表示查询时，可能使用的索引
    • type:表示表的连接类型
    • partitions:匹配的分区
    • table:输出结果集的表
    • select_type:表示查询的类型
    • id:选择标识符

四、SQL调优

1、使用join代替子查询（生产环境中应尽量避免使用子查询）

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');
  • 子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
  • 该优化只针对select有效。对update/delete子查询无效。

2、避免函数索引

select * from t where year(d) >=2016;
--由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。应改为：
select * from t where d >= '2016-01-01';

3、用in来代替or

--低效查询    
select * from t where loc_id=10 or loc_id=20 or loc_id=30;    
--高效查询    
select * from t where loc_id in (10,20,30);

4、like双百分号无法使用到索引

--like双百分号无法使用到索引
select * from t where name like '%de%';
--可以使用到索引
select * from t where name like 'de%';

5、 读取适当的记录limit m,n（分页不要跨度太大）
6、分组统计可以禁止排序

默认情况下，MySQL对所有GROUP BY col1，col2...的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

--低效查询    
select goods_id,count(*) from t group by goods_id;    
--高效查询    
select goods_id,count(*) from t group by goods_id order by null;

7、禁止不必要的order by排序

--低效查询  
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDERBY u.create_time DESC;     
--高效查询    
SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;

8、正确使用组合索引 index（a,b,c）。a 或 a,b 或 a,b,c

  • 若上述8种调优方案调优结果都不理想
    • 看表的数量级是否合理
    • 软件解决后（上述8种方案可以解决所有软件层面上的问题），看硬件。磁盘、I/O、内存啥的该换就换。

五、分库分表

  • 分类
    • 垂直切分
      • 垂直分库

垂直分库就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。如图：

  • 垂直分表

垂直分表是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。在字段很多的情况下（例如一个大表有100多个字段），通过"大表拆小表"，更便于开发与维护，也能避免跨页问题，MySQL底层是通过数据页存储的，一条记录占用空间过大会导致跨页，造成额外的性能开销。另外数据库以行为单位将数据加载到内存中，这样表中字段长度较短且访问频率较高，内存能加载更多的数据，命中率更高，减少了磁盘IO，从而提升了了数据库性能。

    • 优点
      • 解决业务系统层面的耦合，业务清晰, 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等。
      • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈。
    • 缺点
      • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度。
      • 分布式事务处理复杂。
      • 依然存在单表数据量过大的问题（需要水平切分）。

  • 水平切分
    • 当一个应用难以再细粒度的垂直切分，或切分后数据量行数巨大，存在单库读写、存储性能瓶颈，这时候就需要进行水平切分了。水平切分为库内分表和分库分表，是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。如图所示：库内分表只解决了了单一表数据量过大的问题，但没有将表分布到不同机器的库上，因此对于减轻MySQL数据库的压力来说，帮助不是很⼤，大家还是竞争同一个物理机的CPU、内存、网络IO，最好通过分库分表来解决。
    • 优点
      • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。
      • 应用端改造较小，不需要拆分业务模块。
    • 缺点
      • 跨分片的事务一致性难以保证跨库的join关联查询性能较差。
      • 数据多次扩展难度和维护量极大。
    • 分片规则
      • 根据数值范围
        • 按照时间区间或ID区间来切分。例如：按日期将不同⽉月甚⾄至是日的数据分散到不同的库中；将userId为1~9999的记录分到第一个库，10000~20000的分到第二个库，以此类推。某种意义上，某些系统中使用的"冷热数据分离"，将一些使用较少的历史数据迁移到其他库中，业务功能上只提供热点数据的查询，也是类似的实践。
      • 优点
        • 单表大小可控
        • 天然便于水平扩展，后期如果想对整个分片集群扩容时，只需要添加节点即可，无需对其他分片的数据进行迁移 。
        • 使用分片字段进行范围查找时，连续分片可快速定位分片进行快速查询，有效避免跨分片查询的问题。
      • 缺点
        • 热点数据成为性能瓶颈。连续分片可能存在数据热点，例如按时间字段分片，有些分片存储最近时间段内的数据，可能会被频繁的读写，⽽而有些分片存储的历史数据，则很少被查询。

    • 根据数值取模
      • 一般采用hash取模mod的切分方式，例如：将 Customer 表根据 cusno 字段切分到4个库中，余数为0的放到第一个库，余数为1的放到第⼆个库，以此类推。这样同一个⽤用户的数据会分散到同一个库中，如果查询条件带有cusno字段，则可明确定位到相应库去查询。
      • 优点
        • 数据分片相对比较均匀，不容易出现热点和并发访问的瓶颈。
      • 缺点
        • 后期分片集群扩容时，需要迁移旧的数据（使用⼀一致性hash算法能较好的避免这个问题）。
        • 容易面临跨分片查询的复杂问题。比如上例中，如果频繁用到的查询条件中不带cusno时，将会导致无法定位数据库，从而需要同时向4个库发起查询，再在内存中合并数据，取最小集返回给应用，分库反而成为拖累。
    • 分库分表带来的问题
      • 事务一致性问题
      • 跨节点关联查询join问题
        • 解决该问题的一些方法
          • 全局表
            全局表，也可看做是"数据字典表"，就是系统中所有模块都可能依赖的一些表，为了避免跨库join查询，可以将这类表在每个数据库中都保存一份。这些数据通常很少会进行修改，所以也不担心一致性的问题。
          • 字段冗余
            ​ 一种典型的反范式设计，利用空间换时间，为了性能而避免join查询。例如：订单表保存userId时候，也将userName冗余保存一份，这样查询订单详情时就不需要再去查询"买家user表"了。
          • 数据组装
            在系统层面，分两次查询，第一次查询的结果集中找出关联数据id，然后根据id发起第二次请求得到关联数据。最后将获得到的数据进行字段拼装。
        • 跨节点分页、排序、函数
        • 全局主键避重问题
        • 数据迁移、扩容问题

实现分库分表，一般使用Mycat。自己写的话以下四步：

    • ​分片算法
    • SQL解析
    • SQL路由
    • 数据聚合
Flynn_10086
关注
  • 2
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值