写在前面的话:
之前曾说过“不要求每个人一定理解 联表查询(join/left join/inner join等)时的mysql运算过程”,但对于字段选择性差意味着什么,组合索引字段顺序意味着什么,要求每个人必须了解;
重复上一次的话:把mysql客户端(如SQLyog,如HeidiSQL)放在桌面上,时不时拿出来 explain 一把,这是一种美德!
确保亲手查过SQL的执行计划,一定要注意看执行计划里的 possible_keys、key和rows这三个值,让影响行数尽量少,保证使用到正确的索引,减少不必要的Using temporary/Using filesort;
不要在选择性非常差的字段上建索引,原因参见优化策略A;
查询条件里出现范围查询(如A>7,A in (2,3))时,要警惕,不要建了组合索引却完全用不上,原因参见优化策略B;
我们先回顾一下字段选择性的基础知识。
——字段选择性的基础知识——
引子:什么字段都可以建索引吗?
如下表所示,sort 字段的选择性非常差,你可以执行 show index from ads 命令可以看到 sort 的 Cardinality(散列程度)只有 9,这种字段上本不应该建索引:
Table
Non_unique
Key_name
Seq_in_index
Column_name
Collation
Cardinality
Sub_part
Packed
Null
Index_type
Comment
ads
1
sort
1
sort
A
9
\N
\N
BTREE
优化策略A:字段选择性
选择性较低索引 可能带来的性能问题
索引选择性=索引列唯一值/表记录数;
选择性越高索引检索价值越高,消耗系统资源越少;选择性越低索引检索价值越低,消耗系统资源越多;
查询条件含有多个字段时,不要在选择性很低字段上创建索引
可通过创建组合索引来增强低字段选择性和避免选择性很低字段创建索引带来副作用;
尽量减少possible_keys,正确索引会提高sql查询速度,过多索引会增加优化器选择索引的代价,不要滥用索引;
再回顾组合索引与范围查询的业务场景。
——组合索引字段顺序与范围查询之间的关系——
引子:范围查询 city_id in (0,8,10) 能用组合索引 (ads_id,city_id) 吗?
举例,
ac 表有一个组合索引(ads_id,city_id)。
那么如下 ac.city_id IN (0, 8005) 查询条件能用到 ac表的组合索引(ads_id,city_id) 吗?
EXPLAIN
SELECT ac.ads_id
FROM ads, ac
WHERE
ads.id = ac.ads_id
AND ac.city_id IN (0, 8005)
AND ads.status = 'online'
AND ac.start_time<UNIX_TIMESTAMP()
AND ac.end_time>UNIX_TIMESTAMP()
优化策略B:
由于 mysql 索引是基于 B-Tree 的,所以组合索引有“字段顺序”概念。
所以,查询条件中有 ac.city_id IN (0, 8005),而组合索引是 (ads_id,city_id),则该查询无法使用到这个组合索引。
DBA总结道:
组合索引查询的各种场景
兹有 Index (A,B,C) ——组合索引多字段是有序的,并且是个完整的BTree 索引。
下面条件可以用上该组合索引查询:
A>5
A=5 AND B>6
A=5 AND B=6 AND C=7
A=5 AND B IN (2,3) AND C>5
下面条件将不能用上组合索引查询:
B>5 ——查询条件不包含组合索引首列字段
B=6 AND C=7 ——查询条件不包含组合索引首列字段
下面条件将能用上部分组合索引查询:
A>5 AND B=2 ——当范围查询使用第一列,查询条件仅仅能使用第一列
A=5 AND B>6 AND C=2 ——范围查询使用第二列,查询条件仅仅能使用前二列
组合索引排序的各种场景
兹有组合索引 Index(A,B)。
下面条件可以用上组合索引排序:
ORDER BY A——首列排序
A=5 ORDER BY B——第一列过滤后第二列排序
ORDER BY A DESC, B DESC——注意,此时两列以相同顺序排序
A>5 ORDER BY A——数据检索和排序都在第一列
下面条件不能用上组合索引排序:
ORDER BY B ——排序在索引的第二列
A>5 ORDER BY B ——范围查询在第一列,排序在第二列
A IN(1,2) ORDER BY B ——理由同上
ORDER BY A ASC, B DESC ——注意,此时两列以不同顺序排序
顺着组合索引怎么建继续往下延伸,请各位注意“索引合并”概念:
MySQL 5,0以下版本,SQL查询时,一张表只能用一个索引(use at most only one index for each referenced table),
从 MySQL 5.0开始,引入了 index merge 概念,包括 Index Merge Union Access Algorithm(多个索引并集访问),包括Index Merge Intersection Access Algorithm(多个索引交集访问),可以在一个SQL查询里用到一张表里的多个索引。
MySQL 在5.6.7之前,使用 index merge 有一个重要的前提条件:没有 range 可以使用。[出自参考资源2]
索引合并的简单说明:
MySQL 索引合并能使用多个索引
SELECT * FROM TB WHERE A=5 AND B=6
能分别使用索引(A) 和 (B) 或 索引合并;
创建组合索引(A,B) 更好;
SELECT * FROM TB WHERE A=5 OR B=6
能分别使用索引(A) 和 (B) 或 索引合并;
组合索引(A,B)不能用于此查询,分别创建索引(A) 和 (B)会更好;
最后的总结:
仍然是强调再强调:
记住,explain 后再提测是一种美德!
参考资源:
1)中文译稿,2013,MySQL 索引最佳实践之问题反馈;
2)orczhou,2013,MySQL优化器:index merge介绍;
3)orczhou,2013,index merge的补充说明;
4)zhengyun,2013,[慢查优化]联表查询注意谁是驱动表 & 你搞不清楚谁join谁更好时请放手让mysql自行判定;
5)nocode,2013,MySQL Internals-Index Merge优化;
http://www.sql8.net
分享到:
相关推荐
order by 后跟的字段尽量是索引字段,如果这个字段不是索引字段可以考虑时候可以给这个排序字段加上索引 使用存储过程优化 复杂的业务逻辑可以使用存储过程来实现 优点: 减少网络流量--将多种操作放在一个过程...
JAVA面试题MySQL索引原理及索引优化校招面试找工作笔试 目录: 基本概念 MySQL索引结构的分类 ...所谓索引的选择性是指不重复的索引值与表记录数的比值 索引优化 最左前缀原则 Hash索引 介绍、特点。
5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替: A、正确选择复合索引中的主列字段,...
降龙十八掌内功的入门基础,建立索引常用...5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替
主键的选择还要注意组合主键的字段次序,对于组合主键来说,不同的字段次序的主键的性能差别可能会很大,一般 应该选择重复率低、单独或者组合查询可能性大的字段放在前面。 1.3.4 外键的设计 外键作为数据库对象,...
其次,看是否用上正确的索引了,特别复杂的 SQL 语句,当其中 where 子句包含多个带有索引的字段时,更应该注意索引的选择是否合理。错误的索引不仅不会带来性能的提高,相反往往导致性能的降低。 针对如何用...
以SQL Server为工具,讲解SQL语言的应用,提供了近500个曲型应用,读者可以随查随用,深入讲解SQL语言的各种查询语句,详细介绍数据库设计及管理,详细讲解存储过程、解发器和游标等知识,讲解了SQL语言在高级语言中...
因此在大型数据库设计中,只有进行合理的索引字段选择,才能有效提高整个数据库的操作效率。 有时候为了提高性能。减少表的关联,恰当的数据冗余是允许的。 索引对新增,删除,更新的性能影响比较大,对相关的表的...
如果查询中包含了子查询,那么注意首先优化子查询 注意关联子查询,尽量减少关联子查询的使用,因为它的代价很高,并且非常消耗CPU 在Sql语句中使用not exists 代替 not in 用表连接替换EXISTS 使用带有前导字段的...
此外,还要注意在使用字符串类型(如VARCHAR)时,根据实际需求选择适当的字符集和校对规则,以确保正确的数据存储和查询。 使用最小化的数据类型:选择最小化的数据类型来存储数据,以减少存储空间的占用和提高...
本Word文档旨在提供关于建表规范、SQL规范以及索引规范的详细指导,帮助数据库管理员和开发人员更有效地进行数据库设计、SQL语句编写以及索引优化,提升数据库的性能和可维护性。 二、建表规范 表名规范:表名应...
①选择唯一性索引:值是唯一的,查询的更快。 ②经常作为查询条件的字段加索引。 ③为经常需要排序、分组和联合操作的字段建立索引:order by、group by、union(联合)、distinct(去重)等。 ④限制索引个数:索引数量...
SQL Server中数据的访问方法: 表扫描法:当访问未建索引的表内数据时,从表的起始处逐行查找,直到符合查询条件为止。 使用索引:当使用索引访问建有索引的表内数据时,系统会通过遍历索引树结构来查找行的存储位置...
5、索引应该建在选择性高的字段上; 6、索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引; 7、复合索引的建立需要进行仔细分析;尽量考虑用单字段索引代替: A、正确选择复合索引中的主列字段,...
使用触发器派生字段值 第章安全管理 创建角色语句 删除角色语句 授权语句数据库权限 授权语句对象权限 授权语句角色权限 回收权限语句数据库权限 回收权限语句对象权限 回收权限语句角色权限 策略与标记...
如果要对用户所用的登录终端进行限制,可以更新User表中相应用户的Host字段,在进行了以上更改后应重新启动数据库服务,此时登录时可用如下类似命令: mysql -uroot -p; mysql -uroot -pnewpassword; mysql mydb -u...
此规范可有效提高SQL代码的可读性及性能,降低维护成本 一、文档说明 1.1 文档目的 ...7.6 避免在索引字段上添加函数 7.7 引入工作表概念 7.8 定期清理或归档表中的垃圾数据 7.9 使用分区表 7.10 其他注意点
该模块是索引搜索开发加速器,允许开发人员在索引上声明计算的搜索字段,该索引可以捕获和存储范围内项目的目标字段值以及基于作为其表示形式的特定模板的项目字段值。 另外,可以使用搜索增强功能将特定权重应用于...
介绍在使用 Elasticsearch 进行数据搜索和分析时,应该遵循的一些规范和建议,包括 ES 架构的选择、索引结构的设计、查询语句的优化、写入优化等等,以提高 Elasticsearch 的性能、可靠性、安全性和可维护性。...
筛选索引字段 189 在联接字段上进行筛选 189 持续更新时间参数 189 结束时间或管理器接收时间 189 在活动频道中排序 190 使用标准内容中的“实时”频道 190 i/o 子系统性能 190 诊断: 从基本频道特性开始 191 自定义...