# ### 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（去掉左边%） '''如果范围过大不能命中索引,如果范围适当,可以命中索引''' #对应in或者or这样的语句进行优化 select * from s1 where id in (1,2,3); '''优化:union all 比 union速度快，union在合并数据之后,多一步去重操作''' desc select * from s1 where id = 1 union all select * from s1 where id = 1; #type = ref desc select * from s1 where id = 1 union 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 = 10 union 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); 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;#展现s1表所有索引 #6.key_len:判断联合索引覆盖的长度 判断触发没有触发 看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-> 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;#触发 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.7mysql-5.7.25-winx64my.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 * from t1; commit; #窗口2 select * from t1;#数据也跟着改了是不可重读 #3.幻读 #窗口1 begin; insert into t1 values(4,"c",50); select * from t1; commit; #窗口2 select * from t1; #数据也跟着增加了是幻读 #4.通过二次提交commit,可以让多用户同步数据 commit; #5.事务应用的技术(了解) (1) RR级别下,解决不可重读,使用的是MVCC技术,生成最新的mysql系统备份(快照),然后读取快照 (2)RR级别下,解决幻读,gap 间隙锁, next-lock下一键锁