Mysql笔记:高性能 mysql 笔记(上)

发表于 2018-01-23 分类于 ToolorToy Disqus：
本文字数： 8.6k 阅读时长 ≈ 8 分钟

以前我觉得像这种概念上的知识大概知道,然后需要的时候能搜索就好.现在认知改变了,基础知识能记忆的应该尽量装在自己脑海里.你在跟别人沟通的时候,这些可以是共识而不再需要花时间去解释,效率更高.同时要是没意识到这些细微的差别,在定位问题的时候你的解决问题能力其实是差了一级.

第一章 mysql 架构和历史
- 1.2 并发控制
- 1.3 事务
  - 隔离级别
  - 死锁
  - 事务日志
  - mysql中的事务
  - mysql 存储引擎
    - innodb
    - myisam
    - Archive
    - memory
    - 其他引擎
第二章:mysql 基准测试
第三章: 服务器性能分析
- 入门
- 一个诊断案例:在疯狂中找到条理
第四章 Schema与数据类型优化
第五章: 高性能的索引
- 索引的类型
- 还能优化的手段

每一章其实都是很大的话题,所以我只是做一下摘要,至少让自己在概念上能过关.计划有上中下三篇.

第一章 mysql 架构和历史

直接上图,别像我,研究那么久的 mysql ,连整体架构都不知道( ps,mysql 的源代码组织太差,是人读的么 )

1.2 并发控制

控制并发的一种思路是用锁

锁的种类

只读锁
只写锁
读写锁

锁粒度

表锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低

页锁: 开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般

行锁: 开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高

MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁.

锁级别

共享锁(S)

排他锁(X)

加锁的方式

自动锁

显示锁

使用锁的方式

乐观锁

悲观锁

1.3 事务

隔离级别

在一个事务中(可能有多个 update,select), 数据修改后,针对不同时刻的查询,这个修改是否应该体现.这带来了脏读现象.从而也使用了不同的隔离级别来避免

