MySQL存储引擎

-- 查询数据库支持的引擎
SHOW ENGINES;
-- 表含义:
  - support : 指服务器是否支持该存储引擎
  - transactions : 指存储引擎是否支持事务
  - XA : 指存储引擎是否支持分布式事务处理
  - Savepoints : 指存储引擎是否支持保存点
-- 查询某个数据库中所有数据表的引擎
SHOW TABLE STATUS FROM 数据库名称;
-- 查询某个数据库中某个数据表的引擎
SHOW TABLE STATUS FROM 数据库名称 WHERE NAME = '数据表名称';
-- 创建数据表，指定存储引擎
CREATE TABLE 表名(
    列名,数据类型,
    ...
)ENGINE = 引擎名称;
-- 修改表的存储引擎
ALTER TABLE 表名 ENGINE = 引擎名称;

索引的分类

• 功能分类
  • 普通索引： 最基本的索引，它没有任何限制。
  • 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值组合必须唯一。
  • 主键索引：一种特殊的唯一索引，不允许有空值。一般在建表时同时创建主键索引。
  • 组合索引：顾名思义，就是将单列索引进行组合。
  • 外键索引：只有InnoDB引擎支持外键索引，用来保证数据的一致性、完整性和实现级联操作。
  • 全文索引：快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
• 结构分类
  • B+Tree索引 ：MySQL使用最频繁的一个索引数据结构，是InnoDB和MyISAM存储引擎默认的索引类型。
  • Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
    ```sql — 创建索引 CREATE [UNIQUE|FULLTEXT] INDEX 索引名称 [USING 索引类型] — 默认是B+TREE ON 表名(列名…);

— 查看索引 SHOW INDEX FROM 表名;

— 添加索引 — 普通索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名);

— 组合索引 ALTER TABLE 表名 ADD INDEX 索引名称(列名1,列名2,…);

— 主键索引 ALTER TABLE 表名 ADD PRIMARY KEY(主键列名);

— 外键索引(添加外键约束，就是外键索引) ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名);

— 唯一索引 ALTER TABLE 表名 ADD UNIQUE 索引名称(列名);

— 全文索引(mysql只支持文本类型) ALTER TABLE 表名 ADD FULLTEXT 索引名称(列名);

— 删除索引 DROP INDEX 索引名称 ON 表名;

— 删除student表中的idx_score索引 DROP INDEX idx_score ON student; ```

创建索引时的原则

• 对查询频次较高，且数据量比较大的表建立索引。
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升MySQL访问索引的I/O效率。
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。