《高性能MySQL》

第一章 MySQL架构与历史
1.1 MySQL逻辑架构
1.2 开发控制
1.3 事务
1.4 多版本并发控制
1.5 MySQL的存储引擎
1.6 MySQL时间线（Timeline）
1.7 MySQL的开发模式
第二章 MySQL基准测试
2.1 为什么需要基准测试
2.2 基准测试的策略
2.3 基准测试方法
2.4 基准测试工具
2.5 基准测试案例
第三章 服务器性能剖析
3.1 性能优化简介
3.2 对应用程序进行性能剖析
3.3 剖析MySQL查询
3.4 诊断间歇性问题
3.5 其他剖析工具
第四章 Schema与数据类型优化
4.1 选择优化的数据类型entifier）
4.2 MySQL Schema设计中的陷阱
4.3 范式和反范式
4.4 缓存表和汇总表
4.5 加快ALTER TABLE操作的速度
第五章 创建高性能的索引
5.1 索引基础
5.2 索引的优点
5.3 高性能的索引策略
5.4 索引案例学校
5.5 维护索引和表
第六章 查询性能优化
6.1 为什么查询速度会慢
6.2 慢查询基础：优化数据访问
6.3 重构查询的方式
6.4 查询执行的基础
6.5 MySQL查询优化器的局限性
6.6 查询优化器的提示（hint)
6.7 优化特定类型的查询
6.8 案例学习
第七章 MySQL高级特性
7.1 分区表
7.2 视图
7.3 外键约束
7.4 在MySQL内部存储代码
7.5 游标
7.6 绑定变量
7.7 用户自定义函数
7.8 插件
7.9 字符集和校对
7.10 全文索引
7.11 分布式（XA）事务
7.12 查询缓存
第八章 优化服务器设置
8.1 MySQL配置的工作原理
8.2 什么不该做
8.3 创建MySQL配置文件
8.4 配置内存使用
8.5 配置MySQL的I/O行为
8.6 配置MySQL并发
8.7 基于工作负载的配置
8.8 完成基本配置
8.9 安全和稳定的设置
8.10 高级InnoDB设置
第九章 操作系统和硬件优化
9.1 什么限制了MySQL的性能
9.2 如何为MySQL选择CPU
9.3 平衡内存和磁盘资源
9.4 固体存储
9.5 为备库选择硬件
9.6 RAID性能优化
9.7 SAN和NAS
9.8 使用多磁盘卷
9.9 网络配置
9.10 选择操作系统
9.11 选择文件系统
9.12 选择磁盘队列调度策略
9.13 线程
9.14 内存交换区
9.15 操作系统状态
第十章 复制
10.1 复杂概述
10.2 配置复制
10.3 复制的原理
10.4 复制拓扑
10.5 复制和容量规划
10.6 复制管理和维护
10.7 复制的问题和解决方案
10.8 复制有多快
10.9 MySQL复制的高级特性
10.10 其他复制技术
第十一章 可扩展的MySQL
11.1 什么是可扩展性
11.2 扩展MySQL
11.3 负载均衡
第十二章 高可用性
12.1 什么是高可用性
12.2 导致宕机的原因
12.3 如何实现高可用性
12.4 避免单点失效
12.5 故障转移和故障恢复
第十三章 云端的MySQL
13.1 云的优点、缺点和相关误解
13.2 MySQL在云端的经济价值
13.3 云中的MySQL的可扩展性和高可用性
13.4 四种基础资源
13.5 MySQL在云主机上的性能
13.6 MySQL数据库即服务（DBaaS）
第十四章 应用层优化
14.1 常用问题
14.2 Web服务器问题
14.3 缓存
14.4 拓展MySQL
14.5 MySQL的替代品
第十五章 备份与恢复
15.1 为什么要备份
15.2 定义恢复需求
15.3 设计MySQL备份方案
15.4 管理和备份二进制日志
15.5 备份数据
15.6 从备份中恢复
15.7 备份和恢复工具
15.8 备份脚本化
第十六章 MySQL用户工具
16.1 接口工具
16.2 命令行工具集
16.3 SQL使用集
16.4 监控工具

第一章 MySQL架构与历史

本章概要描述MySQL服务器架构、各种存储引擎间的主要区别及区别的重要性
MySQL架构可在多种不同场景中应用，可嵌入到应用程序农，支持数据仓库、内容索引、部署软件、高可用冗余系统、在线事务处理系统等；
MySQL最重要的特性是他的存储引擎架构，使得查询处理及其他系统任务和数据存储、提取分离；

1.1 MySQL逻辑架构

连接管理与安全性；每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个CPU核心或者CPU中进行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

优化与执行：
mysql会解析查询，并创建内部数据结构，然后对其进行各种优化，包括重写查询、决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示hint优化器，影响它的决策过程。也可以请求优化器解释优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和schema、修改相关配置，使应用尽可能高效运行。第六章：优化器的细节。

优化器并不关心表使用的是什么存储引擎，但存储引擎队友优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如某些存储引擎的某种索引，可能对一些特定的查询有优化。

1.2 开发控制

读写锁：
共享锁（读锁S）：读锁是共享的，或者说是互不阻塞。多个客户可以同时读取同一数据，而互不干扰。
排他锁（写锁X）：写锁是排他的，一个写锁会阻塞（排斥）其他的写锁和读锁，以确保同一时间内只有一个用户才能执行写入。

锁粒度：
锁策略：在锁开销和数据安全性间寻求平衡，每个存储引擎可实现指定锁策略和粒度
表锁：table lock 最基本的 开销最小 锁定整表 ，一个用户在对表进行写操作前需要先获得写锁，这将会阻塞其他用户对该表的所有读写操作。
行级锁：row lock 最大程度支持并发 最大的锁开销 在存储引擎层（以自己的方式）实现

1.3 事务

独立工作单元，一组原子性SQL查询

事务的特性：
原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚。
一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态。
隔离性（isolation）通常，一个事务所做的修改在最终提交以前，对其他事务是不可见的。当然这里涉及到隔离级别的问题。
持久性（durability）一旦事务提交，则其所作的修改就会永久保存到数据库中。

并发问题：
丢失修改 A、B同时修改数据1，A修改了数据1后提交，B和A同时读到了数据1，在A修改后提交了修改导致A的修改丢失。 加X锁到事务结束
脏读 A修改数据1后提交事务，B读到了数据1，此时A又回滚事务。B读到了脏数据。 加X锁到事务结束、加S锁
不可重复读 A读了数据1、数据2由数据1和数据2得到数据3，B修改数据1的值，A再次验证数据3的值，发现不可重复读。 加X锁到事务结束、加S锁到事务结束
可串行化 A将表1其中所有数据1改成数据2，同时B往表1中插入数据1。A发现又出现了数据1。 串行执行

隔离级别：
四种，每种规定了事务中所作的修改，较低的隔离可以执行更高的并发、开销也更低
READ UNCOMMITTED未提交读，事务中的修改及时没有提交，对其他事务也是可见的；事务读取未提交的数据：脏读；很少使用
READ COMMITTED提交读，almost库默认隔离级别，非MySQL；事务从开始到结束只看见已提交的事务所作的修改，本身所做的修改对其他事务不可见；不可重复读：两次执行同样的查询，结果可能不一样（其他事务的修改）
REPEATABLE READ可重复读，MySQL默认，解决了脏读，同一事务多次读同样结果；幻读：当某个事务在读取某个范围内的记录时、另一个事务在该范围内插入新的记录，当前事务再次读取该范围记录、幻行
SERIALIZABLE:可串行化，最高，强制事务串行执行，避免幻读问题，读取每行数据时加锁（可导致大量超时和锁争用），很少使用

