从服务器性能优化开始分析数据库优化的点点滴滴

服务器性能优化

1. 性能优化简介
   性能的概念：完成某件任务所需要的时间度量，即响应时间。
   服务器的性能，利用查询语句的相应时间来进行度量，单位是每个查询花费的时间。
   性能优化：在一定的工作负载下，尽可能的降低响应时间。但是无法测量就无法优化，所以我们需要知道，我们应该把测量时间花在什么地方。
   完成一项任务的时间可以分为两部分：执行时间和等待时间。如果要优化执行时间，那么最好是通过测量定位不同的子任务花费的时间，然后优化去掉一些子任务，降低子任务的执行频率或者提升子任务的效率；如果要优化等待时间，会相对复杂一些，因为等待有可能是其他系统间接影响导致的。
   1.1 通过性能剖析（profiling）进行优化
   性能剖析：是测量和分析时间花费在哪里的主要方法。一般有两个步骤：1）测量任务所花费的时间；2）对结果进行统计排序，将重要任务排在前面。
   性能剖析分为两类：
   1）基于执行时间的分析：分析研究什么任务的执行时间最长。
   2）基于等待时间的分析：判断任务在什么地方被阻塞的时间最长。

2. 对应用程序进行性能分析
   1 性能剖析的工具推荐：New Relic（会插入到应用程序中进行性能剖析，将收集到的数据发送到一个基于Web的仪表盘，使用仪表盘更容易利用面向响应时间的方法分析应用性能）；xhprof（测量PHP应用程序）；ifp（测量PHP应用程序，通过使用SHOW PROCESSLIST可以更清楚的知道性能低的查询出自何处）；Percona Toolkit中的pt-query-digest（能够很方便的从查询注释中抽取键值对，所以只需要简单的讲查询记录到Mysql的日志文件中，在对日志文件进行处理即可）

3. 剖析MySQL查询
   1）剖析服务器负载
   MySQL官方版本的慢查询日志，是已经存在的，开销最低，精度最高的测量查询时间的工具，是优化服务器查询的利器。通过设置query_long_time=0来捕获所有的查询。
   Percona Server的慢查询日志，记录了细节更多且更有价值的信息。
   有时因为某些原因如权限的不足，无法在服务器上记录查询。所以出现了两种替代的技术，且都集成到了Percona Toolkit的pt-query-digest中。第一种：通过–processlist选项不断查看SHOW FULL PROCESSLIST的输出，记录查询第一次出现和消失的时间。第二种：通过抓取TCP网络包，然后根据MySQL的C/S通信协议进行解析。可以先通过tcpdump将网络包数据保存到磁盘，然后使用pt-query-digest的–type=tcpdump选项来分析查询。
   **分析查询日志：**从慢查询日志中，利用pt-query-digest工具生成剖析报告来进行分析。分析剖析报告，可以知道哪个查询需要进行优化。另外剖析报告后面包含了各种查询的详细报告。确定需要优化的查询以后，可以利用这个报告迅速的检查查询的执行情况。
   2）剖析单条查询
   在定位到需要优化的单条查询后，确认为什么会花费这么长的时间执行，以及需要如何优化。
   使用SHOW PROCESSLIST：（在mysql命令行输入）当一条查询语句提交给服务器时，此工具会记录剖析信息到一张临时表，并且给查询赋予一个从1开始的整数标识符。剖析报告将会给查询执行的每个步骤以及花费的时间。
   使用SHOW STATUS：该命令返回一些计数器，既有服务器级别的全局计数器，也有基于某个链接的会话级别的计数器。主要用于显示某些活动的频繁程度，但无法给出消耗了多少时间。
   使用慢查询日志：慢查询日志中详细的记录了包括SHOW PROFILE和SHOW STATUS所有的输出，并且还有更多的信息
   使用Performance Schem：还未广泛应用

4. 诊断间歇性问题
   间歇性问题，比如系统偶尔停顿或者慢查询，很难诊断。
   1）先确定是单条查询问题还是服务器问题
   使用 SHOW GLOBAL STATUS：来捕获数据，可以通过某些计数器的尖刺或者凹陷来发现，出现问题的地方。
   使用SHOW PROCESSLIST：捕获数据，来观察是否有大量线程处于不正常的状态或者有其他不正常的特征
   使用查询日志：如果要通过查询日志来发现问题，需要开启慢查询日志并在全局级别设置long_query_time=0，并且要确认所有的链接都采用了新的设置。
   2）捕获诊断数据
   一个可靠且实时的触发器：
   触发器是在问题出现时能够捕获数据的基础。利用Percona Toolkit中的pt-stalk来监控服务器，当达到触发条件时能够是收集数据。
   需要收集什么样的数据呢？收集执行时间过程中的数据和手机等待时间过程中的数据。
   一个收集诊断数据的工具：利用Percona Toolkit中的pt-collect可以收集大量的数据。

Schema与数据类型优化

