MySQL学习笔记

已于 2024-09-11 20:55:42 修改
阅读量1.1k 收藏 36
点赞数 19
文章标签： mysql 学习 笔记
于 2024-09-05 19:19:01 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_37949220/article/details/140920263
版权

MySQL数据库学习

基础篇

关于MySQL的安装和一些基本语法

概述

关系型数据库

建立在关系模型基础上，由多张相互连接的二维表组成的数据库
特点： 使用表存储数据，格式统一，便于维护；使用SQL语言操作，标准统一，使用方便

数据模型

数据模型

SQL语法

通用语法

  • SQL语句可以单行书写也可以多行书写，以分号结尾
  • SQL语句可以使用空格/缩进来增强语句的可读性
  • MySQL数据库的SQL语句不区分大小写，关键字建议用大写
  • 注释：
    单行注释：–注释内容或#注释内容
    多行注释：/*注释内容*/

SQL分类

  • DDL(Data Definition Language):数据定义语言，用来定义数据库对象(数据库，表，字段)
  • DML(Data Manipulation Language):数据操作语言，用来对数据库表中的数据进行增删改
  • DQL(Data Query Language):数据查询语言
  • DCL(Data Control Language):数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL

数据库操作

查询数据库

SHOW DATABASES;

查询当前数据库

SELECT DATABASE();

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除数据库

DROP DATABASE [IF EXISTS] 数据库名;

使用数据库

USE 数据库名;

表操作

查询当前数据库所有表

SHOW TABLES;

查询表结构：显示表的字段信息

DESC 表名;

查询指定表的建表语句

SHOW CREATE TABLE 表名;

创建表

CREATE TABLE 表名(字段1 数据类型 [COMMENT 注释],...)[COMMENT 表注释];

添加字段

ALTER TABLE tb_name ADD 字段 数据类型 [COMMENT 注释];

修改字段:需要保证已有数据与新类型兼容

ALTER TABLE tb_name MODIFY 字段名 新数据类型; #修改字段数据类型
ALTER TBALE tb_name CHANGE 旧字段名 新字段名 数据类型 [COMMENT 注释] [约束];--重命名字段

删除字段

ALTER TABLE tb_name DROP 字段名;

修改表名

ALTER TABLE tb_name RENAME TO new_tb_name;

删除表

DROP TABLE IF EXISTS tb_name;--删除整个表
TRUNCATE TABLE tb_name; #删除数据保留表的格式

数据类型

整数

数据类型字节数取值范围
TINYINT1[0-255]
SMALLINT2[0-65535]
MEDIUMINT3[0-16777215]
INT4[0-4294967295]
BIGINT8[0-18446744073709551616]

注 int(5) 形式用来表示显示的位宽，不足的用0填，超过显示全部数据，此形式只有在数据类型设置了unsigned zerofill才有效，数据占用空间仍为4个字节
浮点型

数据类型字节数描述
float4单精度浮点数
double8双精度浮点数

float(M,D) 表示数据位一共是M位，D位小数位

定点型
decimal(M,D) 浮点型容易出现精度丢失问题，decimal 常用于银行系统，互联网金融系统
浮点型是以近似值存储，定点型是以字符串形式进行保存
日期类型

数据类型字节数格式备注
date3yyyy-MM-dd存储日期值
time3HH:mm:ss存储时分秒
year1yyyy存储年
datetime8yyyy-MM-dd HH:mm:ss存储完整日期
timestamp4yyyy-MM-dd HH;mm:ss存储完整时间，可作时间戳

注 datetime与timestamp的区别
datetime范围为1000-01-01 00:00:00 到 9999-12-31 23:59:59
timestamp范围为1970-01-01 08:00:01 到 2038-01-19 11:14:07
datetime默认值可以为null，timestamp默认值为当前时间，timestamp存储的时间与当前时区相关

字符串

数据类型字符数描述
CHAR(N)0-255 bytes定长字符串
VARCHAR(N)0-65535 byres变长字符串
TINYBLOB0-255 bytes不超过255字符的二进制字符串
TINYTEXT0-255 bytes段文本字符串
BLOB0-65535 bytes二进制形式的长文本数据
TEXT0-65535 bytes长文本数据
MEDIUMBLOB0-16777215 bytes二进制形式的中等长度文本数据
MEDIUMTEXT0-16777215 bytes中等长度文本数据
LONGBLOB0-4294967295 bytes二进制形式的极大文本数据
LONGTEXT0-4294967295 bytes极大文本数据

