索引：索引使用、索引失效 - 版本历史

Eijux：/* “in”与“exists”是否使用索引 */

2021-05-07T13:59:33Z

“in”与“exists”是否使用索引

←上一版本		2021年5月7日 (五) 21:59的版本
第222行：		第222行：

	==== “in”与“exists”是否使用索引 ====		==== “in”与“exists”是否使用索引 ====
	~~首先，“in”是范围查询，而“exists”不是范围查询。~~
	* 见：“[https://segmentfault.com/a/1190000023825926 不要再问我 in，exists 走不走索引了]”		* 见：“[https://segmentfault.com/a/1190000023825926 不要再问我 in，exists 走不走索引了]”


			首先，“in”是范围查询，而“exists”不是范围查询。
	# “in”：		# “in”：
	#: 对于如下语句：		#: 对于如下语句：
	<syntaxhighlight lang="mysql" highlight="">		#: <syntaxhighlight lang="mysql" highlight="">
	select * from t1 where name in (select name from t2);		select * from t1 where name in (select name from t2);
	</syntaxhighlight>		</syntaxhighlight>
第252行：		第253行：
	#: 由上可以看出，对于外层查询，是不使用索引的，即对于每一个外层查询的数据都会在子查询中验证是否存在。		#: 由上可以看出，对于外层查询，是不使用索引的，即对于每一个外层查询的数据都会在子查询中验证是否存在。

	而“in”与“exists”的查询效率，可能根据 MySQL 版本不同、数据量大小不同而不一。		* 而“in”与“exists”的查询效率，可能根据 MySQL 版本不同、数据量大小不同而不一。

	=== “order by”是否使用索引？ ===		=== “order by”是否使用索引？ ===

Eijux：/* 范围查询是否使用索引？ */

2021-05-06T18:48:48Z

范围查询是否使用索引？

@@ 第220行： / 第220行： @@
 select num from a where exists(select 1 from b where num=a.num)
 </syntaxhighlight>
 === “order by”是否使用索引？ ===

Eijux：/* 索引设计原则 */

2021-05-06T15:29:37Z

索引设计原则

←上一版本		2021年5月6日 (四) 23:29的版本
第547行：		第547行：
	#: 比如“a = 1 and b = 2 and c = 3 ”建立“(a,b,c)”索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；		#: 比如“a = 1 and b = 2 and c = 3 ”建立“(a,b,c)”索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式；
	# 尽量选择'''区分度高的列作为索引'''。【区分度：count(distinct col)/count(*)】		# 尽量选择'''区分度高的列作为索引'''。【区分度：count(distinct col)/count(*)】
			#: 区分度在 '''80%''' 以上的时候就可以建立索引；
	# 索引列不能参与计算，保持列“干净”。		# 索引列不能参与计算，保持列“干净”。
	#: 比如“from_unixtime(create_time) = '2014-05-29'”就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成“create_time = unix_timestamp(’2014-05-29’)”；		#: 比如“from_unixtime(create_time) = '2014-05-29'”就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成“create_time = unix_timestamp(’2014-05-29’)”；

Eijux：/* explain执行计划中的索引 */

2021-05-06T15:20:55Z

explain执行计划中的索引

←上一版本		2021年5月6日 (四) 23:20的版本
第155行：		第155行：
	* “type”：联接类型；		* “type”：联接类型；
	*: <syntaxhighlight lang="mysql" highlight="">		*: <syntaxhighlight lang="mysql" highlight="">
	All \| index \| range \| ref \| eq_ref \| const \| system ~~\| null~~		All \| index \| range \| ref \| eq_ref \| const \| system

			All：全表扫描
			index：全索引扫描
			range：有范围的索引扫描
			ref_or_null：表关联时，包含 NULL 值的ref【索引包含NULL】
			ref：表关联时，最左前缀、非主键和unique的索引扫描【索引无法确定单个行】
			eq_ref：表关联时，主键或unique的索引扫描【索引可以确定单个行】
			const：索引仅有一个匹配行【所以可以看作常量】
			system：系统表【const特例】
	</syntaxhighlight>		</syntaxhighlight>
	* “possible_keys”：可能的索引选择；		* “possible_keys”：可能的索引选择；

Eijux：/* like模糊查询的优化 */

2021-05-06T14:43:10Z

like模糊查询的优化

@@ 第461行： / 第461行： @@
 复制代码当开启了 ICP 之后，在执行计划 Extra 可以看到“'''Using index condition'''”提示。
-=== like模糊查询的优化 ===
+=== like 模糊查询的优化 ===
 模糊查询的三种情况：
 # “'''like keyword%'''”：索引有效。
-# “'''like %keyword'''”：索引失效，但可以通过'''翻转函数'''优化。
+# “'''like %keyword'''”：索引失效，但可以通过'''翻转函数'''优化。【？？？】
 #: <syntaxhighlight lang="mysql" highlight="2">
 where i.C_LCN_NO like '%245'                -- 使用索引
@@ 第495行： / 第495行： @@
 WHERE INSTR(t.field,"xxx") > 0
 </syntaxhighlight>
 == 索引设计原则 ==

Eijux：/* “松散索引扫描”与“紧凑索引扫描” */

2021-05-06T13:47:38Z

“松散索引扫描”与“紧凑索引扫描”

←上一版本		2021年5月6日 (四) 21:47的版本
第344行：		第344行：
	*# “AVG(DISTINCT)”，“SUM(DISTINCT)”和“COUNT(DISTINCT)”可以使用松散索引扫描。		*# “AVG(DISTINCT)”，“SUM(DISTINCT)”和“COUNT(DISTINCT)”可以使用松散索引扫描。
	# AVG(DISTINCT), SUM(DISTINCT) 只能使用单一列作为参数。而 COUNT(DISTINCT) 可以使用多列参数。		# AVG(DISTINCT), SUM(DISTINCT) 只能使用单一列作为参数。而 COUNT(DISTINCT) 可以使用多列参数。
	*# 在查询中没有“group ~~by”和“distinct”条件。【？？？】~~		*# 在查询中没有“group by”和“distinct”条件。
			*# 之前声明的松散扫描限制条件同样起作用。
	* 在执行计划中通常可以看到“'''using index for group-by'''”。		* 在执行计划中通常可以看到“'''using index for group-by'''”。
	*: <syntaxhighlight lang="mysql" highlight="">		*: <syntaxhighlight lang="mysql" highlight="">

Eijux：/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

2021-05-06T13:40:41Z

“group by”的“松散索引扫描”与“紧凑索引扫描”

←上一版本		2021年5月6日 (四) 21:40的版本
第292行：		第292行：
	#: 分组字段不以索引最左前缀开始，但查询条件 email='kevin@qq.com' 提供了这部分常量；		#: 分组字段不以索引最左前缀开始，但查询条件 email='kevin@qq.com' 提供了这部分常量；

	==== ~~“group by”的“松散索引扫描”与“紧凑索引扫描”~~ ====		==== “松散索引扫描”与“紧凑索引扫描” ====
	通过“松散索引扫描”（Loose Index Scan）和“紧凑索引扫描”（Tight Index Scan）可以高效快速地完成“group by”操作。		通过“松散索引扫描”（Loose Index Scan）和“紧凑索引扫描”（Tight Index Scan）可以高效快速地完成“group by”操作。

Eijux：/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

2021-05-06T13:40:00Z

“group by”的“松散索引扫描”与“紧凑索引扫描”

←上一版本		2021年5月6日 (四) 21:40的版本
第296行：		第296行：


	#【不使用索引】：“group by”操作在没有合适的索引可用的时候，通常先扫描整个表提取数据并创建一个'''临时表'''，然后按照“group by”指定的列进行排序。		【不使用索引】：“group by”操作在没有合适的索引可用的时候，通常先扫描整个表提取数据并创建一个'''临时表'''，然后按照“group by”指定的列进行排序。
	#: 在这个临时表里面，对于每一个 group 的数据行来说是连续在一起的。完成排序之后，就可以发现所有的 groups，并可以执行聚集函数（aggregate function）。可以看到，在没有使用索引的时候，需要创建临时表和排序。（排序：“group by”能够进行隐式或显示排序，但不建议）		: 在这个临时表里面，对于每一个 group 的数据行来说是连续在一起的。完成排序之后，就可以发现所有的 groups，并可以执行聚集函数（aggregate function）。可以看到，在没有使用索引的时候，需要创建临时表和排序。（排序：“group by”能够进行隐式或显示排序，但不建议）
	#* 在执行计划中通常可以看到“'''Using temporary; Using filesort'''”。		* 在执行计划中通常可以看到“'''Using temporary; Using filesort'''”。
	#: <syntaxhighlight lang="mysql" highlight="">		: <syntaxhighlight lang="mysql" highlight="">
	CREATE TABLE `t1` (		CREATE TABLE `t1` (

第328行：		第328行：

	MySQ有两种索引扫描方式完成“group by”操作，就是上面提到的松散索引扫描和紧凑索引扫描。		MySQ有两种索引扫描方式完成“group by”操作，就是上面提到的松散索引扫描和紧凑索引扫描。
	~~#【“松散索引扫描”】方式下，分组操作和范围预测（如果有的话）一起执行完成的。~~
	~~#: 松散索引扫描相当于 Oracle 中的跳跃索引扫描（skip index scan），就是~~'''~~不需要连续的扫描索引中得每一个元组，扫描时仅考虑索引中得一部分~~'''。
	## 当查询中没有“where”条件的时候，松散索引扫描读取的索引元组的个数和 groups 的数量相同。		【“松散索引扫描”】：'''分组操作和范围预测（如果有的话）一起执行完成的'''。
	## 如果“where”条件包含范围预测，松散索引扫描查找每个 group 中第一个满足范围条件，然后再读取最少可能数的 ~~keys。~~		: 松散索引扫描相当于 Oracle 中的跳跃索引扫描（skip index scan），就是不需要连续的扫描索引中得每一个元组，扫描时仅考虑索引中得一部分。
	#: 松散索引扫描只需要读取很少量的数据就可以完成“group by”操作，因而执行效率非常高。		# 当查询中没有“where”条件的时候，松散索引扫描读取的索引元组的个数和 groups 的数量相同。
	#* 使用松散索引扫描需要满足以下条件：【如上节所述】		# 如果“where”条件包含范围预测，松散索引扫描查找每个 group 中第一个满足范围条件，然后再读取最少可能数的 keys。【？？？】
	#*# 查询在单一表上。		: 松散索引扫描只需要读取很少量的数据就可以完成“group by”操作，因而执行效率非常高。
	#*# “group by”指定的所有列是索引的一个最左前缀，并且没有其它的列。		* 使用松散索引扫描需要满足以下条件：【如上节所述】
	#*# 如果在选择列表select list中存在聚集函数，只能使用“min()”和“max()”两个聚集函数（如果“min()”和“max()”同时存在则需作用于是同一列）。这一列必须在索引中，且紧跟着“group by”指定的列。		*# 查询在单一表上。
	#*# 如果查询中存在除了“group by”指定的列之外的索引其他部分，那么必须以'''常量'''的形式出现（除了“min()”和“max()”两个聚集函数）。		*# “group by”指定的所有列是索引的一个最左前缀，并且没有其它的列。
	#*# 索引中的列必须索引整个数据列的值（full column values must be indexed），而不是一个“前缀索引”。		*# 如果在选择列表select list中存在聚集函数，只能使用“min()”和“max()”两个聚集函数（如果“min()”和“max()”同时存在则需作用于是同一列）。这一列必须在索引中，且紧跟着“group by”指定的列。
	#* 自从5.5开始，松散索引扫描可以作用于在“select list”中其它形式的聚集函数：		*# 如果查询中存在除了“group by”指定的列之外的索引其他部分，那么必须以'''常量'''的形式出现（除了“min()”和“max()”两个聚集函数）。
	#*# ~~AVG~~(DISTINCT)~~, SUM~~(DISTINCT)~~和COUNT~~(DISTINCT)~~可以使用松散索引扫描。AVG~~(DISTINCT), SUM(DISTINCT)~~只能使用单一列作为参数。而COUNT~~(DISTINCT)可以使用多列参数。		*# 索引中的列必须索引整个数据列的值（full column values must be indexed），而不是一个“前缀索引”。
	#*#		* 自从5.5开始，松散索引扫描可以作用于在“select list”中其它形式的聚集函数：
	#* 在执行计划中通常可以看到“'''using index for group-by'''”。		*# “AVG(DISTINCT)”，“SUM(DISTINCT)”和“COUNT(DISTINCT)”可以使用松散索引扫描。
	#*: <syntaxhighlight lang="mysql" highlight="">		# AVG(DISTINCT), SUM(DISTINCT) 只能使用单一列作为参数。而 COUNT(DISTINCT) 可以使用多列参数。
			*# 在查询中没有“group by”和“distinct”条件。【？？？】
			* 在执行计划中通常可以看到“'''using index for group-by'''”。
			*: <syntaxhighlight lang="mysql" highlight="">
	mysql> explain select c1,c2 from t1 group by c1,c2 \G		mysql> explain select c1,c2 from t1 group by c1,c2 \G
	************************* 1. row *************************		************************* 1. row *************************
第358行：		第361行：
	</syntaxhighlight>		</syntaxhighlight>

	~~#【“紧凑索引扫描”】方式下，先对索引执行范围扫描（range scan），再对结果元组进行分组。~~








	~~#: <syntaxhighlight lang="mysql" highlight="">~~

			【“紧凑索引扫描”】方式下，'''先对索引执行范围扫描（range scan），再对结果元组进行分组'''。
			: 紧凑索引扫描可能是全索引扫描或者范围索引扫描，取决于查询条件。当松散索引扫描条件没有满足的时候，“group by”仍然有可能避免创建临时表。如果在“where”条件有范围扫描，那么紧凑索引扫描仅读取满足这些条件的keys（索引元组），否则执行全索引扫描。这种方式读取所有“where”条件定义的范围内的 keys，或者扫描整个索引，因而称作紧凑索引扫描。对于紧凑索引扫描，只有在所有满足范围条件的 keys 被找到之后才会执行分组操作。
			* 如果紧凑索引扫描起作用，那么必须满足：【如上节所述】
			*: 在查询中存在“where”条件字段（索引中的字段）等于常量，且该字段在“group by”指定的字段的前面或者中间。来自于等于条件的常量能够填充搜索 keys 中的 gaps，因而可以构成一个索引的完整前缀。索引前缀能够用于索引查找。【即：分组字段与条件字段必须构成最左索引（顺序无要求），且字段条件必须是与常量的等式】
			** 如果要求对“group by”的结果进行排序，并且查找字段组成一个索引前缀，那么 MySQL 同样可以避免额外的排序操作。
			* 在执行计划中通常可以看到“'''using index'''”，相当于使用了“覆盖索引”。
			*: <syntaxhighlight lang="mysql" highlight="">
			mysql> explain extended select c1,c2 from t1 where c1=2 group by c2 \G
			************************* 1. row *************************
			id: 1
			select_type: SIMPLE
			table: t1
			type: ref
			possible_keys: idx_g
			key: idx_g
			key_len: 5
			ref: const
			rows: 5
			filtered: 100.00
			Extra: Using where; Using index
	</syntaxhighlight>		</syntaxhighlight>

Eijux：/* 范围查询是否使用索引？ */

2021-05-06T13:23:54Z

范围查询是否使用索引？

显示更改

Eijux：/* “索引失效” */

2021-05-06T09:59:25Z

“索引失效”

←上一版本		2021年5月6日 (四) 17:59的版本
第241行：		第241行：
	#* “like %keyword”：可以通过'''翻转函数'''优化。		#* “like %keyword”：可以通过'''翻转函数'''优化。
	# 用“'''or'''”分割开的条件，如果“or”前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。		# 用“'''or'''”分割开的条件，如果“or”前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
			#* 即，“or”前后的字段都必须具有索引。否则，可以考虑使用“'''union'''”替换：
			#: <syntaxhighlight lang="mysql" highlight="">
			select * from dept where dname='jaskey' or loc='bj' or deptno=45
			-- 使用 union 替换
			select * from dept where dname='jaskey' union
			select * from dept where loc='bj' union
			select * from dept where deptno=45
			</syntaxhighlight>
	# '''负向查询'''（not ，not in， not like ，<> ,!= ,!> ,!< ）。		# '''负向查询'''（not ，not in， not like ，<> ,!= ,!> ,!< ）。

索引：索引使用、索引失效 - 版本历史

Eijux：​/* “in”与“exists”是否使用索引 */

Eijux：​/* 范围查询是否使用索引？ */

Eijux：​/* 索引设计原则 */

Eijux：​/* explain执行计划中的索引 */

Eijux：​/* like模糊查询的优化 */

Eijux：​/* “松散索引扫描”与“紧凑索引扫描” */

Eijux：​/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

Eijux：​/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

Eijux：​/* 范围查询是否使用索引？ */

Eijux：​/* “索引失效” */

Eijux：/* “in”与“exists”是否使用索引 */

Eijux：/* 范围查询是否使用索引？ */

Eijux：/* 索引设计原则 */

Eijux：/* explain执行计划中的索引 */

Eijux：/* like模糊查询的优化 */

Eijux：/* “松散索引扫描”与“紧凑索引扫描” */

Eijux：/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

Eijux：/* “group by”的“松散索引扫描”与“紧凑索引扫描” */

Eijux：/* 范围查询是否使用索引？ */

Eijux：/* “索引失效” */