死锁
1、两个多个事务在同一个资源上相互占用并请求锁定对方占用的资源；
2、多个事务试图以不同的顺序锁定资源，可能产生死锁；
3、多个事务同时锁定同一个资源；
锁的行为和顺序和存取引擎相关，同样的顺序执行语句，一些存储引擎会产生死锁一些不会；
死锁产生的双重原因：因为真正的数据冲突(很难避免），因为存储引擎的实现方式导致；
死锁发送后，只有部分或完全回滚其中一个事务，才能打破死锁：InnoDB即回滚持有最少行级排他锁的事务；

MySQL中的事务:存储引擎实现
MySQL两种事务型存储引擎：InnoDB、NDB Cluster
自动提交AUTOCOMMIT； 默认采用自动提交模式，如果不显式开始一个事务，则每个查询都被当做一个事务执行提交操作，可通过AUTOCOMMIT变量来启用=1 =ON 、禁用=0 =OFF（all查询都在一个事务中直到显式commit rollback）事务结束同时开始新的事务，修改这个变量对非事务型表没有任何影响；
MySQL可以通过set transaction isolation level设置隔离级别，新的级别在下一个事务开始时生效，配置文件设置整个库的，也可只改变当前会话的隔离级别set session transaction isolation level read committed;
建议：不管何时都不要显示执行LOCK TABLES ，不管使用的是什么存储引擎

1.4 多版本并发控制

数据库MySQL、Oracle、postgresql等都实现了MVCC，各自实现机制不同【源】
MVCC:每个连接到数据库的读、在某个瞬间看到的是数据库的快照，写操作在提交之前对外不可见；【源】
更新时，将旧数据标记为过时且在别处增加新版本的数据（多个版本的数据，只有一个最新），容许读取之前的数据

特点：
1、每行数据都存在一个版本，每次数据更新时都更新该版本
2、修改时copy出当前版本、随意修改，各事务间不干扰
3、保存时比较版本号，成功commit则覆盖原纪录，失败则放弃rollback
4、只在REPEATABLE READ 和READ COMMITTED两个隔离级别下工作

1.5 MySQL的存储引擎

1.MyISAM
不支持事务，但是整个操作是原子性的
不支持外键，支持表锁
一个MyISAM表有三个文件：索引文件，表结构文件，数据文件
自动存储表的总行数，执行select count() from table时只要简单的读出保存好的行数即可
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
支持全文索引和空间索引
2.InnoDB
支持事务
支持行锁和外键约束，因此可以支持写并发
不存储总行数,执行select count() from table效率比MyISAM低
对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引.即是选定自动增长的健必定作为索引
一个Innodb表存储在一个文件内(共享表空间，表大小不受操作系统的限制)，也可能为多个(设置为独立表空间，表大小受操作系统限制，大小为2G)，受操作系统文件大小的限制
MyISAM引擎的表分成三个文件存储数据,但是InnoDB可以只存储在一个文件内,也可以存储在多个文件内

主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问主键索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。
总结:MySQL还有其它很多的存储引擎,然而那些都没啥用处.大部分情况下InnoDB都是正确选择,除非需要使用到它不具备的特性

mysql将每个数据库保存位数据目录下的一个子目录，创建表示，mysql在子目录下创建与表同名的.frm文件保存表的定义，不同存储引擎保存数据和索引的方式不同，但表的定义在MySQL服务层同一处理；

1.5.1InnoDB存储引擎
InnoDB:默认事务型引擎、最重要、广泛使用
处理大量短期事务；其性能和自动崩溃恢复特性、非事务型存储的需求中也很流行
数据存储在由InnoDB管理的表空间中，由一系列数据文件组成；
使用MVCC支持高并发，并实现了四个标准的隔离级别，默认是REPEATABLE READ可重复读，通过间隙锁next-key locking防止幻读，间隙锁使得InnoDB锁定查询设计的行还锁定索引中的间隙防止唤影行；

间隙锁：当使用范围条件并请求锁时，InnoDB给符合条件的已有数据记录的索引项加锁，对应键值在条件范围内但是不存在的记录（间隙）加锁，间隙锁:【源】
//如emp表中有101条记录，其empid的值分别是 1,2,…,100,101
Select * from emp where empid > 100 for update;
InnoDB对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁；
1、上面的例子，如果不使用间隙锁，如果其他事务插入大于100的记录，本事务再次执行则幻读，但是会造成锁等待，在并发插入比较多时、要尽量优化业务逻辑，使用相等条件来访问更新数据，避免使用范围条件；
2、 在使用相等条件请求给一个不存在的记录加锁时，也会使用间隙锁，当我们通过参数删除一条记录时，如果参数在数据库中不存在，库会扫描索引，发现不存在，delete语句获得一个间隙锁，库向左扫描扫到第一个比给定参数小的值，向右扫描到第一个比给定参数大的值，构建一个区间，锁住整个区间内数据；【源】

1.5.2MyIsSAM存储引擎
全文索引、压缩、空间函数，不支持事务和行级锁，崩溃后无法安全恢复
存储：
将表存储在两个文件中：数据.MYD、索引文件.MYI
表可以包含动态或静态（长度固定）行，MySQL据表定义来决定采用何种行格式
表如是变长行，默认配置只能处理256TB数据（指向记录的指针长度6字节），改变表指针长度，修改表的MAX_ROWS和AVG_ROW_LENGTH，两者相乘=表可到达的max大小，修改会导致重建整个表、表all索引；

特性：
1、对整张表加锁，读、共享锁，写、排他锁，但在读的同时可从表中插入新记录：并发插入
2、修复：可手工、自动执行检查和修复操作，CHECK TABLE mytable检查表错误，REPAIR TABLE mytable进行修复，执行修复可能会丢失些数据，如果服务器关闭，myisamchk命令行根据检查和修复操作；
3、索引特性：支持全文索引，基于分词创建的索引，支持复杂查询
4、延迟更新索引键Delayed Key Write,如果指定了DELAY_KEY_WRITE选项，每次修改完，不会立即将修改的索引数据写入磁盘，写入到内存的键缓冲区，清理此区或关闭表时将对应的索引块写入到磁盘，提升写性能，但是在库或主机崩溃时造成索引损坏、需要执行修复操作

压缩表：
表在创建并导入数据后，不再修改，比较适合，可使用myisampack对MyISAM表压缩（打包），压缩表不能修改（除非先解除压缩、修改数据、再次压缩）；减少磁盘空间占用、磁盘IO，提升查询性能，也支持只读索引；
现在的硬件能力，读取压缩表数据时解压的开销不大，减少IO带来的好处大得多，压缩时表记录独立压缩，读取单行时不需要解压整个表

性能：设计简单，紧密格式存储；典型的性能问题是表锁的问题，长期处于locked状态：找表锁

1.5.3内建的其他存储引擎
Archive：适合日志和数据采集类应用，针对高速插入和压缩优化，支持行级锁和专业缓存区，缓存写利用zlib压缩插入的行，select扫描全表；

Blackhole：复制架构和日志审核，其服务器记录blackhole表日志，可复制数据到备库 日志；

CSV：数据交换机制，将CSV文件作为MySQL表来处理，不支持索引；

Federated：访问其他MySQL服务器的代理，创建远程mysql的客户端连接将查询传输到远程服务器执行，提取发送需要的数据，默认禁用；

Memory：快速访问不会被修改的数据，数据保存在内存、不IO，表结构重启后还在但数据没了
1、查找 或 映射 表 ，2、缓存周期性聚合数据， 3、保存数据分析中产生的中间数据
支持hash索引，表级锁，查找快并发写入性能低，不支持BLOB/TEXT类型的列，每行长度固定，内存浪费

Merge：myisam变种，多个myisam合并的虚拟表

NDB集群引擎：

1.5.4第三方存储引擎
OLTP类:

XtraDB基于InnoDB改进，性能、可测量性、操作灵活

PBXT：ACID/MVCC，引擎级别的复制、外键约束，较复杂架构对固态存储SSD适当支持，较大值类型BLOB优化

TokuDB：大数据，高压缩比，大数据量创大量索引

RethinkDB:固态存储

面向列的

列单独存储，压缩效率高

Infobright：大数据量，数据分析、仓库应用设计的，高度压缩，按照块（一组元数据）排序；块结构准索引，不支持索引（量大索引也没用），如查询无法再存储层使用面向列的模式执行，则需要在服务器层转换成按行处理

社区存储引擎：***

1.5.5选择合适的引擎
除非需要用到某些InnoDB不具备的特性，且无办法可以替代，否则优先选择InnoDB引擎

不要混合使用多种存储引擎，如果需要不同的存储引擎：
1、事务：需要事务支出，InnoDB XtraDB；不需要 主要是select insert 那MyISAM
2、备份：定期关闭服务器来执行备份，该因素可忽略；在线热备份，InnoDB
3、崩溃恢复：数据量较大，MyISAM崩后损坏概率比InnoDB高很多、恢复速度慢
4、持有的特性：

1.5.6转换表的引擎
ALTER TABLE:最简单
ALTER TABLE mytable ENGINE=InnoDB
此会执行很长时间，MySQL按行将数据从原表复制到新表中，在复制期间可能会消耗掉系统all的I/O能力，同时原表上加读锁；会失去和原引擎相关的all特性

导出与导入：
mysqldump工具将数据导出到文件，修改文件中CREATE_TABLE语句的存储引擎选项，同时修改表名（同一个库不能存在相同的表名），mysqldump默认会自动在CREATE_TABLE语句前加上DROP TABLE语句

创建与查询：CREATE SELECT
综合上述两种方法：先建新存储引擎表，利用INSERT……SELECT语法导数

CREATE TABLE innodb_table LIKE myisam_table
ALTER TABLE innodb_table ENGINE=InnoDB;
INSERT INTO innodb_table SELECT * FROM myisam_table;
数据量大的话，分批处理（放事务中）

1.6 MySQL时间线（Timeline）

早期MySQL破坏性创新，有诸多限制，且很多功能只能说是二流的，但特性支持和较低的使用成本，使受欢迎；5.x早起引入视图、存储过程等，期望成为“企业级”数据库，但不算成功，5.5显著改善

1.7 MySQL的开发模式

遵循GPL开源协议，全部源代码开发给社区，部分插件收费；

第二章MySQL基准测试

前言：基准测试benchmark：基本技能，是针对系统设计的一种压力测试，是唯一方便有效、可学习系统在给定的工作负载下回发生什么 的方法，他可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或观察系统如何处理不同的数据，可在系统实际负载外创建虚拟场景进行测试（掌握系统行为）

正文：如前言，基准测试很、重、要！可以完成的工作： 总的来说：测试硬件、预估硬件、验证系统、测压力、调配置
1、验证基于系统的假设，确认假设是否符合实际情况；2、重现系统中某些异常行为，以解决；3、测试系统当前的运行情况，利用历史结果分析诊断无法预测的问题； 4、模拟更高的负载找出系统随压力增加而可能遇到的扩展性瓶颈；5、规划未来的业务增长，硬件、网络容量、相关资源；6、测试应用适应可变环境的能力；7、测试不同的硬件、软件和操作系统配置，证明设备是否配置正确；

对数据库的基准测试的作用，就是分析在当前的配置下（包括硬件配置、OS、数据库设置等），数据库的性能表现，从而找出MySQL的性能阈值，并根据实际系统的要求调整配置。【源】

与真实压力不同：真实的复杂多变；基准测试要求尽可能快执行完成，简单直接、结果易比较、成本低易行

2.2策略
针对系统整体：集成式full-stack

单独测试MySQL：单组件式single-component

推荐整体测试：要正确设置
1、用户关注的是整体的性能；2、MySQL并非总是瓶颈；3、更能揭示应用的真实表现

推荐单独测试：需要数据
1、需比较不同schema或查询的性能；2、针对某个具体问题的测试；3、避免漫长，做短期、快速周期循环

2.2.1指标
目标：细化为一系列问题，具体问题具体分析

吞吐量：单位时间内事务处理数，TPC-C、多用户交互式应用，每秒事务数，每分钟事务数

响应时间或延迟：测试任务所需的整体时间，平均响时、最小响时、最大和所占百分比；借助图表

并发性：测试应用在不同并发下的性能，关注正在工作中的并发操作、同时工作中的线程数 连接数；
web服务器并发性！=数据库的，仅表会话存储机制数据处理能力；测web并发 任意时间有多少并发；

可扩展性：给系统增加一倍工作，理性情况下能获得两部的结果；给系统增一倍资源可或2倍吞吐量
系统业务压力可能发生变化：测可扩展性非常必要；该指标对容量规范有用：提供信息来发现应用瓶颈

尽可能收集测试需求，基于需求设计测试，忌只关注部分指标，而忽略其他指标

2.3方法
要尽可能接近真实应用的情况：
使用全集、数据分布特点、真实分布参数、是否多用户、匹配用户行为、多类型、检查错误日志、系统预热：重启后多长时间才达到正常性能容量、持续一定时间；

2.3.1设计、规范
提出问题、目标明确
标准的基准测试：合适的方案 TPC-H OLTP
专用的测试：复杂、迭代，获易还原的生产数据集快照
计划： 参数、结果文档化、测试详细记录

2.3.2时间
基准测试应运行足够长的时间，无法确认时间可一直运行，持续观察知道确认系统已稳定
一个简单的测试规则：等系统看起来稳定的时间至少=系统预热的时间

2.3.3获取系统性能和状态
尽量多地收集被测系统的信息
best建目录、每执行一轮测试创建单独子目录，将结果、配置文件、测试指标、脚本和其他相关说明保存其中
需要记录是数据：
系统状态、性能指标：CPU使用率 、磁盘I/O、网络流量统计、SHOWGLOBAL STATUS计数器
合理的间隔，记录开始时间、利用时间戳、只是收集就好

2.3.4获取准确的结果
回答些问题：
是否选择了正确的基准测试？是否为问题收集了数据？预热时间是否足够长？
是否采用了错误的测试标准：IO密集型引用采用CPU密集型测试标准来评估性能？
测试结果是否可重复？重测前确保系统状态一致；对症测
影响因素：外部压力、性能分析、监控系统、详细日志、周期性作业
注意：过程中所需资源是专来测试的；测试中尽量少修改参数、通过迭代逐步修改基准测试的参数；认真研究过程中的异常情况并找到原因

2.3.5运行测试分析结果
自动化：减少人为失误，Makefile文件、脚本
测试结果满足目前需求，简单运行几轮测试，看看结果就OK了，如结果变化很大，可多运行几次、或更长时间
结果：分析，将数字变成知识，最终的目的是回答在设计时的问题
如何抽象有意义的结果，依赖于如何收集数据，写脚本分析数据、减少人为失误、工作量、可重复、文档化

2.3.6绘图重要性
一张图胜过千言万语嘛，本来有些知识点宝宝是想画导图的，但是么有画
书中有这么一个语句，分享一下：SHOW FULL PROCESSLIST SHOW PROCESSLIST显示哪些线程正在运行，您也可以使用mysqladmin processlist语句得到此信息，如果您有SUPER权限，您可以看到所有线程，否则，您只能看到您自己的线程，不使用FULL关键词，则只显示每个查询的前100个字符【源】

2.4基准测试工具
集成测试工具：整个应用
1、ab是Apache HTTP，每秒最多可处理多少请求【参考】【2】
2、http_load：ab类似更灵活，被设计为对web服务器测试，通过一个输入文件提供多个URL，随机选择进行测试，也可定制，使其按照时间比率进行测试【参考】
3、JMeter，java程序，可加载其他应用并测试其性能，这个听不错的，上面两个没有接触过，不评论了

单组件式：测MySQL，基于MySQL的系统性能
1、mysqlslap：mysql5.1后自带，模拟服务器的负载，输出计时信息，可执行并发连接数、指定sql语句，否则自动生成select语句【参考】
2、MySQL Benchmark Suite（sql-bench）：自带、5.7拿掉，基准测试套件，用于不同数据库服务器上进行比较测试，单线程串行执行，测执行查询的速度；包含了大量预定义测试，易使用，轻松比较不同引擎或配置的性能测试，CPU密集型的，结果会显示哪些类型的操作在服务器上执行更快，缺点：测试数据集小且无法用指定的数据，需要perl BDB支持；【参考】
3、Super Smack：MySQL、PostgreSQL,提供压力测试和负载均衡，复杂而强大的工具，可模拟多用户访问，加载测试数据到库、随机数据填充测试表【参考】
4、Database Test Suite：类似某些工业标准测试的工具集，免费TPC-C OLTP测试工具
5、sysbench：多线程系统压测，据影响数据库服务器性能的因素评估系统的性能，全能测试工具，支持MySQL、操作系统、硬件的硬件测试【参考】【2】
MySQL的BENCHMARK()函数：测试特定操作的执行速度，参数可以是需要执行的次数或表达式（任何标量表达式）

2.5案例
此处省略n字

2.6总结
建议至少要熟悉sysbench，如何使用oltp（比较不同系统性能） 和fileio 测试；经常执行基准测试，制定一些原则很必要，选择合适的测试工具、建立脚本库，收集信息分析结果，熟练一种绘图工具；

第三章服务器性能剖析

前言： 保持空杯精神，使用性能剖析，专注于测量服务器的时间花费在哪里，思考1、如何确认服务器是否达到了性能最佳状态，2、某条语句为什么不够快，诊断被用户描述为“停顿、堆积、卡死”的某些间歇性疑难故障；
接下来将介绍一些工具、技巧优化整机性能、优化单条语句执行速度，诊断 解决那些很难观察到的问题，展示如何测量系统并生成剖析报告、如何分析系统的堆栈；

3.1简介
性能：为完成某件任务所需要的时间度量，in other words 性能即响应时间
吞吐量：单位时间内的查询数据（性能定义的倒数）
第一步：弄清楚时间都去哪了，在哪消耗了时间
如果通测量没有找到答案，测量方式错了或不够完善，只测量需要优化的活动
不要在错误的时间启动或停止测试，测量的是聚合后的信息而不是目标活动本身；需要定位和优化子任务
原则：无法测量便无法有效地优化

3.1.1通过性能剖析进行优化
性能剖析：测量、分析时间花费在哪里的主要方法
1、测量任务所花费的时间；2、对结果统计、排序（重要前排）
可将相似任务分组汇总，通过性能剖析报告获需要的结果；报告会列出all任务，每行记录一个任务：
任务名、执行时间、消耗时间、平均执行时间，执行占全部时间的百分比；按照任务的消耗时间降序排序；
性能剖析类型：
基于执行时间的分析：什么任务的执行时间最长
基于等待的分析：判断任务在什么地方呗阻塞的时间最长

3.1.2理解性能剖析
性能剖析中缺失但是重要的信息：
1、值得优化的查询
占总响应时间比重很小的查询不值得优化；成本大于收益、停止优化
2、异常情况
没有显式要优化的也要优化，如执行次数少但每次都特别慢的任务
3、未知的未知
丢失时间：任务总时间与实际测量到的时间的差，即使没有发现也要注意这类问题存在的可能性
4、被掩藏的细节
无法显示all响应时间的分布，更多信息、直方图、百分比、标准差、偏差指数
5、无法再更高层次的堆栈中进行交互式 分析

3.2对应用程序进行性能剖析：自上而下
性能瓶颈的影响因素：
1、外部资源，调用外部web服务或搜索引擎
2、应用需要处理大量数据，分析一个超大的xml文件
3、循环中执行昂贵的操作：滥用正则
4、使用低效的算法：暴力搜索算法

建议：新的项目中应考虑包含性能剖析的代码

3.2.1测量PHP应用程序：空

3.3剖析MySQL查询
3.3.1剖析服务器负载
铺获MySQL查询到日志文件：
1、慢查询日志：开销低、精度高，大的磁盘空间，长期开启 注意部署日志轮转工具，只在收集负载样本期间开启即可，5.1后微秒级别；
2、通用日志，查询请求到服务器时进行记录，不包含响应时间和执行计划

分析查询日志
自顶向下，先生成剖析报告（pt-query-digest），查看特别关注的部分

3.3.2剖析单条查询
思考为什么花费这么长时间、如何去优化

使用SHOW PROFILE:MySQL5.1后
查看： show variables like “%pro%”;【源】
默认禁用，开启：set profiling=1；然后在服务器执行语句（关闭 set profiling=off;）
语法：
SHOW PROFILE [type [, type] … ]
[FOR QUERY n]
[LIMIT row_count [OFFSET offset]]

type:
ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function，Source_file，Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息

实质是这些开销信息被记录到information_schema.profiling表
show profiles；查看
show profile for query 2; 获取指定查询的开销（第二条查询开销明细）
show profile cpu for query 2 ;查看特定部分的开销，如下为CPU部分的开销
show profile block io,cpu for query 2; 同时查看不同资源开销

使用SHOW STATUS:计数器
全局show global status、基于某个连接会话级别，作用域要注意
计数器显示活动的频繁程度，常用：句柄计数器、临时文件、表计数器
会创建临时表，通过句柄操作（引用、指针？）访问此临时表，影响show status结果中对应的数字

使用慢查询日志：【源】【源】
将MySQL中响应时间超过阈值long_query_time的语句记录到慢查询日志中（日志可以写入文件或者数据库表，如果对性能要求高的话，建议写文件），默认是10s，需要手动开启

查看：
（1）slow_query_log的值为ON为开启慢查询日志，OFF则为关闭慢查询日志。
（2）slow_query_log_file 的值是记录的慢查询日志到文件中（注意：默认名为主机名.log，慢查询日志是否写入指定文件中，需要指定慢查询的输出日志格式为文件，相关命令为：show variables like ‘%log_output%’；去查看输出的格式）。
（3）long_query_time 指定了慢查询的阈值，即如果执行语句的时间超过该阈值则为慢查询语句，默认值为10秒。
（4）log_queries_not_using_indexes 如果值设置为ON，则会记录所有没有利用索引的查询（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON），一般在性能调优的时候会暂时开启，开启后使用full index scan的sql也会被记录到慢查询日志。

//上述命令只对当前生效，当MySQL重启失效，如果要永久生效，需要配置my.cnf
查看输出格式：文件？表show variables like ‘%log_output%’;
开启通用日志查询： set global general_log=on;
关闭通用日志查询： set globalgeneral_log=off;
设置通用日志输出为表方式： set globallog_output=’TABLE’;
设置通用日志输出为文件方式： set globallog_output=’FILE’;
设置通用日志输出为表和文件方式：set global log_output=’FILE,TABLE’;
查询慢查询语句的个数：show global status like ‘%slow%’;

日志部分内容简介：
哪条语句导致慢查询（sql_text），该慢查询语句的查询时间（query_time），锁表时间（Lock_time），以及扫描过的行数（rows_examined）等信息。

利用自带的慢查询日志分析工具：mysqldumpslow
perl mysqldumpslow –s c –t 10 slow-query.log

-s 表示按何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；-t 表示top的意思，后面跟着的数据表示返回前面多少条；-g 后面可以写正则表达式匹配，大小写不敏感。

使用Performance Schema:【源】【源】
监视MySQL服务器，收集性能参数，且表的存储引擎PERFORMANCE_SCHEMA，低耗能

本地服务器，表是内存表，表内容在服务器启动时重新填充，关闭时丢弃，更改不会被复制或写入二进制日志

特性：
性能方案配置可被动态的执行SQL修改，立即影响到数据收集
监控服务事件：事件是服务做并被感知到的任何事，时间信息可被收集
数据库性能方案，提供对运行时数据库服务进行内部检查的方式，关注性能数据
特定于一个数据库服务，数据库表关联到数据服务，修改不会被备份也不写进二进制日志
存储引擎用“感知点”收集事件数据，且存储在performance_schema数据库，可通过select语句进行查询

补充：数据库初始安装有三个基本库
mysql
包含权限配置，事件，存储引擎状态，主从信息，日志，时区信息，用户权限配置等
information_schema
对数据库元数据的抽象分析，由此提供了SQL语句方式来查询数据库运行时状态，每次对information_schema的查询都产生对metadata的互斥访问，影响其他数据库的访问性能。
performance_schema
内存型数据库，使用performance_schema 存储引擎，通过事件机制将mysql服务的运行时状态采集并存储在performace_schema数据库。注意，两个单词之间用下划线连接时，表示performance_schema是一个数据库；用空格分开时，表示一个数据库性能方案，也表示一个存储引擎。

3.3.3使用性能剖析：有限

3.4诊断简歇性问题
如系统偶尔停顿、慢查询、唤影问题，尽量不要使用试错的方式解决问题：风险大

3.4.1单条查询问题还是服务问题
使用SHOW GLOBAL STATUS
较高频率：1s/次执行该命令铺获数据，问题出现通过计数器的

使用SHOW PROCESSLIST 【参考】显示哪些线程正在运行

使用查询日志
开启慢查询，设置全局的long_query_time=0,确认all连接采用了新设置（可能需要重置all连接使生效）
注意吞吐量突然下降时间段的日志，查询是在完成阶段才写入到慢查询日志的

好的工具事半功倍：tcpdump、pt-query-digest、Percona Server

理解发现的问题
可视化数据：gnuplot /R（绘图工具）

gnuplot：
安装 一些命令： 常用技巧 入门教程 2 Gnuplot 数据可视化

建议：先使用前两种方法，开销低且通简单shell脚本或反复执行的查询交互式收集数据

3.4.2铺获诊断数据
现间歇性问题，尽量多收集数据（不只是问题出现时的）

弄清楚：1、有区分 何时出现了问题 的方法：触发器；2、收集诊断数据的工具

诊断触发器
误差：在没有发生问题期间收集了很多诊断数据，浪费时间（这个和前的、仔细读一下 不矛盾）
漏检：在问题出现时没有铺获到数据，错失了机会，开始收集前确认触发器能够真正地识别问题

好的触发器：
找到些能和正常时的阈值进行比较的指标
选择一个合适的阈值：足够高（正常时不会触发）、不能太高（问题发生时不错过）
推荐工具pt-stalk【参考】【2】触发器，设定到某个条件记录 配置需监控的变量 阈值 检查的频率

收集什么样的数据
执行时间：工作的时间和等待的时间
在需要的时间段内收集all能收集的数据
未知问题发生的原因：1、服务器需做大量工作、导致大量消耗CPU；2、在等待资源释放
不同的方法收集诊断数据，确认原因：
1、剖析报告：确认是否有太多工作，工具：tcpdump 监听TCP流量 模式开闭慢查询日志
2、等待分析：确认是否存在大量等待，GDB堆栈跟踪信息、show processlist ，show innodb status观察线程、事务状态

解释结果数据
目的：1、问题是否真的发生了；2、是否有明显的跳跃性变化

工具：
oprofile利用cpu硬件层面提供的性能计数器(performance counter)，通过计数采样，帮助我们从进程、函数、代码层面找出占用cpu的"罪魁祸首"。实例【参考】
opreport命令，分别从进程和函数层面查看cpu使用情况的方法

samples | %|

 镜像内发生的采样次数     采样次数所占总采样次数的百分比      镜像名称
opannotate命令可显示代码层面占用cpu的统计信息

GDB:Linux应用程序开发中，最常用的调试器是gdb(调试的对象是可执行文件)，它可以在程序中设置断点、查看变量值、一步一步跟踪程序的执行过程(数据、源码）、查看内存、堆栈信息。利用调试器的这些功能可以方便地找出程序中存在的非语法错误。【参考】【参考】 语法和实例

3.4.3一个诊断案例
间歇性性能问题，具备MySQL、innodb、GNU/Linux相关知识

明确：1、问题是什么，清晰描述；2、为解决问题已做过什么操作？

开始：1、了解服务器的行为；2、梳理服务器的状态 参数配置 软硬件环境（pt-summary pt-mysql-summary）

不要被离题太多的各种情况分散了注意力，问题写在纸条上，检查一个划掉一个

是原因还是结果？？？

资源变得效率低下可能的原因：

1、资源过度使用，余额不足；2、资源未被正确匹配；3、资源损坏或失灵

3.5其他剖析工具
USER_STATISTICS:一些表对数据库活动进行测量、审计

strace：调查系统调用情况，使用实际时间、不可预期性、开销的，oprofile使用花费CPU周期

小结：
定义性能最有效的方法是响应时间
无法测量便无法有效优化，性能优化工作需要基于高质量、全方位及完整的响应时间测量
测量的最佳开始点是应用程序，即使问题出在底层的数据库，借助良好的测量较容易发现问题
大多数系统无法完整地测量，测量有时候也会有错误的结果，想办法绕过些限制，要能意识到方法的缺陷和不确定性在哪
完整的测量会产生大量需要分析的数据，so需要用到剖析器（最佳工具）
剖析报告：汇总信息，掩盖和丢弃了很多细节，不会告诉你缺了什么，不能完全依赖
两种消耗时间的操作：工作或等待，almost剖析器只能测量因工作而消耗的时间，so等待分享有时候是很有用的补充，特别是cpu利用率低但工作一直无法完成的情况
优化和提升两回事，当继续提升的成本超过收益时，应停止优化
注意你的直接，思路，决策尽量基于数据
in a words:首先澄清问题、选择合适技术、善用工具、足够细心、逻辑清晰且坚持下去，不要把原因和结果搞混，在确定问题前不要随便针对系统做变动

补充：MySQL只会写数据、日志、排序文件和临时表到磁盘

SHOW STATUS SHOW PROCESSLIST

第四章 Schema与数据类型优化

1.选择最优的数据类型
1）更小的通常更好
一般情况下，应该尽量使用可以存储数据的最小数据类型。更小的数据类型通常更快，因为它们占用更小的磁盘空间、内存和cpu缓存，并且处理时需要的cpu周期也更少。
2）简单就好
简单数据类型的操作通常需要更少的cpu周期。例如，整型比字符操作代价更低，因为字符集和校对规则（排序规则）使字符比较比整型比较更复杂。
3）尽量避免NULL
包含可为NULL的列，对mysql来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在mysql里也需要特殊处理。
通常把可为NULL改为NOT NULL带来的性能提升比较小，所以调优时没必要首先从这入手，除非你确定这会导致问题。但是，如果计划在列上建索引，就应该尽量避免设置成可为NULL的列。

2.具体数据类型选择
1）整数类型
整数类型有这几种：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，分别使用8，16，24，32，64位存储空间。整数类型有可选的UNSIGNED属性，标识不允许为负值。它们使用的是相同的存储空间，并且具有相同的性能。因此可以根据实际情况选择合适的类型。

mysql可以为整数类型指定宽度，但这对大多数应用是没有意义的。因为它不限制值的合法范围，只是规定了mysql的一些交互工具用来显示字符的个数。对于存储和计算来说，INT(1)和INT(20)是相同的。

2）实数类型（带有小数部分的额数字）
实数类型有：FLOAT，DOUBLE，DECIMAL。FLOAT和DOUBLE在存储同样范围的值时，通常比DECIMAL使用更少的存储空间。所以应该尽量只在对小数需要精确计算时才使用DECIMAL。但在数据量比较大时，可以考虑使用BIGINT代替，将需要存储的单位根据小数乘以相应的倍数即可。

3）字符串类型
字符串类型有：CHAR，VARCHAR，BINARY，VARBINARY，BLOB家族和TEXT家族。
VARCHAR存储的是变长字符串（在表没有设置ROW_FORMAT=FIXED情况下）。它比定长字符串更节省存储空间，因为它只使用必要的空间。VARCHAR使用1或2个而外字节记录字符串的长度，如果列的最大长度小于等于255字节，使用1个字节记录长度，大于则使用2个字节。例如，假设采用latin1字符集，一个VARCHAR(10)的列需要11个字节的存储空间，VARCHAR(1000)需要1002字节的存储空间。VARCHAR节省了存储空间，对性能也有帮助，但由于是变长的，UPDATE时如果字符串比原来的字符串更长，就会需要额外的工作。例如，如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，MYISAM会将行拆分成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。所以以下情况适合使用VARCHAR：字符串的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符采用不同的字节数进行存储。

CHAR存储的定长的字符串，当存储CHAR值时，MYSQl会删除所有末尾的额空格。CHAR适合存储很短的字符串或者所有值都接近同一个长度，例如，密码的MD5值就很适合用CHAR存储，还有非常短的字符串CHAR的所用存储空间也会比VARCHAR小。

思考：使用VARCHAR(5)和VARCHAR(200)存储“hello”所用的存储空间是一样大，那么使用短的列有什么优势？其实还是有优势：更长的列会消耗更多的内存，因为mysql通常会分配固定大小的内存块来保存内部值，尤其是使用临时表进行排序或者操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。所以最好的策略还是只分配需要的空间。

BINARY和VARBINARY与CHAR和VARBINARY类似，但它们存储的是二进制字符串。它们适合的场景：字符串比较大小写敏感；mysql比较BINARY字符串的时候，每次按一个字节，并且根据该字节的数值进行比较，因此二进制比较比字符串比较简单很多，所以也更快。

BLOB和TEXT都是为存储很大的字符串而设计，分别采用二进制和字符串的形式进行存储。除非必须，否则尽量不用这两种类型。

mysql还有日期时间类型（DATETIME和TIMESTAMP）、枚举类型和位数据类型。日期时间类型通常可选择性比较少，根据需求选择自己合适的就好。置于枚举类型和位数据类型在实际应用中应用的比较少，这里不记，有兴趣可以去了解。

3.标识符（identifier）的选择
整数类型通常是标识符的最好选择，因为它们很快而且可以使用AUTO_INCREMENT。应该避免使用字符串作为标识符，因为它很消耗空间并且通常比数字慢。对于随机的字符串应该更加注意，例如MD5()，UUID()产生的字符串。这些函数生成的新值会分布在很大的空间内，这会导致SELECT和INSERT变得很慢。

一.数据类型的优化
1.数据类型的选择
存储数据的类型磁盘占用越小越好
避免使用NULL,通常情况下选择为NOT NULL,因为NULL的列会使用更多的存储空间
CHAR和VARCHAR,优先选择CHAR,VARCHAR需要使用1或者2个额外字节记录字符串长度是可变字符串,CHAR是定长的.所以使用CHAR性能更佳
时间范围在1970年~2038年之间,选用TIMESTAMP,除此之外选择DATETIME
IPV4使用int UNSIGNED存储,不要使用传统的VARCHAR(15),int只占4个字节,VARCHAR占用了15个字节.常见于登录日志.当日志表数据量一大,那就将是一个巨大的区别

2.范式和反范式
范式:数据库规范的手段,避免冗余数据的存放
第一范式:数据库每一列只能存放单一值
第二范式:所有数据都要和该数据表的主键有完全相依的关系
第三范式:要求非键属性之间应该是没有关系的
优点：使编程相对简单，数据量更小，更适合放入内存，更新更快，
缺点：查询更复杂

反范式：
试图增加冗余数据或分组数据来优化数据库读取性能的过程，减少了表之间的连接
但如果冗余数据量过大的时候，可能会碰到I/O瓶颈，导致性能变得更差，所以需要
衡量各个表的更新量和查询量
在数据统计分析，数据仓库等领域使用的比较多

3.缓存表,汇总表,计数器表
缓存表:临时数据的存放,例如是否登录过期的token校验
汇总表:对于一些查询很慢的数据,通过汇总记录到汇总表当中
计数器表:对于用户朋友数,访问量,下载量等信息可以作为一个单独表存储,可以避免查询缓存失效

4.附录:
4.1 避免使用MySQL已经遗弃的特性,例如浮点数的精度,或者整数的显示宽度
4.2 尽量使用整型定义标识列

前言：高性能的基石：良好的逻辑、物理设计，根据系统要执行的查询语句设计schema
本章关注MySQL数据库设计，介绍mysql数据库设计与其他关系型数据库管理系统的区别
schema：【源】
schema就是数据库对象的集合，这个集合包含了各种对象如：表、视图、存储过程、索引等。为了区分不同的集合，就需要给不同的集合起不同的名字，默认情况下一个用户对应一个集合，用户的schema名等于用户名，并作为该用户缺省schema。所以schema集合看上去像用户名。
如果把database看作是一个仓库，仓库很多房间（schema），一个schema代表一个房间，table可以看作是每个房间中的储物柜，user是每个schema的主人，有操作数据库中每个房间的权利，就是说每个数据库映射的user有每个schema（房间）的钥匙。 SQL server和Oracle mysql有别

4.1选择优化的数据类型
原则：
1、更小的通过更好，尽量使用可正确存储数据的最小的数据类型（占更少的磁盘 内存 CPU缓存，处理时需要CPU周期更少：更快），但能罩得住数据，存不下就尴尬了
2、简单就好：简单类型（更少CPU周期），使用MySQL内建类型存时间，整型存ip，整型较字符代价低（字符集和校对排序规则使字符较复杂）
3、尽量避免null：最好指定为not null
*）null列使用更多的存储空间，mysql里需要特殊处理
*）null使索引、索引统计和值比较更复杂；可为null的列被索引时，每个索引记录需额外的字节
例外：InnoDB使用单独位bit存储null,so对于稀疏数据（很多值为null）有很好的空间效率，不适合MyISAM

4.1.1整数类型【参考】
整数whole number
tinyint(8位存储空间) smallint(16) mediumint(24) int(32) bigint(64)
1、存储值的范围：，N是存储空间的位数
2、unsigned：可选、不容许负值，可使正数的上限提高一倍：tinyint unsigned 0_{255，tinyint-128}127
3、有无符号使用相同的存储空间，相同的性能
可为整型指定宽度，例如INT(11)，对于大多数应用无意义，不会限制值的合法范围，只是规定了交互工具显示字符的个数，对于存储和计算，int（1）和int（20）是相同的；

实数real number：带小数
float和double，mysql使用duble作为内部浮点计算的类型
decimal：存储精确的小数，mysql服务器自身实现，decimal(18,9)18位，9位小数，9个字节（前4后4点1）
尽量只在对小数进行精确计算时才使用（额外的空间和计算开销），如财务数据
数据量大时，考虑使用bigint代替，将需要存储的货币单位据小数的位数乘以相应的倍数
浮点：
建议：只指定类型、不定精度（mysql），这些精度非标准，mysql会悄选类型、或存时对值取舍
存储同样范围的值时，比decimal更少的空间，float4字节存 double8字节（更高精度范围）

4.1.3字符串类型
varchar和char：
前提：innodb和myisam引擎，最主要的字符串类型
磁盘存储：存储引擎存储的方式与在内存、磁盘上的不能不一样，所以mysql服务器从引擎取值需转格式
varchar：
1、存储可变字符串，比定长节省空间（仅使用必要的空间），但如果表使用row_format=fixed，行会定长存储
2、需使用1/2额外字节记录字符串长度；1）列max长度<=255字节，1字节表示，否2字节，2）采用latinl字符集，varchar（10）列需11个字节的存储空间，varchar（1000）1002字节，2字节存储长度信息
3、节省存储空间，利于性能；但在update可能使行变得比原来更长、需做额外工作
合适的情况：
1）字符串列最大长度比平均长度大很多；2）列的更新少（不担心碎片）；3）使用UTF-8字符串，每个字符均使用不同的字节数存储

char：
1、定长，据长度分配空间，删除all末尾空格；长度不够、空格填充
2、存储空间上更有效率，char(1)来存储只有Y N的值 1个字节 ，varchar2字节，还有一个记录长度
适合的情况：
1）适合存储很短的字符串；2）或all值接近同一个长度；3）经常变更的数据，存储不易碎片
对应空格、存储：
char类型存储时末尾空格被删；数据如何存储取决于存储引擎，Memory引擎只支持定长的行（最大长度分配空间）

binary，varbinary：存储二进制字符串，字节码，长度不够、\0来凑（不是空格）检索时不会去

慷慨不是明智的：varchar（5）和varchar（100）存储‘hell’空间开销一样，长的列消耗更多内存

blob和text：大数据
分别用二进制和字符方式存储，分别属于两组不同的数据类型：字符类型：tinytext、smalltext、text、mediumtext、longtext，对应的二进制类型是tinyblob、smallblob、blob、mediumblob、longblob，两类仅有的不同：blob类型存储的是二进制，无排序规则或字符集，text有字符串 排序规则；
MySQL会把每个blob和text当做独立的对象处理，存储引擎存储时会做特殊处理，当值太大，innoDB使用专门的外部存储区域进行存储，此时每个值在行内需要1~4个字节存储一个指针，然后在外部存储实际的值；

mysql对他们的列排序：只对每列前max_sort_length字节排序；且不能将列全部长度的字符串进行索引，也不能使用这些索引消除排序；
如果explain执行计划的extra包含using temporary：这个查询使用了隐式临时表

使用enum代替字符串类型
定义时指定取值范围，对1～255个成员的枚举需要1个字节存储；对于256～65535个成员，需要2个字节存储。最多可以有65535个成员，ENUM类型只能从成员中选择一个；和set相似
可把不重复的固定的字符串存储成一个预定义的集合，mysql在存储枚举时会据列表值的数量压缩到1/2字节中，在内部会将每个值在列表中的位置保存为整数（从1开始，必须进行查找才能转换为字符串，开销、列表小 可控），且在表的.frm文件中保持“数字-字符串”映射关系的“查找表”；
将一个数字存储到一个 ENUM 中，数字被当作为一个索引值，并且存储的值是该索引值所对应的枚举成员： 在一个 ENUM字符串中存储数字是不明智的，因为它可能会打乱思维；ENUM 值依照列规格说明中的列表顺序进行排序。(ENUM 值依照它们的索引号排序。)举例来说，对于 ENUM(“a”, “b”) “a” 排在 “b” 后，但是对于 ENUM(“b”, “a”)， “b” 却排在 “a” 之前。空字符串排在非空字符串前，NULL 值排在其它所有的枚举值前。为了防止意想不到的结果，建议依照字母的顺序定义 ENUM列表。也可以通过使用GROUP BY CONCAT(col) 来确定该以字母顺序排序而不是以索引值。【源】
排序时安装创建表时的顺序排序的（应该是）；枚举最不好的地方：字符串列表是固定的，添加删除字符串须使用alter table；在‘查找表’时采用整数主键避免基于字符串的值进行关联；

4.1.4日期和时间
datetime：大范围的值 1001 9999 s YYYYMMDDHHMMSS 与时区无关 8字节
默认，以可排序、无歧义的格式显示datetime：2008-01-02 22:33:44

timestamp：1970 2038，1970 1 1以来的秒数，时区 4字节
from_unixtime将unix时间戳转日期，unix_timestamp将日期转unix时间戳

插入时没有指定第一个timestamp列的值，设置为当前时间，插入记录时，默认更新第一个timestamp列的值，timestamp类为not null，尽量使用timestamp（空间效率高）；

可以使用bigint类型存储微妙级别的时间戳，或double存秒之后的小数部分，或使用MariaDB代替MySQL；

4.1.5 位
bit：mysql5.0
前与tinyint同义词，新特性
bit（1）单个位的字段，bit（2）2个位，最大长度64个位
行为因存储引擎而异，MyISAM打包存储all的BIT列（17个单独的bit列只需要17个位存储，myisam3字节ok），其他引擎Memory和innoDB为每bit列使用足够存储的最小整数类型来存放，不节省存储空间；
mysql把bit当做字符串类型，检索bit（1）值、结果是包含二进制0/1的字符串，数字上下文的场景检索，将字符串转成数字，大部分应用，best避免使用；

set
创建表时，就指定SET类型的取值范围 ：属性名 SET(‘值1’,‘值2’,‘值3’…,‘值n’)，“值n”参数表示列表中的第n个值，这些值末尾的空格将会被系统直接删除，字段元素顺序 系统自动按照定义时的顺序显示 重复 只存一次。
其基本形式与ENUM类型一样。SET类型的值可以取列表中的一个元素或者多个元素的组合。取多个元素时，不同元素之间用逗号隔开。SET类型的值最多只能是有64个元素构成的组合，根据成员的不同，存储上也有所不同：【参考，同enum】
1～8成员的集合，占1个字节。
9～16成员的集合，占2个字节。
17～24成员的集合，占3个字节。
25～32成员的集合，占4个字节。
33～64成员的集合，占8个字节。
需要保持很多true、false值，可考虑合并这些列到set类型，在mysql内部以一系列打包的位的集合来表示的（有效利用存储空间）且mysql有find_in_set、field函数，方便在查询中使用；
缺点：改变列的定义代价高，需要alter table，无法再set上通索引查找
在整数列按位操作：
代替set的方式：使用整数包装一系列的位：可把8个位包装到tinyint中，且按位操作来使用，为位定义名称常量来简化这个工作，但是这样查询语句较难写且难理解

4.1.6选择标识符identifier
标识列：自增长列【源】
1）可不用手动插入值，系统提供默认序列值;2)不要求和主键搭配 ; 3)要求是unique key；
4）一个表最多一个；5）类型只能是数值；5）可通过set auto_increment_increment=3;

