???????MySQL 是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，可以從不同的層面進行優(yōu)化以提高系統(tǒng)的性能和效率。下面就是從系統(tǒng)設(shè)計層面、軟件層面、SQL層面的一些優(yōu)化建議。優(yōu)化MySql可以從減輕數(shù)據(jù)庫壓力、提高配置、提高查詢效率等方面入手。

系統(tǒng)層面優(yōu)化

采用分布式架構(gòu)

???????如果單臺服務(wù)器無法滿足需求，可以采用分布式架構(gòu)來提高 MySQL 的性能。常用的分布式架構(gòu)包括主從復(fù)制、讀寫分離等。

擴展

1. 主從復(fù)制:主從復(fù)制是指將一個 MySQL 實例（主庫）中的數(shù)據(jù)復(fù)制到其他 MySQL 實例（從庫）中，從庫中的數(shù)據(jù)與主庫中的數(shù)據(jù)保持一致。主從復(fù)制的主要優(yōu)點是：

• 提高系統(tǒng)的可用性，當主庫故障時，可以快速切換到從庫繼續(xù)提供服務(wù)；
• 分擔(dān)主庫的負載，可以將讀請求分發(fā)到從庫中處理，減輕主庫的負擔(dān)。

2. 讀寫分離:讀寫分離是指將讀請求和寫請求分別分發(fā)到不同的 MySQL 實例中處理。寫請求只發(fā)送到主庫中，而讀請求則發(fā)送到從庫中。讀寫分離的主要優(yōu)點是：

• 分擔(dān)主庫的負載，將讀請求和寫請求分別分發(fā)到不同的 MySQL 實例中處理，減輕主庫的負
• 提高系統(tǒng)的讀性能，將讀請求分發(fā)到從庫中處理，減輕主庫的負擔(dān)；

使用緩存

???????使用緩存優(yōu)化 MySQL 可以大幅提升系統(tǒng)性能，減少數(shù)據(jù)庫的壓力。

常用的緩存方案有以下幾種：

• 前端緩存

???????前端緩存是指將數(shù)據(jù)緩存在客戶端（如瀏覽器）中，減少服務(wù)器端的請求。前端緩存可以通過 HTTP 緩存頭來實現(xiàn)，例如，可以設(shè)置 Cache-Control、Expires、Last-Modified 等緩存頭。

• 應(yīng)用程序緩存

???????應(yīng)用程序緩存是指將數(shù)據(jù)緩存在應(yīng)用程序的內(nèi)存中，減少對數(shù)據(jù)庫的訪問。應(yīng)用程序緩存可以使用一些緩存框架來實現(xiàn)，例如，Redis、Memcached 等。在使用應(yīng)用程序緩存時需要注意緩存數(shù)據(jù)的有效期，避免緩存數(shù)據(jù)過期或失效。

• 數(shù)據(jù)庫緩存

???????數(shù)據(jù)庫緩存是指將數(shù)據(jù)緩存在數(shù)據(jù)庫的內(nèi)存中，減少磁盤 I/O 的訪問。數(shù)據(jù)庫緩存可以使用 MySQL 內(nèi)置的緩存機制來實現(xiàn)，例如，使用 Query Cache、InnoDB Buffer Pool 等。

擴展:使用緩存優(yōu)化 MySQL 需要注意以下幾點：

1. 緩存數(shù)據(jù)的有效期，避免緩存數(shù)據(jù)過期或失效。
2. 緩存數(shù)據(jù)的一致性，需要確保緩存數(shù)據(jù)與數(shù)據(jù)庫中的數(shù)據(jù)保持一致。
3. 緩存數(shù)據(jù)的大小，需要根據(jù)系統(tǒng)的需求和硬件資源來確定緩存的大小。
4. 緩存數(shù)據(jù)的并發(fā)訪問，需要考慮多線程并發(fā)訪問時的鎖競爭問題。
5. 緩存的選擇，需要根據(jù)系統(tǒng)的需求和硬件資源來選擇合適的緩存方案，例如，前端緩存、應(yīng)用程序緩存、數(shù)據(jù)庫緩存等。

使用搜索引擎

???????使用 Elasticsearch 進行查詢可以加速查詢操作，并且可以提供強大的搜索和分析功能。

注意??

• 在使用 Elasticsearch 進行查詢時，需要注意 Elasticsearch 和 MySQL 之間的數(shù)據(jù)一致性問題。由于 Elasticsearch 中的數(shù)據(jù)可能會有延遲，因此需要考慮如何處理數(shù)據(jù)同步和數(shù)據(jù)一致性問題。
• 需要注意 Elasticsearch 和 MySQL 之間的數(shù)據(jù)一致性問題。由于 Elasticsearch 中的數(shù)據(jù)可能會有延遲，因此需要考慮如何處理數(shù)據(jù)同步和數(shù)據(jù)一致性問題。

軟件層面優(yōu)化

調(diào)整 MySQL 參數(shù)配置

???????通過修改 MySQL 的配置參數(shù)來優(yōu)化 MySQL 的性能，例如，修改緩沖池的大小、修改連接數(shù)的數(shù)量、緩存池大小等等。

擴展

