mysql數據庫優化的思路是什麼

本文目錄一覽：

• 1、淺談數據庫查詢優化的幾種思路

• 2、數據庫該如何優化？

• 3、數據庫如何優化

• 4、優化MYSQL數據庫的方法

• 5、mysql數據庫如何優化？誰能給出點具體的解決方案？

• 6、MySQL數據庫性能優化有哪些技巧？

淺談數據庫查詢優化的幾種思路

應盡量避免全表掃描，首先應考慮在 where 及 order by ,group by 涉及的列上建立索引

可以幫助選擇更好的索引和優化查詢語句， 寫出更好的優化語句。 通常我們可以對比較複雜的尤其是涉及到多表的 SELECT 語句， 把關鍵字 EXPLAIN 加到前面， 查看執行計劃。例如： explain select * from news;

用具體的字段列表代替「*」 ， 不要返回用不到的任何字段。

mysql innodb上的理解。

1，不需要的字段會增加數據傳輸的時間，即使mysql服務器和客戶端是在同一台機器上，使用的協議還是tcp，通信也是需要額外的時間。

2，要取的字段、索引的類型，和這兩個也是有關係的。舉個例子，對於user表，有name和phone的聯合索引，select name from user where phone= 12345678912 和 select * from user where phone= 12345678912 ，前者要比後者的速度快，因為name可以在索引上直接拿到，不再需要讀取這條記錄了。

3，大字段，例如很長的varchar，blob，text。準確來說，長度超過728位元組的時候，會把超出的數據放到另外一個地方，因此讀取這條記錄會增加一次io操作。

比如from_unixtime(create_time) = 』2014-05-29』就不能使用到索引，原因很簡單，b+樹中存的都是數據表中的字段值，但進行檢索時，需要把所有元素都應用函數才能比較，顯然成本太大。所以語句應該寫成create_time = unix_timestamp(』2014-05-29』);

使用 procedure analyse()函數對錶進行分析， 該函數可以對表中列的數據類型提出優化建議。 能小就用小。 表數據類型第一個原則是： 使用能正確的表示和存儲數據的最短類型。 這樣可以減少對磁盤空間、 內存、 cpu 緩存的使用。

使用方法： select * from 表名 procedure analyse();

通過拆分表可以提高表的訪問效率。 有 2 種拆分方法

1.垂直拆分

把主鍵和一些列放在一個表中， 然後把主鍵和另外的列放在另一個表中。 如果一個表中某些列常用， 而另外一些不常用， 則可以採用垂直拆分。

2.水平拆分

根據一列或者多列數據的值把數據行放到二個獨立的表中。

創建中間表， 表結構和源表結構完全相同， 轉移要統計的數據到中間表， 然後在中間表上進行統計， 得出想要的結果。

選擇多核和主頻高的 CPU。

使用更大的內存。 將盡量多的內存分配給 MYSQL 做緩存。

4.3.1 使用磁盤陣列

RAID 0 沒有數據冗餘， 沒有數據校驗的磁盤陳列。 實現 RAID 0至少需要兩塊以上的硬盤， 它將兩塊以上的硬盤合併成一塊， 數據連續地分割在每塊盤上。

RAID1 是將一個兩塊硬盤所構成 RAID 磁盤陣列， 其容量僅等於一塊硬盤的容量， 因為另一塊只是當作數據「鏡像」。使用 RAID-0+1 磁盤陣列。 RAID 0+1 是 RAID 0 和 RAID 1 的組合形式。 它在提供與 RAID 1 一樣的數據安全保障的同時， 也提供了與 RAID 0 近似的存儲性能。

4.3.2 調整磁盤調度算法

選擇合適的磁盤調度算法， 可以減少磁盤的尋道時間

對 MySQL 自身的優化主要是對其配置文件 my.cnf 中的各項參數進行優化調整。 如指定 MySQL 查詢緩衝區的大小， 指定 MySQL 允許的最大連接進程數等。

它的作用是存儲 select 查詢的文本及其相應結果。 如果隨後收到一個相同的查詢， 服務器會從查詢緩存中直接得到查詢結果。 查詢緩存適用的對象是更新不頻繁的表， 當表中數據更改後， 查詢緩存中的相關條目就會被清空。

