Mysql知识梳理

这篇具有很好参考价值的文章主要介绍了Mysql知识梳理。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

索引

索引分类

mysql有普通索引、空间索引、主键索引、唯一索引、组合索引和全文索引。

  • 普通索引:使用字段关键字建立的索引,主要目的是提高查询速度。
  • 空间索引:前只有MyISAM引擎支持,用于空间类型的字段,不允许为空。
  • 唯一索引:索引列中的值必须是唯一的,但允许为空值。
  • 主键索引:是一种特殊的唯一索引,不允许有空值
  • 组合索引:在表中的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用,遵循最左前缀集合原则。
  • 全文索引:一种特殊类型的索引,主要用于全文搜索。

索引未命中的原因

  1. 没有查询条件,或查询条件中没有索引
  2. 条件中有or,并且不是所有的条件都有索引
  3. like左匹配或者int字段
  4. 索引列是字符串,但是sql中未加引号
  5. 条件中在索引列使用函数
  6. 采用not in/not exist查询
  7. B-tree索引 is null不走索引,is not null才走索引
  8. 联合索引不满足最左原则

性能调优命令Explain

explain用于解析sql的执行计划,执行计划是MySQL为了执行查询语句而制定的优化方案,包括数据的读取顺序、表的访问方式、索引的使用情况等信息。
以下是使用EXPLAIN指令的基本语法:

EXPLAIN SELECT column1, column2 FROM table_name WHERE condition;

使用EXPLAIN指令后,MySQL会返回一个表格,其中包含以下信息:

  • id:查询的标识符,id越大的越先执行。
  • select_type:查询的类型,如SIMPLE、PRIMARY、SUBQUERY等。
  • table:被查询的表名。
  • partitions:被查询的分区信息。
  • type:访问表的方式,如ALL、INDEX、RANGE等。
    – ALL: 全表扫描;
    – INDEX: 索引全扫描。
    – range: 范围扫描。
    – ref: 使用非唯一索引,或唯一索引的前缀扫描,返回匹配某个单独值得记录行。
    – eq_ref: 多表连接中使用,主键或唯一建作为关联条件。
    – const/system: 单表中最多有一个匹配行。主要用于比较primary key 或unique索引,因为数据都是唯一的,所以性能最优。
    – null: 不用访问表或索引,直接就能得到结果。
  • possible_keys:可能使用的索引。
  • key:实际使用的索引。
  • key_len:使用的索引长度。
  • ref:用于比较的值。
  • rows:MySQL估计需要读取的行数,数值越大越不好,说明没用好索引。
  • filtered:MySQL估计返回的行数占总行数的比例。
  • Extra:附加信息,如使用了临时表、文件排序等。

回表

回表指当查询条件(where条件和返回列)不能被索引索引锁覆盖时,需要访问索引中没有的数据,需要通过回表技术访问数据所在页,获取具体得数据。
与回表相对应的是覆盖查询,即查询条件能够被索引所覆盖,不需要回表查询数据,这样查询效率会更高。因此,在设计数据库时,需要根据具体的情况选择合适的索引策略,以提高查询性能。

mysql性能优化

  1. 优化sql
    1.1 使用explain命令解析sql执行计划
    1.2 尽量命中索引
    1.3 合理设计索引,不是越多越好
  2. 优化表结构
    2.1 经常查询的表尽量避免过大。
    2.2 多余常用的字段尽量冗余,避免大表进行联合查询。
  3. 分库分表进行表瘦身
    3.1 水平分库:以字段为依据,将一个库中的数据进行拆分到多个库中。比如按年份进行分库。
    3.2 水平分表:以字段为依据,将一个表中的数据进行才分到多张表中。
    3.3 垂直分库:以表为依据,按业务将表分到不同的库中。
    3.4 垂直分表:以字段为依据,按字段中的活跃度将表中的字段拆分到不同的表,比如主表和扩展表。

事务

四大特性

事务的四大特性ACID,ACID是原子性(Atomicity [ˌætəˈmɪsəti])、一致性(Consistency [kənˈsɪstənsi])、隔离性(Isolation [ˌaɪsəˈleɪʃn])、持久性(Durability [dərəˈbɪlɪti])的缩写,是数据库事务的基本属性,用于确保事务的可靠性和一致性。

  • 原子性:事务是一个原子操作单元,事务的执行要么全部成功,要么全部失败回滚。
    – 由undolog实现
  • 一致性:事务必须保证数据库从一个状态改变为另一个状态,并保持数据的一致性和完整性。
    – 由原子性、隔离性、持久性共同实现
  • 隔离性:多个事务并发执行时,一个事务的执行不应影响其他事务的执行。
    – 锁+MVCC
  • 持久性:一旦事务提交,对数据库的修改是永久的,即使系统崩溃或故障也能保持。
    – 由redolog实现

事务隔离级别

