MySQL全面瓦解23:MySQL索引实现和使用

发布于 2022-7-22 17:01
浏览
0收藏

MySQL索引实现
上一篇我们详细了解了B+树的实现原理(传送门)。我们知道,MySQL内部索引是由不同的引擎实现的,主要包含InnoDB和MyISAM这两种,并且这两种引擎中的索引都是使用b+树的结构来存储的。

InnoDB引擎中的索引
Innodb中有2种索引:主键索引(也叫聚集索引)、辅助索引(也叫非聚集索引)。

主键索引:每个表只有一个主键索引,b+树结构,叶子节点存储主键的值以及对应整条记录的数据,非叶子节点不存储记录的数据,只存储主键的值。

当表中未指定主键时,MySQL内部会自动给每条记录添加一个隐藏的rowid字段(默认4个字节)作为主键,用rowid构建聚集索引。聚集索引在MySQL中即主键索引。 

辅助索引:每个表可以有多个辅助索引,b+树结构,非聚集索引叶子节点存储字段(索引字段)的值以及对应记录主键的值,其他节点只存储字段的值(索引字段),这就是与聚集索引不同的地方。每个表可以有多个非聚集索引。

MySQL中非聚集索引进一步区分:MySQL全面瓦解23:MySQL索引实现和使用-开源基础软件社区

MyISAM引擎中的索引
也是B+树结构,MyISM使用的是非聚簇索引,如下图,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,

辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。

由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

 

下图更形象说明这两种索引的区别,这边假设了一个存储4行数据的表。Id为主键索引,Name作为辅助索引,图中清晰的体现了聚簇索引和非聚簇索引的差异。

MySQL全面瓦解23:MySQL索引实现和使用-开源基础软件社区

 

我们来分析一下图中数据检索过程:

InnoDB数据检索过程
上面的表中有2个索引:id作为主键索引,name作为辅助索引。

如果需要查询id=14的数据,只需要在左边的主键索引中检索就可以了。

如果需要搜索name='Ellison'的数据,需要2步:

1、先在辅助索引中检索到name='Ellison'的数据,获取id为14

2、再到主键索引中检索id为14的记录

辅助索引这个查询过程在mysql中叫做回表,相对于主键索引多了第二步操作。 

MyISAM数据检索过程
1、在索引中找到对应的关键字,获取关键字对应的记录的地址

2、通过记录的地址查找到对应的数据记录

对比发现:innodb中最好是采用主键查询,这样只需要一次索引,如果使用辅助索引检索,涉及多一步的回表操作,比主键查询要耗时一些。

而innodb中辅助索引区别于myisam的是:

表中的数据发生变更的时候,会影响其他记录地址的变化,如果辅助索引中记录数据的地址,此时会受影响,而主键的值一般是很少更新的,当页中的记录发生地址变更的时候,对辅助索引是没有影响的。 

索引管理和使用
数据准备
请参考第21篇(MySQL全面瓦解21(番外):一次深夜优化亿级数据分页的奇妙经历)中模拟的千万数据,我们以这个数据为测试数据。 

创建索引 
create 方式:

1 create [unique] index index_name on t_name(c_name[(length)]); 


alter表 方式:

1 alter t_name add [unique] index index_name on (cname[(length)]);


这边需注意的是:

index_name 代表索引名称、t_name代表 表名称、c_name代表字段名称。

[] 中括号的内容是可以省略的,也就是说 unique 和 length 可以不写。如果加上了unique,表示创建唯一索引。

如果字段是char、varchar类型,length可以小于字段实际长度,如果是blog、text等长文本类型,必须指定length。

如果tname后面只写一个字段,就是单列索引,如果需要写多个字段,可以使用逗号隔开,这种叫做复合索引。

删除索引
1 drop index index_name on t_name;
查看索引
1 show index from t_name; 
索引修改
即先删除索引,再重建索引:drop +create。

示例
emp表中有500W数据 我们用emp来做测试

1 mysql> select count(*) from emp;
2 +----------+
3 | count(*) |
4 +----------+
5 |  5000000 |
6 +----------+
7 1 row in set 


查看和创建索引
记得我们之前在emp表上做过索引,所以先看一下这个表目前所有的索引

可以看到,目前主键字段id和depno字段上都有建立索引

 1 mysql> desc emp;
 2 +----------+-----------------------+------+-----+---------+----------------+
 3 | Field    | Type                  | Null | Key | Default | Extra          |
 4 +----------+-----------------------+------+-----+---------+----------------+
 5 | id       | int(10) unsigned      | NO   | PRI | NULL    | auto_increment |
 6 | empno    | mediumint(8) unsigned | NO   |     | 0       |                |
 7 | empname  | varchar(20)           | NO   |     |         |                |
 8 | job      | varchar(9)            | NO   |     |         |                |
 9 | mgr      | mediumint(8) unsigned | NO   |     | 0       |                |
10 | hiredate | datetime              | NO   |     | NULL    |                |
11 | sal      | decimal(7,2)          | NO   |     | NULL    |                |
12 | comn     | decimal(7,2)          | NO   |     | NULL    |                |
13 | depno    | mediumint(8) unsigned | NO   | MUL | 0       |                |
14 +----------+-----------------------+------+-----+---------+----------------+
15 9 rows in set
16 
17 mysql> show index from emp;
18 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
19 | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
20 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
21 | emp   |          0 | PRIMARY       |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
22 | emp   |          1 | idx_emp_id    |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
23 | emp   |          1 | idx_emp_depno |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
24 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


