最近读了《高性能MySQL》这本书的前7章，系统的学习了如何更好的使用MySQL，优化SQL性能。

MySQL最重要的是它的存储引擎架构，这种架构设计将查询处理及其它系统任务和数据的存储/提取分离。这种处理和存储分离的设计可以在使用时根据性能、特性来选择合适的数据存储方式。

MySQL的架构与其他数据库相比有点与众不同，可以应用于多种不同的场景，包括Web应用、嵌入到应用程序，还可以支持数据数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统（OLTP）。

首先是MySQL服务器的逻辑架构，可分为3层。最上层负责接收客户端的连接、认证和安全。第二层主要是MySQL的核心服务功能。包括查询解析、缓存、优化以及所有的内置函数。所有跨存储引擎的功能都在这一层实现，包括存储过程、触发器、视图。第三层是存储引擎，存储引擎负责MySQL数据的存储和提取，服务器通过通用接口与存储引擎通信。

在架构的第一层中，每个客户端都会在服务器进程中拥有一个线程，当客户端连接到服务器时，服务器对客户端进行认证。认证是基于用户名、密码和主机信息。如果使用了SSL连接，还可以使用X.509证书认证。认证成功后服务器会验证客户端是否具有权限执行特定的查询。

SQL的优化要从三个角度优化，首先数据库表的定义以及数据类型的选择，其次的是索引的定义，还有查询语句的定义。

数据类型的选择

数据类型的选择有几个原则：

1. 使用最小的数据类型。数据类型越小，占用的磁盘、内存和CPU缓存就更少，CPU的处理速度也就越快。
2. 选择简单的数据类型。比如整型比字符的操作代价更低，因为字符集和校对规则使字符比较比整型更复杂。
3. 尽量避免使用NULL，而是使用默认值。因为可为NULL的列使索引、索引统计和值比较都变得更加复杂，所以MySQL更难对它进行优化

mysql的基本数据类型从大的角度上讲有：整数、实数、字符串、日期和时间、位数据类型和枚举类型。

1. 整数类型有5个：tinyint, smallint, mediumint, int, bigint，占用的存储空间分别是1, 2, 3, 4, 8个字节。整数类型有可选的unsigned属性，表示不允许负值，这可能是正数的上限翻倍。所以在确定不会出现负数的场景下可以使用unsigned。在定义整数时可以定义宽度，不过这只是规定了交互工具显示字符的位数，并不会限制值的范围。
2. 实数类型是有小数部分的数字。实数类型有3个：float, double, decimal。float和double分别占用4字节和8字节，float和double使用的是近似计算，也就是说如果需要判断相等操作，则需要使用大于或小于进行比较。decimal支持精确计算，定义decimal时要指定整数部分和小数部分的长度。MySQL使用二进制格式存储decimal值。它将9位数字包装成4个字节。对于每个部分，需要4个字节来存储9位数的每个倍数。如decimal(18,9)表示可以存储18个数字，最多存储小数点后9个数字，前后各占4个字节，小数点占1字节。decimal默认为decimal(10,0)。decimal最多存储65个数字。浮点类型在存储想用范围的值时，比decimal占用的空间更少，所以应该只在必要的时候才使用decimal。不过还有另外一种代替decimal的方式是讲浮点数乘以相应的倍数保存为bigint，这样可以同时解决浮点数计算不精确和decimal计算代价高的问题。
3. 字符串类型最主要的是varchar和char。不同存储引擎存储字符串的方式不同。在innodb中，varchar用于存储可变长字符串，这种存储方式比char更节省空间。varchar需要使用1到2个字节记录字符串的长度。如果列的最大长小于等于255，则使用1个字节，大于255的话使用2个字节。由于varchar是变长的，所以在做update的时候可能使行的数据长度比原来长，导致innodb需要分裂页。如果1. 字符串列的最大长度比平均长度大很多;2.列的更新很少，所以碎片和页分裂很少; 3.使用utf8字符集，每个字符的字节数都不同，这几种情况都可以考虑使用varchar。在使用varchar时要定义合适的长度，太长了会导致更多的资源开销，例如在排序时，mysql通常会分配固定大小的内存块，这就导致内存的占用很大。char类型是定长的，所以不管实际字符串多长，mysql总是分配定义的长度，如果小于定义的长度会采用空格填充。另外还有binary和varbinary，他们存储的是二进制，填充的时候使用的是0。二进制字符串比较的时候是根据字节对应的数字比较，所以更快。
4. 字符串类型还有blob和text类型，它们是为存储很大的数据而设计的。它们分别都有5种类型：tinytext, smalltext, text, mediumtext, longtext和tinyblob, smallblob, blob, mediumblob, longblob。blob和text的唯一区别是blog存储的是二进制数据，没有排序规则和字符集，而text类型有字符集和排序规则。mysql对blob和text排序时只对max_sort_length字节排序而不是整个字符串。在对blob和text排序时，因为memory引擎不支持blob和text类型，所以如果查询使用了这两种类型，mysql只能使用myisam磁盘临时表，对磁盘的占用很大。
5. 枚举类型有时候可以用来代替字符串类型，mysql存储枚举类型时存储的是数字。但枚举类型的缺点是枚举元素是固定的，修改枚举元素需要alter table，而且添加枚举元素的时候只能在追加，不能重新排序。
6. 日期和时间类型有5种：date, time, datetime, timestamp, year。平时最常用的就是datetime和timestamp，它们都可以用来存储日期+时间。datetime的范围比timestamp大，可以保存1001年到9999年，精度为秒，它的保存格式就是把年月日时分秒保存为14位整数存储，占用8个字节，不能保存时区信息。timestamp只能保存1970年到2038年，它保存的是unix时间戳，只占用4个字节，并且timestamp因为保存的是unix时间戳，所以保存了时区信息。所以不同时区的服务器查询出的时间是不同的。因为timestamp的存储空间更低，所以一般都是使用timestamp。
7. 位数据类型没用过。

