欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

带你了解mysql的索引

程序员文章站 2022-06-05 19:41:41
...

3.1.1匹配最左前缀原理

最左匹配就是最左边优先;创建组合索引时,要根据业务要求,where子句中使用最频繁的一列放在最左边。 组合索引的查找是先根据第一个字段查,然后再根据第二个字段查,或者只根据第一个字段查,但是不能跳过第一个字段,直接从第二个字段开始查,这就是所谓的最左前缀原理。

例:在字段 id,cert_num,test_id上创建一个联合索引,索引顺序会首先按照id字段排序,然后再按照cert_num字段排序,最后是test_id字段。

/==============================================================/ /*

Table: student / /==============================================================*/

create table student_info ( name varchar(10), id varchar(10), cert_num varchar(10), test_id varchar(10), subject varchar(10), grade varchar(10) );

alter table student_info comment '学生表';

/==============================================================/ /*

Index: Index / /==============================================================*/

create index Index on student_info ( id, cert_num, test_id );

下面的SQL语句是按照((id),(id, cert_num),(id, cert_num, test_id))的顺序用到索引。 select * from table where id= ?; select * from table where id= ? and cert_num= ?; select * from table where id= ? and cert_num= ? and test_id= ?;

带你了解mysql的索引

  • 如果不是按照索引的最左列开始查找,则无法使用索引;

    例如:

    下面的SQL语句未使用到索引,因未遵循最左匹配原理。

    explain select * from student_info where cert_num = '1000222001' and test_id = '10024'

  • 带你了解mysql的索引

  • 不能跳过索引中的列;

    例如:

    下面的SQL语句只用到一个索引id。 explain select * from student_info where id = '10001' and test_id = '10024';

  • 带你了解mysql的索引

  • 如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找;

    例如:

    explain select * from student_info where id = '10001' and cert_num like '0000%' and test_id = '10024';

    这个查询只能使用索引的前两列;

以MySQL为例,下面的SQL语句也能使用到索引,查询优化器会重新编译,不建议这样使用。 select * from tb_name where b = 1 and c = 2 and a = 0;

3.1.2 索引类型(存储结构)

  • 索引是应用在SQL查询语句的条件,一般作为WHERE子句的条件。

  • 主键索引:数据列不允许重复,不允许为NULL,一个表只能有一个主键。

  • 普通索引:基本的索引类型,没有唯一性限制,允许为NULL值。

  • 唯一索引:索引列的值必须唯一,但允许有空值;如果是组合索引,则列值的组合必须唯一。

  • 前缀索引:用列的前缀代替整个列作为索引key,比如:like‘xxx%’。

  • Hash索引:采用一定的哈希算法,把键值换算成新的哈希值,只需一次哈希算法即可定位到相应的位置,查询速度非常快。

3.1.2.1 B-Tree索引

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始搜索。

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引,对于频繁访问的表,innodb会透明建立自适应hash索引,即在B树索引基础上建立hash索引,可以显著提高查找效率,对于客户端是透明的,不可控制的,隐式的。

3.1.2.2 哈希索引

哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码,哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针。

在mysql中,只有memory引擎显示支持哈希索引。也是memory引擎表的默认索引类型,同时memory引擎也支持B-tree索引。memory引擎是支持非唯一哈希索引,如果多列的哈希索引值相同,索引会以链表的方式存放在多个记录指针到同一哈希条目中。

哈希索引的查询:

select * from student_info where cert_num = '1000222001'

mysql先计算‘1000222001’的哈希值,并使用该值寻找对应的记录指针;然后找到指针所对应的行,最后比较该行的值是不是’1000222001‘,以确保就是要查找的行;