• innodb_buffer_pool_size：這個參數(shù)配置了 InnoDB 存儲引擎使用的內(nèi)存池的大小，可以用來控制 InnoDB 存儲引擎的緩存區(qū)大小。一般來說，innodb_buffer_pool_size 的大小應(yīng)該是系統(tǒng)內(nèi)存的 50%~70%。
• innodb_log_file_size：這個參數(shù)配置了 InnoDB 存儲引擎的日志文件大小，可以影響到事務(wù)的提交速度和數(shù)據(jù)恢復(fù)速度。一般來說，innodb_log_file_size 的大小應(yīng)該是 1GB~2GB 左右。
• query_cache_size：這個參數(shù)配置了查詢緩存的大小，可以提高查詢的速度。但需要注意，使用查詢緩存會增加 MySQL 服務(wù)器的 CPU 負載，并且會占用額外的內(nèi)存，因此需要根據(jù)實際情況來決定是否啟用查詢緩存。
• max_connections：這個參數(shù)配置了 MySQL 服務(wù)器的最大連接數(shù)，可以控制 MySQL 服務(wù)器的并發(fā)連接數(shù)。需要根據(jù)實際情況來決定 max_connections 的大小，一般來說，max_connections 的值應(yīng)該大于服務(wù)器上同時在線的最大連接數(shù)。
• key_buffer_size：這個參數(shù)配置了 MyISAM 存儲引擎的索引緩存的大小，可以提高查詢的速度。但需要注意，MyISAM 存儲引擎的索引緩存只能緩存索引，無法緩存表數(shù)據(jù)，因此只有在使用 MyISAM 存儲引擎時才需要配置 key_buffer_size。

定期清理無用數(shù)據(jù)

???????定期清理無用數(shù)據(jù)可以幫助我們減少數(shù)據(jù)存儲空間，提高數(shù)據(jù)庫的性能，以及減少備份和恢復(fù)數(shù)據(jù)的時間和成本

擴展

• 清理日志文件：MySQL 中的錯誤日志、二進制日志和慢查詢?nèi)罩镜热罩疚募赡軙加么罅康拇鎯臻g，需要定期清理。
• 清理過期數(shù)據(jù)：在 MySQL 中，經(jīng)常會產(chǎn)生一些過期的數(shù)據(jù)，例如歷史數(shù)據(jù)、日志數(shù)據(jù)等，這些數(shù)據(jù)可以定期清理?？梢酝ㄟ^設(shè)置自動刪除或手動刪除的方式來清理過期數(shù)據(jù)。
• 清理未使用的表和索引：MySQL 中有些表和索引可能已經(jīng)不再使用，但仍然占用著存儲空間。可以通過查詢系統(tǒng)表來找出這些未使用的表和索引，然后進行清理。
• 清理無效的備份文件：MySQL 中的備份文件可能會占用大量的存儲空間，需要定期清理無效的備份文件，保留最新的有效備份文件。
• 優(yōu)化數(shù)據(jù)存儲方式：MySQL 中有很多數(shù)據(jù)存儲方式，例如 MyISAM、InnoDB、MEMORY 等，不同的存儲方式對空間的使用效率也不同?？梢愿鶕?jù)實際情況選擇合適的存儲方式，優(yōu)化數(shù)據(jù)的存儲方式。

創(chuàng)建索引

索引類似于字典的目錄，可以提高查詢的效率。

索引從物理上可以分為：聚集索引，非聚集索引

從邏輯上可以分為：普通索引，唯一索引，主鍵索引，聯(lián)合索引，全文索引

創(chuàng)建索引可以提高數(shù)據(jù)庫查詢性能，但是不是所有場景都適合創(chuàng)建索引。

創(chuàng)建索引

普通索引

適用場景：對于一些較小的表或者經(jīng)常需要進行查詢的表，可以使用普通索引來提高查詢效率。

CREATE INDEX idx_name ON table_name(column_name);

唯一索引

適用場景：當需要保證某個字段的唯一性時，可以使用唯一索引。

CREATE UNIQUE INDEX idx_name ON table_name(column_name);

全文索引

適用場景：當需要進行全文搜索時，可以使用全文索引。

CREATE FULLTEXT INDEX idx_name ON table_name(column_name);

組合索引

適用場景：當查詢條件中涉及多個字段時，可以使用組合索引來提高查詢效率。

CREATE INDEX idx_name ON table_name(column1, column2, ...);

空間索引

適用場景：當需要對地理位置進行查詢時，可以使用空間索引。

CREATE SPATIAL INDEX idx_name ON table_name(column_name);

主鍵索引

適用場景：當需要對某個字段進行唯一標識時，可以使用主鍵索引。

ALTER TABLE table_name ADD PRIMARY KEY(column_name);

外鍵索引

適用場景：當需要對表之間的關(guān)聯(lián)進行查詢時，可以使用外鍵索引。

ALTER TABLE table_name ADD FOREIGN KEY(column_name) REFERENCES ref_table(ref_column);

索引前綴

適用場景：當某個字段值的長度較長，可以使用索引前綴來提高查詢效率。

注意??：使用索引前綴時，可能會導(dǎo)致索引失效或者查詢結(jié)果不準確，需要根據(jù)實際情況進行選擇。

CREATE INDEX idx_name ON table_name(column_name(prefix_length));

適合創(chuàng)建索引的場景

• 經(jīng)常用作查詢條件的列:如果一個列經(jīng)常用作查詢條件，那么為這個列創(chuàng)建索引可以提高查詢性能。例如，用戶表中的用戶名、郵箱、手機號等列。
• 經(jīng)常被用于連接的列:如果一個列經(jīng)常被用于連接多個表，那么為這個列創(chuàng)建索引可以提高連接查詢的性能。例如，在一個訂單表和商品表中，訂單表中的商品編號和商品表中的商品編號經(jīng)常被用于連接查詢。
• 經(jīng)常被用于排序的列:如果一個列經(jīng)常被用于排序，那么為這個列創(chuàng)建索引可以提高排序查詢的性能。例如，新聞網(wǎng)站中的文章發(fā)布時間、評論數(shù)等列。
• 經(jīng)常被用于分組的列:如果一個列經(jīng)常被用于分組，那么為這個列創(chuàng)建索引可以提高分組查詢的性能。例如，在一個銷售數(shù)據(jù)表中，按照產(chǎn)品類別分組的查詢。
• 大表中的常用列:在大表中，查詢性能通常較差。為了提高查詢性能，可以為常用的列創(chuàng)建索引，例如，大表中的訂單號、產(chǎn)品編號等。

