mysql 优化框架是怎样的

共计 4517 个字符，预计需要花费 12 分钟才能阅读完成。

本篇文章给大家分享的是有关 mysql 优化框架是怎样的，丸趣 TV 小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着丸趣 TV 小编一起来看看吧。

MySQL 优化框架

1. SQL 语句优化
2. 索引优化
3. 数据库结构优化
4. InnoDB 表优化
5. MyISAM 表优化
6. Memory 表优化
7. 理解查询执行计划
8. 缓冲和缓存
9. 锁优化
10. MySQL 服务器优化
11. 性能评估
12. MySQL 优化内幕

MySQL 优化需要在三个不同层次上协调进行：MySQL 级别、OS 级别和硬件级别。MySQL 级别的优化包括表优化、查询优化和 MySQL 服务器配置优化等，而 MySQL 的各种数据结构又最终作用于 OS 直至硬件设备，因此还需要了解每种结构对 OS 级别的资源的需要并最终导致的 CPU 和 I / O 操作等，并在此基础上将 CPU 及 I / O 操作需要尽量降低以提升其效率。

数据库层面的优化着眼点：
1、是否正确设定了表结构的相关属性，尤其是每个字段的字段类型是否为最佳。同时，是否为特定类型的工作组织使用了合适的表及表字段也将影响系统性能，比如，数据频繁更新的场景应该使用较多的表而每张表有着较少字段的结构，而复杂数据查询或分析的场景应该使用较少的表而每张表较多字段的结构等。
2、是否为高效进行查询创建了合适的索引。
3、是否为每张表选用了合适的存储引擎，并有效利用了选用的存储引擎本身的优势和特性。
4、是否基于存储引擎为表选用了合适的行格式(row format)。例如，压缩表在读写操作中会降低 I / O 操作需求并占用较少的磁盘空间，InnoDB 支持读写应用场景中使用压缩表，但 MyISAM 仅能在读环境中使用压缩表。
5、是否使用了合适的锁策略，如在并发操作场景中使用共享锁，而对较高优先级的需求使用独占锁等。同时，还应该考虑存储引擎所支持的锁类型。
6、是否为 InnoDB 的缓冲池、MyISAM 的键缓存以及 MySQL 查询缓存设定了合适大小的内存空间，以便能够存储频繁访问的数据且又不会引起页面换出。

操作系统和硬件级别的优化着眼点：
1、是否为实际的工作负载选定了合适的 CPU，如对于 CPU 密集型的应用场景要使用更快速度的 CPU 甚至更多数量的 CPU，为有着更多查询的场景使用更多的 CPU 等。基于多核以及超线程 (hyperthreading) 技术，现代的 CPU 架构越来越复杂、性能也越来越强了，但 MySQL 对多 CPU 架构的并行计算能力的利用仍然是有着不太尽如人意之处，尤其是较老的版本如 MySQL 5.1 之前的版本甚至无法发挥多 CPU 的优势。不过，通常需要实现的 CPU 性能提升目标有两类：低迟延和高吞吐量。低延迟需要更快速度的 CPU，因为单个查询只能使用一颗；而需要同时运行许多查询的场景，多 CPU 更能提供更好的吞吐能力，然而其能否奏效还依赖于实际工作场景，因为 MySQL 尚不能高效的运行于多 CPU，并且其对 CPU 数量的支持也有着限制。一般来说，较新的版本可以支持 16 至 24 颗 CPU 甚至更多。
2、是否有着合适大小的物理内存，并通过合理的配置平衡内存和磁盘资源，降低甚至避免磁盘 I /O。现代的程序设计为提高性能通常都会基于局部性原理使用到缓存技术，这对于频繁操作数据的数据库系统来说尤其如此——有着良好设计的数据库缓存通常比针对通用任务的操作系统的缓存效率更高。缓存可以有效地延迟写入、优化写入，但并能消除写入，并综合考虑存储空间的可扩展性等，为业务选择合理的外部存储设备也是非常重要的工作。
3、是否选择了合适的网络设备并正确地配置了网络对整体系统系统也有着重大影响。延迟和带宽是网络连接的限制性因素，而常见的网络问题如丢包等，即是很小的丢包率也会赞成性能的显著下降。而更重要的还有按需调整系统中关网络方面的设置，以高效处理大量的连接和小查询。
4、是否基于操作系统选择了适用的文件系统。实际测试表明大部分文件系统的性能都非常接近，因此，为了性能而苦选文件系统并不划算。但考虑到文件系统的修复能力，应该使用日志文件系统如 ext3、ext4、XFS 等。同时，关闭文件系统的某些特性如访问时间和预读行为，并选择合理的磁盘调度器通常都会给性能提升带来帮助。
5、MySQL 为响应每个用户连接使用一个单独的线程，再加内部使用的线程、特殊目的线程以及其它任何由存储引擎创建的线程等，MySQL 需要对这些大量线程进行有效管理。Linux 系统上的 NPTL 线程库更为轻量级也更有效率。MySQL 5.5 引入了线程池插件，但其效用尚不明朗。

使用 InnoDB 存储引擎最佳实践：
1、基于 MySQL 查询语句中最常用的字段或字段组合创建主键，如果没有合适的主键也最好使用 AUTO_INCRMENT 类型的某字段为主键。
2、根据需要考虑使用多表查询，将这些表通过外键建立约束关系。
3、关闭 autocommit。
4、使用事务 (START TRANSACTION 和 COMMIT 语句) 组合相关的修改操作或一个整体的工作单元，当然也不应该创建过大的执行单元。
5、停止使用 LOCK TABLES 语句，InnoDB 可以高效地处理来自多个会话的并发读写请求。如果需要在一系列的行上获取独占访问权限，可以使用 SELECT … FOR UPDATE 锁定仅需要更新的行。
6、启用 innodb_file_per_table 选项，将各表的数据和索引分别进行存放。
7、评估数据和访问模式是否能从 InnoDB 的表压缩功能中受益(在创建表时使用 ROW_FORMAT=COMPRESSED 选项)，如果可以，则应该启用压缩功能。

