Mysql 基础篇

小土豆Yuki

发布于 2020-06-15 17:23:46

6780

发布于 2020-06-15 17:23:46

存储引擎是mysql的特性之一，使用者可以根据自己的业务场景选择自己适合的存储引擎，是不是要支持事物，如何选择存储，如何选择索引数据，当然你也可以定制自己的存储引擎，如果你们公司有能力，mysql支持支持很多种存储引擎，如 Myisam ,Innodb,MEMORY,MERGE,BDB,EXAMPLE,CSV等等，mysql 5.5之前默认的存储引擎是Myisam,之后就是Innodb,今天我们只讲常见的存储引擎。

我们先看下一下一些简单的命令

查看当前版本

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.45    |
+-----------+

2.查看当前存储引擎

mysql> show variables like '%storage_engine%'
    -> ;
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_storage_engine     | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine             | InnoDB |
+----------------------------+--------+

3.查看表的存储引擎

mysql> show create table ai;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| ai    | CREATE TABLE `ai` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+

4.修改表的存储引擎

mysql> alter table ai engine=myisam;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show create  table ai;
+-------+----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------+
| ai    | CREATE TABLE `ai` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------+

Myisam存储引擎

Myisam不支持事物，也不支持外键，其优势是查询速度块，在对完整性要求不高，且是以insert ,select 为主选择这存储引擎很合适，每个Myisam在磁盘上存储有三个文件，文件名和表明一样，单扩展名分别是

.frm 存储表定义

.MYI 存储索引

.MYD 存储数据

我们可以指定索引和数据在不同的目录，平均分布IO,获得更快的速度。我们可以使用DATA_DIRECTORY 和INDEX_DIRECTORY 指定，当文件的有损坏的时候，Myisam提供了修复工具，使用CHECK TABLE 语句检查表的健康状况，并用REPAIR TABLE语句修复。

Myisam指出三种存储方式

静态表

动态表

压缩表

Myisam默认的存储方式是静态表，静态表的表字段是固定长度的字段，这种的优势是更快速更容易缓存，出现故障容易恢复，但是他的占用空间比动态表要打，静态表的数据在存储时会按照列的宽度定义补足空格，但是在应用返回的时候会去掉这些空格。

mysql> insert into Myisam_char(name) values ('abced'),(' abced'),('abced '),(' abced ');
mysql> select name,length(name) from Myisam_char;
+--------+--------------+
| name   | length(name) |
+--------+--------------+
| abced  |            5 |
|  abced |            6 |
| abced  |            5 |
|  abced |            6 |
+--------+--------------+
4 rows in set (0.00 sec)

我们看到后面的空格别去掉了，前面的空格保留了。

动态表记录的是不固定的长度的，这样可以减少存储空间，但是频繁的删除和插入会产生很多碎片，需要定时使用OPTIMIZE TABLE 或者使用myisamchk-r命令改善性能,并且故障时恢复相对比较困难。

压缩表是由myisampack工具创建，占据非常小的空间，因为每个记录都进行了压缩，所以有有非常小的访问开销。

Innodb

Innodb具有提交，回滚，和崩溃恢复能力的事物安全，相比较Myisam存储引擎他的写性能差些，占用磁盘空间也多点为了保存数据和索引。

我们现在看一下他的一些特性

自动增长列

他的自动增长列可以手动插入，当插入的是空或者是0的时候，他会自动+1，默认是从0开始，我们也可以使用alter table *** auto_increment=n;指定，但是数据库如果重启，他就会丢失需要我们重新设置。

mysql> create table test(
    -> id int not null auto_increment,
    -> name varchar(10)
    -> ,primary key(id))engine=innodb;
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+-----------------+
| Tables_in_jiepi |
+-----------------+
| Myisam_char     |
| ai              |
| test            |
+-----------------+
3 rows in set (0.00 sec)

mysql> insert into test(id,name) values(1,'wanger'),(null,'wangsan'),(0,'wangsi');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select  * from  test;
+----+---------+
| id | name    |
+----+---------+
|  1 | wanger  |
|  2 | wangsan |
|  3 | wangsi  |
+----+---------+
3 rows in set (0.00 sec)

也可已使用LAST_INSERT_ID()查询最后一次插入的值，当插入多条数据的时候，返回第一条记录的自动增长的值,

mysql> insert into test values (4,'wangwu');
Query OK, 1 row affected (0.01 sec)

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> insert into test values (5,'wangliu'),(6,'wangba');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)
mysql> insert into test (name) values ('wangjiu'),('wangshi');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select  last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                7 |
+------------------+
1 row in set (0.00 sec)

Innodb表的自动增长列必须是索引，如果是组合索引，也必须是在第一列，但是myisam 是可以在其他列的，如果这样插入数据，自动增长列是按照组合索引前几列排序后增长的，如下面演示