不適合創(chuàng)建索引的場景

• 列的數(shù)據(jù)分布不均勻:如果一個列的數(shù)據(jù)分布不均勻，那么為這個列創(chuàng)建索引可能會降低查詢性能，因為數(shù)據(jù)庫查詢優(yōu)化器會認為使用索引查詢的代價比全表掃描高。例如，在一個性別列中，如果大部分數(shù)據(jù)是男性，那么為這個列創(chuàng)建索引可能會降低查詢性能。
• 小表:如果一個表很小，那么為這個表創(chuàng)建索引的代價可能比使用全表掃描更高，因為查詢優(yōu)化器需要額外的代價來使用索引。通常情況下，小于1000行的表不適合創(chuàng)建索引。
• 經(jīng)常更新的列:如果一個列經(jīng)常被更新，那么為這個列創(chuàng)建索引可能會降低數(shù)據(jù)庫寫入性能，因為每次更新操作都需要更新索引。例如，一個日志表中的時間戳列。
• 數(shù)據(jù)類型是 TEXT、BLOB 等:一般對于數(shù)據(jù)類型是 TEXT、BLOB 等的列，創(chuàng)建索引的代價非常高，因為這些列的數(shù)據(jù)比較大，索引也會很大，導(dǎo)致查詢性能下降。

優(yōu)化表結(jié)構(gòu)

• 避免使用過大的字段類型，可以使用合適的數(shù)據(jù)類型來減少存儲空間的浪費；
• 避免使用 NULL，因為 NULL 會增加存儲空間和查詢復(fù)雜度；
• 避免使用 BLOB 和 TEXT 類型的字段，因為這些字段會影響查詢性能；
• 對于大型的表，可以使用分區(qū)表來提高查詢性能。

分庫分表

???????MySQL 分庫分表是一種常用的數(shù)據(jù)庫水平擴展方式，可以有效地解決單個 MySQL 實例無法滿足高并發(fā)、海量數(shù)據(jù)存儲等需求的問題?？梢詫⒁粋€大型的數(shù)據(jù)庫拆分成多個小型的數(shù)據(jù)庫，每個小型數(shù)據(jù)庫中包含一部分數(shù)據(jù)。同時，可以將一個大型的表拆分成多個小型的表，每個小型表中包含一部分數(shù)據(jù)。這種方式可以解決單一數(shù)據(jù)庫或表過大導(dǎo)致的性能瓶頸問題，提高系統(tǒng)的可擴展性和可用性。

擴展

分庫分表的具體實現(xiàn)方法有以下幾種：

• 垂直分庫

???????垂直分庫是將一個大型的數(shù)據(jù)庫拆分成多個小型的數(shù)據(jù)庫，每個小型數(shù)據(jù)庫中包含一部分相關(guān)的表。例如，可以將一個電商系統(tǒng)中的用戶表、訂單表、商品表等拆分成多個小型數(shù)據(jù)庫，每個小型數(shù)據(jù)庫中只包含一部分相關(guān)的表。垂直分庫的優(yōu)點是易于管理，每個小型數(shù)據(jù)庫中包含的表都具有相似的特點。缺點是可能會導(dǎo)致數(shù)據(jù)不一致，例如，當一個表的數(shù)據(jù)需要更新時，可能需要在多個小型數(shù)據(jù)庫中進行更新操作。

• 水平分庫

???????水平分庫是將一個大型的數(shù)據(jù)庫中的表按照某種規(guī)則分散到多個小型數(shù)據(jù)庫中，每個小型數(shù)據(jù)庫中包含一部分表。例如，可以將一個電商系統(tǒng)中的訂單表按照訂單號的范圍分散到多個小型數(shù)據(jù)庫中，每個小型數(shù)據(jù)庫中包含一部分訂單數(shù)據(jù)。水平分庫的優(yōu)點是易于擴展，可以將新的小型數(shù)據(jù)庫添加到系統(tǒng)中。缺點是可能會導(dǎo)致數(shù)據(jù)不一致，例如，當一個表的數(shù)據(jù)需要更新時，可能需要在多個小型數(shù)據(jù)庫中進行更新操作。

• 水平分表

???????水平分表是將一個大型的表按照某種規(guī)則分散到多個小型表中，每個小型表中包含一部分數(shù)據(jù)。例如，可以將一個電商系統(tǒng)中的訂單表按照訂單號的范圍分散到多個小型表中，每個小型表中包含一部分訂單數(shù)據(jù)。水平分表的優(yōu)點是易于擴展，可以將新的小型表添加到系統(tǒng)中。缺點是可能會導(dǎo)致查詢性能下降，因為查詢可能需要在多個小型表中進行。

SQL優(yōu)化

explain執(zhí)行計劃

???????在看具體SQL優(yōu)化之前，可以先了解一下explain執(zhí)行計劃，使用 EXPLAIN 命令來獲取一個 SQL 查詢語句的執(zhí)行計劃。執(zhí)行計劃描述了 MySQL 數(shù)據(jù)庫系統(tǒng)如何執(zhí)行查詢，并且可以用來分析和優(yōu)化查詢語句。

樣本sql