选择标识列类型时
考虑存储类型、mysql对这种类型怎么执行计算和比较，确定后确保在all关联表中使用same类型，类型间要精确匹配；

技巧：
1、整数类型：整数通常最好的选择，很快且可使用auto_increment
2、enum和set类型，存储固定信息
3、字符串：避免，耗空间较数字慢，myisam表特别小心（默认对字符串压缩使用、查询慢）
1）完全“随机”字符串MD5/SHA1/UUID函数生成的新值 会任意分布在很大的空间内，导致insert及部分的select变慢：插入值随机的写到索引的不同位置，insert变慢(页分裂 磁盘随机访问 聚簇索引碎片）；select变慢、逻辑上相邻的行分布在磁盘和内存不同的地方；随机值导致缓存对all类型的查询语句效果都变差（使缓存赖以工作的访问局部性原理失效）

聚簇索引，实际存储的循序结构与数据存储的物理结构一致，通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认都是主键，设置了主键，系统默认就为你加上了聚簇索引；【源】

非聚簇索引记录的物理顺序与逻辑顺序没有必然的联系，与数据的存储物理结构没有关系；一个表对应的非聚簇索引可以有多条，根据不同列的约束可以建立不同要求的非聚簇索引；
2）存储uuid，移除-符号，或者用unhex转换uuid值为16字节的数字，且存储在binary（16）列中，检索时通过hex函数格式化为16进制格式;
UUID生成的值与加密散列函数(sha1)生成的值不同特征：uuid分布不均匀，有一定顺序，不如递增整数

当心自动生成的schema：
严重性能问题，很大的varchar、关联列不同的类型；

orm会存储任意类型的数据到任意类型的后端数据存储中，并没有设计使用更优的类型存储，有时为每个对象每个属性使用单独行，设置使用基于时间戳的版本控制，导致单个属性会有多个版本存在；权衡

4.1.7特殊类型数据：空

4.2MySQL schema设计中的陷阱
因为mysql实现机制导致了一些特定错误，如何避免，慢慢道来：
1、太多的列
MySQL存储引擎api工作时需要在服务器层和存储引擎层通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，从行缓冲中将编码过的列转换成行数据的操作代价高，myisam定长行与服务器行结构正好匹配，不需要转换；但是变长行结构 InnoDB的行结构总是需要转换，转换代价依赖于列的数量。
2、太多的关联
实体-属性-值EAV：糟糕的设计模式，mysql限制了每个关联操作最多只能有61张表，但EAV数据库需许多自关联；一个粗略的经验法则，如果希望查询执行得快速且并发性好，单个查询最好在12个表内做关联；
3、防止过度使用枚举
注意防止过度使用枚举；使用外键关联到字典表或查找表查找具体的值，在mysql中，需要在枚举列表中添加值时，要做一次alter table；MySQL5.0更早alter table阻塞操作，5.1更新版本中，不是在列表末尾增加值也会一样需要alter table
4、非此发明not invent here的null
建议存空值可以用0、特殊值、空字符串代替，尽量不要null；但是不要走极端，在某些场景下、使用null会更好：
create table ……（
//全0 （不可能的日期）会导致很多问题
dt datetime not null default ‘0000-00-00 00:00:00’
……
）
MySQL会在索引中存储null值，Oracle不会

4.3范式与反范式
4.3.1优缺点
1、范式化的更新操作更快
2、当数据较好地范式化时，很少有重复数据，只需要修改更少的数据
3、范式化的表更小，可更好地放到内存里，执行操作更快
4、很少冗余数据，检索列表数据时更少需要distinct、group by语句

缺点：
需要关联，有代价且可能使索引无效

4.3.2反范式的优点和缺点
避免关联，数据比内存大可能比关联要快很多（避免了随机I/O）

4.4缓存表和汇总表
缓存表：对优化搜索和检索查询语句很有效，
存储那些可以较简单地从其他表获取数据（每次获取速度比较慢）的表

汇总表：保存使用group by语句聚合数据的表
使用时决定是实时维护数据还是定期重建，定期重建：节省资源、碎片少、顺序组织的索引（高效）

重建时，保证数据在操作时依然可用，通过“影子表”来实现，影子表：一张在真实表背后创建的表，在完成建表操作后，可通过原子的重命名操作切换影子表和原表

4.4.1物化视图
预先计算并存在磁盘上的表，可通过各种策略刷新和更新，mysql不原生支持，可使用Justin Swanhart工具flexviews实现：

flexviews组成：
变更数据抓取，读取服务器二进制日志且解析相关行的变更
一系列可以帮助 创建和管理 视图 的定义 的 存储过程
一些可应用变更到 数据库中的物化视图 的工具
flexviews通过提取对源表的更改，可增量地重新计算物化视图的内容：不需要查询原始数据（高效）

4.4.2计数器表
计数器表：缓存一个用户朋友数、文件下载次数等，推荐创建一张独立的表存储计数器，避免查询缓存失效；

更新加事务，只能串行执行，为了更高的并发性，可将计数器保存在多行，每次随机选一行更新，要统计结果时，聚合查询；（这个我读了两三边，可能比较笨吧，就是同一个计数器保存多分，每次选其中一个更新，最后求和，好像还不是很好理解哈，多读几遍吧）

4.5加快alter table 操作的速度
mysql大部分修改表结构是：用新的结果创建空表、从旧表中查出all数据插入新表，删除旧表

mysql5.1及更新包含一些类型的“在线”操作的支持，整个过程不需要全锁表，最新版的InnoDB(MySQL5.5和更新版本中唯一的InnoDB)支持通过排序来建索引，建索引更快且紧凑的布局；

一般而言，大部分alter table导致mysql服务中断，对常见场景，使用的技巧：
1、先在一台不提供服务的机器上执行alter table操作，然后和提取服务的主库进行切换
2、影子拷贝，用要求的表结构创建张和源表无关的新表，通过重命名、删表交换两张表（上有）
不是all的alter table都引起表重建，理论上可跳过创建表的步骤：列默认值实际上存在表的.frm文件中，so可直接修改这个文件不需要改动表本身，但mysql还没有采用这种优化方法，all的modify column将导致表重建；
alter column：通frm文件改变列默认值：alter table容许使用alter column、modify column change column修改列，三种操作不一样;
alter table sakila.film alter column rental_duration set default 5;

4.5.1只修改frm文件
mysql有时在没有必要的时候也重建表，如果愿冒一些风险，可做些其他类型的修改而不用重建表：下面操作可能不能正常工作，先备份数据

下面操作不需要重建表：
1、移除一个列的auto_increment
2、增加、移除、更改enum和set常量，如果移除的是被用到的常量、查询返回空字符串

基本技术为想要的表结果创建新的frm文件，然后用它替换掉已经存在的那张表的frm文件：
1、创建一张有相同结构的空表，进行所需的修改
2、执行flush tables with read lock：关闭all正在使用的表且禁止任何表被打开
3、交换frm文件
4、执行unlock tables释放第2步的读锁

示例略

4.5.2快速创建myISAM索引
1、为高效地载入数据到MyISAM表，常用技巧：先禁用索引、载入数据、重启索引：因为构建索引的工作延迟到数据载入后，此时可通过排序构建索引，快且使得索引树的碎片更少、更紧凑
但是对唯一索引无效（disable keys），myisam会在内存中构造唯一索引且为载入的每一行检查唯一性，一旦索引大小超过有效内存、载入操作会越来越慢；
2、在现代版InnoDB中，有个类似技巧：先删除all非唯一索引，然后增加新的列，最后重建删除掉的索引（依赖于innodb快速在线索引创建功能）Percona server可自动完成这些操作；
3、像前alter table 的骇客方法来加速这个操作，但需多做些工作且承担风险，这对从备份中载入数据很有用，如already know all data is effective ，and no need to do the unique check
用需要的表结构创建一张表，不包括索引（如用load data file 且载入的表是空的，myisam可排序建索引）
载入数据到表中以构建MYD文件
按需要的结构创建另外一张空表，这次要包含索引，会创建.frm .MYI文件
获读锁并刷新表
重命名第二张表的frm文件 MYI，让mysql认为这是第一张表的文件
释放读锁
使用repair table来重建表的索引，该操作会通过排序来构建all索引、包括唯一索引

4.6总结
良好的schema设计原则是普通使用的，但mysql有自己的实现细节要注意，概括来说：尽可能保持任何东西小而简单总是好的；mysql喜欢简单
最好避免使用bit
使用小而简单的合适类型；
尽量使用整型定义标识列
避免过度设计，比如会导致极复杂查询的schema设计，或很多列；
应该尽可能避免使用null值，除非真实数据模型中有确切需要
尽量使用相同的类型存储相似、相关的值，特别是关联条件中使用的列
注意可变长字符串，其在临时表和排序时可能导致悲观的按max长度分配内存
避免使用遗弃的特性，如指定浮点数的精度，或整数的显示宽度
小心使用enum和set，虽然他们用起来很方便，但不要滥用，有时会变陷阱
范式是好的，但反范式有时也是必要的；预先计算、缓存或生成汇总表也可获很大好处
alter table 大部分情况会锁表且重建整张表（让人痛苦）本章提供了一些有风险的方法，大部分场景必须使用其他更常规的方法

第五章 创建高性能的索引

一、索引基础
存储引擎使用索引：1、在索引中找到对应值，2、据匹配的索引记录找到对应数据行。

索引是存储引擎用于快速查找记录的一种数据结构，所以索引本质就是数据结构，根据索引类型的不同，所对应的数据结构也不尽相同，索引类型对应的数据结构将在第二节介绍。索引可以包含一个或者多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为mysql只能高效的使用索引的最左前缀。创建一个包含两个列的索引和两个只包含一个列的索引是大不相同的。

使用ORM，仍要理解索引，ORM工具能生产符合逻辑、合法的查询，但很难生成适合索引的查询。

二、索引类型
索引是在存储引擎层实现的，所以并没有统一的索引标准：不同的存储引擎的索引的工作方式不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一索引类型，其底层的实现方式也可能不一样。下面主要讨论的是InnoDB和MyISAM两种存储引擎支持的索引类型。
1、B-Tree索引
使用术语上叫“B-Tree”，实际上底层存储引擎可能使用不同的存储结构，比如，NDB集群存储引擎使用的是T-Tree(一种特殊的AVL树)，而InnoDB和MyISAM使用的都是B+Tree。InnoDB和MyISAM虽然使用相同的存储结构，但是它们索引存储的数据以及使用方式并不相同。下图是建立B-Tree结构（技术实现上是B+Tree）上的索引：

B-Tree对索引列是顺序组织存储的，所以很适合范围查找数据。假设有如下数据表：
CREATE TABLE People(
last_name varchar(50) not null,
first_name varchar(50) not null,
birth date not null,
gender enum(‘m’,‘f’) not null,
key(last_name,first_name,birth)
);
对于表中的每一行数据，索引中包含了last_name,first_name和birth列的值。下图显示B-Tree索引是如何组织数据存储的：

B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点。
叶子节点比较特别，他们的指针指向的是被索引的数据，而不是其他的节点页。

我们注意到，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时的顺序。

B-Tree适用于全键值、键值范围或键前缀查找，其中键前缀查找只适用于根据最左前缀查找。上面的索引对如下类型的查询有效：
1）全值匹配
指的是和索引中的所有列进行匹配，例如上面的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。
2）匹配最左前缀
上面的索引可用于查找所有last_name为Allen的人，即只使用索引的第一列。
3）匹配列前缀
也可以只匹配某一列的值的来头部分。例如上面的索引可用于查找last_name以J开头的人，这里也只用到了索引的第一列。
4）匹配范围值
例如上面的索引可用于查找last_name在Allen和Barrymore之间的人。这里也只用到了索引的第一列。
5）精确匹配某一列并范围匹配另外一列
上面的索引查找所有last_name是Allen，并且first_name 是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。
6）只访问索引的查询
B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，无需访问数据行，后面会专门讨论这种“覆盖索引的优化”。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY 操作。一般来说，如果B-Tree可以按照某种方式查找值，那么也可以按照这种方式用于排序。

下面是关于B-Tree索引的限制：
1）如果不是按照索引的最左列开始查找，则无法使用索引。例如，上面的索引无法查找first_name是Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。
2）不能跳过索引中的列。也就是说，上面的索引无法用于last_name是Smith、并且在某个特定日期出生的人，这里只能用到索引的第一列。
3）如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。列入查询WHERE last_name=‘Smith’ AND first_name LIKE ‘J%’ AND birth=‘1976-12-23’，这个查询只能用到索引的前两列，因为这里的LIKE是一个范围条件。如果范围查询列值的数量有限，可以使用多个等于条件来代替。

看到这里就会明白，索引的顺序很重要。在优化性能的时候，可能需要想到的列但是顺序不同的索引来满足不同的查询需求。

2、哈希索引
哈希索引是基于哈希表实现，只有精确匹配索引所有列的查询才有效。mysql只有Memory引擎显示支持哈希索引，这里不多做记录。值得注意的是InnoDB有一个特殊的额功能叫做“自适应哈希索引”，当InnoDB注意到某些索引只被引用的非常频繁，它会在内存中基于B-Tree之上再创建一个哈希索引（我的理解就是索引的缓存），这是一个完全自动的、内部的行为。如果有必要，用户可以关闭这个功能。

创建自定义的哈希索引。举个例子，要存储大量的URL，并需要根据URL精选搜索查找，如果用B-Tree来存储URL，存储的内容将会很大，因为URL本身都很长，一般情况下会有下面的查询：

SELECT id FROM url WHERE url=“http://www.mysql.com”;
若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面方式查询：
SELECT id from url WHERE url=“http://www.mysql.com” AND url_crc=CRC32(“http://www.mysql.com”);
这样做的性能会非常高，因为mysql优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查询。即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较数据行。这个哈希值的维护可以使用触发器也可以程序里面维护。

有一个表，数据如下

f是假设的哈希函数；三图每个槽顺序编号，但是数据行不是，查询select lname from testhash where fname=‘Peter’ ;时，mysql先计算Peter的哈希值，并使用该值寻找对应的记录指针，据二图，在索引中查找8784，找到第3行指针，比较第三行的值是否为Peter，以确保是要查找的行（来、确认下眼神）紧凑、快

限制：因为此so只适用特定场合，一旦适合、性能显著（适合查找表的需求）
哈希索引只包含哈希值和行指针，不能使用索引值来避免读取行，不过访问内存中的行的速度快
哈希索引数据不是按照索引值顺序存储，无法用于排序
不支持部分索引列匹配查找：使用索引列的全部内容计算哈希值，在列A、B上建索引，如只查A，无法使用索引
只支持等值比较查询 = in <=> ,不支持范围查询
（表非常大易）出现哈希冲突（不同的索引列值却有相同的哈希值）时，引擎须遍历链表中all行指针，逐行比较
冲突很多时，维护操作代价很高，如在某个选择性很低（冲突很多）的列建立哈希索引，当从表中删除一行，引擎需遍历对应哈希值链表中每一行，找到并删除对应的行引用，冲突多代价大

InnoDB引擎有个特殊的功能“自适应哈希索引adaptive hash index”，当某些索引值频繁使用，会在内存中基于B-Tree索引上再建个哈希索引，使得B-Tree索引也具有哈希索引的一些优点：快速哈希查找，这是自动内部行为，可关闭；

创建自定义哈希索引，如果引擎不支持哈希，则可模拟想innodb一样创建哈希索引：

b-tree基础上创建伪哈希索引（还是使用B-Tree查找）使用哈希值而不是键进行索引查找，需要在where子句中手动指定使用哈希函数
实例：存储大量url，据url搜索，使用b-tree存内容会很大：删除原url列的索引，增被索引的url_crc列，使用CRC32做哈希，查询改为：select id from url where url=‘http://www.mysql.com’ and url_crc=CRC32(‘http://www.mysql.com’);性能高 优化器会使用url_crc列索引完成查找，快
避免哈希冲突，须在where中带入哈希值及对应列值，如不查询具体值，可不带入列值，直接使用CRC32哈希值查询，select word ,crc from words where crc=CRC32(‘gun’) and word=‘gun’;
推荐使用FNV64（）做哈希函数，直接替换

3、空间数据索引
只有MYISAM支持，可以用做地理数据存储。但是mysql支持的GIS不完善，所以使用的不多，开源数据库这方面做的比较好的是PostgreSQL的PostGIS。

4、全文索引
全文索引是中特殊类型的索引，它查找的是文本中的关键字，而不是直接比较索引中的值。全文索引更类似与搜索引擎做的事，而不是简单的WHERE条件匹配。MYISAM支持这种索引类型。

5、其它索引类型
还有很多其它存储引擎使用不同类型的数据结构来存储索引。例如，TokuDB使用分形树索引，ScaleDB使用的是Patricia tries，其它的存储引擎技术如InfiniDB和Infobright则使用了一些特殊的数据结构来优化某些特殊的查询。

索引的优点：
快速定位到表的指定位置，据创建索引的数据结构不同，索引其他附加作用
B-Tree顺序存储可以用来做order by和group by操作，数据有序会将相关列值一起存储，存储了实际列值，使用索引即可完成全部查询：总结如下
1.索引大大减少了服务器需要扫描的数据量
2.索引可以帮助服务器避免排序和临时表
3.索引可以将随机I/O变成顺序I/O

思考：索引是最好的解决方案吗？索引并不总是最好的解决方案。总的来说，对于非常小的表，大部分情况下简单的全表扫描更有效(小表可能一次IO就把所有数据行载入到了内存，在内存即使做全表扫描也很快，而使用索引除了需要查找索引树，一般情况至少也需要一次IO)；对于中到大型的表，索引就非常有效；而对于特大型的表，建立和使用索引的代价将随之增长，这种情况下，则需要一种技术直接区分除查询需要的一组数据，而不是一条记录一条记录的匹配。例如可以使用分区技术。

三、高性能索引策略
1、独立的列
如果查询中的列不是独立的，则mysql就不会使用索引。“独立的列”是指索引不能是表达式的一部分，也不能是函数的参数。列如下面这个查询就无法使用actor_id列的索引：
mysql> select actor_id from skali.actor where actor_id+1=5
我们很容易看的出where中的表达式其实就等价于actor_id=4，但是mysql无法自动解析这个方程式，所以我们应该养成简化where条件的习惯，始终将索引列单独放在比较一侧。下面是另一个常见的错误：
select … where TO_DAYS（CURRENT_DATE）-TO_DAYS(date_col)<=10

2、前缀索引和索引选择性
有时候需要索引很长的列，这回让索引变得大且慢。一个策略是前面提到的通过模拟哈希索引，另一个策略就是索引的开始的部分字符，这样可以大大的节约索引空间，从而提高索引效率。但是这样也会降低索引的选择性。

索引的选择性是指，不重复的索引值（也称为基数）和数据表的总记录数的比值（#T），范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让mysql在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

一般情况下某个列的前缀索引的选择性也是足够高的，足以满足查询的性能。对于BLOB、TEXT或，者很长的VARCHAR类型的列，必须使用前缀索引，因为mysql不允许索引这些列分全部长度。

前缀应该足够长，以使得前缀索引的选择性接近与索引整个列（换句话说，前缀的“基数”应该接近于完整列的“基数”），同时又不能太长（以便节约空间）。为了决定前缀合适的长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

3、多列索引
很多人对多列索引的理解不够。一个常见的错误就是，为每个列创建独立的单列索引，或者按照错误的顺序创建多列索引。例如，有如下查询：
mysql> select … where c1=1 and c2=2 and c3=3
然后创建索引把where条件里面的列都创建上索引：
这种索引的策略是错误的，在多个列上建立独立的索引大部分情况下并不能提高mysql的查询性能（在每个列的可选择性都很低的情况下）。mysql5.0和更新版引入了一种叫“索引合并”的策略，一定程度上可以使用表上的多个单列索引来定位指定的的行。

对多个索引相交操作（多个and），意味着需要一个包含all相关列的多列索引，不是多个独立单列索引
服务器对多个索引做联合操作（多个OR），需要耗费大量cpu和内存资源
优化器只关心随机页读取，不会将这些计算到“查询成本”中，消耗cpu 内存资源 影响并发性

索引合并策略有时候是一种优化策略，但实际上更多时候说明了表上的索引建的很糟糕。如果在EXPLAIN看到了索引合并，应该好好检查一下查询和表结构，看是不是已经是最优的。可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

4、选择合适的索引列顺序
正确的顺序依赖使用该索引的查询，并且需要考虑如何更好的满足排序和分组的需求（本节内容适用于B-TREE索引；哈希和其他类型的索引并不会像B-TREE索引一样按顺序存储数据）。
对于如何选择索引的列顺序有一个经验法则：当不需要考虑排序和分组时，将选择性最高的列放到索引最前列通常是很好的。这个时候索引的作用只是用于优化WHERE条件的查找。在这种情况下，这样设计的确实能最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引的选择性（整体基数），也和查询的具体值有关，也就是和值的具体分布有关。可能需要根据 那些运行频率最高的查询来调整列的顺序，让这种情况下索引的选择性最高。
最后，尽管关于选择性和基数的经验法则值得去研究和分析，但一定不要忘了WHERE子句中的排序、分组和范围条件等其他因素，这些因素可能对于查询的性能造成非常大的影响。

5、聚簇索引
聚簇：数据行和相邻键值紧凑存储在一起
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-TREE索引和数据行：数据上存放在索引的叶子页中:聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针 ;

实际存储的循序结构与数据存储的物理机构是一致的，通俗地说：聚簇索引的顺序是数据的物理存储顺序；通常来说物理顺序结构只有一种，一个表的聚簇索引也只能有一个，通常默认主键，设置了主键，系统默认就为你加上了聚簇索引，也可自定义；说到这里非聚簇索引是不是就知道了

存储引擎负责实现索引，不是all引擎支持聚簇索引；InnoDB通过主键聚集数据，上图被索引的列：主键，如果没有定义主键，innoDB会选择唯一的非空索引代替，如果没有则隐式定义一个主键来作为聚簇索引；InnoDB只聚集 在同一个页面中的记录，包含相邻键值的页 可能相距甚远；

聚簇主键可能利性能也可能累性能

因为是存储引擎负责实现，因此不是所有的存储引擎都支持聚簇索引。这里我们主要关注的是InnoDB。但是讨论的原理对于任何支持聚簇索引的存储引擎也是适用的。下图展示聚簇索引是如何存放记录的：
InnoDB将通过主键聚集数据，这也就是说上图中“被索引的列”就是主键。
如果没有定义主键，InnoDB会选择一个唯一非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录。包含相邻键值的页面可能会相差很远。

聚集的数据有一些重要的优点：
1）可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少量的数据页就能获取牧歌用户的全部邮件。
2）数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中获取数据更快
3）使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

同时它也有一些缺点：
1）聚簇索引最大限度的提高了I/O密集型应用的性能，但如果数据全部存放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没有什么优势了。
2）插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键的顺序加载数据，那么加载完数据后最好使用OPTIMIZE TABLE命令重新组织一下表。
3）更新聚簇索引列的代价很高，因为为强制InnoDB将每个被更新的行移动到新的位置。
4）基于聚簇索引的表插入新行，或者主键被更新导致需要移动行的时候，可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行，这是一次页分裂操作。
5）聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
6）二级索引（非聚簇索引）可能比想象中的更大，因为二级索引的叶子节点包含了引用行的主键。
7）二级索引访问需要两次索引查找，而不是一次。
所以在表使用InnoDB做存储引擎时，应使用自增的主键来作为聚集数据。最好避免随机的（不连续且值的分布范围非常大，比如UUID）聚簇索引，特别时对于I/O密集型应用。

注意：对于高并发的工作负载，在InnoDB中按主键顺序插入可能会导致明显的争用。主键的上届会成为“热点”，因为所有的插入都发生在这里，所有并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制；如果遇到这个问题，则可能需要考虑重新设计表或者应用，或者更改innodb_autoinc_lock_mod配置，如果你的不支持这个参数，可以升级到新版本的innodb。



在InnoDB表中按主键顺序插入行
无需要聚集的数据，自增列做主键（保证顺序写入，主键关联操作性能好）；避免随机聚簇索引（I/O密集型的应用），UUID做聚簇索引很槽（插入随机、无聚集特性、时间长、空间大、页分裂、碎片）
UUID聚簇索引：新行的主键值不一定比之前的大，InnoDB要为新行寻找合适位置且分配空间（多次I/O 页分裂 移动数据 碎片），额外工作、分布不够优化

顺序主键何时造成更坏结果：
高并发工作负载，InnoDB按主键顺序插入可能造成明显争用，主键上界成为“热点”：alll插入发生在这里，并发插入导致间接锁竞争；
auto_increment锁机制；考虑重新设计表或应用，或更改innodb_autoinc_lock_mode配置（更新版本）

小结：
独立的列：索引列不是表达式的一部分或函数的参数
前缀索引和索引选择性；选择性算是为前缀索引服务
多列索引、单列索引：对比理解
选择合适的索引列顺序：很重要
聚簇索引：知识需要一个反复的过程

6、覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。覆盖索引是非常有用的工具，能够极大的提高性能。考虑一下如果查询只需要扫描索引而无须回表，通过会带来多少好处：
1）索引条目通常远小于数据行大小，所以如果只需要读取索引，那么mysql会极大的减少数据访问量。这对缓存的负载非常重要，因为这种情况响应时间大部分花在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为所有比数据更小，更容易全部放入内存（这对MyISAM尤其正确，因为MyISAM能够压缩索引以使索引变得更小）。
2）因为索引是按照列值顺序存储的（至少在单个页内是如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。对于某些存储引擎，例如MyISAM和Perconca XtraDB，甚至可以通过命令使得索引完全顺序排列，这让简单的范围查询可以使用完全顺序的索引访问。
3）一些存储引擎如MyISAM在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用。这可能导致严重的性能问题，尤其是那些系统调用占了数据访问中的最大开销的场景。
4）由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。InnoDB的二级索引在叶子节点中保存行的主键值，所以如果二级主键能覆盖查询，则可以避免对主键索引的二次查询。

不是所有类型的索引都可以成为覆盖索引，Mysql只能使用B-Tree索引做覆盖索引。当发起一个被索引覆盖的查询（也叫索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。

索引覆盖查询还有很多陷阱可能会导致无法实现优化。来看看为什么会发生这样的情况，以及如何重写查询以解决该问题。从下面的查询开始：

这里索引无法覆盖查询，有两个原因：
1）没有任何索引能够覆盖这个查询。因为查询从表中选择了所有的列，而没有任何索引覆盖了所有的列。不过，理论上mysql还有一条捷径可以利用：where条件中的列是有索引可以覆盖的，因此mysql可以利用该索引找到对应的actor并检查title是否匹配，过滤之后再读取需要的数据行
2）mysql不能在索引中执行like操作，这是底层存储引擎API的限制

也有办法可以解决上面的问题，需要重写查询并巧妙的设计索引。先将索引扩展至覆盖三个数据列（artist,title,prod_id），然后按如下方式重写查询：

我们把这种方式叫做延迟关联，因为延迟了对列的访问。

索引条件推送
mysql5.6 改进 索引条件推送：改善查询执行方式
mysql利用索引（二级索引）元组和筛字段在索引中的where条件从表中提取数据记录的一种优化操作
概念：Index Condition Pushdown (ICP)用某一个索引对一个特定的表 从表中获取元组，单表利用索引进行扫描以获取数据
目的：减少完整记录（一条完整元组）读取的个数；
对于InnoDB聚集索引无效，只能是对SECOND INDEX这样的非聚集索引有效
//打开ICP，则Extra列中显示“Using index condition” off关闭
set optimizer_switch=‘index_condition_pushdown=on’;
优化过程：存储引擎在访问索引的时候检查筛选字段在索引中的where条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从server层下推到storage engine层；

细化：对比
这个图很棒，真是一张图胜过千言万语的本图了，如果不太理解的话，看原文【源】【源】
不使用ICP的查询过程：
1）存储引擎读取下一行时，首先读取索引元组（index tuple）
然后使用索引元组在基表中（base table）定位和读取整行数据。
2) sever层评估where条件，如果该行数据满足where条件则使用，否则丢弃。
3) 执行1），直到最后一行数据。

