执行计划分析&事务处理

最新推荐文章于 2023-03-28 15:00:07 发布

lempoo

最新推荐文章于 2023-03-28 15:00:07 发布

阅读量187

点赞数

本文链接：https://blog.csdn.net/lempoo/article/details/106944256

版权

### part1 索引树高度

1.表的数据量

数据量越大,树的高度就会变高,理论上三层索引树的高度最为理想,可以支持百万级别的数据量
解决: 可以使用分表(横切,竖切),分库,增加缓存,解决数据量大,查询慢

2.索引键值过长

该索引字段存储数据太大,每个叶子节点最大存储16k,超过这个范围会新增加叶子节点和分支节点
解决:前缀索引(截取前5个长度)

3.数据类型

char(定长) varchar(变长) 从开辟空间速度来看,char快
从数据结构上来看,varchar更为合理


(1) 避免使用select *,不确定表大小的时候,使用count(*) 查一下数据
(2) 尽量使用数据类型较小的字段做索引
(3) 重复值少的,区分度高的字段索引,性别这样的字段不要做索引
(4) 在多表查询时使用join,尽量少的使用子查询

### part2 执行计划分析

“”“desc/explain”""

执行计划: 在一条sql执行之前,制定执行的方案

desc select * from s1;

1.select_type

simple 代表的是简单查询(单表查询,不包括子查询,union)
primary sql嵌套中的主查询(最外层)
subquery sql嵌套中的子查询(最里面)
derived 衍生查询(把子查询结果作为一张临时表)

2.table

在多表或者子查询时候,通过table分析出问题的表是谁.

3.type

# 显示执行计划的类型,优先级从低到高如下, 优化时,至少达到range 或者 ref 级别
all < index < range < ref < eq_ref < const < system

# 1.all 全表扫描 (不走索引) 慢查询
(1)在大范围内查询 > < >= <= != between and  in like ...
(2)where条件中有计算,有函数
(3)数据类型不匹配
(4)拼接条件使用or

# 2.index 全索引扫描
"""扫描整个索引树,才能获取到所有数据,这样的索引失去意义"""
desc select count(*) from s1;

# 3.range 索引范围扫描(注意点:范围太太,不能命中索引)
desc select * from s1 where id < 10;      # type = range
desc select * from s1 where id < 1000000; # type = all
desc select * from s1 where id between 1 and 10;  # type => range
desc select * from s1 where id between 1 and 1000000; # type => all
desc select * from s1 where email like "%w%"; # type => all
desc select * from s1 where email like "w%";  # type => range  (去掉左边的%就可以触发索引)
"""如果范围过大,不能命中索引,如果范围适当,可以命中索引"""
在数据较大时候.要先count(*)  在后在执行计划分析,看是执行的是什么范围(是否命中索引类型,因为索引有大范围和小范围,会使查询速度变快或者变慢,至少range以上的类型),如果小,就执行,大了就取一定的区间找

# 对in或者or这样的语句进行优化
desc select * from s1 where id in (1,2);
"""优化:union all(单纯的合并) 比 union (有去重,相同的两张表合并,有去重范围会扩大)速度快, union在合并数据之后,多一步去重操作"""
将两张表联表
desc select * from s1 where id = 1
union all
select * from s1 where id =1 ;

desc select * from s1 where id = 1
union
select * from s1 where id = 1;

# 优化or条件(有or不会命中索引)
desc select * from s1 where id = 10 or name = "aaaaa"

desc select * from s1 where id = 10
union all
select * from s1 where name = 'aaaaa';

# 4.ref 普通索引查询(非唯一)
desc select * from s1 where email = "xboyww10@oldboy";
desc select * from s1 where id = 10; # 此时id设置是普通索引

# 5.eq_ref 唯一性索引(联表)
"""要求: 应用在多表联查中,被关联的字段需要主键或者唯一,表之间的关系为一对一并且数据条数相同"""
desc select student1.age from student1,class1 where student1.class_id = class1.id
alter table class1 add primary key(id);
delete from student1 where id = 3;

# 6.const:主键或者唯一索引(单表)
""" 针对于primary key  和 unique 索引等值查询 """
desc select * from class1 where id = 1 # type => const
desc select * from class1 where id > 1 # type => range

# 7.system(了解)
"""只有一条数据的系统表"""

4.possible_keys : 执行sql时,可能用到的索引是谁

