雖說給表增加索引在查詢的時候能大大加快查詢速度,但是索引在修改的會降低數據庫的性能(要檢查、更新索引需要時間) 如果您發現實驗過程有什麼不當之處懇請指正 :) 實驗結果:
沒有索引 整型做Primary Key 整型做Index 字符串做Primary Key 字符串做Index
53 61 81 216 218
數據都是在插入100萬的時候得出的,時間單位:秒
可以看出整型當成PK的時候,性能下降了13%,字符串的時候性能下降了4倍吧,沒有必要的時候別用字符串做PK,整型做Index的時候性能下架了60%吧 (性能都是值插入數據的時候的性能)當然如果你的查詢次數遠大於修改次數,這點性能的捨棄還是值得的!所以避免數據庫中索引滿天飛的情況吧
------------------------------------------------------------------------------ 詳細實驗過程:(表的引擎都是 ENGINE=MyISAM ) MySQL> desc Test_Key; | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | NO | PRI | | |
| name | varchar(255) | YES | | NULL | | Int型的id在這裡是主鍵性能分析存儲過程:
create PROCEDURE proc_test_performance(in count bigint)
BEGIN
DECLARE start_time BIGINT;
DECLARE end_time BIGINT;
DECLARE iter BIGINT;
select UNIX_TIMESTAMP() into start_time;
set iter = 0;
WHILE iter<count DO
insert into Test_Key(id,name) values(iter,CONCAT('test',iter));
set iter = iter+1;
end while; select UNIX_TIMESTAMP() into end_time; select (end_time-start_time),start_time, end_time;
select count(*) from Test_Key;
delete from Test_Key;
end; 執行結果:(單位是秒)3次執行分別為63,61,60
MySQL> call proc_test_performance(1000000); | (end_time-start_time) | start_time | end_time | | 63 | 1190704959 | 1190705022 | 1 row in set (1 min 2.22 sec) 去掉Test_Key表中的Primary Key的限制,分別執行三次結果是:53,54,53秒。
MySQL> desc Test_Key; | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+結論一:在這種情況下性能提升13%多一點點 ----------------------------------------------------------------------------- MySQL> desc Test_Key; (沒有索引) | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | | NULL | | 執行結果:
MySQL> call proc_test_performance(1000000); | (end_time-start_time) | start_time | end_time | | 54 | 1190706332 | 1190706386 |
+-----------------------+------------+------------+
三次分別為:54,54,54秒接著修改表的結構,給name加上primary key的約束
MySQL> desc Test_Key; | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | PRI | | | 然後再插入3次時間分別為:216,215,217秒 -------------------------------------------------------------------------
接下來看看普通Index對性能的影響:
MySQL> desc Test_Key; | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | YES | MUL | NULL | |
| name | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
在int上建立Index,三次執行結果耗時分別為:81,82,81秒 MySQL> desc Test_Key; | FIEld | Type | Null | Key | Default | Extra | | id | int(11) | YES | | NULL | |
| name | varchar(255) | YES | MUL | NULL | |
+-------+--------------+------+-----+---------+-------+
在Varchar上建立Index,三次執行結果耗時分別為:218,217,217秒
結論見上面再來看一個有意思的結論: drop procedure if EXISTS proc_test_performance;
create PROCEDURE proc_test_performance(in count bigint)
BEGIN
DECLARE start_time BIGINT;
DECLARE end_time BIGINT;
DECLARE iter BIGINT;
select UNIX_TIMESTAMP() into start_time;
set iter = 0;
WHILE iter<count DO
insert into Test_Key(id,name) values(iter,CONCAT(iter,'test'));//將數字(不同部分)放到前面去
set iter = iter+1;
end while; select UNIX_TIMESTAMP() into end_time; select (end_time-start_time),start_time, end_time;
select count(*) from Test_Key;
delete from Test_Key;
end;
三次插入的時間分別是:159,160,159秒,我想你應該明白為什麼時間能減少這麼多吧,呵呵