索引树的高度、执行计化、事务处理的四项特征

十三、索引树高度

1、表的数据量

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

解决：可以使用分表（横切，竖切），分库，增加缓存，解决数据量大，查询慢的问题

2、索引键值过长

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

解决：前缀索引（截取前5个长度）

3、数据类型

char（定长） varchar（变长）从开辟空间速度来看，char快

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

（1）避免使用select * ，不确定表大小的时候，使用count（*）查一下数据

（2）尽量使用数据类型较小的字段做索引

（3）重复值少的，区分度高的字段索引，性别这样的字段不要做索引

（4）在多表查询时使用join，尽量少的使用子查询

十四、执行计划分析

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索引范围扫描（注意点：范围太大，不能命中索引）

​ 1）普通情况

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  (去掉左边的%)

​ 2）对in或or语句进行优化

​ 优化时：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条件

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时，实际用到的索引是谁

6、key_len

​ 判断联合索引覆盖的长度（通过字节数可以判定出到底触发了哪些联合索引字段）

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

​ utf8通常情况下，中文1个字符占用三个字节，字母占用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

十五、事务处理的四项特征ACID

A.原子性:

​ 同一个事务中执行多条sql语句，要么全部成功，要么直接回滚，作为一个完整的整体，不能再继续分隔得最小个体

C.一致性：

​ a，i，d 都是为了保证数据的一致性才提出来的，比如约束，键在插入数据时，必须按照要求插入，保证规则上的一致性

​ 上升到事务中，如果出现意外导致数据不统一，例如脏读，幻读，不可重读，最终要保证数据是一致的

​ 上升到主从数据库，主数据库增删改，从数据库也要进行同步改变，保证数据的一致性；

I.隔离性：

​ lock + isolation锁，来处理事务的隔离级别

​ 一个事务和另外一个事务工作过程中彼此独立隔离

D.持久性：

​ 把数据写到磁盘上，保证数据持久化存储不丢失

隔离性：隔离级别

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

​ 不可重读：前后多次读取，结果数据内容不一样（同一个会话里，在不修改的情况下，永远只看到同样的一份数据）

​ 幻读：前后多次读取，结果数据的总量不一样

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

查询当前mysql的隔离级别（默认是RR）

select @@tx_isolation;

查询是否自动提交数据

select @@autocommit；

修改mysql配饰文件

找到配置文件my.ini文件修改配置

更改隔离级别

transaction_isolation = READ-UNCOMMITTED

不让系统自动提交数据

autocommit = 0

重启mysql

net start mysql
net stop mysql

脏读

READ-UNCOMMITTED
先去调账设置，重启mysql，尝试在一个窗口里通过事务，更改一条数据，开启另外一个窗口尝试读取，会出现问题

不可重复读

窗口1
begin；
update t1 set k1="abc" where id = 1;
select * from t1;
commit;
窗口2
select * from t1；数据也跟着改了，这就是不可重读

幻读

窗口1
begin；
insert into t1 values(4,'c',50);
select * from t1;
commit;
窗口2
select * from t1; 数量也跟着增加了，这就是幻读

通过二次提交commit，可以让多用户同步数据

commit;

事务应用的计数

（1）RR级别下，解决不可重读，使用mvcc技术，生成最新的mysql的系统备份（快照），然后读取快照
（2）RR级别下，解决幻读，gap间隙锁 next-lock下一键锁