注 char(n)是固定长度，不管存入几个字符，都占用n个字节，varchar(n)是变长字符串，varchar是存入的实际字符数+1个字节数据(n>255时是+2)，char类型的字符串检索速度比varchar类型快。char存储的字符数小于n时，后面补空格，检索时再删除所有空格，所以char字符串不能包含空格，varchar则不受限。

DML

添加数据

INSERT INTO tb_name(字段名1,字段名2,...) VALUES(值1,值2,...);

给表中所有字段添加数据

INSERT INTO tb_name VALUES(值1,值2,...);

批量添加

INSERT INTO tb_name(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...);

注 字符串和日期应用引号括起来

修改数据

UPDATE 表名 SET 字段名1 = 值1,... [WHERE 条件];

删除数据

DELETE FROM tb_name [WHERE 条件];

DQL

查询指定字段并起别名

SELECT 字段名 AS  别名 FROM 表名；

条件查询
between … and 是闭区间，in是在其中之一就可以

聚合函数：将一列数据作为一个整体，纵向计算

函数功能
count统计数量
max最大值
min最小值
avg平均值
sum求和

语法

SELECT 聚合函数(字段列表) FROM 表名；

分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 HAVING 分组后过滤条件;

注 where和having的区别
执行时机不同：where是分组前过滤，不满足where不进行分组，而having是分组后进行过滤
判断条件不同：where不能对聚合函数进行判断，而having可以
执行顺序：where>聚合函数>having
分组之后查询的字段一般是聚合函数和分组字段，查询其他字段无任何意义
排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式，字段2 排序方式;

注 ASC 升序 DESC 降序
分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

注 起始索引=(查询页码-1)x每页记录数，起始索引从0开始
执行顺序

FROM 表 WHERE 条件列表 GROUP BY 分组列表 HAVING 条件列表 SELECT 字段列表 ORDER BY 排序列表 LIMIT 分页参数;

DCL：管理用户

查询用户

USE mysql;
SELECT * FROM USER;

创建用户

CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;

修改用户密码

ALTER USER '用户名'@‘主机名’ IDENTIFIED WITH mysql-native-passwd BY '新密码';

删除用户

DROP USER '用户名'@‘主机名’;

注 主机名可以使用%通配

查询权限

SHOW GRANTS FOR '用户名'@‘主机名’;

授予权限

GRANT 权限 ON 库名.表名 TO ‘用户名’@’主机名‘;

撤销权限

REVOKE 权限列表 ON 库.表 FROM ‘用户名’@‘主机名’ ;

函数

字符串函数

名称功能
concat(S1,S2,…)拼接字符串
lower(str)小写
upper(str)大写
lpad(str,n,pad)左侧填充pad字符到n长度
rpad(str,n,pad)右侧填充
trim(str)去掉首尾空格
substring(str,start,len)返回len长度子串

数值函数

名称功能
ceil(x)向上取整
floor(x)向下取整
mod(x,y)取x/y的余数
rand()0-1之间随机数
Round(x,y)x四舍五入保留y位小数

日期函数

名称功能
curdate()返回当前日期
now()返回当前日期和时间
curtime()返回当前时间
year(date)返回date对应的年份
date_add(date,INTERVAL expr type)返回date间隔expr后的时间
datediff(date1,date2)返回两个日期间隔的天数

流程函数

名称功能
IF(value,t,f)如果alue为true，返回t，否则返回f
IFNULL(value1,value2)value1不为空返回，否则返回value2
CASE WHEN [value1] THEN [res1] ELSE [default] END如果value1为真，返回res1,否则default
CASE [expr] WHEN [value1] THEN [res1] ELSE [default] END如果expr的值为val1,返回res1，否则返回default

约束：作用于表中字段上的规则，用于限制存储在表中的数据

类型含义表示
非空约束不为空not null
唯一约束唯一unique
主键约束唯一且不为空primary key
默认约束未指定值时采用默认值default
外键约束两表间建立连接(MyISAM不支持)foreign key或者MUL
检查约束检查字段是否符合约束条件check

外键约束：保证数据间的一致性和完整性，不允许当前表插入一条外键值不存在的记录
语法

