MySQL索引

是什么

什么是索引

索引类似图书的目录, 可以提高数据库检索的效率, 降低数据库IO成本
索引其实就是一种排好序的数据结构

有哪些索引

根据索引是否为主键分类

主索引
- 主键索引(主索引): 非空且唯一

如果表中有主键: 主键即主索引
如果表中没有主键: InnoDB会选择一个唯一的非空索引代替, 如果没有非空唯一索引则隐式定义一个主键作为主索引

辅助索引

唯一索引: 唯一, 可为空
普通索引: 没有任何限制
全文索引: 大文本对象作为索引

5.6之前的版本中，全⽂文索引只能⽤用于MyISAM存储引擎
5.6 及以后的版本，MyISAM 和 InnoDB 均⽀支持全⽂文索引
在之前的MySQL中，全⽂文索引只对英⽂文有⽤用，⽬目前对中⽂文还不不⽀支持
MySQL8的版本中⽀支持了了对中⽂文分词的全⽂文索引

组合索引: 多个列组合为一个索引, 列值非空

根据数据的存储方式分类

聚簇索引: 索引即数据, 数据即索引 (一个索引与一条记录存放在同一内存块中)
非聚簇索引: 索引包含创建索引的列值和key, 获取其他列的值需通过key回表查询

对于InnoDB, 主键索引为聚簇索引(主键代表了唯一数据), 辅助索引为非聚簇索引
对于MyISAM, 只有非聚簇索引, 当找到索引后, 得到数据存在表中的行号, 通过行号回表查询数据

MyISAM和InnoDB的区别

数据存储方式：
- InnoDB由两种⽂文件组成，表结构，数据和索引
- MyISAM由三种⽂文件组成，表结构、数据、索引
索引的方式：
- 索引的底层都是基于B+Tree的数据结构建⽴
- InnoDB中主键索引为聚簇索引，辅助索引是非聚簇索引
- MyISAM中数据和索引存在不同的文件中，因此都是非聚簇索引
事务的支持：
- InnoDB支持事务
- MyISAM不支持事务

怎么做

索引的创建, 修改, 删除

主键索引

-- 创建表时, 直接创建主键索引(创建自增主键)
create table users(
    id int not null auto_increment primary key
)engine=innobd default charser=utf8mb4;

-- 修改时添加主键和自增
alter table users modify uid int primary key auto_increment;

-- 删除主键索引, 注意需要先取消自增, 在删除主键
alter table users modify uid int;
alter table users drop primary key;

唯一索引

-- 创建表时, 直接创建唯一索引
create table users(
    name vaarchar(10) not null,unique key u_name(name)
)engine=innodb default charset=utf8mb4;

-- 添加唯一索引 unique 当前列要求唯一, 允许为空
alter table users add unique u_name(name);

-- 删除唯一索引 
alter table users drop index u_name;

普通索引

-- 创建表时, 直接创建普通索引
create table users(
    email varchar(10) not null,key index_email(email)
)engine=innodb default charset=utf8mb4;

-- 添加索引
alter table users add index in_name(email);

-- 删除索引
drop index in_name on users;

全文索引

alter table 表名 add fulltext index ft_index(列名);

组合索引

-- 添加索引
alter table users add index in_x(email, phone, uname);

-- 删除索引
alter table users drop in_x;

最后编辑于：2020.08.16 10:45:27