數據庫該如何優化？

數據庫優化可以從以下幾個方面進行：

1.結構層: web服務器採用負載均衡服務器,mysql服務器採用主從複製,讀寫分離

2.儲存層: 採用合適的存儲引擎,採用三範式

3.設計層: 採用分區分表,索引,表的字段採用合適的字段屬性,適當的採用逆範式,開啟mysql緩存

4.sql語句層:結果一樣的情況下,採用效率高,速度快節省資源的sql語句執行

數據庫如何優化

body{

line-height:200%;

}

如何優化MySQL數據庫

當MySQL數據庫邂逅優化，它有好幾個意思，今天我們所指的是性能優化。

我們究竟該如何對MySQL數據庫進行優化呢？下面我就從MySQL對硬件的選擇、Mysql的安裝、my.cnf的優化、MySQL如何進行架構設計及數據切分等方面來說明這個問題。

1.服務器物理硬件的優化

1)磁盤(I/O)，MySQL每一秒鐘都在進行大量、複雜的查詢操作，對磁盤的讀寫量可想而知，所以推薦使用RAID1+0磁盤陣列，如果資金允許，可以選擇固態硬盤做RAID1+0；

2)cpu對Mysql的影響也是不容忽視的，建議選擇運算能力強悍的CPU。

2.MySQL應該採用編譯安裝的方式

MySQL數據庫的線上環境安裝，我建議採取編譯安裝，這樣性能會較大的提升。

3.MySQL配置文件的優化

1)skip

-name

-resolve，禁止MySQL對外部連接進行DNS解析，使用這一選項可以消除MySQL進行DNS解析的時間；

2)back_log

=

384，back_log指出在MySQL暫時停止響應新請求之前，短時間內的多少個請求可以被存在堆棧中，對於Linux系統而言，推薦設置小於512的整數。

3)如果key_reads太大，則應該把my.cnf中key_buffer_size變大，保持key_reads/key_read_requests至少在1/100以上，越小越好。

4.MySQL上線後根據status狀態進行適當優化

1)打開慢查詢日誌可能會對系統性能有一點點影響，如果你的MySQL是主-從結構，可以考慮打開其中一台從服務器的慢查詢日誌，這樣既可以監控慢查詢，對系統性能影響也會很小。

2)MySQL服務器過去的最大連接數是245，沒有達到服務器連接數的上限256，應該不會出現1040錯誤。比較理想的設置是：Max_used_connections/max_connections

*

100%

=85%

5.MySQL數據庫的可擴展架構方案

1)MySQL

cluster，其特點為可用性非常高，性能非常好，但它的維護非常複雜，存在部分Bug;

2)DRBD磁盤網絡鏡像方案，其特點為軟件功能強大，數據可在底層塊設備級別跨物理主機鏡像，且可根據性能和可靠性要求配置不同級別的同步。

優化MYSQL數據庫的方法

在開始演示之前，我們先介紹下兩個概念。

概念一，數據的可選擇性基數，也就是常說的cardinality值。

查詢優化器在生成各種執行計劃之前，得先從統計信息中取得相關數據，這樣才能估算每步操作所涉及到的記錄數，而這個相關數據就是cardinality。簡單來說，就是每個值在每個字段中的唯一值分佈狀態。

比如表t1有100行記錄，其中一列為f1。f1中唯一值的個數可以是100個，也可以是1個，當然也可以是1到100之間的任何一個數字。這裡唯一值越的多少，就是這個列的可選擇基數。

那看到這裡我們就明白了，為什麼要在基數高的字段上建立索引，而基數低的的字段建立索引反而沒有全表掃描來的快。當然這個只是一方面，至於更深入的探討就不在我這篇探討的範圍了。

概念二，關於HINT的使用。

這裡我來說下HINT是什麼，在什麼時候用。

HINT簡單來說就是在某些特定的場景下人工協助MySQL優化器的工作，使她生成最優的執行計劃。一般來說，優化器的執行計劃都是最優化的，不過在某些特定場景下，執行計劃可能不是最優化。

