怎么在mysql中根據(jù)索引優(yōu)化查詢速度

這篇文章將為大家詳細(xì)講解有關(guān)怎么在mysql中根據(jù)索引優(yōu)化查詢速度，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

（一）索引的作用

索引通俗來(lái)講就相當(dāng)于書(shū)的目錄，當(dāng)我們根據(jù)條件查詢的時(shí)候，沒(méi)有索引，便需要全表掃描，數(shù)據(jù)量少還可以，一旦數(shù)據(jù)量超過(guò)百萬(wàn)甚至千萬(wàn)，一條查詢sql執(zhí)行往往需要幾十秒甚至更多，5秒以上就已經(jīng)讓人難以忍受了。

（二）mysql的索引類型：

mysql的索引有5種：主鍵索引、普通索引、唯一索引、全文索引、聚合索引（多列索引）。

唯一索引和全文索引用的很少，我們主要關(guān)注主鍵索引、普通索引和聚合索引。

1）主鍵索引：主鍵索引是加在主鍵上的索引，設(shè)置主鍵（primary key）的時(shí)候，mysql會(huì)自動(dòng)創(chuàng)建主鍵索引；

2）普通索引：創(chuàng)建在非主鍵列上的索引；

3）聚合索引：創(chuàng)建在多列上的索引。

（三）索引的語(yǔ)法：

查看某張表的索引：show index from 表名；

創(chuàng)建普通索引：alter table 表名 add index  索引名 (加索引的列) 

創(chuàng)建聚合索引：alter table 表名 add index  索引名 (加索引的列1,加索引的列2) 

刪除某張表的索引：drop index 索引名 on 表名;

（四）性能測(cè)試

測(cè)試環(huán)境：博主工作用臺(tái)式機(jī)

處理器為Intel Core i5-4460 3.2GHz;

內(nèi)存8G;

64位windows。

1：創(chuàng)建一張測(cè)試表

DROP TABLE IF EXISTS `test_user`;
CREATE TABLE `test_user` (
 `id` bigint(20) PRIMARY key not null AUTO_INCREMENT,
 `username` varchar(11) DEFAULT NULL,
 `gender` varchar(2) DEFAULT NULL,
 `password` varchar(100) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

存儲(chǔ)引擎使用MyISAM是因?yàn)榇艘鏇](méi)有事務(wù)，插入速度極快，方便我們快速插入千萬(wàn)條測(cè)試數(shù)據(jù)，等我們插完數(shù)據(jù)，再把存儲(chǔ)類型修改為InnoDB。

2：使用存儲(chǔ)過(guò)程插入1千萬(wàn)條數(shù)據(jù)

create procedure myproc() 
begin 
declare num int; 
set num=1; 
while num <= 10000000 do 
insert into test_user(username,gender,password) values(num,'保密',PASSWORD(num)); 
set num=num+1;
end while;
 end

call myproc();

由于使用的MyISAM引擎，插入1千萬(wàn)條數(shù)據(jù)，僅耗時(shí)246秒，若是InnoDB引擎，就要花費(fèi)數(shù)小時(shí)了。

然后將存儲(chǔ)引擎修改回InnDB。使用如下命令：  alter table test_user engine=InnoDB;此命令執(zhí)行時(shí)間大約耗時(shí)5分鐘，耐心等待。

tips：這里是測(cè)試，生產(chǎn)環(huán)境中不要隨意修改存儲(chǔ)引擎，還有alter table 操作，會(huì)鎖整張表，慎用。其次：myisam引擎沒(méi)有事務(wù)，且只是將數(shù)據(jù)寫到內(nèi)存中，然后定期將數(shù)據(jù)刷出到磁盤上，因此突然斷電的情況下，會(huì)導(dǎo)致數(shù)據(jù)丟失。而InnDB引擎，是將數(shù)據(jù)寫入日志中，然后定期刷出到磁盤上，所以不怕突然斷電等情況。因此在實(shí)際生產(chǎn)中能用InnDB則用。

3：sql測(cè)試

select id,username,gender,password from test_user where id=999999

耗時(shí)：0.114s。

因?yàn)槲覀兘ū淼臅r(shí)候，將id設(shè)成了主鍵，所以執(zhí)行此sql的時(shí)候，走了主鍵索引，查詢速度才會(huì)如此之快。

我們?cè)賵?zhí)行select id,username,gender,password from test_user where username='9000000'
耗時(shí)：4.613s。

我們給username列加上普通索引。

ALTER TABLE `test_user` ADD INDEX index_name(username) ;

此過(guò)程大約耗時(shí) 54.028s,建索引的過(guò)程會(huì)全表掃描，逐條建索引，當(dāng)然慢了。

再來(lái)執(zhí)行：selectid,username,gender,password from test_user where username='9000000'
耗時(shí)：0.043s。

再用username和password來(lái)聯(lián)合查詢

select id,username,gender,password from test_user where username='9000000' and `password`='*3A70E147E88D99888804E4D472410EFD9CD890AE'

此時(shí)雖然我們隊(duì)username加了索引，但是password列未加索引，索引執(zhí)行password篩選的時(shí)候，還是會(huì)全表掃描，因此此時(shí)

查詢速度立馬降了下來(lái)。

耗時(shí)：4.492s。

當(dāng)我們的sql有多個(gè)列的篩選條件的時(shí)候，就需要對(duì)查詢的多個(gè)列都加索引組成聚合索引：

加上聚合索引：ALTER TABLE `test_user` ADD INDEX index_union_name_password(username,password)
再來(lái)執(zhí)行：

耗時(shí)：0.001s。

開(kāi)篇也說(shuō)過(guò)軟件層面的優(yōu)化一是合理加索引；二是優(yōu)化執(zhí)行慢的sql。此二者相輔相成，缺一不可，如果加了索引，還是查詢很慢，這時(shí)候就要考慮是sql的問(wèn)題了，優(yōu)化sql。

Tips：

1：加了索引，依然全表掃描的可能情況有：

索引列為字符串，而沒(méi)帶引號(hào)；

索引列沒(méi)出現(xiàn)在where條件后面；

索引列出現(xiàn)的位置沒(méi)在前面。

2：關(guān)聯(lián)查詢不走索引的可能情況有：

關(guān)聯(lián)的多張表的字符集不一樣；

關(guān)聯(lián)的字段的字符集不一樣；

存儲(chǔ)引擎不一樣；

字段的長(zhǎng)度不一樣。

關(guān)于怎么在mysql中根據(jù)索引優(yōu)化查詢速度就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。