logo头像
Snippet 博客主题

【MySQL高级实战】-索引与存储引擎学习指南

1. 索引

1.1 索引概述

打开官方索引文档,可以看到MySQL官方对索引的描述:

The best way to improve the performance of SELECT operations is to create indexes on one or more of the columns that are tested in the query. The index entries act like pointers to the table rows, allowing the query to quickly determine which rows match a condition in the WHERE clause, and retrieve the other column values for those rows. All MySQL data types can be indexed.

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

开启翻译模式,大致意思为:

改善SELECT操作性能的最好方法是在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针,从而使查询可以快速确定哪些行与WHERE子句中的条件匹配,并检索这些行的其他列值。所有MySQL数据类型都可以建立索引。

尽管可能会为查询中使用的每个可能的列创建索引,但是不必要的索引会浪费空间和时间,使MySQL难以确定要使用的索引。索引还会增加插入,更新和删除的成本,因为必须更新每个索引。您必须找到适当的平衡,才能使用最佳索引集来实现快速查询。

从上述文档我们可以总结出以下几点:

  • 索引可以一定程度上解决查询性能问题。
  • 索引项类似于指向表记录的指针,可以快速定位数据。
  • MySQL所有的数据类型都是可以建索引。
  • 索引对表的更新(INSERT、UPDATE、DELETE)操作不友好,增加表的更新成本。

1.2 索引优劣势

1.2.1 优势

  • 大大提高了数据检索的效率,降低数据库的IO成本。
  • 通过索引列对数据进行排序,降低数据排序的时间成本。
  • 可创建唯一性索引,保证数据库表中的每一行数据的唯一性。
  • 可以加速表和表之间的连接,实现数据完整性。

1.2.2 劣势

  • 索引需要占用物理空间。
  • 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
  • 对表中的数据进行更新(INSERT、UPDATE和DELETE)操作的时候,索引也要动态的维护,降低了数据的维护速度。

1.3 索引底层结构

1.3.1 索引与数据结构

索引(index)是帮助MySQL高效获取数据的有序数据结构。

在数据之外,数据库系统还维护者满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数
据结构上实现高级查找算法,这种数据结构就是索引。

我们都知道二叉搜索树(Binary Search Tree,BST)查询效率很高,这里先举个例子来阐述索引与数据结构的关系,话不多说,直接上图:

image-20200730163252519

图1是没有建立索引的数据表,最左边以0x开始的十六进制表示数据记录的物理地址,需要注意的是逻辑上相邻的记录在磁盘上并不一定物理相连。

图2是为了加快Col2列的查找,将图1的数据表中的数据映射到二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找快速获取到相应数据。

图1的数据表没有建立索引,会将表顺序扫描所有记录行,想要查询Col2=34的数据,会很快找到,但是想要查询Col2=3的数据需要全表扫描7次才能找到。试想一下,如果千万量级的数据量这样查询,需要扫描N多个记录行,效率极其低效。

如果是图2建立索引,索引与记录维护(顺序存储)在一棵二叉搜索树,会大大提升查询效率。我们都知道二叉树都是有序的。比根节点小的数据放在左子树的左子节点,比根节点大的数据放在右子树的右子节点。想要查询Col2=34的数据,也会很快找到,想要查询Col2=3的数据,只需要3次即可定位到想要的数据。这样做的本质就是用(额外的索引存储)空间换(查询速度)时间,空间复杂度高了,但是时间复杂度低了。

我们知道二叉搜索树是不平衡树,存放节点也非常的少,面对百万级甚至千万级的表数据显得力不从心,因此MySQL选用B+树来作为索引的存储结构。

通过这个例子,可以利用已有的数据结构来优化数据表的查询。一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

1.3.2 B树

B Tree又叫多路平衡搜索树,一颗m阶的BTree特性如下 :

  • 树中每个节点最多包含m个孩子。
  • 除根节点与叶子节点外,每个节点至少有[ceil(m/2)]个孩子。
  • 若根节点不是叶子节点,则至少有两个孩子,除非树只包含一个节点。
  • 所有的叶子节点都在同一层。
  • 每个非叶子节点由n个key与n+1个指针组成,其中[ceil(m/2)-1] <= n <= m-1,ceil表示向上取整 。

