繁依Fanyi0

这个屌丝很懒，什么也没留下！

热门标签

MySQL学习（二）MySQL的索引_学习mysql 优势

作者：繁依Fanyi0 | 2024-07-16 17:29:26

踩

学习mysql 优势

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));
1
2

单列索引值唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length));
ALTER TABLE table_name ADD UNIQUE INDEX index_name(column(length));
1
2

组合索引

ALTER TABLE article ADD INDEX index_name_time (title(50),time(10));
1

删除索引

DROP INDEX index_name ON table;
1

查看索引

SHOW INDEX FROM table_name \G;
1

索引原理分析

索引的存储结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。
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
1

创建三张表来查看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');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

explan 每个字段的含义

id ：每个查询都会自动分配一个id

       表示查询中的顺序，一般会有下面三种情况
             1.id相同：执行顺序从上到下
             2.id不同：如果是子查询 id好会增大，id号越大，查询优先级越高
             3.id相同的、不同的，同时存在
1
2
3
4

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

table：

1.显示查询表的名字，如果使用了别名，这里将显示别名
2.如果不涉及对表的操作，这里显示null
3.如果显示为尖括号括起来的就表示这是个临时表，后边的N就是执行计划中的id，表示结果来自与这个查询产生
4.如果是尖括号括起来的<union M,N>，与上面类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集
1
2
3
4

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

possible_keys：此次查询中可能选用的索引，1个或者多个
key：查询中真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个

key_len：查看组合索引的使用情况

1.用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定使用到所有的列，具体使用到多少个列的索引这里都会算进去，没有使用到的列这里就不计算进去
2.留意一下这个值，算一下你的多列索引总长度，就知道有没有使用到所有的列了
3.另外，ken_len只计算where条件用到的索引长度，而排序和分组就算用到了索引列，也不会计算进去
1
2
3

ref：

1.如果使用的常数等值查询，这里会显示const
2.如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
3.如果是条件使用了表达式、函数、条件发生内部隐式转换，这里会显示func
1
2
3

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';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

索引失效分析

# 索引全值匹配：条件与索引一一对应
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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/繁依Fanyi0/article/detail/835477?site

MySQL学习（二）MySQL的索引_学习mysql 优势

MySQL学习（二）MySQL的索引

索引介绍

索引是什么

索引的优劣势

优势：

劣势：

索引的分类

索引原理分析

索引的存储结构

非聚集索引（MyISAM）

聚集索引（InnoDB）

利用组合索引形成覆盖索引

索引的使用场景

哪些情况需要创建索引

哪些情况不需要创建索引

索引失效

查看执行计划

索引失效分析