mysql性能優(yōu)化就是通過合理安排資源，調(diào)整系統(tǒng)參數(shù)使mysql運(yùn)行更快、更節(jié)省資源。mysql性能優(yōu)化包括查詢速度優(yōu)化、數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化、mysql服務(wù)器優(yōu)化等。

優(yōu)化簡介

優(yōu)化數(shù)據(jù)庫是數(shù)據(jù)庫管理員和數(shù)據(jù)庫開發(fā)人員的必備技能。MySQL優(yōu)化，一方面是找出系統(tǒng)瓶頸，提高M(jìn)ySQL數(shù)據(jù)庫整體的性能；另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整，以提高用戶操作響應(yīng)的速度；同時(shí)還要盡可能節(jié)省系統(tǒng)資源，以便系統(tǒng)可以提供更大負(fù)荷的服務(wù)。

例如，通過優(yōu)化文件系統(tǒng)，提高磁盤I/O的讀寫速度；通過優(yōu)化操作系統(tǒng)調(diào)整策略，提高M(jìn)ySQL在高負(fù)荷情況下的負(fù)載能力；優(yōu)化表結(jié)構(gòu)、索引、查詢語句等使查詢響應(yīng)更快。

在MySQL中可以使用SHOW STATUS語句查詢一些MySQL數(shù)據(jù)庫的性能參數(shù)。

語法：

show status like 'value';

其中，value是要查詢的參數(shù)值，常用的性能參數(shù)如下：

• Connections：連接MySQL服務(wù)器的次數(shù)。
• Uptime：MySQL服務(wù)器的上線時(shí)間。
• Slow_queries：慢查詢的次數(shù)。
• Com_select：查詢操作的次數(shù)。
• Com_insert：插入操作的次數(shù)。
• Com_update：更新操作的次數(shù)。
• Com_delete：刪除操作的次數(shù)。

示例：查詢MySQL服務(wù)器的連接次數(shù)

優(yōu)化查詢

查詢是數(shù)據(jù)庫中最頻繁的操作，提高查詢速度可以有效地提高M(jìn)ySQL數(shù)據(jù)庫的性能。

分析查詢語句

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

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

語法：

EXPLAIN/DESCRIBE [EXTENDED] SELECT select_options

示例：

• id：SELECT 識別符，即查詢序號。
• select_type：SELECT語句的類型，SIMPLE表示簡單查詢，不包括連接查詢和子查詢；PRIMARY表示主查詢或最外層的查詢語句；UNION表示連接查詢的第二個(gè)或后面的查詢語句；UNION RESULT表示連接查詢的結(jié)果；SUBQUERY表示子查詢中的第一個(gè)SELECT語句；DERIVED表示FROM子句的子查詢。
• table：查詢的表。
• type：表的連接類型。

索引對查詢速度的影響

MySQL中提高性能的一個(gè)有效方式就是對數(shù)據(jù)表設(shè)計(jì)合理的索引。索引提供了高效數(shù)訪問數(shù)據(jù)的方法。并且可以加快查詢的速度，因此，索引對查詢的速度有著至關(guān)重要的影響。

索引簡介

索引是對數(shù)據(jù)庫表中一個(gè)或多個(gè)字段的值進(jìn)行排序的一種結(jié)構(gòu)，使用索引可提高數(shù)據(jù)庫中特定數(shù)據(jù)的查詢速度。

索引的意義

索引是一個(gè)單獨(dú)的、存儲在磁盤上的數(shù)據(jù)庫結(jié)構(gòu)，包含著對數(shù)據(jù)表里所有記錄的引用指針。使用索引可以快速找出在某個(gè)或多個(gè)字段中有特定值的行。

如果不使用索引，MySQL必須從第一條記錄開始檢索表中的每一條記錄，直到找出相關(guān)的行。那么表越大，查詢數(shù)據(jù)所花費(fèi)的時(shí)間就越多。

如果在表中查詢的字段有索引，MySQL能夠快速到達(dá)一個(gè)位置去檢索數(shù)據(jù)文件，而不需要再去查看所有數(shù)據(jù)，那么將會節(jié)省很大一部分查詢時(shí)間。

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

如果在empno這個(gè)字段上創(chuàng)建索引，就可以索引文件里面找empno=7566這一行的位置，而不需要再遍歷1W條記錄了。

索引的優(yōu)缺點(diǎn)

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

優(yōu)點(diǎn)

• 通過索引對數(shù)據(jù)進(jìn)行檢索，大大提高了數(shù)據(jù)的查詢效率。
• 通過創(chuàng)建唯一索引，可以保證數(shù)據(jù)庫表中每一條記錄的唯一性。
• 在實(shí)現(xiàn)數(shù)據(jù)的參照完整性方面，可以加速表和表之間的連接。
• 在使用分組和排序子句進(jìn)行數(shù)據(jù)查詢時(shí)，也可以顯著減少查詢中分組和排序的時(shí)間。