比如：表t1經過大量的頻繁更新操作，（UPDATE,DELETE,INSERT），cardinality已經很不準確了，這時候剛好執行了一條SQL，那麼有可能這條SQL的執行計劃就不是最優的。為什麼說有可能呢？

來看下具體演示

譬如，以下兩條SQL，

A：

select * from t1 where f1 = 20;

B：

select * from t1 where f1 = 30;

如果f1的值剛好頻繁更新的值為30，並且沒有達到MySQL自動更新cardinality值的臨界值或者說用戶設置了手動更新又或者用戶減少了sample page等等，那麼對這兩條語句來說，可能不準確的就是B了。

這裡順帶說下，MySQL提供了自動更新和手動更新表cardinality值的方法，因篇幅有限，需要的可以查閱手冊。

那回到正題上，MySQL 8.0 帶來了幾個HINT，我今天就舉個index_merge的例子。

示例表結構：

mysql desc t1;+————+————–+——+—–+———+—————-+| Field | Type | Null | Key | Default | Extra |+————+————–+——+—–+———+—————-+| id | int(11) | NO | PRI | NULL | auto_increment || rank1 | int(11) | YES | MUL | NULL | || rank2 | int(11) | YES | MUL | NULL | || log_time | datetime | YES | MUL | NULL | || prefix_uid | varchar(100) | YES | | NULL | || desc1 | text | YES | | NULL | || rank3 | int(11) | YES | MUL | NULL | |+————+————–+——+—–+———+—————-+7 rows in set (0.00 sec)

表記錄數：

mysql select count(*) from t1;+———-+| count(*) |+———-+| 32768 |+———-+1 row in set (0.01 sec)

這裡我們兩條經典的SQL：

SQL C：

select * from t1 where rank1 = 1 or rank2 = 2 or rank3 = 2;

SQL D：

select * from t1 where rank1 =100 and rank2 =100 and rank3 =100;

表t1實際上在rank1,rank2,rank3三列上分別有一個二級索引。

那我們來看SQL C的查詢計劃。

顯然，沒有用到任何索引，掃描的行數為32034，cost為3243.65。

mysql explain format=json select * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "3243.65" }, "table": { "table_name": "t1", "access_type": "ALL", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "rows_examined_per_scan": 32034, "rows_produced_per_join": 115, "filtered": "0.36", "cost_info": { "read_cost": "3232.07", "eval_cost": "11.58", "prefix_cost": "3243.65", "data_read_per_join": "49K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)

我們加上hint給相同的查詢，再次看看查詢計劃。

這個時候用到了index_merge,union了三個列。掃描的行數為1103，cost為441.09，明顯比之前的快了好幾倍。

mysql explain format=json select /*+ index_merge(t1) */ * from t1 where rank1 =1 or rank2 = 2 or rank3 = 2\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "441.09" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "union(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1103, "rows_produced_per_join": 1103, "filtered": "100.00", "cost_info": { "read_cost": "330.79", "eval_cost": "110.30", "prefix_cost": "441.09", "data_read_per_join": "473K" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank1` = 1) or (`ytt`.`t1`.`rank2` = 2) or (`ytt`.`t1`.`rank3` = 2))" } }}1 row in set, 1 warning (0.00 sec)

我們再看下SQL D的計劃：

不加HINT，

mysql explain format=json select * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "534.34" }, "table": { "table_name": "t1", "access_type": "ref", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "idx_rank1", "used_key_parts": [ "rank1" ], "key_length": "5", "ref": [ "const" ], "rows_examined_per_scan": 555, "rows_produced_per_join": 0, "filtered": "0.07", "cost_info": { "read_cost": "478.84", "eval_cost": "0.04", "prefix_cost": "534.34", "data_read_per_join": "176" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100))" } }}1 row in set, 1 warning (0.00 sec)

加了HINT，

