Mysql

已于 2022-03-09 17:49:20 修改
阅读量75 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql 索引 数据库
于 2021-02-24 11:18:33 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44761260/article/details/113976639
版权

常见存储引擎

InnoDB

  • 默认的存储引擎
  • 支持事务、行锁、外键

MyISAM

  • 不支持事务
  • 插入和查询效率高

数据库设计三大范式

在这里插入图片描述

事务

特性 ACID

  • 原子性Atomicity：一个事务中操作，要么全部成功，要么全部失败
  • 一致性Consistency：应用系统从一个正确的状态到另一个正确的状态。即AID为了保证C
  • 持久性Durability：事务一旦提交，不可回滚
  • 隔离性Isolation：事务之间，相互隔离，互不干扰

隔离级别

概念解释

  • 脏读 ：一个事务处理过程里读取了另一个未提交的事务中的数据
  • 不可重复读 ：一个事务范围内多次查询一个数据，却返回了不同的数据值。由于其他事务修改了数据
  • 幻读：如果执行了两次 SELECT，但第二次返回第一次未返回的行，则该行为“幻影”行。mysql 关于幻读的文档
    在这里插入图片描述

READ UNCOMMITTED

  • 读取其他事务未提交的执行结果

READ COMMITTED

  • 事务可以读取另一个已提交的事务
  • 解决脏读

REPEATABLE READ

  • Mysql默认级别
  • 在同一个事务里， select 的结果总是事务开始时时间点的状态
  • 解决不可重复读问题

MySQL 在RR隔离级别下 解决幻读问题

MVCC 解决 快照读模式的幻读问题
Next-Key Lock 解决当前读模式的幻读问题

快照读

简单查询（不加锁的），都是快照读。
MVCC 会在事务第一次快照读时候生成read view，查询的是事务开始的时候的快照数据，所以就不存在不可重复读和幻读的问题。

MVCC 多版本并发控制

数据有多个版本，每次事务都会生成undo.log
InnoDB 会对每一条数据保存的事务id。
只在RC和RR隔离级别下存在。

  1. insert 数据 name = Jerry,age = 24;
  2. 事务1更新数据 name = tom;
  3. 事务2更新数据 age =30;

InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。
在这里插入图片描述

当前读

lock in share mode（s锁）、for update（x锁）这些需要加锁的查询就是当前读。
就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。

Next-Key Lock

行锁 + 间隙锁的组合

  • 行锁 Record Locks ：下文的X锁和S锁。
-- 防止任何其他事务插入、更新或删除值为10的行。
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;
  • 间隙锁 Gap Locks ：
-- 阻止其他事务将值15插入到列 t.c1中，无论列中是否已经有这样的值，
-- 因为范围中所有现有值之间的间隙都被锁定。
SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
  • Next-Key Lock
-- (-∞,15] 
select * from t where  id <= 15 for update;

SERIALIZABLE

  • 所有读都会加共享锁，并发效率低

锁

行锁

  • 共享锁（也称为 S 锁）：允许事务读取一行数据select * from tableName where... lock in share mode;
  • 独占锁 （也称为 X 锁）：允许事务删除或更新一行数据select * from tableName where... for update;

表锁

为了实现多粒度的锁机制，InnoDB 还有两种内部使用的意向锁，由 InnoDB 自动添加，且都是表级别的锁

  • 意向共享锁 （IS）：事务即将给表中的各个行设置共享锁，事务给数据行加 S 锁前必须获得该表的 IS 锁
  • 意向排他锁 （IX）：事务即将给表中的各个行设置排他锁，事务给数据行加 X 锁前必须获得该表 IX 锁

锁之间关系

  • 冲突：两种锁不能同时存在，需要等待锁释放
  • 兼容：两种锁可以同时存在
锁类型XIXSIS
X冲突冲突冲突冲突
IX冲突兼容冲突兼容
S冲突冲突兼容兼容
IS冲突兼容兼容兼容

InnoDB加锁

  • 意向锁，为InnoDB引擎自动控制，用户不需要干预
  • DML操作，自动给涉及的数据集加X锁
  • 查询操作，默认不加任何锁 => 快照读
  • 查询操作可以手动的显式加锁 => 当前读

索引

  • 数据页：InnoDB存储的最小单位
    在这里插入图片描述
  • 回表：非主键索引根据获取到的主键值，再查找其他数据，图中红色部分
    在这里插入图片描述

索引的本质

索引的本质是一种排好序的数据结构

B+树索引

  • 只有叶子节点保存数据，非叶子节点只保存Key信息。这样可以大大降低B+树的高度
  • B+ 树有利于全表扫描。只需要遍历所有叶子节点
  • 每一个索引都会生成一个索引B+树
    在这里插入图片描述