数据库表的定义

除了数据类型，在定义表时还要注意主键的数据类型，一般来说整数作为自增主键最好了，因为字符串的编码使对字符串的处理更慢。还有就是要尽量避免使用随机字符串，例如uuid，md5作为唯一主键，因为在mysql中主键索引是聚簇索引，随机字符串会导致记录会随机的插入到任何一页，会导致页分裂以及磁盘随机访问，而且还会产生索引碎片，这都导致insert的速度变慢。

在设计数据库表的时候要避免一些不好的设计：

• 不要定义太多的列。mysql查询数据时需要将数据从存储引擎层拷贝到服务器层并进行解码。这个操作的代价依赖于列的数量，所以列的数量越多，可能导致查询的速度越慢。
• 不要使用太多的关联。太多的关联会导致优化器对查询进行优化的代价大大增大，而且优化的结果也可能不好。

索引优化

索引是存储引擎快速找到记录的数据结构。

索引的类型有：B-Tree索引、哈希索引、空间数据索引（R-Tree索引）、全文索引。

B树索引在不同的存储引擎下有不同的数据结构。innodb使用的是b+树。不同的存储方式性能也各不相同，myisam使用前缀压缩技术使得索引更小，innodb则按照原数据格式进行存储。myisam索引通过数据的物理位引用被索引的行，而innodb则根据主键引用被索引的行。b树索引的值都是按顺序存储的，每个叶子页到根的距离相同。B树索引适用于全键值、键值范围和最左键前缀查找，包括全值匹配、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配某一列并范围匹配另外一列、只访问索引的查询（覆盖索引）。B树索引还可以用于查询中的order by操作。

哈希索引基于哈希表实现，只有精确匹配所有列的查询才有效。哈希索引值存储对应的哈希值，所以索引接口十分紧凑，查询速度很快。但是哈希索引的能力非常有限，例如它不能用于排序，不支持部分索引列匹配查询，不支持范围查询，还有如果哈希冲突很多，维护索引的代价会变高以及查询性能也会变低。innodb引擎有个特性叫“自适应哈希索引（adaptive hash index）”，当innodb发现某些索引值使用得很频繁，它会在内存中基于b-tree索引之上再创建一个哈希索引。