使用ICP：
索引条件下推：
筛选字段在索引中的where条件从server层下推到storage engine层

减少存储引擎访问基表的次数和mysql server访问存储引擎的次数
过程：下推 细化

1. storage engine从索引中读取下一条索引元组。
2. storage engine使用索引元组评估下推的索引条件
   如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。
3. 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
4. server层评估没有被下推到storage engine层的where条件，如果该行数据满足where条件则使用，否则丢弃
   
   注意一下ICP的使用条件：
   只能用于二级索引(secondary index)。
   explain显示的执行计划中type值（join 类型）为range、 ref、 eq_ref或者ref_or_null。且查询需要访问表的整行数据，即不能直接通过二级索引的元组数据获得查询结果(索引覆盖)。
   ICP可以用于MyISAM和InnnoDB存储引擎，不支持分区表（5.7将会解决这个问题）。

7、使用索引扫描来做排序
mysql有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果EXPLAIN出来的type列的值为“index”，则说明mysql使用的索引扫描来做排序。

扫描索引本身是很快的，因为只需要从一条记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上是随机I/O，因此按索引顺序读取数据的速度通常要比顺序的全表扫描慢，尤其是在I/O密集型的工作负载时。

mysql可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应尽可能的满足这两种任务。

只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向都是一样时，mysql才能使用索引来对结果做排序。如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，mysql都需要执行排序操作，而无法利用索引排序。