CREATE TABLE students (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);
ALTER TABLE students ADD UNIQUE INDEX email_UNIQUE (email);
#explain執(zhí)行計劃
EXPLAIN select * from students where email ! "123";

執(zhí)行結(jié)果

字段含義

1. id：查詢的唯一標識符。如果查詢包含子查詢，則每個子查詢都有一個唯一標識符。

2. select_type：查詢的類型，例如 SIMPLE（簡單查詢）、PRIMARY（主查詢）或 UNION（聯(lián)合查詢）等。

3. table：查詢的表名。

4. partitions：查詢的分區(qū)。

5. type:連接類型【SQL 性能優(yōu)化的目標：至少要達到 range 級別，要求是 ref 級別，如果可以是

   consts 最好?！?執(zhí)行性能排名:system > const > eq_ref > ref > range > index > all。)

   • system：表僅有一行，基本用不到；

   • const：表最多一行數(shù)據(jù)配合，主鍵查詢時觸發(fā)較多；

   • eq_ref：對于每個來自于前面的表的行組合，從該表中讀取一行。這可能是最好的聯(lián)接類型，除了const類型；

   • ref：對于每個來自于前面的表的行組合，所有有匹配索引值的行將從這張表中讀取；

   • range：只檢索給定范圍的行，使用一個索引來選擇行。當使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比較關(guān)鍵字列時，可以使用range；

   • index：該聯(lián)接類型與ALL相同，除了只有索引樹被掃描。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件??；

   • all：全表掃描；

6. possible_keys：查詢可能使用的索引列表。

7. key：查詢實際使用的索引。

8. key_len：索引的長度。

9. ref：使用索引的列或常數(shù)。

10. rows：MySQL 估計將會掃描的行數(shù)。

11. filtered：使用條件過濾后，剩余的行數(shù)百分比。

12. Extra：額外的信息，例如使用了哪些索引或排序方式等。

    • Using index：只從索引樹中獲取信息，而不需要回表查詢；

    • Using where：WHERE子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。除非你專門從表中索取或檢查所有行，如果Extra值不為Using where并且表聯(lián)接類型為ALL或index，查詢可能會有一些錯誤。需要回表查詢。

    • Using temporary：mysql常建一個臨時表來容納結(jié)果，典型情況如查詢包含可以按不同情況列出列的GROUP BY和ORDER BY子句時；

Select 優(yōu)化

避免 SELECT * 查詢，只查需要的字段

1. 性能問題：使用"SELECT字段"可以提高查詢性能。當使用"SELECT *"時，MySQL需要檢索表中的所有字段，包括不需要的字段，這會占用更多的系統(tǒng)資源和時間。而使用"SELECT字段"可以減少檢索的數(shù)據(jù)量，從而提高查詢性能。
2. 可讀性和維護性問題：使用"SELECT字段"可以提高查詢的可讀性和維護性。當使用"SELECT *"時，查詢結(jié)果中的字段順序可能會發(fā)生變化，這會給程序員帶來一定的困擾。而使用"SELECT字段"可以明確指定查詢的字段，使得查詢結(jié)果的字段順序和查詢語句中的字段順序一致，更易于程序員理解和維護。
3. 安全問題：使用"SELECT字段"可以提高查詢的安全性。當使用"SELECT *"時，如果表結(jié)構(gòu)發(fā)生變化，可能會將不需要的字段暴露給外部，這會給系統(tǒng)帶來潛在的安全風(fēng)險。而使用"SELECT字段"可以避免暴露不需要的字段，從而提高查詢的安全性。

盡量避免or查詢

or沒有索引的字段會走全表查詢，有必要時可以讓拆成多條sql，讓沒有索引的字段and有索引的字段

如a，c字段有單值索引

#這條語句走全表掃描
select * from test where a = "xxx" or b = "xxx";

可以優(yōu)化成下面語句

#分開查詢后續(xù)合并結(jié)果
select * from test where a = "xxx";
select * from test where c = "xxx" and b = "xxx";

#或者單次查詢
select * from test where a = "xxx" or a in (select a from test where c = "xxx" and b = "xxx";);

使用 UNION ALL 替代 UNION

???????因為 UNION ALL 不會去重，速度更快，UNION 會去重，即將兩個結(jié)果集中相同的記錄合并成一條記錄。如果我們確定合并的結(jié)果集中不會出現(xiàn)重復(fù)記錄，那么我們可以使用 UNION ALL 來代替 UNION 操作。

注意??:使用 UNION ALL 可能會增加網(wǎng)絡(luò)傳輸?shù)臄?shù)據(jù)量，因為結(jié)果集中可能會有重復(fù)的記錄。因此，我們需要在實際應(yīng)用中根據(jù)具體情況來選擇使用 UNION 還是 UNION ALL。

避免使用子查詢，可以改成 JOIN

• 執(zhí)行子查詢時, MYSQL需要為內(nèi)層查詢語句的查詢結(jié)果建立一個臨時表。然后外層查詢語句從臨時表中查詢記錄。查詢完畢后,再撤銷這些臨時表。連接(JOIN)之所以更有效率一些，是因為MySQL不需要在內(nèi)存中創(chuàng)建臨時表來完成這個邏輯
• 更容易優(yōu)化查詢：使用 JOIN 操作可以更好地利用索引，提高查詢的效率。
• 可讀性更好：使用 JOIN 操作可以使 SQL 語句更加清晰易懂，降低出錯的概率。
• 子查詢嵌套層數(shù)過多會影響可維護性：使用 JOIN 操作可以將查詢拆分成多個表，易于維護和優(yōu)化。

有必要時在應(yīng)用層 ORDER BY 和 GROUP BY