如果存储引擎不支持哈希索引，可以像innodb一样模拟创建自定义的哈希索引。例如如果要存储大量的url并需要根据url进行查找。如果使用B树索引来存储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');这样做就会大大减小索引的存储页。这样做的缺点就是需要使用触发器维护url_crc列的值：create trigger demo_url_crc before insert or update on demo for each row begin set NEW.url_crc=crc32(NWE.url); end;。使用这样方式时不要使用sha1()和md5()作为哈希函数，因为这两个函数计算的哈希值很长，会浪费大量空间。

空间索引。myisam表支持空间索引，可以用作地理数据存储。

聚簇索引和非聚簇索引

• 聚簇索引：表数据按照索引的顺序来存储的，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚簇索引，叶子结点即存储了真实的数据行，不再有另外单独的数据页。 在一张表上最多只能创建一个聚簇索引，因为真实数据的物理顺序只能有一种。
• 非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚集索引，叶结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数据量一致。

索引，可以大大减少服务器需要扫描的数据量；可以帮助服务器避免排序和临时表；可以将随机io编变成顺序io。

高性能的索引策略：

• 索引列不能是表达式的一部分，也不能是函数的参数。
• 索引字符串的前几个字符串，可以节约索引空间，提高索引效率。
• 选择组合索引，选择合适的索引顺序
• 使用覆盖索引。覆盖索引可以避免回表。有许多优点：1.索引条目比数据行小很多，如果只读取索引数据，可以极大减少数据访问量；2.因为索引是树型存储的，所以对于i/o密集型的范围查询，会比随机从磁盘读取每行数据的io要快的多。
• 使用聚簇索引。

innodb的二级索引的叶子节点中存储的不是“行指针”，而是主键值。这样的策略减少了当出现行移动或数据页分裂时二级索引的维护。innodb的主键是聚簇索引，所以如果将主键设置成自增列，那么在插入数据时就可以保证数据行是顺序写入，避免了随机IO，页分裂以及页缓存的清除，还避免了页碎片，可以大幅提高数据插入的性能。不过，自增主键可能会导致auto_increment锁机制成为热点。

• 使用索引扫描来做排序。mysql有两种方式生成有序的结果：1.通过排序操作；2.按索引顺序扫描。如果执行计划的type列的值是index，说明mysql使用了索引扫描来排序。只有当索引的列顺序和order by的顺序完全一样，并且所有列的排序方向都一样时，mysql才能使用索引来对结果做排序。如果是关联查询，只有order by的字段全是第一张表时才能使用作引排序。
• 压缩索引。myisam使用前缀压缩来减少索引的大小，从而让更多的索引可以放到内存中，它的缺点是进行查找、倒序排列操作的时候可能会更慢，需要消耗更多的cpu资源。所以对cpu密集型应用不是很适用。
• 避免重复索引。在相同列创建多个索引是没有意义的，有了组合索引后就没有必要创建它的前缀索引。
• 正确的索引可以减少锁的争用。innodb只有在访问行的时候服务器层才会对其加锁，而索引能够减少行的访问数量。

查询性能优化

sql查询是由一系列子任务组成的，优化查询就是要么消除子任务或者减少子任务执行的次数，或者让子任务执行的更快。通常来说，查询的生命周期包括：客户端发出请求到服务器，服务器解析查询，服务器生成执行计划，执行查询，返回结果给客户端。其中执行又包括调用存储引擎的接口、数据处理、排序和分组。执行查询的时候需要花费时间的地方有：网络、cpu计算、生成统计信息和执行计划、锁等待，系统调用、线程上下文切换等。

性能优化的前提是能够估算查询的时间是否合理。可以用“快速上限估计法”来估算查询的响应时间。大概意思就是先了解查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机IO，再用IO时间乘以具体硬件条件下下一次IO的消耗时间，最后把这些消耗相加，通过这种方式来判断当前响应时间是否合理。

执行计划中的type列反应了访问类型，访问类型速度从慢到快有：全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用。

