MYSQL的执行计划 事务处理 和 跑路

MYSQL

1. 执行计划

2. 事务处理

3. 跑路

# ### part1 索引树高度

# 1.表的数据行
    数据量越大,树的高度就会变高,理论上3层索引树高度可以支持百万级别数据量
    解决:可以使用分表,分库,数据库缓存,解决数据过大问题
# 2.索引键值过长
    该索引字段存储数据太大,每个叶子节点默认可以存储16k,超过范围增加叶子节点,
    解决:前缀索引 (截取前10个长度)
# 3.数据类型
    char(定长,多余数据用空格补位) varchar(变长) 从数据类型角度,char比varchar快
    从数据结构这个角度上看,varchar更加合理
    选择合理的类型;

# ### part2 执行计划分析
""" desc/explain + sql """
# 执行计划:在一条sql执行之前,制定执行的方案
    desc select * from s1; 

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

# 2.table
在多表,子查询的时候,关注出现问题的那张表是谁

# 3.type
    # 把执行计划的类型,优化级别从低到高,前提都有索引,至少达到range ,ref;
    all < index < range < ref < eq_ref < const < system
    
    # 1.all 全表扫描 (不走索引)
    (1)在大范围内查询 > < >= <= != between .. and  in not in like "%a%"
    (2)where条件中有计算,有函数
    (3)数据类型不匹配(隐式转换)
    (4)索引失效,数据信息过旧,在发生树状结构变更的时候,可能产生索引对不上实际数据的情况,没有命中索引.
    
    # 2.index 全索引扫描
    """扫描整个索引树,才能获取到数据,失去索引意义"""
    desc select count(*) from s1; # index 
    desc select * from s1 where email = "xboyww2@oldboy"; # all
    
    # 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
    """
    注意:where条件中,索引列和如下符号配合
     > < >= <= != between .. and  in not in like "%a%"
     如果范围过大,不能命中索引
     如果范围适当,可以命中索引     
    """
    # or 和 in 语句可以进行优化的,
    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 = 2;
    
    desc select * from s1 where id = 1 
    union  
    select * from s1 where id = 2;
    
    # 4.ref 普通索引查询(非唯一)
    desc select * from s1 where email = 'xboyww2@oldboy';
    
    # 5.eq_ref 唯一性索引(联表)
    """要求:应用在多表联查中,关联字段只能是主键或者唯一索引,表之间是1对1的关系并且数据条数相同,查询具体的某个带索引的字段"""
    desc select student1.class_id from student1,class1 where student1.class_id = class1.id
    alter table student1 add unique(class_id) ; # 为student1的关联字段 添加唯一索引
    alter table class1 add primary key(id);     # 为关联表的id添加主键
    delete from student1 where id = 3           # 让两个表之间的数据等长
    desc select student1.class_id from student1,class1 where student1.class_id = class1.id # 搜索的字段,必须是索引字段
    
    # 6.const:主键或者唯一索引(单表)
    """ 针对primary key 和 unique 索引等值查询"""
    desc select * from s1 where id = 1 # type => const
    desc select * from s1 where id > 1 # type => range
    
    # 7.system(了解):
    """只有一条数据的系统表 或  衍生表(子查询出来的临时表)只有一条数据主查询"""
    # create table ceshi111(id int , name varchar(10));
    # insert into ceshi111 values(1,"a");
    # alter table ceshi111 add primary key(id);
    # desc select * from ceshi111;
    # desc select id from (select id from ceshi111) as t where id = 1
    
# 4.possible_keys : 可能用到的索引是谁
    show index from s1;    
# 5.key           : 实际用到的索引是谁
    
# 6.key_len : 判断联合索引覆盖长度
    预留一个字节,在没有not null 约束的时候,加上一个字节,标记是空还是非空
    utf8 预留的最大字节数是4个字节,通常情况下,一个中文3个字节,一些个别生僻词4个字节存储
    varchar 每次数据存储的时候,系统底层要额外预留2个字节
              有not null   没有not null
    tinyint       1          1+1
    int         4          4+1
    char(5)     5*4        5*4+1
    varchar(5)  5*4+2       5*4+2+1
    
    create table t100(
    n1 int,
    n2 int not null,
    n3 char(5),
    n4 char(5) not null,
    n5 varchar(5),
    n6 varchar(5) not null,
    index index_n1(n1,n2,n3),
    index index_n4(n4,n5,n6)    
    );
    
    insert into t100 values(1,2,"a","b","aa","bb");
    insert into t100 values(1,2,"a","b","aa","bb");
    insert into t100 values(1,2,"a","b","aa","bb");
    insert into t100 values(2,2,"a","b","cc","dd");
    
    desc select * from t100 where n1=2 and n2=2 and n3="a";
    n1=>5 n2=>4 n3=>21 => 30
    # 没有命中索引
    desc select * from t100 where n1=1 and n2=2 and n3="a";
    # index(a,b,c) -> a, ab , abc  创建三个索引.
    # 在创建联合索引的时候 实际是根据参数的不同,创建了不同的索引树,命中一个即可,符合最左前缀原则
    
    
