慢查询，低效率查询或长时间运行的查询经常困扰DBA。它们始终无处不在，但对于负责管理数据库的任何人来说，它们都是生活中不可避免的一部分。
数据库设计不佳会影响查询的效率及其性能。缺乏知识或对函数调用，存储过程或例程的不当使用也会导致数据库性能下降，甚至可能损害整个MySQL数据库集群。
对于主从复制，导致这些问题的一个非常常见的原因是缺少主索引或辅助索引的表。这会导致从属滞后，这种滞后会持续很长时间（在更糟的情况下）。
下面将为您提供有关如何解决MySQL中数据库查询最大化以提高效率和性能的进阶讲解。
始终在表中添加唯一索引
当数据变大时，没有主键或唯一键的表通常会产生巨大的问题。发生这种情况时，简单的数据修改就会使数据库停顿。缺少适当的索引，并且已将UPDATE或DELETE语句应用于特定表，MySQL将选择全表扫描作为查询计划。这可能会导致磁盘I / O读写过多，并降低数据库性能。请参阅以下示例：
root[test]> show create table sbtest2\G *************************** 1. row *************************** Table: sbtest2 Create Table: CREATE TABLE `sbtest2` ( `id` int(10) unsigned NOT NULL, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) root[test]> explain extended update sbtest2 set k=52, pad="xx234xh1jdkHdj234" where id=57; +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | 1 | UPDATE | sbtest2 | NULL | ALL | NULL | NULL | NULL | NULL | 1923216 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.06 sec)复制
带有主键的表有很好的查询计划，
root[test]> show create table sbtest3\G *************************** 1. row *************************** Table: sbtest3 Create Table: CREATE TABLE `sbtest3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2097121 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) root[test]> explain extended update sbtest3 set k=52, pad="xx234xh1jdkHdj234" where id=57; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | UPDATE | sbtest3 | NULL | range | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)复制
主键或唯一键是表结构的重要组成部分，因为这非常重要，尤其是在对表执行维护时。例如，使用Percona工具包中的工具（例如pt-online-schema-change或pt-table-sync）建议您必须具有唯一的键。请记住，PRIMARY KEY已经是一个唯一键，并且一个主键不能保存NULL值，而是唯一键。将NULL值分配给主键会导致类似以下错误：
ERROR 1171 (42000): All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead复制
对于从属节点，在某些情况下，表上不存在主键/唯一键也是很常见的，因此这是表结构的差异。您可以使用mysqldiff实现此目的，也可以使用mysqldump --no-data…params并运行diff来比较其表结构并检查是否存在差异。
扫描具有重复索引的表，然后将其删除
索引重复也会导致性能下降，尤其是当表包含大量记录时。MySQL必须进行多次尝试来优化查询并执行更多查询计划以进行检查。它包括扫描大型索引分布或统计信息，并增加了性能开销，因为它可能导致内存争用或高I / O内存利用率。
当在表上观察到重复索引时查询的降级也归因于缓冲池的饱和。当检查点将事务日志刷新到磁盘时，这也会影响MySQL 的性能。这是由于处理和存储了不需要的索引（实际上浪费了该表的特定表空间中的空间）。请注意，重复的索引也存储在表空间中，该表空间也必须存储在缓冲池中。
查看下表，该表包含多个重复键：
root[test]#> show create table sbtest3\G *************************** 1. row *************************** Table: sbtest3 Create Table: CREATE TABLE `sbtest3` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k` (`k`,`pad`,`c`), KEY `kcp2` (`id`,`k`,`c`,`pad`), KEY `kcp` (`k`,`c`,`pad`), KEY `pck` (`pad`,`c`,`id`,`k`) ) ENGINE=InnoDB AUTO_INCREMENT=2048561 DEFAULT CHARSET=latin1 1 row in set (0.00 sec)复制
大小为2.3GiB
root[test]#> \! du -hs /var/lib/mysql/test/sbtest3.ibd 2.3G /var/lib/mysql/test/sbtest3.ibd复制
让我们删除重复的索引，并使用no-op alter重建表，
root[test]#> drop index kcp2 on sbtest3; drop index kcp on sbtest3 drop index pck on sbtest3; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 root[test]#> alter table sbtest3 engine=innodb; Query OK, 0 rows affected (28.23 sec) Records: 0 Duplicates: 0 Warnings: 0 root[test]#> \! du -hs /var/lib/mysql/test/sbtest3.ibd 945M /var/lib/mysql/test/sbtest3.ibd复制
它已经能够节省高达59％的旧表空间，这确实是巨大的。
要确定重复索引，可以使用pt-duplicate-checker为您处理作业。
调整缓冲池
在本节中，指的仅仅是InnoDB存储引擎。
缓冲池是InnoDB内核空间中的重要组件。InnoDB在访问时在这里缓存表和索引数据。由于使用BTREE将常用数据有效地存储在内存中，因此可以加快处理速度。例如，如果您有多个由> = 100GiB组成的表并被大量访问，那么我们建议您委派一个快速易失性内存，从128GiB的大小开始，并开始为缓冲池分配80％的物理内存。80％必须得到有效监控。您可以使用SHOW ENGINE INNODB STATUS \ G，也可以利用诸如ClusterControl之类的监视软件，该软件提供细粒度的监视，其中包括缓冲池及其相关的运行状况指标。同时设置innodb_buffer_pool_instances变量相应地。您可以将此值设置为大于8（如果innodb_buffer_pool_size> = 1GiB，则为默认值），例如16、24、32或64或更高（如有必要）。
监视缓冲池时，需要检查全局状态变量Innodb_buffer_pool_pages_free，该变量可让您考虑是否需要调整缓冲池，或者考虑是否还有不想要的或重复的索引消耗了缓冲区。该SHOW ENGINE INNODB STATUS \ G变还提供了缓冲池的信息，包括基于数量的个体缓冲池更详细的说明innodb_buffer_pool_instances您已设置。
使用FULLTEXT索引（但仅在适用时）
使用类似的查询
SELECT bookid, page, context FROM books WHERE context like '%for dummies%';复制
其中 context是字符串类型（char，varchar，text）列，是超坏查询的示例！使用必须贪婪的过滤器提取大量记录最终会导致全表扫描，这简直太疯狂了。考虑使用FULLTEXT索引。一个FULLTEXT指数有一个倒排索引设计。倒排索引存储单词列表，每个单词列表包含该单词出现的文档列表。为了支持邻近搜索，还存储每个单词的位置信息，以字节偏移量为单位。
为了使用FULLTEXT来搜索或过滤数据，您需要使用MATCH（）… AGAINST语法的组合，而不是上面的查询。当然，您需要将字段指定为FULLTEXT索引字段。
要创建FULLTEXT索引，只需指定FULLTEXT作为索引。请参阅以下示例：
root[minime]#> CREATE FULLTEXT INDEX aboutme_fts ON users_info(aboutme); Query OK, 0 rows affected, 1 warning (0.49 sec) Records: 0 Duplicates: 0 Warnings: 1 root[jbmrcd_date]#> show warnings; +---------+------+--------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------+ | Warning | 124 | InnoDB rebuilding table to add column FTS_DOC_ID | +---------+------+--------------------------------------------------+ 1 row in set (0.00 sec)复制
尽管使用FULLTEXT索引可以在列内很大的上下文中搜索单词时带来好处，但如果使用不正确，也会产生问题。
在执行FULLTEXT搜索时，经常访问的大型表（许多客户端请求正在搜索不同的唯一关键字）可能会占用大量CPU。
在某些情况下，FULLTEXT不适用。请参阅此外部博客文章。尽管我尚未在8.0上尝试过此操作，但没有看到与此相关的任何更改。我们建议不要使用FULLTEXT搜索大数据环境，尤其是对于高流量表。否则，请尝试利用其他技术，例如Apache Lucene，Apache Solr，tsearch2或Sphinx。
避免在列中使用NULL
在MySQL中，包含空值的列完全可以。但是，如果在索引中使用具有空值的列，则会影响查询性能，因为由于索引分布不佳，优化器无法提供正确的查询计划。但是，有一些方法可以优化涉及空值的查询，但是当然可以满足要求。
有效地设计架构拓扑和表结构
在某种程度上，将数据库表从1NF（第一范式）规范化为3NF（第三范式）可为查询效率带来一些好处，因为规范化的表会避免多余的记录。对表进行适当的规划和设计非常重要，因为这是您检索或提取数据的方式，并且在这些操作的每一项中都需要一定的成本。对于规范化的表，数据库的目标是确保每个表中的每个非键列都直接依赖于键。整个钥匙，只有钥匙。如果达到了这一目标，它将以减少冗余，减少异常和提高效率的形式付出收益。
虽然规范化表有许多好处，但这并不意味着您需要以这种方式规范化所有表。您可以使用Star Schema为数据库实现设计。使用Star Schema设计表的优点是查询更简单（避免复杂的交叉联接），易于检索数据以进行报告，由于无需使用联合或复杂联接或快速聚合而提高了性能。星型模式易于实现，但是您需要仔细计划，因为当表变大并且需要维护时，星型模式会带来很大的问题和不利之处。Star Schema（及其基础表）容易出现数据完整性问题，因此您很可能会出现大量数据冗余的情况。
混合数据库设计（只要您能够确定并确定必须在表上提取哪种数据）非常重要，因为您可以从更高效的查询中受益，并帮助DBA进行备份，维护，和恢复。
摆脱常数和旧数据
如何删除旧数据或归档常量和旧数据如何提高查询效率呢？不断修改并插入新数据的较大表有很多好处，表空间可以快速增长。当记录或数据彼此连续并且对表中的下一行有意义时，MySQL和InnoDB会高效地执行。这意味着，如果您没有不再需要使用的旧记录，则优化器不需要将其包含在统计信息中，从而可以提供更为有效的结果。而且，查询效率不仅在应用程序方面，还需要在执行备份以及维护或故障转移时考虑其效率。
根据需要启用查询日志记录
始终根据您的自定义需求设置MySQL的慢查询日志。如果您使用的是Percona Server，则可以利用其扩展的慢查询日志记录功能。它允许您习惯地定义某些变量。您可以组合查询类型来过滤查询，例如full_scan，full_join，tmp_table等。您还可以通过变量log_slow_rate_type以及许多其他命令来确定慢查询的日志记录速率。
在MySQL中启用查询日志记录（例如慢速查询）的重要性对于检查查询很有用，这样您就可以通过调整某些适合您需求的变量来优化或调整MySQL。要启用慢速查询日志，请确保已设置以下变量：
- long_query_time-为查询可以花费的时间分配正确的值。如果查询花费的时间超过10秒（默认），它将落入您分配的慢查询日志文件中。
- slow_query_log-要启用它，请将其设置为1。
- slow_query_log_file-这是慢速查询日志文件的目标路径。
慢查询日志对于查询分析和诊断导致停顿，从属延迟，长时间运行的查询，内存或CPU密集型甚至导致服务器崩溃的错误查询非常有帮助。如果您使用pt-query-digest或pt-index-usage，请使用慢查询日志文件作为报告这些查询的源目标。
结论
在此文中，讨论了一些可用于最大化数据库查询效率的方法。