mysql explain format=json select /*+ index_merge(t1)*/ * from t1 where rank1 =100 and rank2 =100 and rank3 =100\G*************************** 1. row ***************************EXPLAIN: { "query_block": { "select_id": 1, "cost_info": { "query_cost": "5.23" }, "table": { "table_name": "t1", "access_type": "index_merge", "possible_keys": [ "idx_rank1", "idx_rank2", "idx_rank3" ], "key": "intersect(idx_rank1,idx_rank2,idx_rank3)", "key_length": "5,5,5", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "5.13", "eval_cost": "0.10", "prefix_cost": "5.23", "data_read_per_join": "440" }, "used_columns": [ "id", "rank1", "rank2", "log_time", "prefix_uid", "desc1", "rank3" ], "attached_condition": "((`ytt`.`t1`.`rank3` = 100) and (`ytt`.`t1`.`rank2` = 100) and (`ytt`.`t1`.`rank1` = 100))" } }}1 row in set, 1 warning (0.00 sec)

對比下以上兩個，加了HINT的比不加HINT的cost小了100倍。

總結下，就是說表的cardinality值影響這張的查詢計劃，如果這個值沒有正常更新的話，就需要手工加HINT了。相信MySQL未來的版本會帶來更多的HINT。

mysql數據庫如何優化？誰能給出點具體的解決方案？

1、explain：解釋sql的執行計劃，後邊的sql不執行

2、explain partitions ：用於查看存在分區的表的執行計劃

3、explain extended：待驗證

4、show warnings:

5、show create table:查看錶的詳細的創建語句，便於用戶對錶進行優化

6、show indexes :產看錶的所有索引，show indexes from table_name，同樣也可以從information_schema.statistics表中獲得同樣的信息。cardinality列很重要，表示數據量。

7、show tables status: 查看數據庫表的底層大小以及表結構，同樣可以從information_schema.tables表中獲得底層表的信息。

8、show [global|session]status:可以查看mysql服務器當前內部狀態信息。可以幫助卻行mysql服務器的負載的各種指標。默認是session。同information_schema.global_status和information_schema.session_status

9、show [global|session] variables ：查看當前mysql系統變量的值，其中一些值能影響到sql語句的執行方式。同information_schema.global_variables和information_schema.session_variables;

10、information_schema:包含的表的數量和mysql的版本有關係。

MySQL數據庫性能優化有哪些技巧？

1.存儲引擎的選擇如果數據表需要事務處理，應該考慮使用InnoDB，因為它完全符合ACID特性。如果不需要事務處理，使用默認存儲引擎MyISAM是比較明智的。並且不要嘗試同時使用這兩個存儲引擎。思考一下：在一個事務處理中，一些數據表使用InnoDB，而其餘的使用MyISAM.結果呢？整個subject將被取消，只有那些在事務處理中的被帶回到原始狀態，其餘的被提交的數據轉存，這將導致整個數據庫的衝突。然而存在一個簡單的方法可以同時利用兩個存儲引擎的優勢。目前大多數MySQL套件中包括InnoDB、編譯器和鏈表，但如果你選擇MyISAM，你仍然可以單獨下載InnoDB，並把它作為一個插件。很簡單的方法，不是嗎？

2.計數問題如果數據表採用的存儲引擎支持事務處理（如InnoDB），你就不應使用COUNT（*）計算數據表中的行數。這是因為在產品類數據庫使用COUNT（*），最多返回一個近似值，因為在某個特定時間，總有一些事務處理正在運行。如果使用COUNT（*）顯然會產生bug，出現這種錯誤結果。

3.反覆測試查詢查詢最棘手的問題並不是無論怎樣小心總會出現錯誤，並導致bug出現。恰恰相反，問題是在大多數情況下bug出現時，應用程序或數據庫已經上線。的確不存在針對該問題切實可行的解決方法，除非將測試樣本在應用程序或數據庫上運行。任何數據庫查詢只有經過上千個記錄的大量樣本測試，才能被認可。

4.避免全表掃描通常情況下，如果MySQL（或者其他關係數據庫模型）需要在數據表中搜索或掃描任意特定記錄時，就會用到全表掃描。此外，通常最簡單的方法是使用索引表，以解決全表掃描引起的低效能問題。然而，正如我們在隨後的問題中看到的，這存在錯誤部分。

5.使用「EXPLAIN」進行查詢當需要調試時，EXPLAIN是一個很好的命令，下面將對EXPLAIN進行深入探討。