1. 索引树高度
(1) 表的数据量:
数据量越大,树的高度就会变高,理论上三层索引树的高度最为理想,可以支持百万级别的数据量
解决办法: 可以使用分表(横切,竖切),分库,增加缓存,解决数据量大,查询慢
(2) 索引键值过长:
该索引字段存储数据太大,每个叶子节点最大存储16k,超过这个范围会新增加叶子节点和分支节点
解决:前缀索引(比如截取前5个长度)
(3) 数据类型:
char(定长) varchar(变长) 从开辟空间速度来看,char快
从数据结构上来看,varchar更为合理
(4) 其他优化:
避免使用select *,不确定表大小的时候,使用count(*) 查一下数据
尽量使用数据类型较小的字段做索引
重复值少的,区分度高的字段索引,性别这样的字段不要做索引
在多表查询时使用join,尽量少的使用子查询
2. 执行计划分析: 在执行一条sql语句前, 指定执行的方案 用desc或者 explain
mysql> desc select * from class;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | class | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
(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
all: 全表扫描, 不走索引, 是慢查询
① 在大范围内查询 > < >= <= != between and in like ...
② where条件中有计算,有函数
③ 数据类型不匹配
④ 拼接条件使用or
index: 全索引扫描,扫描整个索引树，这样的索引没有意义
用一个重复读很高的字段作为索引
range:索引范围扫描(若范围太大, 不能命中索引)
desc select * from s1 where id < 5; 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 first_name like "%刘%"; type为all
desc select * from s1 where email like "w%"; # type => range (模糊查询中去掉左边的%)
对in和or这样的语句进行优化:
union all比union速度快,因为union多一步去重的操作,如果需要去重,则使用union
desc select * from s1 where id in (1,2); type为range
desc select * from s1 where id = 1 union select * from s1 where id = 2; type为ref, ref ,all
desc select * from s1 where id = 1 or name = "rachel"; type为all
desc select * from s1 where id = 1 union select * from s1 where name = "rachel"; type为ref, all, all
ref 普通索引查询(非唯一)
desc select * from s1 where id = 10; type为ref
desc select * from s1 where first_name = "刘10"; type为ref
eq_ref 唯一性索引(联表)
要求: 应用在多表联查中,被关联的字段需要主键或者唯一,表之间的关系为一对一并且数据条数相同
const 主键或者唯一索引(单表),针对primary key 或者unique
desc select * from s1 where id = 1000; 设置为主键后, type为const
system(了解)
只有一条数据的系统表
(4) possible_keys : 执行sql时,可能用到的索引是谁
(5) key : 执行sql时,实际用到的索引是谁
show index from s1; # 展现所有的索引
(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
联合索引: 查询的数据中如果有很多重复的数据,不会触发联合索引
3. 事务处理的四项特征: ACID
A.原子性: 同一个事务中执行多条sql语句,要么全部成功,要么直接回滚,作为一个完整的整体,不能再继续分隔的最小个体
C.一致性:
a,i,d 都是为了保证数据的一致性才提出来的,比如约束,键在插入数据时,必须按照要求插入,保证规则上的一致性,
上升到事务中,如果出现意外导致数据不统一,例如脏读,幻读,不可重读,最终要保证数据是一致的
上升到主从数据库,主数据库增删改,从数据也要进行同步改变,保证数据的一致性;
I.隔离性:
lock + isolation锁,来处理事务的隔离级别
一个事务和另外一个事务工作过程中彼此独立隔离
D.持久性:
把数据写到磁盘上,保证数据持久化存储不丢失.
隔离性 : 隔离级别
脏读 : 没提交的数据被读出来了
不可重读 : 前后多次读取,结果数据内容不一样(同一个会话里,在不修改的情况下,永远只看到同样的一份数据)
幻读 : 前后多次读取,结果数据的总量不一样
RU : 读未提交 脏读,不可重读,幻读 READ-UNCOMMITTED
RC : 读已提交 防止脏读,会出现不可重读和幻读 READ-COMMITTED
RR : 可重复读 防止脏读,不可重读 ,可能会出现幻读 REPEATABLE-READ
SR : 可串行化 防止一切,一般不用(会把异步并发的程序变成同步程序,不能并发,性能差)
(1) 修改当前mysql配置:
查询当前mysql的隔离级别 (默认是RR)
select @@tx_isolation;
查询是否自动提交数据
select @@autocommit;
修改mysql配置文件
D:MySQL5.7mysql-5.7.25-winx64my.ini
更改隔离级别
transaction_isolation = READ-UNCOMMITTED
不让系统自动提交数据
autocommit = 0
重启mysql
net stop mysql
net start mysql
(2) 脏读
READ-UNCOMMITTED
先去调整设置,重启mysql ,尝试在一个窗口里通过事务,更改一条数据,开启另外一个窗口尝试读取,会出现问题
(3) 不可重复读
# 窗口1
begin;
update t1 set k1="abc" where id = 1
select * from t1;
commit;
# 窗口2
select * from t1; 数据也跟着改了是不可重读
(4) 幻读
# 窗口1
begin;
insert into t1 values(4,'c',50);
select * from t1;
commit;
# 窗口2
select * from t1; 数量也跟着增加了是幻读
# 通过二次提交commit , 可以让多用户同步数据;
commit;
(5) 事务应用的计数(了解)
RR级别下,解决不可重读,使用mvcc技术,生成最新的mysql的系统备份(快照),然后读取快照
RR级别下,解决幻读,gap 间隙锁 next-lock 下一键锁