Mysql初识
2021-09-16 22:36:39 0 举报AI智能生成
包含知识点：Mysql索引类型，sql执行过程，优化方式，事务隔离级别
作者其他创作
大纲/内容
SQL的执行过程
客户端
服务端
连接器（权限校验）
缓存层（queryCache）
词法分析（分析sql语法是否正确）
优化器（执行计划生成索引的比较和选择）
执行器（根据执行计划，使用对应的索引查询sql）
存储引擎层（读取/保存数据至磁盘）
MyISAM引擎
Innodb引擎
MySQL事务
事务的4大特性
原子性:对于数据的一组操作，要么同时失败，要么全部成功
一致性：事务的开始和结束，数据都必须保持一致的状态。
（转帐前后，数据总额不能发生变化）
（转帐前后，数据总额不能发生变化）
隔离性：多个用户并发操作同一张表时，为每个用户开启一个事务，
各个事务之间保持隔离，互相不影响。
各个事务之间保持隔离，互相不影响。
持久性：事务完成之后，对于数据的操作应该是永久保存的，
即使出现系统故障，也应该保存
即使出现系统故障，也应该保存
事务并发带来的问题
1、脏写：两个事务读取到同一条数据记录后，进行修改， 最后更新的会覆盖掉之前更新的记录
2、脏读：事务A读取到了事务B已经修改，但是未提交的数据，并进行修改，不符合一致性要求
3、不可重复读：事务A的同一条查询语句，在不同时刻出现了不同的结果，不符合隔离性
4、幻读：事务A读取到了事务B新增的数据，感觉出现了幻觉一样，不符合隔离性
事务的隔离级别（由低到高）
1、读未提交（read uncommited),导致问题： 脏读，不可重复读，幻读
2、读已提交（read commited）解决脏读，导致不可重复读，幻读
3、可重复读（repeatable-read）,解决的脏读，不可重复读，
导致幻读（MySQL默认事务隔离级别）
导致幻读（MySQL默认事务隔离级别）
4、串行化：Serializable，事务隔离级别最高，所有事务只能一个一个执行，
解决上面所有问题，不推荐使用
解决上面所有问题，不推荐使用
MySQL的锁机制
从性能方面划分
1、悲观锁（读锁，写锁）
2、乐观锁（MVCC机制）
从数据库操作方面划分
读写锁都属于悲观锁
读写锁都属于悲观锁
1、读锁（共享锁，S锁，Shared），针对同一份数据，
针对同一份数据，多个读操作之间互相不影响
针对同一份数据，多个读操作之间互相不影响
2、写锁（互斥锁，X锁，Exclusive），当前写操作未完成的时，会阻塞其他的写读和写操作
从对数据操作的颗粒度划分
1、表锁
表锁：每次锁住一张表，开销小，加锁快，不会产生死锁；锁的颗粒度大，并发度低，一般在数据表迁移的时候使用表锁
2、行锁
行锁：每次锁住一行数据，开销大，加锁慢；会产生死锁，锁的颗粒度小，并发度高。
两种引擎对于锁的使用
1、Innodb支持事务，支持行级锁（MyISAM都不支持）
2、MyISAM引擎对于select 语句会自动给涉及的表加读锁，update，insert，delete语句会给表加写锁
3、Innodb在select时不会加锁(非串行化隔离级别)，不会加锁，但是update，insert，delete操作都会加行锁
总结：简言之：就是读锁不会阻塞读锁，写锁会阻塞读锁和写锁
存储引擎及索引
Innodb
主键是聚集索引(叶子结点存放数据记录)，索引维护了一颗排好序的B+树
二级索引是非聚集索引(叶子节点存储的是主键值，所以查询时要进行回表)
多个字段的联合索引：多个字段共同排序维护一颗B+树
表结构对应两个文件，.frm(表结构文件)，.ibd(数据和索引文件)
innodb引擎，数据和索引在同一个文件中
MyISAM
表结构对应三个文件：.frm(表结构)， .MyD(数据文件) .MyI(索引文件)
非聚集索引，叶子节点存储的是数据行的地址值，索引文件和数据文件是分离的
MySQL优化
分页查询优化
select * from employees limit 10000, 10;
其实是取出10010条，舍弃前面数据
查询后面的数据时，效率低下
优化、 使用范围查询优化：select * from employees where id > 10000 limit 10;（使用条件苛刻）
优点：使用索引，效率提升
缺点：表中有数据删除时，则查询数据结果和普通分页不一致
select * from employees order by name limit 10000, 10;
使用二级索引进行排序，二级索引叶子结点是主键，
该查询会有多次回表，查询速度不如全表扫描
该查询会有多次回表，查询速度不如全表扫描
优化：思路：使用二级索引排序时，让返回的值尽可能少，
可以先查出主键，然后再根据主键分页排序
可以先查出主键，然后再根据主键分页排序
select * from employees e inner join (select id from employees order by name limit 10000, 10) d on d.id = e.id
join关联优化
NLJ（Nested-Loop-Join）循环嵌套连接算法
特点：两表连接的时候，关联条件使用索引，扫描行数较少(约等于两表行数之和)
小表称为驱动表，大表称为被驱动表，先查询驱动表，利用小表驱动大表
inner join， mysql自己会选择驱动表，不一定左边的就是驱动表
left join 左边的是驱动表，右边是被驱动表
right join 右边是驱动表， 左边是被驱动表
BNL（Block-Nested-Loop-Join）基于块的嵌套循环链接
特点：两表连接的时候，关联条件没有使用索引，
将驱动表的查询结果放到join buffer 中，然后扫描被驱动表，
把被驱动表的每一条记录和join buffer中的数据做对比
将驱动表的查询结果放到join buffer 中，然后扫描被驱动表，
把被驱动表的每一条记录和join buffer中的数据做对比
查询次数约等于两表之和，但join_buffer中数据是无序的，所以比较的次数约为两表数据条数之和
总结：
1、关联字段加索引：在两表关联查询的时候，在关联条件上加索引，以使查询尽可能使用NLJ算法
2、小表驱动大表：如果写sql时，如果明确知道哪张表小，可以直接使用 straight join 固定连接驱动方式
3、straight join： select * from a straight join b on a.id = b.id; 则意味着a表作为驱动表，b表作为被驱动表
注意：1 、straight join 只适用于 inner join, 对于left join， right join 其实已经指定了驱动方式
2、使用该写法时一定要明确两表的数据量关系，即明确大表和小表。
3、对于大表和小表的定义： 指的是两张表根据各自的条件进行过滤后的数据量大小，而不是原始数据量
in 和 exist 优化（原则： 小表驱动大表）
1、select * from A where id in (select id from B)
当B表的数据量小于A表的时候，使用in 查询（先查询B表）
2、select * from A where (select 1 from B where b.id = a.id)
当A表的数量小于B表的时候，用exist，先查询A表
注意： exist(subquery),中的查询结果只有true 和false，因此使用时select 1 和select * 没有区别，在执行过程中，会忽略select列
count(*)优化
对于查询表的行数，count(*)≈count(1)>count(name)>count(id)
前提条件name使用了二级索引，二级索引小于主键索引
count(*) ≈count(1) > count(id) > count(name)
name字段没有使用索引
这里我们默认的联合索引是（`name`, `age`, `position`）
Sql的执行过程以及buffer pool的作用
0 条评论
下一页
