mysql
2023-02-17 21:17:51 0 举报AI智能生成
mysql
作者其他创作
大纲/内容
索引的本质
索引是帮组mysql高效获取数据的排好序的数据结构
索引的数据结构
二叉树
红黑树
Hash表
B Tree
B-Tree
叶节点具有相同的深度，叶节点指针为空
所有索引元素不重复
节点中的数据索引，从左到右递增排序
B+Tree（B-Tree的变种）
非叶子节点不存储data只存储索引，节点可以存储更多的索引（每个节点16KB）
叶子节点包含所有的所有索引字段
叶子节点用指针链接，提高区间访问性能
索引实现
InnoDB（聚集）
表数据文件本身就是按照B+树组织的一个索引结构文件
聚集索引叶节点包含完整的数据记录
InnoDB必须建主键（隐式），并且推荐使用整型的自增型主键
非主键索引叶子节点存储的是主键值，是为了一致性和节省存储空间
图示
辅助索引树
主键索引树
最左前缀原理
MyISAM（非聚集）
索引文件和数据文件是分离的
Explain详解与索引
Explain工具
explain可以模拟优化器执行sql语句，分析查询语句或结构的性能瓶颈
在select前加explain关键字，mysql会在查询上设置一个标记，执行查询会返回执行计划信息，而不是执行这条sql
紧随其后通过show warnings命令可以得到优化后的查询语句
explain的两个变种
explain extended
相比于expalin多个filtered列。可以估算出将要和explain中前一个表进行连接的行数
explain partitions
相比于expalin多个partitions列。如果查询是基于分区表，会显示将访问的分区
explain中的列
id
id越大执行优先级越高，id相同从上往下执行
select_type
simple：简单查询
primary:复杂查询中最外层的select
subquery：包含在select中的子查询
derived：包含在from中的子查询
union
table
explain的一行正在访问哪个表
type
关联类型或访问类型。从优到差为：system>const>eq_ref>ref>range>index>ALL
possible_keys
可能使用哪些索引来查找
key
mysql实际使用哪个索引优化对该表的访问
key_len
mysql在索引里使用的字节数，通过这个值可以算出具体使用的索引中的哪些列
字符串
一个数字或字母占一个字节，一个汉字占3个字节
数字类型
tinyint：1字节
smallint：2字节
int：4字节
bigInt：8字节
时间类型
date：3字节
timestamp：4字节
datetime：8字节
ref
rows
mysql预估要读取检测的行数，并不是结果集的行数
Extra：一些额外信息
Using index
使用覆盖索引：所有的查询结果都是通过索引树拿到，不需要通过主键索引去主键索引树里去拿结果
Using where
使用where语句来处理结果，且查询到的列未被索引覆盖
Using index condition
查询的列不完全被索引覆盖，where条件中是一个前导列的范围
explain select * from film_actor where film_id>1;
Using temporary
mysql需要创建一张临时表来处理结果。出现这种结果一般需要进行优化，通过添加索引来优化;
explain select distinct name from actor;
通过对name字段添加查询索引
Using filesort
将使用外部排序而不是索引排序。也需要考虑使用索引
数据量较小时在内存排序，否则将在磁盘中进行排序
explain select * from actor order by name;
通过对name字段添加查询索引
Select tables optimized away
使用聚合函数（max、min...）来访问存在索引的某个字段
Mysql索引优化
建表语句
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
联合索引第一个字段用范围，不会走索引
EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描
强制走索引
EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';
虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高
覆盖索引优化
EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
index（ name,age,position ）会走索引
in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描。（在IN中的条件占总行数比例高时，会走全表扫描）
like KK% 一般情况都会走索引
索引下推（Index Condition Pushdown，ICP）
MySQL 5.6引入了索引下推优化
可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数
innodb引擎的表索引下推只能用于二级索引
like KK%其实就是用到了索引下推优化
Order by与Group by优化
mysql支持两种排序方式：index和filesort,Using index指mysql扫描索引本身完成排序。index效率高，filesort效率低
order by满足两种情况会使用using index
order by语句使用索引最左前缀
使用group by子句和order by子句条件组合 满足索引最左前缀
尽量在索引上完成排序，遵循索引建立时的最左前缀法则。（索引条件创建的顺序）
如果order by的条件不在索引列上，会产生filesort
能用覆盖索引，尽量使用覆盖索引
group by和order by类型，其实质都是先排序后分组，遵循索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序可以加上order by null禁止排序。
注意：where高于having，能学在where中的限定条件就不要用having去限定了
分页查询优化
误区
很多时候使用分页：select * from employees limit 10000,10;
看似只查询了10条记录，实际上时先读取了100010条记录，然后抛弃前100000条，然后读取到后边10条想要的记录
因此查询一张大表比较靠后的的数据时，执行效率非常低
优化
根据自增且连续的主键排序分页查询
走了索引，扫描行数也大大减少。执行效率增加。单使用场景较少，表数据删除后，主键空缺，导致结果不一致
EXPLAIN select * from employees where id > 90000 limit 5;
必须满足两个条件
主键自增且连续
结果是按照主键排序的
根据非主键字段排序的分页查询
让排序时返回的字段尽可能少
EXPLAIN select * from employees ORDER BY name limit 90000,5;
SELECT * FROM `employees` e INNER JOIN ( SELECT `id` from `employees` ORDER BY `name` LIMIT 90000,5)ed WHERE e.`id` = ed.id;
Join关联查询优化
常见的两种算法
嵌套循环连接算法 nested-loop join（NLJ）
一次一行循环的从第一张表（驱动表）中读取行，在这行中读取到关联字段，根据关联字段在另一张表（被驱动表）中取出满足条件的行，然后取出两张表的结果合集
先执行的是驱动表，后执行的是被驱动表
优化器一般会选择小表做驱动表，使用where条件过滤后，在跟被驱动表一起做关联查询。
使用inner join做关联查询，排在前边的表并不一定会是驱动表
KEY `idx_a` (`a`)
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
使用left join时，左表是驱动表，右表是被驱动表
使用right join时，右表时驱动表，左表是被驱动表
使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表
join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ
NLJ大致流程
从驱动表中读取一行数据（如果有过滤条件，从过滤结果集中取一行数据）
从第一步的数据中，取出关联字段，到被驱动表中查找
从被驱动表中取出的行，和驱动表中的结果集合并，返回给客户端
重复上述步骤
基于块的嵌套循环链接算法 Block Nested-Loop Join (BNL)
把驱动表的数据读取到join_buffer中，然后扫描被驱动表，把被驱动表中的每一行数据取出来和join_buffer中的数据做对比
b字段没有索引
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
BNL大致流程
把驱动表中数据放到join_buffer中
把被驱动表中的每一行取出来，和join_buffer中的数据最对比
返回满足join条件的数据
关联sql优化
关联字段加索引
mysql做join操作时尽量选择NLJ算法
驱动表因为要全部查询出来，所以过滤条件也尽量走索引
小表驱动大表
straight_join适用于join，并不适用于left join和right join
两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”
in和exsits优化
原则：小表驱动大表
select * from A where id in (select id from B)
B为驱动表，A为被驱动表
当B表的数据集小于A表的数据集时，in优于exists
select * from A where exists (select 1 from B where B.id = A.id)
当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
count()查询优化
字段有索引
count(*)≈count(1)>count(字段)>count(主键 id)
字段无索引
count(*)≈count(1)>count(主键 id)>count(字段)
show table status
查询出表总行数的估计值
Mysql事务与锁机制
msql事务
事务属性
原子性
一致性
隔离型
持久性
并发事务问题
脏写
最后的更新覆盖了由其它事务所做的更新
脏读
事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作
不可重复度
事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读
事务A读取到了事务B提交的新增数据，不符合隔离性
事务隔离级别
设置事务模式
读未提交：set tx_isolation='read-uncommitted';
读已提交：set tx_isolation='read-committed';
可重复读：set tx_isolation='repeatable-read';
串行化：set tx_isolation='serializable';
分支主题
锁机制
分类
性能
乐观锁
悲观锁
操作类型
读锁(悲观锁)
共享锁：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（悲观锁）
排它锁：当前写操作没有完成前，它会阻断其他写锁和读锁
数据操作粒度
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁
手动增加表锁
lock table table1 read(write),table2 read(write)
查询表上加过的锁
show open tables;
删除表锁
ublock tables;
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；
锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB与MYISAM的最大不同有两点：
InnoDB支持行级锁
InnoDB支持事务
间隙锁(Gap Lock)
事务A范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里,事务B禁止插入或修改任何数据
临键锁(Next-key Locks)
Next-Key Locks是行锁与间隙锁的组合。
行锁分析
show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
系统库锁相关数据表
查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
释放锁
kill trx_mysql_thread_id
查看锁等待详细信息
show engine innodb status\G;
SQL底层执行原理
分支主题
MVCC与BufferPool缓存机制
分支主题
0 条评论
下一页
