mysql每一步花费时间_[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 从服务器重放终极日志中的时间，把更改应用到自己的数据上