Nemo的轨迹

work hard, be persistent, and good luck

0%

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

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

第一章 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)

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

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

    1
    2
    3
    4
    check table t_name;
    repair table t_name ;
    检查和修复完之后可以在看到 check_time 的更新
    show table status like '%t_name%';
  • 索引: 对 BLOB 和 TEXT , 可以基于前500个字符创建索引; myisam 的全文索引是基于分词.

  • 性能: 延迟更新索引键( DELAY_KEY_WRITE ) 不立即把修改的索引写入磁盘; 性能的瓶颈几乎是出在表锁上.

  • 压缩表: 压缩表适合做仓库存储, 修改一条数据经过解压–修改–压缩储存; 这对变化频繁的数据是不利的.

Archive

只支持 insert 和 select , 利用 zlib 对插入行进行压缩, io 会比 mysiam 更少.但每一次 select 需要扫描全表.Archive 适合日志和数据采集类的应用.

memory

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

其他引擎

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

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

第二章:mysql 基准测试

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

第三章: 服务器性能分析

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

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

入门

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 这个工具的使用,例如

1
2
3
4
5
6
-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.
1
2
3
4
5
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

推荐:Relational Database Index Design and the Optimizers

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

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

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 是无法使用索引的, 后面引入了 索引合并

索引的类型

  1. B-tree 索引

最左匹配原则

  1. 哈希索引

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

  1. 空间索引 (R-Tree)

  2. 全文索引

  3. 聚簇索引

这不是一种索引类型,只是一种数据存储方式.细节依赖于具体实现. 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 也无法利用前缀索引做覆盖扫描.

  • 联合索引

第五章索引未完,待续…