25 3 rows in set
 我们在没有做索引的字段上做一下查询看看,在500W数据中查询一个名叫LsHfFJA的员工,消耗 2.239S  

MySQL全面瓦解23:MySQL索引实现和使用-开源基础软件社区

再看看他的执行过程,扫描了4952492 条数据才找到该行数据:

1 mysql> explain select * from emp where empname='LsHfFJA';
2 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
3 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
4 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
5 |  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL | 4952492 | Using where |
6 +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
7 1 row in set


 

我们在empname这个字段上建立索引

 1 mysql> create index idx_emp_empname on emp(empname); 
 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> show index from emp;
 6 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 7 | Table | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 8 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 9 | emp   |          0 | PRIMARY         |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
10 | emp   |          1 | idx_emp_id      |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
11 | emp   |          1 | idx_emp_depno   |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
12 | emp   |          1 | idx_emp_empname |            1 | empname     | A         |     1650830 | NULL     | NULL   |      | BTREE      |         |               |
13 +-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
14 4 rows in set 


 再看一下这个执行效率,就会发现有质的飞跃:0.001S,就是这么神奇,学过之前那篇的B+ Tree就知道,它不用从头开始扫表核对,而是很小次数的io读取

  MySQL全面瓦解23:MySQL索引实现和使用-开源基础软件社区

再看看他的执行过程,一次定位到该条数据:

1 mysql> explain select * from emp where empname='LsHfFJA';
2 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
3 | id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra                 |
4 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
5 |  1 | SIMPLE      | emp   | ref  | idx_emp_empname | idx_emp_empname | 22      | const |    1 | Using index condition |
6 +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-----------------------+
7 1 row in set


 

设置合适的索引长度
根据我们之前的了解,每个磁盘块(disk)存储的内容是有限的,如果一个页中可以存储的索引记录越多,那么查询效率就会提高,所以我们可以指定索引的字段长度。

但并不是越短越好,要保证字符类型字段查询有足够高的区分度,如果只设置了一个长度,反而导致查询的相似匹配度不高。

长度的原则是要恰到好处,太长索引文件就会变大,因此要在区分度和长度上做一个平衡。

 

如果在我们搜索的内容中,最后的内容是一致的或者高度一致的,那我们就可以省略,比如在用户的email字段上做索引,几乎前10个字符是不一样的,结尾限定在 @****,那么通过前面10个字符就可以定位一个email地址了。

我们在该字段创建索引的时候就可以指定长度为10,这样相对于整个email字段更短些,查询效果确却基本一样,这样一个页中也可以存储更多的索引记录。

 

像我们上面的那个 empname 字段,基本都是6位数的,只是小部分是超过6位数,而且后缀基本一致,所以6位数之后的区分度差不多。

有一个判断 高区分度以及合适长度索引 的通用算法,如下:

1 select count(distinct left(`c_name`,calcul_len))/count(*) from t_name;


 下面是对 empname 做的分析,匹配度越高搜索效率越高:

 1 mysql> select count(distinct left(`empname`,3))/count(*) from emp;
 2 +--------------------------------------------+
 3 | count(distinct left(`empname`,3))/count(*) |
 4 +--------------------------------------------+
 5 | 0.0012                                     |
 6 +--------------------------------------------+
 7 1 row in set
 8 
 9 mysql> select count(distinct left(`empname`,4))/count(*) from emp;
10 +--------------------------------------------+
11 | count(distinct left(`empname`,4))/count(*) |
12 +--------------------------------------------+
13 | 0.0076                                     |
14 +--------------------------------------------+
15 1 row in set
16 
17 mysql> select count(distinct left(`empname`,6))/count(*) from emp;
18 +--------------------------------------------+
19 | count(distinct left(`empname`,6))/count(*) |
20 +--------------------------------------------+
21 | 0.1713                                     |
22 +--------------------------------------------+
23 1 row in set
24 
25 mysql> select count(distinct left(`empname`,7))/count(*) from emp;
26 +--------------------------------------------+
27 | count(distinct left(`empname`,7))/count(*) |
28 +--------------------------------------------+
29 | 0.1713                                     |
30 +--------------------------------------------+
31 1 row in set

 

删除索引

 1 mysql> drop index idx_emp_empname on emp;

 2 Query OK, 0 rows affected
 3 Records: 0  Duplicates: 0  Warnings: 0
 4 
 5 mysql> show index from emp;
 6 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 7 | Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 8 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 9 | emp   |          0 | PRIMARY       |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
10 | emp   |          1 | idx_emp_id    |            1 | id          | A         |     4952492 | NULL     | NULL   |      | BTREE      |         |               |
11 | emp   |          1 | idx_emp_depno |            1 | depno       | A         |          18 | NULL     | NULL   |      | BTREE      |         |               |
12 +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
13 3 rows in set 

 

执行完删除命令再查看,发现索引已经没了

小结
本文只是理解索引的基本用法,后面会认真讲一讲索引的性能分析和优化策略。

总之,理想的索引应该符合以下特征:

1、相对低频的写操作,以及高频的查询的表和字段上建立索引

2、字段区分度高

3、长度小(合适的长度,不是越小越好)

4、尽量能够覆盖常用字段

已于2022-7-22 17:01:43修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