MySQL 知识

最新推荐文章于 2024-10-02 12:42:27 发布
最新推荐文章于 2024-10-02 12:42:27 发布
阅读量281 收藏
点赞数
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/YuYan_wang/article/details/105906207
版权

事务

事务特点：ACID

原子性（atomicity）
一个事务必须被视为一个不可分割的最小工作单元，整个事务要么全部提交成功，要么全部失败回滚
一致性(consistency)
数据库总是从一个一致性的状态转换到另外一个一致性的状态。也就是事务没有进行提交不会将数据保存到数据库中
隔离性(isolation)
一个事务所做的修改在最终提交以前， 对其他事务不可见
持久性(durability)
一旦事务提交，则其所做的修改就会永久保存导数据中

事务隔离

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

隔离级别

隔离级别说明脏读可能性不可重复可读性幻读可能性加锁读
read uncommitted未提交读YesYesYesNO
read committed提交读NOYesYesNO
repeatable read可重复读NONOYesNO
serializable可串行化NONONOYes
  • 脏读： 事务可以读取未提交的数据
  • 不可重复读： 一个事务从开始直到提交之前，所做的任何修改，其他事务不可见
  • 幻读：当某个事务在读取某个范围内的纪录时，另一个事务又在该范围内插入了新的纪录，当之前的事务再次读取该范围的纪录行，会产生幻行
  • 加锁读：在读取每一行数据都加上锁

多版本并发控制协议：MVCC

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)
可以认为MVCC是行级锁的一个变种

InnoDB的MVCC：

  • 原理：
    是通过在每行纪录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间（或删除时间）。当然存储的并不是实际的时间值，而是系统版本号
  • 实现过程：

    • select:
      InnoDB会根据以下两个条件检查每行纪录：

      • InnoDB值查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的
      • 行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除

      只有符合上述两个条件的纪录，才能返回作为查询的结果

    • insert:
      InnoDB为新插入的每一行保存当前系统版本号作为行版本号

    • delete:
      InnoDB为删除的每一行保存当前版本号作为行的删除标识

    • update:
      InnoDB为插入一行新纪录，保存当前版本号作为行版本号，同时保存当前系统版本号到原来的行作为行的删除标识

事务使用

START TRANSACTION | BEGIN [WORK] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET AUTOCOMMIT = {0 | 1}
  • START TRANSACTION 或 BEGIN 语句：开始一项新的事务。
  • COMMIT 和 ROLLBACK：用来提交或者回滚事务。
  • CHAIN 和 RELEASE 子句：分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事物，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
  • SET AUTOCOMMIT 可以修改当前连接的提交方式， 如果设置了 SET AUTOCOMMIT=0，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚

锁

行级锁、表锁、页锁,MyISAM 只支持表锁，InnoDB 支持表锁和行锁，默认为行锁。

  • 表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低。

  • 行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高。

说一下乐观锁和悲观锁？

  • 乐观锁：每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在提交更新的时候会判断一下在此期间别人有没有去更新这个数据。

  • 悲观锁：每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻止，直到这个锁被释放。

存储引擎

常用的引擎有InnoDB、MyISAM、Memory，当然经过MySQL的不断迭代和升级，目前最常用的是InnoDB，除非使用一些InnoDB没有特性，其余的情况下，建议使用InnoDB，因为在mysql5.5版本开始InnoDB已经成为MySQL的默认引擎，说明其优势有目共睹的，同时，InnoDB支持事务，在读写方面也有了很大的提升

区别

itemMyISAMInnoDB
事务不支持支持
外键不支持支持
锁表级锁行级锁
索引支持全文索引5.6.4版本后支持全文索引
索引非聚簇索引聚簇索引
count(*)快，因为内部维护计数器，但存在where时，不会变快慢
MVCC不支持支持
系统奔溃困难
存储文件三个，表结构，索引，数据两个，表结构，索引和数据

注意

  1. 聚簇索引：数据文件和索引文件绑定在一起的，必须要有主键，通过主键索引效率很高，但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。
  2. 非聚簇索引： 数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

索引

索引类型

  • 普通索引
  • 唯一索引
  • 主键索引
  • 全文索引
  • 组合索引

索引的优点

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

索引方法

  • B-Tree
  • HASH

B-Tree

  • 原理
    • 多叉平衡查找树
    • B-Tree
      在这里插入图片描述
    • B+Tree
      在这里插入图片描述

Hash

  • 原理

    • 采用一定的哈希算法，把键值换算成新的哈希值
      在这里插入图片描述

  • 优点

    • 在没有大量重复键值时，等值查询，Hash索引查询速度非常快

  • 缺点

    • Hash索引只能满足 “=” ，“IN”，“<=>”查询，不能进行范围查询
    • 在联合索引中， Hash要么全部使用索引，要么全部不使用索引，无法像BTree那样使用左前缀规则
    • Hash在遇见大量重复值时，效率不一定比BTree高