以5阶B Tree为例,key的数量:公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时,中间节点分裂到
父节点,两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。 演变过程如下:

  1. 插入前4个字母 C N G A ,排序并插入。

    image-20200731143321898

  2. 插入H,n>4,中间元素G字母向上分裂到新的节点。

    image-20200731143445542

  3. 插入E,K,Q不需要分裂。

    image-20200731143547236

  4. 插入M,中间元素M字母向上分裂到父节点G 。

    image-20200731143707850

  5. 插入F,W,L,T不需要分裂 。

    image-20200731143735813

  6. 插入Z,中间元素T向上分裂到父节点中 。

    image-20200731143827771

  7. 插入D,中间元素D向上分裂到父节点中。然后插入P,R,X,Y不需要分裂。

    image-20200731143852737

  8. 最后插入S,NPQR节点n>5,中间节点Q向上分裂,但分裂后父节点DGMT的n>5,中间节点M向上分裂。

    image-20200731143913548

到此,该B Tree树就已经构建完成了, B Tree树和二叉树相比,查询数据的效率更高,因为对于相同的数据量来说,BTREE的层级结构比二叉树小,因此搜索速度快。

1.3.3 B+树

B+Tree为BTree的变种,B+Tree与BTree的区别为:

  • n阶B+Tree最多含有n个key,而B Tree最多含有n-1个key。
  • B+Tree的叶子节点保存所有的key信息,依key大小顺序排列。
  • 所有的非叶子节点都可以看作是key的索引部分。

image-20200731144325332

由于B+Tree只有叶子节点保存key信息,查询任何key都要从root走到叶子。所以B+Tree的查询效率更加稳定。

1.3.4 MySQL B+树

MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指
针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。

MySQL中的 B+Tree 索引结构示意图如下:

image-20200730170248368

1.4 索引分类

1.4.1 聚簇索引

数据和索引存储到一起,找到索引就获取到了数据。聚簇索引是唯一的,InnoDB一定会有一个聚簇索引来保存数据。

如果一个表中的普通列创建了索引,那么叶子节点中存放的值是聚簇索引的key值。

InnoDB聚簇索引选择顺序:

  1. 默认选择主键;
  2. 没有主机,选择唯一的非空索引;
  3. 都没有,则隐式定义一个主键rowId;

1.4.2 非聚簇索引

数据存储和索引分开,叶子节点存储对应的行,需要二次查找,通常称为二级索引或辅助索引;非聚簇索引一定存储有聚簇索引的列值。

1.4.3 单值索引

即一个索引只包含单个列,一个表可以有多个单列索引 。

1.4.4 唯一索引

索引列的值必须唯一,但允许有空值 。

1.4.5 复合索引

一个索引包含多个列 。

1.5 索引语法

1.5.1 环境准备

索引在创建表的时候,可以同时创建, 也可以随时增加新的索引。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
create database index_demo default charset=utf8mb4;

use index_demo;