???????避免使用不必要的 ORDER BY 和 GROUP BY 操作，可以在應(yīng)用層進行排序和分組。

1. 減少數(shù)據(jù)庫負擔(dān)：ORDER BY 和 GROUP BY 操作需要對結(jié)果集進行排序或分組，會增加數(shù)據(jù)庫的負擔(dān)，特別是當結(jié)果集非常大時，效率會更低。而在應(yīng)用層進行排序或分組可以減輕數(shù)據(jù)庫的壓力。
2. 靈活性更高：在應(yīng)用層進行排序或分組可以更加靈活地控制結(jié)果集的處理方式，可以根據(jù)具體需求進行自定義排序或分組操作。

避免使用 LIKE ‘%value%’ 或 LIKE ‘%value’

???????索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。以通配符（%）開頭的搜索字符串會強制數(shù)據(jù)庫系統(tǒng)進行全表掃描。

???????盡可能使用前綴搜索：如果可以使用前綴搜索而不是在字符串的任意位置進行匹配，那么查詢將更加高效。例如，LIKE ‘value%’ 比 LIKE ‘%value%’ 更加高效

如果非要使用 LIKE ‘%value%’ 或 LIKE ‘%value’，可以采取放在語句末、搜索引擎、離線數(shù)據(jù)倉庫

• 放在帶索引查詢的語句末:將 LIKE 語句放在 WHERE 子句的末尾可以避免在查詢的前面進行全表掃描。(如果查詢條件前面的字段是索引查詢，在執(zhí)行l(wèi)ike的時候會縮減范圍使得查詢避免走全表查詢)【注意??:如果索引查詢后的數(shù)據(jù)量依舊很大，不建議使用!】
• 搜索引擎:可以使用 Elasticsearch來進行模糊查詢，效率更高
• 離線數(shù)據(jù)倉庫:如Hive，有些場景可以使用，查詢時長可能會有點久且不能保證實時性
• 使用 REVERSE() 函數(shù)：可以先將需要查詢的值 value 進行反轉(zhuǎn)，然后將查詢條件改寫為 LIKE reverse('value')%'，這樣可以利用索引加速查詢，避免全表掃描。

-- 原查詢：
SELECT * FROM my_table WHERE my_column LIKE '%abc';

-- 優(yōu)化后的查詢：
SELECT * FROM my_table WHERE REVERSE(my_column) LIKE REVERSE('cba')%;

避免在索引字段上使用函數(shù)

???????在索引字段上運用了函數(shù)，導(dǎo)致索引失效。B+ 樹提供索引的快速定位能力，來源于同一層兄弟節(jié)點的有序性。也就是說，對索引字段做函數(shù)操作，可能會破壞索引值的有序性，因此優(yōu)化器就決定放棄走樹搜索功能?？梢栽诓樵兒髮Y(jié)果數(shù)據(jù)字段進行函數(shù)操作。

避免隱式類型轉(zhuǎn)換

隱式類型轉(zhuǎn)換會導(dǎo)致索引失效

#如order_id是varchar類型索引字段，查詢時用的整數(shù)，底層查詢時會做類型轉(zhuǎn)換
select * from test where order_id = 110717;

表連接時候用小數(shù)據(jù)集驅(qū)動大數(shù)據(jù)集

???????小表驅(qū)動大表可以減少不必要的表連接，從而達到優(yōu)化效果

擴展:
???????嵌套循環(huán)聯(lián)接（NLJ）算法：循環(huán)從第一個表中依次讀取行，取到每行再到聯(lián)接的下一個表中循環(huán)匹配。這個過程會重復(fù)多次直到剩余的表都被聯(lián)接了。通過外循環(huán)的行去匹配內(nèi)循環(huán)的行，所以內(nèi)循環(huán)的表會被掃描多次。(可以理解為外層循環(huán)一次就是一次表連接，以小表1萬條，大表1000萬條來對比，小表驅(qū)動大表來算只需要1萬次表連接)

for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions,
          send to client
    }
  }
}

避免在索引字段使用不等值操作(!=、<>)

???????注意??:當不等值操作作用在普通索引上時，可能索引失效

以下是一些常見情況下不等值操作導(dǎo)致索引失效的情況：

1. 數(shù)據(jù)類型不匹配：如果不等值操作涉及到不同數(shù)據(jù)類型的比較，例如將整數(shù)列與字符串列進行比較，MySQL 通常無法使用索引。
2. 函數(shù)或表達式操作：如果不等值操作中使用了函數(shù)或表達式來處理列，例如WHERE LENGTH(column_name) != 5，MySQL 通常無法使用索引，因為它不能對列進行函數(shù)計算。
3. OR 連接多個不等值條件：如果你使用 OR 連接多個不等值條件，MySQL 可能會選擇不使用索引，因為 OR 連接通常難以優(yōu)化。例如，WHERE column_name != 'value1' OR column_name != 'value2' 可能會導(dǎo)致索引失效。
4. 使用 IS NULL 或 IS NOT NULL：當你使用 IS NULL 或 IS NOT NULL 條件時，索引可能會失效，因為這些條件與普通的比較條件不同。
5. 索引選擇性低：索引選擇性指的是索引中不同值的數(shù)量與總行數(shù)的比例。如果索引的選擇性很低，即索引列中的不同值很少，那么 MySQL 可能會選擇不使用索引，因為它認為全表掃描更有效。
6. 小表：如果表本身很小，MySQL 可能會選擇進行全表掃描而不是使用索引，因為在這種情況下索引的開銷可能會超過全表掃描的開銷。

inner join 、left join、right join選擇