B树和B+树的区别

1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
在这里插入图片描述

2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接
在这里插入图片描述

所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

MySQL联合索引

1、联合索引是两个或更多个列上的索引。

对于联合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。

索引：	最左原则
	a  b  c   
	a  b
	a
	a  c  // 只使用 a 的索引
	b  c  // 无法锁定索引
匹配：
	全值匹配
	a = 2 and b = 2 and c =2

	匹配最左前缀
	a = 2
	匹配列前缀
	a like '2%'
	匹配范围值
	a BETWEEN 2 and 10

	精确匹配某一列并范围匹配另一列
	a = 2 and b like '2%'
	只访问索引的查询
注意： 范围查询后的所有索引无法使用

什么情况下应不建或少建索引

1、表记录太少

2、经常插入、删除、修改的表

3、数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

4、经常和主字段一块查询但主字段索引值比较多的表字段

分区

什么是表分区？

分区表是一个独立的逻辑表，但底层由多个物理子表组成。从底层文件系统来看，每一个分区表都有一个使用 # 分隔命名的表文件

表分区与分表的区别

分表：指的是通过一定规则，将一张表分解成多张不同的表。比如将用户订单记录根据时间成多个表。

分表与分区的区别在于：分区从逻辑上来讲只有一张表，而分表则是将一张表分解成多张表。

表分区有什么好处？

  • 表非常大以至于无法全部都放在内存中，或者只在表的最后部分有热点数据，其他均是历史数据
  • 分区表的数据更容易维护，例如：想批量删除大量数据可以清除整个分区
  • 分区表的数据可以分布在不同的物理设备上，从而高效的利用多个硬件设备
  • 可以使用分区表来避免某个特殊的瓶颈，例如InnoDB的单个索引的互斥访问
  • 优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率

分区表的限制因素

  • 一个表最多只能有1024个分区
  • MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。
  • 如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。
  • 分区表中无法使用外键约束

如何判断当前MySQL是否支持分区？

命令：show variables like '%partition%' 运行结果:

have_partintioning 的值为YES，表示支持分区。

MySQL支持的分区类型有哪些？

  • RANGE分区： 这种模式允许将数据划分不同范围。例如可以将一个表通过年份划分成若干个分区
  • LIST分区： 这种模式允许系统通过预定义的列表的值来对数据进行分割。按照List中的值分区，与RANGE的区别是，range分区的区间范围值是连续的。
  • HASH分区 ：这中模式允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如可以建立一个对表主键进行分区的表。
  • KEY分区 ：上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。

SQL 优化

表结构及字段

  • 表结构
    • 将字段很多的表分解成多个表
    • 增加相应的冗余字段
    • 增加中间表，提高查询效率
  • 字段
    • 遵循小而简单,选择简单的类型,并设置合适的大小
    • 尽可能使用not null,在某个情况下，可以使用其他值存储代替Null
    • 字段长度固定的表查询会更快

索引

  • 建立合适的索引，对联合索引中的字段进行分析，合理的创建索引以及顺序
    • 索引使用最左原则，比如： a b c , a b , a, 这中才能命中索引， a c中c字段是无法命中索引的
    • 依据最左原则， 当where中遇见范围查询时，范围查询后的所有索引无法使用
    • 尽量选择区分度高的列作为索引，区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，
  • 索引列不能参与计算
  • 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
  • 禁止给where中条件都单独设置一个索引，可以使用联合索引