MySQL数据库的事务隔离级别有四个,分别是:读未提交(Read Uncommitted)、读提交(Read Committed)、可重复读(Repeatable Read)和串行化(Serializable)。

  • 读未提交:事务中的修改,即使未提交,对其他事务也是可见的。
  • 读提交:事务只能看见已经提交的数据修改,会产生不可重复读。
  • 可重复读:同一事务中多次读取数据的结果是一致的,但是并发的事务可能会对数据产生影响,会导致导致 幻读。
  • 串行化:最严格的事务隔离级别,通过锁机制实现,确保事务串行化顺序执行,能避免幻读,但是效率低下。

mysql默认的事务隔离级别是可重复读(Repeatable Read)

幻读

幻读的本质,如果事务中都是快照读,那么不会产生幻读,但是快照读和当前读一起使用的时候就会产生幻读。

MySQL中,当前读和快照读都是读取数据的方式,具体含义如下:

  • 当前读:也称锁定读(locking read),通过对读取到的数据(索引记录)加锁来保证数据一致性,当前读会对所有扫描到的索引记录进行加锁,无论该记录是否满足WHERE条件都会被加锁1。
  • 快照读:MySQL使用MVCC(Multiversion Concurrency Control)机制来保证被读取到数据的一致性,读取数据时不需要对数据进行加锁,且快照读不会被其他事物阻塞1。

在读提交和可重复读两种事务隔离级别下,普通的SELECT操作使用"快照读",不会对数据加锁,也不会被事务阻塞。

设置事务隔离级别

在MySQL中,可以使用以下SQL语句设置事务的隔离级别:

SET TRANSACTION ISOLATION LEVEL <isolation_level>;

存储引擎

mysql主要的存储引擎是MyISAM和InnoDB,默认使用的是InnoDB引擎。
使用以下命令查询当前使用的存储引擎:

SHOW VARIABLES LIKE 'storage_engine';

使用以下命令查询所有存储引擎:

SHOW ENGINES;
  • MyISAM:不支持事务、不支持外键,索引采用的是非聚簇索引。
  • InnoDB:提供了具有提交、回滚等事务功能,支持自动增长列,外键等功能。采用聚簇索引,索引和数据是存储在同一个文件下。

聚簇索引和非聚簇索引

聚簇索引

  1. 数据挂载在主索引的叶子节点上。
  2. 辅助索引的叶子节点指向主索引。

非聚簇索引

主索引和辅助索引的叶子节点指向数据存储的位置。

最左前缀结合原则

最左前缀原则就是使用联合索引时,查询条件需要遵循索引中列的顺序,从左到右进行匹配。
在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。当创建(a,b,c)复合索引时,想要索引生效的话,只能使用a、ab、ac和abc四种组合。

select a,b,c from table where a = 'v' ; #索引生效,使用索引a
select a,b,c from table where a = 'v' and b='v1'; #索引生效,使用索引ab
select a,b,c from table where a = 'v' and c='v3'; #索引生效,使用索引a
select a,b,c from table where a = 'v' and b='v1' and c='v3'; #索引生效,使用索引abc

注意:实践证明,where后面的条件顺序并不会影响索引的命中条件,即 ba和ab效果一致。原因是mysql在执行前会对where语句进行自行调优。

全文索引

MySQL的全文索引是一种用于搜索文本内容的索引方式,它可以通过分析文本内容中的关键词,帮助提高检索效率。
MySQL使用全文索引主要分为以下步骤:

创建全文索引:

ALTER TABLE table_name ADD FULLTEXT(column_name);

创建全文索引的查询:

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keywords');

在查询语句中,column_name是要查询的列名,keywords是要搜索的关键词。

需要注意的是,只有MyISAM和InnoDB存储引擎支持全文索引,其中MyISAM引擎默认全文索引,InnoDB引擎需要手动创建全文索引。全文索引的查询还需要考虑相关性和阈值,即匹配结果太少可能会查不到结果。

日志

slowlog

设置具体的时间,把执行超时的sql记录,在日志文件中,方便进行优化调整。

binlog

主要用来进行数据库之间的主从同步

errorlog

记录数据库进程中的一些错误信息

relaylog

在slaver机器中暂存同步过来的binlog数据

undolog

回滚日志,记录的是数据的历史版本信息,用来保证原子性和MVCC

redolog

前滚日志,将数据从内存到磁盘的操作进行顺序读写到redolog,当需要进行数据恢复的时候通过redolog进行数据恢复。

两阶段提交

DML操作日志写入redolog和binlog的流程如下:
1.将日志写入redolog,并标记状态为(prepare);
2.将日志写入binlog;
3.修改redolog里面的状态为commit。
当日志写入第一步成功后宕机,重启进行数据恢复时,在redolog中看到记录是prepare的,于是到binlog中查找有无与之对应的数据,如果没有,则将redolog中的该条记录删除,如果有则将状态改为commit。这样就保证了redolog和binlog中的数据一致性。

MVCC