• Inner join 只返回兩個表中匹配的記錄，不包含左表或右表中沒有匹配到的記錄。當需要查詢兩個表中的公共數(shù)據(jù)時，應(yīng)該優(yōu)先使用inner join。
• Left join 返回左表中所有的記錄，以及左表和右表中匹配的記錄。如果右表中沒有匹配到左表中的記錄，則返回null值。當需要查詢左表的所有記錄，并且只需要匹配右表中的一部分記錄時，可以使用left join。
• Right join 返回右表中所有的記錄，以及左表和右表中匹配的記錄。如果左表中沒有匹配到右表中的記錄，則返回null值。當需要查詢右表的所有記錄，并且只需要匹配左表中的一部分記錄時，可以使用right join。

先過濾，再GROUP BY、ORDER BY

???????在SQL查詢語句中，過濾（Filtering）、分組（Grouping）和排序（Sorting）是常見的操作。正確的順序應(yīng)該是先過濾、再分組、最后排序。

1. 減少操作的數(shù)據(jù)量,如果不先過濾數(shù)據(jù)，查詢引擎可能需要對整個數(shù)據(jù)集進行聚合操作，這會消耗大量的時間和計算資源。
2. 提高查詢效率,如果不先過濾數(shù)據(jù)，查詢引擎需要對大量無用的數(shù)據(jù)進行排序或分組操作，這會降低查詢效率。
3. 優(yōu)化查詢計劃，查詢引擎可以通過優(yōu)化查詢計劃，選擇更高效的執(zhí)行計劃來執(zhí)行查詢。如果不先過濾數(shù)據(jù)，查詢引擎可能無法有效地優(yōu)化查詢計劃，導(dǎo)致查詢效率低下。

查詢時對null數(shù)據(jù)處理

???????使用 IS NULL 或 IS NOT NULL 進行判斷：在 WHERE 子句中使用 IS NULL 或 IS NOT NULL 來判斷某個列是否為 NULL 值，可以避免使用等于號（=）進行判斷時可能出現(xiàn)的索引失效情況。

???????使用 COALESCE 函數(shù)替換 NULL 值：在 SELECT 子句中使用 COALESCE 函數(shù)可以將 NULL 值替換為其他指定的值，例如：

#如果 column_name 列的值為 NULL，則查詢結(jié)果中將顯示 'default_value'。
SELECT COALESCE(column_name, 'default_value') AS column_alias FROM table_name;

???????使用 IFNULL 或 NVL 函數(shù)替換 NULL 值：在一些數(shù)據(jù)庫中，也可以使用 IFNULL 或 NVL 函數(shù)來替換 NULL 值，例如：

#如果 column_name 列的值為 NULL，則查詢結(jié)果中將顯示 'default_value'。
SELECT IFNULL(column_name, 'default_value') AS column_alias FROM table_name;

4. 在索引中使用 IS NULL 進行查詢：對于包含大量 NULL 值的列，可以使用包含 IS NULL 的查詢條件來進行索引查詢。這樣可以避免對 NULL 值進行排序或聚合操作，提高查詢效率。

限制返回條數(shù)

???????有必要時限制返回結(jié)果的數(shù)量：在查詢中使用 LIMIT 語句可以限制返回結(jié)果的數(shù)量，只拿取所需要的數(shù)據(jù)，減少數(shù)據(jù)傳輸和處理的時間。

好處

• 減少網(wǎng)絡(luò)傳輸開銷：查詢結(jié)果集非常大，MySQL需要將全部結(jié)果傳輸給客戶端，這會導(dǎo)致網(wǎng)絡(luò)傳輸開銷增加。通過限制返回條數(shù)，可以減少網(wǎng)絡(luò)傳輸開銷。
• 避免客戶端內(nèi)存溢出：查詢結(jié)果集非常大，客戶端需要占用大量的內(nèi)存來存儲結(jié)果集，這可能導(dǎo)致內(nèi)存溢出。通過限制返回條數(shù)，可以避免客戶端內(nèi)存溢出。
• 提高用戶體驗：查詢結(jié)果集非常大，用戶需要等待很長時間才能看到結(jié)果。通過限制返回條數(shù)，可以提高用戶體驗，讓用戶更快地看到部分結(jié)果，從而決定是否需要進一步查詢。
• 提高查詢效率：查詢結(jié)果集非常大，MySQL需要耗費大量的時間和資源來返回全部結(jié)果。通過限制返回條數(shù)，可以減少MySQL的負擔(dān)，提高查詢效率。

明知結(jié)果返回一條記錄，可以加limit1

???????當我們明確知道某個查詢的結(jié)果只會返回一條記錄時，可以添加 LIMIT 1 語句，讓MySQL停止游標移動，來提高查詢效率和減少資源消耗。這是因為 LIMIT 1 會讓 MySQL 在找到第一條匹配記錄之后就停止搜索，而不會繼續(xù)掃描整個表，從而可以更快地返回結(jié)果。

分頁查詢優(yōu)化

???????當進行分頁時，MySQL 并不是跳過 offset 行，而是取 offset+N 行，然后放棄前 offset 行，返回 N 行。例如 limit 10000, 20。mysql排序取出10020條數(shù)據(jù)后，僅返回20條數(shù)據(jù)，查詢和排序的代價都很高。那當 offset 特別大的時候，效率就非常的低下，所以我們要對sql進行改寫

使用書簽

???????用書簽記錄上次取數(shù)據(jù)的位置，過濾掉部分數(shù)據(jù).

如下面語句

#改進前
SELECT id, name, description FROM film ORDER BY name LIMIT 1000, 10;