InnoDB索引类型

索引类型

  • 主键索引：主键的索引
  • 普通索引：最普通的索引
  • 唯一索引：索引列的值必须唯一，但允许有空值
  • 联合索引：如果是组合索引，则列值的组合必须唯一
  • 全文索引：适合于模糊搜索

底层类型

  • 主键索引，聚簇索引
    • 叶子节点存储的是数据（数据页）
  • 非主键索引，非聚簇索引
    • 叶子节点存储的是索引字段和主键字段
    • 查询时候可能会需要回表（仅查询索引列时，不需要回表）

InnoDB 索引

联合索引

ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);

这个联合索引，其实会建出以下三个索引

  • name,city,age
  • name,city
  • name

最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配

  • where city= 'dalian' and age = 60 会让索引失效，缺失优先度最高的name
  • where name= 'dalian' and age = 60 会让索引失效，缺失中间字段city
  • where name= 'dalian' and city like '%lian' and age = 20 name和city会走索引，age不会走索引

查询优化器

当对索引中所有列通过"=" 或 “IN” 进行精确匹配时，索引都可以被用到

  • 所以 MySQL 不存在 where 子句的顺序问题而造成索引失效
  • where city= 'dalian' and name= 'dalian' 不会让索引失效，查询优化器会自动优化

注意事项

创建

  • 尽量使用简单类型（数字之类的）
  • 字段尽量为 NOT NULL
  • 字符串索引尽量指定长度，不要对整个列进行索引
  • 区分度越高越好，比如性别字段，区分度太低，不适合索引
  • 索引太多会影响DML新能

使用

  • LIKE 时要 userName%，不要全模糊
  • 字符串要加''
  • 不要在列上计算，会让索引失效
  • 尽量不要使用NOT IN、<>、!= 操作
  • OR 条件
  • 组合索引的使用要遵守最左前缀原则

编码

如果你在使用Mysql或者MariaDB，不要用utf-8，改用utf-8mb4

缓存

MySQL缓存机制简单的说就是缓存sql文本及查询结果，如果运行相同的SQL，服务器直接从缓存中取到结果，而不需要再去解析和执行SQL。如果表更改了，那么使用这个表的所有缓存查询将不再有效，查询缓存中值相关条目被清空。

  • 查询必须是完全相同(逐字节相同)才能够被认为是相同的
  • 更改指的是表中任何数据或是结构发生改变，包括INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE等，也包括那些映射到改变了的表使用MERGE表的查询
  • 对于一些不常改变数据且有大量相同SQL查询的表，查询缓存会节约很大的性能
  • where条件中如包含任何一个不确定的函数将永远不会被cache, 比如current_date, now等
  • 执行SQL里有触发器,自定义函数时，MySQL缓存也是不起作用的

参考文章

  • https://developer.ibm.com/zh/technologies/databases/articles/os-mysql-transaction-isolation-levels-and-locks/
  • https://juejin.cn/post/6844903666420285454
  • https://juejin.cn/post/6931901822231642125
  • https://blog.csdn.net/qq_39390545/article/details/108540362
  • https://tech.meituan.com/2014/06/30/mysql-index.html
  • https://juejin.cn/post/6844903885501530125
