怎樣優化MySql數據庫的索引？

這篇文章給大家分享的是MySql數據庫的索引優化的詳細介紹，相信大部分人都還沒學會這個技能，為了讓大家學會，給大家總結了以下內容，話不多說，一起往下看吧。

索引是數據庫優化最重要的手段，當我們遇到數據庫性能問題的時候首先想到的就應該是索引優化，我們通過一個例子來看看索引對查詢效率的影響究竟有多大：
我們還是沿用上一講的數據庫里面有一百萬條數據:
根據id查詢index_test表看一下它的查詢計劃：
查詢速度很快，因為id上有主鍵索引，這里是索引查詢
再來根據user字段查詢一下：
花了0.57秒，
如果在user字段上加上索引效果會如何呢？
create index idx_item_user on index_test(user);
再來查詢一下:
所用時間趨近于0，是不是很完美，這就是索引的魔力
索引如此神奇我們是不是該好好利用，可惜有時候明明建了索引在查詢的時候卻不通過索引查詢，這又是什么原因呢？下面就來好好研究一下吧。
準備環境:
創建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','聯想科技','聯想','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
INSERT INTO `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) VALUES('itheima','中糧集團','中糧','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');
創建一個聯合索引
CREATE INDEX idx_seller_name_sta_addr ON tb_seller(NAME,STATUS,address);
如何才能避免索引失效呢?
1） 查詢的時候使用索引的所有字段進行精確匹配
當按照索引中所有的列進行精確匹配時索引可以被用到。
 
2） 最左前綴法則:
如果創建多個列的組合索引要遵守最左前綴法則，指的是查詢從索引的最左邊的列開始，不能跳過索引中的列,所以我們在創建組合索引時把where子句中出現最頻繁的一列放在最左邊。
本例中我們建立了組合索引(NAME,STATUS,ADDRESS),相當于創建了單列索引(NAME),組合索引(NAME,STATUS),組合索引(NAME,STATUS,ADDRESS)
所以在查詢時想讓索引生效where條件只能使用：
 
Name
Name,Status
Name,Status,Address
這樣的組合
 
其它方式索引不會生效:
Status,
Status,Address
如果是這種方式:Name,Address,則只會用到Name的索引，不會用到Address的索引
打個比方就好比穿橋洞
Name是第一個橋洞，Status是第二個，Address是第三個
我們只能穿過第一個橋洞以后才能穿第二個第三個，不能繞過第一個直接穿第二個，第三個，也不能穿過第一個以后直接跳到第三個。
當然如果是這種方式:
Status,Name
Status,Name,Address
也是可以用到索引的
為什么呢？不是最左匹配嗎？原來MySql的查詢優化器會會判斷究竟以什么順序查詢效率最高，然后根據情況調整查詢的順序，讓我們也可以用上索引。
 
3） 范圍查詢右邊的列，不能使用索引
比較一下這兩個查詢:
第一個查詢的key_len長度為813三個索引都用到了，第二個查詢key_len長度為410Name和status這兩個條件走了索引，address沒有走索引
4） 不要在索引列上進行運算操作,否則不會使用索引
5） 字符串不加單引號不會使用索引
比較下面兩個查詢
第一個查詢key_len為410說明name和status都使用了索引，第二個查詢key_len為403說明name使用了索引而status沒有使用，原因是mysql的查詢優化器會進行自動類型轉換導致索引失效。
6） 查詢的字段盡量都是建立索引的字段，不要用select *
第一個查詢的extra為Using where 表示在查找使用索引的情況下，需要回表去查詢所需的數據。
第二個查詢的extra為Using where,Using index表示查找使用了索引，并且要查找的數據都在索引里能找到所以不需要回表查詢。
7） 用or分開的條件,如果or前的條件中的列有索引，后面的列中沒有索引，那么涉及的索引都不會被用到。
8） 以%開頭的like模糊查詢索引失效，如果%在結尾索引不會失效：
這里使用了索引
這里沒有使用索引
如何解決這個問題呢？可以通過覆蓋索引來解決
可以讓我們的查詢列都在索引列里
查詢的列sellerid,name都是索引列，所以即使使用了模糊查詢也可以使用索引
9） 如果mysql評估使用索引比全表掃描更慢則不會使用索引
先在address上創建索引
create index idx_item_address on tb_seller(address);
    Address上建了索引為什么這里沒用索引呢？因為這張表中總共十條記錄有九條都是北京市，所以用索引去查不如全表掃描快。
再來看一個查詢:
 
這里用到了索引。
10) in走索引，not in不走索引

關于MySql數據庫的索引優化就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果喜歡這篇文章，不如把它分享出去讓更多的人看到。