1. 选择优化的数据类型
   数据类型更小的通常更好；简单就好；尽量避免NULL（包含NULL的列，对MySQL来说更难优化，因为NULL的列使得索引、索引统计比较复杂）；
   1）整数类型
   MySQL可以为整数类型指定宽度，如INT(11)，这只规定了在交互过程中用于显示字符的个数。对于存储和计算来说，INT(1)和INT（20）是没有区别的
   2）实数类型
   DECIMAL类型用于存储精确的小数。但与浮点类型相比，在存储相同范围的值时，浮点类型所需要的存储空间更小。因此，推荐使用浮点类型数据。
   3）字符串类型
   VARCHAR：存储可变长度的字符串；比定长更节省空间，需要使用额外1到2个字节记录字符串的长度；使用场景，字符串最大长度比平均长度大很多；列的更新操作较少，因此会产生较少的碎片问题；
   CHAR：定长，会根据需要采用空格进行填充以方便比较。不容易产生碎片。
   BLOB：为存储很大的数据而设计的字符串数据类型。采用二进制存储，无排序规则和字符集。
   TEXT：为存储很大的数据而设计的字符串数据类型。采用字符存储，有排序规则和字符集。
   使用枚举代替字符串类型：
   4）日期和时间类型
   DATETIME：与时区无关， YYYY-MM-DD-HH-MM-SS，使用8个字节存储
   TIMESTAMP：保存了从1970.1.1.00以来的秒数，使用4个字节存储。推荐使用。
   5）位数据类型
   BIT：在一列中存储一个或者多个true/false值。
   SET：当需要保存很多true/false值，考虑合并这些列到一个SET数据类型。
   6）选择标识符
   为标识列选择合适的数据类型很重要。整数类型是标识列最好的选择，因为他们很快并且可以用AUTO_INCREAMENT。

2. MySQL设计中的陷阱
   太多的列；太多的关联；全能的枚举；变相的枚举；

3. 范式和反范式
   1）范式的优缺点
   范式化的更新操作比反范式化快；
   当数据较好的范式化时，就只要很少或者没有重复数据，所以只需要修改更少的数据；
   范式化的表通常更小，可以更好的放在内存中，执行操作更快；
   很少有多余的数据意味着更少需要使用DISTINCT和GROUP BY语句；
   缺点是：通常需要关联，稍微复杂的查询语句，需要进行多次关联；
   2）反范式的优缺点
   可以很好的避免关联；
   3）混用范式化和反范式

4. 缓存表和汇总表
   有时提升性能的最好方式是在同一张表中保存衍生的冗余数据。有时也需要创建完全独立的汇总表或者缓存表。
   缓存表：可以比较简单的从其他表中获取数据的表。对于优化搜索和检索查询语句非常有效。
   汇总表：保存的是使用GROUP BY语句聚合数据的表。例如：在一个很繁忙的网站中不可能维护一个实时精确地计数器，作为替代方案，可以每小时生成一张汇总表。
   1）物化视图
   物化视图实际上是预先计算并且存储在磁盘上的表，可以通过各种策略刷新和更新。
   2）计数器表

5. 加快ALTER TABLE操作的速度
   大部分ALTER TABLE操作将终端MySQL服务。所以常用技巧介绍如下：
   先在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
   影子拷贝：用要求的表结构创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表。
   1） 只修改.frm文件
   为想要的表结构创建一个新的.frm文件，然后用它替换掉已经存在的那张表的.frm文件。
   2）快速创建MyISAM索引
   先禁用索引，载入数据，然后重新启动索引。

索引优化

索引优化是对查询性能优化的最有效的手段。

1. 索引基础
   1） 索引类型
   索引是存储在引擎层而不是服务器层的。
   B-Tree索引：使用B-Tree来存储数据，但是不同的底层存储引擎可能使用不同的存储结构。例如：NDB集群中使用了T-Tree结构存储这种索引；InnoDB则使用B+Tree；B-Tree索引能够加快访问数据的速度（二叉树的查找时间为O（nlogn））；B-Tree树对索引列是顺序组织存储的，所以很适合查找范围数据；
   可以使用B-Tree索引的查询类型：全职匹配；匹配最左前缀；匹配最左前缀；匹配范围值；精确匹配某一列并范围匹配另一列；只访问索引的查询；
   B-Tree索引的限制：如果不是按照索引的最左列开始查找，则无法使用索引；不能跳过索引中的列；如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找；
   哈希索引：基于哈希表实现，只有精确匹配索引所有的列查询才有效。在MySQL只有Memory存储引擎支持哈希索引；哈希索引查找非常快；
   哈希索引限制：哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行；哈希索引中的哈希值不是按照顺序排列的，所以无法用于排序；哈希索引，不支持部分索引列匹配查找；哈希索引只支持等值查询；访问哈希索引的数据非常快，除非有哈西冲突；哈希冲突时，一些索引维护操作的代价也会很高；
   空间数据索引（R-Tree）：
   全文索引：

