MySQL学习总结

最新推荐文章于 2023-07-02 12:49:06 发布

tkom

最新推荐文章于 2023-07-02 12:49:06 发布

阅读量321

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/dgyingling/article/details/104642276

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

参考自：《高性能mysql》（第三版）

1 MySQL基本架构

MySQL 主要分为 Server 层和存储引擎层：

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。
存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。
SQL 的执行过程分为两类，一类对于查询等过程如下：权限校验—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎
•对于更新等语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log prepare—》binlog—》redo log commit

2 数据库锁

不同的存储引擎支持不同的锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
行级锁：开销大，枷锁慢，会出现死锁；锁定粒度小，发生冲突的概率低，并发度最高。
页面锁：居于表级锁和行级锁中间，会出现死锁。
MyISAM和MEMORY存储引擎，采用表级锁；
BDB采用页面锁，也支持表级锁
InnoDB默认采用行级锁，也支持表级锁；

查看表级锁争用情况：检查table_locks_waited 和 table_locks_immediate 两个值，table_locks_waited越高，争用越多

show status like 'table%'

表级锁分为表共享读锁和表独占写锁。

2.1 MyISAM存储引擎

2.1.1 锁调度问题

MyISAM读写锁互斥，当读进程和写进称同时获取锁时，写进称会优先获取到写锁，因为MySQL 认为写请求一般比读请求要重要，这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。

通过指定启动参数low-priority-updates，使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性，降低该语句的优先级。
给系统参数max_write_lock_count 设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。

2.2 InnodDB存储引擎

InnoDB 与 MyISAM 的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。

事务的ACID：原子性、一致性、隔离性、持久性
事务的隔离级别：未提交读、已提交读、可重复读、序列化；
并发事务带来的问题：脏读、幻读、不可重复读、更新丢失；

隔离级别\并发问题	脏读	不可重复读	幻读
未提交读	是	是	是
已提交读	否	是	是
可重复读	否	否	是
序列化	否	否	否

2.2.1 可重复读实现原理

可重复读通过MVCC（多版本并发控制）来实现。MVCC只在读已提交和可重复读级别下工作，读已提交级别查询的总是最新版本的记录。

每新增一行会给行加两列（创建版本号和删除版本号），用系统版本号作为创建版本号。
删除一行时将系统版本号作为改行删除版本号。
修改时，会先复制该行，并将系统版本号作为创建版本号，然后删除原来的行，将系统版本号作为删除行的删除版本号。
增删改时，会将系统版本号作为当前事务版本号。
查询时，会查询创建版本号小于或等于当前事务版本号的数据行，以及删除版本为NULL或者删除版本号大于当前事务版本号的行。

如果是快照读的情况，则可重复读的隔离级别已经避免了幻读的问题；
如果是当前读的情况（先select，然后update，再select），则无法避免幻读。

2.2.2 解决幻读的两种方法

使用序列化隔离级别，select会自动添加share next-key(共享间隙锁和行锁)；
在可重复读的级别下，使用select ... for update或者select ... lock in share mode查询，它们会自动添加行锁和间隙锁。

2.2.3 隔离级别查询及设置

查询隔离级别
默认的隔离级别为可重复读。

#mysql8.0
select @@transaction_isolation;
#mysql8.0之前
select @@tx_isolation;

临时设置隔离级别

set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

2.2.4 行锁

行锁类型：共享锁、排他锁；
表锁类型：意向共享锁、意向排他锁。

注：在加行锁时，会先加意向锁。

查看行级锁争用情况：检查Innodb_row_lock状态变量

show status like 'innodb_row_lock%';

如果发现锁争用比较严重，如 InnoDB_row_lock_waits 和 InnoDB_row_lock_time_avg 的值比较高，还可以通过设置 InnoDB Monitors 来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

2.2.5 行锁实现方式

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁。

2.2.6 加锁方法

共享锁：select … lock in mode;
排他锁：select … for update;
表锁：lock tables a read, b write;
解除表锁：unlock tables;

2.2.7 间隙锁

对于键值在条件范围内但并不存在的记录，叫做“间隙 (GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key 锁)。

2.2.8 死锁

发生死锁后，InnoDB 一般都能自动检测到，并使一个事务释放锁并回退，另一个事务获得锁，继续完成事务。但在涉及外部锁，或涉及表锁的情况下，InnoDB 并不能完全自动检测到死锁，这需要通过设置锁等待超时参数 innodb_lock_wait_timeout 来解决。

避免死锁的方法：

尽量使用较低的隔离级别;
精心设计索引，并尽量使用索引访问数据，使加锁更精确，从而减少锁冲突的机会;
选择合理的事务大小，小事务发生锁冲突的几率也更小;
给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁;
不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会;
尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响;
不要申请超过实际需要的锁级别;除非必须，查询时不要显示加锁;
对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。