mysql> create table test1(
    -> d1 int not null auto_increment,
    -> d2 int not null,
    -> name varchar(10),
    -> index (d2,d1)
    -> )engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test1 (d2 ,name) values (2,'2'),(3,'3'),(4,'4'),(2,'2'),(3,'3'),(4,'4');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0
syntax to use near 'test1' at line 1
mysql> select  *  from test1;
+----+----+------+
| d1 | d2 | name |
+----+----+------+
|  1 |  2 | 2    |
|  1 |  3 | 3    |
|  1 |  4 | 4    |
|  2 |  2 | 2    |
|  2 |  3 | 3    |
|  2 |  4 | 4    |
+----+----+------+
6 rows in set (0.00 sec)

2.外键约束

目前支持外键的只有innodb,在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。

我们先建立两张表

mysql> create table country(
    -> country_id int not null auto_increment,
    -> country varchar(20) not null,
    -> primary key (country_id)
    -> )engine=innodb;
Query OK, 0 rows affected (0.03 sec)
mysql> create table city(
    -> city_id int not null auto_increment,
    -> city varchar(20) not null,
    -> country_id int not null,
    -> primary key (city_id),
    -> key idx_fk_country_id(country_id),
    -> FOREIGN KEY (country_id) REFERENCES country (country_id)
    -> on delete restrict on update cascade
    -> )engine=innodb;
Query OK, 0 rows affected (0.04 sec)

在创建索引的时候，可以指定删除或者更新的时候，对子表进项响应的操作

RESTRICT:限制子表有关联记录的时候不能更新

CASCADE：表示在父表删除或更新的时候，子表也进行删除更新

SET NULL：表示父表在删除或更新的时候，子表随对应的子段进行set null

NO ACTION：限制子表有关联记录的时候不能更新

city设置的是on delete restrict on update cascade，父表删除的时候，子表有管来你数据不予许删除，父表在更新的时候，子表对应的更新

mysql> insert into country values(1,'shanxi');
Query OK, 1 row affected (0.01 sec)

mysql> insert into city values(1,'xian',1);
Query OK, 1 row affected (0.00 sec)

mysql> delete from country where country_id =1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`jiepi`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
mysql> update country set country_id=100 where country_id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select *  from country;
+------------+---------+
| country_id | country |
+------------+---------+
|        100 | shanxi  |
+------------+---------+
1 row in set (0.00 sec)

mysql> select  *  from city;
+---------+------+------------+
| city_id | city | country_id |
+---------+------+------------+
|       1 | xian |        100 |
+---------+------+------------+
1 row in set (0.00 sec)

3.存储方式

共享表空间存储:这种方式的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir和innodb_index_file_path定义的表空间中，可以是多个文件。

多表空间存储:表的结构依然保留在.frm中，数据和索引保存在.idb中，对于分区表，每一个分区对应一个文件，文件名是表明+分区名,这样可以平分磁盘的IO.

要使用多表空间存储，要使用参数innodb_file_per_table.并且在重启之后才会生效,多表空间存储生效后，只对新建表生效.多表空间特性可以方便的进行单表备份，和恢复操作，如果直接复制.idb和.frm是不能正确被识别的，因为没有共享表空间的数据字典信息，但是可以使用命令

alter table tbl_name discard tablespace;
alter table tbl_name import  tablespcae;

将备份恢复到数据库中，但是这样的单表备份只能恢复到原来的数据库中.如果要恢复到其他数据库，可以使用mysqdump 和 mysqlimport.

在多表空间中，共享表空间也是必须有的，因为他要把内部数据词典和在线重做日志放到这个文件中。

MEMORY

MEMORY存储引擎使用内存存储表的，每个MEMORY表对应一个文件，格式是.frm,由于他的数据在内存中，所以他的访问速度非常的快，他默认索引是HSAH，也可以指定BTREE索引，但是当服务器一旦重启，他的数据将丢失。

一般我们会使用 -init-file选项，使用 insert into ...select 或load data infile ,可以在服务启动的时候加载数据。

他的的大小是有限制，默认是16MB但是可以设置，使用 max_heap_table_size,也可以使用max_rows子句指定最大行数.

MEMORY主要适用于更新频率不多的作为统计表的中间结果表使用，切记，使用MEMORY可能或丢失数据，他实际上是没有写入磁盘的。

MERGE

MERGE是一个Myisam表的组合，他们的表的结构必须完全相同，对MERGE表的查询，删除，更新实际上是对myisam表的操作，我们对MERGE的drop 实际上对内部的表是没有影响的，他删除的只是MERGE的定义.

我们也可以使用子句INSERT_METHOD定义插入哪一张表，他有三个值

LAST:插入最后一张表

FIRST:插入第一张表

NO:表示不能对MERGE尽心插入操作

我们说的很抽象，我们演示一下

