面試題：在日常工作中怎麼做MySQL優化的？

前言

#MySQL常見的最佳化手段分為下面幾個面向：

#SQL最佳化、設計最佳化，硬體最佳化等，其中每個大的方向中又包含多個小的最佳化點

#下面我們具體來看看

SQL最佳化

此最佳化方案指的是透過最佳化SQL 語句以及索引來提高MySQL 資料庫的運作效率，具體內容如下：

分頁最佳化

例如：

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

最佳化方案：

• # #延遲關聯

  先透過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

• 書籤方式

  書籤方式說白了就是找到limit第一個參數對應的主鍵值，再根據這個主鍵值再去過濾並limit

  例如：

#

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

##NULL在MySQL中不好處理，儲存需要額外空間，運算也需要特殊的運算符，含有NULL的列很難進行查詢最佳化

應當指定列為not null，用0、空串或其他特殊的值取代空值，例如定義為int not null default 0

最小資料長度

越小的資料類型長度通常在磁碟、記憶體和CPU快取中都需要更少的空間，處理起來更快

使用最簡單資料型別

簡單的資料型別運算代價更低，例如：能使用int 型別就不要使用varchar 類型，因為int 類型比varchar 類型的查詢效率更高

盡量少定義text 類型

text 類型的查詢效率很低，如果必須使用text定義字段，可以把此字段分離成子表，需要查詢此字段時使用聯合查詢，這樣可以提高主表的查詢效率

##適當分錶、分庫策略

分錶是指當一張表中的欄位更多時，可以嘗試將一張大表拆分為多張子表，把使用比較高頻的主資訊放入主表中，其他的放入子表，這樣我們大部分查詢只需要查詢欄位較少的主表就可以完成了，從而有效的提高了查詢的效率

分庫是指將一個資料庫分成多個資料庫。例如我們把一個資料庫拆分為了多個資料庫，一個主資料庫用於寫入和修改數據，其他的用於同步主資料並提供給客戶端查詢，這樣就把一個庫的讀和寫的壓力，分攤給了多個庫，從而提高了資料庫整體的運作效率

常見類型選擇

整數類型寬度設定

MySQL可以為整數型別指定寬度，例如int (11)，實際上並沒有意義，它並不會限制值的範圍，對於儲存和計算來說，int(1)和int(20)是相同的

VARCHAR和CHAR類型

char類型是定長的，而varchar儲存可變字串，比定長更省空間，但是varchar需要額外1或2個位元組記錄字串長度，更新時也容易產生碎片

需要結合使用場景來選擇：如果字串列最大長度比平均長度大很多，或者列的更新很少，選擇varchar較合適；如果要存很短的字串，或者字串值長度都相同，例如MD5值，或列資料經常變更，選擇使用char類型

#DATETIME和TIMESTAMP類型

datetime的範圍更大，能表示從1001到9999年，timestamp只能表示從1970年到2038年。 datetime與時區無關，timestamp顯示值依賴時區。在大多數場景下，這兩種類型都能很好地工作，但是建議使用timestamp，因為datetime佔用8個字節，timestamp只佔用了4個字節，timestamp空間效率更高

BLOB和TEXT類型

blob和text都是為儲存很大資料而設計的字串資料類型，分別採用二進位和字元方式儲存

在實際使用中，要慎用這兩種類型，它們的查詢效率很低，如果字段必須要使用這兩種類型，可以把此字段分離成子表，需要查詢此字段時使用聯合查詢，這樣可以提高主表的查詢效率

範式化

當資料較好範式化時，修改的資料較少，而且範式化的表格通常要小，可以有更多的資料快取在記憶體中，所以執行操作會更快

缺點則是查詢時需要更多的關聯

第一範式：欄位不可分割，資料庫預設支持

第二範式：消除對主鍵的部分依賴，可以在表中加上一個與業務邏輯無關的欄位作為主鍵，例如用自增id

第三範式：消除對主鍵的傳遞依賴，可以將表拆分，減少資料冗餘

硬體最佳化

MySQL 對硬體的需求主要體現在三個方面：磁碟、網路和記憶體

磁碟

磁碟應該盡量使用有高效能讀寫能力的磁碟，例如固態硬碟，這樣就可以減少I/O 運作的時間，從而提高了MySQL 整體的運作效率

磁碟也可以盡量使用多個小磁碟而不是一個大磁碟，因為磁碟的轉速是固定的，有多個小磁碟就相當於擁有多個並行運行的磁碟一樣

##網絡

保證網路頻寬的通暢（低延遲）以及夠大的網路頻寬是MySQL 正常運作的基本條件，如果條件允許的話也可以設定多個網路卡，以提高網路高峰期My​​SQL 伺服器的運行效率

記憶體

MySQL 伺服器的記憶體越大，那麼儲存和快取的資訊就越多，而記憶體的效能是非常高的，從而提高了整個MySQL 的運作效率

以上是面試題：在日常工作中怎麼做MySQL優化的？的詳細內容。更多資訊請關注PHP中文網其他相關文章！