SQL语句

  • 不要在where中 对索引列使用表达式或函数,将运算放在等号后面
  • 对于count(*)、count(列）、count(常量),在SQL92中count(*)是标准统计行数的语法，并且在效率上面与count(常量) 差不太多
  • like查询，不要以通配符开头，如果需要可以使用sphinx
  • 禁止使用select(*) ，因为会增加很多不必要的消耗（cpu、io、内存、带宽等），并且减少了覆盖索引的可能性
  • 对于大数据，进行分段操作
  • 对于联合索引，如果存在范围查询，比如between，<,>等条件时，会造成后面索引失效，如果范围值比较小，可以采取in进行替代
  • 尽量使用inner join，避免left join，因为如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表
  • 避免使用JOIN关联太多的表
  • 尽量使用join代替子查询
  • 多表连接时，尽量小表驱动大表，即小表 join 大表
  • 在千万级分页时使用limit
  • 对于经常使用的查询，可以开启缓存
  • 尽量少用 or ， 可以使用union all 来代替
  • 避免隐式类型转换

注意

写完sql ， 尽量使用explain进行分析下，sql是否命中索引，以及是否有优化的空间

主从复制

  • 工作

    • 在主库上把数据更改纪录到二进制日志中
    • 备库将主库上的日志复制到自己的中继日志中
    • 备库读取中继日志中的事件，将其重放到备库数据之上

  • 具体步骤(此步骤适用于主库和从库都是刚搭建)

    • 在主库的my.cnf文件中增加或修改如下内容，并重启服务器：
    log_bin = mysql-bin
# 介于 1 ~ 2^32 -1 之间
server_id = 10
    • 备库上也需要在my.cnf 中增加类似的配置，并重启服务器：
    log_bin = mysql-bin
server_id  = 2
# 指定中继日志的位置
relay_log  = /var/lib/mysql/mysql-relay-bin  
# 允许备库将其重放的时间也纪录到自身的二进制日志中
log_slave_updates = 1           
# 阻止任何没有特权权限的线程修改数据                   
read_only  =1
    • 启动复制
    mysql> change master to MASTER_HOST = ‘server1’,
       -> MASTER_USER = ‘repl’,
       -> MASTER_PASSWORD =‘password’,
       -> MASTER_LOG_FILE = ‘mysql-bin.000001’,
       -> MASTER_LOG_POS =0;
 注意：
   1、MASTER_LOG_POS 参数被设置为0，因为要从日志的开头读起。
   2、可以使用 SHOW SLAVE STATUS 语句检查复制是否正确执行
    • 开始复制
    mysql> START SLAVE
YuYan_wang
关注
专栏目录
学习笔记 | 完整的MySQL知识体系 思维导图
大虎牙的博客
06-17 3209
学习目标： 数据库的架构设计、性能调优、大规模数据库集群运维 如何利用 MySQL 来支撑互联网的海量数据和高并发请求 掌握 MySQL 的底层运行机制，教你如何发挥 MySQL 的最佳性能、让你掌握 MySQL 在互联网应用领域的最佳实践，学会如何处理亿级业务数据和高并发请求。 MySQL的主流分支 MySQL 的 8 大优点 第一个是体积小、速度快，可以在不占用太多资源的情况下提供数据库服务； 第二个是开源免费，工具生态完善，用起来没有经济门槛； 然后是，简单易用，维护成本低，用起来没有技术
MySQL知识点汇总
LuckyPrinceyue的博客
12-13 1847
数据冗余
MYSQL知识点
Allurelove19的博客
03-17 5644
本文根据bilibili达内的大数据课程（BV1FQ4yR7SM）总结，受益匪浅。学习的比较慢，学完某块，我也会继续上传我的笔记 1、mysql基本概念 （1）数据库就是存储数据的仓库，可以按照一定的数据结构存储管理大量的数据及数据与数据之间的关系，它本质上是一种信息管理系统。常见有：层次式数据库、网络式数据库、关系型数据库。 （2）关系型数据库：即为建立在关系模型基础上的数据库。 （3）Oracle（收费） SQLServer Mysql（开源） SQLite（轻量型数据库，安卓等） 2、SQL语言简介
个人总结：Mysql知识图谱
霍城延的随笔
01-16 2786
使用思维导图工具对mysql使用，进行知识总结。
MySQL知识点梳理 万字超详解
weixin_59551524的博客
03-18 6717
文章目录MySQL数据库分类SQL分类数据库的基础操作常用数据类型表的操作MySQL表的增删查改(基础)增加操作查询（Retrieve)order by 排序别名去重 distinct条件查询 where分页查询修改update删除 deleteMySQL增删查改(进阶)null约束unique唯一约束DEFAULT：默认值约束PRIMARY KEY：主键约束FOREIGN KEY： 外键约束CHECK约束（了解）数据库表的设计一对一一对多多对多新增聚合查询聚合函数GROUP BY子句HAVING联合查询
【MySQL】数据库基础知识
热门推荐
m0_51064412的博客
05-20 5万+
文章目录一. 什么是数据库二. 为什么要有数据库三. 数据库分类四. 数据库的基本使用1. MySQL安装2. 检查MySQL后端服务器是否启动3. 连接MySQL服务器 一. 什么是数据库 数据库（database）是一整套的数据存储容灾解决方案。是用来组织、存储和管理数据的仓库。 当今世界是一个充满着数据的互联网世界，充斥着大量的数据。数据的来源有很多，比如出行记录、消费记录、浏览的网页、发送的消息等等。除了文本类型的数据，图像、音乐、声音都是数据。 为了方便管理互联网世界中的数据，就有了数据库管理系统
MySQL知识点总结（必读，详细）
qq_45701130的博客
01-16 4212
mysqlMySQL常见的数据类型mysql的索引**无法命中索引的情况**一些注意事项 MySQL常见的数据类型 mysql的数据类型：数值型、日期/时间、字符串类型 tinyint:小整数，数据类型用于保存一些范围的整数数值范围，MySQL中无布尔值，使用tinyint(1)构造 int：整数 varint：大整数 float：单精度浮点数，数值越大越不准确 double:双精度浮点数，数值越大月不准确 char:用于表示固定长度的字符串，可以包含最多达255个字符 varchar：用于变长的字符串，可
【MySQL】如何构建一个完整的MySQL知识体系（MySQL专栏启动）
小明的Java问道之路
11-01 4029
为了能够使大家真正高效学好 MySQL，本文通过为什么要学MySQL，到MySQL要学习什么，给出本专栏会讲解MySQL哪些知识，全面了解本专栏，帮助大家可以简历中写“精通MySQL”而不虚，帮助学习本专栏的人成为组内数据库Owner。
MySQL知识点思维导图
持之以恒，方得始终
03-23 1260
MySQL知识点思维导图，文章里是PNG格式，可能不是很清晰，文末附上PDF和xmind工程文件链接，需要的小伙伴自取，都只需要1个币（设置成0个币还需要啥下载码）。如有错误的地方欢迎大家批评指正。 MySQL知识点思维导图PDF MySQL知识点思维导图PDF-MySQL文档类资源-CSDN下载MySQL知识点思维导图PDF更多下载资源、学习资料请访问CSDN下载频道.https://download.csdn.net/download/qq_36756682/85022433My...
MySQL基础知识总结
徒挽的博客
11-03 3445
MySQL基础知识总结
MySQL知识点思维导图PDF
03-23
MySQL知识点思维导图PDF
mysql知识点
09-28
### MySQL知识点详解 #### 一、MySQL数据库概述 **1. MySQL执行流程及发展历程** - **MySQL起源**：MySQL最初由瑞典MySQL AB公司开发，后被Sun Microsystems收购，随后Sun Microsystems又被Oracle公司收购，因此...
MYSQL知识学习过程笔记
10-27
MYSQL知识学习过程笔记 在学习MYSQL之前，首先需要了解什么是数据库、数据库管理系统和SQL它们之间的关系是什么？数据库英文单词DataBase，简称DB，按照一定格式存储数据的一些文件的组合，顾名思义：存储数据的...
MySQL知识总结思维导图
10-27
MySQL知识总结思维导图，压缩包内包含MySQL数据库基础、MySQL库的操作、MySQL表的操作（DDL）、MySQL数据类型、MySQL表的约束、MySQL基本查询（DML）、MySQL内置函数、MySQL复合查询、MySQL内外连接、MySQL索引特性...
Mysql知识体系和调优
10-04
Mysql知识体系和调优，方面了解MYSQL性能调优的知识点
MySQL表的增删改查(基础)
最新发布
weixin_63965017的博客
10-02 532
语法表名values(值，值......);值与列相匹配。
【MySQL】MySQL 数据库主从复制详解
人生苦短，睡觉要紧，睡醒再说
10-01 1252
MySQL 主从复制是实现高可用性和负载均衡的重要手段。通过合理配置和监控，可以显著提升数据库的可用性和性能，有效地管理数据同步，实现系统的弹性和鲁棒性。在实际应用中，应根据业务需求和系统架构来合理设计部署方案，并定期监控复制状态，以确保数据的一致性和完整性。了解主从复制的工作原理及其配置过程，对数据库管理员至关重要。希望本文能帮助您深入理解 MySQL 主从复制的相关知识。
【mysql相关总结】
Charles D
09-30 345
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
Python 3 和 MySQL(PyMySQL) 的完美结合
wjs2024的博客
10-02 625
PyMySQL 是一个纯 Python 编写的 MySQL 客户端库，它提供了与 MySQL 数据库进行交互的接口。PyMySQL 设计轻量、易于安装和使用，且完全兼容 MySQLdb 接口，使得从 MySQLdb 迁移变得非常简单。Python 3 和 PyMySQL 库的结合为开发人员提供了一种高效、简洁的方式来与 MySQL 数据库进行交互。通过本文的介绍，您应该能够理解如何安装 PyMySQL、建立数据库连接、执行 SQL 语句、处理查询结果以及处理可能出现的错误。
mysql 知识 xmind图
01-26
下面是一个MySQL知识的Xmind图： 一、数据库基础知识 1. 数据库概念 2. 数据库管理系统 3. 关系型数据库 二、MySQL环境搭建 1. 下载安装MySQL 2. 配置MySQL环境 3. 启动和关闭MySQL服务 三、MySQL基本操作 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值