本文共 2830 字,大约阅读时间需要 9 分钟。
1、什么是索引,什么时候需要使用索引?
索引是通过额外的空间来保存你需要快速查询的一些字段,将这些字段按照一定的顺序存储并与原始数据进行映射,这样如果你要查询这些字段就无需去查询原表(可以肯定的是索引表的数据量肯定比原表小)
索引是提高查询性能的最佳途径。
但是索引也有它适用的范围,通常对于可以预计是小表的话,我们不会去见索引会浪费空间;对于中表或者大表,通过建立合适的索引能够极大地提高查询速度;当然对于超大表,不建议建立索引,会浪费太多的存储空间,会有其他第三方支持,补充一点:对于超大表的话可以考虑用分区表。
2、B-Tree索引的查询类型?
MySQL数据库中最常用的引擎是InnoDB(当然还有MyISAM这种引擎不怎么用),一般来说索引的类型指明都是用B-Tree索引,该索引的特点有如下:
(1)匹配最左前缀:即最左边的列是要先匹配,才能匹配剩余的列。
(2)全值匹配
(3)范围匹配:当用了范围匹配,在之后的列将不再用索引了
(4)精确匹配某一列,并范围匹配另外一列:即将2、3结合
(5)只访问索引查询:这个特性也就是我们说的覆盖索引
3、InnoDB中无哈希索引,如何利用哈希索引?
在InnoDB中是不存在哈希索引,那么哈希索引又如何用,其实并不能叫做哈希索引,只是用哈希特性,本质上还是利用B-Tree索引。
通常的用法是:针对某个较长的字符串,比如存储了URL,值为:,那么我们要对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(
SELECT pro_id
FROM products
WHERE actor=’SEAN CARRY’AND title LIKE ‘%APOLLO%’
)AS t1 on (t1.prod_id=products.prod_id)
分析上面的语句:
可以看到在JOIN中的列全部是我们建立的多列索引的列,因此可以利用覆盖索引,当我们覆盖索引查找到需要的pro_id后,再关联表查询便可减少数据量。
8、MySQL中有序结果集生成的方式有哪些?索引在其中起什么作用?
两种方式:(1)通过排序操作;(2)按索引排序(如果Explain中的type:index说明是按索引排序)
9、什么是冗余索引?
比如创建了一个多列索引(A,B),再创建一个单列索引(A),此时单列索引就是冗余索引,因为这只是第一个索引的前缀索引;如果创建一个单列索引(B),则不是。
在创建索引中对于相同的列,我们可能会按照不同的顺序来创建索引来满足不同的业务需求。
10、范围条件IN和>、<在多列索引中使用的区别?
IN在索引中会转化为多个等式,而<、>不可以。
11、索引导致的性能缺点有哪些?
索引的问题:
(1)占用硬盘,mysql需要的时候会调入内存,但是如果太大仍然会有问题。
(2)导致插入、更新等操作变慢
12、选择索引和编写利用索引的查询,需要记住的三个原则:
(1)单行访问是很慢的
(2)按顺序访问范围数据是很快的
(3)索引覆盖查询是很快的
转载地址:http://rmomi.baihongyu.com/