CREATE TABLE tb_name(...,[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段) REFERENCES 主表（主表列名))；

或者

ALTER TABLE tb_name ADD CONSTAINT 外键名称  FOREIGN KEY (外键字段) REFERENCES 主表（主表列名);

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除更新行为：主表删除记录时是否需要检查和更新子表

名称解释
NO ACTION删除更新主表时，查看该记录是否有对应外键，有则不允许删除更新
RESTRICT同NO ACTION
CASCADE删除更新时，一并删除更新子表记录
SET NULL删除时子表更新为NULL
SET DEFAULT主表更新时，子表更新为默认值(InnoDB不支持)

语法

ALTER TABLE ... ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

关系处理方式
一对多(多对一)在多的一方建立外键，指向一的主键
多对多建立第三张中间表
一对一多用于单表拆分，以提升操作效率，在任意一方加入主键，关联另一方的主键，并且设置外键为唯一的

多表查询
笛卡尔积，需要消除无效的笛卡尔积

内连接查询

查询两表的交集
隐式内连接查询

SELECT 字段 FROM 表1,表2 WHERE 条件;

显式内连接查询

SELECT 字段 FROM 表1 [inner] join 表2 ON 连接条件;

注：显式内连接比隐式内连接性能要好

外连接查询

左表或者右表的全部，加上两表的交集，交集为空则字段值默认为NULL
外连接查询必须有ON,否则会报错
左连接查询
显示左表的全部

SELECT 字段 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

右连接查询
显示右表的全部

SELECT 字段 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
自连接查询

当前表与自己建立连接查询，可以是内连接也可以是外连接查询，必须要取别名

SELECT 字段列表 FROM 表1 别名A JOIN 表2 别名2 ON 条件;
联合查询

把多次查询结果合并起来，形成新的结果集

SELECT 字段列表 FROM 表A 
UNION [ALL]
SELECT 字段列表 FROM 表B;

对于联合查询，多张表的列数和字段类型要保持一致，UNION ALL不去重

子查询

又叫做嵌套查询，是指在一个SQL查询中嵌套另一个查询，子查询可以放在SELECT,FROM,WHERE,HAVING等语句中，用于从另一个查询结果集中检索数据
标量子查询
子查询返回单个值

SELECT 字段列表 FROM 表名 WHERE 字段 = (SELECT 字段 FROM 表名 WHERE 条件);

列子查询
子查询返回的结果是一列

SELECT 字段列表 FROM 表名 WHERE 字段 = (SELECT 字段 FROM 表名 WHERE 条件);

行子查询
子查询返回的结果是一行

SELECT 字段列表 FROM 表名 WHERE 字段 = (SELECT 字段列表 FROM 表名 WHERE 条件);

表子查询
子查询返回的结果是多行多列

事务

一组操作的集合，是一个不可分割的工作单位，事务会把所有操作作为整体向系统提交或撤销操作，即这些操作要么同时成功要么同时失败
语法

SELECT @@autocommit; # 设置事务提交方式
COMMIT; # 提交事务
ROLLBACK; # 回滚事务
START TRANSACTION; # 开始事务 BEGIN也可以

ACID特性
原子性(Atomicity):事务是不可分割的原子单元，要么全部成功，要么全部失败
一致性(Consistency):事务完成时，必须使所有的数据保持一致的状态
隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
持久性(Durability):事务一旦提交或回滚，则对数据的改变是永久的

并发事务问题

问题原因
脏读一个事务读到另一个事务还没提交的数据
不可重复读一个事务先后读取同一条记录，但两次读取的数据不同
幻读一个事务按条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在了，好像出现了幻影

解决方案

事务隔离级别脏读不可重复读幻读
Read Uncommitted是是是
Read Committed否是是
Repeatable Read否否是
Serializable否否否

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION

设置事务隔离级别

SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE];

[注]：事务隔离级别越高，数据越安全，但性能越低

进阶篇

存储引擎

存储数据，建立索引，更新、查询数据等技术的实现方式，存储引擎是基于表的，不是基于库的

InnoDB

兼顾高可靠性和高性能的通用存储引擎，MySQL5.5后，默认的存储引擎
特点
DML操作遵循ACID模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和正确性
文件： xxx.ibd文件，InnoDB引擎的每张表都会对应这样的一个表空间文件，存储该表的表结构(frm,sdi)，数据和索引
参数： innodb_file_per_table
InnoDB逻辑存储结构