mysql> create table payment_2007(
    ->   country_id int not null ,
    ->   payment_date datetime,
    ->   amount decimal(15,2),
    ->   key idx_fx_country_id(country_id)
    -> )engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> create table payment_all(
    ->   country_id int not null ,
    ->   payment_date datetime,
    ->   amount decimal(15,2),
    ->   key idx_fx_country_id(country_id)
    -> )engine=merge union=(payment_2006,payment_2007) insert_method=last;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into payment_2006 values(1,'2006-05-01',10000),(2,'2006-08-01',15000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into payment_2007 values(1,'2007-05-01',20000),(2,'2007-08-01',25000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select *  from payment_2006;
+------------+---------------------+----------+
| country_id | payment_date        | amount   |
+------------+---------------------+----------+
|          1 | 2006-05-01 00:00:00 | 10000.00 |
|          2 | 2006-08-01 00:00:00 | 15000.00 |
+------------+---------------------+----------+
2 rows in set (0.00 sec)

mysql> select *  from payment_2007;
+------------+---------------------+----------+
| country_id | payment_date        | amount   |
+------------+---------------------+----------+
|          1 | 2007-05-01 00:00:00 | 20000.00 |
|          2 | 2007-08-01 00:00:00 | 25000.00 |
+------------+---------------------+----------+
2 rows in set (0.00 sec)
mysql> create table payment_all(
    ->   country_id int not null ,
    ->   payment_date datetime,
    ->   amount decimal(15,2),
    ->   key idx_fx_country_id(country_id)
    -> )engine=merge union=(payment_2006,payment_2007) insert_method=last;
Query OK, 0 rows affected (0.01 sec)

mysql> select *  from  payment_all;
+------------+---------------------+----------+
| country_id | payment_date        | amount   |
+------------+---------------------+----------+
|          1 | 2006-05-01 00:00:00 | 10000.00 |
|          2 | 2006-08-01 00:00:00 | 15000.00 |
|          1 | 2007-05-01 00:00:00 | 20000.00 |
|          2 | 2007-08-01 00:00:00 | 25000.00 |
+------------+---------------------+----------+
4 rows in set (0.00 sec)

mysql> insert into payment_all values(3,'2006-06-01',30000),(4,'2006-09-01',35000);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select  *  from  payment_all;
+------------+---------------------+----------+
| country_id | payment_date        | amount   |
+------------+---------------------+----------+
|          1 | 2006-05-01 00:00:00 | 10000.00 |
|          2 | 2006-08-01 00:00:00 | 15000.00 |
|          1 | 2007-05-01 00:00:00 | 20000.00 |
|          2 | 2007-08-01 00:00:00 | 25000.00 |
|          3 | 2006-06-01 00:00:00 | 30000.00 |
|          4 | 2006-09-01 00:00:00 | 35000.00 |
+------------+---------------------+----------+
6 rows in set (0.00 sec)

mysql> select  *  from payment_2007;
+------------+---------------------+----------+
| country_id | payment_date        | amount   |
+------------+---------------------+----------+
|          1 | 2007-05-01 00:00:00 | 20000.00 |
|          2 | 2007-08-01 00:00:00 | 25000.00 |
|          3 | 2006-06-01 00:00:00 | 30000.00 |
|          4 | 2006-09-01 00:00:00 | 35000.00 |
+------------+---------------------+----------+
4 rows in set (0.00 sec)

我们看到我们的payment_all 在插入数据的时候，是插入在了payment_2007表中，那是因为我们在创建payment_all的时候指定的insert_method=last.

那merge和分区表的有什么区别呢

merge是不能按照日志进行对应的表插入的，分区表是可以的，merge可以透明的对多个表进行查询和更新操作，对于按照时间记录的日志操作，我们可以透明的进行插入操作。

存储引擎如何选择呢

Myisam:此存储引擎是对查询和插入为主，删除和更新操作少的应用比较适合，并度数据的完整性要求不高，myisam就非常适合，主要应用于web,数据仓库.

Innodb:他支持事物和外键，对于数据的完整性要求比较高的，并发是要求数据一致性的场景，适合Innodb存储引擎，可以对数据事物进行提交，回滚，使用与财务相关应用，准确度要求比较高的场景.

MEMORY:他是把数据存储在内存中，由于内存的大小有限，太大的无法缓存在内存中，并且要保证数据可以恢复，因为数据库异常重启的时候，内存的数据将全部丢失，他使用于更新不频繁的小表，用于快速访问结果.

MERGE:他是一系列的myisam表，有效的改善了myisam单表的存储能力，可以均匀的分布在多个磁盘，有效改善了访问速度。

上面也是一般经验，但是应用的场景千差万别，要根据实际情况选择自己的存储引擎。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2019-11-04，如有侵权请联系 cloudcommunity@tencent.com 删除

存储