1、什么是索引,什么时候需要使用索引?
索引是通过额外的空间来保存你需要快速查询的一些字段,将这些字段按照一定的顺序存储并与原始数据进行映射,这样如果你要查询这些字段就无需去查询原表(可以肯定的是索引表的数据量肯定比原表小)。索引是提高查询性能的最佳途径。
但是索引也有它适用的范围,通常对于可以预计是小表的话,我们不会去见索引会浪费空间;对于中表或者大表,通过建立合适的索引能够极大地提高查询速度;当然对于超大表,不建议建立索引,会浪费太多的存储空间,会有其他第三方支持,补充一点:对于超大表的话可以考虑用分区表。
2、B-Tree索引的查询类型?
MySQL数据库中最常用的引擎是InnoDB,一般来说索引的类型指明都是用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位的哈希函数来减少重复的概率。
注意:针对长文本建立索引的时候,我们也可以考虑取长文本的前缀建立索引。
4、那些情况下创建的索引列不起作用?
索引列不起作用的情况:
1)、将所有列使用函数等条件,比如:
SELECT
num_id
FROM
USER us
WHERE
us.id + 1 = 5;//使用了加号
SELECT
userid
FROM
USER
WHERE
TO_DAYS( CURRENT_DATE ) - TO_DAYS( query_date ) <= 10;
2)、范围查询之后的索引列不起作用
范围查询主要是指< 、>,以及like,注意:对于in 会转化多个等式,因此仍然可以。
5、多列索引中列的排序如何选择?
对于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;
如果确认custom_id比staff_id的选择性更高,则添加如下多列索引:
ALTER TABLE payment ADD KEY ( customer_id, staff_id );
6、MySQL中有序结果集生成的方式有哪些?索引在其中起什么作用?
两种方式:
1)、通过排序操作;
2)、按索引排序(如果Explain中的type:index说明是按索引排序)
7、什么是冗余索引?
比如创建了一个多列索引(A,B),再创建一个单列索引(A),此时单列索引就是冗余索引,因为这只是第一个索引的前缀索引;如果创建一个单列索引(B),则不是。
在创建索引中对于相同的列,我们可能会按照不同的顺序来创建索引来满足不同的业务需求。
8、范围条件IN和>、<在多列索引中使用的区别?
IN在索引中会转化为多个等式,而<、>不可以。
9、索引导致的性能缺点有哪些?
索引的问题:
1)、占用硬盘,mysql需要的时候会调入内存,但是如果太大仍然会有问题。
2)、导致插入、更新等操作变慢
10、选择索引和编写利用索引的查询,需要记住的三个原则:
1)、单行访问是很慢的
2)、按顺序访问范围数据是很快的
3)、索引覆盖查询是很快的