快捷導(dǎo)航

MySQL中索引的優(yōu)化的示例詳解

更新時(shí)間：2023年02月19日 15:40:46 作者：陽(yáng)862

索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一,通過索引通常可以幫助用戶解決大多數(shù)的MySQL的性能優(yōu)化問題。本文主要來講講索引優(yōu)化的方法，希望對(duì)大家有所幫助

使用索引優(yōu)化

索引是數(shù)據(jù)庫(kù)優(yōu)化最常用也是最重要的手段之一,通過索引通?？梢詭椭脩艚鉀Q大多數(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:403
explain select * from tb_seller ts where name='小米科技' and status ='1';-- key_lem:410
explain 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這個(gè)索引
-- 不要在索引列上進(jìn)行運(yùn)算操作，索引將失效。
explain select * from tb_seller where substring(name,3,2) ='科技';-- 沒有使用索引
-- 字符串不加單引號(hào)，造成索引失效。
explain select * from tb_seller where name='小米科技' and status = 1 ;-- key_lem:403，沒有使用status這個(gè)索引

2、情況二

explain中的extra列

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