mysql优化

mysql优化
一、索引的数据结构

mysql的InnoDB引擎采用B+Tree树结构,叶子节点存放的是每一行完整的数据记录,索引的键采用数据表中的主键
当数据表中将主键作为索引时,底层会将存储的数据分成很多页,每页存储16kb的数据,不同页之间以链表的形式进行链接(双向链表),每页的头指针指向下一页的首地址。页目录的根节点目录页,目录页中以键值对的形式存放了很多页,key值对应每页的起始位置,value存放的是页的地址。

二、聚集索引

聚集索引的数据是根据主键的顺序进行存储的,相当于将主键作为索引构造B+树,叶子节点存放的是表中的行数据记录。
聚集索引对主键的排序查找速度比较快
每张表只能由一个聚集索引,聚集索引排序时会影响整张表的结构

三、非聚集索引

除聚集索引之外添加的其他索引,非聚集索引叶子节点存储的是相应记录的主键值。

使用非聚集索引查找数据时,先根据非聚集索引查找到主键,然后根据聚集索引查找到对应的数据记录(回表操作),非聚集索引比聚集索引查询速度慢。

每张表可以由多个非聚集索引,非聚集索引排序不会影响整个表的结构

在这里插入图片描述

在这里插入图片描述

四、复合索引

SELECT * FROM t1 ORDER BY b,c,d

当b相同时按照c排序,当b和c都相同时按照d排序

在这里插入图片描述

最左原则(复合索引,模糊查询)

当b确定时,中间不会隔开,才会用到索引。

EXPLAIN SELECT * FROM t1 WHERE b = 2 AND c = 2

在这里插入图片描述

当b不确定时,不会用到索引,只会查找整个表

EXPLAIN SELECT * FROM t1 WHERE c = 2

在这里插入图片描述

五、uuid为什么不适合做索引

uuid由32个十六进制数字+4个破折号组成,如果采取uuid作为主键,则每页中存放的数据量会变少。

在B+Tree中,添加数据时索引列要进行排序,uuid可能会使数据位置频繁的变动,影响性能

在构建B+Tree时会产生许多冗余节点,uuid会造成冗余节点占取更多的存储空间,影响性能

面试题:为什么要给表加主键且主键必须是整型,且必须自增长

主键作为查询条件时可以提高查询效率
在进行增加时,B+Tree中只在后面追加,不影响前面数据的重构。

六、哪些列适合使用索引

表的主键和外键必须有索引(默认创建)
有大量数据的表应该加索引
在where条件中经常出现的列应该有索引
索引尽量建在小字段中,查询性能较好
值得变化多得情况下,适合加索引

七、explain

id

id越大先执行

EXPLAIN SELECT * FROM film
WHERE cid = (
SELECT cid FROM category
WHERE cname='动作'
)

在这里插入图片描述

id相同,按顺序执行

EXPLAIN SELECT * FROM category c,film f
WHERE c.cid = f.cid

在这里插入图片描述
2. select_type

simple:简单查询(只要不包含union和子查询的查询都是simple查询)

subquery:子查询

primary:主查询

在这里插入图片描述

union:联合查询

EXPLAIN SELECT * FROM category WHERE cid = 1
UNION
SELECT * FROM category WHERE cid = 2

在这里插入图片描述

derived:临时表

EXPLAIN SELECT * FROM film f,
(SELECT cid,AVG(price) avgprice FROM film GROUP BY cid) mm
WHERE f.cid = mm.cid AND price>avgprice

在这里插入图片描述

dval:逻辑表

SELECT 5 FROM DUAL

dependent subquery:相关子查询

EXPLAIN SELECT *,cid FROM film f
WHERE price >(SELECT AVG(price) FROM film WHERE cid = f.cid)

在这里插入图片描述

type的常见取值

all:全表扫描(尽量避免)

EXPLAIN SELECT * FROM t1

在这里插入图片描述

index(也是全表扫描,但是会走索引)

查询的数据包含在一个索引中时,不用回表,可直接查询

EXPLAIN SELECT b FROM t1

在这里插入图片描述

在这里插入图片描述 – 当按照索引顺序查找时,为index

EXPLAIN SELECT * FROM t1 ORDER BY a

range:当索引键进行>或<操作时为range(针对于聚集索引)