有一种情况下order by子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果where子句或者join子句中对这些列指定了常量，就可以“弥补”索引的不足。例如：
如果对“rental_date”,“inventory_id”，"customer_id"按字段顺序建立了多列索引，则即使order by子句不满足最左前缀的要求，也可以用于查询排序。这是因为索引的第一列被指定为一个常数。

8、压缩（前缀压缩）索引
MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存，这在某些情况下能极大的提高性能。默认值压缩字符串，但通过参数设置也可以对整数做压缩。

压缩使用更少的空间，待见是某些操作可能更慢。因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在所言块中使用二分查找而只能从头开始扫描。正序的扫描速度还不错，但如果时倒序就是很好了。测试表明，对于cpu密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。压缩索引的倒序扫描就更慢了。压缩索引需要在cpu内存资源与磁盘之间做平衡。压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

可以在create table语句中指定PACK_KEYS参数来控制索引压缩的方式。

9、冗余和重复索引
mysql允许在相同的列上创建多个索引，无论是有意的还是无意的。mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个的考虑，这回影响性能。
重复索引是指在相同的列上按照相同的顺序创建的相同类型的额索引。应该避免这样创建索引，发现以后也应该立即移除。

10、索引和锁
索引可以让查询锁定更少的行。如果你的查询不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。

InnoDB只在访问行的时候才会对其加锁，而索引能减少InnoDB访问的行数，从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回到服务器层以后，mysql服务器才能应用where子句。这是已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。通过下面的例子解释这些情况:

这条查询仅仅会返回2_{4之间的行，但实际上获取了1}4之间的行的排他锁。InnoDB会锁住第一行，这是因为mysql为该查询选择的执行计划是索引范围扫描：

换句话说，底层存储引擎的操作是“从索引的开头开始获取满足条件actor_id<5的记录”，服务器并没有告诉InnoDB可以过滤第1行的where条件。注意到EXPLAIN的Extra列出现了“Using where”，这表示mysql服务器将存储引擎返回行以后再应用where过滤条件。

5.4案例学习
5.4.1支持多种过滤条件
考虑表上all的选项，对查询优化：优化查询 索引中找平衡

建议将使用频率高的列作为前缀（即是选择性低）在查询是即使没该字段也要用上该字段

尽可能将需要做范围查找的列放到索引的后面

不能滥用，降性能：in条件，优化器做的组合以指数形式增加，达到一定数量不再执行计划评估，索引不能很好被利用，耗内存

5.4.2避免多个范围条件

5.4.3优化排序
数据量比较大，翻页翻到后面，order by和limit，费时，解决：反范式化、预先计算缓存
或延迟关联：通过覆盖索引查询返回需要的主键，据主键关联原表获取数据（上一篇有例子）

5.5维护索引和表
目的：
1、找到并修复损坏的表，2、维护准确的索引统计信息，3、减少碎片

5.5.1目的一
损坏的索引可能导致查询返回错误数据，莫须有的主键冲突、导致数据库崩溃等
check talbe（有些引擎不支持）找出表和索引的错误
repair table修复损坏的表，不是all引擎支持，如不支持，通过alter重建表
系统区域、行数据区域损坏：备份中恢复表、损坏文件中恢复数据
引擎表损坏：硬件问题、外部操作数据文件、不是查询的问题
遇到损坏：最重要找出原因，设置innodb_force_recovery进入InnoDB强制恢复模式修复数据

5.5.2更新索引统计信息
查询优化器通过两个API了解存储引擎的索引值分布，决定如何使用索引
1、records_in_range,向存储引擎传入两个边界值获取在这个范围大概有多少条记录
2、info，返回各种类型的数据，包括索引的基数（每个键值有多少条记录）
show index from table_name 查看索引基数；
cardinality索引列基数：显示存储引擎估算索引列有多少个不同的取值
mysql5.0及以后可information_schema.statistics查询这些信息（info出的信息）
MySQL优化器使用基于成本的模型，以查询需要扫描多少行 来衡量成本，如表无统计信息或不准确，优化器可能会做出错误的决定，可通过analyze_table重新生成统计信息解决

