MySQL笔记

MySQL笔记

一.事务

1.开启事务

至于start transaction 和 begin的区别:
两者的作用一摸一样,只是在begin可能成为关键字的时候,使用start transaction 可以 避免这种情况,start transaction或者begin开启一个事务,然后使用commit提交事务或者ROLLBACK回滚事务

在默认情况下,用户执行的每一条SQL语句都会被当成单独的事务自动提交。如果要将一组SQL语句作为一个事务,则需要先执行以下语句显式地开启一个事务。

START TRANSACTION; Begin

上述语句执行后,每一条SQL语句不再自动提交,用户需要使用以下语句手动提交,只有事务提交后,其中的操作才会生效。

COMMIT;

如果不想提交当前事务,可以使用如下语句取消事务(即回滚)。

ROLLBACK;
2.事务的四大特性:

1.原子性: Atom

  • 原子性(Atomicity)是指一个事务必须被视为一个不可分割的最小工作单元,只有事务中所有的数据库操作都执行成功,才算整个事务执行成功。
  • 事务中如果有任何一个SQL语句执行失败,已经执行成功的SQL语句也必须撤销,数据库的状态退回到执行事务前的状态。

**2.一致性:**Consist

  • 一致性(Consistency)是指在事务处理时,无论执行成功还是失败,都要保证数据库系统处于一致的状态,保证数据库系统不会返回到一个未处理的事务中。
  • MySQL中的一致性主要由日志机制实现,通过日志记录数据库的所有变化,为事务恢复提供了跟踪记录。

**3.隔离性:**Isolation

  • 隔离性(Isolation)是指当一个事务在执行时,不会受到其他事务的影响。保证了未完成事务的所有操作与数据库系统的隔离,直到事务完成为止,才能看到事务的执行结果。
  • 隔离性相关的技术有并发控制、可串行化、锁等。当多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

**4.持久性:**Durable

  • 持久性(Durability)是指事务一旦提交,其对数据库的修改就是永久性的。需要注意的是,事务的持久性不能做到百分百的持久,只能从事务本身的角度来保证永久性,而一些外部原因导致数据库发生故障,如硬盘损坏,那么所有提交的数据可能都会丢失。
3.并发事务引起的问题

1.脏读:

一个事务读取到另个事务还没有提交的数据

2.不可重复读:

一个事务分两次读取某个数据,前后两次读取数据不一致

3.幻读:

一个事务读取某个数据时,并没有该数据,但插入时发现已经存在

4.事务的隔离级别

1.查看事务隔离级别

# 查看全局隔离级
SELECT @global.transaction_isolation;
# 查看当前会话中的隔离级
SELECT @@session.transaction_isolation;
# 查看下一个事务的隔离级
SELECT @@transaction_isolation;

2.设置事务隔离级别

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

3.读未提交(脏读)

READ UNCOMMITTED 是事务中最低的级别,在该级别下的事务可以读取到其他事务中未提交的数据,这种读取的方式也被称为脏读(Dirty Read)。简而言之,脏读是指一个事务读取了另外一个事务未提交的数据。

4.读已提交(不可重复读)

READ COMMITTED 是大多数 DBMS (如 SQL Server、Oracle) 的默认隔离级,但不包括MySQL。

在该隔离级下只能读取其他事务已经提交的数据,避免了脏读数据的现象。但是在该隔离级别下,会出现不可重复读(NON-REPEATABLE READ)的问题。

5.可重复读

REPEATABLE READ 是MySQL的默认事务隔离级,它解决了脏读和不可重复读的问题,确保了同一事务的多个实例在并发读取数据时,会看到同样的结果。但在理论上,该隔离级会出现幻读(PHANTOM READ)的现象。

幻读又被称为虚读,是指在一个事务内两次查询中数据条数不一致,幻读和不可重复读有些类似,同样发生在两次查询过程中。不同的是,幻读是由于其他事务做了插入记录的操作,导致记录数有所增加。不过,MySQL的InnoDB存储引擎通过多版本并发控制机制解决了幻读的问题。

6.串型化

SERIALIZABLE 是最高级别的隔离级,它在每个读的数据行上加锁,使之不会发生冲突,从而解决了脏读、不可重复读和幻读的问题。但是由于加锁可能导致超时(Timeout) 和 锁竞争(Lock Contention)现象,因此 SERIALIZABLE 也是性能最低的一种隔离级。除非为了数据的稳定性,需要强制减少并发的情况时,才会选择此种隔离级。

