目錄

• 使用索引優(yōu)化
• 數(shù)據(jù)準(zhǔn)備
• 避免索引失效應(yīng)用-全值匹配
• 避免索引失效應(yīng)用-最左前綴法則
• 避免索引失效應(yīng)用-其他匹配原則

使用索引優(yōu)化

索引是數(shù)據(jù)庫優(yōu)化最常用也是最重要的手段之一,通過索引通常可以幫助用戶解決大多數(shù)的MySQL的性能優(yōu)化問題。

數(shù)據(jù)準(zhǔn)備

use world;  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));  insert into tb_seller values("alibaba","阿里巴巴","阿里小店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("baidu","百度科技有限公司","百度小店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("huawei","華為科技有限公司","華為小店","e10adc3949ba59abbe057f20f883e","0","北京市","2088-01-01 12:00:00"),							("itcast","傳智播客教育科技有限公司","傳智播客","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("itheima","黑馬程序員","黑馬程序員","e10adc3949ba59abbe057f20f883e","0","北京市","2088-01-01 12:00:00"),							("luoji","羅技科技有限公司","羅技小店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("oppo","oppo科技有限公司","oppo官方旗艦店","e10adc3949ba59abbe057f20f883e","0","北京市","2088-01-01 12:00:00"),							("ourpalm","掌趣科技股份有限公司","掌趣小店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("qiandu","千度科技","千度小店","e10adc3949ba59abbe057f20f883e","2","北京市","2088-01-01 12:00:00"),							("sina","新浪科技有限公司","新浪官方旗艦店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"),							("xiaomi","小米科技","小米官方旗艦店","e10adc3949ba59abbe057f20f883e","1","西安市","2088-01-01 12:00:00"),							("yijia","宜家家居","宜家官方旗艦店","e10adc3949ba59abbe057f20f883e","1","北京市","2088-01-01 12:00:00"); -- 創(chuàng)建組合索引create index index_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效應(yīng)用-全值匹配

該情況下，索引生效，執(zhí)行效率高。

-- 避免索引失效應(yīng)用-全值匹配-- 全值匹配，和字段匹配成功即可，和字段順序無關(guān)explain select * from tb_seller ts where name ="小米科技" and status ="1" and address ="北京市"; explain select * from tb_seller ts where status ="1" and name ="小米科技" and address ="北京市";

避免索引失效應(yīng)用-最左前綴法則

該情況下，索引生效,執(zhí)行效率高。

-- 避免索引失效應(yīng)用-最左前綴法則-- 如果索引了多列，要遵守最左前綴法則，指的是查詢從索引的最左前列開始，并且不跳過索引中的列explain select * from tb_seller ts where name="小米科技";-- key_lem:403explain select * from tb_seller ts where name="小米科技" and status ="1";-- key_lem:410explain select * from tb_seller ts where status ="1" and name="小米科技" ;-- key_lem:410，依然跟順序無關(guān) -- 違反最左前綴法則，索引失效explain select * from tb_seller ts where  status ="1";-- 違反最左前綴法則，索引失效 -- 如果符合最左前綴法則，但是出現(xiàn)跳躍某一列，只有最左列索引生效explain select * from tb_seller where name="小米科技" and address="北京市";-- key_lem:403

避免索引失效應(yīng)用-其他匹配原則

該情況下，索引生效，執(zhí)行效率高。

1、情況一

-- 避免索引失效應(yīng)用-其他匹配原則-- 范圍查詢右邊的列，不能使用索引explain select * from tb_seller  where name= "小米科技" and status >"1" and address="北京市";-- key_lem:410,沒有使用status這個索引-- 不要在索引列上進(jìn)行運(yùn)算操作，索引將失效。explain select * from tb_seller where substring(name,3,2) ="科技";-- 沒有使用索引-- 字符串不加單引號，造成索引失效。explain select * from tb_seller where name="小米科技" and status = 1 ;-- key_lem:403，沒有使用status這個索引

2、 情況二

explain中的extra列

extra含義using filesort說明mysq|會對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取，稱為“文件排序" ,效率低。using temporary需要建立臨時表(temporary table)來暫存中間結(jié)果，常見于order by和group by;效率低using indexSQL所需要返回的所有列數(shù)據(jù)均在一棵索引樹上，避免訪問表的數(shù)據(jù)行，效率不錯using where在查找使用索引的情況下，需要回表去查詢所需的數(shù)據(jù)using index condition查找使用了索引，但是需要回表查詢數(shù)據(jù)using index；using where查找使用了索引，但是需要的數(shù)據(jù)都在索引列中能找到，所以不需要回表查詢數(shù)據(jù)

但是再加有個password

3、情況三

4、情況四

5、 如果MySQL評估使用索引比全表更慢，則不使用索引。is NULL , is NOT NULL有時有效,有時索引失效。in走索引，not in索引失效。單列索引和復(fù)合索引，盡量使用符合索引

驗證

創(chuàng)建了單一的三個索引，最后面where全使用了但explain顯示只用了index_name

到此這篇關(guān)于MySQL中索引的優(yōu)化的示例詳解的文章就介紹到這了,更多相關(guān)MySQL索引優(yōu)化內(nèi)容請搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持！