MySQL性能優化

CDA數據分析師

優質創作者

優化查詢

查詢是數據庫中最頻繁的操作，提高查詢速度可以有效地提高MySQL數據庫的性能。

分析查詢語句

通過對查詢語句的分析，可以了解查詢語句的執行情況，找出查詢語句執行的瓶頸，從而優化查詢。

MySQL中提供了EXPLAIN語句和DESCRIBE語句來分析查詢語句。

語法
：

EXPLAIN/DESCRIBE SELECT select_options

示例
：

id：SELECT 識別符，即查詢序號。

select_type：SELECT語句的類型，SIMPLE表示簡單查詢，不包括連接查詢和子查詢；PRIMARY表示主查詢或最外層的查詢語句；UNION表示連接查詢的第二個或後面的查詢語句；UNION RESULT表示連接查詢的結果；SUBQUERY表示子查詢中的第一個SELECT語句；DERIVED表示FROM子句的子查詢。

table：查詢的表。

type：表的連接類型。

索引對查詢速度的影響

MySQL中提高性能的一個有效方式就是對數據表設計合理的索引。索引提供了高效數訪問數據的方法。並且可以加快查詢的速度，因此，索引對查詢的速度有著至關重要的影響。

索引簡介

索引是對數據庫表中一個或多個字段的值進行排序的一種結構，使用索引可提高數據庫中特定數據的查詢速度。

索引的意義

索引是一個單獨的、存儲在磁盤上的數據庫結構，包含著對數據表裏所有記錄的引用指針。使用索引可以快速找出在某個或多個字段中有特定行。

如果不使用索引，MySQL必須從第一條記錄開始檢索表中的每一條記錄，直到找出相關的行。那麼表越大，查詢數據所花費的時間就越多。

如果在表中查詢的字段有索引，MySQL能夠快速到達一個位置去檢索數據文件，而不需要再去查看所有數據，那麼將會節省很大一部分查詢時間。

比如說emp表中1W個員工的記錄，要查詢工號為7566的員工信息select * from emp where empno=7566，如果沒有索引，服務器會從表中第一條記錄開始，一條條往下遍曆，直到找到empno=7566的員工信息。

如果在empno這個字段上創建索引，就可以索引文件裏面找empno=7566這一行的位置，而不需要再遍曆1W條記錄了。

索引的優缺點

所有MySQL的字段類型都可以添加索引，但是索引也不是越多越好，而是要根據業務數據合理的使用。

優點

通過索引對數據進行檢索，大大提高了數據的查詢效率。

通過創建唯一索引，可以保證數據庫表中每一條記錄的唯一性。

在實現數據的參照完整性方面，可以加速表和表之間的連接。

在使用分組和排序子句進行數據查詢時，也可以顯著減少查詢中分組和排序的時間。

缺點

創建索引和維護索引要耗費時間，並且隨著數據量的增加所耗費的時間也會增加。

索引也需要占磁盤空間的，創建的索引太多，索引文件也會占用一定的存儲空間。

當對表中的數據進行增加、刪除、修改時，索引也需要動態的維護，這樣就降低了數據的維護速度。

創建索引的原則

索引設計不合理或缺少索引都會對數據庫和應用程序的性能造成障礙，高效的索引對於獲得良好的性能非常重要。

需要創建索引的情況

主外鍵和唯一約束的字段自動創建索引

頻繁作為查詢條件的字段應該創建索引

查詢中排序的字段應該創建索引

查詢中分組或統計的字段應該創建索引

不需要創建索引的情況

表中記錄太少不需要創建索引

需要頻繁增刪改的字段不適合創建索引

where子句中用不到的字段不需要創建索引

重複值較多的字段不需要創建索引

索引的結構

索引是在存儲引擎中實現的，使用不同的存儲引擎，所支持的索引也是不同的。

在mysql中常用兩種索引結構BTree和Hash，兩種算法檢索方式不一樣，對查詢的作用也不一樣。

MyISAM和InnoDB存儲引擎只支持BTREE索引，MEMORY/HEAP存儲引擎支持HASH和BTREE索引。

MySQL的InnoDB存儲引擎是支持hash索引的，不過我們必須啟用，hash索引的創建由InnoDB存儲引擎自動優化創建，我們干預不了。

索引的類型

索引的類型可以分類以下幾種：

普通索引：最基本的索引，沒有任何限制

唯一索引：索引字段的取值不能重複，可以有空值，但空值也只能出現一次。

主鍵索引：索引字段的取值不能為空，也不能重複。

組合索引：一個索引包含多個字段，只有在查詢條件中使用了創建索引時的第一個字段，索引才會被使用。

全文索引：通過關鍵字符，就能找到該字段所屬的記錄行。僅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT類型的字段上使用。

空間索引：對空間數據類型（GEOMETRY、POINT、LINESTRING、POLYGON）的字段建立的索引，僅限MyISAM引擎，且要求索引字段的取值不能為空。

索引的操作

實際上索引也是一張表，創建索引時，數據庫管理系統會在本地磁盤建立索引文件，裏面保存了索引字段，並指向實體表的記錄。

創建索引

創建表的同時須指定索引名、表名和字段名。
語法
：

create index <索引名> on <表名>(<字段名>);

自動創建索引

在表中定義了主鍵約束時，會自動創建一個對應的主鍵索引。

在表中定義了外鍵約束時，會自動創建一個對應的普通索引。

在表中定義了唯一約束時，會自動創建一個對應的唯一索引。

示例
：emp表中的job添加普通索引

mysql> create index job_index on emp(job);

查看索引

語法
：

show index from <表名>;

示例
：查看emp表中的索引

使用索引

在查詢語句中使用索引會大大提升數據的檢索速度。
示例
：

刪除索引

刪除索引只是刪除了表中的索引對象，表中的數據不會被刪除。
語法
：

drop index <索引名> on <表名>;

示例
：