# ### part2 事务处理的四项特征 ACID
A.原子性 :
    同一个十五中可能有多条sql语句,要么全部成功,要么直接回滚,作为一个完整的整体,不能再分割的最小个体
C.一致性 :
    a,i,d 都是为了保证数据的一致性才提出来的,比如约束,键,在插入数据时,必须按照约定条件才能插入,保证插入数据规则上的一致性
    上升到事务中,防止意外情况导致数据不统一,比如脏读,幻读,不可重复读,最终要决定数据的同步一致
    在上升到主从数据库,主数据库执行这条插入数据,从数据库一定执行,保证数据一致性
I.隔离性 :
    lock+isolation锁,来处理事务的隔离界别,
    一个事务和另外一个事务在工作过程中彼此独立,
    如果同时更改同一个数据,因为锁机制的存在,先执行的先改,其他事务需要等待
D.持久性 :
    把数据写到磁盘上,保证数据的持久化存储
    
# 隔离性: 隔离级别
begin;
执行SQL
commit;
rollback;

脏读: 没提交的数据被读出来了
不可重读: 前后多次读取,数据内容不一样(同一个会话中,在不进行修改的时候,永远只看到同样的一套数据)
幻读    : 前后多次读取,数据总量不一样

RU : 读未提交 : 脏读,不可重读,幻读
RC : 读已提交 : 防止脏读,会出现 不可重读和幻读
RR : 可重复读 : 防止脏读,不可重复读,可能会出幻读 (默认级别)
SR : 可串行化 : 什么都能防止(多个会话窗口同步,不能并发,性能差)
    
# 查询默认的隔离级别
select @@tx_isolation;
# 查询是否自动提交数据
select @@autocommit;
    
# [mysqld]
/etc/mysql/mysql.conf.d
nano mysqld.cnf
# 更改隔离级别
transaction_isolation = READ-COMMITTED
# 重启
service mysql restart

# 创建一张表
create table t1(id int , k1 char(10),k2 int);
insert into t1 values(1,"x",10);
insert into t1 values(1,"x",10);
insert into t1 values(2,"a",20);
insert into t1 values(2,"b",20);
insert into t1 values(3,"a",30);
insert into t1 values(3,"b",30);
    
# 1.脏读
# 2.不可重复读
"""针对于当前mysql会话窗口,不应该每次发生的结果都不一样,这破坏一致性原则,叫不可重读;"""
begin;
update t1 set k2 = 11 where id = 1;
select * from t1;
commit;
    
begin;
update t1 set k2 = 21 where id = 1;
select * from t1;
commit;

# 3.幻读
# 会话窗口1
begin;
update t1 set k2=100 where id>=1
# 会话窗口2
insert into t1 values(4,'c',40)
# 会话窗口1
commit  发现数据不一致,多出了很多数据
+------+------+------+
| id   | k1   | k2   |
+------+------+------+
|    1 | x    |  100 |
|    1 | x    |  100 |
|    2 | a    |  100 |
|    2 | b    |  100 |
|    3 | a    |  100 |
|    3 | b    |  100 |
|    4 | c    |   40 |

# 如何在RR级别下,彻底避免脏读,幻读,不可重读
在配置文件中 , 加入
autocommit=0 把rc隔离级别注释掉
service mysql restart


# 4.事务应用的技术(了解)
(1) RR级别下,解决不可重读
    MVCC技术:多版本并发时,防止不可重复现象;
    每次事务窗口开启时,都会生成一个最新版本的快照,然后去读取快照

(2) RR级别下,解决幻读(除了行级锁,表级锁,在记住下面两把锁,解决事务处理问题)
    gap       间隙锁
    next-lock 下一键锁