mysql横切竖切_mysql进阶优化2---day41

#### part1 索引树高度

#1.表的数据量

数据量越大,树的高度就会变高,理论上三层索引树的高度最为理想,可以支持百万级别的数据量

解决:可以使用分表(横切,竖切),分库,增加缓存,解决数据量大,查询慢#2.索引键值过长

该索引字段存储数据太大,每个叶子节点最大存储16k,超过这个范围会新增加叶子节点和分支节点

解决:前缀索引(截取前5个长度)#3.数据类型

char(定长) varchar(不定长) 从开辟空间速度来看,char快

从数据结构上来看,varchar更为合理

(1) 避免使用select *,不确定表大小的时候,使用count(*)查一下数据

(2) 尽量使用数据类型较小的字段作索引

(3) 重复值少的,区分度高的字段索引,性别这样的字段不要做索引

(4) 在多表查询时使用join,尽量少的使用子查询####part2 执行计划分析

'''desc/explain'''

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

desc select * froms1;#1.select type

simple 代表的是简单查询(单表查询,不包括子查询,union)

primary sql嵌套中的主查询(外层)

subquery sql嵌套中的子查询(里面)

derived 衍生查询(把子查询结果作为一张临时表)#2.table

在多表或者子查询时候,通过table分析出问题的表是谁#3.type#显示执行计划的类型,优先级从低到高，优化时，至少达到range或者ref级别

all < index

(1)在大范围内查询 > < >= <= != between and inlike ...

(2)where条件中与计算,有函数

(3)数据类型不匹配

(4)拼接条件使用or#2.index 全索引扫描

'''扫描整个索引树,才能获取到所有数据,这样的索引失去意义'''desc select count(*) froms1;#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(去掉左边%)

'''如果范围过大不能命中索引,如果范围适当,可以命中索引'''

#对应in或者or这样的语句进行优化

select * from s1 where id in (1,2,3);'''优化:union all 比 union速度快，union在合并数据之后,多一步去重操作'''desc select* from s1 where id = 1union all

select* from s1 where id = 1; #type = ref

desc select* from s1 where id = 1union

select* from s1 where id = 1; #type =ref但是多了一个all去重

#优化or条件

desc select * from s1 where id = 10 or name= "aaa"; #type=all

desc select * from s1 where id = 10union all

desc select* from s1 where name = "aaa";#4.ref 普通索引查询(非唯一)

desc select * from s1 where email = 'xboyww10@oldboy';

desc select* from s1 where id = 10; #此时id设置是普通索引

#5.eq_ref 唯一索引(联表)字段条数要一样才会触发

'''要求:应用在多表联查中,被关联的字段需要主键或者唯一,表之间的关系为一对一,并且数据条数相同'''desc select* from student1,class1 where student1.class_id =class1.id;

alter table class1 add primary key(id);

deletefrom 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;#展现s1表所有索引

#6.key_len:判断联合索引覆盖的长度 判断触发没有触发 看key_len

在没有not null 约束的时候,默认预留一个字节,标记是空或者非空

utf8 通过情况下.中文1个字符占用3个字节.字母占用1个字节,极个别的生僻字占用4个字节

varchar 每次存储数据的时候，系统底层默认会额外预留2个字节notnull(不为空) 没有not null(可为空)

tinyint1 1+1int4 4+1char(5) 5*3 5*3+1varchar(5) 5*3+2 5*3+2+1

#把数据表中的数据导入

n1-> 5B n2-> 4B n3->16B 5+4+16 = 25desc 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;#触发 9

desc select * from t100 where n1 =2;#触发 5 命中n1

desc select * from t100 where n1 =2 and n3="a"; #命中n1，最左前缀原则

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:持久性:

把数据写到磁盘上,保证数据持久化存储不丢失.#隔离性:隔离级别

脏读:没提交的数据被读出来了

不可重读:前后多次读取,结果数据内容不一样(同一个会话里,在不修改的情况下,永远只看到同样一份数据)

幻读:前后多次读取,结果数据的总量不一样

事务处理增加隔离级别4中级别

RU:读未提交的 :脏读,不可重读,幻读 READ-UNCOMMITTED

RC:读已提交的 :防止脏读,会出现不可重读和幻读 READ-COMMITTED

RR:可重复读 :防止脏读,不可重读,可能会出现幻读 REPEATABLE-READ

SR:可串行化 :防止一切(但是会把异步并发的程序变成同步程序,不能并发,性能差)#查询当前mysql的隔离级别,默认是RR

select @@tx_isolation;#查询是否自动提交数据

select @@autocommit; #1是提交成功#系统默认的隔离级别 RR

transaction_isolation= REPEATABLE-READ#防止系统自动提交

autocommit =0#修改mysql配置文件

D:\MySQL5.7\mysql-5.7.25-winx64\my.ini#更改隔离级别

transaction_isolation=READ-UNCOMMITTED#重启mysql

net stop mysql#在开启

net start mysql#1.脏读

先去调整设置，重复mysql，尝试在一个窗口里通过事务

更改一条数据，开启另外一个窗口尝试读取，会出现问题#2.不可重复读#窗口1

begin;

updata t1 set k1="abc" where id = 1;

select* fromt1;

commit;#窗口2

select * from t1;#数据也跟着改了是不可重读

#3.幻读#窗口1

begin;

insert into t1 values(4,"c",50);

select* fromt1;

commit;#窗口2

select * from t1; #数据也跟着增加了是幻读

#4.通过二次提交commit,可以让多用户同步数据

commit;#5.事务应用的技术(了解)

(1) RR级别下,解决不可重读,使用的是MVCC技术,生成最新的mysql系统备份(快照),然后读取快照

(2)RR级别下,解决幻读,gap 间隙锁, next-lock下一键锁