#改進后
#name為上次查詢后的最大值，注意這種場景只適用于不存在重復(fù)值的場景。
SELECT id, name, description FROM film WHERE name > 'begin' ORDER BY name LIMIT 10;

延遲關(guān)聯(lián)

???????延遲關(guān)聯(lián)：通過使用覆蓋索引查詢返回需要的主鍵，再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù).MySQL 延遲關(guān)聯(lián)（Delayed Join）是一種優(yōu)化技術(shù)，可以提高查詢性能。當查詢需要關(guān)聯(lián)多個表時，延遲關(guān)聯(lián)可以將一些關(guān)聯(lián)操作推遲到后面的查詢中，從而減少關(guān)聯(lián)操作的數(shù)量，提高查詢性能。

#id是主鍵值，name上面有索引。這樣每次查詢的時候，會先從name索引列上找到id值，然后回表，查詢到所有的數(shù)據(jù)。可以看到有很多回表其實是沒有必要的。完全可以先從name索引上找到id（注意只查詢id是不會回表的，因為非聚集索引上包含的值為索引列值和主鍵值，相當于從索引上能拿到所有的列值，就沒必要再回表了），然后再關(guān)聯(lián)一次表，獲取所有的數(shù)據(jù)
#改進前
SELECT id, name, description FROM film ORDER BY name LIMIT 100,5;

#改進后
SELECT film.id, name, description FROM film 
JOIN (SELECT id from film ORDER BY name LIMIT 100,5) temp
ON film.id = temp.id

倒序查詢

???????假如查詢倒數(shù)最后一頁，offset可能會非常大

#改進前
SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10;
#改進后
SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10;

Update優(yōu)化

使用或者涉及索引字段進行update

???????確保涉及到UPDATE語句的列上有索引，這樣可以大大減少查詢時間。特別是對于更新大表的操作。

使用批量更新

???????批量更新可以將多個單獨的UPDATE操作合并為一個，從而減少服務(wù)器和客戶端之間的通信次數(shù)。可以使用INSERT … ON DUPLICATE KEY UPDATE語句或使用UPDATE … WHERE語句，同時更新多行數(shù)據(jù)。

限制更新

• LIMIT限制:在UPDATE語句中使用LIMIT關(guān)鍵字來限制更新的行數(shù)，以避免對整個表進行更新。特別是當更新大表時，限制更新行數(shù)是非常必要的?！究梢栽趹?yīng)用層面進行開關(guān)控制，在更新出錯誤數(shù)據(jù)后及時停掉應(yīng)用，進行回滾】
• WHERE限制:限制更新行數(shù)可以避免意外或不正確的更新操作，例如當使用錯誤的 WHERE 子句時，可以防止更新整個表的數(shù)據(jù)。

避免使用子查詢

使用子查詢更新數(shù)據(jù)可能會導(dǎo)致以下問題：

• 不可預(yù)測性問題：當使用子查詢更新時，可能會發(fā)生數(shù)據(jù)不一致的情況。這是因為子查詢返回的結(jié)果可能會受到其他會話更新的影響，從而導(dǎo)致數(shù)據(jù)不一致的情況。
• 可讀性問題：使用子查詢的更新語句通常比較復(fù)雜，難以理解和維護，特別是在涉及多個子查詢的情況下。
• 性能問題：子查詢可能會導(dǎo)致 MySQL 執(zhí)行緩慢，特別是當數(shù)據(jù)量很大時。因為子查詢會逐行掃描數(shù)據(jù)表，并且每個子查詢都會消耗額外的資源。

???????可以使用 JOIN 或 EXISTS 進行相關(guān)的過濾和更新操作，這通常比子查詢更高效和可讀。

減少觸發(fā)器和外鍵

在 MySQL 中進行 UPDATE 操作時，可能會觸發(fā)與表相關(guān)的觸發(fā)器和外鍵，這可能會導(dǎo)致以下問題：

1. 性能問題：觸發(fā)器和外鍵需要額外的時間和資源來處理，這可能會導(dǎo)致 UPDATE 操作變得緩慢，并增加數(shù)據(jù)庫的負載。
   • 擴展1:當進行 UPDATE 操作時，MySQL 會檢查更新的數(shù)據(jù)是否涉及到任何觸發(fā)器或外鍵。如果存在相關(guān)的觸發(fā)器或外鍵，MySQL 需要執(zhí)行額外的邏輯來確保數(shù)據(jù)的完整性和一致性。這可能會導(dǎo)致額外的時間和資源開銷，從而導(dǎo)致 UPDATE 操作變得緩慢。
   • 擴展2:如果表中存在多個觸發(fā)器和外鍵，那么在進行 UPDATE 操作時，需要處理多個復(fù)雜的操作，從而增加代碼的復(fù)雜度和難度。這可能會導(dǎo)致數(shù)據(jù)庫負載的增加，影響數(shù)據(jù)庫性能。
2. 可維護性問題：如果表中存在多個觸發(fā)器和外鍵，那么在進行 UPDATE 操作時，可能需要處理多個復(fù)雜的操作，從而增加代碼的復(fù)雜度和難度。
3. 數(shù)據(jù)完整性問題：如果 UPDATE 操作中涉及到多個表或外鍵關(guān)系，那么觸發(fā)器和外鍵可能會對數(shù)據(jù)完整性產(chǎn)生影響。如果不正確地處理這些關(guān)系，可能會導(dǎo)致數(shù)據(jù)不一致或損壞。

Insert 優(yōu)化

批量插入數(shù)據(jù)

???????使用 INSERT INTO … VALUES (…) 語句單條插入數(shù)據(jù)的效率較低，可以使用 INSERT INTO … VALUES (…), (…), (…) 的語法批量插入多條數(shù)據(jù)，可以顯著提高插入數(shù)據(jù)的速度。

