高性能MySql笔记

最新推荐文章于 2023-02-01 00:30:38 发布

a7459

最新推荐文章于 2023-02-01 00:30:38 发布

阅读量292

点赞数

分类专栏： mysql相关文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/a7459/article/details/124562330

版权

mysql相关专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、MySql的架构与历史

Mysql主要是sql熟练，能写个性能优秀的sql就好了。
沉下心练练sql，还是很容易的，部署哪些直接看官方文档。

读写锁

Mysql支持读锁（共享锁）和写锁（排它锁），这里有点类似操作系统。

锁粒度

表锁 table lock

开销最小的锁策略。表锁会锁定整张表，一个用户在进行写操作（插入、更新、删除）的时候，需要先获得写锁，这会阻塞其他用户对该表的所有读写操作。只有没有写锁时，其他读取的用户才能获得读锁，读锁之间是不相互阻塞的。也就是说表锁在写数据的时候其他操作全阻塞。允许一改多读，有点类似volatile。哈哈哈

行级锁

行级锁可以最大程度地支持并发处理，同时也带来了最大的锁开销。InnoDB支持行级锁。行级锁是存储引擎实现的，不是MySql服务器实现的。

事务隔离级别

未提交读 read uncommited 也叫作脏读

事务的修改，还没有提交上去，就被其他查询读到了。所以是未提交读。也叫作脏读，因为事务有可能会滚嘛。那样就读到脏数据了。这个性能上也没有优势，没个啥使用场景。

提交读 read commited 也叫作不可重复读

大多数数据库默认的隔离级别是这个，但MySql不是。一个事务，从开始到提交之前，所做的任何操作对其他事务都是不可见的。也就是要读提交过事务的数据。因为可能之前查了一次，事务提交后又查了一次数据就不一样了，所以也叫作不可重复读。

可重复读 repeatable read

Mysql的默认隔离级别。
这个保证了在同一个事务中多次读取同样的记录结果是一致的。读也有事务？怎么控制？但是这个隔离级别不能解决幻读。幻读通过InnoDB的MVCC控制解决了。所以这个Mysql的默认隔离级别还是挺好的。
所谓幻读，是指事务A读取与搜索条件相匹配的若干行。事务B以插入或删除行等方式来修改事务A的结果集，然后再提交。
幻读：
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样
————————————————
版权声明：本文为CSDN博主「ianhol」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/ianhol/article/details/51150823

可串行化 serializable

串行执行，没啥用。

死锁

InnoDB目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。这个是一种比较简单的解决方法。

多版本并发控制 MVCC

可以认为MVCC是一个行级锁的变种，但是在很多情况下避免了加锁操作，因此开销更低。MVCC不同的引擎实现不同，但是大都实现了非阻塞的读操作，写操作也只是锁定了必要的行。

MVCC实现原理

通过保存数据在某个时间点的快照实现，相当于加了很多版本号。根据事务开始时间的不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。因为不同的时间查的是不同的快照，不同的版本号。
MVCC的实现，不同存储引擎可能不一样。一般有乐观并发控制和悲观并发控制。

InnoDB的MVCC实现

InnoDB通过在每行记录后面保存两个隐藏的列来实现MVCC。两个列分标保存了行的创建时间和过期时间（删除时间）。当然存储的并不是实际的时间值，而是系统版本号。可能版本号的组成里面带了时间规则。每开始一个新的事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。
在这里插入图片描述

InnoDB存储引擎

是MySql的默认事务引擎。
InnoDB通过MVCC来支持高并发，默认隔离级别是可重复读，并通过间隙锁（next-key locking）策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询设计的行，还会对索引中间的间隙进行锁定，防止幻影行的插入。
基于聚簇索引建立。
InnoDB事务模型和锁

二、MySql基准测试

sysbench，优秀的MySql基准测试工具。

三、服务器性能剖析

DBA搞

四、schema与数据类型优化

数据长度尽可能小，类型越简单越好，尽量避免null，可以搞个默认值。
timestamp标示的时间范围比datetime小，但是占用空间只有datetime的一半。推荐使用timestamp。
int(1)和int(20)对于存储和计算来说是一样的，这个可以设置大一点。
decimal类型可以存储带小数点的精确财务数据，但是数据量大的情况下性能太差了。推荐使用bigint代替decimal，存的时候扩大n倍，查到后再自己除一下就好了。

五、创建高性能的索引

索引要注意最左前缀的问题。索引几个字段的顺序很重要。

B+Tree

所有的值都是按顺序存储的，并且每个叶子页到根的距离相同。

生效场景

全值匹配
最左前缀匹配列前缀 like a% 这种，前面没有百分号就能走索引
匹配范围值，但是使用范围查询后，右边的列就不能走索引了
只访问索引的查询

hash索引

只有精确匹配索引中的所有列才生效。有点类似hashMap根据key取数据。
不支持范围查询，用的场景不是很多

高性能索引的策略

使用索引的时候,索引列不能运算.
字符串很长的索引可以设置值索引前面几个字符作为前缀索引,但是前缀索引不能用来做order by和group by.
多列索引要注意索引的顺序,左边列的值一定要有.选择性最高的列放在最前面,也就是筛选力度最大的列.

聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式.
使用要慎重，目前没啥使用的。优点和缺点都很明显。

顺序索引和UUID作为索引优劣

顺序索引插入快，那个B+Tree 按顺序往后写就行了，UUID要去找位置，型男不太好。
但是高并发情况下，autoincrement会有很明显的锁争用。一般需要改 innodb_autoinc_lock_mode的配置。

覆盖索引

如果一个索引包含（或者覆盖）所有需要查询的字段的值，就称为“覆盖索引”
回表：查完索引再回表里查询。哈哈哈
只能使用b+tree做覆盖索引。
如果一个查询不能搞覆盖索引，可以先根据索引列筛选一波，再筛选其他字段。
Mysql可以做最左前缀的like。
在这里插入图片描述

需要注意的是要根据子查询返回的行，来确定这样搞是否合适。

索引排序

只有当索引的列顺序和order by 子句的顺序完全一直，并且所有列的排序方向都一样的时候，Mysql才能使用索引来对结果做排序。
如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一个表时，才能使用索引做排序。

冗余和重复索引

可以在一个列上建多个索引，但是性能差，不要这样搞。
用不到的索引就删掉。

索引和锁

索引可以让查询锁定更少的行。

六、查询性能优化

只查询需要的字段
可以拆分一个大查询为多个小查询
关联查询用java也能拆分的，这样做更容易命中mysql的缓存，而且执行单个查询可以减少锁的竞争。
一次查大批量，会占用mysql很多内存也不好，所以现在查询基本都分页查询。
MySql会优化关联表的顺序。
子查询带in的最好用exists替代。
分页可以用innner join ,先用id分个页。