mysql sql优化 & 事务处理特征 & 约束添加、删除

2021-12-30 04:10:26

索引树高度

  1. 表的数据量

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

    • 解决：可以使用分表(横切，竖切)，分库，增加缓存，解决数据量大，查询慢

  2. 索引键值过长

    • 该索引字段存储数据太大，灭个叶子节点最大存储16k，超过这个范围会新增加叶子节点和分页节点
    • 解决：前缀索引(截取前5个长度)

  3. 数据类型

    • char(定长) varchar(变长) 从开辟空间的速度来看，char快；从数据结构上看，varchar更为合理
      • (1) 避免使用select ，不确定表大小的时候，使用count()查一下数据
      • (2) 尽量使用数据类型较小的字段做索引
      • (3) 重复值少的，区分度高的字段做索引，性别这样的字段不要做索引
      • (4) 在夺标查询时 使用join，尽量少的使用子查询

执行计划

  • desc/explain

  • 执行计划：在一条sql执行之前，指定执行的方案

    desc select * from s1;

mysql> desc select * from ceshi_table;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | ceshi_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)



mysql> explain select * from ceshi_table;
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | ceshi_table | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   100.00 | NULL  |
+----+-------------+-------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 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 全索引扫描

        • 扫描整个索引树，才能获取到所有数据，这样的索引失去意义

          desc select count(*) from s1;

      • 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 100000; #type=all
desc select * from s1 where email like "%w%";   #type=all
desc select * from s1 where email like "w%"; #type=range
‘‘‘如果范围过大，不能命中索引，如果范围适当，可以命中索引‘‘‘
desc select * from s1 where id in (1,2,3)
#对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 =‘aaaa‘;

desc select * from s1 where id = 10
union all
select * from s1 where name = ‘aaaa‘;

      • ref 普通索引查询(非唯一)

        desc select * from s1 where email = ‘xboyww10@oldboy‘;
desc select * from s1 where id = 10;

      • eq_ref 唯一性索引(联表)

        alter table s1 drop index index_id;
alter table s1 add primary key(id);
desc select * from s1 where id = 10;

        create database db0624;
use db0624;
#创建一张表
create table class1(id int,classname varchar(255));
create table student1(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
class_id int
);
#插入数据
insert into student1 values(null,;"wangbaoqiang",82,2);
insert into student1 values(null,;"wanglihong",7,1);
insert into student1 values(null,;"wangwen",7,2);

insert into class1 values(1,"python30");
insert into class1 values(2,"python31");

        ‘‘‘要求：应用在多联表中，被关联的字段需要主键或者唯一，表之间的关系为一对一并且数据条数相同‘‘‘
desc select student1.age from student1,class1 where student1.class_id = class1.id;#ALL
alter table class1 add primary key(id);

desc select student1.age from student1,class1 where student1.class_id = class1.id;#INDEX
delete from student1 where id = 3;
desc select student1.age from student1,class1 where student1.class_id = class1.id;#EQ_REF

      • const：主键或者唯一索引(单表)

        ‘‘‘针对于primary key 和unique索引等值查询‘‘‘
desc select * from class1 where id=1;  #const
desc select * from class1 where id>1;  #range

      • system(了解)

        • 只有一条数据的系统表

    4. possible_keys：执行sql时，可能用到的索引是谁

    5. key：执行sql时，实际用到的索引是谁

      show index from s1;展现表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

      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(1,2,"a","b","cc","dd");

#把数据表中的数据导入
mysql> desc t100;
+-------+------------+------+-----+---------+-------+
| 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    |       |
+-------+------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> select * from t100;
+------+----+------+----+------+----+
| n1   | n2 | n3   | n4 | n5   | n6 |
+------+----+------+----+------+----+
|    1 |  2 | a    | b  | aa   | bb |
|    1 |  2 | a    | b  | aa   | bb |
|    1 |  2 | a    | b  | aa   | bb |
|    1 |  2 | a    | b  | cc   | dd |
+------+----+------+----+------+----+
4 rows in set (0.00 sec)

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

#index(a,b,c)-> a,ab,abc 创建了三组索引，符合最前缀原则，第一个字段必须存在才能触发

事务处理的四项特征 ACID

  • A：原子性

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

  • C：一致性

    • a,i,d 都是为了保证数据的一致性才提出来的，比如约束，键在插入数据时，必须按照要求插入，保证规则上的一致性；
    • 上升到事务中，如果出现意外导致数据不一致，例如脏读，幻读，不可重读，最终要保证数据是一致的
    • 上升到主从数据库，主数据库增删改，从数据也要进行同步改变，保证数据的一致性

  • I：隔离性

    • lock + isolation锁，来处理事务的隔离级别
    • 一个事务和另外一个事务工作过程中彼此独立隔离

  • D：持久性

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

  • 隔离性：隔离级别

    + 脏读：没提交的数据被读出来了
+ 不可重读：前后多次读书，结果数据内容不一样(同一个会话里，在不修改的情况下，永远只看到同样的一份数据)
+ 幻读：前后多次读取，结果数据的总量不一样
    • RU(READ-UNCOMMITTED)：读未提交：脏读，不可重读，幻读
    • RC (READ-COMMITTED)：读已提交：防止脏读，会出现不可重读和幻读
    • RR(REPEATABLE-READ)：可重复读：防止脏读，不可重读，可能会出现幻读
    • SR (SERIALIZABLE)：可串行化：防止一切(但是会把异步并发的程序变成同步程序，不能并发，性能差)
    • 查询当前mysql的隔离级别(默认是RR)
    select @@ tx_isolation;

    • 查询当前是否自动提交数据

      select @@ autocommit;

    • 修改mysql配置文件

      D:\MYSQL5.7\mysql-5.7.25-winx64\my.ini
#系统默认的隔离级别（REPEATABLE-READ）
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，可以让多用户同步数据；

      5. 事务应用的技术(了解)

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

关于约束的添加和删除

  1. 添加、删除 约束 not null

    #alter table 表名 modify 字段名 类型
alter table t1 modify id int not null
alter table t1 nodify id int

  2. 添加、删除 unique唯一索引

    #alter table 表名 add unique(id)
alter table t1 add unique(id)
alter table t1 drop index id

  3. 添加、删除 primary key

    #alter table 表名 add primary key(id)
alter table t1 add primary key(id)
alter table t1 drop primary key

  4. 添加、删除 foreign key 外键（先通过desc表 找到外键的名字，然后再删）

    alter table student1 drop foreign key student1_ibkf_1#删除
alter table student1 add foreign key(classid) references class(id)#添加

mysql sql优化 & 事务处理特征 & 约束添加、删除

上一篇：MySQL到ClickHouse实时同步-CloudCanal实战


下一篇：[TcaplusDB知识库]查看TcaplusDB集群状态