概述

這里是記錄一些本人在看書或是開發(fā)過程中遇到的一些數(shù)據(jù)庫的性能優(yōu)化問題，希望與君共勉。

---

版權(quán)說明

著作權(quán)歸作者所有。
商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
本文作者：Coding-Naga
發(fā)表日期： 2016年4月5日
本文鏈接：http://blog.csdn.net/lemon_tree12138/article/details/51062548
來源：CSDN
更多內(nèi)容：分類 >> 數(shù)據(jù)庫

---

奇技淫巧

0. 提前終止查詢

這一點可能注意過的人并不多，我現(xiàn)在假設(shè)我的數(shù)據(jù)表中有一label字段，假設(shè)我現(xiàn)在查詢的值在數(shù)據(jù)表中只有一份，而且比較靠前。SQL語句如下：

SELECT * FROM labels WHERE label='iwpfdc';

查詢的結(jié)果如下：

+----+--------+ | id | label | +----+--------+ | 25 | iwpfdc | +----+--------+ 1 row in set (22.13 sec)

查詢上面的結(jié)果，竟然耗費了22秒！這是一個恐怖的事情。而且可以看到id只有25，說明，這是一個比較靠前的數(shù)據(jù)。這是可怕的，因為考慮到這條數(shù)據(jù)可能只有一條。可是，上面的查詢語句會對整個數(shù)據(jù)表進(jìn)行全表掃描。為了防止全表掃描，我們可以在查到了一條數(shù)據(jù)時，就中止查詢。所以這里我們可以使用LIMIT 1來提前終止查詢。

SELECT * FROM labels WHERE label='iwpfdc' LIMIT 1;

現(xiàn)在來看結(jié)果：

+----+--------+ | id | label | +----+--------+ | 25 | iwpfdc | +----+--------+ 1 row in set (0.10 sec)

只有0.1秒了，時間是可觀的。不過這還不是最佳處理，還有一些更好的操作請繼續(xù)閱讀。

---

1. 為搜索字段創(chuàng)建索引

這一點很重要，在平常的項目開發(fā)中，為搜索字段創(chuàng)建索引是必不可少的。不過我們了不能為了創(chuàng)建索引而創(chuàng)建索引，也不要為所有的列都創(chuàng)建索引，畢竟數(shù)據(jù)庫在對索引的維護上也是有時間消耗的。下面暫且只說明索引帶來的好處。
還是上面的數(shù)據(jù)庫，在對label列創(chuàng)建完索引之后，再一次查詢時，結(jié)果有了明顯地提升。
查詢SQL語句

select * from labels where label='yjrplewcft';

查詢結(jié)果

+----+------------+ | id | label | +----+------------+ | 35 | yjrplewcft | +----+------------+ 1 row in set (0.11 sec)

上面是在進(jìn)行“全表”掃描的結(jié)果，雖然如此，可是速度還是很快。下面就第1條優(yōu)化建議進(jìn)行查詢，看看結(jié)果。
查詢SQL語句

SELECT * FROM labels WHERE label='yjrplewcft' LIMIT 1;

查詢結(jié)果

+----+------------+ | id | label | +----+------------+ | 35 | yjrplewcft | +----+------------+ 1 row in set (0.03 sec)

這里只用了0.03秒就完成了查詢。由此可見，索引和提前終止查詢在實際應(yīng)用中的重要性。

---

2. 使用EXPLAIN

EXPLAIN在對SQL語句的優(yōu)化上有指導(dǎo)性的意義，這很重要。比如下面的這條SQL語句：

EXPLAIN SELECT * FROM labels WHERE label='yjrplewcft' LIMIT 1;

下面是EXPLAIN的結(jié)果：

+----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | labels | NULL | ref | index_labels | index_labels | 92 | const | 1 | 100.00 | Using index | +----+-------------+--------+------------+------+---------------+--------------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.15 sec)

從上面可以很直觀地看到，type、rows、filtered、Extra等列的數(shù)據(jù)都是最佳的，由此可得出此條查詢語句是一條性能比較好的SQL語句。
本人在《MySQL多表查詢核心優(yōu)化》文章中也有一些具體的實戰(zhàn)示例，感興趣的同學(xué)可以進(jìn)行參考。

---

3. 避免返回不需要的列

其實在實際的查詢中很少有可能需要查詢數(shù)據(jù)表中所有的列，那么就需要有選擇地進(jìn)行選擇字段。所以在你的項目里請避免使用 SELECT * 。
從數(shù)據(jù)庫里讀出越多的數(shù)據(jù)，那么查詢就會變得越慢。尤其在需要經(jīng)過網(wǎng)絡(luò)傳輸時，這就會大大地增加網(wǎng)絡(luò)傳輸?shù)呢?fù)載。
所以，在實際的開發(fā)過程中，還是只查詢自己當(dāng)前需要的東西，避免使用 SELECT * 吧。

---

4. 盡可能地使用 NOT NULL

這一點對于新手來說可能也不是太了解，本人也是最近才了解到這一點。因為在MySQL中對NULL字段處理的特殊性，致使我們需要對NULL字段進(jìn)行格外關(guān)照。
首先，我們要知道在MySQL中，NULL值并非我們一般意義上的空值。他是占用空間的，可以通過下面的查詢了解：

