首页 > 数据库 >MySQL篇

MySQL篇

时间:2024-07-25 15:52:19浏览次数:15  
标签:事务 查询 索引 MySQL 失效 日志 数据

一、定位慢查询

定位慢查询很简单,主要有以下方式:

  1. 通过运维工具分析
  2. 开启mysql慢日志

在mysql的配置文件/etc/my.cnf中配置并开启慢日志功能

# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
  • 分析某一条具体的sql语句
EXPLAIN/SELECT   字段列表   FROM   表名   WHERE  条件 ;

执行结果:

image-20240514174740084

通过key和key_len检查是否命中了索引(索引本身存在是否有失效的情况)

通过type字段查看sql是否有进一步的优化空间,是否存在全索引扫描或全盘扫描

通过extra建议判断,是否出现了回表的情况,如果出现了,可以尝试添加索引或修改返回字段来修复

索引

  • 索引是帮助MySQL高效查找数据的满足特定查找算法的数据结构
  • 索引的底层数据结构是B+树

聚集索引和二级索引

分类 含义 特点
聚集索引(Clustered Index) 将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据 必须有,而且只有一个
二级索引(Secondary Index) 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 可以存在多个

聚集索引选取规则:

  • 当存在主键唯一索引时,优先使用主键或第一个唯一索引作为聚集索引
  • 如果主键和唯一索引都没有,InnoDB会自动生成一个rowid作为隐藏的聚集索引

回表查询:

通过二级索引查找到对应主键值,然后再根据主键值查询整行数据,这个过程叫做回表

覆盖索引:

指查询使用了索引,并且所有需返回的字段全部能在该索引中直接找到

联合索引:

指由多个字段共同构成的索引

索引失效情况

最左前缀匹配法则:

创建了联合索引(a,b,c)后,只有当前面的字段相同时,才会去比较后面的字段,优先按照联合索引最左字段进行匹配

索引失效情况:

① 如果不遵循最左匹配原则,则可能会导致索引失效

  • where b='' c='': 此时索引全部失效
  • where a='' c='':此时仅有索引a生效

② 范围查询右边的列,不能使用索引

  • where a='' b>'': 此时c索引失效

③ 在索引字段上进行运算,索引也会失效

④ 字符串不加单引号,索引也会失效(造成类型转换)

⑤ 模糊查询%xxx模式,也会导致索引失效

SQL优化经验

1.表的设计优化,数据类型的选择

2.索引优化,索引创建原则

3.sql语句优化,避免索引失效,避免使用select * ….

4.主从复制、读写分离,不让数据的写入,影响读操作

5.分库分表

事务

  • 并发事务可能出现的问题
  1. 脏读
  2. 不可重复读
  3. 幻读
  • 解决方案 —— 事务的隔离级别
  1. 读未提交
  2. 读已提交:只能解决脏读
  3. 可重复读:只能解决脏读不可重复读
  4. 串行化:全部解决

MySQL日志

  • redo log:记录的是数据页的物理变化,当服务器宕机或其他情况导致数据不一致时,可用来同步数据,保证了事务的持久性
  • undo log:记录的是逻辑日志,当事务回滚时,通过逆操作来恢复数据,保证了事务的原子性一致性

保证事务的隔离性

  1. 排他锁:用于数据修改操作,确保不会同时同一资源进行多重更新
  2. MVCC(多版本并发控制):指维护一个数据的多个版本,使得读写操作没有冲突
  • 隐式字段

①trx_id(事务id),记录每一次操作的事务id,是自增的

②roll_pointer(回滚指针),指向上一个版本的事务版本记录地址

  • undo log

①回滚日志,存储老版本数据

②版本链:多个事务并行操作某一行记录,记录不同事务修改数据的版本,通过roll_pointer指针形成一个链表

  • readview

    • 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据

    • 不同的隔离级别快照读是不一样的,最终的访问的结果不一样

      RC :每一次执行快照读时生成ReadView

      RR:仅在事务中第一次执行快照读时生成ReadView,后续复用

数据库主从同步原理

①主库在事务提交时,会把数据变更记录在二进制日志文件 Binlog 中。

②从库读取主库的二进制日志文件 Binlog ,写入到从库的中继日志 Relay Log 。

③从库重做中继日志中的事件,将改变反映它自己的数据