二.存储引擎

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表而不是基于库的,所以存储引擎也可以被称为表引擎。 默认存储引擎是InnoDB

-- 查询建表语句
show create table account;
-- 建表时指定存储引擎
CREATE TABLE 表名(
    ...
) ENGINE=INNODB;
-- 查看当前数据库支持的存储引擎
show engines;

三.索引(index)

1.优缺点

优点:

  • 提高数据检索效率,降低数据库的IO成本
  • 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗

缺点:

  • 索引列也是要占用空间
  • 索引大大提高了查询效率,但降低了更新的速度,比如 INSERT、UPDATE、DELETE

2.存储:

用二叉树和红黑树会有层级越深,检索速度变慢缺点。

B-Tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BaIZGBi8-1682567995392)(/Users/zhangyujie/Library/Application Support/typora-user-images/image-20230426084842405.png)]

B+Tree

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ACtFdQq2-1682567995392)(/Users/zhangyujie/Library/Application Support/typora-user-images/image-20230426084924139.png)]

与 B-Tree 的区别:

  • 所有的数据都会出现在叶子节点
  • 叶子节点形成一个单向链表

MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的 B+Tree,提高区间访问的性能。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Th2cyTR-1682567995397)(/Users/zhangyujie/Library/Application Support/typora-user-images/image-20230426085014600.png)]

Hash

哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ivnsjvDJ-1682567995399)(https://dhc.pythonanywhere.com/media/editor/Hash索引原理图_20220317143226150679.png)]

特点:

  • Hash索引只能用于对等比较(=、in),不支持范围查询(betwwn、>、<、…)
  • 无法利用索引完成排序操作
  • 查询效率高,通常只需要一次检索就可以了,效率通常要高于 B+Tree 索引

存储引擎支持:

  • Memory
  • InnoDB: 具有自适应hash功能,hash索引是存储引擎根据 B+Tree 索引在指定条件下自动构建的
  1. 为什么 InnoDB 存储引擎选择使用 B+Tree 索引结构?
  • 相对于二叉树,层级更少,搜索效率高
  • 对于 B-Tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针也跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低
  • 相对于 Hash 索引,B+Tree 支持范围匹配及排序操作
分类
分类含义特点关键字
主键索引针对于表中主键创建的索引默认自动创建,只能有一个PRIMARY
唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE
常规索引快速定位特定数据可以有多个
全文索引全文索引查找的是文本中的关键词,而不是比较索引中的值可以有多个FULLTEXT

在 InnoDB 存储引擎中,根据索引的存储形式,又可以分为以下两种:

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

聚集索引选取规则:

  • 如果存在主键,主键索引就是聚集索引
  • 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引
  • 如果表没有主键或没有合适的唯一索引,则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NvAF4Eq1-1682567995407)(https://dhc.pythonanywhere.com/media/editor/演示图_20220319215403721066.png)]

语法

创建索引:

idx_tableName_colName

CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name (index_col_name, ...);

如果不加 CREATE 后面不加索引类型参数,则创建的是常规索引

查看索引:

SHOW INDEX FROM table_name;

删除索引:

DROP INDEX index_name ON table_name;

3.最左前缀法则

如果索引关联了多列(联合索引),要遵守最左前缀法则,最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列
如果跳跃某一列,索引将部分失效(后面的字段索引失效)。如果最左侧不存在则索引失效。

联合索引中,出现范围查询(<, >),范围查询右侧的列索引失效。可以用**>=或者<=来规避索引失效问题**。


4.SQL提示

是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

例如,使用索引:

explain select * from tb_user use index(idx_user_pro) where profession="软件工程";
不使用哪个索引:
explain select * from tb_user ignore index(idx_user_pro) where profession="软件工程";`
必须使用哪个索引:
explain select * from tb_user force index(idx_user_pro) where profession="软件工程";`

use 是建议,实际使用哪个索引 MySQL 还会自己权衡运行速度去更改,force就是无论如何都强制使用该索引。


5.索引失效情况
  1. 在索引列上进行运算操作,索引将失效。如:explain select * from tb_user where substring(phone, 10, 2) = '15';
  2. 字符串类型字段使用时,不加引号,索引将失效。如:explain select * from tb_user where phone = 17799990015;,此处phone的值没有加引号
  3. 模糊查询中,如果仅仅是尾部模糊匹配,索引不会是失效;如果是头部模糊匹配,索引失效。如:explain select * from tb_user where profession like '%工程';,前后都有 % 也会失效。
  4. 用 or 分割开的条件,如果 or 其中一个条件的列没有索引,那么涉及的索引都不会被用到。
  5. 如果 MySQL 评估使用索引比全表更慢,则不使用索引

四.多表查询

1.内连接

隐式内连接:

SELECT 字段列表 FROM1,2 WHERE 条件 ...;

显式内连接:

SELECT 字段列表 FROM1 [ INNER ] JOIN2 ON 连接条件 ...;
2.外连接查询

左外连接:
查询左表所有数据,以及两张表交集部分数据

SELECT 字段列表 FROM1 LEFT [ OUTER ] JOIN2 ON 条件 ...;

相当于查询表1的所有数据,包含表1和表2交集部分数据

右外连接:
查询右表所有数据,以及两张表交集部分数据

SELECT 字段列表 FROM1 RIGHT [ OUTER ] JOIN2 ON 条件 ...;
image-20230426105144668
3.自连接查询

当前表与自身的连接查询,自连接必须使用表别名

语法:

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

自连接查询,可以是内连接查询,也可以是外连接查询

4.多表联合查询 union, union all

把多次查询的结果合并,形成一个新的查询集

语法:

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

注意事项

  • UNION ALL 会有重复结果,UNION 不会
  • 联合查询比使用or效率高,不会使索引失效
5.子查询

SQL语句中嵌套SELECT语句,称谓嵌套查询,又称子查询。

SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

子查询返回的结果是单个值(数字、字符串、日期等)。
常用操作符:- < > > >= < <=

根据子查询位置可分为:

  • WHERE 之后
  • FROM 之后
  • SELECT 之后
1.标量子查询
		-- 查询销售部所有员工
    select id from dept where name = '销售部';
    -- 根据销售部部门ID,查询员工信息
    select * from employee where dept = 4;
    -- 合并(子查询)
    select * from employee where dept = (select id from dept where name = '销售部');
    -- 查询xxx入职之后的员工信息
    select * from employee where entrydate > (select entrydate from employee where name = 'xxx');
2.列子查询

返回的结果是一列(可以是多行)。

常用操作符:

操作符描述
IN在指定的集合范围内,多选一
NOT IN不在指定的集合范围内
ANY子查询返回列表中,有任意一个满足即可
SOME与ANY等同,使用SOME的地方都可以使用ANY
ALL子查询返回列表的所有值都必须满足
3.行子查询

返回的结果是一行(可以是多列)。
常用操作符:=, <, >, IN, NOT IN

例子:

-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');
4.表子查询

返回的结果是多行多列
常用操作符:IN

例子:

-- 查询与xxx1,xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in 
(select job, salary from employee where name = 'xxx1' or name = 'xxx2');

-- 查询入职日期是2006-01-01之后的员工,及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e 
left join dept as d on e.dept = d.id;

五.SQL优化

查询某个参数时 select@@…

1.普通插入:
  1. 采用批量插入(一次插入的数据不建议超过1000条)
  2. 手动提交事务
  3. 主键顺序插入
2.大批量插入:

​ 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令插入。

# 客户端连接服务端时,加上参数 
--local-infile(这一行在bash/cmd界面输入)
mysql --local-infile -u root -p
# 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关set global local_infile = 1;
select @@local_infile;
# 执行load指令将准备好的数据,加载到表结构中												字段分隔符									行分隔符
load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
3.主键优化

数据组织方式:在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(Index organized table, IOT)

**页分裂:**页可以为空,也可以填充一般,也可以填充100%,每个页包含了2-N行数据(如果一行数据过大,会行溢出),根据主键排列。
**页合并:**当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。当页中删除的记录到达 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前后)看看是否可以将这两个页合并以优化空间使用。

MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或创建索引时指定

主键设计原则:

  • 满足业务需求的情况下,尽量降低主键的长度
  • 插入数据时,尽量选择顺序插入,选择使用 AUTO_INCREMENT 自增主键
  • 尽量不要使用 UUID 做主键或者是其他的自然主键,如身份证号 (占用内存)
  • 业务操作时,避免对主键的修改
4.Order By 优化
  1. Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区 sort buffer 中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序
  2. Using index:通过有序索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高

如果order by字段全部使用升序排序或者降序排序,则都会走索引,但是如果一个字段升序排序,另一个字段降序排序,则不会走索引,

explain的extra信息显示的是Using index, Using filesort

如果要优化掉Using filesort,则需要另外再创建一个索引,

如:create index idx_user_age_phone_ad on tb_user(age asc, phone desc);

此时使用select id, age, phone from tb_user order by age asc, phone desc;会全部走索引

