python学习第41天
一.索引树高度
(1)表的数据量
数据量越大,树的高度就会变高,理论上三层索引树的高度最为理想,可以支持百万级别的数据量
解决: 可以使用分表(横切,竖切),分库,增加缓存,解决数据量大,查询慢
(2)索引键值过长
该索引字段存储数据太大,每个叶子节点最大存储16k,超过这个范围会新增加叶子节点和分支节点
解决:前缀索引(截取前5个长度)
(3)数据类型
char(定长) varchar(变长) 从开辟空间速度来看,char快
从数据结构上来看,varchar更为合理
(4)注意点:
(1) 避免使用select *,不确定表大小的时候,使用count(*) 查一下数据
(2) 尽量使用数据类型较小的字段做索引
(3) 重复值少的,区分度高的字段索引,性别这样的字段不要做索引
(4) 在多表查询时使用join,尽量少的使用子查询
二.执行计划分析
执行计划:在一条sql执行之前,制定执行的方案
desc/explain 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这样的语句进行优化
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条件
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';
- 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个字符占用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 创建了三组索引 ,符合最左前缀原则,第一个字段必须存在才能触发;
三.事务处理的四项特征 ACID
(1)ACID
A.原子性:
同一个事务中执行多条sql语句,要么全部成功,要么直接回滚,作为一个完整的整体,不能再继续分隔的最小个体
C.一致性:
a,i,d 都是为了保证数据的一致性才提出来的,比如约束,键在插入数据时,必须按照要求插入,保证规则上的一致性,
上升到事务中,如果出现意外导致数据不统一,例如脏读,幻读,不可重读,最终要保证数据是一致的
上升到主从数据库,主数据库增删改,从数据也要进行同步改变,保证数据的一致性;
I.隔离性:
lock + isolation锁,来处理事务的隔离级别
一个事务和另外一个事务工作过程中彼此独立隔离
D.持久性:
把数据写到磁盘上,保证数据持久化存储不丢失.
(2)隔离性: 隔离级别
脏读 : 没提交的数据被读出来了
不可重读 : 前后多次读取,结果数据内容不一样(同一个会话里,在不修改的情况下,永远只看到同样的一份数据)
幻读 : 前后多次读取,结果数据的总量不一样
RU : 读未提交 : 脏读,不可重读,幻读 READ-UNCOMMITTED
RC : 读已提交 : 防止脏读,会出现不可重读和幻读 READ-COMMITTED
RR : 可重复读 : 防止脏读,不可重读 ,可能会出现幻读 REPEATABLE-READ
SR : 可串行化 : 防止一切(但是会把异步并发的程序变成同步程序,不能并发,性能差)
# 查询当前mysql的隔离级别 (默认是RR)
select @@tx_isolation;
# 查询是否自动提交数据
select @@autocommit;
# 修改mysql配置文件
D:\MySQL5.7\mysql-5.7.25-winx64\my.ini
# 更改隔离级别
transaction_isolation = READ-UNCOMMITTED
# 不让系统自动提交数据
autocommit = 0
# 重启mysql
net stop mysql
net start mysql
# 1.脏读
READ-UNCOMMITTED
先去调整设置,重启mysql ,尝试在一个窗口里通过事务,更改一条数据,开启另外一个窗口尝试读取,会出现问题
# 2.不可重复读
# 窗口1
begin;
update 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 下一键锁
