索引是什么?
索引类似大学图书馆建书目索引,可以提高数据检索的效率,降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降,虽然官方文档说500~800w记录,所以大数据量建立索引是非常有必要的。
索引类型及创建
主键索引
主键索引是一种特殊的唯一索引,一个表只能有一个主键,不允许有空值。一般是在建表的时候同时创建主键索引:
CREATE TABLEtable
(id
int(11) NOT NULL AUTO_INCREMENT ,title
char(255) NOT NULL , PRIMARY KEY (id
));
普通索引
这是最基本的索引,它没有任何限制
直接创建索引
CREATE INDEX index_name ON table(column(length))
修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
创建表的时候同时创建索引
CREATE TABLEtable
(id
int(11) NOT NULL AUTO_INCREMENT ,title
char(255) CHARACTER NOT NULL ,content
text CHARACTER NULL ,time
int(10) NULL DEFAULT NULL , PRIMARY KEY (id
), INDEX index_name (title(length)))
组合索引(复合索引)
指多个字段上创建的索引,只有在查询条件中使用了创建索引时的第一个字段,索引才会被使用。使用组合索引时遵循最左前缀集合 索引第一个字段唯一值越多越好 (离散度)
如何判断离散度: select count( distinct id ) from A
ALTER TABLEtable
ADD INDEX name_city_age (name,city,age);
唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。它有以下几种创建方式:
创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
创建表的时候直接指定
CREATE TABLEtable
(id
int(11) NOT NULL AUTO_INCREMENT ,title
char(255) CHARACTER NOT NULL ,content
text CHARACTER NULL ,time
int(10) NULL DEFAULT NULL , UNIQUE indexName (title(length)));
全文索引
主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE index创建fulltext索引,要比先为一张表建立fulltext然后再将数据写入的速度快很多。
CREATE TABLEtable
(id
int(11) NOT NULL AUTO_INCREMENT ,title
char(255) CHARACTER NOT NULL ,content
text CHARACTER NULL ,time
int(10) NULL DEFAULT NULL , PRIMARY KEY (id
), FULLTEXT (content));
索引的优缺点
以上介绍了索引类型 也介绍了索引会增加 数据检索的效率,降低数据库的IO成本 。但是 是索引越多越好吗?如果一张表所有字段都创建了索引 会怎么样?
优点
可以大大加快数据的检索速度,这也是创建索引的最主要的原因,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
缺点
创建索引和维护索引要耗费时间,具体地,当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,会降低增/改/删的执行效率;占用物理空间。
如何正确创建索引
尽量使用自增长主键
首先能有效减少页分裂,MySQL中数据是以页为单位存储的且每个页的大小是固定的(默认16kb),如果一个数据页的数据满了,则需要分成两个页来存储,这个过程就叫做页分裂。
如果使用了自增主键的话,新插入的数据都会尽量的往一个数据页中写,写满了之后再申请一个新的数据页写即可(大多数情况下不需要分裂,除非父节点的容量也满了)。
选择性高的列优先
在创建索引的时候通常要求将选择性高的列放在最前面,对于选择性不高的列甚至可以不创建索引。如果选择性不高,极端性情况下可能会扫描全部或者大多数索引,然后再回表,这个过程可能不如直接走主键索引性能高。
索引列的选择往往需要根据具体的业务场景来选择,但是需要注意的是索引的区分度越高则价值就越高,意味着对于检索的性价比就高。索引的区分度等于count(distinct 具体的列) / count(*),表示字段不重复的比例。(例如TYPE 值 1 2 3 索引意义不大)
联合索引优先于多列独立索引
联合索引优先于多列独立索引, 假设有三个字段a,b,c, 索引(a)(a,b),(a,b,c)可以使用(a,b,c)代替。MySQL中的索引并不是越多越好,各个公司的规定中往往会限制单表中的索引的个数。原因在于,索引本身也会占用一定的空间,并且维护一个索引时有一定的代码的,所以在满足需求的情况下一定要尽可能创建更少的索引。
覆盖索引避免回表 (使用联合索引会出现)
覆盖索引如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引 k 已经“覆盖了”我们的查询需求,我们称为覆盖索引。由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
覆盖索引的查询优化
覆盖索引同时还会影响索引的选择,对于(a,b,c)索引来说,理论上来说不满足最左匹配原则,但是实际上也会走索引。原因在于,优化器认为(a,b,c)索引的性能会高于全表扫描,实际情况也是这样的,感兴趣的小伙伴不妨分析一下上文中介绍的数据结构。
explain select a,b,c from test_table where b = “188466668888” and c = “23”;
满足查询和排序
索引要满足查询和排序。大部分同学在创建索引时,通常第一反应是查询条件来选择索引列,需要注意的是查询和排序同样重要,我们建立的索引要同时满足查询和排序的需求.
包含要排序的列
select c, d from test_table where a = 1 and b = 2 order by c;
虽然查询条件只使用了a,b两个字段,但是由于排序用到了c字段,我们能可以建立(a,b,c)联合索引来进行优化。
保证索引字段顺序
如上文中的介绍,索引的字段顺序决定了索引数据的组织顺序。要想更高性能的检索数据,一定要尽可能的借助底层数据结构的特点来进行。如,索引(a, b)的默认组织形式就是先根据a排序,在a相同的情况下再根据b排序。
考虑索引的大小
内存中的空间十分宝贵,而索引往往又需要在内存中。为了在有限的内存中存储更多的索引,在设计索引时往往要考虑索引的大小。比如我们常用的邮箱,xxxx@xx.com, 假设都是abc公司的,则邮箱后缀完全一致为@abc.com, 索引的区分度完全取决于@前面的字符串。
>针对上述情况,MySQL 是支持前缀索引的,也就是说,你可以定义字符串的一部分作为索引。默认地,如果你创建索引的语句不指定前缀长度,那么索引就会包含整个字符串。(如何创建 alter table x_test add index(x_name(1)))
如果使用的 email 整个字符串的索引结构执行顺序是这样的:从 index1 索引树找到满足索引值是’liqiang156@11.com’的这条记录,取得 id (主键)的值ID2;到主键上查到主键值是ID2的行,将这行记录加入结果集;
取 email 索引树上刚刚查到的位置的下一条记录,发现已经不满足 email='liqiang156@qq.com’的条件了,循环结束。这个过程中,只需要回主键索引取一次数据,所以系统认为只扫描了一行。但是它的问题就是索引的后半部分都是重复的,浪费内存。
这时我们可以考虑使用前缀索引,如果使用的是 index2 (email(7) 索引结构),执行顺序是这样的:从 index2 索引树找到满足索引值是’liqiang’的记录,找到的第一个是 ID1,到主键上查到主键值是 ID1 的行,判断出 email 的值是’liqiang156@xxx.com’,加入结果集。
取 index2 上刚刚查到的位置的下一条记录,发现仍然是’liqiang’,取出 ID2,再到 ID 索引上取整行然后判断,这次值仍然不对,则丢弃继续往下取。
重复上一步,直到在 index2 上取到的值不是’liqiang’或者索引搜索完毕之后,循环结束。在这个过程中,要回主键索引取 4 次数据,也就是扫描了 4 行。通过这个对比,你很容易就可以发现,使用前缀索引后,可能会导致查询语句读数据的次数变多。
不过方法总比困难多,我们在建立索引时可以先通过语句查看一下索引的区分度,或者提前预估余下前缀长度,对于上述问题我们可以将前缀长度调整为9即可达到效果。索引,在使用前缀索引时,一定要充分考虑数据的特征,选择合适的
对于一些比较长的字段的等值查询,我们也可以采用其他方式来缩短索引的长度。比如url一般都是比较长,我们可以冗余一列存储其Hash值。
select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=‘input_id_card_string’
对于我们国家的身份证号,一共 18 位,其中前 6 位是地址码,所以同一个县的人的身份证号前 6 位一般会是相同的。为了提高区分度,我们可以将身份证号码倒序存储。
select field_list from t where id_card = reverse(‘input_id_card_string’);
如何正确使用索引
不在索引上进行任何操作
索引上进行计算,函数,类型转换等操作都会导致索引从当前位置(联合索引多个字段,不影响前面字段的匹配)失效,可能会进行全表扫描。
对于需要计算的字段,则一定要将计算方法放在“=”后面,否则会破坏索引的匹配,目前来说MySQL优化器不能对此进行优化。
隐式类型转换
需要注意的是,在查询时一定要注意字段类型问题,比如a字段时字符串类型的,而匹配参数用的是int类型,此时就会发生隐式类型转换,相当于相当于在索引上使用函数。
只查询需要的列
在日常开发中很多同学习惯使用 select * … 来构建查询语句,这种做法也是极不推荐的。主要原因有两个,首先查询无用的列在数据传输和解析绑定过程中会增加网络IO,以及CPU的开销,尽管往往这些消耗可以被忽略,但是我们也要避免埋坑。
其次就是会使得覆盖索引"失效", 这里的失效并非真正的不走索引。覆盖索引的本质就是在索引中包含所要查询的字段,而 select * 将使覆盖索引失去意义,仍然需要进行回表操作,毕竟索引通常不会包含所有的字段,这一点很重要。
不等式条件
查询语句中只要包含不等式,负向查询一般都不会走索引,如 !=, <>, not in, not like等。
模糊匹配查询
最左前缀在进行模糊匹配时,一般禁止使用%前导的查询,如like “%zhangsan”。
最左匹配原则
索引是有顺序的,查询条件中缺失索引列之后的其他条件都不会走索引。比如(a, b, c)索引,只使用b, c索引,就不会走索引。
如果索引从中间断开,索引会部分失效。这里的断开指的是缺失该字段的查询条件,或者说满足上述索引失效情况的任意一个。不过这里的仍然会使用到索引,只不过只能使用到索引的前半部分。
值得注意的是,如果使用了不等式查询条件,会导致索引完全失效。而上一个例子中即使用了不等式条件,也使用了隐式类型转换却能用到索引。
同理,根据最左前缀匹配原则,以下如果使用b,c作为查询条件则不会使用(a, b, c)索引。
下一章: B树与B+树