怎樣優(yōu)化MySql數(shù)據(jù)庫的索引？

這篇文章給大家分享的是MySql數(shù)據(jù)庫的索引優(yōu)化的詳細(xì)介紹，相信大部分人都還沒學(xué)會這個(gè)技能，為了讓大家學(xué)會，給大家總結(jié)了以下內(nèi)容，話不多說，一起往下看吧。

索引是數(shù)據(jù)庫優(yōu)化最重要的手段，當(dāng)我們遇到數(shù)據(jù)庫性能問題的時(shí)候首先想到的就應(yīng)該是索引優(yōu)化，我們通過一個(gè)例子來看看索引對查詢效率的影響究竟有多大：
我們還是沿用上一講的數(shù)據(jù)庫里面有一百萬條數(shù)據(jù):
根據(jù)id查詢index_test表看一下它的查詢計(jì)劃：
查詢速度很快，因?yàn)?/span>id上有主鍵索引，這里是索引查詢
再來根據(jù)user字段查詢一下：
花了0.57秒，
如果在user字段上加上索引效果會如何呢？
create index idx_item_user on index_test(user);
再來查詢一下:
所用時(shí)間趨近于0，是不是很完美，這就是索引的魔力
索引如此神奇我們是不是該好好利用，可惜有時(shí)候明明建了索引在查詢的時(shí)候卻不通過索引查詢，這又是什么原因呢？下面就來好好研究一下吧。
準(zhǔn)備環(huán)境:
創(chuàng)建tb_seller表
create table `tb_seller` (
       `sellerid` varchar (100),
       `name` varchar (100),
       `nickname` varchar (50),
       `password` varchar (60),
       `status` varchar (1),
       `address` varchar (100),
       `createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('huawei','華為科技有限公司','華為小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itcast','聯(lián)想科技','聯(lián)想','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itheima','中糧集團(tuán)','中糧','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('luoji','羅技科技有限公司','羅技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('oppo','OPPO科技有限公司','OPPO官方旗艦店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('sina','新浪科技有限公司','新浪官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('xiaomi','小米科技','小米官方旗艦店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('yijia','宜家家居','宜家家居旗艦店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
創(chuàng)建一個(gè)聯(lián)合索引
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(NAME,STATUS,address);
如何才能避免索引失效呢?
1） 查詢的時(shí)候使用索引的所有字段進(jìn)行精確匹配
當(dāng)按照索引中所有的列進(jìn)行精確匹配時(shí)索引可以被用到。
 
2） 最左前綴法則:
如果創(chuàng)建多個(gè)列的組合索引要遵守最左前綴法則，指的是查詢從索引的最左邊的列開始，不能跳過索引中的列,所以我們在創(chuàng)建組合索引時(shí)把where子句中出現(xiàn)最頻繁的一列放在最左邊。
本例中我們建立了組合索引(NAME,STATUS,ADDRESS),相當(dāng)于創(chuàng)建了單列索引(NAME),組合索引(NAME,STATUS),組合索引(NAME,STATUS,ADDRESS)
所以在查詢時(shí)想讓索引生效where條件只能使用：
 
Name
Name,Status
Name,Status,Address
這樣的組合
 
其它方式索引不會生效:
Status,
Status,Address
如果是這種方式:Name,Address,則只會用到Name的索引，不會用到Address的索引
打個(gè)比方就好比穿橋洞
Name是第一個(gè)橋洞，Status是第二個(gè)，Address是第三個(gè)
我們只能穿過第一個(gè)橋洞以后才能穿第二個(gè)第三個(gè)，不能繞過第一個(gè)直接穿第二個(gè)，第三個(gè)，也不能穿過第一個(gè)以后直接跳到第三個(gè)。
當(dāng)然如果是這種方式:
Status,Name
Status,Name,Address
也是可以用到索引的
為什么呢？不是最左匹配嗎？原來MySql的查詢優(yōu)化器會會判斷究竟以什么順序查詢效率最高，然后根據(jù)情況調(diào)整查詢的順序，讓我們也可以用上索引。
 
3） 范圍查詢右邊的列，不能使用索引
比較一下這兩個(gè)查詢:
第一個(gè)查詢的key_len長度為813三個(gè)索引都用到了，第二個(gè)查詢key_len長度為410Name和status這兩個(gè)條件走了索引，address沒有走索引
4） 不要在索引列上進(jìn)行運(yùn)算操作,否則不會使用索引
5） 字符串不加單引號不會使用索引
比較下面兩個(gè)查詢
第一個(gè)查詢key_len為410說明name和status都使用了索引，第二個(gè)查詢key_len為403說明name使用了索引而status沒有使用，原因是mysql的查詢優(yōu)化器會進(jìn)行自動類型轉(zhuǎn)換導(dǎo)致索引失效。
6） 查詢的字段盡量都是建立索引的字段，不要用select *
第一個(gè)查詢的extra為Using where 表示在查找使用索引的情況下，需要回表去查詢所需的數(shù)據(jù)。
第二個(gè)查詢的extra為Using where,Using index表示查找使用了索引，并且要查找的數(shù)據(jù)都在索引里能找到所以不需要回表查詢。
7） 用or分開的條件,如果or前的條件中的列有索引，后面的列中沒有索引，那么涉及的索引都不會被用到。
8） 以%開頭的like模糊查詢索引失效，如果%在結(jié)尾索引不會失效：
這里使用了索引
這里沒有使用索引
如何解決這個(gè)問題呢？可以通過覆蓋索引來解決
可以讓我們的查詢列都在索引列里
查詢的列sellerid,name都是索引列，所以即使使用了模糊查詢也可以使用索引
9） 如果mysql評估使用索引比全表掃描更慢則不會使用索引
先在address上創(chuàng)建索引
create index idx_item_address on tb_seller(address);
    Address上建了索引為什么這里沒用索引呢？因?yàn)檫@張表中總共十條記錄有九條都是北京市，所以用索引去查不如全表掃描快。
再來看一個(gè)查詢:
 
這里用到了索引。
10) in走索引，not in不走索引

關(guān)于MySql數(shù)據(jù)庫的索引優(yōu)化就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，可以學(xué)到更多知識。如果喜歡這篇文章，不如把它分享出去讓更多的人看到。