MySQL学习笔记简单分享(5)

limit优化一个常见又非常头疼的问题就是 limit 2000000,10 (深度分页)，此时需要MySQL排序前2000010 记录，仅仅返回2000001- 2000010的记录,其他记录丢弃,查询排序的代价非常大。优化思路：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

explain select count(*) from tb_user;

MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;

InnoDB引擎,它执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。

优化思路：自己计数。

count(字段)时，该字段某行值为null，该行不会被统计。

count优化count的几种用法：

count （主键）：InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。

count（字段）：没有not null 约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null，不为null，计数累加。有not null约束：InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

count (1) ：InnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1"进去,直接按行进行累加。

count (*) ：InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加。

按照效率排序的话， count(字段) < count(主键id) < count(1) ≈ count(*),所以尽量使用count(*)。

update优化

update student set no= '2000100100' where id = 1;(主键索引，行锁)

update student set no ='2000100105' where name='韦一笑';(没有索引，表锁)

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。

Mysql事务

事务用于保证数据的一致性，它是一组操作的集合，该组的所有操作要么全部成功，要么全部失败

当执行事务操作时，mysql会在表上加锁，防止其他用户修改表的数据，这对用户来讲是非常重要的

事务操作

start transaction ---开始事务

savepoint 节点名 ----设置保存点

rollback to 节点名 ----回退事务，返回指定的节点

rollback ----回退所有的事务

commit ----提交事务，所有的DML操作生效，不能再回退

10:00 11:00 12:00

savapoint A savepoint B commit

当从12:00 rollback 到A时，10.00-12.00之间的操作也全都删除，将不可回归B节点

12.00用户一旦commit提交事务，所有操作生效，不可更改

如果不进行commit操作，其他连接不一定看到新数据

如果不开始事务，默认情况DML操作会自动提交，不能回滚

开始事务后，没有创建保存节点，回滚直接回滚到事务开始的时刻

存储引擎要为Innodb才能使用事务，MyISAM不支持

开始事务：start transaction / set autocommit = off

事务操作的两种方式

1、关闭自动提交

set @@autocommit=0;//将事务提交设置为0，关闭自动提交

然后执行sql，如果所有sql操作执行完成无异常，则commit提交事务，完成sql操作;

如果sql执行过程中出现问题，则rollback回滚事务;

2、设置开启事务

开始事务：start transaction / begin

开启事务后，也不会自动提交，所有sql执行正常完成，提交事务commit;否则执行rollback回滚事务;

事务的四大特性

理解事务的隔离级别之前需要了解事务的四大特性（ACID原则）：

1.事务的原子性(Atomicity)：是指一个事务要么全部执行，要么不执行，也就是说一个事务不可能只执行了一半就停止了。比如你从取款机取钱，这个事务可以分成两个步骤：1划卡，2出钱。不可能划了卡，而钱却没出来。一组sql操作必须同时完成，要么就都不完成，不可分割。2.事务的一致性(Consistency)：是指事务的运行并不改变数据库中数据的一致性。例如，完整性约束了a+b=10，一个事务改变了a，那么b也应该随之改变。3.隔离性(Isolation）：是指两个以上的事务不会出现交错执行的状态。因为这样可能会导致数据不一致，更加具体的来讲，就是事务之间的操作是独立的。4.持久性(Durability）：事务的持久性是指事务执行成功以后，该事务对数据库所作的更改便是持久的保存在数据库之中，不会无缘无故的回滚。

并发事务问题

多个连接开启各自事务操作数据库中的数据时，数据库要负责隔离操作，以保证各个连接在获取数据时的准确性

如果不设置事务隔离级别，会引发

1.脏读 一个事务读取另一个事务尚未提交的修改时，产生脏读

2.不可重复读 同一查询在同一事务中多次进行，由于其他提交事务所做的修改或删除，每次返回不同的数据集，此时发生不可重复读

3.幻读 同一查询在同一事务中多次进行，由于其他提交事务所做的插入操作，每次返回不同的数据集，此时发生幻读

1、脏读：在一事务中执行了修改表中数据的操作，但该操作还没进行commit提交，在此时，另一个事务进行了查询操作，查询到了此时还未提交的数据，是为脏读。

2、不可重复读：一个事务进行了查询操作，比如查询了id为1的用户信息，之后另一个事务进行了更新id为1的用户的信息并提交了事务，之后第一个事务又再次查询了id为1的用户信息，此时查询到的两次结果并不一致，是为不可重复读。

3、幻读：一个事务进行了查询操作，比如此时该表中没有数据，之后另一个事务进行了插入操作并提交事务，之后第一个事务对该表插入信息，但发现Mysql提示此时表中已经有了该条信息(如果有主键，可能报出主键重复的错)，但是查询数据时仍然查不到该数据！只有等第一次事务也提交后才能看到数据！此现象是为幻读。

事务隔离级别

为了解决并发事务问题

Read uncommitted(读未提交)

读未提交，就是一个事务可以读取另一个未提交事务的数据。

脏读、不可重复读、幻读在这种隔离级别下仍都会产生。

Read committed(读提交)---Oracle的默认隔离级别

读提交，就是一个事务要等另一个事务提交后才能读取数据。

这种隔离级别可以解决脏读。

Repeatable read(重复读) ---MySQL的默认隔离级别

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作。

这种隔离级别可以避免脏读、不可重复读。

Serializable （序列化）

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

会加锁，A操作表时，没有提交，此时B来操作，B会卡住无法进行，也就是不支持并发了！

ps:大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read

查看当前会话隔离级别：select @@transaction_isolation（5.7版本及以后）/@@tx_isolation（5.6及以前的版本）;

查看系统当前隔离级别：select @@global.transaction_isolation/@@global.tx_isolation;

设置当前会话的隔离级别：set session transaction isolation level 隔离级别;

设置系统当前隔离级别：set global transaction isolation level 隔离级别;

事务隔离级别越高、性能越低;性能越高，隔离级别越低--一般使用数据库默认隔离级别很少进行修改。

ZLS_Pri

关注

23
点赞
踩
12

收藏

觉得还不错? 一键收藏
1
评论
MySQL学习笔记简单分享(5)

3、幻读：一个事务进行了查询操作，比如此时该表中没有数据，之后另一个事务进行了插入操作并提交事务，之后第一个事务对该表插入信息，但发现Mysql提示此时表中已经有了该条信息(如果有主键，可能报出主键重复的错)，但是查询数据时仍然查不到该数据！2、不可重复读：一个事务进行了查询操作，比如查询了id为1的用户信息，之后另一个事务进行了更新id为1的用户的信息并提交了事务，之后第一个事务又再次查询了id为1的用户信息，此时查询到的两次结果并不一致，是为不可重复读。不可能划了卡，而钱却没出来。
复制链接

扫一扫