针对于非聚集索引查询时,如果查询的数据量超过表中数据量的一定比例,则会转为全表扫描,因为非聚集索引有回表的代价,如果回表次数过多,则可以直接进行全表扫描。

EXPLAIN SELECT * FROM t1 WHERE a > 1

在这里插入图片描述

ref:非主键、非唯一性约束的列等值查询时,使用ref

EXPLAIN SELECT * FROM t1 WHERE b = 1

在这里插入图片描述

eq_ref:多表联合查询时,主键、唯一性约束的列等值查询时,使用eq_ref

左外连接(可以控制查表的先后顺序,保左表),先查从表,再查主表时,使用eq_ref

EXPLAIN SELECT * FROM film f LEFT JOIN category c ON f.cid =c.cid WHERE fname = '功夫'
在这里插入图片描述

const:按主键查询,必须是单表查询,单表查询中使用主键或唯一性索引进行等值判断。

EXPLAIN SELECT * FROM t1 WHERE a = 1
在这里插入图片描述

效率:all<index<range<ref<eq_ref<const

possible_keys、key、key_len

EXPLAIN SELECT b,d FROM t1 WHERE b = 1 AND c = 1 AND d = 1

在这里插入图片描述

ref

EXPLAIN SELECT b,d,e FROM t1 WHERE b = 1

在这里插入图片描述

rows

EXPLAIN SELECT * FROM film f LEFT JOIN category c ON f.cid = c.cid WHERE fname = '功夫'

在这里插入图片描述

extra

using index :只使用索引(在索引中包括,性能较高)

EXPLAIN SELECT b,d FROM t1

b,d在复合索引idx_b_c_d中,索引覆盖,不需要额外排序

在这里插入图片描述

using filesort:额外排序(查找的顺序和排序不一致时会出现额外排序)

b有索引,但e没有索引

EXPLAIN SELECT b FROM t1 ORDER BY e

在这里插入图片描述

using where:使用了where条件

EXPLAIN SELECT * FROM t1 WHERE d = 1

using index condition:需要进行回表操作

EXPLAIN SELECT b,e FROM t1 WHERE b > 1
在这里插入图片描述

小表驱动大表原则

在这里插入图片描述

在这里插入图片描述

使用小表驱动大表原则可以提高查询效率。

八、事务

四大特性

原子性:一个事务不能被分割,执行和不执行只能选其一。

一致性:事务执行前后数据库的数据在逻辑上保持一致

比如,转账前A账户有1000元,B账户有1000元

A向B转账200元,转账后A账户有800元,B账户有1200元

但转账前后A和B的总数都是2000

隔离性:指多个事务同时操作同一数据时,事务之间的影响

持久性:事务一旦提交或者回滚,则数据库中的数据拥有了持久性,就算设备故障也不会影响数据的存储。

2.事务的四种隔离级别

读未提交 set transaction isolation level read uncommitted

可以读到其他事务未提交的数据。这样读到的数据就称之为“脏数据”

读已提交 set transaction isolation level read committed 只能读取到其他事务提交以后的数据,避免了脏读,但是存在不可重复读的问题。在一个事务中,查找相同数据两次,结果会不一样。

在这里插入图片描述

可重复读 set transaction isolation level repeatable read 不管其他事务怎样修改数据,只要该级别事务没有修改数据,读取前后的值一样,可以避免不可重复读现象。该级别存在的问题时,不能读取最新的数据。

串行化级别 set transaction isolation level serializable 该级别避免了 “脏读、 不可重复读、 不能读取最新数据” 的问题!,该级别在读取时会加个排它锁,其他事务则读取不了数据,但没有并发性。

九、MVCC

Multi-Version Concurrency Control.多版本并发控制

MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下

innDB中通过为每一行记录添加两个额外的隐藏的值(tx_id和roll_pointer)来实现MVCC,tx_id用来存储的每次对某条聚簇索引记录进行修改的时候的事务id。,roll_pointer是一个回滚指针,指向这条聚簇索引记录的上一个版本的位置

ReadView中主要就是有个列表来存储系统中当前活跃着的事务,也就是begin了还未提交或者回滚的事务。通过这个列表来判断记录的某个版本是否对当前事务可见。

在READ COMMITED隔离级别下,每次select时,ReadView会随着更新