3 优化MySQL Server

用 SHOW VARIABLES 和 SHOW STATUS 命令查看 MySQL的服务器静态参数值和动态运行状态信息

调优参数：
innodb_buffer_pool_size: 定义了 InnoDB 存储引擎的表数据和索引数据的最大内存缓冲区大小。这个值设得越高，访问表中数据需要的磁盘 I/O 就越少。在一个专用的数据库服务器上，可以设置这个参数达机器物理内存大小的 80%。

innodb_flush_log_at_trx_commit: 用来控制缓冲区中的数据写入到日志文件以及日志文件数据刷新到磁盘的操作时机。对这个参数的设置可以对数据库在性能与数据安全之间进行折中。

当这个参数是 0 的时候，日志缓冲每秒一次地被写到日志文件，并且对日志文件做向磁盘刷新的操作，但是在一个事务提交不做任何操作。
当这个参数是 1 的时候，在每个事务提交时，日志缓冲被写到日志文件，并且对日志文件做向磁盘刷新的操作。
当这个参数是 2 的时候，在每个事务提交时，日志缓冲被写到日志文件，但不对日志文件做向磁盘刷新的操作，对日志文件每秒向磁盘做一次刷新操作。

在 MySQL 官方手册中，为了确保事务的持久性和复制设置的一致性，都是建议将这个参数设置为 1 。

innodb_additional_mem_pool_size: 用来存储数据库表字典和其他内部数据结构的内存池的大小，其默认值是 1MB。应用程序里的表越多，则需要在这里分配越多的内存。
innodb_lock_wait_timeout: MySQL 可以自动地监测行锁导致的死锁并进行相应的处理，但是对于表锁导致的死锁不能自动的监测，所以该参数主要被用于在出现类似情况的时候等待指定的时间后回滚。系统默认值是 50 秒，用户可以根据应用的需要进行调整。
innodb_log_buffer_size: 日志缓存大小。默认的设置在中等强度写入负载以及较短事务的情况下，一般都可以满足服务器的性能要求。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存，因为它每秒都会刷新一次，因此无需设置超过 1 秒所需的内存空间。通常设置为 8~16MB 就足够了。越小的系统它的值越小。系统默认值是 1MB。
innodb_log_file_size: 一个日志组(log group)中每个日志文件的大小。此参数在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是带来的副作用是，当系统灾难时恢复时间会加大。系统默认值是 5MB。

4 主从复制，读写分离

4.1 创建两个数据库

——mysql-data
————3307
——————data
——————my.cnf
——————mysql_3307.err
————3308
——————data
——————my.cnf
——————mysql_3308.err

并赋予mysql用户的权限
chown -R mysql:mysql mysql-data/

my.cnf内容

[client]
port=3306
socket=/Users/tkom/mysql_data/3307/mysql.sock


[mysqld]
user=mysql
port=3306
socket=/Users/tkom/mysql_data/3307/mysql.sock
basedir=/usr/local/mysql
datadir=/Users/tkom/mysql_data/3307/data
log-bin=/Users/tkom/mysql_data/3307/mysql-bin
server-id=6


[mysqld_safe]
log-error=/Users/tkom/mysql_data/3307/mysql_3307.err
pid-file=/Users/tkom/mysql_data/3307/mysqld.pid

初始化数据库

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/Users/tkom/mysql_data/3308/data

在这里插入图片描述

启动数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/Users/tkom/mysql_data/3308/my.cnf --datadir=/Users/tkom/mysql_data/3308/data --basedir=/usr/local/mysql --user=mysql 2>&1 > /dev/null &

连接数据库

/usr/local/mysql/bin/mysql -uroot -p -S /Users/tkom/mysql_data/3308/mysql.sock

4.2 主从复制

连接主数据库

show master status;

change master to master_host='localhost',master_user='root',master_password='123456',master_port=3307, master_log_file='maste-bin.000005',master_log_pos=161835;

show slave status\G;

如果从数据库需要重新连接主数据库，可以先执行stop slave;，然后执行2。

5 sql实战

5.1 查询给定几列不为空且不为空字符串的数量

select count(column1 <> "" or null) , count(column2 <> "" or null)  from table_name

5.2 查询字段给默认值

select IFNULL(column1, 2) from table_name

tkom

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习总结

MySQL数据库锁不同的存储引擎支持不同的锁表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。行级锁：开销大，枷锁慢，会出现死锁；锁定粒度小，发生冲突的概率低，并发度最高。页面锁：居于表级锁和行级锁中间，会出现死锁。MyISAM和MEMORY存储引擎，采用表级锁；BDB采用页面锁，也支持表级锁InnoDB默认采用行级锁，也支持表级锁；查看表级...
复制链接

扫一扫