目錄

• 使用索引優化
• 數據準備
• 避免索引失效應用-全值匹配
• 避免索引失效應用-最左前綴法則
• 避免索引失效應用-其他匹配原則

使用索引優化

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

數據準備

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"); -- 創建組合索引create index index_seller_name_sta_addr on tb_seller(name,status,address);

避免索引失效應用-全值匹配

該情況下，索引生效，執行效率高。

-- 避免索引失效應用-全值匹配-- 全值匹配，和字段匹配成功即可，和字段順序無關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 ="北京市";

避免索引失效應用-最左前綴法則

該情況下，索引生效,執行效率高。

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

避免索引失效應用-其他匹配原則

該情況下，索引生效，執行效率高。

1、情況一

-- 避免索引失效應用-其他匹配原則-- 范圍查詢右邊的列，不能使用索引explain select * from tb_seller  where name= "小米科技" and status >"1" and address="北京市";-- key_lem:410,沒有使用status這個索引-- 不要在索引列上進行運算操作，索引將失效。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|會對數據使用一個外部的索引排序，而不是按照表內的索引順序進行讀取，稱為“文件排序" ,效率低。using temporary需要建立臨時表(temporary table)來暫存中間結果，常見于order by和group by;效率低using indexSQL所需要返回的所有列數據均在一棵索引樹上，避免訪問表的數據行，效率不錯using where在查找使用索引的情況下，需要回表去查詢所需的數據using index condition查找使用了索引，但是需要回表查詢數據using index；using where查找使用了索引，但是需要的數據都在索引列中能找到，所以不需要回表查詢數據

但是再加有個password

3、情況三

4、情況四

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

驗證

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

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