MySQL 高阶三 （索引性能分析）

执行过程 Explain

 explain select * from student s, course c , student_coure sc where s.id = sc.studentid and c.id = sc.courseid;

EXPLAIN执行计划各字段含义:

【ld】 id相同，执行顺序从上到下; id不同，值越大，越先执行)。
【select_type】表示SELECT的类型，常见的取值有SIMPLE〈简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询)、UNION(UNION中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询）等
【key_len】 用到的索引长度，越短越好。

重点关注：【type】【possible_key】【key】
【type】表示连接类型，性能由好到差的连接类型为null > system > const > eq_ref > ref > range > index > all。
type = null 和 system: 一般不查询业务表，比如 select ‘A’
type = const: 主键或者unquie时，比如，select * from user where id= 1 或者 where idCard = 3423…
type = eq_ref 和 ref: 字段创建了索引 select * from user where name= ‘张三’ name 前提是建立了所以。
type = range ，常见于<、<=、>、>=、between等操作符（explain select * from user where age> 10 and user < 20）,前提 age 字段必须是 unique 或者时主键 对数据建立了数据结构，否则依然能是 ALL 全文扫描。
type = index: 创建了索引，但是也是全字段扫描，说明索引效果不佳。
type = all: 没有创建索引的字段查询都是ALL explain select * from user where name= ‘zhangsan’
【possible_key】 可能用的的索引，如果是null 说明没用索引
【key】实际用到的索引

索引使用规则：

索引规则

1、最左前缀法则：

当使用联合索引时：要求查询时必须包含最左边的列：例如：（下面创建的索引  username 是最左边的列）

EXPLAIN select * from users where username='test' -- 有效

EXPLAIN select * from users where username = 'test' and phone='18061581849'  and age=1 -- 有效

EXPLAIN select * from users where username = 'test' and phone='18061581849'  -- 无效

EXPLAIN select * from users where username='test'  and phone='18061581849'   -- username 有效  phone无效 因为中间缺少 age 导致索引只有执行了部分 可以观察 keylen

EXPLAIN select * from users where username  = 'test' and age >1  and phone='18061581849' -- 部分有效 age使用了范围查询，导致 phone 失效，正确用法，改成 >= 

总结： 联合索引，查询必须包含索引中最左边的列，并且如果跳过中间的列，那么后面的列的索引将会失效。
使用返回查询时，会导致，部分索引失效，正确做法，使用>= 符合代替
下面两张图，的key_len 表名了 生效字段的长度。 username = 195 age = 5 phone = 195 全部生效就是 395

username + age = 200

不要在索引列上使用计算操作。

select * from users where  substring(phone,10,2)='49' -- 使用了计算，导致失效

查询类型要匹配。

EXPLAIN select * from users where phone = 18061581849  -- 无效 本来是字符串 忘记单引号 变成数字类型，导致索引失效。

模糊查询 ，后面模糊有效，前面无效。

EXPLAIN select * from users where id=1 or age = 1  -- id 和 age都必须有索引，否则索引失效

注意：如果 age是联合组件，那么age 必须遵从最左前缀法则。否则也无效。

数据分布影响

当前数据：

EXPLAIN select * from users where phone>='18961511111'  -- 会使用索引，要查询的数据较少时，使用索引效率较高。
EXPLAIN select * from users where phone>='111'  -- 不会使用索引，数据涵盖的范围很大，使用索引效率更低。
--同理  is null 和 is not null 走不走索引取决于查询的结果相对一总数据是 大多数还是少数，少就用索引，反之不用。

索引指定

-- 当表的phone 字段有联合索引和单列索引时，可以指定或者忽略使用该索引
use index(idx_users_phone) -- 建议MySQL使用 idx_users_phone 索引
ignore index(idx_uses_phone) -- 忽略MySQL使用 idx_users_phone 索引
force index(idx_users_phone) -- 强制MySQL使用 idx_users_phone 索引
--使用方式如下：
select * from users use index(idx_users_phone) where phone = '123123123';

为什么不要 select * 因为会触发回表查询，所以如果数据量比较大，查询是可以建立联合索引。增加性能。但要注意，索引太多也会导致插入效率变低。

EXPLAIN select id,username,nickname FROM users WHERE username = 'test' -- using index 表示只用到idx_phone 索引所以效率很高

EXPLAIN select id,username,nickname,age FROM users WHERE username = 'test' --age 用到了回表查询
EXPLAIN select * FROM users WHERE username = 'test'  --用到了回表查询

前缀索引 ，用于txt 大文本索引。

create index idx_users_mark on users(mark(5))  --针对mark字段的前5个字符创建索引，注意如果重复数据太多不具备效果。

EXPLAIN select mark FROM users where mark like "星星%"; -- 仅仅支持后模糊查询。

索引的设计原则：

1、数据量较大（100w条以上），查询较频繁的标建立索引
2、针对 【where】 【order by】【group by】 操作的字段建立索引。
3、尽量选择区分度高的列作为索引。（例如：身份证号 ），重复数据较多的数据，建立索引效果差例如，性别。
4、更具查询的结果列，来创建使用联合索引，避免回表，和遵从最左前缀法则。
5、索引可以提高查询效率，也会影响修改效率。
6、索引列尽量，使用not null约束，可以提升查询效率，因为null在索引中要做特殊处理。