作者：微信小助手
发布时间：2024-08-22T17:50:25
一般语句优化 先从一般的语句优化开始，其实对于很多规范大家并不陌生，可就是在用的时候，无法遵从，希望今天大家再过一遍，可以养成一种良好的数据库编码习惯。 选择合适的数据类型及字符集 使用合适的数据类型可以减少存储空间和提高查询速度。这个可不能小看，数据量到达一个量级，这个就能看出明显差异。 例子：对于布尔值使用 TINYINT(1) 而不是 CHAR(1) 比如你有一个字段是表示业务状态或者是类型。 对于仅存储英文的表，使用 latin1 而不是 utf8mb4。 避免使用SELECT * 仅选择必要的列，减少数据传输量。 例子：避免 SELECT *，改用具体列名。 合理使用JOIN、避免子查询 避免过多的 JOIN 操作，尽量减少数据集的大小。 例子：优化连接条件，确保连接列上有索引。 尽量使用 JOIN 或者 EXISTS 代替子查询。 例子：避免使用子查询，改用 JOIN。 使用UNION代替OR、优化ORDER BY和GROUP BY 确保 ORDER BY 和 GROUP BY 的列上有索引。 例子：在排序和分组列上添加索引。 在业务允许的情况下，使用 UNION 代替 OR 条件。 例子：用两个查询的 UNION 代替一个带 OR 的查询。 避免使用%开头的LIKE查询 避免使用 % 开头的 LIKE 查询，因为不能使用索引。 例子：使用全文本搜索代替 LIKE '%keyword%'。也就是让%在最后面 这个尤其重要，相信各位在各大平台网站上。很多搜索只有输入前面的字才能有结果，你输入中间的字，会查询不到，其实就是这个原理。 使用批量插入、优化INSERT操作 使用批量插入减少插入操作的开销。 在批量插入时，关闭唯一性检查和索引更新，插入完成后再开启（此种情况大家可根据业务来，比如当查询很频繁的时候，这样操作会影响查询效率）。 避免使用HAVING代替WHERE 在可能的情况下，使用 WHERE 代替 HAVING 进行过滤。 例子：避免使用 HAVING 过滤。
配置参数调优 该部分主要针对MySQL的配置做一些操作，这块还是相当重要的，虽然是运维领域，但熟悉MySQL的配置是我们研发的不可不会的领域。 调整innodb_buffer_pool_size innodb_buffer_pool_size 是 InnoDB 存储引擎最重要的配置参数之一，用于指定 InnoDB 缓冲池的大小。缓冲池用于缓存数据页、索引页和 InnoDB 表的其它信息。合理设置这个参数对数据库性能有很大影响。 增大 InnoDB 缓冲池大小，提高缓存命中率。 但是这里要注意 该值并不是越大越好。innodb_buffer_pool_size 应该设置要尽可能大，但要确保为操作系统和其他应用程序留出足够的内存。 一般建议在数据库专用服务器上设置为物理内存的 60% 到 80%。通过监控数据库性能和内存使用情况，可以进一步调整这个参数以优化数据库性能。 调整query_cache_size query_cache_size 是用于指定查询缓存的大小。查询缓存可以缓存 SELECT 查询的结果，避免重复执行相同的查询，从而提高性能。 然而，在 MySQL 8.0 及更高版本中，查询缓存已经被完全移除。如果你使用的是 MySQL 8.0 及以上版本，可以忽略 query_cache_size 参数。 调整thread_cache_size 增大线程缓存大小，减少线程创建开销。 调整table_open_cache 增大表缓存大小，减少表打开的开销。 调整tmp_table_size和max_heap_table_size 增大临时表和堆表的最大大小，减少磁盘 I/O。 调整innodb_flush_log_at_trx_commit 根据需求调整日志刷新策略，权衡性能和数据安全性。 调整innodb_log_file_size 增大日志文件大小，减少日志文件切换的开销。 调整innodb_log_buffer_size 增大日志缓冲区大小，提高写入性能。 调整innodb_io_capacity 根据磁盘 I/O 性能调整 InnoDB I/O 容量。 调整max_connections 增大最大连接数，支持更多并发连接。 调整sort_buffer_size 增大排序缓冲区大小，提高排序操作的性能。 调整read_buffer_size 增大读缓冲区大小，提高顺序扫描性能。 正确使用索引 这块是最重要的，因为假如使用不当，那么创建索引不但没有效果，反而还会成为负担。 在常用查询条件和连接条件的列上建立索引 这块很清楚，反正只要发现查询较慢，优先检查where条件后面，有没有被创建索引。 遵循最左前缀原则 这个是针对复合索引时的要求，遵循最左前缀原则。 例子：对于索引 (a, b, c)，可以用于 (a)，(a, b)，(a, b, c) 的查询。
CREATE TABLE users ( is_active TINYINT(1));
CREATE TABLE messages ( content VARCHAR(255) CHARACTER SET latin1);
SELECT id, name, email FROM users;
SELECT * FROM users uJOIN orders o ON u.id = o.user_idWHERE u.status = 'active';
SELECT u.name, o.amountFROM users uJOIN orders o ON u.id = o.user_id;
CREATE INDEX idx_order_date ON orders (order_date);SELECT * FROM orders ORDER BY order_date;
SELECT id, name FROM users WHERE status = 'active'UNIONSELECT id, name FROM users WHERE status = 'pending';
SELECT * FROM products WHERE description LIKE 'keyword%';
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');
SET autocommit=0;SET unique_checks=0;SET foreign_key_checks=0;-- 批量插入操作SET unique_checks=1;SET foreign_key_checks=1;COMMIT;
SELECT user_id, COUNT(*) FROM ordersWHERE order_date > '2020-01-01'GROUP BY user_idHAVING COUNT(*) > 1;
SET GLOBAL innodb_buffer_pool_size = 2G;
SET GLOBAL thread_cache_size = 100;
SET GLOBAL table_open_cache = 4000;
SET GLOBAL tmp_table_size = 64M;SET GLOBAL max_heap_table_size = 64M;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL innodb_log_file_size = 256M;
SET GLOBAL innodb_log_buffer_size = 16M;
SET GLOBAL innodb_io_capacity = 2000;
SET GLOBAL max_connections = 500;
SET GLOBAL sort_buffer_size = 4M;
SET GLOBAL read_buffer_size = 2M;
</ul>
<pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">CREATE</span> <span class="code-snippet__keyword">INDEX</span> idx_abc <span class="code-snippet__keyword">ON</span> table_name (a, b, c);</span></code><code><span class="code-snippet_outer"><span class="code-snippet__keyword">SELECT</span> * <span class="code-snippet__keyword">FROM</span> table_name <span class="code-snippet__keyword">WHERE</span> a = <span class="code-snippet__number">1</span> <span class="code-snippet__keyword">AND</span> b = <span class="code-snippet__number">2</span>;</span></code></pre>
</section>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"><br></span></strong></p>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">避免在索引列上进行计算</span></strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"></span></p>
<p style="line-height:1.75em;"><br></p>
<p style="line-height:1.75em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">例子：避免 WHERE YEAR(date) = 2020，改用范围查询。</span></p>
<section class="code-snippet__fix code-snippet__js">
<ul class="code-snippet__line-index code-snippet__js">
</ul>
<pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">SELECT</span> * <span class="code-snippet__keyword">FROM</span> orders <span class="code-snippet__keyword">WHERE</span> <span class="code-snippet__built_in">date</span> <span class="code-snippet__keyword">BETWEEN</span> <span class="code-snippet__string">'2024-06-01'</span> <span class="code-snippet__keyword">AND</span> <span class="code-snippet__string">'2024-06-30'</span>;</span></code></pre>
</section>
<p style="line-height:1.75em;"><strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"><br></span></strong></p>
<p style="line-height:1.75em;"><strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">避免重复索引</span></strong></p>
<p style="line-height:1.75em;"><br></p>
<p style="line-height:1.75em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">检查并删除重复的索引，减少维护开销。了解mysql底层的都知道，创建索引，就会增加一个页，重复索引无疑是给增加负担。</span></p>
<p><br></p>
<p style="line-height:1.75em;"><strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">更新频繁的列慎用索引</span></strong></p>
<p style="line-height:1.75em;"><br></p>
<p style="line-height:1.75em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">对于更新频繁的列，索引会增加写操作的开销，需要慎重使用。</span></p>
<section class="code-snippet__fix code-snippet__js">
<ul class="code-snippet__line-index code-snippet__js">
</ul>
<pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">CREATE</span> <span class="code-snippet__keyword">INDEX</span> idx_update_col <span class="code-snippet__keyword">ON</span> table_name (update_col); </span></code><code><span class="code-snippet_outer"><span class="code-snippet__comment">-- 如果 update_col 更新频繁，需慎用</span></span></code></pre>
</section>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"><br></span></strong></p>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">避免过多的列使用复合索引</span></strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"></span></p>
<p style="line-height:1.75em;"><br></p>
<p style="line-height:1.75em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">复合索引的列数不要太多，列数过多会增加索引的维护开销，并且可能导致索引文件过大。对此可以拆分为较少复合索引和单个索引</span></p>
<section class="code-snippet__fix code-snippet__js">
<ul class="code-snippet__line-index code-snippet__js">
</ul>
<pre class="code-snippet__js" data-lang="sql"><code><span class="code-snippet_outer"><span class="code-snippet__keyword">CREATE</span> <span class="code-snippet__keyword">INDEX</span> idx_columns <span class="code-snippet__keyword">ON</span> table_name (col1, col2, col3, col4, col5); </span></code><code><span class="code-snippet_outer"><span class="code-snippet__comment">-- 列数太多</span></span></code></pre>
</section>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"><br></span></strong></p>
<p style="line-height:1.75em;"><strong style="font-size: var(--articleFontsize);letter-spacing: 0.034em;"><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;">使用覆盖索引</span></strong><span style="outline: 0px;font-size: 15px;color: rgb(62, 62, 62);line-height: 1.6em;visibility: visible;letter-spacing: 0.544px;font-family: Helvetica, Arial, sans-serif;"></span></p>
<p style="line-height:1.75em;"><br></p>
<p style="line-height:1.75em;"><span style="outline: 0px;f