1 MySQL索引
MySQL支持的引擎如下图,其中常用的引擎有MyISAM和InnoDB两种
通过命令show engines可以查看MySQL所支持的引擎类型如下,其中常用的引擎有InnoDB、MyISAM和Memory三种。
在MySQL 5.5及以上版本以后默认的引擎为InnoDB,可已通过命令查看默认的存储引擎
1.1 InnoDB和MyISAM的区别
区别 | Innodb | MyISAM |
---|---|---|
事务 | 支持 | 不支持 |
外键 | 支持 | 不支持 |
索引 | 即支持聚簇索引又支持非聚簇索引 | 只支持非聚簇索引 |
行锁 | 支持 | 不支持 |
表锁 | 支持 | 支持 |
存储文件 | frm,ibd | frm,myi,myd |
具体行数 | 每次必须要全表扫描统计行数 | 通过变量保存行数 |
那么如何选择索引呢?
1、是否需要支持事务,如果需要选择innodb,如果不需要选择myisam
2、如果表的大部分请求都是读请求,可以考虑myisam,如果既有读也有写,使用innodb
2 数据库索引类型
2.1 聚簇索引与非聚簇索引
InnoDB存储引擎在进行数据存储时必须要绑定到一个索引列上,默认是主键,如果没有主键会选择唯一键,如果也没有唯一键则会生成一个6字节的row_id。跟数据绑定在一起的引擎称之为聚簇索引,没有跟数据绑定在一起的索引成为非聚簇索引。
InnoDB存储引擎中既有聚簇索引也有非聚簇索引,而MyISAM只支持非聚簇索引。
- 聚簇索引:将数据存储和索引放在一起、并且是按照一定的顺序组织的,找到索引也就找到了数据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是相邻的存放在磁盘上的。
- 非聚簇索引:叶子节点不存储数据,存储的是数据行地址,也就是说根据索引查找到数据行的位置再去磁盘查找数据,这就有点类似一本书的目录,比如要找到第三章第一节,那就现在目录里面查找,找到对应的页码后再去对应的页码看文章。
2.2 索引分类
索引分为:主键索引、唯一索引、普通索引、组合索引和全文索引
- 主键索引:以主键所创建的一种特殊索引,一般创建的表中如果有主键,会自动创建主键索引,不需要通过命令来创建。
-- 建表语句如下:其中id为主键
create table user_info
(
id int not null,
name varchar(32) not null comment '姓名',
gender int null comment '性别',
age int null comment '年龄',
tel char(11) null comment '手机号',
constraint user_info_pk
primary key (id)
)
comment '人员详细信息表';
通过命令查询查询表中的索引,可以在没有通过命令创建索引的情况下已经存在一个索引,索引名称为:PRIMARY,Column_name为id,而id为表中的主键。同时Index_type为BTREE,而实际上MySQL的索引数据结构为B+树,请不要被查询结果所迷惑。
当表没有主键的时候,如果表有唯一键,则会根据唯一键自动创建索引
-- 没有主键,只有唯一字段
create table user_info2
(
id int null,
name int null comment '姓名'
);
create unique index user_info2_name_uindex
on user_info2 (name);
- 唯一索引:唯一索引的值必须唯一,但是允许有控制,如果是组合唯一索引,则列的组合值必须唯一。
-- 通过create方式创建唯一索引
CREATE UNIQUE INDEX NAME_INDEX ON user_info(name);
-- 当然,也可以通过以下方式来创建索引
ALTER TABLE user_info ADD UNIQUE NAME_INDEX(name);
通过命令可以查看新创建的唯一索引NAME_INDEX。
插入两条不同的数据,但是name列同为lisi,由上面的建表语句可知,我们的name没有唯一限制,但在插入第二条名字为lisi的数据报错了,因为被唯一索引限制插入。
- 普通索引:普通索引是最基本的索引,没有任何限制,相对于唯一索引,创建方式只需要去掉UNIQUE关键字。不做多余的解释了。
- 组合索引:两个或以上列的索引叫做组合索引。
ALTER TABLE user_info ADD INDEX gender_age(gender,age);
-- 同时可以根据create命令来创建索引
再次查询索引,可以看到我们重构创建了一个gender_age的组合索引。
- 全文索引:FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引和仅适用于 CHAR, VARCHAR和 TEXT列。需要注意,在MySQL5.5及以前只有MyISAM支持全文索引。
CREATE FULLTEXT INDEX fulltext_tel ON user_info(tel);
我们通过命令查看索引,可以看到全文索引的Index_type为FULLTEXT