mysql优化学习笔记

一时失言乱红尘 2022-05-16 01:20 208阅读 0赞

mysql入门之后需要掌握的知识

mysql的架构  
客户端向mysql服务器发起查询请求，首先会建立连接，连接的时候采用线程池模式，以较少的线程来处理较多的连接，然后将请求交给处理线程，处理线程首先会导查询缓存中查找是否有缓存的查询结果，如果没有就将客户端发来的sql交给解析器，解析器会优化查询语句，然后调用存储引擎提供的API对磁盘数据进行读取，然后将结果返回并放到查询缓存中。

不同存储引擎会单独提供自己的API供mysql服务器调用，且相互独立，类似的架构还有php的php-cgi模式，docker的docker client和docker server模式。

优化查询  
生成解析树（可能会重写查询，决定表的读取顺序，增加索引），可以通过请求优化解释器过程的各个因素来提高自己编写查询语句的水平

并发控制：服务器层和存储引擎层

读写锁：也叫共享锁/排他锁  
锁粒度：锁定必要部分，减少开销  
锁策略：在锁的开销和的安全性之间寻求平衡。由存储引擎自己来实现  
锁策略实现的相关例子：  
表锁：开销最小，由mysql服务器实现，不是存储引擎实现，可用于alter table等语句  
行级锁：最大程度支持并发处理，最大开销，在存储引擎(innodb.xtradb)实现

事务：原子性的sql查询  
用start transaction开始  
用commit结束  
用rollback回滚

ACID测试：  
原子性、一致性、隔离性、持久性  
隔离性分级：  
未提交读：没有提交也对外可见  
提交读：不可重复读，两次读取的结果可能不一致  
可重复读(musql默认隔离级别)：存在幻读问题。由于在某个读取的过程中有新的数据行插入导致第二次读比第一次读的数据多几行，从而产生幻行。由版本控制（时间戳解决）  
可串行化：强制事务串行化执行，加锁读  
设置隔离级别：set transaction isolation level XXXX XXXX

死锁：多个事务以不同的顺序锁定Innodb处理死锁。解决办法：将持有最少行级排他锁的事务进行回滚  
事务日志：修改缓存，将日志写到硬盘中，轮询刷回硬盘

事务型存储引擎：  
mysql中：innodb和NDBClustor  
第三方：xtraDB和PBXT

自动提交：mysql默认为自动提交  
设置自动提交变量：set autocommit = 0;(1表示启用，0表示禁用)，只对事务型存储引擎的表有用。  
在执行之前会强制执行commit提交当前的活动事务的命令：alter table、lock table

不能在同一个事务中混合使用事务型表和非事务型表，当做回滚操作时，非事务型表无法回滚，导致数据库处于不一致的状态

两阶段锁定协议：innodb采用的协议。隐式锁定：在事务执行的过程中买随时都可以执行锁定，锁只有在执行commit或者rollback的时候才会释放，并且所有的锁是在同一时刻被释放。  
显式锁定：select…lock in share mode、select … for update

多版本并发控制(MVCC):避免加锁操作，非阻塞的读操作，锁定必要的行写操作  
实现原理：通过保存数据在某个时间点的快照来实现的。依照事务开始的时间点  
分类：乐观并发版本控制和悲观并发版本控制  
innodb的MVCC：在每行记录后面保存两个隐藏的列（行创建时间，行过期时间（删除时间））。存储的是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号  
在可重复读的隔离级别下，MVCC具体的操作：  
select：返回结果会检查两个条件：首先innodb之查找版本早于当前事务版本的数据行。行的删除版本要么未定义，要么大于当前事务版本号。  
insert：为新插入的每一行保存当前系统版本号作为行版本号  
delete：为删除的每一行保存当前系统版本号作为行删除标识  
update：为插入的新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

mysql 的存储引擎：  
数据库—-数据目录下的一个子目录  
数据表—–数据库子目录下创建一个和表同名的.frm文件保存表的定义

innodb存储引擎：处理大量的短期事务，很少会被回滚。具有自动崩溃恢复特性。  
数据存储在表空间中，由一系列的数据文件组成，将每个表的数据和索引存放在单独的文件中。  
采用MVCC来支持高并发，实现了四个标准的隔离级别，默认是可重复读，采用间隙锁策略防止幻读，防止幻影行的插入。  
表是由聚簇索引简历，对主键查询有很高的性能。  
存储格式平台独立，可将数据和索引文件在多平台中相互移植  
可预测性读，自动在内存中创建Hash索引以加速读操作的自适应哈希索引，能够加速插入操作的插入缓冲区。  
支持热备份

Mylsam存储引擎  
全文索引、压缩、空间函数等。不支持事务和行级锁且崩溃后无法安全恢复。表小、只读场景  
存储：数据文件(.myd)和索引文件(.myi)。包含动态或者静态（长度固定）行。变长行（256TB），可通过改变max\_rows和avg\_row\_length选项的值来实现，两者相乘就是最大大小。  
特性：加锁与并发（对整张表加锁，共享锁和排他锁，并发插入）  
修复（手工或者自动执行检查和修复操作，通过check table mytable检查表的错误，repair table mytable修复错误。或使用myisamchk命令行工具进行检查和修复操作）  
索引特性（前500个字符创建索引，全文索引）  
延迟更新索引键（指定delay\_key\_write选项，写到内存中的键缓冲区，只有在清理键缓冲区或者主机崩溃时会造成索引损坏，需要执行修复操作）  
mylsam压缩表：表创建并导入数据以后，不会再进行修改操作。使用muisampack对mylsam表进行压缩（打包pack），不能进行修改，极大减少磁盘空间占用，减少磁盘I/O，提升查询性能。  
mylsam性能：对索引键缓冲区的mutex锁，MariaDB基于端的索引键缓冲区机制来避免该问题。表锁问题对性能消耗比较大。

Archive引擎  
CSV引擎：将普通的CSV文件（逗号分割值的文件）。用于excel和数据库的数据交互的存储机制。  
NDB集群引擎：用作Mysql集群