十三、索引树高度
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下一键锁