MVCC(Multi-version concurrency controll),多版本并发控制,用来解决数据并发场景中并发读写问题的。
并发场景下,多线程共同读写会有线程安全问题,会产生脏读、幻读、不可重复读的问题。多线程共同写会产生丢失更新的问题。
MVCC底层实现由三部分组成:隐藏字段、undolog、readview。

  • 隐藏字段:对于用户不可见;
    – DB_TXR_ID:创建这条记录或最后一次修改该记录的事务的id值。
    – DB_ROLL_PTR: 回滚指针,指向的是上一个数据的版本。
    – DB_ROW_ID:隐藏主键,如果数据表没有设置主键,会生成一个6个字节的rowid作为隐藏主键。
  • undolog:回滚日志,表示在insert、delete、update操作产生的方便回滚的日志。undolog中存储的是通过DB_ROOLL_PTR链接起来的记录历史版本的一个链表,链表会有专门的线程负责清理。
  • readview:读视图是在事务进行快照读的时候产生的读视图,保存的并不是数据的信息,而是事务的相关信息。包含以下三个部分:
    – trx_list: 在生成readview时刻,当前系统正在活跃的事务列表。
    – up_limit_id:当前活跃列表中事务id最小的值。
    – low_limit_id: 系统尚未分配的笑一个事务id。

数据是否可读通过可见性算法进行判断,可见性算法的判断依据是readView+DB_TXR_ID,事务通过控制readView的生成时机来控制数据的是否可读。文章来源地址https://www.toymoban.com/news/detail-675619.html

到了这里,关于Mysql知识梳理的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【MySQL】数据库基本知识小结

    哈喽大家好,我是阿Q,今天我们来总结一下【MySQL】 入门的必备知识点吧~ 数据库 :DataBase 简称 DB,就是信息的集合或者说数据库是由数据库管理系统管理的数据的集合。 数据库管理系统 :DataBase Management System 简称 DBMS,是一种操纵和管理数据库的大型软件,通常用于建立

    2024年02月09日
    浏览(51)
  • MySQL基础篇——MySQL数据库客户端连接,数据模型,SQL知识

    作者简介:一名云计算网络运维人员、每天分享网络与运维的技术与干货。   座右铭:低头赶路,敬事如仪 个人主页:网络豆的主页​​​​​​ 目录 前言 一.客户端连接MySQL 二. 数据模型 1.关系型数据库(RDBMS) 2.数据模型 三.SQL 1.SQL通用语法 2.SQL分类 3.数据库操作 1). 查

    2024年02月06日
    浏览(77)
  • 【数据库】MySQL基础知识全解

    系列综述: 💞目的:本系列是个人整理为了 秋招面试 的,整理期间苛求每个知识点,平衡理解简易度与深入程度。 🥰来源:材料主要源于拓跋阿秀、小林coding等大佬博客进行的,每个知识点的修正和深入主要参考各平台大佬的文章,其中也可能含有少量的个人实验自证。

    2024年02月09日
    浏览(61)
  • Mysql数据库基础知识总复习

    小亭子正在努力的学习编程,接下来将开启javaEE的学习~~ 分享的文章都是学习的笔记和感悟,如有不妥之处希望大佬们批评指正~~ 同时如果本文对你有帮助的话,烦请点赞关注支持一波, 感激不尽~~ 目录 前言 数据库基础知识 数据,数据库,数据库管理系统,数据库系统 数据

    2024年02月02日
    浏览(59)
  • 【MySQL数据库重点】第二节:MySQL基础知识(基本操作)

    目录 一:数据库的操作 1.显示数据库 2.创建数据库 3.使用数据库 4.删除数据库 二:常用数据类型 1.数值类型:整型和浮点型 2.字符串类型 3.日期类型 三:表的操作 1.查看表结构 2.创建表 3.删除表 1.显示数据库 语法: show databases;  2.创建数据库 (1)简化语法 create database 数

    2024年02月08日
    浏览(56)
  • 【一文详解】知识分享:(MySQL关系型数据库知识进阶)

    Mysql体系结构: 连接层 位于最上层,是一些客户端和连接服务,主要完成一些类似于连接处理,授权认证及相关的安全方案。 服务器也会为安全接入的每个客户端验证它所具有的操作权限。 服务层 第二层,主要完成大多数的核心服务功能,如sql接口,并完成缓存的查询,sql的分析和优

    2024年02月02日
    浏览(77)
  • MySQL数据库基础初学者必备知识

    目录 1.数据库的基本操作 1.1显示所有数据库 1.2创建数据库 1.3删除数据库 2.数据库的类型 2.1数值类型 2.2字符串类型 2.3日期类型 3.表的简单操作 3.1创建一个表 3.2显示表的结构 3.3显示数据库中的所有表 3.4删除指定表 4.实战练习 数据库的基本操作为:显示数据库,创建数据库

    2023年04月11日
    浏览(72)
  • MySQL数据库基础知识,基础操作详细整理

    引入: 淘宝网,京东、微信,抖音等都有各自的功能,那么当我们退出系统的时候,下次再访问时,为什么信息还存在? 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。 每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数

    2024年04月17日
    浏览(57)
  • 【数据库】MySQL概念性基础知识期末复习

    第一章 3 二维表结构……数据模型—— 关系数据模型 5 描述全部数据整体逻辑结构—— 模式 6 逻辑数据独立性—— 模式变,外模式和应用程序不变 7 物理数据独立性—— 内模式变,外模式和应用程序不变 9 R-(R-S)—— R∩S 10 从两个关系笛卡尔积中选取他们属性间满足一定条

    2024年02月02日
    浏览(120)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包