提高工作效率：MySQL的優(yōu)化技巧

1、在所有用于where,order by和group by的列上添加索引

1）索引除了能夠確保唯一的標(biāo)記一條記錄，還能是MySQL服務(wù)器更快的從數(shù)據(jù)庫中獲取結(jié)果。索引在排序中的作用也非常大。

Mysql的索引可能會占據(jù)額外的空間，并且會一定程度上降低插入，刪除和更新的性能。但是，如果你的表格有超過10行數(shù)據(jù)，那么索引就能極大的降低查找的執(zhí)行時(shí)間。

2）強(qiáng)烈建議使用“最壞情況的數(shù)據(jù)樣本”來測試MySql查詢，從而更清晰的了解查詢在生產(chǎn)中的行為方式。

3）假設(shè)你正在一個(gè)超過500行的數(shù)據(jù)庫表中執(zhí)行如下的查詢語句：

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

上述查詢會迫使Mysql服務(wù)器執(zhí)行一個(gè)全表掃描來獲得所查找的數(shù)據(jù)。

4）型號，Mysql提供了一個(gè)特別的Explain語句，用來分析你的查詢語句的性能。當(dāng)你將查詢語句添加到該關(guān)鍵詞后面時(shí)，MySql會顯示優(yōu)化器對該語句的所有信息。

如果我們用explain語句分析一下上面的查詢，會得到如下的分析結(jié)果：

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  

可以看到，優(yōu)化器展示出了非常重要的信息，這些信息可以幫助我們微調(diào)數(shù)據(jù)庫表。首先，MySql會執(zhí)行一個(gè)全表掃描，因?yàn)閗ey列為Null。其次，MySql服務(wù)器已經(jīng)明確表示它將要掃描500行的數(shù)據(jù)來完成這次查詢。

5）為了優(yōu)化上述查詢，我們只需要在customer_id這一列上添加一個(gè)索引m即可：

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

如果我們再次執(zhí)行explain語句，會得到如下結(jié)果：

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優(yōu)化Like語句

1）有時(shí)候，你可能需要在查詢中使用or操作符進(jìn)行比較。當(dāng)or關(guān)鍵字在where子句中使用頻率過高的時(shí)候，它可能會使MySQL優(yōu)化器錯(cuò)誤的選擇全表掃描來檢索記錄。union子句可以是查詢執(zhí)行的更快，尤其是當(dāng)其中一個(gè)查詢有一個(gè)優(yōu)化索引，而另一個(gè)查詢也有一個(gè)優(yōu)化索引的時(shí)候。

比如，在first_name和last_name上分別存在索引的情況下，執(zhí)行如下查詢語句：

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. 避免使用帶有前導(dǎo)通配符的表達(dá)式

當(dāng)查詢中存在前導(dǎo)通配符時(shí)，Mysql無法使用索引。以上面的student表為例，如下的查詢會導(dǎo)致MySQL執(zhí)行全表掃描，及時(shí)first_name字段上加了索引。

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

使用explain分析得到如下結(jié)果：

   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行數(shù)據(jù)，這將使得查詢極其緩慢。

4. 優(yōu)化數(shù)據(jù)庫架構(gòu)

1）規(guī)范化

首先，規(guī)范化所有數(shù)據(jù)庫表，即使可能會有些損失。比如，如果你需要?jiǎng)?chuàng)建兩張表分別用來記錄customers和orders數(shù)據(jù)，你應(yīng)當(dāng)在order表上用顧客id引用顧客，而不是反過來。下圖顯示了沒有任何數(shù)據(jù)冗余而設(shè)計(jì)的數(shù)據(jù)庫架構(gòu)。

5.使用最佳數(shù)據(jù)類型

1）MySQL支持各種數(shù)據(jù)類型，包括integer，float，double，date，datetime，varchar，text等。當(dāng)設(shè)計(jì)數(shù)據(jù)庫表時(shí)，應(yīng)當(dāng)盡可能使用能夠滿足特性的最短的數(shù)據(jù)類型。

比如，如果你在設(shè)計(jì)一個(gè)系統(tǒng)用戶表，而該用戶數(shù)量不會超過100個(gè)人，你就應(yīng)該對user_ud使用'TINYINT'類型，該類型的取值范圍為-128至128。如果一個(gè)字段需要存儲date型值，使用datetime類型比較好，因?yàn)樵诓樵兊臅r(shí)候無需進(jìn)行復(fù)雜的類型轉(zhuǎn)換。

當(dāng)值全為數(shù)字類型時(shí)，使用Integer。在進(jìn)行計(jì)算時(shí)，Integer類型的值比文本類型的值速度更快。

以上就是提高工作效率：MySQL的優(yōu)化技巧的詳細(xì)內(nèi)容，更多請關(guān)注php中文網(wǎng)其它相關(guān)文章！