MySql“存储引擎”与操作系统的“文件系统”文件系统数据在磁盘上如何存储和管理存储引擎数据在数据库层面应该如何存储和管理基本数据存、取数据库事务、锁、数据备份、恢复、优化...决定存储方式库相当于目录没有指定存储引擎InnoDB数据存在.ibd文件中聚簇索引结构数据和主键索引绑定默认存储引擎MyISAM 数据存在.MYD文件索引存在.MYI文件数据和索引分离CSV以逗号为字段分隔符的纯文本文件*.csv (Excel可以直接存为csv格式文件直接导入到mysql)存储引擎InnoDB 存储引擎默认1、支持完整的事务 可通过COMMIT/ROLLBACK回滚适合金融、订单等核心业务银行转账A -B(A-100,A流水,B100,B流水)出错先回滚 -提醒2、行级锁表级锁 支持行级锁只锁修改的行并发读写效率高比如 100 人同时改不同订单互不影响3、支持外键约束 且崩溃后可通过 redo/undo日志恢复数据保证数据不丢失适用场景 99% 的业务场景电商、金融、社交MyISAM 存储引擎1、高性能读操作在执行简单查询时速度通常比InnoDB快因为它没有事务和行级锁带来的额外开销2、表级锁 只有表级锁改一行锁整张表并发写时性能极差比如改 1 行数据整张表都不能读 / 写3、不支持外键约束和事务处理 崩溃后可能出现数据损坏需手动修复myisamchk工具适应场景以查询为主的系统仓库系统文本类型的常用命令show engines;SELECT DEFAULT_STORAGE_ENGINE;SHOW TABLE status like course\G; //查看表状态SELECT engine FROM tables GROUP BY engine; //查询当前 MySQL 实例中所有表使用的存储引擎并按引擎类型分组展示指定该表使用 MyISAM 存储引擎.MYD存储表的数据内容.MYI存储表的索引信息.sdi文件 表结构元数据文件MySql索引索引 - 目录 - 通过目录信息帮助快速找到想要的数据如果没有索引查找数据 全表扫描优1、提升查询速度2、降低磁盘I/O次数 按索引字段对数据进行排序3、有助于服务器避免进行排序和使用临时表缺1、增加存储成本2、降低写入性能写入数据时同步要更新索引数据3、索引可能失效id,WHERE price 没用上索引的底层结构二叉搜索树1、每个节点最多有2个子节点2、任何节点左子节点都小于当前节点右子节点都大于当前节点二叉搜索树能够有效地提升查找效率但是在某些情况下树可能变成链表层级非常多无法提升查询效率只有左子树或只有右子树这种情况树不平衡AVL树平衡二叉树1、每个节点最多有2个子节点2、任何节点左子节点都小于当前节点右子节点都大于当前节点3、每个节点的左右字数的高度差不能超过1会自动调整数据量如果特别大时BTree找一种单节点可以存储多个键值数据的平衡树1、每个节点都可以有多个子节点m2Btree当max-degree3B树B-树节点存储内容内部节点和叶子都存储数据叶子节点结构叶子节点没有直接连接插入删除数据可能影响多个层级的节点使用场景精确查询不适合范围查询B树节点存储内容内部节点内部只存储键值叶子节点存储数据叶子节点结构叶子节点形成链表支持快速遍历插入删除数据主要集中在叶子节点稳定性好使用场景精确查找范围查询性能都很好创建索引--create index 索引名 ON 表名 (列信息);主键索引创建主键唯一索引创建UNIQUE普通索引创建表时创建联合索引多列索引CREATE TABLE 表名(字段1 字段类型 ...,字段2 字段类型 ...,INDEXindex_name 字段信息)create table test_index( tid INT NOT NULL, username CHAR(4), age INT, INDEX idx_age (age) );给test_index表的age字段创建名为idx_age的普通索引表已经创建好了需要再增加索引1、create index 索引名 ON 表名 (列信息);create table test_index2(tid INT NOT NULL, username CHAR(4), age INT);createindex idx_age ontest_index2(age);2、alter table 表名 add index 索引名(字段);alter table test_index2add indexidx_username(username);show create table test_index2\G;注一般建议创建表的时候将索引创建好如果表中的数量特别多时创建索引会消耗很多时间创建唯一索引create unique index 索引名 on 表名 (字段);show create table test_index2\G;alter table 表名 add unique index 索引名(索引字段);注添加unique index时注意如果数据库中有数据时需要确保字段的值不能有重复否则会创建失败主键索引 没有create创建ALTER TABLE 表名 ADD PRIMARY KEY 索引名(索引字段)create table test_index3(tid INT NOT NULL, username CHAR(4), age INT);alter table test_index3 add primary key idx_tid(tid);多列索引create index idx_name on table_name (列1,列2...)alter table table_name add index idx_name(列1列2...)由多列组成的索引在查询的时候需要将第1列索引作为查询字段例create index idx_pname_price on product (pname,price);查看方式1、show create table product\G;2、show index from product;使用索引创建索引时如果数据库中有数据确保数据符合索引要求查询时条件查询条件参数必须包含第一个索引字段(使用EXPLAN、SELECT语句)EXPLAIN是 MySQL 提供的 “索引诊断工具”能直观告诉你 SQL 语句是否用到了索引、用了哪个索引、以及索引的使用效率如何。执行EXPLAIN SQL后 直接反映索引的使用状态EXPLAIN SELECT * FROM product WHERE pid 1;删除索引--drop index index_name on table_name;drop index index_name on table_name;drop index idx_pname_price on product;MyISAM存储引擎 以读为主的数据库从MySql5.6版本开始Innodb和MyISAM都支持创建全文索引 FULLTEXT INDEX(仅适用于文本类型字段 CHAR/VARCHAR/TEXT)create fulltext index idx_name on table_name(col);alter table table_name add fultext index idx_name(col);create table test_index5(tid INT NOT NULL, username CHAR(4), age INT) ENGINEMyISAM;create fulltext index idx_name on test_index5(name);验证现有索引show index from product;
MySql存储引擎与索引
MySql“存储引擎”与操作系统的“文件系统”文件系统数据在磁盘上如何存储和管理存储引擎数据在数据库层面应该如何存储和管理基本数据存、取数据库事务、锁、数据备份、恢复、优化...决定存储方式库相当于目录没有指定存储引擎InnoDB数据存在.ibd文件中聚簇索引结构数据和主键索引绑定默认存储引擎MyISAM 数据存在.MYD文件索引存在.MYI文件数据和索引分离CSV以逗号为字段分隔符的纯文本文件*.csv (Excel可以直接存为csv格式文件直接导入到mysql)存储引擎InnoDB 存储引擎默认1、支持完整的事务 可通过COMMIT/ROLLBACK回滚适合金融、订单等核心业务银行转账A -B(A-100,A流水,B100,B流水)出错先回滚 -提醒2、行级锁表级锁 支持行级锁只锁修改的行并发读写效率高比如 100 人同时改不同订单互不影响3、支持外键约束 且崩溃后可通过 redo/undo日志恢复数据保证数据不丢失适用场景 99% 的业务场景电商、金融、社交MyISAM 存储引擎1、高性能读操作在执行简单查询时速度通常比InnoDB快因为它没有事务和行级锁带来的额外开销2、表级锁 只有表级锁改一行锁整张表并发写时性能极差比如改 1 行数据整张表都不能读 / 写3、不支持外键约束和事务处理 崩溃后可能出现数据损坏需手动修复myisamchk工具适应场景以查询为主的系统仓库系统文本类型的常用命令show engines;SELECT DEFAULT_STORAGE_ENGINE;SHOW TABLE status like course\G; //查看表状态SELECT engine FROM tables GROUP BY engine; //查询当前 MySQL 实例中所有表使用的存储引擎并按引擎类型分组展示指定该表使用 MyISAM 存储引擎.MYD存储表的数据内容.MYI存储表的索引信息.sdi文件 表结构元数据文件MySql索引索引 - 目录 - 通过目录信息帮助快速找到想要的数据如果没有索引查找数据 全表扫描优1、提升查询速度2、降低磁盘I/O次数 按索引字段对数据进行排序3、有助于服务器避免进行排序和使用临时表缺1、增加存储成本2、降低写入性能写入数据时同步要更新索引数据3、索引可能失效id,WHERE price 没用上索引的底层结构二叉搜索树1、每个节点最多有2个子节点2、任何节点左子节点都小于当前节点右子节点都大于当前节点二叉搜索树能够有效地提升查找效率但是在某些情况下树可能变成链表层级非常多无法提升查询效率只有左子树或只有右子树这种情况树不平衡AVL树平衡二叉树1、每个节点最多有2个子节点2、任何节点左子节点都小于当前节点右子节点都大于当前节点3、每个节点的左右字数的高度差不能超过1会自动调整数据量如果特别大时BTree找一种单节点可以存储多个键值数据的平衡树1、每个节点都可以有多个子节点m2Btree当max-degree3B树B-树节点存储内容内部节点和叶子都存储数据叶子节点结构叶子节点没有直接连接插入删除数据可能影响多个层级的节点使用场景精确查询不适合范围查询B树节点存储内容内部节点内部只存储键值叶子节点存储数据叶子节点结构叶子节点形成链表支持快速遍历插入删除数据主要集中在叶子节点稳定性好使用场景精确查找范围查询性能都很好创建索引--create index 索引名 ON 表名 (列信息);主键索引创建主键唯一索引创建UNIQUE普通索引创建表时创建联合索引多列索引CREATE TABLE 表名(字段1 字段类型 ...,字段2 字段类型 ...,INDEXindex_name 字段信息)create table test_index( tid INT NOT NULL, username CHAR(4), age INT, INDEX idx_age (age) );给test_index表的age字段创建名为idx_age的普通索引表已经创建好了需要再增加索引1、create index 索引名 ON 表名 (列信息);create table test_index2(tid INT NOT NULL, username CHAR(4), age INT);createindex idx_age ontest_index2(age);2、alter table 表名 add index 索引名(字段);alter table test_index2add indexidx_username(username);show create table test_index2\G;注一般建议创建表的时候将索引创建好如果表中的数量特别多时创建索引会消耗很多时间创建唯一索引create unique index 索引名 on 表名 (字段);show create table test_index2\G;alter table 表名 add unique index 索引名(索引字段);注添加unique index时注意如果数据库中有数据时需要确保字段的值不能有重复否则会创建失败主键索引 没有create创建ALTER TABLE 表名 ADD PRIMARY KEY 索引名(索引字段)create table test_index3(tid INT NOT NULL, username CHAR(4), age INT);alter table test_index3 add primary key idx_tid(tid);多列索引create index idx_name on table_name (列1,列2...)alter table table_name add index idx_name(列1列2...)由多列组成的索引在查询的时候需要将第1列索引作为查询字段例create index idx_pname_price on product (pname,price);查看方式1、show create table product\G;2、show index from product;使用索引创建索引时如果数据库中有数据确保数据符合索引要求查询时条件查询条件参数必须包含第一个索引字段(使用EXPLAN、SELECT语句)EXPLAIN是 MySQL 提供的 “索引诊断工具”能直观告诉你 SQL 语句是否用到了索引、用了哪个索引、以及索引的使用效率如何。执行EXPLAIN SQL后 直接反映索引的使用状态EXPLAIN SELECT * FROM product WHERE pid 1;删除索引--drop index index_name on table_name;drop index index_name on table_name;drop index idx_pname_price on product;MyISAM存储引擎 以读为主的数据库从MySql5.6版本开始Innodb和MyISAM都支持创建全文索引 FULLTEXT INDEX(仅适用于文本类型字段 CHAR/VARCHAR/TEXT)create fulltext index idx_name on table_name(col);alter table table_name add fultext index idx_name(col);create table test_index5(tid INT NOT NULL, username CHAR(4), age INT) ENGINEMyISAM;create fulltext index idx_name on test_index5(name);验证现有索引show index from product;