使用 LOAD DATA INFILE

???????如果需要插入大量數(shù)據(jù)，可以使用 LOAD DATA INFILE 語句從本地文件中快速地加載數(shù)據(jù)到 MySQL 數(shù)據(jù)庫中。這種方式比使用 INSERT INTO … VALUES (…) 的方式要快得多。

擴展：

以下是使用 LOAD DATA INFILE 的步驟：

1. 創(chuàng)建表：首先需要創(chuàng)建一個表，用于存儲要導(dǎo)入的數(shù)據(jù)。
2. 創(chuàng)建文件：創(chuàng)建一個包含要導(dǎo)入數(shù)據(jù)的文件。文件格式必須與 MySQL 表中列的順序一致，并使用特定的分隔符將列分開。
3. 使用 LOAD DATA INFILE：使用 LOAD DATA INFILE 語句將文件中的數(shù)據(jù)加載到 MySQL 表中。

例如，如果要將數(shù)據(jù)從名為 data.txt 的文件中加載到名為 mytable 的表中，可以使用以下 SQL 語句：

#指定文件路徑
LOAD DATA INFILE 'data.txt'
#指定要插入的表
INTO TABLE mytable
#指定列之間的分隔符
FIELDS TERMINATED BY ',' 
#指定每個列值的引號字符
ENCLOSED BY '"' 
#指定行分隔符
LINES TERMINATED BY '\n'

???????使用 LOAD DATA INFILE 時，需要確保 MySQL 用戶擁有從指定文件讀取數(shù)據(jù)的權(quán)限。如果需要從網(wǎng)絡(luò)中的文件中讀取數(shù)據(jù)，可以使用 MySQL 客戶端的 --local-infile 選項啟用從本地加載數(shù)據(jù)的功能。

大量更新數(shù)據(jù)時禁用索引

???????在進行大量數(shù)據(jù)插入時，MySQL 可能會花費很多時間來更新索引。如果可以接受數(shù)據(jù)插入完成后再創(chuàng)建索引，可以使用 ALTER TABLE … DISABLE KEYS 語句禁用索引，等數(shù)據(jù)插入完成后再重新啟用索引?！咀⒁??:當前可以在極少量查詢的情況下使用！否則在大數(shù)據(jù)量下會導(dǎo)致大量慢sql】

使用 REPLACE INTO

??????? REPLACE INTO 語句與 INSERT INTO 語句的區(qū)別在于，如果要插入的數(shù)據(jù)已經(jīng)存在，INSERT INTO 語句會產(chǎn)生重復(fù)記錄錯誤。而 REPLACE INTO 語句會自動更新原有的數(shù)據(jù)行，而不是產(chǎn)生錯誤。

REPLACE INTO 語句在以下情況下非常有用：

1. 當需要插入新數(shù)據(jù)時，如果表中已經(jīng)存在相同的數(shù)據(jù)，則用新的數(shù)據(jù)替換原有數(shù)據(jù)。
2. 當需要更新一行數(shù)據(jù)時，如果該行數(shù)據(jù)不存在，則插入新數(shù)據(jù)；如果該行數(shù)據(jù)已經(jīng)存在，則用新的數(shù)據(jù)替換原有數(shù)據(jù)。

大數(shù)據(jù)量減少自增主鍵的使用

???????如果表中存在自增主鍵，并且插入的數(shù)據(jù)量很大，那么每次插入數(shù)據(jù)時，MySQL 都需要重新計算自增主鍵的值，這可能會導(dǎo)致插入數(shù)據(jù)變慢?？梢钥紤]使用其他方式來生成主鍵，例如使用 UUID 或使用應(yīng)用程序生成主鍵。

Delete 優(yōu)化

推薦使用 TRUNCATE TABLE

???????如果您需要刪除整個表格中的所有行，則可以使用 TRUNCATE TABLE 命令。這比 DELETE 命令更快，并且可以在不影響系統(tǒng)性能的情況下刪除大量數(shù)據(jù)。

推薦使用 TRUNCATE TABLE 的原因有以下幾點：

1. TRUNCATE TABLE 比 DELETE 語句更快。這是因為 TRUNCATE TABLE 在刪除數(shù)據(jù)時不會記錄每一行的刪除操作，而是將整個表格直接清空，從而避免了一些額外的開銷和日志記錄。對于大型表格，TRUNCATE TABLE 可以顯著提高清空數(shù)據(jù)的速度。
2. TRUNCATE TABLE 比 DELETE 語句更安全。TRUNCATE TABLE 操作是原子性的，也就是說，在 TRUNCATE TABLE 運行過程中，如果出現(xiàn)故障或錯誤，整個操作會被回滾到原始狀態(tài)，從而保證了數(shù)據(jù)的一致性。相比之下，DELETE 語句需要一行一行地刪除數(shù)據(jù)，如果出現(xiàn)故障或錯誤，數(shù)據(jù)可能會被刪除一部分，但不會被完全清空。

推薦使用刪除標記

???????有些場景下需要讓某些數(shù)據(jù)不展示出去，可以使用一個標記來表示這個數(shù)據(jù)被刪除。例如可以使用date_delete字段，里面可以存儲刪除的時間戳或者0、1等，在查詢的時候進行過濾。文章來源地址http://www.zghlxwxcb.cn/news/detail-815933.html

到了這里，關(guān)于萬字長文帶你走進MySql優(yōu)化(系統(tǒng)層面優(yōu)化、軟件層面優(yōu)化、SQL層面優(yōu)化)的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！