Mysql优化--官方文档

Mysql优化–官方文档

https://dev.mysql.com/doc/refman/5.7/en/optimize-overview.html
数据库性能取决于数据库级别的多个因素，例如表、查询和配置设置。这些软件构造会导致硬件级别的 CPU 和 I/O 操作，您必须最大限度地减少这些操作并使其尽可能高效。当您研究数据库性能时，您首先要学习软件方面的高级规则和指南，并使用挂钟时间测量性能。当您成为专家时，您会更多地了解内部发生的情况，并开始测量 CPU 周期和 I/O 操作等内容。
典型用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。高级用户寻找机会改进 MySQL 软件本身，或开发自己的存储引擎和硬件设备来扩展 MySQL 生态系统。
数据库级别优化
硬件层面的优化
平衡便携性和性能

数据库级别优化

使数据库应用程序快速运行的最重要因素是其基本设计：
表格结构是否正确？特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的列？例如，执行频繁更新的应用程序通常具有许多列数较少的表，而分析大量数据的应用程序通常具有少量列数较多的表。
是否使用了正确的 索引来提高查询效率？
您是否为每个表使用了适当的存储引擎，并利用了您使用的每个存储引擎的优势和特性？特别是，选择诸如事务性存储引擎 InnoDB 或诸如此类的非事务性存储引擎 MyISAM 对于性能和可扩展性可能非常重要。
ps：InnoDB是新表的默认存储引擎。在实践中，高级 InnoDB性能特性意味着 InnoDB表通常优于更简单的MyISAM表，特别是对于繁忙的数据库。
每个表是否使用适当的行格式？此选择还取决于表所使用的存储引擎。特别是，压缩表使用更少的磁盘空间，因此需要更少的磁盘 I/O 来读取和写入数据。压缩适用于所有类型的 InnoDB表工作负载以及只读 MyISAM表。
应用程序是否使用了适当的 锁定策略？例如，在可能的情况下允许共享访问，以便数据库操作可以并发运行，并在适当的时候请求独占访问，以便关键操作获得最高优先级。同样，存储引擎的选择也很重要。存储InnoDB引擎无需您参与即可处理大多数锁定问题，从而实现数据库中更好的并发性并减少代码的实验和调整量。
用于缓存的 所有内存区域的大小是否正确？也就是说，足够大以容纳频繁访问的数据，但又不能大到使物理内存过载并导致分页。要配置的主要内存区域是InnoDB缓冲池、MyISAM键缓存和 MySQL 查询缓存。

硬件层面的优化

随着数据库变得越来越繁忙，任何数据库应用程序最终都会达到硬件限制。DBA 必须评估是否可以调整应用程序或重新配置服务器以避免这些 瓶颈，或者是否需要更多硬件资源。系统瓶颈通常来自以下来源：
磁盘寻道。磁盘找到一条数据需要时间。对于现代磁盘，平均时间通常低于 10 毫秒，因此理论上我们每秒可以执行大约 100 次搜索。使用新磁盘时，时间改善缓慢，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。
磁盘读写。当磁盘处于正确的位置时，我们就需要读取或写入数据。对于现代磁盘，一个磁盘至少可提供 10–20MB/s 的吞吐量。这比查找更容易优化，因为您可以从多个磁盘并行读取。
CPU 周期。当数据在主存中时，我们必须对其进行处理才能得到结果。与内存量相比，拥有较大的表是最常见的限制因素。但对于小表，速度通常不是问题。
内存带宽。当 CPU 需要的数据量超出了 CPU 缓存的容量时，主存带宽就会成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。

SQL语句优化–Select（官方文档）

除了SELECT语句之外，查询的优化还适用于语句中的 CREATE TABLE…AS SELECT、 INSERT INTO…SELECT和WHERE子句等 结构。这些语句将写入操作与面向读取的查询操作结合在一起。
1、在where条件上添加索引
对于使用联接和外键等功能引用不同表的查询尤其重要，可以使用该EXPLAIN语句来确定哪些索引用于 SELECT
2、最大限度地减少查询中 全表扫描 的次数 ，尤其是对于大表。
3、隔离并调整查询的任何部分，例如需要过多时间的函数调用。根据查询的结构方式，可以为结果集中的每一行调用一次函数，甚至可以为表中的每一行调用一次函数，从而大大提高效率。—不太懂
4、通过定期使用ANALYZE TABLE 语句使表统计信息保持最新，以便优化器拥有构建高效执行计划所需的信息。
5、了解特定于每个表的存储引擎的优化技术、索引技术和配置参数。两者InnoDB都有 MyISAM一套用于启用和维持查询高性能的准则
6、您可以使用优化 InnoDB 只读事务的InnoDB中的技术来 优化表的单查询事务
7、尽量不要在字句中进行类型转化或者函数操作（索引失效）
8、调整 MySQL 用于缓存的内存区域的大小和属性。通过有效使用 InnoDB 缓冲池、 MyISAM键缓存和 MySQL 查询缓存，重复查询运行速度更快，因为第二次及后续时间都会从内存中检索结果。
9、对于使用缓存内存区域快速运行的查询，仍然可以进一步优化，以便它们需要更少的缓存内存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多的并发用户、更大的请求等，而不会出现性能大幅下降。
10、处理锁问题，查询速度可能会受到同时访问表的其他会话的影响。

where字句优化

您可能会想重写查询以使算术运算更快，同时牺牲可读性。因为 MySQL 自动执行类似的优化，所以您通常可以避免这项工作，并以更易于理解和维护的形式保留查询。MySQL 执行的一些优化如下
1、删除不必要的括号：

   ((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

2、恒定折叠：

   (a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5

3、恒定条件去除：

   (b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6

4、索引使用的常量表达式仅计算一次。
5、COUNT(*)在单个表上不带 a 的是直接从表和表WHERE的表信息中检索。当仅与一个表一起使用时， 这也适用于任何表达式
6、及早检测无效常量表达式
7、如果不使用GROUP BY或聚合函数（COUNT()、 MIN()…），则尽量将HAVINGWHERE转化为Where
8、对于连接中的每个表， WHERE构建一个更简单的方法来快速 WHERE评估表并尽快跳过行。
9、通过尝试所有可能性找到连接表的最佳连接组合。如果 ORDER BYandGROUP BY子句中的所有列都来自同一个表，则在连接时首先首选该表。
10、查询每个表索引，并使用最好的索引，除非优化器认为使用表扫描更有效。根据最佳索引是否跨越表的 30% 以上来使用扫描，但固定百分比不再决定使用索引还是扫描之间的选择。优化器现在更加复杂，并且根据其他因素进行估计，例如表大小、行数和 I/O 块大小。
11、在某些情况下，MySQL 甚至可以从索引中读取行，而无需查阅数据文件。如果索引中使用的所有列都是数字，则仅使用索引树来解析查询。
12、
待续
https://dev.mysql.com/doc/refman/5.7/en/range-optimization.html