总结:

  • 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)
  • 如果不可避免出现filesort,大数据量排序时,可以适当增大排序缓冲区大小 sort_buffer_size(默认256k)

5.Group By 优化
  • 在分组操作时,可以通过索引来提高效率
  • 分组操作时,索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat

则句式可以是select ... where profession order by age,这样也符合最左前缀法则

6.Limit 优化

常见的问题如limit 2000000, 10,此时需要 MySQL 排序前2000000条记录,但仅仅返回2000000 - 2000010的记录,其他记录丢弃,查询排序的代价非常大。
优化方案:一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

例如:

-- 此语句耗时很长
select * from tb_sku limit 9000000, 10;
-- 通过覆盖索引加快速度,直接通过主键索引进行排序及查询
select id from tb_sku order by id limit 9000000, 10;
-- 下面的语句是错误的,因为 MySQL 不支持 in 里面使用limit-- 
select * from tb_sku where id in (select id from tb_sku order by id limit 9000000, 10);
-- 通过连表查询即可实现第一句的效果,并且能达到第二句的速度
select * from tb_sku as s, (select id from tb_sku order by id limit 9000000, 10) as a where s.id = a.id;
7.Count 优化

MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count() 的时候会直接返回这个数,效率很高(前提是不适用where);
InnoDB 在执行 count(
) 时,需要把数据一行一行地从引擎里面读出来,然后累计计数。
优化方案:自己计数,如创建key-value表存储在内存或硬盘,或者是用redis

count的几种用法:

  • 如果count函数的参数(count里面写的那个字段)不是NULL(字段值不为NULL),累计值就加一,最后返回累计值
  • 用法:count(*)、count(主键)、count(字段)、count(1)
  • count(主键)跟count()一样,因为主键不能为空;count(字段)只计算字段值不为NULL的行;count(1)引擎会为每行添加一个1,然后就count这个1,返回结果也跟count()一样;count(null)返回0

各种用法的性能:

  • count(主键):InnoDB引擎会遍历整张表,把每行的主键id值都取出来,返回给服务层,服务层拿到主键后,直接按行进行累加(主键不可能为空)
  • count(字段):没有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加;有not null约束的话,InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加
  • count(1):InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一层,放一个数字 1 进去,直接按行进行累加
  • count(*):InnoDB 引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加

按效率排序:count(字段) < count(主键) < count(1) < count(),所以尽量使用 count()

8.update优化(避免行锁升级为表锁)

InnoDB 的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁。

如以下两条语句:
update student set no = '123' where id = 1;

这句由于id有主键索引,所以只会锁这一行;
update student set no = '123' where name = 'test';

这句由于name没有索引,所以会把整张表都锁住进行数据更新,解决方法是给name字段添加索引。

  • 5
    点赞
  • 15
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
CSDN是一个技术交流平台,里面有许多关于各种编程语言和数据库的学习资料和笔记。而MySQL是其中一种常用的关系型数据库管理系统,也是开放源代码软件之一。 在CSND上,MySQL笔记是指关于MySQL数据库的学习和使用的笔记和教程。这些笔记包含了MySQL数据库的基本概念、安装配置、SQL语句的使用、数据表的设计和管理、索引使用、数据备份和恢复等方面的知识。学习MySQL笔记可以帮助开发者更好地理解和应用MySQL数据库MySQL笔记主要可以分为以下几个方面来介绍和学习: 1. 数据库基础知识:学习数据库的基本概念和原理,了解关系型数据库的特点以及MySQL的特点。 2. 安装和配置:学习如何在不同操作系统上安装和配置MySQL数据库,包括设置用户名、密码和端口等。 3. SQL语句的使用:学习SQL语句的基本语法和常用命令,包括查询、插入、更新、删除等操作。 4. 数据表的设计和管理:学习如何设计和创建数据表,包括选择适当的数据类型、设置主键和外键等。 5. 索引的使用:学习如何创建和使用索引来提高查询效率和数据访问速度。 6. 数据备份和恢复:学习如何进行MySQL数据库的数据备份和恢复,包括全量备份和增量备份等。 通过学习MySQL笔记,开发者可以掌握MySQL数据库的基本操作和高级功能,提高数据管理和查询的效率。此外,还可以了解MySQL数据库的优化技巧和性能调优方法,提升数据库的性能和稳定性。 总之,通过CSDN上的MySQL笔记,开发者可以系统地学习和掌握MySQL数据库的相关知识,从而更好地应用于实际的项目开发中。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值