索引的应用

1.创建索引

​

create index 索引名 on 表名 (属性A，属性B...)	//创建索引
create unique 索引名 on 表名 (属性A，属性B...)	//唯一索引

drop index 索引名 //删除

应该创建索引的列：经常被搜索的列

​ 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
​ 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度。因为大多连接都在外码和主码属性之间进行。
​ 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连 续的
​ 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
​ 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

全文索引

​ 对于大量文本检索操作，使用like效率很低，这时可以使用全文索引来提高效率。它必须建立在字符串上。

​ 全文索引字段值必须建立在最大字段值和最小字段值之间才有效（Innodb：3-84）

​ 默认使用等值匹配，如a匹配a，而不匹配ab。可以使用against(‘a*’ in boolean mode)进行模糊匹配

create fulltext 索引名 on 表名 (属性A，属性B...)

//使用
select *
from 表
where match(name/*属性*/) against('aaa'/*要匹配的串*/)

二、回表查询和覆盖索引

​ 回表查询指通过索引项定位到数据后，还需要将相应数据行从磁盘读入主存。当select中包含不是搜索码的属性时，就会产生回表查询。

​ 覆盖索引值要查询的属性就是索引项的搜索码，属性的值就在索引项里存储，可以直接返回索引项中存储的数据，效率高。

三、最左前缀原则

​ 当使用复合索引时遵循最左前缀原则。顾名思义，所谓最左前缀，就是最左优先，即查询中使用到最左边的搜索码，索引会生效，否则索引失效。例：一个复合索引包括(name,age,time)，那么单独查询name，或查询name和age，或name、age、time，索引都会生效。若查询age或age，name。等，则不会使用索引。但这只是理论上，实际上mysql的查询优化器会自动选择最优的查询顺序。

​ 实际上，建立一个索引，对于索引中的字段，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

四、一些索引细节的面试题

1.mysql使用like模糊查询时，索引能不能起作用？

​ 1. like %keyword 索引失效，使用全表扫描。但可以通过翻转函数+like前模糊查询+建立翻转函数索引=走翻转函数索 引，不走全表扫描。

​ 2.like keyword% 索引有效。

​ 3.like %keyword% 索引失效，也无法使用反向索引。

2.mysql中如果某一列有null值，那么包含该列的索引是否有效？

​ 索引是有效的。虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。null值通常需要额外的空间来记录null值。不建议列上允许为空。最好限制not null，并设置一个默认值，比如0和''空字符串等，如果是datetime类型，可以设置成'1970-01-01 00:00:00'这样的特殊值。

​ 对MySQL来说，null是一个特殊的值，。比如：不能使用=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等，某列可为null比not null可能需要更多的存储空间等。

五、索引和排序

​ mysql中支持filesort和index两种排序方式。

​ filesort：先把结果查出，然后在缓存中进行排序，效率低。它有两种算法：单路排序和双路排序。

​ 单路排序：从磁盘查询所需的所有列的数据，然后在内存中排序并返回。如果查询数据超出sortbuffer，会导致多次磁盘io，并创建临时表，降低效率。解决方案：少使用select *；增加sort_buffer_size容量和max_length_for_sort_data容量。

​ 双路排序：需要两次磁盘io，第一次将需要排序的字段（列）读出来进行排序，第二次读取其他字段数据。

​ index：使用索引自动实现排序，不需另做排序操作，效率高。

以下几种情况会使用index排序

​ 1.order by子句索引列组合满足索引最左前缀原则

select id from  表 order by id; //对应（id），（id，name，...）索引有效

​ 2.where子句+order by子句索引列组合满足索引最左前缀原则

select id from 表 where id=3 order by name //对应（id，name）索引有效

以下会使用filesort

​ 1.对索引列同时使用asc和desc

​ 2.where子句+order by子句索引列组合满足索引最左前缀原则 ，但where使用了范围查询

​ 3.order by子句或where子句+order by子句索引列组合不满足最左前缀原则。

​ 4.order by子句或where子句+order by子句使用了不同的索引。

select id from 表 order by name,age; //分别在name和age上建立了索引，但不是复合索引

​ 5.order by子句或where子句+order by子句中索引列使用了表达式。