Mysql join不使用索引

【问题场景】

有个30多行的大SQL执行效率特别慢，问题集中在一个子查询上，开始没有建索引，可是发现索引都创建了，还是不走索引

ql语句描述：有三张表需要关联查询，关联关系如下

A表

B表关联 A.col = B.id

C表关联 B.col = C.id

问题出在 B表关联 A.col = B.id，为啥？执行计划就是不走id主键，C表关联 B.col = C.id都可以正常走

【解决思路】

1、尝试单表查询，验证索引是否正常试了一下单表查询B是可以走主键索引，正常，排出索引问题

2、尝试优化SQL 修改了一下SQL，将left join 分别改为inner join，join和子查询，几种方式都不能走索引，排出优化可能

https://blog.csdn.net/everda/article/details/77476716看到这篇文章，修改了表的编码和字段的编码方式，终于可以正常走索引了

问题总结

怎么使用执行计划？

expain + SQL 语句
执行计划包含信息

select_type包括范围：

simple. primary，subquery, derived, union, union result

查询类型主要是用于区别普通查询，联合查询，子查询等复杂的查询

simple，简单的select 语句，查询中不包含自查询或者 unionprimary,
-查询若包含任何复杂的子部分，最外层查询则被标记为primary subquery，
在 select 或 where 列表中包含子查询derived，在 from 列表中包含自查询被标记为 derived （衍生）MySQL 会递归执行这些自查询，把结果放在临时表中。
union，若第二个 select 出现在 union 之后，则被标记为 union.
若 union 包含在 from 子句子查询中，外层 select 将别标记为 derived
union result, 从 union 表中获取结果的 select

table

这行数据是关于那张表的

type类型：all , index , range, ref, eq_ref, const, system ,null

type 显示的是关联类型或者访问类型，是较为重要的一个指标，结果从好到坏依次是： system > count > eq_ref > range > index > all

sytem > const > eq_ref > ref > fulltext > ref_or_null > index_merge >> unique_subquery > index_subquery > range > index > ALL

备注：一般来说，得以保证查询至少达到 rang 级别，最好能达到 ref。

Null

explain select min(id) from t1;

mysql 能够在优化阶段分析查询语句，在执行阶段不用再访问表或者索引。例如：在索引列中选取最小值，单独查询索引来完成，不需要在执行时访问表

system表只有一行记录（等于系统表），这是 const 类型的特列，平时不会出现，这个也可以忽略不计

const

explain select * from (select * from t1 where id =1) d1;

表示通过索引一次就找到了，

const 用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快如将主键置于where 列表中， MySQL 就能将该查询转换为一个常量。

eq_ref

explain select * from t1, t2 where t1.id = t2.id;

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描. ##### ref

tb_emp ddl

CREATE TABLE

tb_emp