因为索引自身只需要存储对应的哈希值,所以索引的结构十分紧凑,这也是哈希索引速度快的原因。但是哈希索引也有他的限制:

  • 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行;

  • 哈希索引数据并不是按照索引值顺序来存储的,所以也就无法用于排序;

  • 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来计算哈希值;(例如建立联合哈希索引,查询其中一列是无法使用索引的)

  • 哈希索引只支持等值比较查询例如:=、in()、<=>,不支持任何范围查询。

  • 访问哈希索引的数据非常快,除非有很多哈希冲突(不同索引列值却有相同的哈希值)。当哈希值冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行比较,直到找到所有符合条件的行;(冲突多的话,维护操作的代价也高)

3.1.2.3 空间数据索引(R-Tree)

MyISAM表支持空间索引,无需前缀查询,它会从所有维度来索引数据。

3.1.2.4全文索引

全文索引是一种特殊类型的索引,他查找的是文本中的关键词,而不是直接比较索引中的值。

3.1.2.5 其他索引类别

3.1.2.5.1 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储方式。

innodb中主键所建立的是聚簇索引,而唯一索引、普通索引、前缀索引等都是二级索引。

innodb通过主键聚集数据,被索引的列就是主键列,所以说主键就是聚簇索引。

如果没有定义主键,innodb会选择一个唯一的非空索引代替。如果没有这样的索引,innodb会隐式定义一个主键来作为聚簇索引(row_id)。

innodb中二级索引(非聚簇索引)叶子节点中保存的不是指向物理位置的指针,而是行的主键值;这就意味着通过二级索引查找行,存储引擎需要找到二级索引的叶子节点,获得对应的主键值,然后根据这个值去聚簇索引中查找到对应的行(也就是回表,覆盖索引可以避免这样的操作前提是只查询索引列)。

3.1.2.5.2 前缀索引

前缀索引就是取字段的前几个字节作为索引,前缀索引是一种能是索引更小、更快的有效办法,但是前缀索引无法做group by和order by,也无法使用前缀索引做覆盖扫描;

eg. ALTER TABLE TABLENAME ADD KEY (column(前缀长度));

3.1.2.5.3 覆盖索引

如果一个索引包含所有需要查询的字段的值,就是覆盖索引;

如果二级索引可以覆盖查询,就可以避免对主键索引的二次查询;

覆盖索引必须要存储索引列的值,而哈希索引、空间索引和全文索引等都不存储索引的值,所以mysql只能使用B-Tree索引做覆盖索引;

3.1.3 key和index的区别

①key 是数据库的物理结构,它包含两层意义,一是约束(偏重于约束和规范数据库的结构完整性),二是索引(辅助查询用的)。包括primary key, unique key, foreign key 等。

  • primary key 有两个作用,一是约束作用(constraint),用来规范一个存储主键和唯一性,但同时也在此key上建立了一个index;

  • unique key 也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个index;

  • foreign key也有两个作用,一是约束作用(constraint),规范数据的引用完整性,但同时也在这个key上建立了一个index;

可见,mysql的key是同时具有constraint和index的意义,这点和其他数据库表现的可能有区别。(至少在Oracle上建立外键,不会自动建立index),因此创建key也有如下几种方式:

  1. 在字段级以key方式建立, 如 create table t (id int not null primary key);

  2. 在表级以constraint方式建立,如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));

  3. 在表级以key方式建立,如create table t(id int, primary key (id));

  4. 其它key创建类似,但不管那种方式,既建立了constraint,又建立了index,只不过index使用的就是这个constraint或key。

②index是数据库的物理结构,它只是辅助查询的,它创建时会在另外的表空间(mysql中的innodb表空间)以一个类似目录的结构存储。索引要分类的话,分为前缀索引、全文本索引等;因此,索引只是索引,它不会去约束索引的字段的行为(那是key要做的事情)。 如,create table t(id int, index inx_tx_id (id));

③最后的释疑:

  • 我们说索引分类,分为主键索引、唯一索引、普通索引(这才是纯粹的index)等,也是基于是不是把index看作了key。比如 create table t(id int, unique index inx_tx_id (id)); --index当作了key使用

  • 最重要的也就是,不管如何描述,理解index是纯粹的index,还是被当作key,当作key时则会有两种意义或起两种作用。

上面的一个例子:

create index Index on student_info ( id, cert_num, test_id );

然后查这个建表语句:

mysql> show create table student_info;

| Table | Create Table | student_info | CREATE TABLE student_info ( name varchar(10) DEFAULT NULL, id varchar(10) DEFAULT NULL, cert_num varchar(10) DEFAULT NULL, test_id varchar(10) DEFAULT NULL, subject varchar(10) DEFAULT NULL, grade varchar(10) DEFAULT NULL, KEY Index (id,cert_num,test_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表' |

由此可见普通的索引和普通的key其实是一样的;

3.1.3.1 MySQL Key值(PRI, UNI, MUL)的含义:

PRI主键约束;

UNI唯一约束;

MUL可以重复。

注:若是普通的key或者普通的index(实际上,普通的key与普通的index同义)。

当我们在desc 表名; 的时候,有一个Key值,表示该列是否含有索引 假设表结构如下所示

mysql> desc student_info;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| name | varchar(10) | YES | | NULL | |

| id | varchar(10) | YES | MUL | NULL | |

| cert_num | varchar(10) | YES | | NULL | |

| test_id | varchar(10) | YES | | NULL | |

| subject | varchar(10) | YES | | NULL | |

| grade | varchar(10) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

6 rows in set (0.01 sec)

我们看到Key那一栏,可能会有4种值,即'啥也没有','PRI','UNI','MUL':

  1. 如果Key是空的, 那么该列值的可以重复,表示该列没有索引, 或者是一个非唯一的复合索引的前导列;

  2. 如果Key是PRI, 那么该列是主键的组成部;

  3. 如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列),且不能含有空值(NULL);

  4. 如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL;

注:

  1. 如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI(如果是PRI,则一定是UNI);那么"desc 表名"; 的时候,显示的Key值按照优先级来显示 PRI->UNI->MUL;那么此时,显示PRI。

  2. 如果某列不能含有空值,同时该表没有主键,则一个唯一性索引列可以显示为PRI,

  3. 如果多列构成了一个唯一性复合索引,那么一个唯一性索引列可以显示为MUL。(因为虽然索引的多列组合是唯一的,比如ID+NAME是唯一的,但是每一个单独的列依然可以有重复的值,因为只要ID+NAME是唯一的即可)

3.1.4 索引的优点

索引可以让服务器快速地定位到表的指定位置。

①索引大大减少了服务器需要扫描的数据量

②索引可以帮助服务器避免排序和临时表

③索引可以将随机I/O变为顺序I/O

3.2 高性能索引

3.2.1 独立的列

尽量使用独立的列,索引列不能是表达式的一部分,也不能是函数的参数,也就是将索引单独放在比较符号的一侧;

3.2.2 索引的选择性

不重复的索引值和数据表的记录数的比值,比值越高则查询效率越高,因为选择性高的索引可以让mysql在查找的时候过滤更多的行。

多列索引,不考虑排序和分组的时候,将选择性最高的列放在索引最前面,性能是最高的。

3.2.3 避免冗余索引和重复索引

重复索引:在相同的列上按照相同顺序创建的相同类型的索引;

冗余索引(B-Tree):如果创建了索引(A,B),在创建索引(A)就是冗余索引,因为索引(A)是索引(A,B)的前缀索引。但是如果先创建索引(B,A)再创建索引(A)则不是冗余索引,因为他不是索引(B,A)的最左前缀列;

3.2.4 避免多个范围条件

mysql无法再使用范围列后面的其他索引列,但是对于“多个等值条件查询”没有这个限制;

eg.

SELECT  COLUMN FROM TABLE 
WHERE COLUMN_1 IN (...)
AND COLUMN_2 > ...
AND COLUMN_3 IN (...)
-- '>'就是范围查询,加入这三列是索引的话,到第三列就是用不到索引了;但是从explain来看她无法区别 '>'和in,他把这两种都当作'range'类型,但是in是多个等值查询,它后面的列会使用索引;
相关标签: mysql学习笔记