每种存储引擎实现索引统计信息的方式不同，需要analyze table 频率不同 运行成本不同
1、memory不存储统计信息
2、myisam将索引统计信息存储在磁盘，analyze table需进行一次全索引扫描来计算索引基数，锁表
3、直到mysql5.5，innodb不在磁盘存储索引统计信息，而是通过随机索引访问评估并将其存储在内存中
首先随机读取少量索引页面，为此样本计算索引的统计信息，通过innodb_stats_sample_pages设置样本页数据
InnoDB将在 表首次打开、执行analyze table 、表大小发生非常大的变化 计算索引统计信息
将在打开information_schema表、show table status 、show index、客户端开启自动补全功能时触发索引统计信息的更新(大量数据、很严重的问题 特别I/O慢时 锁），可关闭innodb_stats_on_metadat避免上述问题
更稳定执行计划、系统重启快速生成统计信息，可用系统表持久化these信息，percona5.1通innodb_use_sys_stats_table启用该特性，mysql5.6通innodb_analyze_is_persistent控制
关闭索引统计信息后需要周期性使用analyze table手动更新信息，否则 MySQL的脾气 你懂的

5.5.3减少索引和数据碎片
B-Tress索引可能碎片化，减低查询效率 : B-Tree需随机磁盘访问才能定位到叶子页
叶子页物理分布顺序且紧密，查询性能更好，否 范围查、索引覆盖扫描 速度会降
表的数据存储也可能碎片化：更复杂
行碎片：row fragmentation 行存储在多个地方的多个片段中
行间碎片：intranet-row fragmentation逻辑上顺序的页（或者）行在磁盘上不是顺序存的 （或者这两字是什么意思？）
全表扫描 聚簇索引扫描之类的操作很大影响
剩余空间碎片：free space fragmentation 数据页中有大量空余空间
消除碎片：
可通过执行optimize table或导出再导入重新整理数据(almost引擎）
不支持的引擎，通过alter table重建表（修改引擎）
对于MyIASM 通排序算法重建索引消除碎片

5.6总结
选择索引和编写利用索引的查询时原则：
1、单行访问很慢，best读取的块中能包含尽可能多所需要的行
2、按顺序 访问 范围数据：尽量使用原生顺序 避免额外排序
1）不需多次磁盘寻道，2）按需要顺序读取 不再需额外排序操作，order by不需排序 将行按组聚合计算
3、索引覆盖 查询 很快，包含all列，不需回表，避免单行访问

判断索引合理性：
按响应时间对查询进行分析：
找出消耗max时间 或 给服务器压力max的查询（第3章），检查查询的schema sql 索引结构
判断是否有查询扫描了太多的行、做了很多额外的排序、使用临时表 、使用随机I/0访问数据、太多回表查

如果查询不能从all可能的索引中获益：
1、是否可建更合适的索引，2、是否可重写该查询

第五章 创建高性能的索引
1.索引类型
1.1 普通索引 NORMAL:
是最基本的索引，它没有任何限制。
1.2 唯一索引 SPATIAL:
与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
1.3 主键索引:
是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
1.4 组合索引:
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
1.5 全文索引
主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

2.缺点
2.1 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2.2 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
2.3 索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

3.注意事项
3.1 索引不会包含有null值的列:
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
3.2 使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.3 索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
3.4 like语句操作
一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引
3.5 不要在列上进行运算
这将导致索引失效而进行全表扫描
例如 SELECT * FROM table_name WHERE YEAR(column_name)<2017;
3.6 不使用not in和<>操作

4.索引方法
hash:精准查询使用
B-Tree:范围查询使用

5.通过EXPLAIN优化索引

5.1 索引基础

5.2 索引的优点

5.3 高性能的索引策略

5.4 索引案例学校

5.5 维护索引和表

第六章 查询性能优化

衡量查询性能的三个指标
返回的行数：意思是select返回的列数以及行记录数。
扫描的行数：查询所需要扫描的行数。
相应的时间：执行sql时间以及等待（sleep等待连接，lock等待锁的时间）
返回的行数

案例

1. 查询不需要的记录即多余的记录
2. 查询多余的列。很多时候为了方便直接select *，这样造成有些字段是不需要的也查询出来了。建议只查询出所需的字段。减少返回数据的大小，降低IO的压力
3. 重复查询相同的数据

优化方法

1. 采用索引覆盖以及延时索引
2. 重写查询

采用复杂查询还是简单查询

切分查询：将大的查询切分成小的查询。例如，delete数据多时，会造成锁住很多数据，占满了事务日志，造成后续的查询等待锁时间过长。如果切分成小的查询，减少了锁的竞争以及减少了对性能的影响。

分解关联查询：将关联查询分解成多条简单查询。在过去会认为客户端连接性能较低，且解析优化的过程较长，所以一般会把多条简单的查询写成关联查询。但现在随着连接性能提高以及缓存效率的提高，将关联查询分解成多条简单查询，未尝不是件好事。分解后的优势在于：1.增加利用查询的缓存。2.减少了锁竞争。3.减少冗余数据的查询。4.扩展性提高

3.更改库表结构:
例如：采用单独的汇总表

扫描的行数
案例

1. 减少扫描的行数：例如当只查询一条数据记录时，若没有提示存储引擎，则扫描到了对应的记录数据后，还会继续扫描知道结束。解决办法是加上limit 1，提示只需要一条数据记录，就不会对继续扫描多余的行数。

响应时间
响应时间由等待时间和执行时间组成。要想了解时间都用在哪里，首先要知道执行语句过程中有哪些步骤。根据mysql逻辑结构分成三层，第一层是客户端与数据库的连接，第二层是服务器层，有解析、缓存、优化，第三层是存储引擎，负责数据的存储与读取。接下来会根据三层结构分析执行查询中有哪些步骤?

查询流程
客户端与服务区进行连接通信
服务器检查是否有缓存，缓存如果命中则，直接返回缓存中的结构。否则进行下一步
对sql进行解析预处理，再由优化器生成执行计划
根据执行计划，服务器调用存储引擎的API查询
得到数据则缓存并返回给客户端
查询状态
对于一个mysql连接，或者说一个县城，任何时刻都一个状态，该状态表示MYSQL当前正在做什么。有很多方式能查看当前的状态，最简单的是SHOW FULL PORCESSLIST命令。

Sleep
线程等待客户端发送新的请求
Query
线程正在查询或者正在将结果发给客户端
Locked
线程等待锁释放
Analyzing and statistics
线程正在收集存储引擎的统计信息
Copying to tmp table
线程正在执行查询，并且将其结果集都复制到一个临时表中，这种状态一般要么是在做Group By操作，要么是文件排序操作，或者是UNION操作。若后面还有on disk标记，则表示临时表放在磁盘中
Sorting result
线程正在对结果进行排序
Sending data
表示多种情况，可能在多个状态之间发传输数据，或者在生成结果集，或者在向客户端返回数据
优化特定的查询
优化COUNT()查询
很多认为MYISAM存储引擎中的count()肯定比INNODB存储引擎中的count()快，其实不一定，当在无where条件的情况下，MYISAM中存储了count()的总数据数，所以这种时候MYISAM比INNODB快。但是在有where条件下的时候，可以是统计某个列值的数量时，MYISAM的COUNT()与其他引擎没有什么不同。
如果当要统计某个列值的数量时，建议直接count(),而不是count(列字段)。这样的好处在于直接告诉存储引擎忽略所有列而直接统计所有行数。
使用近似值，如果总记录数不要求完全精确的话，比如统计网站当前活跃用户数以及搜索引擎的总条数。这样我们可以利用explain中的rows行数进行代替。

关联查询
优化关联查询，要确保ON或者USING子句中的列上有索引
建立索引时需要考虑到关联的顺序。通常来说，只需要在关联顺序中的第二个表的相应列上创建索引。例如，当表A和表B用列c关联的时候，假设关联的顺序是B、A，那么就不需要在B表的c列上建立索引。没有用到的索引只会带来额外的负担。
确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样才能使用索引来优化这个过程

子查询
优化的建议尽可能的使用关联查询代替子查询
GROUP BY 和 DISTINCT
对关联字段做分组效率高
WITH ROLLUP转移到应用程序中进行处理

LIMIT分页
当翻页到非常靠后页面的时候，偏移量会非常大，这是LIMIT的效率是非常差的。例如查询第50000，50020记录时，需要扫描50020条记录后返回结果，这样的效率是非常低的。优化方法如下：
利用上一页最后的主键做偏移。例如上述的例子,必须满足的是主键是连续且
SELECT * FROM sakila.rental where rental_id > 50000 DESC LIMIT 20;
延时关联。利用索引覆盖先查找出主键，在通过主键查找对应的记录。尽可能的减少扫描的页面
SELECT film.film_id, film.description FROM sakila.film
INNER JOIN
(SELECT film_id FROM sakila.film ORDER BY title LIMIT 50000,20) AS lim
USING(film_id);

返回的记录总数
将总数替换成下一页按钮。假设每页显示20条记录，每次查询就增多一条即21条记录，如果有21条记录，则显示下一页，否则就说明没有更多的数据，无须显示下一页按钮
将指定数量的数据缓存
利用执行计划explain中的row

UNION查询
除非确实需要服务器消除重复的行，否则一定要使用UNION ALL。如果没有ALL关键字，MySql会给临时表加上DISTINCT选项，这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。

6.1 为什么查询速度会慢

6.2 慢查询基础：优化数据访问

6.3 重构查询的方式

6.4 查询执行的基础

6.5 MySQL查询优化器的局限性

6.6 查询优化器的提示（hint)

6.7 优化特定类型的查询

6.8 案例学习

前言：从查询设计的一些基本原则开始，介绍一些更深的查询优化技巧，会介绍一些mysql优化器内部的机制；展示MySQL如何执行查询、*也将学会如何改变查询的执行计划，最后看优化器哪些方面做得不够好，探索查询优化模式
目标：帮助大家更深刻理解MySQL如何真正地执行查询，且明白高效低效的原因何在

6.1 为什么查询速度会慢
查询生命周期：
客户端、服务器、服务器解析、生成执行计划、执行（大量为了检索数据到引擎的调用、调用后数据处理：排序 分组等）、返回结果
完成这些任务查询需要在不同地方花费时间:网络 cpu计算 生成统计信息和执行计划 锁等待 等
了解生命周期，清楚查询时间消耗情况对优化查询很大意义

6.2慢查询基础：优化数据访问
almost低下查询可通过减少访问的数据量进行优化
1、确认用于程序是否在检索大量超过需要的数据：访问太多的行/列
2、确认MySQL服务器层是否在分析大量超过需要的数据行

6.2.1是否向库请求了不需要的数据
用缓存、需权衡，尽量不用select * ，limit

6.2.2MySQL是否在扫描额外的记录
指标：1、响应时间，2、扫描的行数，2、返回的行数（到慢日志中找）

响应时间：服务时间+排队时间
服务时间：数据库处理这个查询真正花了多长时间
排队时间：服务器因为等待某些资源而没有真正执行查询的时间
看到一个查询响应时间时，先自问：这个时间是否合理，推荐“快速上限估计”来估算：
了解该查询需要哪些索引及它的执行计划是什么，计算大概需要多少个顺序和随机I/O，用其乘以在具体硬件条件下一次I/O消耗时间，最后相加=大概参考值

扫描的行数、返回的行数
二者应该相同，但是……
explain中的type列反应了访问类型
全表扫描all、范围扫描、唯一索引查询、常数引用：从慢到快、行数从小到大
一般MySQL使用如下三种方式应用where条件，从好到坏：
1、索引中使用where条件过滤不匹配的记录，存储引擎层完成
2、使用索引覆盖扫描（extra列using index）返回记录，直接从索引中过滤不需要的记录并返回命中结果，服务器层，无需回表
3、从表中返回数据，过滤不满足条件的记录（extra列using where）服务器层完成，先从表读出记录然后过滤
优化：
1、使用索引覆盖扫描，all需要的列放到索引中无需回表
2、改变库表结构，单独汇总表
3、重写复杂的查询

6.3重构查询的方式
6.3.1一个复杂查询还是多个简单查询
mysql 连接和断开连接是很轻量级的，响应数据给客户端相比是慢的
其他条件相同时，使用尽可能少的查询是很好的，但是要视情况而定

6.3.2 3切分查询、分解关联查询
大查询：有时候需分而治之

分解关联查询：对每一个表进行单表查询，然后将结果在应用程序中进行关联
1、缓存效率更高：应用程序方便缓存单表查询的结果对象
2、单个查询减少锁的竞争、查询本身效率可能有所提升
3、应用层做关联，更容易对库拆分，易做到高性能和可可扩展
4、减少冗余记录的查询，应用层做关联，对某条记录应用只需查询一次，减少网络和内存消耗
5、相当于在应用中实现了哈希关联，

6.4查询执行的基础
查询执行路径：mysql到底做了什么

6.4.1MySQL客户端/服务器通信协议
半双工：任何时刻，服务器和客户端不能同时给对方放送消息，简单快速但无法控制流量
客户端从服务器获取数据，只能接着不停让其停
多数连接mysql的库函数均可获取all结果集并缓存到内存里（默认）或逐行获取数据

查询状态：
状态：MySQL当前正在做什么，show full processlist （结果集中的command即是）
1、sleep线程等待客户端发送新请求
2、query线程正在执行查询或将结果发送给客户端
3、locked：mysql服务器层，该线程正等待表锁；引擎级别实现的锁：innodb行锁 不会体现在线程状态中
4、analyzing and statistics：线程正在收集存储引擎的统计信息，并生产查询的执行计划
5、copying to tmp table [on disk]线程正在执行查询，且将其结果集都复制到一个临时表中
在做group by 文件排序 union 操作时，后有on disk 表示mysql正在将一个内存临时表放到磁盘上
6、sorting result：线程正在对结果集进行排序
7、sending data：多种情况，可能在多个状态间传送数据，或在生成结果集，或向客户端返回数据

6.4.2查询缓存
是否命中缓存：大小写敏感的哈希查找实现，命中、检查权限（缓存中有）ok返回

6.4.3查询优化处理
查询的生命周期下一步：将一个SQL转换成一个执行计划，MySQL再依照这个执行计划和存储引擎进行交互：解析SQL、预处理、优化SQL执行计划

语法解析器和预处理
通过关键字将SQL语句进行解析：
解析器将使用MySQL语法规范验证解析查询，
生成解析树，预处理器据规则检查解析树是否合法

查询优化器
优化器将语法树从众多执行方式中找到best的执行计划
基于成本的优化器：预测查询使用某种执行计划时的成本，选择min的一个：通过last_query_cost得当前查询成本
show status like ‘Last_query_cost’
选择错误的执行计划原因：
1、统计信息不准，2、执行计划中的成本估算!=实际执行成本，3、不考虑并发，4、MySQL不是都基于成本的优化，5、mysql不考虑不受其控制的操作的成本（存储过程 自定义函数），6、有时无法估算all可能的执行计划

策略：
静态优化：可直接对解析树进行分析、完成优化；编译时优化，第一次完成一直有效，不依赖特别的数值
动态优化：查询上下文相关，每次查询重写评估，运行时优化

能够处理的优化类型：
重新定义关联表的顺序、将外连接转化为内连接
等价变换规则简化规范表达式，优化count min max （类似常量）
预估并转化为常数表达式、覆盖索引扫描、子查询优化、提前终止查询
等值传播、列表in的比较：先排序、二分查找，==多个OR：

数据和索引的统计信息
服务器查询优化器生成查询执行计划需向存储引擎获取响应的统计信息：
每表或索引有多少个页面，表的索引基数是多少、数据行、索引长度 、分布信息

如何执行关联查询
mysql认为任何一个查询都是一次“关联”，借助临时表 关联

执行计划：
生成指令树、引擎执行完成该树返回结

关联查询优化器：
决定表关联时的顺序，评估不同顺序时的成本选择，因为前面的嵌套所以这个还是挺重要的因素
当需要关联的表数超过optimizer_search_depth限制，会选择“贪婪”搜索模式

排序优化:
排序过程统称文件排序filesort；尽可能避免排序或尽可能避免对大量数据排序
数据量<排序缓存区：内存中快速排序；内存不够，将数据分块、每块使用快速排序 排序，将各块排序结果存放在磁盘上，合并 返回

排序算法：
两次传输排序（旧版）读取行指针和需要排序的字段，对其进行排序，据排序结果读取所需要的数据行；lot随机I/O ，排序时存尽可能少的数据，排序缓存区容纳尽可能多的行数进行排序
单次传输排序（新 4.1后）读取查询所需all列，据给定列排序，查询需all列总长<=max_length_for_sort_data，选此
排序消耗临时空间比较大：足够长的定长空间存排序记录、UTF-8 将为每字符预留三个字节

关联查询需排序：
1、order by子句all列来自关联的第一个表，在关联处理第一个表时进行文件排序，在explain的extra字段会有using filesort
2、其他，先将关联结果存临时表，all关联结束进行文件排序，explain的extra看到using temporary，using filesort，有limit，则limit再排序后应用
mysql5.6很多改进：
当只想要返部分排序结果，limit 不再对all结果排序，据实际情况，抛弃不满足条件的结果再进行排序

6.4.4查询执行引擎
逐步据执行计划（一个数据结构）完成查询，过程中需通过储存引擎实现的接口handler API
查询中每张表由一个handler实例表示(优化阶段便为表建handler实例，优化器据实例接口获取表的相关信息）
引擎接口有丰富的功能（虽底层接口十几个而已），存储引擎插件式架构成为可能的同时也有些限制，不是all的操作由handler完成

6.4.5返回结果给客户端
last阶段，返回结果，可被缓存将结果放到查询缓存中
增量、逐步返回的过程：
服务器处理完最后一个关联表，生成第一条结果时，mysql便可开始向客户端逐步返回结果集
好处：1、服务器不存太多结果、内存省了，2、客户端第一时间获得返回结果
结果集每行均一个满足mysql客户端/服务器通信协议的封包发送 通过TCP协议传输 （ing封包缓存批量传输）

6.5MySQL查询优化器局限性
6.5.1关联子查询
where子查询实现的非常糟糕，最糟一类where包含in

优化：
exists等效改写：

或使用group_concat（）在in中构造由逗号分隔的列表：【源】

GROUP_CONCAT()函数，主要用来处理一对多的查询结果，通常会结合GROUP BY一起使用

GROUP_CONCAT([DISTINCT] expr [,expr ...]
 
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
 
             [SEPARATOR str_val])

GROUP_CONCAT后，SELECT里如果使用了LIMIT是不起作用的

SELECT
    s.stu_id          AS  studentId,
    s.stu_name     AS  studentName,
//数值类型的数据转化成二进制BLOB类型
//CAST(expr AS type)或CONVERT(expr, type)将数值类型的数据转化成字符串
    GROUP_CONCAT(CAST(c.course_id AS CHAR) ORDER BY c.course_id) AS courseId,
    GROUP_CONCAT(c.course_name)  AS  studentCourse
FROM
    student s
LEFT JOIN
    stu_course sc
ON
    s.stu_id = sc.stu_id
LEFT JOIN
    course c
ON
    sc.course_id = c.course_id
GROUP BY
    studentId

如何用好关联子查询
上面说子查询不好，但是没有绝对真理，具体问题具体分析，explain好工具。值得利用：通过测试验证猜想

6.5.2union的限制
如希望union各个子句能据limit取部分结果集，或希望先排好序再合并结果集，要在union各个子句中分别使用这些个子句

6.5.3索引合并优化
where子句包多个复杂条件、mysql能访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行

6.5.4等值传递：
某些时候，等值传递会带来些额外消耗

6.5.5并行执行
mysql无法利用多核特性来并行执行查询

6.5.6哈希关联
参考第5章自定义哈希索引

6.5.7松散索引扫描
mysql5.6之后的版本，通过索引条件下推方式解决松散索引扫描的一些限制
explain的extra字段显示using index for group-by 表示使用了松散索引扫描

6.6查询优化器的提示hint
在查询中加入相应的提示，可控制该查询的执行计划
HIGHT_PRIORITY 和 LOW_PRIORITY 执行顺序相关
当多个语句同时访问某一个表时，哪些语句的优先级高、低
H*：select语句，mysql将其放在表队列的前面；insert语句，简单抵消全局low_priority设置的影响
L*：让语句一直等待
对使用表锁的引擎有效，不要在innodb或有细粒度锁机制 并发控制的引擎中使用：导致并发插入被禁
DELAYED：对insert replace有效
将 使用该提示的语句 立即返回给客户端，将插入的行数据放入到缓存区 在表空闲时批量将数据写入
不是all的存储引擎都支持，会导致last_insert_id无法正常工作
STRAIGHT_JOIN： 关联顺序相关
放在select后：查询中all表按在语句中顺序进行关联
可放任何两个关表的名字间：固定器前后两表的关联顺序
可使explain语句查优化器选择的关联顺序，然后使用该提示重写查询，在看顺序，升级需重审视查询
SQL_SMALL_RESULT和SQL_BIG_RESULT：select
告诉优化器对group by或distinct查询如何使用临时表及排序
SM：告诉器结果集会很小，可将结果集放在内存的索引临时表，避免排序
B：告诉器结果集可能非常大，建议使用磁盘临时表做排序操作
SQL_BUFFER_RESULT:
告诉器将查询结果放入到一临时表，尽可能快地释放表锁，服务器端需more内存，客户端稍解放
SQL_CACHE和SQL_NO_CACHE：
这个结果集是否应该缓存在查询缓存中
SQL_CALC_FOUND_ROWS：
让返回的结果集包含more信息，不应该使用，可通found_row获得这个值
FOR UPDATE和LOCK IN SHARE MODE：只对实现了行级锁的引擎有效
控制select语句锁机制，会对符合查询条件的数据行加锁，避免使用，锁挣用
USE INDEX 、IGNORE INDEX和FORCE INDEX：
使用或不使用哪些索引来查询记录
5.1及后可通过FOR ORDER BY 和FOR GROUP BY指定是否对排序和分组有效
force index同use index（除for*会告诉优化器全表扫描成本高于索引扫描）
5.6新增：
1、optimizer_search_depth:控制优化器在穷举执行计划时的限度
2、optimizer_prune_level:默认打开，让优化器据需要扫描的行数来决定是否跳过某些执行计划
3、optimizer_switch：包含些开启/关闭优化器特性的标志位
前两个参数控制优化器走一些捷径：让优化器处理复杂sql时仍高效，但也可能错过些真正最优的执行计划
自定义设置的 优化器提示 可能使新版的优化策略失效：耍小聪明 不太好

6.7优化特定类型的查询
多数优化技巧和特定版本有关，注意版本
6.7.1优化count查询
作用：
1、统计某个列的数量、行数，如果count()指定列或列的表达式，统计的是这个表达式有值的结果数 非null
2、统计结果集的行数，mysql确认（）内表达式值不为空，便是在统计行数
关于MyISAM：无where的count会非常快
取小的范围、近似值、汇总表、缓存系统
6.7.2优化关联查询
1、确保on或using子句中列上有索引，无其他理由，只在关联顺序的第二张表相应列建索引
2、group by 和order by 的表达式只涉及一个表中的列，才能使用索引
3、版本升级，注意关联语法、运算符优先级等可能发生变化的地方

6.7.3优化子查询
尽可能使用关联查询代替，mysql5.6及更新的版本或mariadb可忽略

6.7.4优化group by和distinct
可使用索引来优化
无法使用索引时：
group by使用临时表或文件排序做分组，可通过提示sql_big_result和sql_samll_result‘指挥’优化器
对关联查询做分组，且按照查找表的某个列进行分组，采用查找表的标识列来分组的效率会更高
优化group by with rollup：
如对返回分组结果再做一次超级聚合，可使用with rollup实现，尽可能将with rollup功能转移到程序中处理

6.7.5优化limit分页
偏移量很大但是要的数据很少，优化 要么在页面中限制分页的数量 要么优化大偏移量的性能
1、尽可能使用索引覆盖扫描，据需要做一次关联操作再返回需要的列
2、延迟关联：提效率，扫描尽可能少的页，获取要访问的记录后据关联列回原表查询需的列
3、将limit查询转换为已知位置的查询，让mysql通过范围扫描获得对应的结果
预先计算出边界值，between * and *，limit 20 ，下次从20个后开始

6.7.6优化SQL_CALC_FOUND_ROWS：
分页时，技巧在limit语句中加SQL_CALC_FOUND_ROWS提示，可获得去掉limit后满足条件的行数，作为分页的总数，会扫描all满足条件的行、抛弃不需要的行，代价可能有点高
1、将具体的页数换成“下一页”按钮，每页显示20条但查询21条哦，21条存在则有数据，否则没有数据(哈哈~优秀优秀）
2、先获取缓存较多数据，每次分页从缓存中获取：程序据结果集大小采取不同策略，<1000 显示all分页链接 >1000 灵活设计 比 找到all再抛弃效率高
使用explain结果中的rows值作为结果集总数的近似值，需精确结果再count（*）

6.7.7用户union查询
通过创建、填充临时表来执行union查询，很多优化策略在union中无法很好使用，需要手工将where 、limit、 order by 等下推到union各个子查询中，让优化器充分利用这些条件进行优化
除非需要服务器消除重复行，否则一定要使用union all ，无all ，mysql会给临时表加上distinct：导致对临时表做唯一性查询，代价高

6.7.8静态查询分析
percona toolkit 的pt-query-advisor能解析查询日志、分析查询模式、给出all可能会有潜在问题的查询、给出建议

第七章

前言：

mysql从5.0和5.1开始引入很多高级特性：分区 、触发器等，这些特性表现如何、带你去发现

7.1分区表
分区表是独立逻辑表，底层由多个物理子表组成

底层文件系统看、分区表all有个#分隔命名的表文件

对底层表的封装：索引也是按分区子表定义的，无全局索引

实现分区的代码是一组底层表的句柄对象Handler Object的封装

对分区表（可直接）请求：通过句柄转成存储引擎接口调用，分区对SQL黑盒子、应用透明

创建表时使用PARTITION BY子句定义每个分区存放的数据，执行查询优化器据分区定义过滤不需要的分区

优缺点：
较粗的粒度将相关数据放一起；表数据可分布不同物理设备、高效利用设备；

表最多只能有1024个分区；5.1中分区表达式须整数or返回整数的表达式；分区字段有主键or唯一索引列，all主键列和唯一索引列都必须包含进来；无法使用外键约束；

发挥大作用的场景：

表大到无法all放到内存中，或表前面都是历史数据

7.1.1分区表原理
存储引擎管理分区的各底层表和普通表一样，分区表的索引：在各底层表上各自加一完全相同的索引

select：分区层先打开并锁住all底层表，优化器判断是否可过滤，再调对应引擎接口访问各个分区的数据

insert：分区层先打开并锁住all底层表，确定哪个分区接收该记录，再将记录写入对应底层表

delete：打开锁住all底层表，确定分区、底层表删除

update：打开锁住all底层表，确定分区，取出数据更新，应放在哪个分区，最后对底层表写入，原数据底层表删除

引擎能自己实现行级锁，如InnoDB则会在分区层释放对应表锁，加锁解锁与普通innodb查询类似

7.1.2、3分区表类型 使用
范围、键值、哈希、列表分区、5.5中可使用range columns类型的分区

数据量超大，b-tree索引无法起作用，除非索引覆盖查询

7.1.4什么情况下回出现问题 5.5版分割线
1、null值或无效值使分区过滤无效：

first分区特殊分区：非法值对应数据放入，查询时mysql检查两个分区

优化：so创建一个无用的第一分区，5.5中不需要优化

2、分区列和索引列不匹配：无法进行分区过滤

3、选择分区的成本很高：类型多 实现方式不同 性能不同

  范围分区：写入时扫描分区列表找到合适目前分区，可通过限制分区数据缓解，键哈希分区无此问题

4、打开并锁住all底层表的成本可能很高：发生在过滤前，和类型无关，影响all查询

5、维护分区成本可能很高：一些操作重组分区 或类似alter操作：需要复制数据 建临时分区 复制其中 删除原区

限制：

1、all分区必须使用相同引擎；2、分区函数中可使用的函数和表达式也有一些限制

3、某些引擎不支持分区；4、对应myisam分区表，不能使用load index into cache

5、myisam表，分区表需打开more文件描述符：看似一表其实lot独立分区，每个分区对引擎都是独立表

  即时配置合适表缓存，还会出现超过文件描述符限制的问题

注意版本，新版本改进了很多

7.1.5查询优化 303
访问分区表：在where条件中带入分区、可让优化器过滤掉无须访问的分区

MySQL只能在使用分区函数的列比较过滤分区，运行时分区过滤

原则：即便在创建分区时可使用表达式，但在查询时却只能据列来过滤分区

7.1.6合并表
早期、简单分区实现，容许用户单独访问各个子表，一门待淘汰的技术

相当于容器，包含了多个真实表，可在create table中使用特别union语法指定包含哪些真实的表

前提条件：字段要和被合并的各个真实表字段相同；表中的索引各个真实字表也有

子表虽有主键限制，但是合并表仍可能有重复值；每个子表行为和表定义相同（同一个表嘛）但合并表全局不受条件影响；删合并表子表不受影响，子表删除但文件描述打开、该表还存在，只能通过合并表访问到

insert_method=last 所有的insert语句发给最后一个表；first

注意：

1、创建不检查各子表的兼容性，子表定义不同，可能建出无法使用的合并表；创建后修改子表使用合并表会报错

2、无法使用replace、自增字段

3、访问合并表，访问all子表，限制合并表中子表数很重要（当合并表是某个关联查询的一部分表时：访问一个表的记录数可能会将比较传递到关联的其他表中）

 执行范围查找，每子表各执行一次，比直接访问单表性能差，子表越多、性能越糟

 全表扫描和普通表的全表扫描速度同

 合并表上做唯一键、主键查询，找到即停止

 子表读取顺序和create table顺序同

合并表子表可直接被访问，具有mysql5.5分区不能提供的特性

1、myisam表可以是多个合并表的子表；2、可通过复制.frm MYI MYD文件，实现服务器间复制子表

3、合并表中添加新子表容易，直接修改合并表定义即可；4、可创建只包含需要数据的合并表；

5、想对子表备份 恢复 修改 修复 等 可先将其从合并表删除，结束后再加；6、可用myisampack压缩子表

7.2视图
5.0后引入，本身虚拟表、不放数据，返回的是其他表生成的，视图和表同一命名空间中

很多地方MySQL对视图和表同样对待，但也有不同，不能对视图创触发器、不是drop table删除视图

实现视图的两种实现算法：本身属性

如果视图中包含group by、distinct、任何聚合函数、union、子查询等的时候无法在原表记录和视图记录建立一一映射，将使用临时表算法实现视图；

explain 一条对视图的查询查看使用了什么算法：select_type为derived临时表

create algorithm = temptable view v1 as select * from actor：基于该视图任何查询视图all生成一个临时表

7.2.1可更新视图 updatable view
1、通过更新视图来更新视图涉及的表，指定了合适的条件、可更新 删除 写入数据

2、如果视图定义中包含group by union 聚合函数及其他特殊情况，不能被更新

3、更新查询可是个关联语句，被更的列须来自同一个表

4、all临时表算法 无法被更新

5、定义视图时可使用check option子句：任何通过视图更新的行，须符合视图本身where定义：不能更新视图定义列以外的列：不能更 continent列，插不同continent值的新数据

7.2.2视图对性能的影响
无须在系统中建权限，借助视图实现基于列的权限控制

临时表算法实现的视图，有时性能会很糟，递归执行这类视图，先外后内，无法做more内外结合的优化

7.2.3视图的限制
不支持物化视图（将视图结果放在一个可查看的表中，定期从原表刷新数据到这个表）

不支持索引：可构建缓存表或汇总表模拟物化视图和索引

修改视图：

并不会保存视图定义的原始SQL语句，可通过.frm文件最后一行取得一些信息，如有file权限，可用LOAD_FILE()语句读取创建信息，再加一些字符处理工作，可获得完整创建语句

substring_index（被截取字段，关键字，关键字出现的次数）【源】【源】

如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容

小结：
分区技术等待淘汰，但是思想、作用还有可取之处

视图这个比较常用，相对而言这篇博客偏于理论，实践性知识还需要在学习、总结

7.3外键约束
有使用成本，修改时在另一张表中执行查找操作；加锁、慢

如果确保两个相关表数据一致的话，使用外键比在应用程序汇总检查一致性性能更高

7.4MySQL内部存储代码
通过触发器、存储过程、函数的形式存储代码，5.1开始，可在定时任务中存代码

不同类型的存储代码区别：执行上下文（输入输出），存储过程/函数都可以接收参数然后返回值，但是触发器和事件却不行；

优缺点：
1、服务器内执行、部署，离数据最近，备份、维护在服务器端完成，维护工作简单、服务器上执行还可节省带宽和网络延迟

2、缓存执行计划，代码重用降消耗，方便地统一业务规则、保证某些行为总是一致，为应用提供一定的安全性：更细粒度的权限控制

3、可简化代码的维护和版本更新，应用和数据库开发人员更好分工

MySQL本身无好用的开发 调试工具
存储代码效率差些：可使用的函数有限
给程序代码部署带来额外复杂性：部署内部存储代码
安全隐患：非标准加密功能放在存储程序中，库被攻破数据泄露；如果加密函数放在程序代码中，必须同时攻破程序和数据库才能获得数据
存储过程会给数据库服务增加额外的压力，数据库服务器的扩展比应用差很多
不能控制资源消耗，调试困难，与基于语句的二进制日志复制合作地并不好

总的来说：存储代码帮应用隐藏复杂性，开发更简答，性能更低，复制有风险；1、问程序逻辑在数据库还是应用代码中实现，编写存储代码时明白这是将程序逻辑放在数据库中；

7.4.1存储过程和函数
优化器：

无法使用关键字deterministic优化单个查询中多次调用存储函数的情况、无法评估存储函数执行成本；

存储程序越小越简单越好，但是当可代替很多小查询时、推荐用存储过程调

7.4.2触发器
无返回值，可改变读取、改变 触发SQL语句所影响的数据

减少客户端和服务器间的通信，简化应用逻辑、提高性能

注意：

每个表每个事件max有关触发器，mysql只支持“基于行的触发”针对一条记录

掩盖服务器背后的工作，问题难排查，可导致死锁和锁等待（触发器失败原SQL失败），并不一定保证更新的原子性

7.4.3事件
mysql5.1引入新的存储代码的方式，类似linux定时任务，在内部实现

在独立的事件调度线程中初始化（set global event_scheduler:=1设置调度线程）

创建事件意味着给服务器带来额外的工作、执行SQL 可能会对性能有很大影响

线程执行完会被销毁

可通show processlist的command查看，总是显示connect

7.4.4存储程序中保留注释
使用版本相关注释

7.5游标
服务器中只提供在存储过程或更底层的客户端API中使用的只读、单向的游标

游标指向的对象all存在临时表中的，so只读，可逐行指向查询结果，让程序进一步处理

打开游标时需要执行整个查询：

如果只是访问一小部分，安排一些limit

7.6绑定变量
4.1支持服务器端绑定变量prepared statement，提高了客户端和服务器端数据传输效率

过程：
创建绑定变量SQL，客户端向服务器发送SQL语句原型，服务器接收到这个SQL语句框架后，解析并存储这个SQL语句的部分执行计划，返回给客户端一个SQL语句处理句柄，每次执行这类查询，都使用这个句柄

 使用？标记可接受参数位置，执行时使用具体值代替这些问号

为什么高效：
解析一次SQL语句；优化器工作只执行一次；二进制方式发送参数和句柄，效率高、省内存降网络开销，格式转换开销，仅传参数；直接将存储存缓存

7.6.1绑定变量的优化
如果执行计划需要据传入的参数做计算则mysql无法缓存这部分计划

三类优化：
在准备阶段：服务器解析SQL、移除不可能的条件、重写子查询

第一次执行：可能的话，先简化嵌套循环的关联、将外关联转化为内关联

每次SQL执行：服务器过滤分区、尽量移除count min max 移除常量表达式 检查常量表 做必要的等值传播 分析和优化ref range 和索引优化等访问数据的方法 优化关联顺序

7.6.2SQL接口的绑定变量
4.1支持，以SQL方式使用绑定变量

存储过程中使用，构建并执行动态SQL

7.6.3绑定变量的限制
会话级别，连接间不能共用绑定变量句柄，连接断开、原有句柄不能再用
5.1后绑定变量的SQL不能使用查询缓存
不是all时候使用绑定变量都获更好的性能：只执行一次SQL
总是忘记释放绑定变量资源，则服务器容易资源‘泄漏’，绑定变量SQL总数的限制是全局的，某一个地方错误可对其他线程产生影响

7.7用户自定义函数UDF
可使用支持C语言 调用约定的编程语言来实现

需要预先编译好并动态链接到服务器上，速度快、可访问大量操作系统的功能，可使用大量库函数

但无法调用UDF线程中使用当前事务处理的上下文去读写数据：更适合计算或与外交互

MySQL版本升级时注意相应改变，是否需要重新编译，或修改UDF让其工作，确保UDF是线程安全的

7.8插件
存储过程插件：

  帮在存储过程运行后再处理一次运行结果

后台插件：

  让程序在MySQL中运行，实现自己的网络监听，执行自己的定时任务

INFORMATION_SCHEMA：

  提供一个新的内存INFORMATION_SCHEMA

全文解析插件：

  处理文本功能，据自己的需求来对文档分词、增强词语匹配功能

审计插件：

  在查询执行的过程中的某些固定点被调用，用作记录MySQL事件日志

认证插件：

   可在MySQL客户端也可在服务器端，使用这类插件扩展认证功能

7.9字符集和校对
字符集：一种从二进制编码到某类字符符号的映射

校对：一组用于每个字符集的排序规则

4.1后每类编码字符都有对应的字符集和校对规则

7.9.1MySQL如何使用字符集
1、每种字符集都有可能有多种校对规则，且都有一个默认的校对规则

2、每个校对规则都是针对某个特定的字符集的

3、校对规则和字符集总是一起使用，统称一个字符集

只有基于字符的值才真正的有字符集的概念，对于其他类型的值，字符集只是一个设置：指定用哪一个字符集来做比较或其他操作

mysql的设置：创建对象时的默认值 在服务器和客户端通信时的设置

创建对象时的默认设置
mysql服务器、每个库、每个表都有默认的字符集和校对规则，逐层继承的默认设置，最终靠底层的默认设置影响创建的对象，至上而下告诉MySQL使用什么字符集来存储某个列

各层可指定特定字符集或让服务器使用默认值

创建库时，据服务器上的character_set_server设定库默认字符集
建表，将据数据库的字符集设置指定表的字符集设置
创建列时，据表的设置指定列的字符集设置
只有当创建列没有指定字符集时，表的默认才起作用，更高的设置只是指定默认

服务器和客户端通信时的设置：
不同的字符集，服务器端要翻译转换：

1、服务器假设客户端是按character_set_client设置的字符集传输数据和sql语句

2、服务器收到客户端SQL语句时，先将其转换成字符集character_set_connection，且使用这个设置决定如何将数据转成字符串

3、服务器端返回数据或错误信息给客户端，将其转换成character_set_result

据需要，使用set names 或set character set语句来改变上面的设置，客户端程序和API也需要使用正确的字符集才能避免在通信时出现问题

MySQL如何比较字符串大小：
如字符集不同，先转成同一个字符集再比较

 如果两个字符集不兼容，抛出错误，通过函数convert显示将其中一个转成一个兼容的字符集

mysql会为每个字符串设置“可转换性”：值的字符集的优先级，影响mysql字符集隐式转换后的值

可使用charset、collation、coercibility定位各字符集相关的错误
可使用前缀和collate子句指定字符串的字符集或校对字符集
指定utf8字符集，collate二进制校对规则

一些特殊情况：
诡异的character_set_database设置

   默认值同默认数据库的设置，当改变库时、跟着变，so当连接到MySQL实例上又没有指定要使用的数据库时，默认值和character_set_server相同

load data infile:

  数据库总是将文件中的字符按照字符集character_set_database来解析，5.0更新版中可在load data infile中使用character set 设定字符集但最好不要依赖这个设定

 指定字符集最好的方式是先使用USE指定数据库、执行set names设定字符集，最后在加载数据；

  mysql在加载数据时，总是以同样的字符集处理所有的数据，不管列是否有不同的字符集设定；

  LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必须是一个文字字符串；【源】

select into outfile：

  导出数据到指定目录下【源】不做任何转码地写入文件，可使用convert将all列做一次转码

嵌入式转义序列：

  MySQL据character_set_client的设置来解析转义序列

解析器在处理字符串的转义字符时，不关心校对规则、前缀只是一个关键字而已

7.9.2选择字符集和校对规则
使用show characterset 和show collation查看mysql支持的字符集和校对规则

极简原则：

alter table将对应列转成互相兼容的字符集

最好先为服务器、数据库选择合理的字符集，据不同情况、让某些列选择合适的字符集

校对规则
是否以大小写敏感的方式比较字符串，或以字符串编码的二进制值比较大小

不同：二进制校对规则直接使用字符的字节进行比较

           大小写敏感的在多字节字符集时，有更复杂的比较规则

设置字符集：
不必同时指定字符集和校对规则的民名字，mysql会使用可能的默认值来填充

7.9.3字符集和校对规则如何影响查询
不同字符集和校对规则间的转换可能带来额外系统开销：

只有排序查询要求的字符集与服务器数据的字符集相同，才能使用索引进行排序

索引跟据数据列的交规规则（排序规则）进行排序

mysql在需要时会进行字符集转换为适应各种字符集，转换列的索引将无法使用

可在explain extended后使用show warnings 查看mysql是如何处理字符集

UTF-8
多字节编码，存储一个字符会使用变长的字节数（1~3）

在MySQL内部，通常使用一个定长的空间存储字符串再进行操作：希望总是保证缓存中有足够空间来存储字符串

多字节字符集中：一个字符不再是一个字节

length和char_length计算字符串的长度，在多字节字符集中，两函数返回值不同

确保在统计字符集时使用char_length

索引限制：

 要索引UTF-8字符集的列，mysql会假设每个字符集都是三个字节，最长索引前缀的限制一下缩短到原理三分之一

考虑使用什么字符集时需要据存储的具体内容来决定：

 存储英文字符，utf-8不会消耗太多空间（英文字符仍使用一个字节）

 存储非拉丁语系的字符：俄语、阿拉伯语区别会很大

   只存阿拉伯语可使用cp1256字符集 用一个字节标识all阿拉伯语字符

 还需要存别的语言，可使用utf-8，这时相同的阿拉伯语字符会消耗more空间

当具体语种编码转换为utg-8，存储空间的使用会相应增加，如果使用的是InnoDB字符集的改变可能导致数据大小超过可在页内存储的临界值，需保存在额外的外部存储区、严重空间浪费、带来空间碎片

7.10全文索引
通过关键字匹配进行查询过滤，基于相似度的查询

有自己独特的语法，没有索引可以工作，有了索引效率更高，全局搜索的索引有独特的结构；全文索引支持各种字符内容的搜索，也支持自然语言搜索和布尔搜索

主讲MyISAM的全文索引：

作用对象时全文集合，具体的：对表某一条记录，mysql会将需要索引的列全部拼接成一个字符串，进行索引

myisam是一类特殊的b-tree索引：第一层all关键字，对应于每个关键字的第二层（一组相关的文档指针）

不会索引文档对象中的all词语，据如下规则过滤一些词语：

停用词列表中的词不会被索引，默认停用词据通用英语的使用来设置，可使用参数ft_stopword_file指定外部文件来使用自定义的停用词
对应长度大于ft_min_word_len的词语和长度小于ft_max_word_len的词语，不会被索引？反了吧

7.10.1自然语言的全文索引
计算每个文档对象和查询的相关度（基于匹配的关键词个数、次数 反比）

自动按照相似度进行排序，无法使用索引排序，不想使用文件排序的话，不要在查询中使用order by子句

match指定的列必须和全文索引中指定的列一样，否则无法使用全文索引：不会记录关键字来自哪一列，无法使用全文索引查询某个关键词是否在某列中存在

7.10.2布尔全文索引
布尔搜索中，用户可在查询中自定义某个被搜索的词语的相关性

通过停用词列表过滤掉“噪声”词，还要求搜索关键词长度>ft_min_word_len,<ft_max_word_len,返回的结果未经排序

使用()分组、构造复制搜索查询

7.10.3mysql5.1中全文索引变化
新增插件式解析、性能提升：定制增强搜索功能

7.10.4全文索引的限制和替代方案
只有一种判断相关性的方法：词频

全文索引只有全部在内存中，性能才非常好

可实现简单边框搜索：范围值

缓存全文索引返回的主键值：分页显示常用

7.10.5全文索引配置和优化
日常维护：optimize table减少碎片，IO密集型、定期全文索引重建

保证索引缓存足够大、all全文索引都 缓存在内存中，可设置单独的键缓存

提供一个好的停用词表很重要，默认的对英语不错，其他语言不合适

忽略一些太短的单词，最小长度可通过ft_min_word_len配置、optimize table重建索引使其生效

向有全文索引表中导入大量数据时，先通过disable keys禁用全文索引，导入将结束用enable keys建立全文索引

7.11分布式XA事务
分布式事务让存储引擎级别的ACID扩展到数据库层面，甚至多个库间，MySQL5.0及后支持XA事务

需要事务协调器保证all事务参与者都完成了准备工作、都准备好、all事务可以提交，mysql此过程中是一个参与者

两种xa事务：

MySQL可参与到外部的分布式事务中，还可以同xa协调存储引擎和二进制日志

7.11.1内部XA事务
mysql本身的插件式架构导致在其内部需要使用xa事务，各存储引擎完全独立，跨引擎需要xa协助

7.11.2外部XA事务
影响因素多、尽量不用

xa事务是多个服务器间同步数据的方法，如果不能使用mysql本身复制或性能不是瓶颈时，可以尝试使用xa

7.12查询缓存
mysql查询缓存保存查询返回的完整结果，当查询命中缓存，立刻返回结果

应该默认关闭查询缓存，如果查询缓存很有用，可配置小的查询缓存空间

7.12.1mysql如何判断缓存命中
缓存存在一个引用表中，通过哈希值引用

这个哈希值包含：查询本身、当前要查询是数据库、客户端协议的版本等一些其他可能影响返回结果的信息

判断时直接使用mysql语句和客户端发送过来的原始信息，如何不同都导致不命中

 使用统一编码规则

查询语句中一些不确定数据时，不会被缓存，如包含now、current_date的查询

如果查询语句中包含如何不确定函数，那么查询缓存中是不可能找到缓存结果的，mysql只要发现不能被缓存的部分就会禁用这个查询缓存

7.12.2查询缓存如何使用内存
用于查询缓存的内存被分成一个个数据块，数据块是变长的，每块存储了自己的类型、大小和存储数据本身，指向前后数据块的指针

数据库类型：存储查询结果、存储查询和数据表的映射、存储查询文本等

服务器启动时先初始化查询缓存需要的内存：完整空闲块，当需要缓存时，先从大的空间块申请一块用于存储结果（>query_cache_min_res_unit配置）

先锁住空间块在找到合适的块，慢、尽量避免：先选尽可能小的、如果用完仍有数据需要存储，会申请新的数据块，查询完成、申请的空间有剩余、将其释放，mysql自己管理一大块内存，碎片

7.12.3什么情况下查询缓存能发挥作用
消耗大量资源的查询：汇总计算查询、count

复杂的select语句都可以使用查询缓存（设计表上的增删改操作要比查少才行）

查询缓存命中率：qcache_hits/(qcache_hits+com_select)多少合适、欠缺

直观反映：命中写入比率：qcache_hits和qcache_inserts比值>3:1 缓存有效，最好能达到10:1

观察查询缓存的内存实际使用情况、确实是否需要缩小或扩大查询缓存

7.12.4如何配置和维护查询缓存
query_cache_type:

是否打开查询缓存，off on demand：在查询语句中 明确写明sql_cache的语句才放入查询缓存（会话和全局）

query_cache_size：

 总内存空间，单位字节，1024整数倍

query_cache_min_unit：

 查询缓存中分配内存块时最小单位

query_cache_limit：

能够缓存的最大的查询结果，查询结果大于此值不会被缓存

query_cache_wlock_invalidate：

某个数据表被其他连接锁住，是否仍然从查询缓存中返回结果，默认off

减少碎片
内存实际消耗query_cache_size-qcache_free_memory除以qcache_queries_in_cache计算单个查询的平均缓存大小

通过qcache_free_blocks观察碎片：反映查询缓存中空闲块的大小

flush query cache完成碎片整理：all查询缓存重排序，all空闲空间聚集到查询缓存的一块区域上，期间all连接无法访问查询缓存

提高查询缓存的使用率
qcache_lowmem_prunes增加很多：

还有很多空闲块，碎片

分配的查询缓存空间不够大，检查qcache_free_memory查看多少未使用内存

7.12.5innodb和查询缓存
innodb自己的mvcc，和查询缓存交互更复杂，4.0事务处理中查询缓存被禁，4.1及后会控制在事务中是否可使用查询缓存

是否可以访问取决于当前事务ID及对应表上是否有锁：当前事务ID小于该事务ID，则无法访问查询缓存，锁、不能

7.12.6通用查询缓存优化
多个小表代替一个大表对查询缓存有好处
批量写入只需一次缓存失效：不要同时批量写和延迟写
缓存空间太大，过期操作时可能导致服务器僵死：控制大小、禁用
无法在库或表级别控制查询缓存，可通过sql_cache和sql_no_cache控制某个select语句，修改会话级别变量query_cache_type控制
写密集型应用，直接禁用，query_cache_size设为0
对互斥信号量的竞争，有时直接关闭查询缓存对读密集型的应用有好处
如希望部分查询走缓存，设置query_cache_type为demand，在希望缓存的查询中加上SQL_cache

7.12.7查询缓存的替代方案
mysql查询缓存工作的原则：

执行查询最快的方式就是不去执行（这个厉害了）但是查询仍需要发送到服务器端，服务器也需要做点工作

7.13总结
分区表：

粗粒度、简易索引策略，适用于大数据量的过滤场景：在没有合适索引，对其中几个分区进行全表扫描；只有一个分区和索引是热点，且分区和索引都在内存中；单表分区数不要超过150个，且注意某些导致无法做分区过滤的细节

视图：

好几个表的复制查询；使用临时表时无法将where条件下推到个各表，不能使用索引

外键：

外键限制将约束放到mysql中，被看作是确保系统完整性的额外的特性，对应必须维护外键的场景，性能更高

额外复杂性和索引消耗，增加表间交互，导致系统更多的锁和竞争

存储过程：

本身实现了存储过程、触发器、存储函数和事件，通常这些课省网络开销，提示系统性能，但没有其他数据库系统成熟和全面

绑定变量：

只需解析一次，大量重复类型的查询，性能提升；执行计划的缓存和传输使用的二进制协议，使用绑定变量更快；

插件：

c/c++编写的插件最大程度扩展mysql功能

字符集：

字节到字符间的映射，校对规则是指一个字符集的排序方法，很多人使用Latin1或utf-8

如果使用utf-8，在临时表和缓存区要注意：会按每个字符三字节的最大占用空间分配存储空间，可能消耗更多的内存或磁盘空间

让字符集合mysql字符集配置相符

全文索引：

通过帮客户构建和使用sphinx来解决全文索引问题

xa事务：

除非你明白innodb_support_xa的意义，不要改

innodb和二进制日志也是需要使用xa事务来做协调的、保证协调崩溃、数据能一致地恢复

查询缓存：

高并发环境下查询缓存导致系统性能下降；不要设置太大内存且只有在明确收益时才使用

第八章

前言：
解释为mysql服务器创建一个靠谱的配置文件的过程，创建好配置的最快的方法是从理解mysql内核和行为开始，可利用这些知识知道配置mysql，最后将想要配置和当前配置比较，纠正重要且有价值的不同之处；

通常只需把基本项配置正确、更多时间放在schema优化、索引及查询设计上

8.1mysql配置的工作原理
配置机制：mysql从命令行和配置文件获得配置信息

清楚知道配置文件的位置，which mysqld

配置文件分成很多部分：每部分开头是用[]括起来的分段名称，程序读取同名分段

8.1.1语法、作用域、动态性
配置项设置使用小写，单词间下划线或横线，固定格式

作用域：服务器级别（全局作用域）、每个连接（会话）、对象级

  很多会话级跟全局变量相等，默认值，改变会话级变量只影响当前连接

query_cache_siez全局

sort_buffer_size：全局、每个线程可设

join_buffer_size：全局默认，且线程可设，若一查询关联多张表，可为每个关联分配一个关联缓冲

show global variables查看全局设置是否生效

8.1.2设置变量的副作用
从缓存中刷新脏块、小心可在线更改的设置

不能总通过看名称猜测变量的效果

key_bufer_size：一次性为键缓存区分配all指定的空间
table_cache_size：延迟到下次有线程打开表，检查该量，大于缓存中表达数量、把最新打开的表放入缓存，小、删除不常有表
thread_cache_size：下次有连接被关闭生效，连接被关闭，检查缓存是否有空间缓存线程，有、缓存该线程被下次用，无、销毁线程
query_cache_size：启动时一次性分配初始化该内存，修改、立删all缓存的查询，重分到指定大小、初始化
read_buffer_size：有查询需要使用才为该缓存分片内存，一次性分指定大小
read_rnd_buffer_size：有查询需要才为该缓存分配内存，只分配需要的内存
sort_buffer_size：查询需要做排序才为缓存分配指定大小

8.1.3入门
通监控确认生产环境中变量的修改是提高、降低了性能

配置文件中写好注释；配置文件置于版本控制之下；改变配置前，优化查询和schema

8.1.4通基准测试迭代优化
不建议；费要如此、开始配置服务前、开发定制基准测试包

一点点改、更改后运行基准测试、运行足够长时间确认性能是否稳定

8.2什么不该做
不要据一些‘比率’来调优

不要使用调优脚本、互联网搜索如何配置不总是一个好主意

不啊哟相信很流行的内存消耗公式：不可能非常准确把握内存消耗的上限

8.3创建配置文件
默认设置被设计成不使用大量的资源、有超过几MB的数据，需要定制配置

建议：不要使用这些建议作为起点，不要使用操作系统安装包自带的配置文件

大多数配置是最佳配置

决定使用某个存储引擎为默认，显示配置

更好的方法设置缓存池大小

从服务器内存总量开始
减去操作系统内存占用
减去mysql自身需要的内存
减去足够让操作系统缓存InnoDB日志文件的内存（至少二进制日志）
减去其他配置MySQL缓冲和缓存需要的内存：myisam键缓存、查询缓存
除以105%，差不多接近InnoDB管理缓冲池增加的自身管理开销
结果四舍五入，向下取合理的值
了解系统，估计需要多少内存

从比默认值大一点的安全值开始比较好，配置内存缓存池时，谨慎：内部做了什么、参数间如何相互影响、在决定配置

open_files_limit：典型linux系统上设置地尽可能大：否则将会碰到打开文件太多的24号错误

8.3.1检查mysql服务器状态变量
通过show global status输出作为配置输入，通过工作负载自定义配置

看绝对值、值如何随时间改变，为高峰和非高峰时间做几个快照

小结：
8.4配置内存使用
内存消耗：控制内存和不可控内存

配置内存步骤：

确定可使用内存上限
确定每个连接MySQL需要使用多少内存：排序缓冲和临时表
确定操作系统需要多少内存：包其他程序使用的内存
剩下的内存给mysql缓冲

8.4.1mysql可使用多少内存
1、机器上安装了多少物理内存

2、考虑系统或架构的限制：

mysql单进程多线程，受系统限制：32位linux内核通常限制任意进程可使用的内存量在2.5G~2.7G

64位限制：5.0及前版本上，有4G限制，MySQL手册上记载每个变量最大值

3、堆栈大小和其他设置的限制

8.4.2每个连接需要的内存
mysql保持一个连接只需要少量内存，还要求一个基本量的内存来执行任何给定查询；

知道高峰期消耗多少内存：使用许多大的临时表或复杂存储过程的查询、高内存消耗；

观察服务器在真实工作压力下使用了多少内存，可在进程的虚拟内存大小看到

8.4.3为操作系统保留内存
如无虚拟内存正在交换Paging到磁盘：操作系统内存足够

至少保留1G~2G的内存，建议2G或总内存5%为基准：较大者为准，可再增加一些预留

不为操作系统缓存增加任何内存：可能会变得非常大

通常利用all剩下的内存做文件系统缓存，这应从操作系统自身需求分离出来

8.4.4为缓存分配内存
MySQL为缓存分配更多的内存，使用缓存类避免磁盘访问

最重要的缓存：

InnoDB缓冲池
InnoDB日志文件和MyISAM数据的操作系统缓存
MyISAM键缓存
查询缓存
无法手工配置的缓存，例如二进制日志和表定义的操作系统缓存

8.4.5InnoDB缓冲池Buffer Pool
很大缓冲池；预热、关闭都花费时间

无法同时加速关闭和重启两个动作：事先知道何时关闭InnoDB，可在运行时修改innodb_max_dirty_pages_pct变量，将值改小、等刷新线程清理缓冲池，脏页较少时关闭

 默认通过后台线程刷新脏页，合并写入，高效顺序写到磁盘：延迟缓冲池中刷写脏页的操作：直到其他线程必须使用空间才刷写【传送门】

很大的缓存池，重启后服务器也许需要花很长的时间预热缓冲池，特别是磁盘很慢，可用Percona Server加快预热，或重启后立即全表扫描或索引扫描把索引载入缓冲池

8.4.6MyISAM键缓存Key Caches
只缓存索引，大部分是该表、为键缓存分较多内存，分配前了解myisam索引实际上占用多少磁盘空间：设置不要超索引总大小或操作系统保留内存的25%~50%，更小为准

=索引存储占用空间

默认将all索引缓存在默认键缓存中：第一次需访问MYI文件时

1、可创建键缓冲：配置文件添加key_buffer_1.key_buffer_size=1G

2、key_buffer_1缓存t1、t2索引

3、show status 和 show variables监控键缓冲使用情况：

4、缓存区使用率：没什么用

5、每秒缓存未命中次数：key_reads/Uptime

6、每10s获一次状态值的变化量

7、使用操作系统缓存 缓存数据文件

8、不管有没有myisam，需要将key_buffer_size设置为较小的值：mysql服务器内部有时会使用myisam：group by

mysql键缓存块大小
5.1及后可设置myisam索引块的大小（myisam_block_size）和操作系统一样，以避免写时读取

8.4.7线程缓存
存为后面新连接服务的线程：

  新连接创建，缓存有线程删一个并分配给新连接，连接关闭、缓存有空间，线程放回缓存无则毁

查看threads_created，设置thread_cache_size（指定缓存中可存线程数）合适

8.4.8表缓存 Table cache
包含表frm文件的解析结果，其他数据：依赖存储引擎

可重用资源，是服务器存储引擎间分离不彻底的产物

myisam查看关闭是否干净：表索引文件计数器

mysql5.1：

1、打开表的缓存：opened_tables，建删临时表、不停增长

2、表定义缓存：table_definition_cache设置，解析frm文件的结果，从其他资源分离出来：表描述符；全局、all连接共享；可足够高但myisam可使关机time long

无法打开更多文件：可能需要增mysql容许打开文件的数，同my.cnf设open_files_limit

线程和表缓存需要内存不多，有效节约资源，提升效率

8.4.9InnoDB数据字典Data Dictionary
innodb开一张表，增加对象到字典，关闭时不移除；内存泄漏

第一次开表计算统计信息，I/O操作，代价高，可在percona server（5.6中的innodb_analyze_is_persistent）打开innodb_use_sys_stats_table存统计信息到磁盘

启动后，innodb统计可能对服务器和特定查询产生冲击，可关闭innodb_stats_on_metadata避免耗时表统计信息刷新

innodb为每个.ibd使用单个 全局文件描述符（myisam表缓存），设置了innodb_file_per_table则可保持打开.ibd数有限，bestinnodb_open_files足够大使服务器可保持all.ibd文件同时打开
8.5配置mysql的I/O行为
性能和数据安全间的较量

8.5.1InnoDB I/O配置
控制怎么恢复（启动时自动ing）、打开和刷新数据

InnoDB事务日志：
innodb变更任何数据时，会写条变更记录到内存日志缓存区；缓存满时 事务提交时 或每一s innodb都会刷写缓存区内容到磁盘日志文件

日志条目紧凑，不基于页，不会一次存储整页（浪费空间）

日志缓冲写到日志：简单将数据从InnoDB内存缓冲转移到操作系统的缓存（内存中）无持久化存储；

日志刷新：innodb请求操作系统把数据刷出缓存，且确认写到磁盘（慢），阻塞I/O调用;

1、 减少提交事务时开销，日志记录事务、无须每次提交时刷新脏块到磁盘

2、事务修改的数据和索引映射到表空间随机位置：随机I/O(请求时磁头移到正确位置,读出需要的部分 转到开始位置)

3、日志将随机I/O变几乎顺序的日志文件 数据文件I/O，日志写到磁盘=事务持久化，未写到、可重放日志 恢复已提交事务

4、最后后台线程只能将变更环形写到数据文件（日志大小固定）：写到尾部 跳转开头继续 不覆盖未到数据文件的：清掉已提交的记录

5、日志文件大小受控于innodb_log_file_size和innodb_log_files_in_group，默认共10M，对高性能工作 least *百MB；修改大小 ：完全关闭MySQL、旧文件移到其他地方 重配参数 重启 删除旧文件

     大小：权衡正常数据变更开销和崩溃恢复需要的时间，太小 做more检查点 更多日志写 太大崩溃恢复时不得不做大量工作 增加恢复时间

     数据大小 访问模式影响恢复时间 较短的行使更多修改 可放在同样日志中 so可能必须在恢复时重放更多修改操作：innodb条目紧凑 不基于页 不费空间存储整页

6、如有大事务，增加日志缓冲区（默认1MB）可减少I/O，变量innodb_log_buffer_size可控制日志缓冲区大小，不需要设得很大，1~8MB除非要写很多大的blob记录

       较大的缓存区可减少争用区内空间分配

7、检查show innodb status 的log监控日志 日志缓冲区I/O性能， 通过innodb_os_log_written状态变量查看innodb对日志文件写了多少数据

       经验：10~100s间隔的数字 记录峰值 —判断日志缓冲是否设置得正好：日志文件大小应容纳服务器1h活动内容

8、刷新日志缓冲：使用mutex锁住缓冲区 刷新到所需位置 移动剩下条目到缓冲区前 mutex释放时maybe more one 事务准备好刷新日志记录 group commit使一个I/O提交多个事务

   缓冲须刷新到持久化存储 确保提交的事务完全被持久化，如更在乎性能 修改innodb_flush_log_at_trx_commit控制日志缓冲刷新频繁程度

                    0：日志缓冲写到日志文件，每秒钟刷新一次，事务提交不做任何事

                   1：缓冲写到日志文件，每次事务提交都刷新到持久化存储（默认，最安全）保证不丢失任何已提交的事务，除非磁盘或操作系统是“伪”刷新

                     2：每次提交时把日志缓冲不刷新写到日志文件，InnoDB每秒做一次刷新；如mysql挂了，2不丢事务，整个服务挂了、断电了，可能会丢失一些事务

9、最佳配置：设置innodb_flush_log_at_trx_commit=1 且日志文件放到有电池保护的写缓存RAID卷中，（=1降低每s可提交的事务数；否 可能导致丢失事务）如不在意持久性，其他值也可以

InnoDB怎样打开和刷新日志以及数据文件
innodb_flush_method配置innodb如何跟文件系统相互作用：读写 日志、数据文件

windows和非windows操作系统对这个选项的值时互斥的

如果使用类UNIX操作系统且RAID控制器带有电池保护的写缓存，建议使用O_DIRECT，否则默认值或O_DIRECT推荐，具体看应用类型

值范围：

1、fdatasync非windows上的默认值 刷新文件数据

innodb通常用fsync代替fdatasync来刷新数据、日志文件（more I/O）
fdatasync似fsync但只刷新文件的数据，某些场景下导致数据损坏
fsync缺：操作系统在self缓存中缓冲些数据（双重缓冲浪费但如让文件系统do更智能I/O调度 批量操作 好处：both系统写合并执行、预读优化）
innodb_file_per_table导致每个文件独立做fsync：写*表不能合并到一个I/O

2、O_DIRECT：依赖操作系对数据文件做标记或directio函数

不影响日志，部分类unix系统有效（gnu linux FreeBSD Solaris55.0），almost系统用fcntl设置文件描述符的这一标记
fsync刷新文件到磁盘，通知操作系统不缓存数据不预读，读写直接通存储设备
需带有写缓存的RAID卡且设置为Write-Back（写入会在RAID上缓存上缓冲：only stay 好性能）
可能导致服务器预热时间变长，也可能导致小容量的缓冲池比缓存I/O操作慢
不对innodb_file_per_table产生额外损失，当使用该标记不用i_f_p_t，maybe 由于some顺序I/O 性能损失

RAID是一种把多块独立的物理硬盘按不同的方式组合起来形成一个硬盘组（逻辑硬盘），从而提供比单个硬盘更高的存储性能和提供数据备份技术;

3、ALL_O_DIRECT：

 percona server、 mariadb用，服务器打开日志文件时也能用标准mysql打开数据文件的方式O_DIRECT

4、O_DSYNC

日志文件调用open时设置O_SYNC标记，使得all写同步，不影响数据文件

O_SYNC：不禁用操作系统层的缓存，在缓存中写数据 发送到磁盘： 操作系统maybe把“使用同步I/O”标记下传给硬件层：tell设备不要使用缓存、把修改过的缓冲数据刷写到设备上，（如果设备支持 接着传递个指令给设备刷新它自己的缓存）每个write pwrite在函数完成前把数据同步到磁盘 阻塞

不用O_SYNC标记的写入调用fsync容许写操作积累在缓存 一次性刷写alll data

O_SYNC：同时同步数据 元数据 O_DSYNC：同步数据

5、async_unbuffered：

 Windows默认值，让innodb对almost写 使用无缓冲的I/O

 当innodb_flush_log_at_trx_commit=2，对日志文件使用缓冲I/O

 在Windows2000 XP 更新版本对数据读写all用操作系统原生异步（重叠）I/O

6、unbuffered

   Windows有效，与5类似，不使用原生异步I/O

7、normal：

   Windows有效，让innodb不使用原生异步I/O或无缓冲I/O

8、nosync 和littlesync

   开发使用，对生产环境来说不安全，不应该使用

InnoDB表空间
数据保存在表空间内：本质一个由一个或多个磁盘文件组成的虚拟文件系统

innodb用表空间存储表 索引 回滚日志 插入缓冲 双写缓冲 其他内部数据结构

配置表空间

 innodb_data_file_path定制表空间文件：放在innodb_data_home_dir目录下

回收空间方式：导出数据 关闭mysql 删除all文件 修改配置 重启 创新数据文件 导入数据：如果表空间损坏 innodb拒绝启动 （傲娇一把）

innodb_file_per_table让innodb为每张表使用一个文件：在数据字典存储为“表名.ibd”：删除一张表时回收空间简易

打开该选项，仍需为回滚日志 其他系统创建共享表空间（不把all数据存其中：明知 best关闭自动增长）

更差的drop table性能：某些文件系统慢 删除时锁定、扫描缓冲池（可从percona server的一个修复中获益让服务器慢慢清理掉被删表的页面）

建议：使用innodb_file_per_table且给共享表空间设置大小范围

innodb页16kb，即使表只有1kb的数据

行的旧版本和表空间：

只有改变了数据的事务才创建旧版本的行

没有清理的行版本会对all查询尝试影响，使得表和索引更大，清理现场跟不上、性能显著下降，新版本清理过程显著提升了性能且分离出来了、可建多个

双写缓冲：避免页没写完所导致的数据损坏

是表空间特殊保留区域，本质是最近写回的页面的备份拷贝；

容许日志文件更加高效（不必要包含整页，更像页面二进制变量）

每个页面末尾有校验值，不匹配 损坏 恢复时 innodb需读取缓冲页面且验证校验值，值不对、从它原始位置读取该页面

innodb_doublewrite=0关闭

其他的I/O配置项

1、sync_binlog控制mysql怎么刷新二进制日志到磁盘，默认0：不刷新 操作系统决定何时；比0大 指定两次刷新到磁盘的动作间隔多少二进制日子写操作

 不设置1，崩溃后可能导致二进制日志误同步事务数据：导致复制中断，不可能及时恢复，可能比innodb_flush_log_at_trx_commit更损性能

将带有电池保护写缓存的高质量RAID控制器设置为使用写回策略，可支持每秒数千的写入，且保证写到持久化存储，显著提升性能

小结：

innodb I/O配置 内存很多，应该撑得起I/O的分量吧

8.5.2 MyISAM的i/o配置
myisam每次写操作后便把索引变更刷新磁盘

1、使用lock table 延迟写入直到解锁这些表，精确控制哪些写被延迟，何时

2、delay_key_write也可延迟索引写入，修改的键缓冲块直到表被关闭才刷新

 off：每次写操作后刷新键缓冲中的脏块到磁盘，除非lock tables锁定了

 on:打开延迟键写入，只对用delay_key_write选项创建的表有效

 all：all的myisam表都是有延迟写入

缺点：

1、很多写被延迟，可能需要费更长时间关闭表：等待缓冲刷新到磁盘

2、因为上 flush tables需要很长时间

3、键缓冲没有刷回去的脏块可能占用空间，导致从磁盘上读取新块无空间

myisam_recover：控制myisam如何寻找 修复错误

1、配置文件 命令行设置，通过show variables like‘myisam_recover_options’查看

2、只有小的myisam表建议打开：通知mysql表打开时 检查是否损坏 且找到问题时修复

default：尝试修复如何被标记为崩溃或无标记为完全关闭的表

backup：将数据文件的备份写到.BAK文件，以便随后进行检查

force：即使.MYD文件中丢失的数据超过一行，也让恢复继续

quick：除非有删除块，否则跳过恢复

如果有大堆myisam表，启动后用check tables 和repair tables命令来做

打开数据文件的内存映射访问：

内存映射使myisam通过操作系统页面缓存访问.MYD文件，避系统调用开销

通过myisam_use_mmap选项打开内存映射

8.6配置mysql并发

8.6.1InnoDB并发配置
innodb的“线程调度器”控制线程怎么进入内核访问数据，及在内核中一次可做哪些事

1、基本限制并发innodb_thread_concurrency：限制一次性可有多少线程进入内核，0不限制 使用更小的值更好，做实验确定

2、使用线程池

8.6.2myisam并发配置
删除和插入行：

删除不会重整理整个表，行标记为删除，表中留下“空洞”，插入时可能利用这些空间，如果无空洞 插入表尾

myisam可边读取边并发追加新行：新插入数据不可见

设置concurrent_insert配置打开并发插入：否则不支持

0：不容许，all插入对表加锁互斥

1：默认值，表无空洞，容许并发插入

2：mysql5.0，并发插入到表尾，如无线程从表读数据，新行放空洞里，更碎片化

可设置low_priority_updates让insert replace delete update优先级低于select，让select获取好的并发度

8.7基于工作负载的配置
配置服务器的一个目标：将其定制得符合特定的工作负载

熟悉你的服务器：用innotop监控，pt_query_digest创查询报告，创建processlist快照

8.7.1优化blob和text的场景
服务器不能再内存临时表中存储blob值

第9章：

MySQL需要的四种基本资源:cpu、内存、硬盘以及网络资源，倾向于很多快速的cpu

合理做法：不要超过两个插槽；内存方面 经济的服务器内存：18个DIMM槽，单条8GB（会变）

持久化存储选择：SAN、传统硬盘、固态存储设备（以提供性能的次序排序）

需要功能和纯粹容量时，SAN；昂贵、小的随机I/O很大延迟（慢的互联方式、工作集太大）
传统硬盘很大、便宜，随机读慢，best 服务器硬盘组成RAID 10卷，带有电池保护单元的RAID控制器，设置写缓存未writeBack
固态小且昂贵，随机I/O快 ；SSD便宜更慢缺可靠性验证：做RAID提升；PCIe昂贵容量有限，非常快且可靠不需要RAID
操作系统：存储、网络、虚拟内存管理

使用GNU/Linux，采用XFS文件系统，且为服务器页面交换倾向率 硬盘队列调度器设置恰当的值

10、复制

2种方式：通主库记录二进制日志，备库重放日志来异步复制

推荐配置：

1、明确指定二进制日志名字：保证二进制日志名在all服务器上一致 log_bin

2、备库：为中继日志指定绝对路径

11章

构建高可扩展性系统原则：

1、在系统内尽量避免串行化和交互

2、避免不同节点间的交互

2、助规划可扩展性：

  应用的功能完成了多少？预期的最大负载是？某个系统失效会发生什么

数据分片最大挑战：查找和获取数据：如何查找数据取决于如何分片

负载均衡：服务器前端设置一个负载均衡器，将请求路由至最空闲的可用服务器

实现应用所明确需要的，为可能的快速增长做好预先规划

12高可用性

在宕机造成的损失与降低宕机时间所花费的成本间取一个平衡

宕机：
运行环境：裁判空间耗尽

性能：运行糟糕的SQL，服务器bug、错误行为，糟糕的schema 索引设计

复制：主备数据不一致

数据丢失或损坏：DROP TABLE误操作，缺少可用备份

其他：……

实现高可用性：
1、适当配置、监控、规范或安全保障措施避免人为失误

2、系统中造冗余，且具备故障转移能力

提升平均失效时间MTBF
使用innodb并进行适当配置、skip_name_resolve禁止DNS

备库只读，不让复制自启动、定期审查查询语句、归档清理不需要的数据

禁用查询缓存（除非能证明有效）、避免使用复杂特性（复制过滤 触发器）

监控重要组件和功能，尽量记录服务器状态和性能指数、定期检查复制完整性

为文件系统保留一些空间，养成习惯、评估管理系统的改变、状态及性能信息

测试恢复工具和流程（含从备份中恢复数据）、最小权限原则、系统干净 整洁

好的命名和组织约定、谨慎安排升级数据库服务器、升级前使用诸如Percona Toolkit的pt-upgrade类工具检查系统

降低平局恢复时间MTTR
一个能够提供冗余和故障转移能力的系统架构

团队成员是最重要的高可用性资产

14 应用层优化

缓存：
找到正确的粒度和缓存过期策略组合

决定哪些内容适合缓存，缓存在哪里

被动缓存：除了存储和返回数据不做其他事情 memcached

主动缓存：缓存未命中做额外工作，将请求转发给应用的其他办部分生成请求结果，存储并返回 squid

测量：剖析每一层问题；检查web服务器配置和缓存

15备份与恢复

还原：从备份文件获取数据

恢复：当异常发生后对一个系统或其部分的拯救

逻辑备份：导出；物理备份：复制原始文件

先使用物理复制，以此数据启动MySQL服务器实例运行mysqlcheck，周期性使用mysqldump执行逻辑备份

1、备份二进制日志：备份后使用flush logs开始新的二进制日志（只需备份新的二进制日志）

2、不备份没有改变的表，MyISAM记录每个表的最后修改时间，通过查看磁盘上的文件运行show table status查看时间，使用innodb 利用触发器记录最后修改时间

3、不备份无改变的行，某些数据不需要备份，至少一周一次全备份

数据一致性：数据指定时间点一致；文件一致性

innodb每次启动检测数据和日志文件，是否需要恢复过程：据日志文件将事务应用到数据文件，回滚未提交变更

恢复损坏的InnoDB数据：

optimize table修复损坏的二级索引；聚簇索引：innodb_force_recovery导出表；

小结：

无侵入式二进制原始数据备份：

从文件系统或SAN快照中直接复制数据文件；使用percona xtraBackup热备份

备份二进制日志，尽可能久地保存多份备份的数据和二进制文件

16、MySQL用户工具

接口工具
帮助运行查询，创建表和用户，执行其他日常任务等

命令行工具集
Percona Toolkit：日志分析、复制完整性检测、数据同步、模式和索引分析、查询建议和数据归档目的

SQL实用集
common_schema：针对服务器脚本化和管理的代码和视图

开源监控工具
nagios：问题检测和告警系统，文件：周期性检测服务器，将结果与默认 自定义阈值比较 发给联系人 难维护

zabbix：同时支持监控和指标收集的完整系统，数据库；配置简单、灵活、可扩展，图形