[bigdata-071] 《高性能mysql》读书笔记（不全未整理）

1. mysql架构 1.1 逻辑架构 ? ? [客户端] --> [连接/线程处理-->[查询缓存，分析器]-->[优化器]] --> [存储引擎] ? ? 每个客户连接在服务器进程都有自己的线程，每个连接的查询都会在某个指定的单独线程完成，这些线程轮流运行在某个cpu上。服务器可以缓存线程。 ? ? mysql解析查询，创建一个内部数据结构，并对其进行优化。比如，包括重写查询，决定查询的读表顺序，选择必需使用的索引。 ? ?? 1.2 并发控制 ? ? 不止一个查询同时修改数据，则有并发控制问题。并发处理有两个层面：服务器层，存储引擎层。 ? ? ? ?? ? ? 读锁/共享索：一个用户读，另一个用户修改，则会有问题。 ? ? 写锁/排它锁：一个写锁会阻塞其他读锁和写锁。 ? ? 锁粒度：表锁table lock，行级锁 row locks。 1.3 事务 ? ? 事务是一组原子性的SQL查询语句，视为一个工作单元，如果数据库引擎能成功对执行它的所有SQL查询语句，那么就执行成功，如果任何一条语句不能执行或者崩溃，那么所有语句就不会执行。要么都执行，要么一条都不执行，回滚原状。 ? ? ACID事务。 ? ? 隔离级：read uncommitted 读取位未提交内容; read committed 读取提交内容; repeatable read可重读（mysql的默认隔离等级）; serializable可串行化。 ? ? 可串行话是最高级别的隔离等级，强制事务排序，每个数据行加锁，但很慢，会导致大量的延时。 ? ? 死锁：注意死锁问题。 ? ? 事务日志：它可以市的事务处理更加高效。原理：追加写文件，比随机写文件要更快。 ? ? mysql有三个事务引擎：InnDB,NDB cluster,Falcon，第三方引擎也支持事务，比如solidDB和PBXT。 1.4 mysql的存储引擎 ? ? mysql把每个库database保存为数据目录下的一个子目录。当创建一个表时，mysql会在和表名同名的，以.fm为后缀的文件中存储表的定义表的meta数据。 ? ? 获知具体每张表使用何种存储引擎，可以使用show table status命令。 ? ? show table status like 'trust_api_record'; ? ? show table status like 'trust_api_record' G ? ? 能看到Engine的类型，通常都是InnoDB ? ? MyISAM把每个表存成两个文件：数据文件和索引文件，后缀分别是.MYD和.MYI。 ? ? InnoDB将所有数据共同存储在一个或者几个数据文件，一般称之为表空间。 ? ? 内存表：访问速度快，数据永不改变，重启后不需要保留。 1.5 场景 ? ? 单纯的每秒高达数千记录的插入，数据引擎MyISAM,Archive,PBXT等。 ? ? 如果需要运行报告程序，对数据进行查询汇总，需要提取大量数据：使用mysql内置的复制特性，将数据克隆到第二太服务器，在从服务器上运行这些对时间和cpu苛刻的程序。主服务器只负责插入数据，这样不影响实时的数据记录。这是临时的，随着应用规模的增长，这个策略是不可靠的，无法稳定运行。 ? ? 另一种是使用合并表，不要将所有数据记录在同一张表里，而是记录到不同的表，表明可以根据年份，原表明，日期，月份进行区分，然后进入合并表进行查询。 ? ? 订单处理：必须使用事务。InnoDB最佳。 ? ? 股票报价：MyISAM最合适。 ? ?? ? ?? 2. 寻找瓶颈，基准测试与性能分析 2.1 定义 ? ? 什么是改进对象？特定查询，数据库架构，硬件？ ? ? 基准测试，性能分析。 ? ? 基准测试：系统的整体性能。 ? ? 性能分析：在哪里花费最多时间，消耗了最多资源。 2.2 基准测试策略 ? ? 两种策略：整个应用测试; 单独测试mysql。 2.3 测试指标 ? ? 单位时间事务处理量/吞吐量 ? ? 响应时间/时延 ? 比如：95%的响应时间是5毫秒，表示95%的查询会在5毫秒内完成。 ? ? 扩展性 ?并发连接增加，性能如何。 ? ? ... 2.4 测试工具 ? ? ab; http_load; jmeter ? ? mysqlslap; sysbench; database test suit; mysql benchmark suite; super smack; dbt2 tpc-c 2.5 性能分析 ? ? 配置mysql ? ? ? ? ? ? 把每一步写入到数据库记下来然后再分析，没啥神奇的事情。 ? ? mysql有两种查询日志：普通日志，慢速日志。 ? ? 配置my.cnf可以设置日志细节。 ? ? show status，检查每一次查询执行哪些动作。 ? ? show profile 3. 架构优化和索引 3.1 索引是什么 ? ? 数据类型越小越好;越简单越好; 尽量避免NULL; ? ? 索引对高性能非常重要。 ? ? 不同的数据类型，使用不同的索引 ? ? b-tree: 比如，文本字段按照字母顺序在树上进行排序 ? ? 哈希索引： ? ? 可以建立自己的哈希索引 ? ? 空间索引 ? ? 全文索引 ? ?? 3.2 高性能索引策略 ? ? 聚集索引 ? ? 覆盖索引 ? ?? 4. 查询性能优化 [高性能mysql] 1.复制 mysql内建复制功能，配制一台或者多台主从服务，热备份hot spare，冷备份等等。 可以对整台服务器，特定数据库，特定表，进行复制。 mysql支持两种复制方案：基于语句复制; 基于行复制。前者是现在使用最多的复制方式。后者是5.1之后才有。两种方式都是记录主服务器的二进制日志，并在从服务器上重放进行复制，都是异步的。大型查询可能会让从服务器落后主服务器几秒几分几个消失。 mysql复制是向后兼容，新版本服务器意识老版本服务器的从服务器，但老版本服务器不能是新版本的从服务器。 复制不会大幅度增加主服务器的开销。 复制对扩展读有好处。要小心使用扩展写。复制在服务器多的时候也比较浪费，因为会复制不需要的数据。 2. 复制的通常用途： 数据分布; 负载平衡; 备份; 高可用性和故障转移; 测试mysql升级。 3. 复制如何工作： 3.1 主服务器把数据更改记录到二进制日志 3.2 从服务器把主服务器的二进制日志事件copy到自己的中继日志 3.3 从服务器重放终极日志中的时间，把更改应用到自己的数据上 ? ? ??