CREATE TABLE `city` (
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `country` (
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into `city` (`city_id`, `city_name`, `country_id`) values(1,'西安',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(2,'NewYork',2);
insert into `city` (`city_id`, `city_name`, `country_id`) values(3,'北京',1);
insert into `city` (`city_id`, `city_name`, `country_id`) values(4,'上海',1);

insert into `country` (`country_id`, `country_name`) values(1,'China');
insert into `country` (`country_id`, `country_name`) values(2,'America');
insert into `country` (`country_id`, `country_name`) values(3,'Japan');
insert into `country` (`country_id`, `country_name`) values(4,'UK');

1.5.2 建立索引

语法:

1
2
3
4
5
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

示例 : 为city表中的city_name字段创建索引

1
2
3
mysql> create index idx_city_name on city(city_name);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0

1.5.3 查看索引

语法:

1
show index from table_name;

示例: 查看city表的索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
mysql> show index from city\G;
*************************** 1. row ***************************
Table: city
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: city_id
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: city
Non_unique: 1
Key_name: idx_city_name
Seq_in_index: 1
Column_name: city_name
Collation: A
Cardinality: 4
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.01 sec)

1.5.4 删除索引

语法 :

1
DROP INDEX index_name ON tbl_name;

示例 : 想要删除city表上的索引idx_city_name,可以操作如下:

1
2
3
mysql> drop index idx_city_name on city;
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0

1.5.5 ALTER命令操作索引

1
2
3
4
5
6
7
8
-- 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL
alter table tb_name add primary key(column_list);
-- 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)
alter table tb_name add unique index_name(column_list);
-- 添加普通索引, 索引值可以出现多次。
alter table tb_name add index index_name(column_list);
-- 该语句指定了索引为FULLTEXT, 用于全文索引
alter table tb_name add fulltext index_name(column_list);

1.6 索引设计原则

索引的设计可以遵循一些已有的原则,创建索引的时候请尽量考虑符合这些原则,便于提升索引的使用效率,更高效的使用索引。

  • 对查询频次较高,且数据量比较大的表建立索引。

  • 索引字段的选择,最佳候选列应当从where子句的条件中提取,如果where子句中的组合比较多,那么应当挑选最常用、过滤效果最好的列的组合。

  • 使用唯一索引,区分度越高,使用索引的效率越高。

  • 索引可以有效的提升查询数据的效率,但索引数量不是多多益善,索引越多,维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说,索引过多,会引入相当高的维护代价,降低DML操作的效率,增加相应操作的时间消耗。另外索引过多的话,MySQL也会犯选择困难病,虽然最终仍然会找到一个可用的索引,但无疑提高了选择的代价。

  • 使用短索引,索引创建之后也是使用硬盘来存储的,因此提升索引访问的I/O效率,也可以提升总体的访问效率。假如构成索引的字段总长度比较短,那么在给定大小的存储块内可以存储更多的索引值,相应的可以有效的提升MySQL访问索引的I/O效率。

  • 利用最左前缀,N个列组合而成的组合索引,那么相当于是创建了N个索引,如果查询时where子句中使用了组成该索引的前几个字段,那么这条查询SQL可以利用组合索引来提升查询效率。如下创建复合索引,相当于对name创建索引、对name , email创建了索引、对name , email, status创建了索引。

    1
    CREATE INDEX idx_name_email_status ON tb_user(name,email,status);

2. 存储引擎

2.1 存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。

存储引擎就是存储数据,建立索引,更新查询数据等等技术的实现方式 。存储引擎是基于表的,而不是基于库的。所以存储引擎也可被称为表类型。

Oracle,SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎,可以根据需要使用相应引擎,或者编写存储引擎。

MySQL5.0支持的存储引擎包含 : InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等,其中InnoDB和BDB提供事务安全表,其他存储引擎是非事务安全表。

可以通过指定 show engines , 来查询当前数据库支持的存储引擎 :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

创建新表时如果不指定存储引擎,那么系统就会使用默认的存储引擎,MySQL5.5之前的默认存储引擎是MyISAM,5.5之后就改为了InnoDB。 查看Mysql数据库默认的存储引擎 ,执行如下指令 :

1
2
3
4
5
6
7
8
9
10
mysql> show variables like '%storage_engine%';
+---------------------------------+-----------+
| Variable_name | Value |
+---------------------------------+-----------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| disabled_storage_engines | |
| internal_tmp_mem_storage_engine | TempTable |
+---------------------------------+-----------+
4 rows in set (0.01 sec)

2.2 不同存储引擎的区别

特点 InnoDB MyISAM MEMORY MERGE NDB
存储限制 64TB 没有
事务安全 支持
锁机制 行锁(适合高并发) 表锁 表锁 表锁 行锁
B树索引 支持 支持 支持 支持 支持
哈希索引 支持
全文索引 支持(5.6版本之后) 支持
集群索引 支持
数据索引 支持 支持 支持
索引缓存 支持 支持 支持 支持 支持
数据可压缩 支持
空间使用 N/A
内存使用 中等
批量插入速度
支持外键 支持

2.3 存储引擎的选择原则

在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

  • InnoDB : 是Mysql的默认存储引擎,用于事务处理应用程序,支持外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询意外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定, 还可以确保事务的完整提交和回滚,对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB是最合适的选择。
  • MyISAM : 如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
  • MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供几块的访问。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。
  • MERGE:用于将一系列等同的MyISAM表以逻辑方式组合在一起,并作为一个对象引用他们。MERGE表的优点在于可以突破对单个MyISAM表的大小限制,并且通过将不同的表分布在多个磁盘上,可以有效的改善MERGE表的访问效率。这对于存储诸如数据仓储等VLDB环境十分合适。

2.4 常见的存储引擎

2.4.1 InnoDB

InnoDB存储引擎是Mysql的默认存储引擎。InnoDB存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎,InnoDB写的处理效率差一些,并且会占用更多的磁盘空间以保留数据和索引。

  • 事务控制

    1
    2
    3
    4
    5
    create table goods_innodb(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
    )ENGINE=innodb DEFAULT CHARSET=utf8mb4;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into goods_innodb(id,name)values(null,'华为Mate 30 Pro');
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from goods_innodb; -- 当前客户端A可以查到自己未提交的事务数据
    +----+-------------------+
    | id | name |
    +----+-------------------+
    | 1 | 华为Mate 30 Pro |
    +----+-------------------+
    1 row in set (0.00 sec)

    mysql> select * from goods_innodb; -- 重新启动另一个客户端B进行查询,其他事务未提交的数据看不到
    Empty set (0.00 sec)

    mysql> commit; -- 当前客户端A事务提交
    Query OK, 0 rows affected (0.04 sec)

    mysql> select * from goods_innodb; -- 重新在客户端B进行查询,就可以查询到客户端A提交的数据
    +----+-------------------+
    | id | name |
    +----+-------------------+
    | 1 | 华为Mate 30 Pro |
    +----+-------------------+
    1 row in set (0.00 sec)
  • 外键约束

    MySQL支持外键的存储引擎只有InnoDB , 在创建外键的时候, 要求父表必须有对应的索引,子表在创建外键的时候,也会自动的创建对应的索引。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    create table country_innodb(   -- 主表
    country_id int NOT NULL AUTO_INCREMENT, -- 主键索引
    country_name varchar(100) NOT NULL,
    primary key(country_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    create table city_innodb( -- 子表
    city_id int NOT NULL AUTO_INCREMENT,
    city_name varchar(50) NOT NULL,
    country_id int NOT NULL,
    primary key(city_id),
    key idx_fk_country_id(country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES -- 外键
    country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
    )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

    insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
    insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'ShangHai',1);

    mysql> show create table city_innodb\G; -- 查看city_innodb表结构
    *************************** 1. row ***************************
    Table: city_innodb
    Create Table: CREATE TABLE `city_innodb` (
    `city_id` int NOT NULL AUTO_INCREMENT,
    `city_name` varchar(50) NOT NULL,
    `country_id` int NOT NULL,
    PRIMARY KEY (`city_id`),
    KEY `idx_fk_country_id` (`country_id`),
    CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    1 row in set (0.00 sec)

    在创建索引时,可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT、CASCADE、SET NULL和NO ACTION。

    RESTRICT和NO ACTION相同, 是指限制在子表有关联记录的情况下, 父表不能更新;

    CASCADE表示父表在更新或者删除时,更新或者删除子表对应的记录;

    SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL 。

    针对上面创建的两个表, 子表的外键指定是ON DELETE RESTRICT ON UPDATE CASCADE 方式的, 那么在主表删除记录的时候, 如果子表有对应记录, 则不允许删除, 主表在更新记录的时候, 如果子表有对应记录, 则子表对应更新 。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    mysql> select * from country_innodb; -- country_innodb所有记录
    +------------+--------------+
    | country_id | country_name |
    +------------+--------------+
    | 1 | China |
    | 2 | America |
    | 3 | Japan |
    +------------+--------------+
    3 rows in set (0.00 sec)

    mysql> select * from city_innodb; -- city_innodb所有记录
    +---------+-----------+------------+
    | city_id | city_name | country_id |
    +---------+-----------+------------+
    | 1 | Xian | 1 |
    | 2 | NewYork | 2 |
    | 3 | ShangHai | 1 |
    +---------+-----------+------------+
    3 rows in set (0.00 sec)

删除country_id为1的country数据会删除失败,原因是有外键的限制。

1
2
mysql> delete from country_innodb where country_id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`index_demo`.`city_innodb`, CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country_innodb` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE)

更新主表country表的字段country_id ,同时也会将city_innodb表的外键字段country_id更新。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
mysql> select * from country_innodb where country_id = 1; -- 查询country_id = 1的记录
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 1 | China |
+------------+--------------+
1 row in set (0.00 sec)

mysql> select * from city_innodb where country_id = 1; -- 查询city_innodb且外键city_innodb=1的记录
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
| 1 | Xian | 1 |
| 3 | ShangHai | 1 |
+---------+-----------+------------+
2 rows in set (0.00 sec)

mysql> update country_innodb set country_id = 100 where country_id = 1; -- 更新country_id = 1记录的主键为100
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from country_innodb where country_id = 100; -- 查看是否更新成功
+------------+--------------+
| country_id | country_name |
+------------+--------------+
| 100 | China |
+------------+--------------+
1 row in set (0.00 sec)

mysql> select * from city_innodb; -- 查询外键是否也更新成功
+---------+-----------+------------+
| city_id | city_name | country_id |
+---------+-----------+------------+
| 1 | Xian | 100 |
| 2 | NewYork | 2 |
| 3 | ShangHai | 100 |
+---------+-----------+------------+
3 rows in set (0.00 sec)
  • 存储方式

    nnoDB 存储表和索引有以下两种方式 :

    ①. 使用共享表空间存储, 这种方式创建的表的表结构保存在.frm文件中, 数据和索引保存innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。

    ②. 使用多表空间存储, 这种方式创建的表的表结构仍然存在 .frm 文件中,但是每个表的数据和索引单独保存在.ibd 中。
    ③. MySQL8开始删除了原来的frm文件,并采用 Serialized Dictionary Information (SDI), 是MySQL8.0重新设计数据字典后引入的新产物,并开始已经统一使用InnoDB存储引擎来存储表的元数据信息。SDI信息源记录保存在ibd文件中。 如何可以查看表结构信息,官方提供了一个工具叫做ibd2sdi,可以离线的将ibd文件中的冗余存储的sdi信息提取出来,并以json的格式输出到终端。

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    135
    136
    137
    138
    139
    140
    141
    142
    143
    144
    145
    146
    147
    148
    149
    150
    151
    152
    153
    154
    155
    156
    157
    158
    159
    160
    161
    162
    163
    164
    165
    166
    167
    168
    169
    170
    171
    172
    173
    174
    175
    176
    177
    178
    179
    180
    181
    182
    183
    184
    185
    186
    187
    188
    189
    190
    191
    192
    193
    194
    195
    196
    197
    198
    199
    200
    201
    202
    203
    204
    205
    206
    207
    208
    209
    210
    211
    212
    213
    214
    215
    216
    217
    218
    219
    220
    221
    222
    223
    224
    225
    226
    227
    228
    229
    230
    231
    232
    233
    234
    235
    236
    237
    238
    239
    240
    241
    242
    243
    244
    245
    246
    247
    248
    249
    250
    251
    252
    253
    254
    255
    256
    257
    258
    259
    260
    261
    262
    263
    264
    265
    266
    267
    268
    269
    270
    271
    272
    273
    274
    275
    276
    277
    278
    279
    280
    281
    282
    283
    284
    285
    286
    287
    288
    289
    290
    291
    292
    293
    294
    295
    296
    297
    298
    299
    300
    301
    302
    303
    304
    305
    306
    307
    308
    309
    310
    311
    312
    313
    314
    315
    316
    317
    318
    319
    320
    321
    322
    323
    324
    325
    326
    327
    328
    329
    330
    331
    332
    333
    334
    335
    336
    337
    338
    339
    340
    341
    342
    343
    344
    345
    346
    347
    348
    349
    350
    351
    352
    353
    354
    355
    356
    357
    358
    359
    360
    361
    362
    363
    364
    365
    366
    367
    368
    369
    370
    371
    372
    373
    374
    375
    376
    377
    378
    379
    380
      [root@litong ~]# ls /data/mysql/mall # mysql8以下的存储方式,frm文件存放表的元数据:表结构,ibd存放数据和索引信息
    city.frm city.frm country.ibd country.frm

    [root@litong master]# ll data/index_demo/ # mysql8已经删除了frm文件,只有ibd文件
    总用量 480
    -rw-r----- 1 root root 131072 7月 31 15:39 city.ibd
    -rw-r----- 1 root root 131072 8月 3 10:43 city_innodb.ibd
    -rw-r----- 1 root root 114688 7月 31 15:26 country.ibd
    -rw-r----- 1 root root 114688 8月 3 10:43 country_innodb.ibd
    -rw-r----- 1 root root 114688 8月 3 10:23 goods_innodb.ibd
    [root@litong data]# ibd2sdi index_demo/city_innodb.ibd # 通过ibd2sdi工具输出sdi信息
    ["ibd2sdi"
    ,
    {
    "type": 1,
    "id": 365,
    "object":
    {
    "mysqld_version_id": 80021,
    "dd_version": 80021,
    "sdi_version": 80019,
    "dd_object_type": "Table",
    "dd_object": {
    "name": "city_innodb",
    "mysql_version_id": 80021,
    "created": 20200803023043,
    "last_altered": 20200803023043,
    "hidden": 1,
    "options": "avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;",
    "columns": [
    {
    "name": "city_id",
    "type": 4,
    "is_nullable": false,
    "is_zerofill": false,
    "is_unsigned": false,
    "is_auto_increment": true,
    "is_virtual": false,
    "hidden": 1,
    "ordinal_position": 1,
    "char_length": 11,
    "numeric_precision": 10,
    "numeric_scale": 0,
    "numeric_scale_null": false,
    "datetime_precision": 0,
    "datetime_precision_null": 1,
    "has_no_default": false,
    "default_value_null": false,
    "srs_id_null": true,
    "srs_id": 0,
    "default_value": "AAAAAA==",
    "default_value_utf8_null": true,
    "default_value_utf8": "",
    "default_option": "",
    "update_option": "",
    "comment": "",
    "generation_expression": "",
    "generation_expression_utf8": "",
    "options": "interval_count=0;",
    "se_private_data": "table_id=1070;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "column_key": 2,
    "column_type_utf8": "int",
    "elements": [],
    "collation_id": 255,
    "is_explicit_collation": false
    },
    {
    "name": "city_name",
    "type": 16,
    "is_nullable": false,
    "is_zerofill": false,
    "is_unsigned": false,
    "is_auto_increment": false,
    "is_virtual": false,
    "hidden": 1,
    "ordinal_position": 2,
    "char_length": 200,
    "numeric_precision": 0,
    "numeric_scale": 0,
    "numeric_scale_null": true,
    "datetime_precision": 0,
    "datetime_precision_null": 1,
    "has_no_default": true,
    "default_value_null": false,
    "srs_id_null": true,
    "srs_id": 0,
    "default_value": "AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA\nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA",
    "default_value_utf8_null": true,
    "default_value_utf8": "",
    "default_option": "",
    "update_option": "",
    "comment": "",
    "generation_expression": "",
    "generation_expression_utf8": "",
    "options": "interval_count=0;",
    "se_private_data": "table_id=1070;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "column_key": 1,
    "column_type_utf8": "varchar(50)",
    "elements": [],
    "collation_id": 255,
    "is_explicit_collation": false
    },
    {
    "name": "country_id",
    "type": 4,
    "is_nullable": false,
    "is_zerofill": false,
    "is_unsigned": false,
    "is_auto_increment": false,
    "is_virtual": false,
    "hidden": 1,
    "ordinal_position": 3,
    "char_length": 11,
    "numeric_precision": 10,
    "numeric_scale": 0,
    "numeric_scale_null": false,
    "datetime_precision": 0,
    "datetime_precision_null": 1,
    "has_no_default": true,
    "default_value_null": false,
    "srs_id_null": true,
    "srs_id": 0,
    "default_value": "AAAAAA==",
    "default_value_utf8_null": true,
    "default_value_utf8": "",
    "default_option": "",
    "update_option": "",
    "comment": "",
    "generation_expression": "",
    "generation_expression_utf8": "",
    "options": "interval_count=0;",
    "se_private_data": "table_id=1070;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "column_key": 4,
    "column_type_utf8": "int",
    "elements": [],
    "collation_id": 255,
    "is_explicit_collation": false
    },
    {
    "name": "DB_TRX_ID",
    "type": 10,
    "is_nullable": false,
    "is_zerofill": false,
    "is_unsigned": false,
    "is_auto_increment": false,
    "is_virtual": false,
    "hidden": 2,
    "ordinal_position": 4,
    "char_length": 6,
    "numeric_precision": 0,
    "numeric_scale": 0,
    "numeric_scale_null": true,
    "datetime_precision": 0,
    "datetime_precision_null": 1,
    "has_no_default": false,
    "default_value_null": true,
    "srs_id_null": true,
    "srs_id": 0,
    "default_value": "",
    "default_value_utf8_null": true,
    "default_value_utf8": "",
    "default_option": "",
    "update_option": "",
    "comment": "",
    "generation_expression": "",
    "generation_expression_utf8": "",
    "options": "",
    "se_private_data": "table_id=1070;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "column_key": 1,
    "column_type_utf8": "",
    "elements": [],
    "collation_id": 63,
    "is_explicit_collation": false
    },
    {
    "name": "DB_ROLL_PTR",
    "type": 9,
    "is_nullable": false,
    "is_zerofill": false,
    "is_unsigned": false,
    "is_auto_increment": false,
    "is_virtual": false,
    "hidden": 2,
    "ordinal_position": 5,
    "char_length": 7,
    "numeric_precision": 0,
    "numeric_scale": 0,
    "numeric_scale_null": true,
    "datetime_precision": 0,
    "datetime_precision_null": 1,
    "has_no_default": false,
    "default_value_null": true,
    "srs_id_null": true,
    "srs_id": 0,
    "default_value": "",
    "default_value_utf8_null": true,
    "default_value_utf8": "",
    "default_option": "",
    "update_option": "",
    "comment": "",
    "generation_expression": "",
    "generation_expression_utf8": "",
    "options": "",
    "se_private_data": "table_id=1070;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "column_key": 1,
    "column_type_utf8": "",
    "elements": [],
    "collation_id": 63,
    "is_explicit_collation": false
    }
    ],
    "schema_ref": "index_demo",
    "se_private_id": 1070,
    "engine": "InnoDB",
    "last_checked_for_upgrade_version_id": 0,
    "comment": "",
    "se_private_data": "autoinc=0;version=0;",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "row_format": 2,
    "partition_type": 0,
    "partition_expression": "",
    "partition_expression_utf8": "",
    "default_partitioning": 0,
    "subpartition_type": 0,
    "subpartition_expression": "",
    "subpartition_expression_utf8": "",
    "default_subpartitioning": 0,
    "indexes": [
    {
    "name": "PRIMARY",
    "hidden": false,
    "is_generated": false,
    "ordinal_position": 1,
    "comment": "",
    "options": "flags=0;",
    "se_private_data": "id=155;root=4;space_id=13;table_id=1070;trx_id=3855;",
    "type": 1,
    "algorithm": 2,
    "is_algorithm_explicit": false,
    "is_visible": true,
    "engine": "InnoDB",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "elements": [
    {
    "ordinal_position": 1,
    "length": 4,
    "order": 2,
    "hidden": false,
    "column_opx": 0
    },
    {
    "ordinal_position": 2,
    "length": 4294967295,
    "order": 2,
    "hidden": true,
    "column_opx": 3
    },
    {
    "ordinal_position": 3,
    "length": 4294967295,
    "order": 2,
    "hidden": true,
    "column_opx": 4
    },
    {
    "ordinal_position": 4,
    "length": 4294967295,
    "order": 2,
    "hidden": true,
    "column_opx": 1
    },
    {
    "ordinal_position": 5,
    "length": 4294967295,
    "order": 2,
    "hidden": true,
    "column_opx": 2
    }
    ],
    "tablespace_ref": "index_demo/city_innodb"
    },
    {
    "name": "idx_fk_country_id",
    "hidden": false,
    "is_generated": false,
    "ordinal_position": 2,
    "comment": "",
    "options": "flags=0;",
    "se_private_data": "id=156;root=5;space_id=13;table_id=1070;trx_id=3855;",
    "type": 3,
    "algorithm": 2,
    "is_algorithm_explicit": false,
    "is_visible": true,
    "engine": "InnoDB",
    "engine_attribute": "",
    "secondary_engine_attribute": "",
    "elements": [
    {
    "ordinal_position": 1,
    "length": 4,
    "order": 2,
    "hidden": false,
    "column_opx": 2
    },
    {
    "ordinal_position": 2,
    "length": 4294967295,
    "order": 2,
    "hidden": true,
    "column_opx": 0
    }
    ],
    "tablespace_ref": "index_demo/city_innodb"
    }
    ],
    "foreign_keys": [
    {
    "name": "fk_city_country",
    "match_option": 1,
    "update_rule": 3,
    "delete_rule": 2,
    "unique_constraint_name": "PRIMARY",
    "referenced_table_catalog_name": "def",
    "referenced_table_schema_name": "index_demo",
    "referenced_table_name": "country_innodb",
    "elements": [
    {
    "column_opx": 2,
    "ordinal_position": 1,
    "referenced_column_name": "country_id"
    }
    ]
    }
    ],
    "check_constraints": [],
    "partitions": [],
    "collation_id": 255
    }
    }
    }
    ,
    {
    "type": 2,
    "id": 18,
    "object":
    {
    "mysqld_version_id": 80021,
    "dd_version": 80021,
    "sdi_version": 80019,
    "dd_object_type": "Tablespace",
    "dd_object": {
    "name": "index_demo/city_innodb",
    "comment": "",
    "options": "encryption=N;",
    "se_private_data": "flags=16417;id=13;server_version=80021;space_version=1;state=normal;",
    "engine": "InnoDB",
    "engine_attribute": "",
    "files": [
    {
    "ordinal_position": 1,
    "filename": "./index_demo/city_innodb.ibd",
    "se_private_data": "id=13;"
    }
    ]
    }
    }
    }
    ]

2.4.2 MyISAM

MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务的完整性没有要求或者以SELECT、INSERT为主的应用基本上都可以使用这个引擎来创建表 。

  • 不支持事务

    1
    2
    3
    4
    5
    create table goods_myisam(
    id int NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key(id)
    )ENGINE=myisam DEFAULT CHARSET=utf8mb4;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    mysql> start transaction; # 开启事务
    Query OK, 0 rows affected (0.00 sec)

    mysql> insert into goods_myisam(id,name)values(null,'华为P40 Pro'); # 插入一条数据
    Query OK, 1 row affected (0.03 sec)

    mysql> select * from goods_myisam; # 查看已插入的数据
    +----+---------------+
    | id | name |
    +----+---------------+
    | 1 | 华为P40 Pro |
    +----+---------------+
    1 row in set (0.00 sec)

    mysql> rollback; # 回滚事务
    Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> select * from goods_myisam; # 此时再查询,我们发现事务没有回滚,说明不支持事务
    +----+---------------+
    | id | name |
    +----+---------------+
    | 1 | 华为P40 Pro |
    +----+---------------+
    1 row in set (0.00 sec)
  • 存储方式

    每个MyISAM在磁盘上存储成3个文件,其文件名都和表名相同,但扩展名分别是 : .frm (表结构定义文件)、.MYD(my data数据存储文件)、MYI(My Index , 存储索引);

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    [root@litong data]# ll /test/ # mysql5.7的存储方式
    total 20
    -rw-r----- 1 mysql mysql 67 Aug 3 13:57 db.opt
    -rw-r----- 1 mysql mysql 8586 Aug 3 13:58 goods_myisam.frm
    -rw-r----- 1 mysql mysql 0 Aug 3 13:58 goods_myisam.MYD
    -rw-r----- 1 mysql mysql 1024 Aug 3 13:58 goods_myisam.MYI
    [root@litong data]# ls index_demo/ | grep goods_myisam; # mysql8的.frm文件已经改为了.sdi文件
    -rw-r----- 1 root root 2898 8月 3 11:47 goods_myisam_366.sdi
    -rw-r----- 1 root root 24 8月 3 11:48 goods_myisam.MYD
    -rw-r----- 1 root root 2048 8月 3 11:48 goods_myisam.MYI

2.4.3 MEMORY

Memory存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件,格式是.frm ,该文件中只存储表的结构,而其数据文件,都是存储在内存中,这样有利于数据的快速处理,提高整个表的效率。

MEMORY类型的表访问非常地快,因为他的数据是存放在内存中的,并且默认使用HASH索引 , 但是服务一旦关闭,表中的数据就会丢失。

2.4.4 MERGE

MERGE存储引擎是一组MyISAM表的组合,这些MyISAM表必须结构完全相同,MERGE表本身并没有存储数据,对MERGE类型的表可以进行查询、更新、删除操作,这些操作实际上是对内部的MyISAM表进行的。

对于MERGE类型表的插入操作,是通过INSERT_METHOD子句定义插入的表,可以有3个不同的值,使用FIRST 或LAST 值使得插入操作被相应地作用在第一或者最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE表执行插入操作。

可以对MERGE表进行DROP操作,但是这个操作只是删除MERGE表的定义,对内部的表是没有任何影响的。

image-20200803140837993

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
create table order_1990(            -- order分表1
order_id int ,
order_money decimal(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8mb4;

create table order_1991( -- order分表2
order_id int ,
order_money decimal(10,2),
order_address varchar(50),
primary key (order_id)
)engine = myisam default charset=utf8mb4;

create table order_all( -- order合并的主表
order_id int ,
order_money decimal(10,2),
order_address varchar(50),
primary key (order_id)
)engine = merge union = (order_1990,order_1991) INSERT_METHOD=LAST default -- 默认插入的数据会在最后一张表中插入
charset=utf8mb4;

insert into order_1990 values(1,100.0,'北京');
insert into order_1990 values(2,100.0,'上海');

insert into order_1991 values(10,200.0,'北京');
insert into order_1991 values(11,200.0,'上海');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
mysql> select * from order_1990; -- 查询order_1990的数据
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
+----------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from order_1991; -- 查询order_1991的数据
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
+----------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from order_all; -- 查询order_all的数据
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
+----------+-------------+---------------+
4 rows in set (0.00 sec)

mysql> insert into order_all values(100,10000.0,'西安'); -- 为order_all插入一条数据
Query OK, 1 row affected (0.03 sec)

mysql> select * from order_all; -- 查询是否插入成功
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
| 100 | 10000.00 | 西安 |
+----------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from order_1991; -- 插进了order_1991这张分表中,由于在MERGE表定义时,INSERT_METHOD选择的是LAST那么插入的数据会向最后一张表中插入。
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 10 | 200.00 | 北京 |
| 11 | 200.00 | 上海 |
| 100 | 10000.00 | 西安 |
+----------+-------------+---------------+
3 rows in set (0.00 sec)

mysql> select * from order_1990; -- 并没有插入到order_1990表中
+----------+-------------+---------------+
| order_id | order_money | order_address |
+----------+-------------+---------------+
| 1 | 100.00 | 北京 |
| 2 | 100.00 | 上海 |
+----------+-------------+---------------+
2 rows in set (0.00 sec)

3. 面试连环炮

  1. 为什么MySQL索引结构选用B+树?为什么不选用其他数据结构?
  2. 什么时候该建立索引,什么时候不该建立索引?
  3. 聚簇索引和非聚簇索引的区别?
  4. 索引的分类有哪些?
  5. 聚簇索引一定是唯一索引吗?
  6. 请解释回表、覆盖索引、最左匹配、索引下推。

4. 参考资料

支付宝打赏 微信打赏

请作者喝杯咖啡吧