MyISAM

MySQL早期的默认存储引擎
特点： 不支持事务，不支持外键；支持表锁，不支持行锁，访问速度快
文件： sdi文件，存储表结构信息，MYD文件，存储数据，MYI文件，存储索引

Memory

表数据存储在内存中，只能做临时表或缓存使用
特点： 内存存放，hash索引
文件： sdi文件，存储表结构信息

存储引擎选择

InnoDB:应用对事务的完整性有比较高的要求，除插入和查询外，包含很多更新，删除操作
MyISAM:以读和插入为主，只有很少的更新和删除操作，对事务和并发要求不高
Memory:用作临时表和缓存

索引

帮助MySQL高效获取数据的数据结构(有序)
优点
提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗
缺点
索引列占用空间，提高查询效率，降低更新效率

结构

B+Tree索引

B Tree（多路平衡查找树）
以m路搜索树为例，分支节点包含数据，除根节点外 （至少包含一个key），每个节点包含：m/2 -1(向上取整)<=i<=m-1个key，并且所有的叶子节点都在同一层
B+Tree
与B Tree相比，B+Tree 做了如下改变，一是分支节点只有key，不保存数据，叶子节点保存所有的数据，二是叶子节点形成一个单向链表
MySQL 对经典的B+Tree做了优化，在原B+ Tree的基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，提高了区间访问的性能

Hash索引

采用一定的Hash算法，将键值换算成新的Hash值，映射到对应的槽位上，然后存储在Hash表中，产生Hash碰撞时，可以通过链表解决
特点
Hash索引只能用于对等比较(=,in)，不支持范围查找，并且无法排序，查询效率高于B+Tree
存储引擎支持
Memory引擎支持Hash索引，InnoDB引擎中具有自适应Hash功能，Hash索引是 存储引擎根据B+Tree索引在指定条件下自动构建的

分类

分类含义特点关键字
主键索引针对表中主键创建的索引默认自动创建，只能有一个PRIMARY
唯一索引避免同一个表中某数据列的值重复，NULL值可以重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引查找文本中的关键字，不是比较索引的值可以有多个，MyISAM支持FULLTEXT

根据索引存储形式分类

分类含义特点
聚集索引数据与索引存放在一起，索引的叶子节点保存在行数据中有且仅有一个
二级索引数据和索引分开存储，叶节点关联对应的主键可以有多个

聚集索引选取规则
如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
否则，InnoDB会自动生成一个rowId作为隐藏的聚集索引

语法

创建索引

CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON tb_name (col1,...);

或

CREATE TABLE tb_name(字段1 数据类型,...,
[UNIQUE|FULLTEXT] INDEX [index_name](col1 desc,col2 asc,...)
);

或

ALTER TABLE tb_name ADD [UNIQUE|FULLTEXT] INDEX [index_name](col1 asc,col2 desc,...);

查看索引

SHOW INDEX FROM tb_name;

删除索引

DROP INDEX index_name ON tb_name;

或

ALTER TABLE tb_name DROP INDEX index_name;

索引使用

最左前缀法则
联合索引遵循最左前缀法则，查询从索引的最左列开始，并且不跳过索引中的列
范围查询
联合索引中，出现范围查询(>,<)，范围查询的右侧的列索引失效，尽量使用(>=,<=)符号，此时右侧索引列不是失效
索引列运算
不要在索引列进行运算操作，索引将失效
字符串不加引号，索引将失效
模糊查询
头部模糊查询索引将失效
or连接
用or分隔开的条件，只有前后均含索引时，才会使用索引
数据分布影响
如果MySQL评估使用索引比全表查询更慢，则不适用索引
SQL提示
优化数据库的一个重要手段，在SQL中加入一些人为的提示来达到优化操作的目的

SELECT * FROM TB_USER FORCE/IGNORE/USE INDEX(INDEX_NAME) WHERE 条件;

覆盖索引
减少使用SELECT *，多使用覆盖索引，覆盖索引是SELECT的数据只用从索引中就能取得
前缀索引
当字段类型是字符串(varchar,text等)时，可以只将字符串中的一部分前缀建立索引，可以大大节约索引时间，从而提高索引效率
前缀长度 可以根据索引的选择性来决定，选择性是指不重复的索引值和数据表的记录总数的比值，选择性越高则查询效率越高