假设有三个事务

a事务的tx_id为50,a事务进行insert操作(自动提交事务),则版本链中会存放a事务的当前操作的结果

INSERT INTO t3 VALUES(NULL,'aaa') SELECT * FROM t3

在这里插入图片描述

开启b事务,b事务的tx_id为100,b事务进行update操作但未提交,则readView中会存放b事务的tx_id,则readView的值变为[100],版本链中会存放b事务的当前update操作的结果

在这里插入图片描述

开启c事务,c事务开启READ COMMITED隔离级别,c事务的tx_id为150,c事务进行update操作但未提交,则readView中会存放c事务的tx_id,则readView的值变为[100,150],此时进行select(每次select时,ReadView会随着更新,则readView的值变为[100,150]),则会去版本链中找,发现tx_id=150在readView列表中存在,此时就通过指针继续找下一条,依此类推查找。当b事务进行提交,然后进行select操作时,ReadView会随着更新,readView的值变为[150]

在这里插入图片描述

当三者事务都提交之后(并发结束),readView的值变为[],版本链中保留最近修改的记录,之前的记录会全部删除。再进行查找时数据表中的值已经更新。

在这里插入图片描述

在REPEATABLE READ隔离级别下,只有第一次select时,ReadView会随着更新,读取的是ReadView快照的内容,ReadView快照是不变的。

十、mysql锁的16个知识点

DDL会自动提交事务

DQL :select,group by,order by,having
DDL:create,drop,alter
DCL:grant,revoke
DML:insert,update,delete
TCl:begin,rollback,commit

共享锁与共享锁之间是兼容的

共享锁又称为读锁,简称S锁。共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。

排他锁与任何锁都不兼容

排他锁又称为写锁,简称X锁。排他锁就是不能与其他锁并存,如果一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据行读取和修改。

select语句默认不加任何锁

update、delete、insert语句默认加排他锁

默认情况下,一条独立的sql语句会自动开启事务,以及提交事务

锁会在事务结束时,自动释放

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

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

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

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

行锁,行锁中的一种特殊锁:间隙锁,锁定一个范围(包括未来新增的行)。GAP锁的目的是为了防止同一事务的两次当前读,出现幻读的情况。

在事务隔离级别为read committed的前提下,查询语句无论是否走索引,都仅仅锁住查询到的记录,新增的记录不会被锁住,且没有间隙锁!会有幻读问题

在事务隔离级别为repeatable read的前提下,
查询语句如果走索引,则会锁住查询到的记录同时会使用到间隙锁!
如果没有走索引(全表扫描),则会锁住整个表!

意向锁:每加一个行锁,就会在表的外面加一个意向锁,则再加表锁就加不成功。

悲观锁 和乐观锁

悲观锁:总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁(共享锁),这样别人想拿这个数据就会阻塞直到它拿到锁。

实现:开启事务,启用锁机制

乐观锁:总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。如果别人更新过,则放弃本次操作

实现:使用版本号、使用时间戳

死锁:多个事务同时具有排他锁会产生死锁,则释放当前操作事务的排它锁。一个事务中锁不会有冲突。

查询语句如果走索引,则会锁住查询到的记录同时会使用到间隙锁!
如果没有走索引(全表扫描),则会锁住整个表!

意向锁:每加一个行锁,就会在表的外面加一个意向锁,则再加表锁就加不成功。

悲观锁 和乐观锁

悲观锁:总是假设最坏的情况,每次去拿数据的时候都认为别人会修改,所以每次在拿数据的时候都会上锁(共享锁),这样别人想拿这个数据就会阻塞直到它拿到锁。

实现:开启事务,启用锁机制

乐观锁:总是假设最好的情况,每次去拿数据的时候都认为别人不会修改,所以不会上锁,但是在更新的时候会判断一下在此期间别人有没有去更新这个数据。如果别人更新过,则放弃本次操作

实现:使用版本号、使用时间戳

死锁:多个事务同时具有排他锁会产生死锁,则释放当前操作事务的排它锁。一个事务中锁不会有冲突。

作者:weixin_40490101

相关推荐

在这里插入图片描述

全面解析数据库索引(数据库索引种类大盘点)

在这里插入图片描述

mysql中replace into的用法详解

matlab的mink函数

MySQL 之 mysql.server 运行