什么是索引
索引是一个单独的、存储在磁盘上的数据库结构,包含着对数据表里所有记录的引用指针。使用索引是提高查询效率的最好的方式。
索引是在存储引擎中实现的,因此,每种存储引擎的索引都不尽相同,且并不是每种存储引擎都支持所有的索引类型。
MySQL中索引的存储类型有两种,BTREE和HASH,且不是每种存储引擎都能使用两种。InnoDB和MyISAM存储引擎只支持BTREE索引;MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。
为什么使用索引以及有什么缺陷
- 使用索引有以下优势:
- 加快查询效率,这也是索引的主要功能
- 通过唯一索引保证字段的唯一性,比如:自增流水号
- 加快表和表之间的连接速度
- 减少分组和排序的数据查询时间
- 索引的缺点:
- 创建和维护索引也要耗费时间,并且和数据量是成正比的
- 索引也会占用一定的存储空间,如果对于存储空间本就不足的情况下,会显得捉襟见肘
- 在进行表的新增、修改和删除的时候,索引也要进行维护(可以先禁用索引)
索引的类型
MySQL的索引可以大致分为以下几类:
- 普通索引是MySQL中基本索引类型,通常情况下就是使用这类索引来提高查询效率,支持重复和为null;
- 唯一索引要求索引列的值必须唯一,允许为null,主键索引就是一种不允许为null的特殊唯一索引;
- 单列索引一个索引里只有一个字段,一个表可以有多个单列索引;
- 组合索引就是一个索引里面包含了多个字段,只有在查询条件中使用了这些字段的左边字段是,索引才会生效。
- 全文索引(fulltext)在定义索引的列上支持值的全文查找,允许重复和为null,只支持char、varchar、text类型字段
- 空间索引是对空间数据类型的字段建立的索引(用的少就懒得了解了)
MySQL索引的创建方式
- 创建表的时候创建
CREATE TABLE table_name [col_name data_type] [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name [length]) [ASC|DESC]
其中UNIQUE、FULLTEXT和SPATAIL为可选参数,分别表示唯一索引、全文索引和空间索引;INDEX和KEY为同义词,作用相同。
CREATE TABLE test01 (
id INT NOT NULL,
NAME VARCHAR ( 4 ) NOT NULL,
UNIQUE INDEX uniqueIdx ( id )
);
- 在已存在表中创建
- alter table
ALTER TABLE table_name add [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
- create index
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name on table_name (col_name[length],...) [ASC|DESC]
ALTER TABLE test01 ADD UNIQUE INDEX uniqueIdx01 ( id );
CREATE UNIQUE INDEX uniqueIdx02 ON test01 ( id );
什么情况下需要增加索引
- 需要频繁使用的字段,比如按某个字段分组或排序,又或者用来进行表连接的时候可以考虑使用索引
- 必须保证某一字段的唯一性的时候,可以考虑使用唯一索引
如何对索引进行分析
可以使用explain关键字进行分析
EXPLAIN
SELECT
*
FROM
sys_user a
INNER JOIN sys_score b ON a.id = b.userid
其中:
- possible_key给出了MySQL在搜索数据记录时可选用的索引
- key是实际选用的索引
索引失效的几种情况
- 使用不等于(!=或<>);
- 使用like的时候以%开头;
- 字符串不加单引号;
- 使用or;
- 使用聚合函数;
- 使用组合索引,不遵循“最作前缀”原则
索引的实现原理
索引是在存储引擎层实现的,不同的存储引擎有不同的实现方式:
-
MyISAM
B+Tree作为索引结构,使用叶子节点的data存放数据记录的地址。 -
InnoDB
也是使用B+Tree作为索引结构,但是在实现方式上却有所不同。在InnoDB中的data域中保存了完整的数据记录。这个索引的key就是数据表的主键,因此InnoDB表数据文件本身就是主索引。
如何在使用phone like ‘%9527’ 这种情况下使用索引
可以加一个冗余列phone_reverse,然后为冗余列建立索引,查询语句中使用语句phone_reverse like reverse('%9527')
即可。