mysql知识点整理-2

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