MySQL（二）MySQL的索引
索引介绍
索引是什么
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书的目录，能加快数据库的查询速度。
索引的优劣势
优势：
-
可以提高数据检索的性能，降低数据库的IO成本，类似于书的目录。–检索
-
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。 --排序
- 被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的怕排序要复杂一些。
- 如果按照索引的列进行排序，对应order by语句来说，效率会提高很多。
- where 索引列在存储引擎层处理，索引下推ICP。
- 覆盖索引 select字段 字段是索引。
劣势：
-
索引会占用磁盘空间。
-
索引会提高查询效率，但是会降低更新效率，比如每次对表的增删改操作。
-
MySQL不仅要保存数据，还要保存或者更新对应的索引文件。
索引的分类
单列索引、组合索引、全文索引、空间索引、位图索引（oracle）
单列索引之普通索引
CREATE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD INDEX index_name(column(length));
单列索引值唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column(length));
组合索引
ALTER TABLE article ADD INDEX index_name_time (title(50),time(10));
删除索引
DROP INDEX index_name ON table;
查看索引
SHOW INDEX FROM table_name \G;
索引原理分析
索引的存储结构
- 索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。
- MyISAM和InnoDB存储引擎：只支持B+TREE索引，也就是说默认使用BTREE，不能够更换。
- MEMORY/HEAP存储引擎：支持HASH和BTREE索引。
B树和B+树数据结构实例网站：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
非聚集索引（MyISAM）
索引和数据是分开存储的
主键索引
主键索引是一个B+tree，tree上存的是每一行数据的地址；
辅助索引
辅助索引是一个B+tree，tree上存的是每一行数据的地址；
聚集索引（InnoDB）
主键索引
主键索引是一个B+tree，数据挂在主键索引上；
主键：
-
有主键
-
没有主键
- 找唯一字段当主键
- 自动生成伪列当主键
辅助索引
辅助索引是一个B+tree，主键挂在辅助索引上；
利用组合索引形成覆盖索引
可以利用组合索引完成覆盖索引，就可以不需要检索两次索引树，就可使用辅助索引完成查询。
索引的使用场景
哪些情况需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引 where
- 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
- 查询中排序的字段，应该创建索引，因为B+TREE 有顺序
- 覆盖索引 不需要回表 组合索引
- 统计或者分组字段，应该创建索引
哪些情况不需要创建索引
- 表的记录太少，索引是要有存储开销
- 频发更新索引需要维护
- 查询字段使用频率不高
索引失效
查看执行计划
explain出来的信息有10列，分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
创建三张表来查看sql的执行计划
# --用户表
create table tuser(
id int primary key,
loginname varchar(100),
name varchar(100),
age int,
sex char(1),
dep int,
address varchar(100)
);
# --部门表
create table tdep(
id int primary key,
name varchar(100)
);
# --地址表
create table taddr(
id int primary key,
addr varchar(100)
);
# --创建普通索引
alter table tuser add index idx_dep(dep);
# --创建唯一索引
alter table tuser add unique index idx_loginname(loginname);
# --创建组合索引
alter table tuser add index idx_name_age_sex(name,age,sex);
# --创建全文索引
alter table taddr add fulltext ft_addr(addr);
insert into tuser values (1,'ls','lisi',18,1,1,'xian');
insert into tuser values (2,'zs','zhangsan',19,1,2,'xian');
insert into tuser values (3,'ww','wangwu',20,1,2,'shanghai');
insert into tuser values (4,'ll','liuliu',18,2,1,'beijing');
insert into tuser values (5,'qf','qifeng',22,2,3,'xian');
insert into tuser values (6,'bd','badang',23,1,1,'xian');
insert into tdep values (1,'ls');
insert into taddr values (1,'beijing');
explan 每个字段的含义
- id ：每个查询都会自动分配一个id
表示查询中的顺序，一般会有下面三种情况
1.id相同：执行顺序从上到下
2.id不同：如果是子查询 id好会增大，id号越大，查询优先级越高
3.id相同的、不同的，同时存在
-
select_type：查询类型：主要用于区别 普通查询、联合查询(union、union all)、子查询等复杂查询
-
# SIMPLE ：不包含union、union all 或者不包含子查询的 简单查询，有连接查询时，外层的查询为SIMPLE，且只有一个 explain select * from tuser; # PRIMARY ：有union 或者子查询的语句，最外层的查询 即为PRIMARY explain select (select name from tuser) from tuser; # SUBQUERY ：除了from包含的子查询为其他的都可能是SUBQUERY explain select (select name from tuser) from tuser; # DEPENDENT SUBQUERY ：表示这个查询收到外部查询的影响 explain select id,name,(select name from tdep a where a.id = b.id) from tuser b; # UNION、UNION RESULT ：表示union链接的两个查询，除了第一个（PRIMARY）外后面的都是union explain select * from tuser union select * from tuser; # DEPENDENT UNION ：表示union、union all链接的两个查询，表示这个查询收到外部查询的影响 explain select * from tuser where sex in (select sex from tuser where sex=1 union select sex from tuser where sex=2); # union result ：表示包含union、union all的结果集，他不需要参与查询，所以id为null # derived ：from字句中出现的子查询，也叫做派生表； explain select * from (select * from tuser where id=1) a;
-
-
table：
-
1.显示查询表的名字，如果使用了别名，这里将显示别名 2.如果不涉及对表的操作，这里显示null 3.如果显示为尖括号括起来的就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自与这个查询产生 4.如果是尖括号括起来的<union M,N>，与上面类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集
-
-
type：性能依次从好到差
-
# 除了All之外，其他的类型都可以用到索引，除了index_merge外，其他的type都只用到一个索引 # 优化器会选择最优的一个索引 # 最少要索引使用到range级别。 # sxystem：表中只有一行数据或者空表 explain select * from (select * from tuser where id=1) a; # const(重要)：使用唯一索引或者主键，返回记录一定是1记录的等值where条件的时，通常type是const。其他数据库一般叫做为一索引扫描 explain select * from tuser where id=1;#主键索引 explain select * from tuser where loginname='ls';#唯一索引 # eq_ref：链接字段主键或者唯一索引，此类型通常出现在多表的join查询，表示对前表的每一个结果，都只能在后表中匹配到一行结果，并且查询的比较操作通常是‘=’，查询效率较高 explain select a.id,a.dep from tuser a left join tdep b on a.dep=b.id; # ref(重要)：针对非唯一索引，使用‘=’查询非主键，或者是使用了最左前缀规则索引查询 explain select dep from tuser where dep='1'; explain select sex from tuser where name='lisi' and age ='18'; # fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 explain select * from taddr where match(addr) against('bei'); # ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。 # unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值 # index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。 # range(重要)：说因范围扫描，常见于使用>、<、is null、between、in、like等运算符的查询中 # -like：‘a%’使用索引 ‘%a’不使用索引 explain select * from tuser where name like 'l%'; # 其他 explain select * from tuser where id < 2; explain select * from tuser where id between 1 and 3; explain select * from tuser where id in (1,2,3); # index_merge # 表示查询使用了两个以上的索引，最后取交集或者并集，常见and，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取两个索引，性能可能大部分时间都不如range # index(重要): 条件是出现在索引树种的节点的。可能没有完全匹配索引 # 索引全表扫描：把索引从头到尾扫一遍，常见于使用索引列就可以处理，不需要读取数据文件的查询，可以使用索引排序或者分组的查询。 # 索引覆盖 explain select loginname from tuser; explain select age from tuser order by age DESC;# 索引排序 explain select sex from tuser group by sex;# 索引分组 # All(重要)：这个就是全表扫描数据文件，然后在server层进行过滤返回符合要求的记录 explain select * from tuser;
-
-
possible_keys：此次查询中可能选用的索引，1个或者多个
-
key：查询中真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个
-
key_len：查看组合索引的使用情况
-
1.用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定使用到所有的列，具体使用到多少个列的索引这里都会算进去，没有使用到的列这里就不计算进去 2.留意一下这个值，算一下你的多列索引总长度，就知道有没有使用到所有的列了 3.另外，ken_len只计算where条件用到的索引长度，而排序和分组就算用到了索引列，也不会计算进去
-
-
ref：
1.如果使用的常数等值查询，这里会显示const 2.如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段 3.如果是条件使用了表达式、函数、条件发生内部隐式转换，这里会显示func -
rows：这里是执行计划中估算的扫描行数，不是精确(innoDB不是精确值，MyISAM是精确值，主要原因是innoDB中使用了MVCC并发机制)
-
extra(重要)：这个列包含不适合在其他列中显示但十分重要的额外信息系，这个列显示的信息非常多，有几十种，常用的有
-
# no table use # 1.不带from字句的查询，或者from dual # 2.使用not in()字句形式子查询或者not exist运算符的连接查询，这种叫做反链接 # 3.即，一般连接查询是先查内表，再查外表，反链接就是先查询外表，在查询内表 # using filesort(重要) # 1.排序是无法使用到索引，就会出现这个。常见于order by和group by语句中 # 2.说明Mysql会使用一个外部的索引排序，而不是按照索引顺序进行读取 # 3.Mysql中无法利用索引完成的排序操作称为‘文件排序’ explain select * from tuser order by address; # using index(重要) # 查询是不需要回表查询，直接通过索引就可以获取到查询的数据 # 1.表示相应的select查询中使用到了覆盖索引(covering index)，避免访问表的数据行，效率不错 # 2.如果同时出现using where，说明索引被用来执行查找索引键值 # 3.如果没有同时出现using where，表明索引用来读取数据而非执行查找动作 explain select age,name from tuser; # using temporary # 1.表示使用了临时表存储中间结果 # 2.MySql在对查询结果order by和group by时使用了临时表 # 3.临时表可以时是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table,used_tmp_disk_tbale才能看出来 # 4.distinct，在select部分使用了distinct关键字 explain select distinct a.id from tuser a left join tdep t on a.dep=t.id; # using where(重要) # 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤 explain select * from tuser where address='beijing'; # using index condition # 查询条件分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤。 # 5.6之后支持了IPC特性，可以吧检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量 explain select * from tuser where name ='lisi';
-
索引失效分析
# 索引全值匹配：条件与索引一一对应
explain select * from tuser where name='lisi' and age = '18' and sex='1';
# 最左前缀：如果索引了多个列，要遵守最佳左前缀法则。指的是查从索引的最左前列开始，并且不跳过索引中的列
# 错误示范
# 带头索引死
explain select * from tuser where sex='1';
# 中间索引断 (带头索引生效，其他索引失效)
explain select * from tuser where name='lisi' and sex='1';
# 正确示范
explain select *from tuser where name='lisi' and age = '18' and sex = '1';
# 不要再索引上做计算
explain select * from tuser where loginname='zs';
# 不要进行这些操作：计算、函数、自动/手动 类型转换，不然会导致索引失效而转为全表扫描
explain select * from tuser where left(loginname,1) = 'zs';
# 范围条件右边的列失效
# 出现范围条件（betwee、<、>、in等），索引不能继续使用右边的值
explain select * from tuser where name = 'lisi' and age > 18 and sex ='1';
# 尽量使用覆盖索引
# 尽量使用覆盖索引（只查询索引的列），也就是查询列和索引列一致，减少select *
explain select * from tuser;
explain select name,loginname from tuser;
# 覆盖索引
explain select loginname from tuser;
explain select name from tuser;
explain select name,age,sex from tuser;
# 索引字段上不要使用不等号
# 索引字段上使用（！= 或者 <>）判断时，会导致索引失效而转向全表扫描
# 注：主键索引会使用范围索引，辅助索引会失效
explain select * from tuser where name != 'lisi';
explain select * from tuser where id != '1';
# 主键索引字段上不可以判断null
# 主键字段上不可以使用null
# 索引字段上使用is null判断是可以使用索引
explain select * from tuser where name is null;
# is not null 不使用索引
explain select * from tuser where name is not null ;
# 索引字段使用like不以通配符开头
# 索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效，转向全表扫描
explain select * from tuser where name like '%l';
explain select * from tuser where name like 'l%';
# 使用覆盖索引可以解决这个 失效问题
explain select name,age,sex from tuser where name like '%l%';
# 索引字段字符串要加单引号(隐式转换问题)
# 索引字段是字符串的话，查询时不加单引号会导致全表扫描
explain select * from tuser where loginname=1111;
explain select * from tuser where loginname='1111';
# 索引字段不要使用or
explain select * from tuser where name='lisi' or age = 18;