SELECT count(distinct substring(字段,1,5))/count(*) FROM TB_USER;

单列索引和联合索引
业务场景中，如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引

索引设计原则

  1. 针对数据量比较大，且查询比较频繁的表建立索引
  2. 针对常作为查询条件，排序，分组操作的字段建立索引
  3. 尽量选择区分度高的列建立索引，尽量建立唯一索引
  4. 字符串较长时，可以建立前缀索引
  5. 尽量使用联合索引，减少单列索引
  6. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束条件

SQL性能分析

SQL语句执行频率

SHOW GLOBAL STATUE LIKE 'COM_______';

慢日志查询
慢日志记录了所有执行时间超过指定参数的(long_query_time,单位：秒，默认取值10秒)的所有SQL语句的日志，MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/mysql/my.cnf)中配置如下信息slow_query_log=1,long_query_time=2
配置完毕以后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log

systemctl restart mysql/mysqld

profile详情
查看是否支持profile操作

SELECT @@HAVE_PROFILING;

开启profiling，默认情况下是关闭的

SET PROFILING=1

查看每条SQL的耗时情况

SHOW PROFILES;

查看指定query_id的SQL语句各个阶段的耗时情况

SHOW PROFILE FOR QUERY query_id;

查看指定query_id的SQL语句CPU的使用情况

SHOW PROFILE CPU FOR QUERY query_id;

explain执行计划
EXPLAIN或DESC命令获取MySQL如何执行SELECT语句的信息

字段含义
id序列号，id相同，执行顺序从上往下，id不同，值越大，越先执行
select_typeSIMPLE(简单表，不适用表连接或子查询)，PRIMARY(主查询，即外层的查询)，UNION(UNION中的第一个或者后面的查询语句)，SUBQUERY(包含子查询)
type连接类型，由好到坏：null(不访问任何表),system(访问系统表),const(主键查询或者唯一索引查询),eq_ref,ref(非唯一索引查询),range,index(使用了索引，但是会扫描索引),all(全表扫描)
possible_key显示可能应用到这张表上的索引，一个或多个
key实际用到的索引，NULL表示没用索引
key_len表示索引中使用的字节数，为索引用到的最大可能长度，并非实际使用长度，在不损失精度的前提下，越小越好
rows估计必须要查询的行数
filtered返回结果的行数占需读取行数的百分比，值越大越好

SQL优化

插入数据-insert优化
  • 批量插入
INSERT INTO tb_user values(),(),(),,,;
  • 手动提交事务 系统默认自动提交事务，每一条insert语句都开启一个事务，频繁提交事务会影响性能
START TRANSACTION;...;COMMIT;
  • 主键顺序插入 主键顺序插入性能高于乱序插入
  • 大批量数据插入 如果一次性插入大量数据，使用insert性能太低，使用load指令插入
# 客户端连接服务器端时，加上参数 --local-infile
mysql --local-infile -u user_name - p passwd 
# 设置全局参数local_infile=1，开启从本地加载文件导入数据的开关
set global local_infile=1;
# 执行load命令，将数据加载到表结构中
load data local infile '文件路径' into table `tb_user` fields terminated by '分隔符' lines terminated by '分隔符';
主键优化

数据组织形式：在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，称为索引组织表(Index Organizied Table)
页分裂：页可以为空，也可以填充一半，也可以100%，每个页包含了2-N行数据，根据主键排列，主键乱序插入时，若插入的数据在两页之间，InnoDB会找到前一页中间靠后的位置进行分裂，将后半页与新数据存储到新的一页
页合并：删除一行记录时，记录没有被物理删除，只是被标记为删除且它的空间边的允许被其他记录声明使用，当页中删除的记录达到MERGE_THRESHOLD(默认50%),InnoDB会开始寻找最靠近的页，看看是否可以优化空间使用
主键设计原则

  • 满足业务需求的情况下， 尽量降低主键的长度
  • 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键
  • 尽量不要使用UUID或其他自然主键(如身份证号)
  • 业务操作时，避免对主键的修改
order优化

