高性能MySQL

一、基础介绍

连接管理 每个客户端连接在fu服务器中拥有一个线程,这个连接的查询只会在单个线程执行。
锁机制 数据库有表锁、行锁两种粒度

事务

  • 原子性

要么全部提交成功、要么全部失败回滚

  • 一致性

数据库总是从一个一致性状态切换到另一个一致性状态,比如刚开始100块钱,最后还是100块钱。

  • 隔离性

一个事务在最终提交前对其他事务是不可见的

  • 持久性

一个事务提交成功,就会持久化到数据库。

隔离级别

  • 未提交读

事务中的修改没有提交、对其他事务依然是可见的。事务可以读取未提交的数据,称为脏读

  • 提交读(不可重复读)

一个事务开始的时候,只能看见已经提交的事务所做的修改。重复读会读到前一次事务已经提交的数据,导致不一样。

  • 可重复读 (MySQL的默认事务隔离级别)

多次读取同一个记录,结果是一致的。但是无法解决幻读,幻读就是事务在读取某个范围的时候,另外一个事务插入了新记录,这个时候会发现多一行。(因为无法锁住新增的一行)。 为了解决这种问题,InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)

  • 可串行化

无并发

事务日志

事务日志是顺序写、也就是追加的方式进行IO,比随机IO效率高的多。

MVCC

MVCC是行级锁的一个变种,但是它在很多情况下避免了加锁操作。MVCC通过保存某个时间点的快照、这样与其他事务就隔离开了。InnoDB的MVCC,通过在每行记录后面保存两个隐藏的列来实现(创建时间过期时间)

创建时间 应该比较好理解,创建有先后。 过期时间 是指,当事务1在select的时候发现一条记录的删除时间是4,因为执行事务1的时候,事务4在执行删除,此时 4 > 1,事务1在select的时候会把那条记录查询出来,因为事务1先执行版本号更小。详细例子可以参照https://blog.csdn.net/whoamiyang/article/details/51901888

InnoDB存储引擎

概览 InnoDB采用MVCC支持高并发,并且实现了四个标准的隔离级别,其默认级别是可重复读,通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,防止幻读

MyISAM特性

  1. MyISAM是对整张表加锁,而不是针对行,读取时会对需要读到的所有表加共享锁,写入时则对表加排它锁。但是在表有读取查询的同时,也可以往表里插入新的记录。
  2. 支持在每次修改的执行完成的时候,不会立即将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区(in-memory key buffer),只有在清理键缓冲区或者关闭表的时候才会将对应的索引快写入到磁盘。但是会在主机崩溃的时候损坏索引 因此需要执行修复操作。

基准测试工具

  • ab

ab是apache HTTP的一个工具,能够测api接口的性能

  • http_load

类似ab,也是api接口性能测试,它可以通过载入一个文件(url集合)随机选择url进行测试

  • JMeter

图形化的测试工具,也是HTTP压测常用

二、性能优化

慢查询日志

根据记录内容分为以下好几种日志:

a、错误日志:记录启动、运行或停止mysqld时出现的问题。

b、通用日志:记录建立的客户端连接和执行的语句。

c、更新日志:记录更改数据的语句。该日志在MySQL 5.1中已不再使用。

d、二进制日志:记录所有更改数据的语句。还用于主从复制。

e、慢查询日志:记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

show variables like “%slow%”

分析日志 – mysqldumpslow

分析日志,可用mysql提供的mysqldumpslow,使用很简单,参数可–help查看

推荐用分析日志工具 – mysqlsla

查询性能分析

> show profiles

> show profile for query 1

可以查看每一步查询的耗费时间。

show status where variable_name like 'Hander%' or variable_name like 'Created%'

// 可以查看是否创建了临时表,是否用到了索引

科普一条linux命令, tail -c +3214 file.log | head -n100
意思是从3214字节开始,截取前100行。

show global status where Variable_name like "Threads%" or Variable_name like "Que%"


能够看出当前查询的次数和当前连接的个数。如果正在查询的线程数明显上升,同时每秒的查询数相比正常数据有严重下跌,可能是内部遇到什么瓶颈了,有线程在排队。

show processlist


这个能看出当前查询的线程状态信息

Strace

strace工具可以查看系统调用的情况。

strace -cfp $(pidof mysqld)

表优化

  • 索引禁止用NULL

用NULL会用额外的空降存储,还有可能导致MyISAM这种引擎搞成可变大小的索引

  • 整数类型

TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT 分别使用8, 16, 24,32,64 位存储空间
如果想更大,可以用unsigned参数,这样变成了无符号的数、但是其实底层空间是一样的。

  • DECIMAL

能存储特别大的数字,最多65个数字,每四个字节存9个数字(4位能存0-8)

  • Varchar
  • Char
Comments
Write a Comment