SELECT LENGTH(''), LENGTH(' '), LENGTH(NULL); +------------+-------------+--------------+ | LENGTH('') | LENGTH(' ') | LENGTH(NULL) | +------------+-------------+--------------+ | 0 | 1 | NULL | +------------+-------------+--------------+ 1 row in set (0.00 sec)

其次，雖然NULL占用了空間，可是MySQL卻并不為NULL字段創(chuàng)建索引。

---

5. 盡可能地使用定長的字段

這一點要怎么理解呢？如果你學(xué)習(xí)或是了解過操作系統(tǒng)中的地址偏移量的概念，那么這個技巧你應(yīng)該會很快就理解了。
固定長度的表會提高性能，MySQL在搜尋的時候也會更快一些。因為這些固定的長度是很容易就可以計算下一個數(shù)據(jù)的偏移量，所以讀取的自然也會很快。而如果字段不是定長的，那么，每一次要找下一條的話，需要程序先找到主鍵。

---

6. 不要以字符串定義IP地址

IP地址的形式一般就是這樣的：127.100.100.101。而根據(jù)上面第5點的技巧，我們也可以很快對這一字段有一個很好的設(shè)計，就像這樣：

ip VARCHAR(15) NOT NULL

是的，你看已經(jīng)對字段進(jìn)行了定長的設(shè)計，這個很合理呀。可是，如果你了解IP地址是可以進(jìn)行整型轉(zhuǎn)換的話，那么這樣的設(shè)計顯然有點遜色了。我們可以將這個字段定義成UNSIGNED INT或是LONG。
將IP定義成整型的好處在于，方便地對其創(chuàng)建索引，還可以方便地進(jìn)行范圍查詢：

... WHERE ip BETWEEN ip1 AND ip2

這一點在實際的項目開發(fā)中使用得還是比較多的。

---

7. 盡量避免使用ORDER BY

在MySQL中，對結(jié)果進(jìn)行排序是很耗時間的。我們還可是通過一個實例來說明會更加直觀一些。

SELECT * FROM labels ORDER BY label LIMIT 200000, 5; +---------+------------------------+ | id | label | +---------+------------------------+ | 3182819 | anjmlhwztqbu | | 1654768 | anjmlrf | | 6719238 | anjmopcfeydwkubtqhgxlr | | 9301930 | anjmq | | 3042587 | anjmqhxbzyrkpvswlto | +---------+------------------------+ SELECT * FROM labels LIMIT 200000, 5; +---------+------------------------+ | id | label | +---------+------------------------+ | 3182819 | anjmlhwztqbu | | 1654768 | anjmlrf | | 6719238 | anjmopcfeydwkubtqhgxlr | | 9301930 | anjmq | | 3042587 | anjmqhxbzyrkpvswlto | +---------+------------------------+ 5 rows in set (0.08 sec)

所以，還是在不需要排序的時候避免使用ORDER BY吧。

---

8. 大量數(shù)據(jù)的情況下，盡量使用分頁查詢

這一條其實與第3條很類似，一個是從縱向的角度切分，一個是從橫向的角度進(jìn)行切分。對于分頁的使用在第7條中也有實例應(yīng)用，可以參見。

---

9. 在要求不是很苛刻的情況下，可以使用max(id)代替count(*)

對于存在大量數(shù)據(jù)的情況下，如果我們需要查詢當(dāng)前數(shù)據(jù)表中有多少條記錄，這個要怎么做呢？
很容易想到的是使用COUNT()函數(shù)來完成，可是對于數(shù)據(jù)量很大的情況下，這里的查詢會很慢。因為我們在設(shè)計數(shù)據(jù)表的時候，一般會為數(shù)據(jù)表設(shè)計一個id字段，這個字段會隨著數(shù)據(jù)的添加而自增。所以，如果我們對于查詢結(jié)果只是想有一個粗略地了解的話，我們完全可以采用MAX()函數(shù)查詢。下面就是實際的應(yīng)用：
COUNT()

SELECT COUNT(*) FROM labels;

查詢結(jié)果

+----------+ | COUNT(*) | +----------+ | 10534997 | +----------+ 1 row in set (5.04 sec)

MAX(id)

SELECT MAX(id) FROM labels;

查詢結(jié)果

+----------+ | MAX(id) | +----------+ | 10537190 | +----------+ 1 row in set (0.00 sec)

可以看到這里的查詢結(jié)果是有很明顯地懸殊的。所以如果你對查詢結(jié)果并沒有很嚴(yán)格的要求的話，建議采用MAX(id)來查詢。

---

Ref

• 《高性能MySQL(第3版).Baron.Scbwartz》
• 《MySQL技術(shù)內(nèi)幕 InnoDB存儲引擎 第2版》
• http://www.bitscn.com/pdb/mysql/201007/188342.html

---

相關(guān)資源下載

• 本文相關(guān)數(shù)據(jù)庫鏈接

---

征集

如果你也需要使用ProcessOn這款在線繪圖工具，可以使用如下邀請鏈接進(jìn)行注冊：
https://www.processon.com/i/56205c2ee4b0f6ed10838a6d

總結(jié)

以上是生活随笔為你收集整理的MySQL 性能优化技巧（一）的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。