MySQL中的数据用各种不同的技术存储在文件中,这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。我的理解是,数据存储使用不同的数据结构,使得它们各自支持不同的技术和功能,因此划分成不同的存储引擎。而存储引擎是对应表的,所以也被称为表类型。
用以下指令可以查看MySQL中支持的存储引擎,挑几个整理一下知识。
- show engines;
+——————–+———+————————————————
—————-+————–+——+————+
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+——————–+———+————————————————
—————-+————–+——+————+
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to
it disappears) | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for tempor
ary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and f
oreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
+——————–+———+————————————————
—————-+————–+——+————+
9 rows in set (0.00 sec)
支持事务,行级锁,以及外键,拥有高并发处理能力。但是在创建索引和加载数据时,比MyISAM慢。
不支持事务和行级锁。所以速度很快,性能优秀。可以对整张表加锁,支持并发插入,支持全文索引。
支持Hash索引,内存表,Memory引擎将数据存储在内存中,表结构不是存储在内存中的,查询时不需要执行磁盘I/O操作,所以要比MyISAM和InnoDB快很多倍,但是数据库断电或是重启后,表中的数据将会丢失,表结构不会丢失。
索引是一种数据结构。索引本身很大,不可能全部存储在内存中,因此索引以索引表的形式存储在磁盘中。
并不能找到一个给定健值的具体行,B+树索引只能找到被查找数据行所在的页,然后从数据库将页读入内存,在内存中查找。
B+树索引可以分为聚集索引和辅助索引。聚簇索引是按照数据存放的逻辑地址为顺序的,而非聚簇索引就不一样了;聚簇索引能提高多行检索的速度,而非聚簇索引对于单行的检索很快。
动作描述 | 使用聚集索引 | 使用非聚集索引 |
---|---|---|
列经常被分组排序 | Y | Y |
一个或极少不同的值 | N | N |
返回某范围内的数据 | Y | N |
小数目的不同值 | Y | N |
大数目的不同值 | N | Y |
外键 | Y | Y |
主键 | Y | Y |
频繁更新的列 | N | Y |
频繁修改索引列 | N | Y |
在《数据库索引探索》中已经整理过Hash索引的知识,这里不再重复。
最普通的索引,没有任何限制。
- # 创建表的同时创建索引
- CREATE TABLE `artical` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `subject` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
- `time` Date NULL DEFAULT NULL,
- PRIMARY KEY(`id`),
- INDEX index_subject (subject)
- );
- # 直接创建索引
- CREATE INDEX <index_name> ON <table_name>(<column_name>);
- # 修改表结构的方式添加索引
- ALTER TABLE <table_name> ADD INDEX index_name (<column_name>);
实际上,最好的做法是创建表的时候创建索引,如果创建表之后再修改新建索引的话,对于聚集索引,会根据原来的表,创建一个新的表带有索引数据结构,再把原来的表删去,新创建的表改成原来的表的名字。而非聚集索引则是通过修改索引文件来完成。所以都是需要占用额外的资源来修改或新建索引的。
与普通索引的不同的是,索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
- # 创建表的时候直接指定
- CREATE TABLE `user` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `name` char(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- `tel` char(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- PRIMARY KEY(`id`),
- UNIQUE index_subject (name)
- );
- # 直接创建索引
- CREATE UNIQUE INDEX <index_name> ON <table_name>(<column_name>);
- # 修改表结构的方式添加索引
- ALTER TABLE <table_name> ADD UNIQUE index_name (<column_name>);
索引值必须唯一,不能为NULL,在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位。
MySQL从3.23.23版开始支持全文索引和全文检索,FULLTEXT索引仅可用于 MyISAM 表;他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建,或是随后使用ALTER TABLE 或CREATE INDEX被添加。
- # 创建表的时候添加全文索引
- CREATE TABLE `artical` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `subject` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
- `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
- `time` Date NULL DEFAULT NULL,
- PRIMARY KEY(`id`),
- FULLTEXT (content)
- )engine=MyISAM;
- # 修改表结构添加全文索引
- ALTER TABLE artical ADD FULLTEXT INDEX index_content(content);
- # 直接创建索引
- CREATE FULLTEXT INDEX index_content ON artical(content);
多个单列索引与单个多列索引的查询效果不同,因为执行查询时,MySQL只能使用一个索引,会从多个索引中选择一个限制最为严格的索引。
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引:ALTER TABLE article ADD INDEX index_titme_time (subject,title(50),time(10)),实际上包含三个索引(subject),(subject, title), (subject, title, time)。
在使用查询的时候遵循“最左前缀”:
,
- c1
,
- c2
),使用where c2 = “aaa” and c3 = “bbb”不能使用索引
- c3
,
- c1
,
- c2
),where c1 = “xxx” and c2 like = “aa%” and c3 = “sss”查询只会使用索引的前两列,因为like是范围查询
- c3
来源: