面接の質問: 日常業務で MySQL を最適化するにはどうすればよいですか?

#序文

#MySQL の一般的な最適化メソッドは次の側面に分かれています:

#SQL の最適化、設計の最適化、ハードウェアの最適化など、それぞれの主要な方向には複数の小さな最適化ポイントが含まれています

詳しく見てみましょう

SQL 最適化この最適化計画は、SQL ステートメントとインデックスを最適化することにより、MySQL データベースの運用効率を向上させることを指します。具体的な内容は次のとおりです。

#ページングの最適化

#例:

select * from table where type = 2 and level = 9 order by id asc limit 190289,10;

最適化計画:

Delayed association

最初に where 条件を使用して主キーを抽出し、次にテーブルを元のデータ テーブルに関連付け、元のセカンダリ インデックスを使用してデータ行を抽出する代わりに、主キー ID を使用してデータ行を抽出します
• 例:

  select a.* from table a, (select id from table where type = 2 and level = 9 order by id asc limit 190289,10 ) b where a.id = b.id

ブックマーク方法

端的に言えば、ブックマーク方法は次のとおりです。制限の最初のパラメータに対応するメイン パラメータを見つけます。キー値を指定し、この主キー値に基づいてフィルタリングして制限します。
• 例:

  select * from table where id > (select * from table where type = 2 and level = 9 order by id asc limit 190289, 1) limit 10;

インデックスの最適化

##インデックスを正しく使用する

假如我们没有添加索引，那么在查询时就会触发全表扫描，因此查询的数据就会很多，并且查询效率会很低，为了提高查询的性能，我们就需要给最常使用的查询字段上，添加相应的索引，这样才能提高查询的性能

建立覆盖索引

InnoDB使用辅助索引查询数据时会回表，但是如果索引的叶节点中已经包含要查询的字段，那它没有必要再回表查询了，这就叫覆盖索引

例如对于如下查询：

select name from test where city='上海'

我们将被查询的字段建立到联合索引中，这样查询结果就可以直接从索引中获取

alter table test add index idx_city_name (city, name);

在 MySQL 5.0 之前的版本尽量避免使用or查询

在 MySQL 5.0 之前的版本要尽量避免使用 or 查询，可以使用 union 或者子查询来替代，因为早期的 MySQL 版本使用 or 查询可能会导致索引失效，在 MySQL 5.0 之后的版本中引入了索引合并

索引合并简单来说就是把多条件查询，比如or或and查询对多个索引分别进行条件扫描，然后将它们各自的结果进行合并，因此就不会导致索引失效的问题了

如果从Explain执行计划的type列的值是index_merge可以看出MySQL使用索引合并的方式来执行对表的查询

避免在 where 查询条件中使用 != 或者 <> 操作符

SQL中，不等于操作符会导致查询引擎放弃索引索引，引起全表扫描，即使比较的字段上有索引

解决方法：通过把不等于操作符改成or，可以使用索引，避免全表扫描

例如，把column<>’aaa’，改成column>’aaa’ or column<’aaa’，就可以使用索引了

适当使用前缀索引

MySQL 是支持前缀索引的，也就是说我们可以定义字符串的一部分来作为索引

我们知道索引越长占用的磁盘空间就越大，那么在相同数据页中能放下的索引值也就越少，这就意味着搜索索引需要的查询时间也就越长，进而查询的效率就会降低，所以我们可以适当的选择使用前缀索引，以减少空间的占用和提高查询效率

比如，邮箱的后缀都是固定的“@xxx.com”，那么类似这种后面几位为固定值的字段就非常适合定义为前缀索引

alter table test add index index2(email(6));

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本

需要注意的是，前缀索引也存在缺点，MySQL无法利用前缀索引做order by和group by 操作，也无法作为覆盖索引

查询具体的字段而非全部字段

要尽量避免使用select *，而是查询需要的字段，这样可以提升速度，以及减少网络传输的带宽压力

优化子查询

尽量使用 Join 语句来替代子查询，因为子查询是嵌套查询，而嵌套查询会新创建一张临时表，而临时表的创建与销毁会占用一定的系统资源以及花费一定的时间，同时对于返回结果集比较大的子查询，其对查询性能的影响更大

小表驱动大表

我们要尽量使用小表驱动大表的方式进行查询，也就是如果 B 表的数据小于 A 表的数据，那执行的顺序就是先查 B 表再查 A 表，具体查询语句如下：

select name from A where id in (select id from B);

不要在列上进行运算操作

不要在列字段上进行算术运算或其他表达式运算，否则可能会导致查询引擎无法正确使用索引，从而影响了查询的效率

select * from test where id + 1 = 50;
select * from test where month(updateTime) = 7;

一个很容易踩的坑：隐式类型转换：

select * from test where skuId=123456

skuId这个字段上有索引，但是explain的结果却显示这条语句会全表扫描

原因在于skuId的字符类型是varchar(32)，比较值却是整型，故需要做类型转换

适当增加冗余字段

增加冗余字段可以减少大量的连表查询，因为多张表的连表查询性能很低，所有可以适当的增加冗余字段，以减少多张表的关联查询，这是以空间换时间的优化策略

正确使用联合索引

使用了 B+ 树的 MySQL 数据库引擎，比如 InnoDB 引擎，在每次查询复合字段时是从左往右匹配数据的，因此在创建联合索引的时候需要注意索引创建的顺序

例如，我们创建了一个联合索引是idx(name,age,sex)，那么当我们使用，姓名+年龄+性别、姓名+年龄、姓名等这种最左前缀查询条件时，就会触发联合索引进行查询；然而如果非最左匹配的查询条件，例如，性别+姓名这种查询条件就不会触发联合索引

Join优化

MySQL的join语句连接表使用的是nested-loop join算法，这个过程类似于嵌套循环，简单来说，就是遍历驱动表（外层表），每读出一行数据，取出连接字段到被驱动表（内层表）里查找满足条件的行，组成结果行

要提升join语句的性能，就要尽可能减少嵌套循环的循环次数

一个显著优化方式是对被驱动表的join字段建立索引，利用索引能快速匹配到对应的行，避免与内层表每一行记录做比较，极大地减少总循环次数。另一个优化点，就是连接时用小结果集驱动大结果集，在索引优化的基础上能进一步减少嵌套循环的次数

如果难以判断哪个是大表，哪个是小表，可以用inner join连接，MySQL会自动选择小表去驱动大表

避免使用JOIN关联太多的表

对于 MySQL 来说，是存在关联缓存的，缓存的大小可以由join_buffer_size参数进行设置

在 MySQL 中，对于同一个 SQL 多关联（join）一个表，就会多分配一个关联缓存，如果在一个 SQL 中关联的表越多，所占用的内存也就越大

如果程序中大量的使用了多表关联的操作，同时join_buffer_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性

排序优化

利用索引扫描做排序

MySQL有两种方式生成有序结果：其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的

但是如果索引不能覆盖查询所需列，就不得不每扫描一条记录回表查询一次，这个读操作是随机IO，通常会比顺序全表扫描还慢

因此，在设计索引时，尽可能使用同一个索引既满足排序又用于查找行

例如：

--建立索引（date,staff_id,customer_id）
select staff_id, customer_id from test where date = '2010-01-01' order by staff_id,customer_id;

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，才能够使用索引来对结果做排序

UNION优化

MySQL处理union的策略是先创建临时表，然后将各个查询结果填充到临时表中最后再来做查询，很多优化策略在union查询中都会失效，因为它无法利用索引

最好手工将where、limit等子句下推到union的各个子查询中，以便优化器可以充分利用这些条件进行优化

此外，除非确实需要服务器去重，一定要使用union all，如果不加all关键字，MySQL会给临时表加上distinct选项，这会导致对整个临时表做唯一性检查，代价很高

慢查询日志

出现慢查询通常的排查手段是先使用慢查询日志功能，查询出比较慢的 SQL 语句，然后再通过 Explain 来查询 SQL 语句的执行计划，最后分析并定位出问题的根源，再进行处理

スロークエリログとは、MySQL の設定でオンにできるスロークエリログの記録機能のことで、long_query_time 値を超えた SQL はログに記録されます。

"slow_query_log=1"

を設定してスロー クエリをオンにします。スロー ログ機能をオンにした後は、MySQL のパフォーマンスに一定の影響を与えることに注意してください。運用環境 この関数の使用には注意してください

#設計の最適化#NULL

## の使用は避けてください。 MySQL の #NULL 処理が難しく、ストレージに余分なスペースが必要で、操作にも特殊な演算子が必要です NULL を含むカラムはクエリが困難です null 値の代わりに、たとえば、null ではなく int として定義されます デフォルト 0

最小データ長

通常、ディスク、メモリ、および CPU キャッシュに必要なデータ型の長さは小さいほど、スペースが少なくなり、処理が高速になります

最も単純なデータを使用しますtype

単純なデータ型の操作は安価です。たとえば、varchar 型が使用できる場合は int 型を使用しないでください。int 型のクエリ効率は varchar 型のクエリ効率よりも高いためです。

テキスト タイプの定義はできるだけ少なくしてください。

テキストを使用する必要がある場合、テキスト タイプのクエリ効率は非常に低くなります。 サブテーブルに分割できるフィールドを定義します。このフィールドをクエリする必要がある場合は、メイン テーブルのクエリ効率を向上させることができる結合クエリを使用してください。