2. 索引的优点
   索引大大的减少了服务器需要扫描的数据量；索引可以帮助服务器避免排序和临时表；所以把随机I/O变成顺序I/O；

3. 高性能的索引策略
   1）独立的列：列不能是函数的参数，也不能是表达式的一部分；
   2）前缀索引和索引选择性：前缀应该足够长，以使得前缀索引的选择性接近于索引整个列；前缀索引是使得索引更小、更快的有效方法；但其缺点是，MySQL无法使用前缀索引做ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描；
   3）多列索引：能够使得扫描现在各自的单列上进行扫描，然后将扫描得到的结果进行合并。这个算法有三个变种：OR条件的联合（union），AND条件的相交（intersection），组合前两种情况的联合及相交；
   4）选择合适的索引顺序：当不需要考虑排序和分组时，将选择性最高的列放到索引最前列是一个很好的选择；根据那些运行频率最高的查询来调整索引列的顺序，让索引的选择性更高；
   5）聚簇索引：当表有聚簇索引时，他的数据行实际存放在索引的叶子结点中。在InnoDB中通过主键聚簇数据。聚簇索引可以把相关数据保存在一起；使得数据访问更快；使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
   6）覆盖索引：一个索引包含（或者说覆盖）所有需要查询的字段的值；可以使得查询只需要扫描索引，而无须回表。好处如下：索引条目通常小于数据行大小，所以只需要读取索引，那么MySQL就会极大地减少数据访问量；因为索引是按照列值顺序存储的，所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少的多；由于InnoDB的聚簇索引，覆盖索引对InnoDB特别有用。
   7）使用索引扫描来做排序：MySQL可以通过两种方式来生成有序的结果：排序操作和按索引顺序扫描。
   8）压缩（前缀压缩）索引：MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引放入内存。
   9）避免冗余和重复索引
   10）避免未使用的索引
   11）索引和锁：索引可以让查询锁定更少的行

4. 维护索引和表
   即使创建了表并加上了合适的索引，还需要对表进行维护。维护表的目的：找到并修复损坏的表；维护准确的索引统计信息，减少碎片；
   1）找到并修复损坏的表
   当损坏的是索引，那么使用CHECK TABLE来检查是否发生了表损坏，使用REPAIR TABLE来修复损坏的表；当损坏的是InnoDB引擎中的表，那么使用innodb_force_recovery参数进入InnoDB强制恢复模式来修复数据。
   2）更新索引统计信息：MySQL查询优化器更具两个API来了解存储引擎的索引值的分布信息，已决定如何使用索引。第一个API是record_in_range()，通过向存储引擎传入两个边界值获取在这个范围内大概有多少条记录。第二个API是info()，该接口返回各种类型的数据，包括索引的基数。MySQL优化器使用的是基于成本的模型，而衡量成本主要的指标就是一个查询需要扫描多少行。
   3）减少索引和数据的碎片：
   行碎片（数据行被存储为多个地方的多个片段中）；行间碎片（逻辑上顺序的页，或者行在磁盘上不是顺序存储的）；剩余空间碎片（数据页中有大量的空余空间）。通过OPTIMIZE TABLE或者导出在导入的方式可以重新整理数据。

查询性能优化

查询优化、索引优化、库表结构优化需要齐头并进。

1. 为什么查询速度会慢
   评价查询性能的好坏，我们应该从相应时间出发。对于查询操作来说，是由多个子查询组成的，因此优化查询，应该从优化子查询入手。而MySQL的一个查询操作中存在哪一些子查询呢，我们可以通过在“服务器性能剖析”中介绍的工具来进行查看。优化查询的目的就是减少和消除一些不必要的额外操作，或者某些被额外重复了很多次的操作，或者某次执行的太慢的操作。
2. 慢查询的基础：优化数据访问
   1）确定查询只返回需要的数据，避免出现以下几种情况：查询不需要的数据；多表关联时返回全部的列；总是取出全部的列；重复查询相同的数据；
   2）在返回需要数据的前提下，选择查询开销最少的操作。衡量查询开销的三个指标：响应时间，扫描行数，返回行数。当遇到查询需要扫描大量的数据却只返回少量的行时，可以使用如下技巧：使用覆盖扫描，把所有需要的列放到索引中，这样存储引起无须回表获取对应的行就可以返回结果；改变库表结构；重写这个负责查询。
   3）重构查询，来达到一样的查询效果。如下：将一个复杂的查询分解为多个简单的查询；将大查询分解成多个小查询，每个小查询完成一部分的功能；分解关联查询，对每一个表进行一次单表查询，然后将结果在应用层中进行关联（好处：让缓存的效率更高；将查询分解后，执行单个查询可以减少锁的竞争；在应用层做关联，可以更容易对数据库进行拆分，高性能和可扩展；提升查询本身的效率）

MySQL高级特性

1. 分区表