缺點(diǎn)

• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，并且隨著數(shù)據(jù)量的增加所耗費(fèi)的時(shí)間也會增加。
• 索引也需要占磁盤空間的，創(chuàng)建的索引太多，索引文件也會占用一定的存儲空間。
• 當(dāng)對表中的數(shù)據(jù)進(jìn)行增加、刪除、修改時(shí)，索引也需要動態(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。

創(chuàng)建索引的原則

索引設(shè)計(jì)不合理或缺少索引都會對數(shù)據(jù)庫和應(yīng)用程序的性能造成障礙，高效的索引對于獲得良好的性能非常重要。

需要創(chuàng)建索引的情況

• 主外鍵和唯一約束的字段自動創(chuàng)建索引
• 頻繁作為查詢條件的字段應(yīng)該創(chuàng)建索引
• 查詢中排序的字段應(yīng)該創(chuàng)建索引
• 查詢中分組或統(tǒng)計(jì)的字段應(yīng)該創(chuàng)建索引

不需要創(chuàng)建索引的情況

• 表中記錄太少不需要創(chuàng)建索引
• 需要頻繁增刪改的字段不適合創(chuàng)建索引
• where子句中用不到的字段不需要創(chuàng)建索引
• 重復(fù)值較多的字段不需要創(chuàng)建索引

索引的結(jié)構(gòu)

索引是在存儲引擎中實(shí)現(xiàn)的，使用不同的存儲引擎，所支持的索引也是不同的。

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

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

MySQL的InnoDB存儲引擎是支持hash索引的，不過我們必須啟用，hash索引的創(chuàng)建由InnoDB存儲引擎自動優(yōu)化創(chuàng)建，我們干預(yù)不了。

索引的類型

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

• 普通索引：最基本的索引，沒有任何限制
• 唯一索引：索引字段的取值不能重復(fù)，可以有空值，但空值也只能出現(xiàn)一次。
• 主鍵索引：索引字段的取值不能為空，也不能重復(fù)。
• 組合索引：一個(gè)索引包含多個(gè)字段，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段，索引才會被使用。
• 全文索引：通過關(guān)鍵字符，就能找到該字段所屬的記錄行。僅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT類型的字段上使用。
• 空間索引：對空間數(shù)據(jù)類型（GEOMETRY、POINT、LINESTRING、POLYGON）的字段建立的索引，僅限MyISAM引擎，且要求索引字段的取值不能為空。

索引的操作

實(shí)際上索引也是一張表，創(chuàng)建索引時(shí)，數(shù)據(jù)庫管理系統(tǒng)會在本地磁盤建立索引文件，里面保存了索引字段，并指向?qū)嶓w表的記錄。

創(chuàng)建索引

創(chuàng)建表的同時(shí)須指定索引名、表名和字段名。 語法：

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

自動創(chuàng)建索引

• 在表中定義了主鍵約束時(shí)，會自動創(chuàng)建一個(gè)對應(yīng)的主鍵索引。
• 在表中定義了外鍵約束時(shí)，會自動創(chuàng)建一個(gè)對應(yīng)的普通索引。
• 在表中定義了唯一約束時(shí)，會自動創(chuàng)建一個(gè)對應(yīng)的唯一索引。

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

mysql> create index job_index on emp(job);

查看索引

語法：

show index from <表名>;

示例：查看emp表中的索引

使用索引

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

刪除索引

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

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

示例：

——熱門課程推薦：

想學(xué)習(xí)PYTHON數(shù)據(jù)分析與金融數(shù)字化轉(zhuǎn)型精英訓(xùn)練營，您可以點(diǎn)擊>>>“人才轉(zhuǎn)型”了解課程詳情；

想從事業(yè)務(wù)型數(shù)據(jù)分析師，您可以點(diǎn)擊>>>“數(shù)據(jù)分析師”了解課程詳情；

想從事大數(shù)據(jù)分析師，您可以點(diǎn)擊>>>“大數(shù)據(jù)就業(yè)”了解課程詳情；

想成為人工智能工程師，您可以點(diǎn)擊>>>“人工智能就業(yè)”了解課程詳情；

想了解Python數(shù)據(jù)分析，您可以點(diǎn)擊>>>“Python數(shù)據(jù)分析師”了解課程詳情；

想咨詢互聯(lián)網(wǎng)運(yùn)營，你可以點(diǎn)擊>>>“互聯(lián)網(wǎng)運(yùn)營就業(yè)班”了解課程詳情； 

想了解更多優(yōu)質(zhì)課程，請點(diǎn)擊>>>