MySQL 索引的那点知识

最新推荐文章于 2021-02-27 03:44:03 发布

timchen525

最新推荐文章于 2021-02-27 03:44:03 发布

阅读量330

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/timchen525/article/details/75194423

版权

MySQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

1、什么是索引，什么时候需要使用索引？

索引是通过额外的空间来保存你需要快速查询的一些字段，将这些字段按照一定的顺序存储并与原始数据进行映射，这样如果你要查询这些字段就无需去查询原表（可以肯定的是索引表的数据量肯定比原表小）

索引是提高查询性能的最佳途径。

但是索引也有它适用的范围，通常对于可以预计是小表的话，我们不会去见索引会浪费空间；对于中表或者大表，通过建立合适的索引能够极大地提高查询速度；当然对于超大表，不建议建立索引，会浪费太多的存储空间，会有其他第三方支持，补充一点：对于超大表的话可以考虑用分区表。

2、B-Tree索引的查询类型？

MySQL数据库中最常用的引擎是InnoDB（当然还有MyISAM这种引擎不怎么用），一般来说索引的类型指明都是用B-Tree索引，该索引的特点有如下：

（1）匹配最左前缀：即最左边的列是要先匹配，才能匹配剩余的列。

（2）全值匹配

（3）范围匹配：当用了范围匹配，在之后的列将不再用索引了

（4）精确匹配某一列，并范围匹配另外一列：即将2、3结合

（5）只访问索引查询：这个特性也就是我们说的覆盖索引

3、InnoDB中无哈希索引，如何利用哈希索引？

在InnoDB中是不存在哈希索引，那么哈希索引又如何用，其实并不能叫做哈希索引，只是用哈希特性，本质上还是利用B-Tree索引。

通常的用法是：针对某个较长的字符串，比如存储了URL，值为：http://www.baidu.com，那么我们要对URL进行建立索引，如果直接建立的话，索引占用的空间太大，可以通过哈希函数（比如：crc32）将其值映射到一个固定长度的数值，将该列的值添加表中，并为该列创建索引。这里面会有一个问题，那就是可能会存在重复的哈希值。解决的办法：

（1）如果能够容忍重复的情况下，在查询where条件中，除了附加索引的列的值，还要添加url的值，当重复的时候，就会用url的值来去重。

（2）另外一种方法，是通过64位的哈希函数来减少重复的概率。

补充一点：针对长文本建立索引的时候，我们也可以考虑取长文本的前缀建立索引，参考第5个问题。

4、那些情况下创建的索引列不起作用？

索引列不起作用的情况：

（1）将所有列使用函数等条件，比如：

SELECT actor_id FROM sakila.actor WHERE actor.id + 1 = 5; //使用了加号

SELEC … WHERE TO_DAYS(CURRENT_DATE)-TO_DAYS(date_col)<=10;

（2）范围查询之后的索引列不起作用

范围查询主要是指< 、>，以及like，注意：对于in 会转化多个等式，因此仍然可以。

5、前缀索引如何创建及其特性，以及如何实现后缀索引？

创建前缀索引：

ALTER TABLE sakila.city_demo ADD KEY (city(7))

根据某个前缀利用group统计出个数：

SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 10;

在一个查询中计算不同长度的前缀的选择性：

SELECT COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4 FROM sakila.city_demo;

6、多列索引中列的排序如何选择？

对于B-Tree的多列索引，采用的是最左列排序，即在设计多列索引的情况，应该将选择性最高的放到索引的最前列（好处是能够快速定位到具体的行，不然会存在很多重复的行）

一般选择的法则：经验法则，示例如下：

比如针对两个列：staff_id和cunstom_id列的选择，通过以下方法：

SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, COUNT(DISTINCT custom_id)/COUNT(*) AS customer_id_selectivity,COUNT(*) FROM payment \G;

如果确认custom_id比staff_id的选择性更高，则添加如下多列索引：

ALTER TABLE payment ADD KEY(customer_id, staff_id);

7、什么是覆盖索引？如何使用覆盖索引来实现延迟关联呢？

覆盖索引：如果在某次查询中的字段覆盖了索引中的字段（即：一个索引包含或者说覆盖所有要查询的字段）

之所以要强调覆盖索引，是因为覆盖索引的条目数目通常远小于数据行大小，且索引是按列值顺序存储的（适合group以及相关范围查询）

一个使用延迟关联中使用了覆盖索引的例子：

比如：SELECT * FROM products WHERE actor=”SEAN CARRY” AND title like ‘%APOLO%’\G;

通过Explain计划看到，Extra:Using where说明没有使用到索引。

解决方法：添加多列索引(artist, title, pro_id)

SELECT *

FROM products

JOIN(