mysql优化

最新推荐文章于 2024-04-28 18:31:24 发布

爱喝茶的猫先生

最新推荐文章于 2024-04-28 18:31:24 发布

阅读量605

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/weixin_38601197/article/details/118634533

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

mysql优化

MySQL引擎

MyISAM：不支持事务

innodb：支持表锁和行锁

一、局部性原理

show create table film

查看建表的详细信息

show table status in filmclub
like 'film'

查看filmclub数据库的film表信息

1、cpu读取一行数据时，会将该行数据所在的相邻数据都会被读取出来。mysql的innodb,一页有16kb字节。16kb=1024*16=16384字节

2、表中的页结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GKvuEuFB-1625896976587)(C:\Users\very\AppData\Roaming\Typora\typora-user-images\1584322796447.png)]

page head:

page directory:不排序没有页目录，页目录表示表中行数据的所在位置

3、表的主键索引primary key

show index from 表名，展示表的索引。

表中添加主键，会根据主键排序。insert效率降低，select效率提高。

表中追加主键：

alter table 表名

add constraint pk_a primary key

4、uuid适合做主键吗？

答：不适合，每一页存储数据的固定大小为16kb

1、uuid所占字节太长，页中存放的数据会变少

2、主键索引列需要排序，因为uuid是随机产生的，所以并不是排序后，向B+Tree后插入。因此很有可能改变树的结构

3、B+Tree 结构的特点是，有冗余节点的产生。uuid太占空间冗余数据变大。

为什么针对于每张表，必须创建一个主键，且主键尽量使用整形，且有自增长功能？

答：1、主键可以做为聚集索引

2、整型所占空间少，可以存储更多的信息

3、自增长可以使主键自动排序，添加数据会向后自动插入，不会改变B+Tree结构

二、索引

一张表只能有一个主键为聚集索引，外键是非聚集索引

1、聚集索引和非聚集索引

聚集索引会影响整张表的数据结构，非聚集索引不会影响表的结构

聚集索引：数据行的顺序与列值（一般是主键的那一列）的逻辑顺序相同，一个表中只能拥有一个聚集索引

非聚集索引：该索引中索引的逻辑顺序与磁盘上的物理存储顺序不同，一个表中可以拥有多个非聚集索引

2、回表

查询非聚集索引时，会先查到非聚集索引的数据，然后根据它关联的聚集索引，再回来查询表中的数据，查出聚集索引的行数据。该过程称为回表

3、非聚集索引的创建、删除

create index idx_b on 表（b）:给表的b列添加索引

create index idx_bdc on 表（b,c,d）：给表中的b,c,d列添加索引（复合索引）

drop index idx_b on 表：删除表中b列的索引

create index inx_b on t1(b)
create index idx_b_c_d on t1(b,c,d)
drop index inx_b on t1

4、复合索引排序

第一列相同的前提下，第二列才会排序。第一列为主要条件

select * from film
order by cid,price

5、复合索引查询

按照最左原则查询，最左边的列如果不确定时，其它索引列没有顺序。只有排序完成，才可以走索引查询的结构。如果最左侧的列没有确定时，其它索引列查询会按照全表来查询。

查询时应该从最左侧开始写

select * from t1
order by b,c,d

6、模糊查询的最左原则

将最左侧确定时，才能用到索引查询

7、那些列适合做索引

表的主键、字段不长、

三、explain

explain select * from 表

展示查询结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D0Z3aOIV-1625896976590)(C:\Users\very\AppData\Roaming\Typora\typora-user-images\1584339396681.png)]

1、id

id越大越先执行，id相同按顺序执行。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZJ059KYH-1625896976592)(C:\Users\very\AppData\Roaming\Typora\typora-user-images\1584349798603.png)]

2、select_type 查询的种类

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HNoDj6h6-1625896976593)(C:\Users\very\AppData\Roaming\Typora\typora-user-images\1584341675105.png)]

simple查询:不包含union（联合查询，将结果集连接）和子查询的查询

explain select * from film

primary查询：查询中最外层SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）

explain select * from film
union
select * from film


EXPLAIN select * from film
where fid=(
	select fid from film
	where fname='叶问'
)

subquery查询：子查询


explain select * from film 
where cid=(
	select cid from category
	where cname='战争'
)

derived查询：子查询位于from子句中，构建的虚拟表

EXPLAIN  select * from film f,(SELECT cid,avg(price) avgprice from film group by cid)tt
where f.cid=tt.cid and price > avgprice

dependent subquery: 相关子查询，子查询的条件用到外表的值

EXPLAIN select *,cid,(select avg(price) from film where cid=f.cid) from film f
where price > (select avg(price) from film where cid=f.cid)

3、table 表名

4、type ：all、index、range、ref、eq_ref、const效率由底到高

1）all:全表扫描，扫描所有数据，效率极低。避免全表扫描。没有走索引

explain select * from film

2）index:是另外一种形式的全表扫描，扫描所有索引

index出现的情景：

查询的数据，全部包含在一种索引中时，不用回表

当按照索引顺序查询全表数据的时候，查询的数据存在索引排序，并且查找时按索引进行排序查找（order by）。

EXPLAIN select b,c,d from t1

EXPLAIN select * from t1 order by a

3）range: 查询时条件有大于、小于，出现range。如果回表的代价高于全表扫描，则为all。b+tree为双向连接

EXPLAIN select * from t1 
where  b < 3

4) ref：查询条件有非主键、非唯一约束的列既非聚集性索引，且查询条件有等值判断