com.少年
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql<>操作符_mysql 操作符
weixin_29061509的博客
02-07 1654
1 mysql 操作符下图表示所有操作符的执行优先级，从高到低，同一行中的操作符优先级相同，相同优先级的情况则从左到右执行如果想改变优先级执行顺序则可以使用括号()1.1 对比操作符对比操作符的执行结果为true,false,null 三种Between A and B 代表检查值是否在A和B之间Coalesce()代表返回第一个非Null的值,如果没有非null值，则返回NULL=代表相等操作符...
mysql详解（持续更新）
热门推荐
Ferao的博客
10-21 3万+
去重语句： select distinct 列名 from 表名 字符类型转数值类型语句： select to_number('000123') from dual; 插入语句： insert into 表名 (字段) values (字段值) 新增表字段： alter table 表名 add (字段名 integer default 0 not null); 删除表字段： alter........................................
MySQL运算符 != 和 ＜＞ 以及 = 和 ＜=＞ 的区别
xmt1139057136的专栏
04-26 9850
你知道的越多，不知道的就越多，业余的像一棵小草！你来，我们一起精进！你不来，我和你的竞争对手一起精进！编辑：业余草blog.csdn.net/qq_39390545推荐：https://w...
MySQL | 常见运算符介绍
天下弈星~的博客
03-30 3826
运算符是告诉MySQL执行特定算术或逻辑操作的符号。MySQL的内部运算符很丰富，主要有四大类，分别是算术运算符、比较运算符、逻辑运算符、位运算符。
mysql大于等于、小于等于的写法
故事的小黄花的博客
07-30 1万+
mysql大于等于>=、小于等于<=、>、<的写法 第一种写法 大于等于 <![CDATA[ >= ]]> 小于等于 <![CDATA[ <= ]]> 大于 <![CDATA[ > ]]> 小于 <![CDATA[ < ]]> 第二种写法 原符号 < <= > >= & ' " 替换符号
MySql 8.0.37
最新发布
05-30
MySql 8.0.37
mysql8.0升级版本
12-12
MySQL 8.0 升级版本 在本文中，我们将详细介绍 MySQL 8.0 升级版本的步骤和注意事项。MySQL 是一个流行的开源关系数据库管理系统，广泛应用于各种Web 应用程序中。升级 MySQL 版本可以提高数据库性能、修复 bugs 和...
深入浅出MySQL全文
06-29
深入浅出MySQL全文 MySQL 是一种关系型数据库管理系统，广泛应用于各种行业和领域。本资源将从基础篇开始，详细讲解 MySQL 的安装、配置、启动和关闭服务、SQL 基础等知识点。 MySQL 的安装与配置 MySQL 的安装是...
MySQL5.5.62
11-06
MySQL 5.5.62 是 MySQL 数据库管理系统的一个重要版本，主要针对 Windows 操作系统提供了32位和64位的安装包。MySQL 是一个广泛使用的开源关系型数据库管理系统（RDBMS），由 Oracle 公司维护。它以其高效、稳定和...
mysql 5.7 perl
08-08
离线安装mysql文件包，包含依赖，以及peal全套安装包
mysql省市区表完整版文件
10-07
MySQL数据库在许多应用中用于存储地理信息，如省市区数据，尤其在中国的网站和应用程序中很常见。"mysql省市区表完整版文件"提供了一个全面的数据库表结构，包含了中国所有省份、城市和区县的详细信息，这些信息通常...
mysql57驱动jar包
12-25
mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57驱动jar包mysql57...
MySQL8.0参考手册.pdf
04-25
MySQL 8.0 参考手册 MySQL 8.0 参考手册是 MySQL 官方提供的一份详细的参考手册，该手册涵盖了 MySQL 8.0 版本的所有方面，包括概述、安装、配置、管理、安全、性能优化、故障排除等等。下面是该手册中的一些重要...
MySQL中的＜=＞
qq_51897206的博客
09-04 857
和=号的相同点像常规的=一样，两个值进行比较，结果是0（不等于）或1（相等）;换句话说：’A’’B’得0和’a’’a‘得1。和=号的不同点和=运算符不同的是，NULL的值是没有任何意义的。所以=号运算符不能把NULL作为有效的结果。所以：请使用,‘a’ NULL 得0 NULL NULL 得出 1。和=运算符正相反，=号运算符规则是 ‘a’=NULL 结果是NULL 甚至NULL = NULL 结果也是NULL。
mysql<>操作符_MySql 中的<=>操作符
weixin_33286939的博客
01-19 1932
今天在学习数据库的索引优化时，关于memory存储引擎的的hash索引时，看到了操作符<=> ，这个操作符还是第一次见到，于是上网查了一下。我想大家应该知道 = != <>操作符吧，特别说明一下<> 与!=都是不等于的意思，但是一般都是用<>来代码不等于因为<>在任何SQL中都起作用但是!=在sql2000中用到，则是语法错误，不兼...
MySQl 命令大全
墨鱼菜鸡
07-11 2万+
W3School SQL 教程：http://www.w3school.com.cn/sql/index.asp菜鸟教程 ---SQL 教程：http://www.runoob.com/sql/sql-tutorial.html MySQL是一个关系型数据库管理系统，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都...
mysql<>操作符,讲解MySQL中<=>操作符的用法
weixin_30486919的博客
03-17 4070
讲解MySQL中<=>操作符的用法问题 ：我在看以前的一个开发者的代码时看到WHERE p.name <=> NULL在这个查询语句中 <=>符号是什么意思啊？是不是和 =号是一样啊？还是一个语法错误啊？但是没有显示任何错误或者异常。我已经知道了mysql中的 <> = !=等符号。最佳回答 ：和=号的相同点像常规的=运算符一样，两个值进行比较，结果...
MySQL查询语句与数据库管理实践
"MySQL SQL查询语句大全集锦，涵盖了从数据库的基本操作到数据的增删改查等核心功能，适合初学者进行每日练习。同时，还包括了MySQL服务的启动与安全设置建议，有助于提升数据库的安全性。" MySQL是世界上最流行的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值