MYSQL的ORDER BY的组合索引

转载

mob6454cc6d5f87 2024-09-13 19:51:28

文章标签 MYSQL的ORDER BY的组合索引主键字段 mysql 文章分类 MySQL 数据库

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的ORDER BY的组合索引_mysql

如果不是按照索引的最左列开始查找,则无法使用索引；
例如：
下面的SQL语句未使用到索引，因未遵循最左匹配原理。
explain select * from student_info where cert_num = '1000222001' and test_id = '10024'
不能跳过索引中的列；
例如：
下面的SQL语句只用到一个索引id。 explain select * from student_info where id = '10001' and test_id = '10024'；
如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找；
例如：
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也有如下几种方式：

在字段级以key方式建立，如 create table t (id int not null primary key);
在表级以constraint方式建立，如create table t(id int, CONSTRAINT pk_t_id PRIMARY key (id));
在表级以key方式建立，如create table t(id int, primary key (id));
其它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;

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

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

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

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

6 rows in set (0.01 sec)

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

如果Key是空的, 那么该列值的可以重复，表示该列没有索引, 或者是一个非唯一的复合索引的非前导列；
如果Key是PRI, 那么该列是主键的组成部；
如果Key是UNI, 那么该列是一个唯一值索引的第一列(前导列)，且不能含有空值(NULL)；
如果Key是MUL, 那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL；

注：

如果对于一个列的定义，同时满足上述4种情况的多种，比如一个列既是PRI，又是UNI（如果是PRI，则一定是UNI）；那么"desc 表名"; 的时候，显示的Key值按照优先级来显示 PRI->UNI->MUL；那么此时，显示PRI。
如果某列不能含有空值，同时该表没有主键，则一个唯一性索引列可以显示为PRI，
如果多列构成了一个唯一性复合索引，那么一个唯一性索引列可以显示为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是多个等值查询,它后面的列会使用索引;

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。