首頁 >資料庫 >mysql教程 >提高工作效率:MySQL的最佳化技巧

提高工作效率:MySQL的最佳化技巧

不言
不言轉載
2018-11-14 16:54:372616瀏覽

這篇文章帶給大家的內容是關於提高工作效率:MySQL的優化技巧,有一定的參考價值,有需要的朋友可以參考一下,希望對你有幫助。

1、在所有用於where,order by和group by的欄位上新增索引

1)索引除了能夠確保唯一的標記一筆記錄,還能是MySQL伺服器更快的從資料庫取得結果。索引在排序中的作用也非常大。

Mysql的索引可能會佔據額外的空間,並且會在一定程度上降低插入,刪除和更新的效能。但是,如果你的表格有超過10行數據,那麼索引就能極大的降低查找的執行時間。

2)強烈建議使用「最壞情況的資料樣本」來測試MySql查詢,從而更清晰的了解查詢在生產中的行為方式。

3)假設你正在一個超過500行的資料庫表中執行如下的查詢語句:

mysql>select customer_id, customer_name from customers where customer_id='345546'

上述查詢會迫使Mysql伺服器執行一個全表掃描來獲得所尋找的資料。

4)型號,Mysql提供了一個特別的Explain語句,用來分析你的查詢語句的效能。當你將查詢語句加入到該關鍵字後面時,MySql會顯示優化器對該語句的所有資訊。

如果我們用explain語句分析一下上面的查詢,會得到如下的分析結果:

mysql> explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | customers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    10.00 | Using where |

可以看到,優化器展示出了非常重要的信息,這些信息可以幫助我們微調資料庫表。首先,MySql會執行一個全表掃描,因為key列為Null。其次,MySql伺服器已經明確表示它將要掃描500行的資料來完成這次查詢。

5)為了最佳化上述查詢,我們只需要在customer_id這一列上新增一個索引m:

mysql> Create index customer_id ON customers (customer_Id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

如果我們再執行explain語句,會得到以下結果:

mysql> Explain select customer_id, customer_name from customers where customer_id='140385';
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+| id | select_type | table     | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+|  1 | SIMPLE      | customers | NULL       | ref  | customer_id   | customer_id | 13      | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+---------------+-------------+---------+-------+------+----------+-------+

2. 用Union優化Like語句

1)有時候,你可能需要在查詢中使用or操作符來比較。當or關鍵字在where子句中使用頻率過高的時候,它可能會使MySQL優化器錯誤的選擇全表掃描來檢索記錄。 union子句可以是查詢執行的更快,尤其是當其中一個查詢有一個最佳化索引,而另一個查詢也有一個最佳化索引的時候。

例如,在first_name和last_name上分別存在索引的情況下,執行如下查詢語句:

mysql> select * from students where first_name like 'Ade%' or last_name like 'Ade%'

上述查詢和下面使用union合併兩條充分利用查詢語句的查詢相比,速度慢了許多。

mysql> select * from students where first_name like 'Ade%' union all select * from students wherelast_name like 'Ade%'

3. 避免使用帶有前導通配符的表達式

當查詢中存在前導通配符時,Mysql無法使用索引。以上面的student表為例,如下的查詢會導致MySQL執行全表掃描,及時first_name欄位上加了索引。

mysql> select * from students where first_name like '%Ade'

使用explain分析得到如下結果:

 | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  500 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+

如上所示,Mysql將掃描全部500行數據,這將使查詢極其緩慢。

4. 最佳化資料庫架構

1)規範化

首先,規範化所有資料庫表,即使可能會有些損失。例如,如果你需要建立兩張表分別用來記錄customers和orders數據,你應該在order表上用顧客id引用顧客,而不是反過來。下圖顯示了沒有任何資料冗餘而設計的資料庫架構。

提高工作效率:MySQL的最佳化技巧

5.使用最佳資料型別

1)MySQL支援各種資料型別,包括integer,float,double, date,datetime,varchar,text等。當設計資料庫表時,應盡可能使用能夠滿足特性的最短的資料類型。

例如,如果你在設計一個系統用戶表,而該用戶數量不會超過100個人,你就應該對user_ud使用'TINYINT'類型,該類型的取值範圍為-128至128。如果一個欄位需要儲存date型值,使用datetime類型比較好,因為在查詢的時候不需要進行複雜的類型轉換。

當值全為數字型別時,使用Integer。在進行計算時,Integer類型的值比文字類型的值速度更快。

以上是提高工作效率:MySQL的最佳化技巧的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:segmentfault.com。如有侵權,請聯絡admin@php.cn刪除