分库分表

  • 水平分库
  • 水平分表
  • 垂直分库
  • 垂直分表

标签:事务,查询,索引,MySQL,失效,日志,数据
From: https://www.cnblogs.com/six-years/p/18323353

相关文章

  • mysql常用语法
    MySQL约束注释--单行注释/*多行注释*/#井号注释MySQL命令执行顺序FROM>ON>JOINWHERE>GROUPBY>HAVING+聚合函数>SELECT>ORDERBY>LIMIT数据库设计——DDLshowdatabases; --查看当前所有的数据库use[数据库名]; --打开指定的数据库showtab......
  • Mysql常用函数
    1、FIND_IN_SETFIND_IN_SET()是一个MySQL中的字符串函数,用于在一个由逗号分隔的字符串列表中查找指定字符串的位置。如果找到了指定的字符串,FIND_IN_SET()将返回该字符串在列表中的位置(索引值)。如果没有找到,它将返回0。其语法如下:FIND_IN_SET(str,strlist)str:......
  • Mysql 函数 (进阶介绍 八)
    目录MySQLCURTIME()函数定义和用法语法实例MySQLDATE()函数定义和用法语法实例MySQLEXTRACT()函数定义和用法语法实例MySQL字符串连接CONCAT()函数使用方法:MySQL字符串截取SUBSTRING()函数 使用方法:MySQL数学函数使用方法:mysqlsubstr()函数截......
  • 一文了解MySQL索引机制
    接触MySQL数据库的小伙伴一定避不开索引,索引的出现是为了提高数据查询的效率,就像书的目录一样。某一个SQL查询比较慢,你第一时间想到的就是“给某个字段加个索引吧”,那么索引是什么?是如何工作的呢?一起静下心来,耐心看完这篇文章吧,干货不啰嗦,相信你一定会有所收获。一、索引模型模......
  • Mysql中修改新创建用户的密码
    创建新用户时,新用户没有权限,所以自己无法修改成简单的密码(1)可以通过root用户给权限,让新用户自己修改:1.先进入root用户,mysql-uroot-p1234562.给新用户权限:grantallprivilegeson.to'新用户名'@'localhost'withgrantoption;(其中withgrantoption是让新用户......
  • SpringBoot + MyBatis 实现 MySQL 主从复制动态数据源切换
    概述在项目中的某些场景中,需要对数据库进行一些优化。常用的有如下的实现方法:读写分离、引入缓存技术、主从复制、分库分表等。今天来简单介绍一些如何在程序中实现动态切换数据源,可能某台服务器性能比较好,让流量多的方法执行切换到此数据源去操作等等。当然这种思想也可以扩展......
  • MySql数据表创建并新增数据
    --------------------------------Tablestructureforbs002h------------------------------DROPTABLEIFEXISTS`bs002h`;CREATETABLE`bs002h`(`id`bigint(20)NOTNULLAUTO_INCREMENT,`cr_time`datetime(6)NOTNULL,`rg_time`datetime(6)NOT......
  • 记一次mysql生产误删表抢救操作
    背景数据库的操作必须要采取一系列的措施来规范流程和管理。然而,不同客户群体管理水平不一,删库跑路也并不是茶余饭后的笑话,它真真实实的,突如其来的就发生在你身边。接项目经理前线打来电话,某医院突发生产故障,通过业务日志得知,数据库缺表,需要紧急恢复数据,恢复生产。思路确认环......
  • MySQL配置开发文章:学习路线、使用案例及代码示例
    引言MySQL是一个广泛使用的开源关系型数据库管理系统,由于其性能高、成本低、可靠性强,成为了许多开发者的首选工具,本文将介绍MySQL的基本配置、学习路线、使用案例及代码示例,帮助你更好地掌握MySQL的使用MySQL配置开发教程学习路线基础知识:了解关系型数据库的基本概念,熟......
  • 整合Apache Hudi+Mysql+FlinkCDC2.1+CDH6.3.0
    一、环境准备1.环境准备:flink1.13.1+hudi0.10+hive2.1.1+cdh6.3.0+mysql5.7+flinkcdc2.1+flinkweb平台二.编译hudi(这个编译是以前的一个测试版本,编译大同小异)1.使用git命令下载hudi0.10的代码steven@wangyuxiangdeMacBook-Pro~gitclonehttps://github.com/ap......