EXPLAIN 语句解析：
id:SELECT 语句的标识符，一般为数字，表示对应的 SELECT 语句在原始语句中的位置。没有子查询或联合的整个查询只有一个 SELECT 语句，因此其 id 通常为 1。在联合或子查询语句中，内层的 SELECT 语句通常按它们在原始语句中的次序进行编号。但 UNION 操作通常最后会有一个 id 为 NULL 的行，因为 UNION 的结果通常保存至临时表中，而 MySQL 需要到此临时表中取得结果。

select_type:
即 SELECT 类型，有如下值列表：
SIMPLE：简单查询，即没有使用联合或子查询；
PRIMARY：UNION 的最外围的查询或者最先进行的查询；
UNION：相对于 PRIMARY，为联合查询的第二个及以后的查询；
DEPENDENT UNION：与 UNION 相同，但其位于联合子查询中(即 UNION 查询本身是子查询)；
UNION RESULT：UNION 的执行结果；
SUBQUERY：非从属子查询，优化器通常认为其只需要运行一次；
DEPENDENT SUBQUERY：从属子查询，优化器认为需要为外围的查询的每一行运行一次，如用于 IN 操作符中的子查询；
DERIVED：用于 FROM 子句的子查询，即派生表查询；

table:
输出信息所关系到的表的表名，也有可能会显示为如下格式：
：id 为 M 和 N 的查询执行联合查询后的结果；
：id 为 N 的查询执行的结果集；

type:
MySQL 官方手册中解释 type 的作用为“type of join(联结的类型)”，但其更确切的意思应该是“记录 (record) 访问类型”，因为其主要目的在于展示 MySQL 在表中找到所需行的方式。通常有如下所示的记录访问类型：
system: 表中仅有一行，是 const 类型的一种特殊情况；
const：表中至多有一个匹配的行，该行仅在查询开始时读取一次，因此，该行此字段中的值可以被优化器看作是个常量 (constant)；当基于 PRIMARY KEY 或 UNIQUE NOT NULL 字段查询，且与某常量进行等值比较时其类型就为 const，其执行速度非常快；
eq_ref：类似于 const，表中至多有一个匹配的行，但比较的数值不是某常量，而是来自于其它表；ed_ref 出现在 PRIMARY KEY 或 UNIQUE NOT NULL 类型的索引完全用于联结操作中进行等值(=) 比较时；这是除了 system 和 const 之外最好的访问类型；
ref：查询时的索引类型不是 PRIMARY KEY 或 UNIQUE NOT NULL 导致匹配到的行可能不惟一，或者仅能用到索引的左前缀而非全部时的访问类型；ref 可被用于基于索引的字段进行 = 或 = 操作；
fulltext：用于 FULLTEXT 索引中用纯文本匹配的方法来检索记录。
ref_or_null：类似于 ref，但可以额外搜索 NULL 值；
index_merge：使用“索引合并优化”的记录访问类型，相应地，其 key 字段 (EXPLAIN 的输出结果) 中会出现用到的多个索引，key_len 字段中会出现被使用索引的最长长度列表；将多个“范围扫描 (range scan)”获取到的行进行合并成一个结果集的操作即索引合并(index merge)。
unique_subquery：用于 IN 比较操作符中的子查询中进行的“键值惟一”的访问类型场景中，如 value IN (SELECT primary_key FROM single_table WHERE some_expr)；
index_subquery：类似于 unique_subquery，但子查询中键值不惟一；
range：带有范围限制的索引扫描，而非全索引扫描，它开始于索引里的某一点，返回匹配那个值的范围的行；相应地，其 key 字段(EXPLAIN 的输出结果) 中会输出所用到的索引，key_len 字段中会包含用到的索引的最长部分的长度；range 通常用于将索引与常量进行 =、、、=、、=、IS NULL、=、BETWEEN 或 IN()类的比较操作中；
index：同全表扫描 (ALL)，只不过是按照索引的次序进行而不行的次序；其优点是避免了排序，但是要承担按索引次序读取整个表的开销，这意味着若是按随机次序访问行，代价将非常大；
ALL：“全表扫描”的方式查找所需要的行，如果第一张表的查询类型(EXPLAIN 的输出结果) 为 const，其性能可能不算太坏，而第一张表的查询类型为其它结果时，其性能通常会非常差；

Extra:
Using where：MySQL 服务器将在存储引擎收到数据后进行“后过滤(post-filter)”以限定发送给下张表或客户端的行；如果 WHERE 条件中使用了索引列，其读取索引时就由存储引擎检查，因此，并非所有带有 WHERE 子句的查询都会显示“Using where”；
Using index：表示所需要的数据从索引就能够全部获取到，从而不再需要从表中查询获取所需要数据，这意味着 MySQL 将使用覆盖索引；但如果同时还出现了 Using where，则表示索引将被用于查找特定的键值；
Using index for group-by：类似于 Using index，它表示 MySQL 可仅通过索引中的数据完成 GROUP BY 或 DISTINCT 类的查询；
Using filesort：表示 MySQL 会对结果使用一个外部索引排序，而不是从表里按索引次序来读取行。

以上就是 mysql 优化框架是怎样的，丸趣 TV 小编相信有部分知识点可能是我们日常工作会见到或用到的。希望你能通过这篇文章学到更多知识。更多详情敬请关注丸趣 TV 行业资讯频道。