在进行数据库查询的时候,索引是非常重要的,当然前提是达到一定的数据量。索引就像字典一样,通过偏旁部首来快速定位,而不是一页页
的慢慢找。
索引依赖存储引擎层实现,所以支持的索引类型和存储引擎相关,同一种索引底层实现在不同存储引擎也是不一样的
本文基于MySQL8.0版本,关于explain用法,可以参考:MySQL高级 之 explain执行计划详解
创建索引语法:
CREATE TABLE table_name[col_name data_type]
[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY]
[index_name](col_name[length])
[ASC|DESC]
参数:
1、UNIQUE、FULLTEXT和SPATIAL为可选参数,分别表示唯一索引、全文索引和空间索引
2、INDEX和KEY为同义词,二者作用相同,用来指定创建索引
3、col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择
4、index_name为指定索引的名称,为可选参数,如果不指定则MySQL默认col_name为索引
5、length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度,如果数据列很长的话,MySQL不允许把整列作为索引
6、ASC或DESC指定升序或者降序的索引值存储
主键:主键索引是特殊的唯一索引,不能为null,建表时指定主键,只能有一个。
唯一索引:索引列的值必须唯一,包含联合索引,允许为null。
一般索引:最普通的索引,没有限制,相对于前两种效率最差。
全文索引:用于全文搜索,V5.7版本之后,MyIsam和InnoDB支持,只能用于char、varchar、Text列
除了建表时创建索引,还有两种方式:
1、Alter TABLE table_name ADD PRIMARY KEY (column_name)/UNIQUE [indexName] (column_name)/INDEX index_name (column_name)/FULLTEXT (column_name)
2、CREATE UNIQUE INDEX index_name/INDEX index_name ON table_name (column_name) 不适用于primary key和FULLTEXT
索引分类:
1、B-tree索引:除了archive以外的存储引擎,都支持
使用B+树的数据结构实现来存储数据,能够加快数据的查询速度,从索引的根节点开始往下搜索
B-tree索引的数据是顺序存储的,所以适合范围查找
使用场景:
1、全值匹配的查询,例如:id='1001'
2、匹配最左前缀的查询,例如现在把id和name建立一个联合索引,这时候查询id='1001'可以使用到联合索引,因为id为这个索引最左字段,
但是如果通过name进行筛选,就无法用到联合索引
3、匹配列前缀查询,例如:id like '100%'也可以用到联合索引
4、匹配范围值的查询,例如id < '1001' and id > '1010'
5、精确匹配左前列并范围匹配另一列
6、值访问索引的查询(覆盖索引)
总结:对于联合索引,MySQL从左向右匹配知道遇到范围查询(>,
个人使用MySQL8.0版本,测试发现范围匹配照样可以使用索引
使用限制:
1、不使用索引最左列的查询,无法使用到联合索引
2、使用索引时不能跳过索引中的列
3、not in和<>无法使用索引
4、索引中有某个列使用了范围查找,则右边的所有列都无法使用索引
PS:不仅可以在where查询中使用,也可以使用在order by和group by中
2、Hash索引:
Memory存储引擎默认的索引,InnoDB也有Hash索引,这是InnoDB自动建立
Hash索引是基于Hash表实现的,对于Hash索引中所有列,存储引擎为每一列计算一个hash值,hash索引存储的就是hash码
理论上,效率还是好于B树索引,但是其使用限制太多了
使用限制:
通过hash索引找到对应的行,然后对行的数据进行读取,进行两次查找
无法用于避免数据的排序操作
无法使用部分索引键的查询
<