MySQL的索引知识

程序员文章站 2022-07-05 17:20:02

一、什么是索引。索引是用来加速查询的技术的选择之一，在通常情况下，造成查询速度差异的因素就是索引是否使用得当。当我们没有对数据表的某一字段段或者多个字段添加索引时，实际上执行的全表扫描操作，效率很低。而如果我们为某些字段添加索引，在执行搜索时便可以通过扫描索引，然后再找出索引对应的值，从 ......

一、什么是索引。

索引是用来加速查询的技术的选择之一，在通常情况下，造成查询速度差异的因素就是索引是否使用得当。当我们没有对数据表的某一字段段或者多个字段添加索引时，实际上执行的全表扫描操作，效率很低。而如果我们为某些字段添加索引，mysql在执行搜索时便可以通过扫描索引，然后再找出索引对应的值，从而提高效率。

二、索引的类型

实际上索引的类型不多，以下只是针对个人以前遇到的索引概念的解释，有可能某个索引有多种称呼，只是取决于你用哪个角度去描述它。

b树索引：采用b-trees数据结构存储索引，比如primary key，unique ，index。
hash索引：将一个散列函数应用于每一个列值，最终的散列值都会被存入索引，用于执行查找。
r树索引：采用r-trees数据结构存储索引，比如spatial index。（空间数据类型的索引）
全文索引（fulltext index）：一般在char，varchar或者text列上创建此索引。可用来代替like ‘%xx%’实现模糊查询。
前缀索引：只对一个列或者多个列的前几个字符或者字节索引。
唯一索引：只对一个列创建索引。
多列索引：对多个列创建索引。在多列索引中必须注意最左前缀这个原则。比如对于（col1，col2，col3）这三列进行索引时，只有（col1），（col1,col2),(col1,col2,col3)才能进行索引搜索。
注意（col1，col3）也不能进行索引。
聚簇索引：每个innodb表都有一个特殊的索引称为聚簇索引，一般来说，当为一个表定义一个primay key时，innodb就会使用它作为聚簇索引。如果没有定义primary key时，mysql就会查找第一个非空的 unique index作为聚簇索引。如果以上两种情况都不满足的话，innodb内部会在表的每一行产生一个隐藏的并且名为gen_clust_index的聚簇索引。这个聚簇索引是一个六个字节长度的行id字段，id值随着新行的插入而单调增长。实际上，除了聚簇索引，其他索引都称为二级索引。在innodb中，二级索引的每一行（将索引假设为行方便理解，实际上索引的存储方式取决于具体的存储引擎）中都包含着一个primary key列，innodb使用primary key这一列的列值在聚簇索引中查找相对应的数据（可以将聚簇索引理解为中间值），从而最后得到最终的结果集。聚簇索引的数据分布如下图：（图来自《高性能mysql》）

上图中，节点页存放是索引（对应着二级索引的primary key），叶子页存放着所对应的数据，节点页和叶子页这个整体就称为聚簇索引，由此可见，聚簇索引更像是一种数据存储结构。
覆盖索引：当查询的结果集可以通过所创建的索引查找出来时，这个索引就称为覆盖索引。
下面举个例子：

对于上面这个表，当执行下面的语句时，就会使用覆盖索引查询。

因为我们在创建表的时候对last_name、first_name创建了多列索引，并且在查询的时候只查询这两列的结果，因此mysql会使用覆盖索引查询数据，这也意味着mysql不会对实际的数据行进行查询，因为所需结果已经可以从索引中查找出来了。
另外可以看一下下面的sql语句：

在上面的sql语句中，我们想查询id和last_name的值，而id是主键，last_name是多列索引中的最左索引，但是此时的查询依旧使用覆盖索引查询。原因在于id实际是作为聚簇索引的，而多列索引自然就是二级索引了，上面提到，二级索引都包含着一列primary key列，而列值就是聚簇索引的索引值，因此此时mysql可以直接使用覆盖索引中查找出对应的结果集。

三、b树索引和hash索引的比较

innodb存储引擎和myisam存储引擎都只支持b树索引（实际上innodb还支持自适应的hash索引，只是不能人为创建），memory存储引擎默认使用hash索引，但它也支持b树索引。
在使用<、<=、=、>=、>、<>、!=和between运算符，进行精确比较或者范围比较时，使用b树索引会带来高效。如果匹配模式是以一个纯字符串，而不是一个通配符作为开头的，那么b树索引还可以用在使用like进行模式匹配的操作里。
下面举个例子：
对于hash索引，在使用运算符=或者<=>（安全等于的意思，当比较的值含有null值的时候，来返回一个布尔值）完成精确（这里说精确是因为hash索引是用一个hash函数对整个列值hash，而不是某几个字符或者字节）匹配的比较操作里，散列索引的速度非常快。

四、索引的挑选

一般对于出现在where子句中的列、连接子句中的列、或者出现在order by或group by子句中的列创建索引是比较好的。
尽量索引短小值。应尽量选用较小的数据类型。比如值的长度不超过25个字符，那么就不要用char（200），其他数据类型同理。特别是innodb表来说，因为它使用的是聚簇索引，如果主键过长的话，会导致二级索引占用的存储空间过大。
索引字符串值的前缀。当对字符串列进行索引时，应当尽可能指定前缀长度。比如某一个列的前n个字符足够唯一的话，那么就可以不用为整列进行索引。

五、索引的代价

索引确实可以加快检索速度，但是它同时也降低了索引列的插入、删除和更新值的速度，因为写入一个行不仅是写入一个数据行，还要更改索引。表的索引越多，需要做出的更改就越多，平均性能下降得也就越多。并且当所创建的索引过多时，mysql查询优化器在选择使用哪种索引方案时，也会降低一定的效率。其次，索引也会占用磁盘空间，多个索引会占据更大的空间。与没有索引相比，使用索引很快便达到表的大小极限。

六、创建索引

使用create table创建索引（index_name可选）
使用alter table为已有表创建索引（index_name可选）
使用create index创建索引（index_name不可省略）

如果某个索引列在索引时使用了primary key或spatial，则它必须为not null的。其他索引列允许包含null值。
如果想要限制某个索引，让它只包含唯一值，那么可以把这个索引创建为primary key或unique索引。这两种索引很像，主要区别有一下两点：
每个表只能包含一个primary key。因为primary key的名字总是为primary，而同一个表不允许有两个同名的索引。可以在一个表里放置多个unique索引。
primary key不可以包含null值，而unique索引可以。如果某个unique索引包含了null值，那么它就可以包含多个null值。因为null值不会与任何值相等，包括它本身。