Using filesort： 查询满足条件的数据行，然后在排序缓冲区sort buff中完成排序操作
Using Index： 通过有序索引顺序扫描直接返回有序数据，不需要额外的排序，操作效率高

  • 根据排序字段建立合适的索引，多字段排序，也遵循最左前缀法则
  • 尽量使用覆盖索引，覆盖索引不需要再回表查询，在使用覆盖索引的前提下才会有可能是Using Index
  • 多字段排序，一个升序一个降序时，需要注意联合索引在创建时的规则
  • 如果不可避免的出现filesort，大数据量排序时，可以适当增加排序缓冲区大小sort_buffer_size(默认是256K)，以避免出现磁盘文件排序
group by优化
  • 分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也满足最左前缀法则
limit优化

一个常见又头疼的问题是limit 2000000,10,此时需要Mysql排序前2000010条数据，再返回2000000-2000010条数据，其他记录丢弃，查询排序的代价非常大

  • 一般分页查询时，通过创建覆盖索引能较好地提高性能，可以通过覆盖索引加子查询进行优化
SELECT s.* from tb_user as s,(SELECT id from tb_user order by id limit 2000000,10) t where s.id=t.id;
count优化

count的几种用法
count(主键)：InnoDB引擎会遍历整张表，取键值，按主键计数
count(字段)：存在not null约束时，按行累加；不存在not null约束时，判断字段是否为空
count(1)：遍历整张表，不取值，按行累加
count(*)：按行累加，可以衍生为count('test')，count(0)等形式
效率排序：count(*)~count(1)>count(主键)>count(字段) 尽量使用count(*)

update优化
  • InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁变成表锁

锁

计算机协调多个进程或线程并发访问某一资源的机制

分类

全局锁： 锁住数据库中的所有表，可以读但不能写
表级锁： 锁住整张表
行级锁： 锁住一行数据

全局锁

对整个数据库实例加锁，加锁后整个实例就处于只读状态，使用场景是做全库的逻辑备份，对所有的表进行锁定
特点

  • 如果在主库上备份，备份期间不能执行更新，业务基本停摆
  • 如果在从库上备份，则从库不能执行主库同步过来的二进制日志，会导致主从延迟
表级锁

每次操作锁住整张表，锁定粒度大，并发度最低
分类
表锁，元数据锁，意向锁

表锁

分类
共享读锁(read lock)：当前会话只能进行DQL操作，不能进行DML和DDL操作
独占写锁(write lock)：当前会话可以进行DQL，DML和DDL操作
语法

lock tables tb_name read/write;

unlock tables/客户端断开连接
元数据锁（MDL）

MDL加锁过程是系统自动控制的，无需显式使用，在访问一张表的时候会自动加上。主要作用是维护表元数据的数据一致性，避免DML和DDL语句的冲突。
实操过程中，如果开启两个事务，一个事务先执行DDL语句，另一个事务后执行DML语句，则DDL不会加上EXCLUSIVE锁，DML也不会加上SHARE_READ锁。
如果开启两个事务，一个事务先执行DML语句，另一个事务后执行DDL语句，则DDL会加上EXCLUSIVE锁，DML会加上SHARE_READ锁。
这表明系统会自动判断需不需要加元数据锁，尽量保持存储引擎的最大并发性能。

意向锁

为了避免DML在执行时，加的行锁和表锁的冲突，在InnoDB中引入意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
意向共享锁： 与表锁共享锁(read)兼容，与表锁排他锁(write)互斥
意向排他锁： 与表锁共享锁(read)和表锁排他锁(write)互斥。意向锁之间不会互斥

行级锁

行锁
锁定单个行记录的锁，防止其他事物对此进行update和delete，在RC和RR隔离级别下都支持
InnoDB的行锁是针对索引加的锁，不通过索引条件检索数据，那么会升级为表锁
间隙锁
锁定索引记录间隙，确保索引记录间隙不变，防止其他事务在这个间隙进行insert，防止幻读，在RR隔离级别下支持
临键锁
行锁和间隙锁的结合，在RR隔离级别下支持
默认情况下，InnoDB在Repeatable Read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读

  • 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁
  • 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隙锁
  • 索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止

【注】间隙锁可以共存。行锁锁的是主键

MVCC(Multi-Version Concurrency Control)

维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现MVCC提供了一个非 阻塞读功能，MVCC的具体实现还需要依赖于数据库中的三个隐藏字段，undo log日志，readview
当前读
读取的是记录的最新版本， 读取时还要保证其他并发事务不能修改当前记录，会对读取的记录加锁，lock in share mode,for update,insert into,update,delete都是当前读
快照读
简单的select就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读