• 首先检查是否向服务器请求了不需要的记录、不需要的列
• 然后服务器是否扫描了额外的记录。扫描行数和返回行树的比率一般在1:1和10:1之间
• 使用mysql时尽量使用多个简单的查询，不要使用过于复杂的查询。第1个原因是在其它数据库中，总是强调需要数据库层完成尽可能多的工作，这样做的原因是以前总是认为网络通信、查询解析和优化的代价很高，但是mysql的连接和断开连接很轻量，在返回一个小的查询结果方面很高效。第2个原因是将关联查询分解为多个简单查询，可以充分利用缓存，使查询效率更高。还有可能减少关联时的冗余记录的查询。另外在应用层做关联，更利于应用和数据库的扩展。

mysql执行查询的过程：

• 客户端发送查询到服务器
• 服务器先检查缓存，如果命中缓存则直接返回缓存的结果
• 如果没有命中缓存则进行sql解析、预处理然后优化器生成执行计划
• 然后mysql根据执行计划调用存储引擎的接口执行查询
• 最后将结果返回给客户端

mysql客户端和服务器之间的通信协议是半双工的，所以同一时间只能从一端发送数据到另一端。客户端知恩感用一个单独的数据包将查询传给服务器，当查询语句很长长过了max_allowed_packet的长度时，服务器会拒绝接受数据并返回错误。

mysql的查询状态有以下7种：

• sleep：线程正在等待客户端发送请求
• query：线程正在执行查询或正在返回结果给客户端
• locked：线程在mysql的服务器层等待表锁。存储引擎级别的锁不会体现在线程的状态中
• analyzing and statistics：线程正在收集统计信息生成执行计划
• copying to tmp table [on disk]：线程正在将结果复制到临时表，这时线程可能在做group by操作或文件排序。如果有on disk，表示正在将临时表放到磁盘上
• sorting result：线程正在对结果集排序
• sending data：线程可能在多个状态之间传送数据；或正在生成结果集；或在向客户端返回数据

mysql服务器对查询的优化

如果查询语句没有命中缓存，mysql就要对sql进行解析（解析语法是否正确）、预处理（验证权限）、优化执行计划（基于查询优化器）。

mysql的查询优化器是基于成本的优化器，它会尝试预测一个成本最低的执行计划，成本的最小单位是随机读取一个4K数据页的成本。但是查询优化器不一定能够找到成本最低的执行计划，这个有多个原因

1. 查询优化器依赖mysql存储引擎的统计信息来评估成本，像表或索引的页面个数、索引的分布情况、索引和数据行的长度，但是存储引擎的统计信息有时是不准确的；
2. 执行计划估算的成本不等于实际成本。因为mysql成本的最小单位是随机读取4K数据页的成本，但是有的执行计划它读取的页面是顺序读；
3. 优化器估算的时候没有考虑到缓存；

优化器的优化策略可以分为两种，一种是静态优化，可以对解析树直接分析完成优化；一种是动态优化，依赖于查询条件具体的值以及统计信息。相同的语句，静态优化只需要做一次，而动态优化每次都要做。

mysql能够处理的优化有：

• 重新定义表的关联顺序
• 使用等价变换
• 优化count()、min()、max()。例如mysql可以通过获取索引的第一条记录或者最后一条记录来直接获取最大、最小值
• 预估能转换为常数表达式
• 优化子查询，减少多个查询多次访问数据
• 提前终止查询
• 等值传播
• 优化in()查询

排序和分页查询

• 对于选择性很低的列，可以增加特殊的索引来做排序。例如可以创建(sex, rating)索引用于查询：select * from profiles where sex=’M’ order by rating limit 10;
• 无论如何优化，当limit的偏移量很大的时候，查询都会有很大的性能问题，因为随着偏移量增加，mysql需要花费大量的时间来扫描需要丢弃的数据。可以限制用户能够翻页的数量。还可以使用延迟关联的方式，通过使用覆盖索引查询只返回需要的主键，在根据这些主键关联原表获得需要的行。
• 记录上次查询的id，下次直接从记录的id开始扫描: select * from profiles where sex='M' and id > 10 order by rating limit 10;

优化特定类型的查询

