MySQL中索引的优化的示例详解

目录

• 使用索引优化
• 数据准备
• 避免索引失效应用-全值匹配
• 避免索引失效应用-最左前缀法则
• 避免索引失效应用-其他匹配原则

使用索引优化

索引是数据库优化最常用也是最重要的手段之一,通过索引通常可以帮助用户解决大多数的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: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，依然跟顺序无关
 
-- 违反最左前缀法则，索引失效
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索引优化内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！