EXPLAIN select * from t1 
where  b = 3

5) eq_ref：多表连接查询，针对主键或唯一性约束的列进行等值判断。

EXPLAIN select  * from film f,category c
where f.cid=c.cid

使用外连接可以控制查询表的先后顺序

6) const：必须是单表查询，并且单表主键或唯一性约束，进行等值判断

EXPLAIN select * from film 
where fid=1

5、 possible_key 可能用到的索引

6、key 真正用到的索引

7、key_len 索引的长度

可以根据索引长度来推测复合索引的使用情况

8、ref 当前查询所用的条件

参考参照、代表一个条件列

9、rows 推测索引要扫描多少行，估算的值是有索引的列

10、Extra 额外

1) Using filesort 查询列的索引已经排序，同时使用别的列排序。

explain select a from t2 ORDER BY b

2)Using index(重点) 表示索引覆盖，只要出现索引覆盖，就意味着性能高。不用回表

3)Using where 表示where查询条件

4) Using index condition 表示查询的列不全在一个索引中

explain select a,b from t2
where b>1

四、MySQL优化实战

1、select语句执行顺序

2、小表驱动大表，sql语句执行少。先查询数据量小的表，再连接大表。

explain select * from category c  join film f
on c.cid=f.cid

3、海量数据测试

select ename from emp
where ename='QAcLzn'

create index idx_ename on emp(ename)
show index from emp

五、MVCC多版本并发控制读操作

truncate table 表清空表数据

1、每个事务都有一个事务id，自增长。（表中存在隐藏列：事务id,事务回滚指针）

2、多个事务同时操作数据时，会产生版本链。全部事务全都提交了，就只会保留最新版本的信息。

3、readview 集合中专门存放（活动事务id），正处于活动中的事务（begin之后还没有commit或rollback）

4、脏读：直接读取最新版本

5、read committed：事务隔离级别在读已提交每次select，都会更新readview

读取数据时根据事务隔离级别，当用户读取数据时。先读取版本链最新的数据和事务id,然后在readview中比对是否存在相匹配的事务id，如果存在相匹配的事务id，则会沿着该事务的最新版本链向下查找，直到找到readview中不存在的事务id（既已经提交不活跃的事务）。当活动事务全部清空时，只留最新版本。

6、repeatable read 可重复读：只有在第一次select时，才会更新read view

只有在第一次select时，才会更新read view。因此不管用户怎么修改数据。read view中始终保存的是第一次select时的read view。所以只会读到readview中没有的活动事务id的数据（不活跃的事务数据）

7、serializeable 串行：加共享锁

六、锁写操作

select @@tx_isolation:产看事务隔离级别

设置innodb的事务级别方法是：set 作用域 transaction isolation level 事务隔离级别，例如~

SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SkV8HdMK-1625896976595)(C:\Users\very\AppData\Roaming\Typora\typora-user-images\1584430051944.png)]

1、单独执行sql语句不开启事务时，事务会自动开启、自动提交

2、锁会在事务结束时，自动释放

3、ddl会自动提交事务

一旦出现死锁，会将一方自动释放

4、共享锁和共享锁之间兼容

5、排他锁和任何锁都不兼容

6、select 语句默认不加任何锁

7、update、insert、delete 语句执行会添加排他锁

8、select也可以强行加共享锁，只要在查询语句之后添加上lock in share mode

9、select也可以强行加排他锁，只要在查询语句之后添加for update即可

10、在事务隔离级别为serializable串行的前提下，select语句默认就会添加共享锁

11、表锁 lock table in share mode、 lock table in exclusive mode

12、行锁，行锁中的一种特殊锁：间隙锁，锁定一个范围。GAP锁的目的是为了防止同一事务的两次当前读，出现幻读的情况。

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。

13、当事务级别在read committed:读已提交的情况下，只会锁住现有的数据，没有间隙锁。

14、当事务级别在repeatable read:可重复读的情况下，不仅会锁住现有数据，还会锁住将来要出现的数据，存在间隙锁。

如果查询走索引，查询到结果的同时使用间隙锁。

如果查询没有走索引，会锁住整张表

15、意向锁标志性的锁

16、悲观锁，当程序员在select语句执行时，并不像别人更改自己的数据。就会添加悲观锁

begin;
select * from t1 lock in share mode

乐观锁，当程序员执行select语句时，不担心数据被修改。而不添加任何锁，称为乐观锁

谈谈mysql优化

1、索引的数据结构:

B+Tree,所有数据都在叶子层。且已经排序，叶子层还是一个双向链表，其他层有数据冗余

2、复合索引、最左原则

只有左边的列确定了，右边的列才是有顺序的。例：查电话号码

3、聚集索引和非聚集索引的区别

聚集索引是对整张表排序，非聚集索引是这一列排序。回表的概念，单查询的列在索引中不存在时会产生回表的现象

4、什么时候适合用索引

　1) 主键自动建立唯一索引

2) 频繁作为查询条件的字段应该建立索引

3) 查询中与其他表关联的字段，外键关系建立索引

4) 单键/组合索引的选择问题，组合索引性价比更高

5) 查询中排序的字段，排序字段若通过索引去访问将大大提高排序效率

6) 查询中统计或者分组字段

5、不适合创建索引条件

1) 表记录少的

2) 经常增删改的表或者字段

3) where条件里用不到的字段不创建索引

4) 过滤性不好的不适合建索引

数据都在叶子层。且已经排序，叶子层还是一个双向链表，其他层有数据冗余