在性能測試中遇到性能瓶頸最的多地方就是數據庫這塊，而數據庫出問題很多都是索引使用不當導致，根據以往遇到的索引問題做個簡單的總結：
一、索引的利弊
索引的好處：索引能夠極大地提高數據檢索的效率，讓Query 執行得更快，也能夠改善排序分組操作的性能，在進行排序分組操作中利用好索引，將會極大地降低CPU資源的消耗。
索引的弊端：
1、更新數據庫時會更新索引，這樣，最明顯的資源消耗就是增加了更新所帶來的 IO 量和調整索引所致的計算量。
測試代碼：
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`time1` varchar(11) DEFAULT NULL,
`time2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURREN T_TIMESTAMP,
`time3` int(11) DEFAULT NULL,
`stats` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `NewIndex1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=gbk |
a、為了保證測試的公平性，所以必須保證添加索引前后的數據是完全一致的，現在中保留表中id小于200萬的數據。
mysql> DELETE FROM test.test WHERE id >2000000;
Query OK, 231412 rows affected (0.92 sec) |
b、沒有添加索引時添加200萬數據用時1.81秒
mysql> INSERT INTO test.`test` (time1,time2,time3,stats) SELECT time1,time2,time3,stats FROM test.`test`;
Query OK, 231412 rows affected (1.32 sec)
Records: 231412 Duplicates: 0 Warnings: 0 |
c、清除剛添加數據
mysql> DELETE FROM test.test WHERE id >2000000;
Query OK, 231412 rows affected (1.00 sec) |
d、添加索引
mysql> ALTER TABLE `test`.`test` ADD INDEX `time1_2_3_stats` (`time1`, `time2`, `time3`, `stats`);
Query OK, 0 rows affected (0.97 sec)
Records: 0 Duplicates: 0 Warnings: 0 |
e、添加索引后增加200萬數據用時4.88秒
mysql> INSERT INTO test.`test` (time1,time2,time3,stats) SELECT time1,time2,time3,stats FROM test.`test`;
Query OK, 231412 rows affected (2.27 sec)
Records: 231412 Duplicates: 0 Warnings: 0 |
2、索引也會占用一定的存儲空間，有些時候索引所占的空間有可能超過數據所占的空間；
例如：下面舉一個比較特殊的例子（如果字段大小設置不合理或者索引建的過多可能會導致一些問題），表結構和索引情況如下：
CREATE TABLE `friends` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`uid` bigint(20) unsigned DEFAULT ’0′,
`fuid` bigint(20) unsigned DEFAULT ’0′,
`fname` varchar(50) DEFAULT ”,
`fpicture` varchar(150) DEFAULT ”,
`fsex` tinyint(1) DEFAULT ’0′,
`status` tinyint(1) DEFAULT ’0′,
PRIMARY KEY (`id`),
KEY `fuid` (`fuid`),
KEY `fuid_fname` (`fuid`,`fname`),
KEY `uid_stats` (`uid`,`status`)
) ENGINE=MyISAM AUTO_INCREMENT=262145 DEFAULT CHARSET=gbk |
新建10萬條數據后，這個表的索引文件為4.4M而數據文件僅有3.9M：
[root@qa05v /usr/local/mysql/data/test]# du -sh friends.*
12K friends.frm
3.9M friends.MYD
4.4M friends.MYI |
這里有點需要注意的是對于varchar字段，索引的長度是其定義的長度。比如一行中`fname` varchar(50) DEFAULT ” 實際只存了3個byte數據，但是其索引長度是50，所以造成了索引有可能是比數據大。
二、索引使用原則
1、索引可以改善查詢，但會減慢更新，索引不是越多越好，特別是在數據增、刪、改比較頻繁的表中，過多的索引反而會導致系統整體性能的下降，這一點已經在索引的弊端中介紹過，這里就不做太多介紹。
2、離散程度越小，不適合加索引，例如：不要給性別建索引 status這樣字段建索引；
測試代碼（stats字段是0~4隨機生成的）：
mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4;
+———–+
| count(id) |
+———–+
| 740591 |
+———–+
1 row in set (0.40 sec)
mysql> ALTER TABLE `test`.`test` add INDEX `stats` (`stats`);
Query OK, 3702592 rows affected (24.08 sec)
Records: 3702592 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_NO_CACHE count(id) FROM test.test WHERE stats=4;
+———–+
| count(id) |
+———–+
| 740591 |
+———–+
1 row in set (1.58 sec) |
3、在數據量較少且訪問頻率不高的情況下，假如只有一百行記錄的表不需要建立索引。因為在數據量少的情況下，使用全表掃描效果比走索引更好，這就好比只有一本只有5頁的書，如果我們想找其中一個章節，我們一般不會通過目錄去尋找，而是直接去找了。
測試數據：
mysql> SELECT COUNT(*) FROM test.newtest; //該表數據為10000條
+———-+
| COUNT(*) |
+———-+
| 10001 |
+———-+ |
沒有索引時執行時間大概接近于0秒
mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′;
+————–+
| count(time1) |
+————–+
| 9992 |
+————–+ |
添加索引
mysql> ALTER TABLE `test`.`newtest` ADD INDEX `time1` (`time1`);
Query OK, 10001 rows affected (0.04 sec)
Records: 10001 Duplicates: 0 Warnings: 0 |
添加索引后執行時間為0.02sec
mysql> SELECT SQL_NO_CACHE count(time1) FROM test.`newtest` WHERE time1 >’20130517100591′;
+————–+
| count(time1) |
+————–+
| 9992 |
+————–+
1 row in set (0.02 sec) |
4、避免建立兩個或以上功能相同索引。例如已經建立字段A、B兩個字段的索引，應該避免再建立字段A的單獨索引。兩個索引之間，對相同的查詢都會起到相同的作用。建立兩個功能相同的索引，反而會容易引起數據庫產生錯誤的查詢計劃，降低查詢效率。
5、選擇正確的組合索引字段順序，最常用的查詢字段和選擇性、區分度較高的字段，應該作為索引的前導字段使用。假設存在組合索引it1c1c2(c1,c2)，查詢語句select * from t1 where c1=1 and c2=2能夠使用該索引。查詢語句select * from t1 where c1=1也能夠使用該索引。但是，查詢語句select * from t1 where c2=2不能夠使用該索引，因為沒有組合索引的引導列，即，要想使用c2列進行查找，必需出現c1等于某值，所以在在添加聯合索引的時候盡量將常用的字段放到最前面。
例如：
ALTER TABLE `test`.`friends` ADD INDEX `fuid_fname` (`fuid`, `fname`)；創建組合索引
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fname=’test5′;
+—-+————-+———+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | friends | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
+—-+————-+———+——+—————+——+———+——+——–+————-+ |
如果單獨再創建一個fuid索引這樣造成了索引的浪費。
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fuid=’364′;
+—-+————-+———+——+—————+————+———+——-+——-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+————+———+——-+——-+————-+
| 1 | SIMPLE | friends | ref | fuid_fname | fuid_fname | 9 | const | 33712 | Using where |
+—-+————-+———+——+—————+————+———+——-+——-+————-+ |
聯合索引使用情況：
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fuid=’364′ and fname=’test5′;
+—-+————-+———+——+—————+————+———+————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+————+———+————-+——+————-+
| 1 | SIMPLE | friends | ref | fuid_fname | fuid_fname | 112 | const,const | 2 | Using where
+—-+————-+———+——+—————+————+———+————-+——+————-+ |
Sql優化器會對SELECT * FROM test.`friends` WHEREfname=’test5′ and fuid=’364′;進行優化，優化后的效果可能為：ELECT * FROM test.`friends` WHERE fuid=’364′ and fname=’test5′，這點目前還沒證實;
mysql> EXPLAIN SELECT * FROM test.`friends` WHERE fname=’test5′ and fuid=’364′;
+—-+————-+———+——+—————+————+———+————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———+——+—————+————+———+————-+——+————-+
| 1 | SIMPLE | friends | ref | fuid_fname | fuid_fname | 112 | const,const | 2 | Using where
+—-+————-+———+——+—————+————+———+————-+——+————-+ |
6、合適的字段數，組合索引的字段數不適宜較多，較多的組合索引字段數會降低索引查詢效率，除業務特點需要建立多字段的組合主鍵例外。
三、索引分析利器explain
在做性能測試的過程中經常遇到一些數據庫的問題，通常使用慢查詢日志可以找到執行效果比較差的sql，但是僅僅找到這些sql是不行的，我們需要協助開發人員分析問題所在，這就經常用到explain。
explain顯示了mysql如何使用索引來處理select語句以及連接表。可以幫助選擇更好的索引和寫出更優化的查詢語句。
使用方法，在select語句前加上explain就可以了：
mysql> explain select * from test.index_test where time1=’20130517100552′;
+—-+————-+————+——+—————+——-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+————+——+—————+——-+———+——-+——+————-+
| 1 | SIMPLE | index_test | ref | time1 | time1 | 253 | const | 1 | Using where |
+—-+————-+————+——+—————+——-+———+——-+——+————-+ |
EXPLAIN列的解釋：
id：SELECT識別符。這是SELECT的查詢序列號，若沒有子查詢和聯合查詢，id則都是1,并且Mysql會按照id從大到小的順序執行query，在id相同的情況下，則從上到下執行。
table：顯示這一行的數據是關于哪張表的。
type：這是重要的列，顯示連接使用了何種類型。
Explain的type顯示的是訪問類型，是較為重要的一個指標，結果值從好到壞依次是：
system>const>eq_ref> ref > range > index > ALL
possible_keys：顯示可能應用這張表中的那個索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合適的語句。
key：實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MYSQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MYSQL忽略索引。
key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好。
ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數。
rows：MYSQL認為必須檢查的用來返回請求數據的行數。
Extra：關于MYSQL如何解析查詢的額外信息。
Extra列返回的描述
Distinct：一旦MYSQL找到了與行相聯合匹配的行，就不再搜索了
Not exists：MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了
Range checked for each Record（index map:#）:沒有找到理想的索引，因此對于從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，并用它來從表中返回行。這是使用索引的最慢的連接之一
Using filesort：當看到這個的時候，查詢就有可能需要優化了。MYSQL需要進行額外的步驟來發現如何對返回的行排序。它根據連接類型以及存儲排序鍵值和匹配條件的全部行的行指針來排序全部行
Using index：列數據是從僅僅使用了索引中的信息而沒有讀取實際的行動的表返回的，這發生在對表的全部的請求列都是同一個索引的部分的時候
Using temporary 看到這個的時候，查詢需要優化了。這里，MYSQL需要創建一個臨時表來存儲結果，這通常發生在對不同的列集進行ORDER BY上，而不是GROUP BY上
Using where：使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。如果不想返回表中的全部行，并且連接類型ALL或index，這就會發生，或者是查詢有問題不同連接類型的解釋。
四、引起索引失效的一些因素
1、like語句操作
一般情況下不鼓勵使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%”不會使用索引而like “aaa%”可以使用索引。
例如：
mysql> EXPLAIN SELECT * FROM test.`test` WHERE time1 LIKE ‘%550′ LIMIT 0,10;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+
mysql> EXPLAIN SELECT * FROM test.`test` WHERE time1 LIKE ’2013%’ LIMIT 0,10;
+—-+————-+——-+——-+—————+——-+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——-+———+——+———+————-+
| 1 | SIMPLE | test | range | time1 | time1 | 85 | NULL | 3922626 | Using where |
+—-+————-+——-+——-+—————+——-+———+——+———+————-+ |
2、在索引列上使用函數，或者對索引列進行運算，運算包括(+，-，*，/，! 等)會導致索引失效
例如：
mysql> EXPLAIN SELECT * FROM test.test WHERE id-1=153743;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+
mysql> EXPLAIN SELECT * FROM test.test WHERE id=153744;
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+
| 1 | SIMPLE | test | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+—-+————-+——-+——-+—————+———+———+——-+——+——-+ |
3、查詢的數量是表的大部分，比如30％以上，這只是估算值看。
例如：
mysql> select count(*) from test.test4; //查看一下數據量
+———-+
| count(*) |
+———-+
| 200000 |
+———-+
mysql> ALTER TABLE `test`.`test4` ADD INDEX `id_num` (`id_num`); //添加索引
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from test.test4 where id_num<60000; //滿足條件的數據為6萬條的情況
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
| 1 | SIMPLE | test4 | ALL | id_num | NULL | NULL | NULL | 200307 | Using where |
+—-+————-+——-+——+—————+——+———+——+——–+————-+
mysql> explain select * from test.test4 where id_num<10000;//滿足條件為1萬條的情況
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
| 1 | SIMPLE | test4 | range | id_num | id_num | 5 | NULL | 9998 | Using where |
+—-+————-+——-+——-+—————+——–+———+——+——+————-+
1 row in set (0.00 sec) |
4、字符型字段為數字時在where條件里不添加引號
例如：
被測試數據庫的表結構如下：
mysql>desc test;
+——-+————+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+——-+————+——+—–+——————-+—————————–+
| id | int(11) | NO | PRI | NULL | auto_increment |
| time1 | char(42) | YES | MUL | NULL | |
| time2 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| time3 | int(11) | YES | | NULL | |
| stats | tinyint(1) | YES | MUL | NULL | |
+——-+————+——+—–+——————-+—————————–+ |
這是添加了引號的sql語句的執行計劃：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM test.test WHERE time1 =’20130517160342′;
+—-+————-+——-+——+—————+——-+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——-+———+——-+——+————-+
| 1 | SIMPLE | test | ref | time1 | time1 | 85 | const | 4281 | Using where |
+—-+————-+——-+——+—————+——-+———+——-+——+————-+ |
這是沒有添加引號的sql語句的執行計劃：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM test.test WHERE time1 =20130517160342;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test | ALL | time1 | NULL | NULL | NULL | 3922757 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+ |