5.key : 执行sql时,实际用到的索引是谁

show index from s1; # 展现所有的索引

6.key_len : 判断联合索引覆盖的长度(通过字节数可以判定出到底触发了哪些联合索引字段)

在没有not null 约束的时候,默认预留一个字节,标记是空或者非空
utf8 通常情况下,中文1个字符占用3个字节,字母占用1个字节,极个别的生僻字占4个字节
varchar 每次存储数据的时候,系统底层默认会额外预留2个字节
		  有not null(不为空)    没有not null(可为空)
tinyint    1                     1+1
int        4					 4+1
char(5)    5*3				   	 5*3+1
varchar(5) 5*3 + 2				 5*3+2+1


# 把数据表中的数据导入
+------+----+------+----+------+----+
| n1   | n2 | n3   | n4 | n5   | n6 |
+------+----+------+----+------+----+
|    1 |  2 | a    | b  | aa   | bb |
|    1 |  2 | a    | b  | aa   | bb |
|    1 |  2 | a    | b  | aa   | bb |
|    2 |  2 | a    | b  | cc   | dd |
+------+----+------+----+------+----+
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| n1    | int(11)    | YES  | MUL | NULL    |       |
| n2    | int(11)    | NO   |     | NULL    |       |
| n3    | char(5)    | YES  |     | NULL    |       |
| n4    | char(5)    | NO   | MUL | NULL    |       |
| n5    | varchar(5) | YES  |     | NULL    |       |
| n6    | varchar(5) | NO   |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
n1 -> 5B  n2 -> 4B n3 ->16B   5+4+16 = 25
在对条联合表的时候,以最左作为索引键,在判断是否触发的时候,首先看最左键是否重复(左前缀原则),如果最左键重复,触发不了,如果没有重复,可以触发,必须有最左键(而且是相连的),没有最左键就不管重复不重复都触发不了
desc select * from t100 where n1 = 2 and n2 = 2 and n3 ="a"; # 命中n1 n2 n3
desc select * from t100 where n1 = 1 and n2 = 2 and n3 ="a"; # 如果有重复数据,不会触发联合索引
desc select * from t100 where n1 = 1 and n2 = 2 ;# 如果有重复数据,不会触发联合索引
desc select * from t100 where n1 = 2 and n2 = 2 ;# n1 -> 5B  n2 -> 4B 命中n1 , n2
desc select * from t100 where n1 = 2;# n1 -> 5B 命中n1
desc select * from t100 where n1 = 2 and n3 = "a"; # n1 -> 5B 命中n1 没有命中n3
desc select * from t100 where n2 = 2 and n3 = "a"; # 符合最左前缀原则,在没有n1的情况下不能触发;

# 联合索引: index(a,b,c) -> a , ab , abc 创建了三组索引 ,符合最左前缀原则,第一个字段必须存在才能触发;

### part2 事务处理的四项特征 ACID

A.原子性:
同一个事务中执行多条sql语句,要么全部成功,要么直接回滚,作为一个完整的整体,不能再继续分隔的最小个体

C.一致性:
a,i,d 都是为了保证数据的一致性才提出来的,比如约束,键在插入数据时,必须按照要求插入,保证规则上的一致性,
上升到事务中,如果出现意外导致数据不统一,例如脏读,幻读,不可重读,最终要保证数据是一致的
上升到主从数据库,主数据库增删改,从数据也要进行同步改变,保证数据的一致性;

I.隔离性:
lock + isolation锁,来处理事务的隔离级别
一个事务和另外一个事务工作过程中彼此独立隔离

D.持久性:
把数据写到磁盘上,保证数据持久化存储不丢失.

隔离性 : 隔离级别

脏读 : 没提交的数据被读出来了
不可重读 : 前后多次读取,结果数据内容不一样(同一个会话里,在不修改的情况下,永远只看到同样的一份数据)
幻读 : 前后多次读取,结果数据的总量不一样

RU : 读未提交 : 脏读,不可重读,幻读 READ-UNCOMMITTED
RC : 读已提交 : 防止脏读,会出现不可重读和幻读 READ-COMMITTED
RR : 可重复读 : 防止脏读,不可重读 ,可能会出现幻读 REPEATABLE-READ
SR : 可串行化 : 防止一切(但是会把异步并发的程序变成同步程序,不能并发,性能差)