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

優(yōu)化簡(jiǎn)介

優(yōu)化數(shù)據(jù)庫(kù)是數(shù)據(jù)庫(kù)管理員和數(shù)據(jù)庫(kù)開發(fā)人員的必備技能。MySQL優(yōu)化，一方面是找出系統(tǒng)瓶頸，提高M(jìn)ySQL數(shù)據(jù)庫(kù)整體的性能；另一方面需要合理的結(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ǔ)句等使查詢響應(yīng)更快。

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

語(yǔ)法：

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ù)庫(kù)中最頻繁的操作，提高查詢速度可以有效地提高M(jìn)ySQL數(shù)據(jù)庫(kù)的性能。

分析查詢語(yǔ)句

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

MySQL中提供了EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句來(lái)分析查詢語(yǔ)句。

語(yǔ)法：

EXPLAIN/DESCRIBE [EXTENDED] SELECT select_options

示例：

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

索引對(duì)查詢速度的影響

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

索引簡(jiǎn)介

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

索引的意義

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

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

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

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

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

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

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

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

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

缺點(diǎn)

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

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

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

需要?jiǎng)?chuàng)建索引的情況

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

不需要?jiǎng)?chuàng)建索引的情況

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

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

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

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

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

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

索引的類型

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

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

索引的操作

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

創(chuàng)建索引

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

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

自動(dòng)創(chuàng)建索引

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

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

mysql> create index job_index on emp(job);

查看索引

語(yǔ)法：

show index from <表名>;

示例：查看emp表中的索引

使用索引

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

刪除索引

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

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

示例：

——熱門課程推薦：

想學(xué)習(xí)PYTHON數(shù)據(jù)分析與金融數(shù)字化轉(zhuǎn)型精英訓(xùn)練營(yíng)，您可以點(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)營(yíng)，你可以點(diǎn)擊>>>“互聯(lián)網(wǎng)運(yùn)營(yíng)就業(yè)班”了解課程詳情； 

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