事务隔离级别读类型
Read Committed每次select都生成一个快照读
Repeatable Read开启事务后的第一个select语句是快照读的地方
Serializable快照读退化为当前读
实现原理
数据隐藏字段含义
DB_TRX_ID最近修改事务的ID，记录插入这条数据或最后一次修改该记录的事务ID
DB_ROLL_PTR回滚指针，指向这条记录的上一个版本，用于配合undo log
DB_ROW_ID隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段

undo log
回滚日志，当insert的时候，产生的undo log只在回滚时需要，在事务提交后，可立即被删除，而update，delete的时候，产生的undo log 日志不仅在回滚时需要，在快照时也需要，不能立即删除(因为insert时产生的是新的数据，没有旧的版本，而delete和update都会有版本链，会指向旧的数据，所以不能删除)
undo log版本链
不同事务或者相同事务对同一条记录进行修改，会导致该记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表的尾部是最早的旧记录。
ReadView
快照读SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id
包含四个核心字段，如下表所示

字段含义
m_ids当前活跃的事务ID集合
min_trx_id最小活跃事务ID
max_trx_id预分配事务ID，当前最大事务ID+1
creator_trx_idReadView创建者的事务ID

版本链数据访问规则

  1. trx_id == creator_trx_id 可以访问该版本，说明数据是当前事务修改的
  2. trx_id < min_trx_id：可以访问该版本，说明数据已经提交
  3. trx_id > max_trx_id：不可以访问该版本，说明该事物在ReadView生成后开启
  4. min_trx_id <= trx_id <= max_trx_id && trx_id not in (m_ids)：如果trx_id不在活跃事务集合里，说明事务已经提交，可以访问

总结：可以访问ReadView之前已经提交的版本，也可以访问当前事务修改的版本

不同的隔离级别，生成ReadView的时机不同：
READ COMMITTED： 事务每一次执行快照时生成ReadView
REPEATABLE READ： 在事务第一次执行快照读时生成ReadView，后续复用

MySQL刷题

  1. MySQL使用三值逻辑，True,False,Unknown,任何与NULL 进行比较得出的结果都会是Uknown。因此MySQL中提供is null和is not null来判断是否为空。
  2. MySQL中用来计算字符串中字符数的最佳函数是char_length(str),它返回字符串长度，另一个常用的函数LENGTH(str)可能会返回不同的结果，因为该函数返回字符串 str 的字节数，某些字符包含多于 1 个字节，如"￥"。
  3. 连接查询时，ON连接的条件会筛选掉不符合的行，但是左连接会保证左表中有记录，右连接会保证右表中有记录，外连接查询必须得有ON连接条件，否则报错
  4. CROSS JOIN返回整个笛卡尔积，跟没有条件的INNER JOIN效果一样