推荐阅读:深入分析事务的隔离级别](http://www.hollischuang.com/archives/943)

4 种隔离级别: 未提交读;提交读;可重复读;可串行化;

Read Uncommitted: 事务中的修改,即使没有提交, 其他事务是可见的. 一般不建议使用,在没有明显的好处下,带来了 dirty read (脏读)
Read Committed: 事务只能看见自己已经提交的事务所做的修改.对于这个事务来说是会产生脏读的(两次同样的查询, 会带来不一样的结果)
Repeatable Read: 保证了在一个事务中多次读取同样记录的结果是一致的.这理论上避免了脏读的现象.但无法避免另一个问题:幻读.(某个事务在读取某一个范围内的记录时,另一个事务又在该范围内插入了新的记录,当该事务再次执行相同的查询时,就会产生幻行)

可重复读是 Mysql 默认的事务隔离级别.

Serializale: 可串行化.最高的隔离级别.它通过强制事务串行执行,避免了前面说的幻读问题.

死锁

说一下,这里生成锁的原因不仅仅是时间凑巧,数据加载不是一条条的,磁盘是会预加载的,所以这里也有可能把锁加上导致死锁.有时候这才是分析的难点(我没遇到过,这是大神的 PPT 说的)

事务日志

写数据更快: 更新数据并不是将内存的拷贝值回写到数据本身硬盘所在的位置, 而是追加到事务日志中(使用磁盘一小块区域内的顺序 I/O), 随机 I/O 需要在磁盘的多个地方移动磁头.

事务日志持久后,内存中修改的数据在后台可以慢慢刷回到磁盘中去.所以修改数据需要些两次磁盘.

这里可以体会一个思想,预写日志( Write####Ahead Logging). 当修改一次数据比较慢的时候,我们就找一个地方快速把我们的修改记录记下来.

mysql中的事务

mysql 提供了两种事务型的存储引擎: InnoDB 和 NDB Cluster.
mysql 默认采用自动提交( AutoCommit ) 模式,也就是说,如果不是显示地声明一个事务,每一个查询都被当做一个事物执行提交. 可以通过以下命令来查看

1	show variables like '%autocomm%';

值得注意的点:

autocommit = 0 ,当前所有查询都是在一个事务中,除非显示执行 commit
对于 MyIsam 这样存储引擎的表,由于没有事务的支持,可以把autocommit 当做一直是 ON 的状态
Innodb 采用的是两阶段锁定协议( two-phase locking protocol ). 事务执行过程中,随时可以执行锁定,但锁只有在 commit 或者 rollback 的时候才会释放,并且该事物所有的锁是同一个时刻释放的.

mysql 存储引擎

对下面 sql 执行结果的理解

1	show table status like '%table_name%'

有点 sql 基础都能理解的几个字段: 表名( name );使用的存储引擎( MyIsam ); 创建时间;更新时间; 表注释(comment); 下面详细说一下其它字段的意义(感觉我这个笔记其实变成一个简短的手册了)

row__format: dynamic(行的长度是不确定的,例如包含了 varchar,blob 这样类型的字段.这里可以了解一下 varchar,char,text 之间的区别),fixed(行的长度是可以确定的),compressed(这个类型只存在在压缩表里面) Compact()
rows: 这个好理解,行数.但小心,在 InnoDB里面这个值是估计值,在 MyIsam 是准确值( MyIsam 数据文件,索引,表结构是独立分开的,表的信息,如总行数是可以不通过 count(1) 得出 )
avg__row__length: 平均每一行的字节数,可以除以1024的平方转为 MB
data_length: 表数据大小,字节为单位
max_data_length: 表的最大存储容量,这跟存储引擎有关为什么 innodb 这里显示0,myisam ???
index_length: 索引的大小
data_free: 对于 myisam, 表示已经分配但没有使用的空间,包括删除的行,后续可以被 insert 的空间
auto_increment: 建表的时候,可以设置某个字段为自增(常常为主键) 这里记录的是当前自增的最大值. 我们可以研究一下这里生成的方式:当需要的时候,获取到表中 key 的最大值,加一.之后这个值是常驻在内存中,方便下一次获取.
check_time:
collation: 建表默认字符集和字符列排序规则
checksum: 启用的话,保存的是整个表的实时校验和
createoption: 建表的时的其它选项

innodb

InnoDB 使用 MVCC 来支持高并发, 并且支持4种标准的隔离方式,默认是 Repeatable Read,并且通过间隙锁(next_key locking) 策略来防止幻读的出现.间隙锁使得 Innodb 不仅仅是锁定涉及查询的行,还会对索引中的间隙行进行锁定,以防止幻行的插入( 原来如此, 这就能解释了 )

InnoDB 内部做的优化点:(感觉这些也是坑存在的原因呀) 列一下,感觉都可以当做一个话题来聊一聊:

基于聚簇索引建立的(它使得对主键的查询有很高的效率,但二级索引就需要带上主键)
从磁盘读取的时候采用了预测读
在内存中建立 hash 索引以加速读操作的自适应哈希索引(adaptive hash index ) , 以及能够加速插入操作的插入缓存( insert buffer ) — 这一段话就够我们受的了…..

myisam

特性关键词: 全文检索,压缩, 空间函数( GIS ),不支持事物和行级锁,崩溃后是无法恢复的.三个文件(表结构,表数据myd,表索引 myi)

加锁: 表锁,读取的时候共享锁, 写入的时候,排它锁

修复: 修复不等同于奔溃后恢复. 修复会使得一些数据丢失.

check table t_name;
repair table t_name ;
检查和修复完之后可以在看到 check_time 的更新
show table status like '%t_name%';

索引: 对 BLOB 和 TEXT , 可以基于前500个字符创建索引; myisam 的全文索引是基于分词.
性能: 延迟更新索引键( DELAY_KEY_WRITE ) 不立即把修改的索引写入磁盘; 性能的瓶颈几乎是出在表锁上.
压缩表: 压缩表适合做仓库存储, 修改一条数据经过解压–修改–压缩储存; 这对变化频繁的数据是不利的.

memory

需要快速访问,数据不会被修改, 重启后丢失也无所谓.那就使用 memory 引擎,数据是全部加载到内存的.]

其他引擎

基本没用过: blackhole ; csv ; federated ;merge ; NDB 集群引擎

merge 表是一类 myisam 表的聚集.表结构一模一样. merge 的好处是对外提供了一个统一视图,让别人以为只有一张表.这是我的理解.

第二章:mysql 基准测试

首先明确测试啥:怎么衡量你的性能? 需要的指标是什么? 指标的基准又是什么

第三章: 服务器性能分析

首先判断到底是服务器的问题,还是 mysql 的问题,然后再定位是不是某一条 sql 引起的.

这是我之前的一种思维误区吧, 我一来就是先分析 sql.这也是没有整体把握的体现

入门

分析慢查询日志: (my.cnf 里面可以设置当一个查询超过多少时间的时候,就会被记录下来.这样方便做性能优化) 作者建议使用 pt-query-digest 自顶向下的分析方法,先得到一个整体的分析报告. 而不是一来就打开整个慢查询日志开始干(好吧, 我的确是这样的)

show variables like 'slow_query_log';
show variables like 'long_query_time';

set global slow_query_log=1; # 这个只对当前数据库生效(对当前session, 建议退出重新登录 ),重启 mysql 就失效了.永久生效的方式是修改配置文件 my.cnf .
slow_query_log =1
set global long_query_time=3;
slow_query_log_file=/tmp/mysql_slow.log 默认是放在/var/lib/mysql/localhost-slow.log
long_query_time 默认查询超过10s 会记录下来,可以在 my.cnf 修改.刚好等于10s 是不会记录下来的.

然后可以通过命令 select sleep(5) 看看慢查询日志长什么样

# [email protected]: root[root] @ localhost []
# Thread_id: 49 Schema: QC_hit: No
# Query_time: 5.003580 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1515237341;
select sleep(5);

有了慢查询日志的概念之后,我们学习一下 mysqldumpslow 这个工具的使用,例如

-c 访问计数
-t top 的意思
-g 可以是用正则过滤
-r 返回记录
-l 锁定的时间
mysqldumpslow -s c -t 10 slow.log | less # 日志中记录最多的10个 sql

使用第三方工具:

总是有更好的工具让你更快定位到问题.

pt-query-digest: 一个 perl 脚本,可以对整个慢查询 log 输出一个统计报告: pt-query-digest slow_query_log.log

到了这一步,定位到是单条查询的问题,这时候才是想办法优化 sql.( 好吧,我之前的文章一来就是在优化 sql, 现在在大神的上帝视角来看,我这是小毛孩 )

sql 的优化手段有: 使用explain 查看 sql 的执行情况,主要关注命中的索引; 开启 profiles 查看 sql 消耗的资源.

explain
1. type: 命中索引类型. 结果好到坏– range(常数值的范围,索引范围扫描) > index(索引全扫描) > ALL (全表扫描). 一般查询应到达 range 级别
2. rows: sql 执行检查的记录数
  主要关注这两点,其他可以查资料了解
profiles

1
2
3

> set profiling=1; (默认没开启)
> your sql ;
> show profiles;

SQL层面已经优化不动, 这时候我的建议:从客户端请求到最后获取数据,每一个链路都梳理一遍.性能肯定有办法提升.例如

Client 是否可以在本地加 cache?
Client 到 Server 的连接(长连接短连接了解下, 连接池了解下)
Server端: 分库分表,使用主从(分离读写),冷热隔离
以上试过还有问题,这样的业务量,国内第一梯队的互联网公司了吧. 考虑招一个架构师吧

一个诊断案例:在疯狂中找到条理

在大量的输出中快速找到问题可能发生的地方
这种需要线上处理经验的,我实在很好奇在这里纸上谈兵的作用.能不能把 DBA 聚集起来,模拟线上问题发生,新手可以在上面学习.leecode 是考你基本能力,这个平台的目标是靠你工程的能力.可以想一想.其实大数据处理也一样,大公司现在的做法是每周期的线上比赛.

服务器基本的状态: 多少 cpu, 核心, 操作系统版本,数据库版本,磁盘使用情况, io 情况, 数据库数据量,

too mamy connection : 我的第一反应是调高默认配置的连接数,囧…..

第四章 Schema与数据类型优化

建议:

最小原则: 在确定情况下, 越小的类型说明占用磁盘越少,读取越快
简单:整形比字符操作代价更低(用内建类型存储时间和用整形存储 ip)
避免 NULL: 当可为 NULL 的字段作为索引,会多占一个字节的空间,同时在 myisam,可能是一个固定长度的 int 变成可变长度的索引.也有例外, innodb 使用单独的位 bit 储存,这对于稀疏矩阵是有很好的空间效率的.但这点不适用与 Myisam.

timestamp 和datetime都可以表示到秒的时间,但 timestamp 存储空间少一半,并且会根据时区改变,具有自动更新的能力,容易, timestamp 能表示的时间范围会小一些.

timestamp 4个字节, 时间在1970到2037之间可以为空,但不允许自定义值.以 UTC 格式保存

datetime 8个字节,可以 null, 可以自定义,系统不会修改其值.与时区无关.

int(11) 里面11 的意思是’限制显示时候的宽度’,并不是限制 int 的大小.
尽量存储整形,然后了根据业务进行转换.

varchar 和 char

varchar 是按需存储,也就是说它存储的是可变字符串.(但是当你指定 row_format=fixed 的时候, varchar 就变成了定长存储)
一个 varchar(10) 需要11个字节的存储空间,varchar(1000) 则需要1002 个字节,其中2个字节是用来存储列的长度
varchar 是变长的,可以提高存储性能; 但update的时候,

要是行可能比原来的更长,这会导致做额外的工作:如果一个行占用的空间增加,并且页内没有足够的空间可以存储,这种情况下,不同的存储引擎处理方式是不一样的, myisam 会将行拆成不同的片段存储, innodb 则需要分裂页来使行可以放进页内.其他的存储引擎也许从不在原数据位置更新数据.

使用 varchar 的情况: 字符串列最长长度比平均长度大很多;列更新比较少,(所以碎片不是问题);使用了像 uft-8这样复杂的字符集,每一个字符都使用不同的字节数进行存储.

在5.0版本后, mysql 在存储和检索时会保留末尾的空格,但4.1之前是剔除的. innodb 更灵活, 它会把过长的 varchar 存储为 blob?

char: 定长;存储 char 类型时, mysql 会提出所有末尾空格. char 在比较的时候会采取填充空格的方式以方便进行比较.
char 适合存储短字符,或者所有值的长度很接近.例如,存储 md5值.对于定长的字符更不容易产生碎片. 对于非常短的列, char 比 varchar 更有效率. 例如存储 Y 或者 N, char(1) 需要一个字节, varchar(1) 需要两个.
eg: 同样都是 char(10) , ‘aa’, ‘ aa’, ‘aa ‘是不一样的.’aa ‘末尾的空格会被截断.

memory 引擎只支持定长的行,有可变长字段也会根据最大长度分配最大空间.不过,填充和截取空格的行为是在 mysql 服务层操作的,对存储层透明.
同理还有 binary 和 varbinary 的区别. 它们存储的是二进制字符串(存储的是字节码).字节码比较会更快.

对于 BLOB 和 TEXT , mysql 经常是当做一个对象来处理 (存储在一个外部存储区域来存储), 使用一个指针(1~4个字节)指向它们实际的存储的值. BLOB 存储的是二进制数据,没有排序规则或字符集. TEXT 有.在进行排序的时候, TEXT 实际会比较的字符长度在 max_sort_length.memory 引擎不支持 BLOB 和 TEXT .

使用enum存储字符串类型

create table enum_test(e enum(‘aa’,’bb’,’cc’) not null) ;

mysql 存储的时候实际上是把变量e 存储为整形,(只占1~2个字节) .在 .frm 文件中存储数字–字符串的映射关系的存储表.会导致疑惑的地方就是你在 order by e 的时候,不是按字母,是按枚举值

timestamp and datetime

datetime 精度到秒,与时区无关.使用8个字节存储,表示范围是1001年到99999年. timestamp( 默认建议)保存了自1970年1月1日以来的秒数,所以它只需要4个字节的存储时间(1970~2038).timestamp 有时区的概念,不同时区(有时候是主机的时间)存储的值不一样.

bit

底层来看,bit 是一个字符串类型.不同的存储殷勤对 bit 的存储是不一样的( myiasm 17个 bit为了节省空间, 使用3个字节解决.但 memory 和 innnodb 则是一个 bit 一个整形, 不节省空间.)

现在用来越觉得 mysql 难的原因在于他就像一个城邦, 缺乏内部的一致性.导致需要一个高级 DBA 来处理.这样的数据库还能流行?

最后: 建议

尽可能用整形,并且加上自增
使用 enum 作为固定长度的标识行是可行的, 但大部分情况最好避免
避免字符串. 一来消耗空间, 同时在 myisam 上使用字符串作为标示行要小心,myiasm 会对字符串进行压缩索引,这会导致查询变慢. (我简单的查询, 500 次性能反而是用 set 的好一点点.)
混用范式化和反范式化: 背景就不说了,最常见的反范式化数据的方法是复制或者缓存.(在不同的表里面存储相同的列,使用触发器更新缓存值.)
alter table 很耗性能,最差的是整张表重建.

第五章: 高性能的索引

索引的目的:

减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机 io 变成顺序 io

索引并不总是有效的: 小表, 简单的扫描全表更高效; 中大型表,索引是有效的,特大型表,建立和使用索引的代价随之增长.

下面是无法使用索引的,但只要一个简单的变换就可以.体会这里面的区别

select * from table where id + 1 = 5 ;
select * from table where to_days(current_date) - to_days(date_col) <= 10 ; 
select * from table where id = 1 or cnt_id = 3 (建议使用 union all ) 在早版本的4.1 mysql 是无法使用索引的, 后面引入了 索引合并

索引的类型

B-tree 索引

最左匹配原则

哈希索引

在 memory 引擎下工作(该引擎也可以使用 B-tree)

空间索引 (R-Tree)
全文索引
聚簇索引

这不是一种索引类型,只是一种数据存储方式.细节依赖于具体实现. Innodb 的聚簇索引是在同一个结构中保存了 B-Tree 索引和数据行.(通过主键聚集数据)

看完这一段, 聚集索引和 B+tree 到底什么关系, 恍然大悟.

还能优化的手段

索引的选择性

前缀索引: 优化的是索引本身的大小

对于占用空间较多的字符串,全部作为索引是不划算的.所以有人提出了一种思想:那我们就用部分字符串当做索引不就好了,例如字段的前4个字符.但仔细想,这会带来什么问题呢? 要是有个字段,命名方式是 ‘nemo’ + 自定义部分,你会发现这时候索引就是无效的,每一个都是以 nemo 为索引. 所以有人提出了Cardinality 的概念. 去字段前4个字符为例,具体计算如下

1 2	select count(distinct left(字段名,4) )/ count(1) from table

该值越大(值的差异越大),说明分散效果越好,说明越适合当索引.可以适当调整要取的字符数,怎么调,根据实际的数据和经验调

缺点: 是无法使用前缀索引做order by 和 group by ,mysql 也无法利用前缀索引做覆盖扫描.

联合索引

第五章索引未完,待续…