適切なテーブルとデータベース戦略

テーブルの分割これは、テーブル内にさらに多くのフィールドがある場合、大きなテーブルを複数のサブテーブルに分割し、より頻繁に使用されるメイン情報をメインテーブルに配置し、その他をサブテーブルに配置することを試みることができることを意味します。クエリは、より少ないフィールドを含むメイン テーブルのみをクエリする必要があるため、クエリの効率が効果的に向上します。

サブデータベースとは、データベースを複数のデータベースに分割することを指します。たとえば、データベースを複数のデータベースに分割し、1 つのメイン データベースはデータの書き込みと変更に使用され、他のデータベースはメイン データの同期とクエリ用のクライアントへの提供に使用されます。 1つのデータベースの負荷を共有し、複数のライブラリを提供することでデータベース全体の運用効率を向上させます

共通型の選択

整数型の幅設定

MySQL では、次のような整数型の幅を指定できます。 int (11) は実際には意味がありません。値の範囲は制限されません。格納と計算では、int(1) と int(20) は同じです

VARCHAR と CHAR 型

char 型は固定長ですが、varchar は可変文字列を格納するため、固定長よりも多くのスペースを節約できます。ただし、varchar は文字列の長さを記録するために追加の 1 バイトまたは 2 バイトを必要とし、また、更新時に更新する必要があります。断片化が起こりやすいです。

文字列列の最大長が平均長よりはるかに長い場合、または列がほとんどない場合は、使用シナリオと組み合わせて選択する必要があります。更新された場合、varchar の方が適しています。非常に短い文字列を保存する場合、または文字列値が MD5 値など同じ長さである場合、または列データが頻繁に変更される場合は、char 型

## を使用することを選択してください。

#DATETIME 型と TIMESTAMP 型

datetime の範囲はより広く、1001 年から 9999 年までの年を表すことができますが、タイムスタンプは 1970 年から 2038 年までの年のみを表すことができます。 datetime はタイムゾーンとは関係がなく、タイムスタンプの表示値はタイムゾーンによって異なります。ほとんどのシナリオでは、どちらのタイプも適切に機能しますが、日時は 8 バイトを占有し、タイムスタンプは 4 バイトしか占有しないため、タイムスタンプを使用することをお勧めします。タイムスタンプ空間の方が効率的です。

##BLOB タイプと TEXT タイプ

blob と text は、大量のデータを格納するように設計された文字列データ型で、それぞれバイナリ モードと文字モードで格納されます

実際の使用では、これら 2 つの型を使用するときは注意してください。クエリ効率は非常に低くなります。フィールドでこれら 2 つのタイプを使用する必要がある場合は、このフィールドをサブテーブルに分割できます。このフィールドをクエリする必要がある場合は、結合クエリを使用すると、メイン テーブルのクエリが向上します。効率

正規化データがより適切に正規化され、変更されるデータが減り、正規化されたテーブルが通常より小さくなると、より多くのデータをキャッシュできるようになります。メモリ内に保存されるため、操作の実行が高速になります。

欠点は、クエリ中により多くの関連付けが必要になることです。

第一正規形: フィールドは分割できず、データベースはデフォルトでこれをサポートします

第 2 正規形: 主キーへの部分的な依存を排除​​します。自動インクリメント ID

## を使用するなど、ビジネス ロジックに関係のないフィールドを主キーとしてテーブルに追加できます。 #第 3 正規形: 主キーへの依存を排除​​する 主キーの推移的な依存関係によりテーブルが分割され、データの冗長性が低下する可能性があります

ハードウェアの最適化

MySQL のハードウェア要件は、主にディスク、ネットワーク、メモリの 3 つの側面に反映されます

ディスク

ディスクでは、I/O 実行時間を短縮して MySQL の全体的な動作効率を向上させるために、ソリッド ステート ドライブなどの高性能の読み取りおよび書き込み機能を備えたディスクを使用するようにする必要があります

ディスクディスクの回転速度は固定されているため、1 つの大きなディスクの代わりに複数の小さなディスクを使用することもできます。複数の小さなディスクを使用することは、複数のディスクを並列実行することと同じです

ネットワーク

スムーズなネットワーク帯域幅 (低遅延) と十分なネットワーク帯域幅を確保することは、MySQL の通常の動作の基本条件です。条件が許せば、複数のネットワーク カードをセットアップして、パフォーマンスを向上させることもできます。ネットワークのピーク時の MySQL サーバーのパフォーマンス 運用効率

メモリ

MySQL サーバーのメモリが大きいほど、より多くの情報が保存およびキャッシュされ、メモリのパフォーマンスが非常に高いので、MySQL全体の動作効率が向上しました。

以上が面接の質問: 日常業務で MySQL を最適化するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。