• count()的时候如果不需要特别精确，可以使用执行计划来获取近似值，还可以去掉复杂的条件查询提升查询性能，尝试删除distinct约束来避免文件排序。还可以使用汇总表、覆盖索引来避免全表扫描。
• 优化关联查询：确保on关键字的列上有索引，而且要注意表的关联顺序。确保group by， order by中只设计到一个表的列。
• 优化group by和distinct。在mysql中无法使用索引时，group by使用两种策略来完成，一种是使用临时表或文件排序做分组。可以使用hint：sql_big_result和sql_small_result来让优化器按照希望的方式运行。关联查询使用group by时使用表的标示列grou by效率更高。group by的时候如果不关心排序，可以使用order by null来避免文件排序。
• 使用用户自定义变量的缺点：无法使用查询缓存；表名、列名limit自居不能使用变量；如果时连接池或是持久化连接可能会有bug；使用未定义的变量不会产生任何语法错误
• 使用分区表

MySQL hint

• high priority, low priority。high priority可以同于select, insert; low priority可以用于select, insert, update,delete。只对表锁的存储引擎有效，不要在innodb这种有细粒度锁机制和并发控制的存储引擎中使用。
• delayed。对insert和replace有效。使用delayed后服务器会将插入的数据放入到缓冲区，并立即返回给客户端，然后在表空闲时批量将数据写入。可以使用在日志系统中，或者是需要大量写入但不需要等待结果的系统。
• straight_join。放在select之后表示所有表按语句出现的顺序进行关联。
• sql_small_result, sql_big_result用于select，告诉优化器对group by/distinct查询如果使用临时表及排序。sql_small_result表示结果集会很小，可以将结果集放到内存中的索引临时表做排序操作。sql_big_result表示结果集会很大，建议使用磁盘临时表做排序。
• sql_buffer_result。告诉优化器将查询结果放到临时表，然后尽可能快的释放表锁。
• sql_cache, sql_no_cache
• sql_calc_found_rows
• for update, lock in share mode
• use index, force index, ignore index
• 控制优化器行为的参数：
• optimizer_search_depth:穷举执行计划的限度
• optimizer_prune_level：是否跳过某些执行计划
• optimizer_switch：开启/关闭优化器的某些特性

MySQL其它特性

分区表

分区表是一个独立的逻辑表是由底层的多个物理子表组成。MySQL在查询分区表的时候，优化器会根据分区表的分区定义过滤分区，这样查询只需要扫描有数据的分区。一个表最多有1024个分区。

操作分区表时，分区层先打开并锁住所有的底层表，再确定操作的是哪个分区，然后再操作对应的分区。如果存储引擎能够自己实现行级锁，例如InnoDB，则会在分区层释放对应表锁。

MySQL支持的分区类型包括：范围分区、键值分区、哈希分区、列表分区、列分区（range columns）

分区表的问题：

1. NULL值会使分区过滤无效。
2. 如果定义的索引列和分区列不匹配，会导致查询无法进行分区过滤
3. 选择分区的成本可能很高。随着分区数的增长，选择哪个分区的成本会越来越高
4. 打开并锁住所有底层表的成本可能很高。
5. 维护（修改）分区的成本可能很高

视图

MySQL优化视图的两种算法：合并算法和临时表算法。

合并算法指服务器将视图SQL和查询SQL进行合并，服务器查询时是基于底层表查询的，这种方式服务器优化器可以更好的进行优化。

临时表算法指服务器先创建临时表（temporary table）然后基于临时表查询。

如果视图中包含group by, distinct, 聚合函数, union, 子查询等，只要不能在原表记录和视图记录中建立一一对应的场景，MySQL都将使用临时表算法来实现视图。具体采用的是哪种算法可以通过查看执行计划，如果select type为derived（派生）说明这个视图采用的是临时表算法。如果底层的临时表很大，那么执行explain可能会很慢，因为在5.5之前的版本中explain是需要实际执行生成临时表。

MySQL还不支持物化视图，物化视图是指将视图结果数据存放在一个可以查看的表中。

存储过程

优点：

1. 在服务器内部执行，节省带宽和网络延迟
2. 服务器可以缓存存储过程的执行计划，降低消耗
3. 提升安全性，可以通过存储过程访问没有权限的表

缺点：

1. 不好调试、难编写
2. 增加应用的复杂性

查询缓存

缺点：效率低；缓存可能成为高配置服务器的资源竞争点，在多核服务器上还可能导致服务器僵死。