mysql資料庫優化五步走,mysql資料庫優化總結

本文目錄一覽：

• 1、MySQL資料庫性能優化有哪些技巧？
• 2、mysql資料庫
• 3、北大青鳥java培訓：mysql資料庫的優化方法？
• 4、優化MYSQL資料庫的方法
• 5、北大青鳥設計培訓：Mysql資料庫的設計和優化？
• 6、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進行深入探討。

mysql資料庫

MySQL資料庫一般指MySQL，MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發。

mysql是目前網站以及APP應用上用得較多的一個開源的關係型資料庫系統，可以對數據進行保存，分段化的數據保存，也可以對其數據進行檢索，查詢等功能的資料庫。

默認的mysql資料庫中存有一個庫這個就是mysql的系統資料庫，可以對其保存系統的數據包括mysql資料庫的信息，資料庫root賬號，普通賬號，以及資料庫的名稱，還有資料庫的一些表還有一些數字型的數據類型結構都會有所保存。

mysql資料庫的優點

（1）MySQL資料庫是用C和C++語言編寫的，並且使用了多種編輯器進行測試，以保證源碼的可移植性。

（2）支持多個操作系統例如：Windows、Linux、Mac OS等等。

（3）支持多線程，可以充分的利用CPU資源。

（4）為多種編程語言提供API，包括C語言、Java、PHP、Python語言等。

（5）MySQL優化了SQL演算法，有效的提高了查詢速度。

（6）MySQL內提供了用於管理，檢查以及優化資料庫操作的管理工具。

（7）它能夠作為一個單獨的應用程序應用在客戶端伺服器網路環境中，也可以作為一個庫嵌入到其他的軟體中並提供多種語言支持。

北大青鳥java培訓：mysql資料庫的優化方法？

我們都知道，伺服器資料庫的開發一般都是通過java或者是PHP語言來編程實現的，而為了提高我們資料庫的運行速度和效率，資料庫優化也成為了我們每日的工作重點，今天，福建IT培訓就一起來了解一下mysql伺服器資料庫的優化方法。

為什麼要了解索引真實案例案例一：大學有段時間學習爬蟲，爬取了知乎300w用戶答題數據，存儲到mysql數據中。

那時不了解索引，一條簡單的「根據用戶名搜索全部回答的sql「需要執行半分鐘左右，完全滿足不了正常的使用。

案例二：近線上應用的資料庫頻頻出現多條慢sql風險提示，而工作以來，對資料庫優化方面所知甚少。

例如一個用戶數據頁面需要執行很多次資料庫查詢，性能很慢，通過增加超時時間勉強可以訪問，但是性能上需要優化。

索引的優點合適的索引，可以大大減小mysql伺服器掃描的數據量，避免內存排序和臨時表，提高應用程序的查詢性能。

索引的類型mysql數據中有多種索引類型，primarykey，unique，normal，但底層存儲的數據結構都是BTREE;有些存儲引擎還提供hash索引，全文索引。

BTREE是常見的優化要面對的索引結構，都是基於BTREE的討論。

B-TREE查詢數據簡單暴力的方式是遍歷所有記錄;如果數據不重複，就可以通過組織成一顆排序二叉樹，通過二分查找演算法來查詢，大大提高查詢性能。

而BTREE是一種更強大的排序樹，支持多個分支，高度更低，數據的插入、刪除、更新更快。

現代資料庫的索引文件和文件系統的文件塊都被組織成BTREE。

btree的每個節點都包含有key，data和只想子節點指針。

btree有度的概念d=1。

假設btree的度為d，則每個內部節點可以有n=[d+1，2d+1)個key，n+1個子節點指針。

樹的大高度為h=Logb[(N+1)/2]。

索引和文件系統中，B-TREE的節點常設計成接近一個內存頁大小(也是磁碟扇區大小)，且樹的度非常大。

這樣磁碟I/O的次數，就等於樹的高度h。

假設b=100，一百萬個節點的樹，h將只有3層。

即，只有3次磁碟I/O就可以查找完畢，性能非常高。

索引查詢建立索引後，合適的查詢語句才能大發揮索引的優勢。

另外，由於查詢優化器可以解析客戶端的sql語句，會調整sql的查詢語句的條件順序去匹配合適的索引。

優化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資料庫的設計和優化？

在JAVA開發中資料庫的學習也是我們需要了解的，截下來幾篇文章都是關於資料庫的設計和應用，那麼java課程培訓機構廢話不多說開始學習吧！  資料庫的設計  資料庫設計是基礎，資料庫優化是建立在設計基礎之上的。

好的資料庫一定擁有好的設計。

  資料庫設計的目標是為用戶和各種應用系統提供一個信息基礎設施和高效的運行環境。

  資料庫的三大範式  第一範式1NF:所有的域都應該是原子性的，即資料庫表的每一列都是不可分割的原子數據項，而不能是集合，數組，記錄等非原子數據項。

  第二範式2Nf:第二範式在第一範式的基礎之上更進一層。

第二範式需要確保資料庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關(主要針對聯合主鍵而言)。

也就是說在一個資料庫表中，一個表中只能保存一種數據，不可以把多種數據保存在同一張資料庫表中。

  第三範式3Nf:所有欄位必須與主鍵直接相關，而不是間接相關。

也可以理解為欄位不要和其他非主鍵欄位相關.  注意:這三個範式儘可能去遵守,不是一定要墨守成規.這只是讓我們設計的表的時候,越靠近這些範式,可以使欄位盡量的減小冗餘.但是有時候也可以根據實際需要小小的違背一下.但是第三範式違反一下還可以接受,但是第一範式別違反.  資料庫設計的步驟  需求分析階段  準確了解與分析用戶需求(包括數據與處理)。

是整個設計過程的基礎，是最困難、最耗費時間的一步。

  概念結構設計階段  是整個資料庫設計的關鍵–設計資料庫的E-R模型圖，確認需求信息的正確和完整  Entity_Relationship—實體之間的關係  一對一  一對多  多對一

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的版本有關係。