(

id

int(11) NOT NULL AUTO_INCREMENT,

name

varchar(30) DEFAULT NULL,

dept_id

int(11) DEFAULT NULL,

PRIMARY KEY (

id

) ;

员工表添加年龄列

alter table tb_emp add column

age

int(11) default null after

name

;

添加复合索引

create index idx_emp_name_age on tb_emp(

name

age

);

explain select * from tb_emp where

name

= ‘z3’;

非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到多个符合个条件的行，所以它应该属于查找和扫描的混合##### range

explain select * from t1 where id between 1 and 3;

explain select * from t1 where id in (1, 2, 3);

只检索给定范围内的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是你在 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描

index

explain select id from t1;

Full Index Scan ， index 和 ALL 的区别，index 类型只遍历索引树，这通常比 ALL 快，因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的，而 all 是从硬盘中读取的）

all

explain select * from t1;

Full Table Scan 将遍历全表找到匹配的行

备注：一般来说，得以保证查询至少达到 rang 级别，最好能达到 ref。

possible_keys显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用.

key实际使用的索引，如果为NULL，则没有使用索引查询中若使用了覆盖索引，则该索引仅出现在KEY列表中explain select col1, col2 from t1;

create index idx_col1_col2 on t1(col1, col2);

explain select col1, col2 from t1;

key_len

desc t1;

explain select * from t1 where col1 = ‘ab’;

explain select * from t1 where col1 = ‘ab’ and col2 = ‘bc’;

表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好 key_len 显示的只为索引字段的最大可能长度，** 并非实际使用长度**。即 key_len 是根据表定义计算而得，不是通过表内检索出的。

总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。

key_len 计算规则

字符串
char(n) n 字节长度
varchar(n) 如果是utf-8，则长度 3n + 2 字节，加的两个字节用来存储字符串长度数值类型
tinyint 1 字节
smallint 2 字节
int 4 字节
bigint 8 字节
时间类型date 3 字节
tiemstamp 4 字节
datetime 8 字节
如果字段允许为 null , 需要一个字节记录是否为 null

索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半段的字符串提取出来做索引。

Rows根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数

filtered

Extra包含不适合其他列中显示但十分重要的额外信息 id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra1.

Using filesort文件排序

2. Using temporaryALTER TABLE

t1

ADD COLUMN

col1

VARCHAR(30) NULL DEFAULT NULL AFTER

other_column

;

ALTER TABLE

t1

ADD COLUMN

col2

VARCHAR(30) NULL DEFAULT NULL AFTER

col1

;

explain select col2 from t1 where col1 in (‘ab’, ‘ac’, ‘as’) group by col2 \G;

explain select col2 from t1 where col1 in (‘ab’, ‘ac’, ‘as’)

group by col1, col2, col3 \G;

使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by 。

Using index

explain select col2 from t1 where col1=100;

explain select col1, col2 from t1;

表示相应的 select 操作使用了覆盖索引 (Covering Index), 避免了访问表的数据行，效率不错～如果同时出现 using where , 表示索引被用来执行索引键值的查找；如果没有同时出现 using where , 表明索引引用来读取数据而非执行查找动作。

覆盖索引（Covering Index）覆盖索引（Covering Index）, 一说为索引覆盖理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖理解方式二：索引是高效找到的行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。注意：1. 如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select * ；2. 因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。

Using index conditionexplain select * from tb_emp where name
> ‘z3’;

查询的列不完全被覆盖索引覆盖，where 条件中是一个前导的范围
Using Where表明使用了 where 过滤
using join buffer使用了链接缓存7. impossible whereexplain select * from t1 where 1=2;

where 子句的值总是 false , 不能用来获取任何元组
select tbale optimized away在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*）操作不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct优化 distinct 操作在找到第一匹配的元祖后立即停止找相同值的动作。举个例子例子描述：explain select d1.name, (select id from t3) d2 from

(select id, name from t1 where other_column = ‘’) d1

union

(select name, id from t2);

Mysql join不使用索引

案例解析：第一行（执行顺序4）：id 列为1 ，表示 union 的第一个 select , select_type 的 primary 表表示该查询为外层查询， table列被标记为 , 表示查询结果来自一个衍生表，其中 derived3 中的 3 代表查询衍生自第三个 select 查询，即 id 为 3 的 select [select d1.name … ]第二行（执行顺序为2）：id 为 3 ，是整个查询中第三个 select 的一部分，因查询包含在from 中，所以为derived 。【select id, name from where other_column = ‘’】第三行（执行顺序为3）：select 列表中的子查询 select_type 为 subquery , 为整个查询中的第二个 select . [select id from t3]第四行（执行顺序为1）：select_type 为 union ，说明第四个 select 是 unin 里的第二个 select , 最先执行【select name ,id from t2】第五行（执行顺序为5）：代表 union 的临时表中读取行的阶段， table 列的 <union , 1, 4> 表示用第一个和第四个 select 结果进行union 操作。【两个结果 union 操作】

join查询不走索引

如果where条件中有过滤条件会首先走where条件，但是where条件中的字段导致全表扫描，所以就没有用到指定的索引

因此，谨记，大表查询的时候，where 的条件千万记得加上索引！！！！

如果两个字段或者两张表的编码不同，也会导致索引失效。

未提供筛选条件

有两张表，一张叫teacherInfo,一张叫departmenInfo两张表有一个相同的字段，叫depnum，两张表都以这个字段建立了索引，可是我执行以下语句时，departmenInfo要进行全表扫描。

SELECT TA.* FROM teacherInfo TA,departmenInfo TB WHERE TA.depnum=TB.depnum1

如果把两个表的位置改改如下，又变成teacherInfo要全表扫描：

SELECT TA.* FROM departmenInfo TB，teacherInfo TA WHERE TA.depnum=TB.depnum1

其实就是放在后面的表总要进行全表扫描。一直想不明白明明两张表都有索引，为什么总有一张表是要进行全表扫描呢？

上网找原因，原来是上面的语句没有筛选条件，TA.depnum=TB.depnum这个条件只是做了表的关联查询，并未提供筛选条件，如果再加一个条件”and depnum=’001’”等筛选条件，索引立即用上了。

索引查询失效的几个情况：

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。

2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效

3、组合索引，不是使用第一列索引，索引失效。

4、数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描

所以，建议不要在索引列上做任何操作。

5、在索引列上使用 IS NULL 或 IS NOT NULL操作（有时失效）。

6、最佳左前缀法则

如果一张表的索引有多个，要遵守最佳左前缀法则，即查询从索引的最左前列开始并且不跳过索