XWalnut
关注
Mysql学习笔记（全）
m0_60264772的博客
12-07 2万+
目录 MyISAM和InnoDB 性能下降SQL慢的原因： Mysql执行顺序 SQLJoin 索引 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 Extra 索引优化 索引口诀 索引案例 Order By 索引优化 exsites Mysql慢查询日志命令 函数操作批量插入数据 show Profile分析sql 全局查询日志 Mysql锁 锁命令 表锁：读锁 表锁：写锁 行锁 事务 行锁测试 间隙锁 行
尚硅谷MySQL学习笔记
热门推荐
IAMLSL的博客
02-11 2万+
MySQL笔记目录一、MySQL数据库基础篇大纲1. 数据库概述与MySQL安装篇第01章：数据库概述第02章：MySQL环境搭建2. SQL之SELECT使用篇第03章：基本的SELECT语句第04章：运算符第05章：排序与分页第06章：多表查询第07章：单行函数第08章：聚合函数第09章：子查询3. SQL之DDL、DML、DCL使用篇第10章：创建和管理表第11章：数据处理之增删改第12章：MySQL数据类型精讲第13章：约束4. 其它数据库对象篇第14章：视图第15章：存储过程与函数第16章：变量、
mysql 学习笔记
javaTalk
01-23 3512
mysql 学习记录，包含日志、事务、索引、锁等功能。
MYSQL学习笔记
weixin_44134589的博客
04-04 1336
提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档 MYSQL 学习笔记（一）一、掌握概念库的增删改查1、系统数据库2、创建数据库2.1.语法2.2.数据库的命名规则3、数据库的相关操作二、存储引擎1、什么是存储引擎2、mysql 支持的存储引擎3、指定表的类型/存储引擎三、表的增删改查1、表的介绍2、创建表3、查看表的结构4、修改表的结构4.1.修改表名4.2.增加字段4.3.删除字段4.4.修改字段5、复制表 一、掌握概念库的增删改查 1、系统数据库 information_schem.
Mysql学习笔记
huangbaokang的博客
07-22 1168
查看mysql默认的存储引擎 show variable like 'storage_engine%'; 查看表的详情 show create table table_name \G mysql修改表名 alter table old_table_name rename [to] new_table_name mysql修改字段名字 alter table table_name change...
MySQL学习笔记8——游标
qq_44778824的博客
04-24 2357
本节学习了游标的使用方法，包括在存储过程中使用游标的4个步骤，分别是定义游标、打开游标、读取游标数据和关闭游标。除此之外，还介绍了经常与游标结合使用的流程控制语句，包括循环语句LOOP、WHILE和REPEAT；条件判断语句IF和CASE；还有跳转语句LEAVE和ITERATE。
MySQL学习笔记1：数据库概述（尚硅谷）
qq_43101414的博客
02-22 3600
MySQL学习系列1
Linux mysql学习笔记
icanflying的博客
11-10 6475
一、安装MySQL 1、源代码安装 wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm rpm -ivh mysql-community-release-el7-5.noarch.rpm yum update yum install mysql-server 2、权限设置： chown mysql:mysql -R /var/lib/mysql 3、初始化 MySQL： mysqld ----user=mysql
MYSQL学习笔记-SQL
Hhg66hh的博客
01-11 544
MySQL学习笔记.zip
02-23
这份“MySQL学习笔记”将引导我们深入理解其核心概念和实用技能。 一、MySQL简介 MySQL是一个开源、免费的数据库系统，由瑞典的MySQL AB公司开发，后被Oracle公司收购。它的设计目标是速度、可移植性和简洁性，支持...
mysql学习笔记
12-16
这份“mysql学习笔记”涵盖了MySQL的基础知识，包括但不限于数据库的概念、SQL语言的使用、以及更深入的多表操作。 在“day08入门笔记”中，你可能会学到以下内容： 1. **数据库基本概念**：了解什么是数据库，...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
MYSQL中JDBC的使用
eternal__day的博客
11-14 1044
JDBC 是Java 中的一组API，用于执行SQL 操作（例如CRUD 操作：增、删、改、关系），同时可以和各种类型的数据库类型进行连接（MySQL、Oracle、SQL Server 等）。在项目中，可以通过下载MySQL的Connector/J包并添加到项目的类路径中。时，避免了简单的字符串粘贴可能带来的代码差异性差、安全性低的问题（如SQL注入），提供了更安全和清晰的插值方式。
力扣-Mysql-3293-计算产品最终价格（中等）
Jotcy的博客
11-15 892
本题需求为计算产品最终价格，并没有很难思考的点。只需要根据题目要求使用case when 条件判断 进行价格计算即可；进行两表关联时要注意选择 Products表为主表，以及价格计算时要使用 round() 函数四舍五入保留两位小数。
Debezium-MySqlConnectorTask
最新发布
SlothLu的博客
11-18 341
MySqlConnectorTask用于设置和启动MySQL连接器，确保能够正确地读取和处理MySQL的二进制日志。
【赵渝强老师】MySQL InnoDB的数据文件与重做日志文件
赵渝强老师CSDN博客主页
11-14 482
MySQL与Oracle一样都是通过逻辑存储结构来管理物理存储结构，即管理硬盘上存储的各种文件。下面将详细介绍InnoDB存储引擎中的数据文件和重做日志文件。
【mysql】锁机制 - 3.意向锁
lixiaonan0318的博客
11-18 288
是为了而设置的一种预判机制，即在一个操作发起实际资源的锁申请行为之前，先对更粗力度的资源发起一个加锁意向声明。
MySQL学习笔记：DQL查询语句详解
"本篇是关于MySQL学习笔记的第六部分，主要关注DQL（Data Query Language），即查询语句的使用。通过创建一个名为`student02`的表，包含了id、NAME、age、sex、address、math和english等字段，并插入一些学生数据，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值