Mysql的索引

1、索引的概念

索引是一个排序的列表，列表当中存储的时索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。

2、索引的作用

• 利用索引数据库可以快速定位，大大加快查询速度，为主要作用
• 表的数据很多，查询需要关联多个表，这个时候索引也可以提高查询速度
• 加快表与表之间的连接速度
• 使用分组和排序时可以大大减少时间
• 提高数据库恢复数据时的速度

索引的副作用

• 索引需要占用额外的磁盘空间，innodb表数据文件本身也是索引，myisam引擎中，索引和数据文件是分离的。
• 更新一个包含索引的表，要比更新一个没有索引表花费的时间更多。

3、创建索引的原则依据

有索引，数据会先进行索引查询，然后定位数据，索引使用不当，反而会增加数据库的负担

• 1、主键和外键必须有索引（创建好了主键和外键自动就是索引，不需要额外声明）
• 2、一个表超过了300行记录，必须要有索引，否则数据库会遍历表的所有数据
• 3、互相之间有关联的表，在关联字段应该建立索引
• 4、字段的唯一性太差不适合建立索引
• 5、更新太频繁的字段不适合建立索引
• 6、经常被where条件匹配的字段，尤其是表数据比较多的应该创建索引
• 7、在经常进行group by，order by的字段上建立索引
• 8、索引的列的字段越小越好，长文本字段不适合建立索引

4、索引的类型

1、B-树索引，又叫Btree树形结构的索引，也是大部分数据库的默认索引类型。

• 根节点：树的最顶端的分支节点
• 分支节点：指向索引里其他的分支节点，也可以是叶子节点
• 叶子节点：直接指向表里的数据行

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

2、哈希索引，散列索引，把任意长度的输入，通过散列算法变换成固定长度的输出，该输出就是散列值，散列值分别对应数据里的列和行。

• HASH 索引只支持等值比较，如“=”“IN()”或“<=>”
• HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

查看表的索引

show index from 表名;

修改索引类型

alter table 表名 engine=INNODB/MEMORY;

5、索引的分类和创建

创建表的时候需要考虑的因素

• 1、关联程度，三张表，选好关联字段
• 2、每个字段的长度也要考虑
• 3、设计合理的索引列
• 4、表数据要控制在合理的范围内，可以在牺牲一定性能的条件下满足需求，5秒以上就要考虑优化，十秒以上一般出问题（缓存失效、缓存击穿、缓存雪崩）

普通索引

最基本的索引类型，没有唯一性之类的限制

直接创建：
create index 索引名 on 表名 (列名[(length)]);
#(列名(length))：length是可选项
如果忽略length的值，则使用整个列的值作为索引
如果指定，使用列的前length个字符来创建索引
#索引名建议以“_index”结尾

修改表方式创建：
alter table 表名 add index 索引名 (列名);

创建表时指定：
create table 表名 (
...
index 索引名 (列名)
);

 唯一索引

与普通索引类似，但区别是唯一索引列的每个值都唯一
唯一索引允许有空值（注意和主键不同）
如果是用组合索引创建，则列值的组合必须唯一
添加唯一键将自动创建唯一索引

直接创建：
create unique index 索引名 on 表名 (列名);

修改表方式创建：
alter table 表名 add unique 索引名 (列名);

创建表时指定：
create table 表名 (
...
unique 索引名 (列名)
);

主键索引

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”
一个表只能有一个主键，不允许有空值
添加主键将自动创建主键索引

创建表时指定：
create table 表名 (
...
primary key (列名)
);

修改表方式创建：
alter table 表名 add primary key (列名); 

全文索引

适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息

直接创建：
create fulltext index 索引名 on 表名 (列名);

修改表方式创建：
alter table add fulltext 索引名 (列名);

创建表时指定：
CREATE TABLE 表名 (
...
索引名 (列名)
);
#数据类型可以为CHAR、VARCHAR或者TEXT

使用全文索引查询:
select * from 表名 where match(列名) against('查询内容');
select * from 表名 where 列名 like '查询内容';

联合索引

指定一个索引名，一个索引名对应多个列名
可以是单列上创建的索引，也可以是在多列上创建的索引
需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效

直接创建：
create index 索引名 on 表名(列名1,列名2,列名3,...);

修改表的方式创建：
alter table 表名 add index 索引名 (字段1,字段2,字段3,...);

创建表时指定：
CREATE TABLE 表名 (
...
index 索引名 (列名1,列名2,列名3,...),
);

select 查询时 where 语句中的条件字段 要与组合索引的字段排列顺序一致（最左原则）
select * from 表名 where 列名1='...' and 列名2='...' and 列名3='...';

 

6、查看索引是否生效

explain select...

示例：

create table test (
id int(4) primary key,
name varchar(10),
cardid int(18) not null,
phone int (11) not null,
unique index name_cardid_phone (name,cardid,phone)  #都是唯一值的联合索引
);

mysql机制：默认会找最短的索引列

联合索引，从左到右侧开始，不能跳过索引，否则索引会失效
字符串不加引号索引也会失效
使用or语句索引一定失效，使用or作为条件，mysql无法同时使用多个索引

最左前缀法则 : 如果是联合索引,查询从索引的最左侧开始,不跳过其他索引. 如果跳过,则索引失效
create index index_name on user(name,status,address);

select * from user where name = ? and status = ? and address = ?  (全部索引有效)
		
select * from user where status = ?   (索引失效)
		
select * from user where name = ? and status = ?   (两个索引有效)
		
select * from user where name = ? and address = ?  (第一个索引有效, 第二个失效)
		
select * from user where status = ? and address = ? (索引失效)

7、删除索引

直接删除索引：
drop index 索引名 on 表名;

修改表方式删除索引：
alter table 表名 drop index 索引名;

删除主键索引：
alter table 表名 drop primary key;