MySQL数据库索引、事务与存储引擎

最新推荐文章于 2021-10-09 12:23:56 发布
最新推荐文章于 2021-10-09 12:23:56 发布
阅读量1.8k 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_50854537/article/details/111589315
版权

文章目录

数据库索引

一：索引的概念

索引是一个特殊的文件，包含着对数据表中所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。例如需要遍历 200 条数据，在没有索引的情况下，数据库会遍历全部 200 条数据后选择符合条件的；而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录。

  • 是一个排序的列表，存储着索引值和这个值所对应的物理地址

  • 无须对整个表进行扫描，通过物理地址就可以找到所需数据

  • 是表中—列或者若干列值排序的方法

- 需要额外的磁盘空间

二：索引的作用

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间（因为索引也要随之变动）。但是相比付出的代价，索引的作用显得更为重要：

  1. 数据库利用各种快速定位技术，能够大大加快查询速率
  2. 当表很大或查询涉及到多个表时，可以成干上万倍地提高查询速度
  3. 可以降低数据库的IO成本，并且还可以降低数据库的排序成本
  4. 通过创建唯─性索引保证数据表数据的唯─性
  5. 可以加快表与表之间的连接
  6. 在使用分组和排序时,可大大减少分组和排序时间

三：索引的分类

MySQL 的索引分为以下几类:

  • （1）普通索引： 这是最基本的索引类型，而且它没有唯一性之类的限制

  • （2）唯一性索引：这种索引和前面的 “普通索引” 基本相同，但有一个区别：索引列的所有值只能出现一次，即必须唯一。当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。

  • （3）主键索引：主键是一种唯一性索引，但它必须指定为 “PRIMARY KEY” 。在数据库中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。

  • （4）全文索引：索引类型为 FULLTEXT ，全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。

  • （5）单列索引与多列索引：索引可以是单列上创建的索引，也可以是在多列上创建的索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或两列或多列排序时，索引也很有帮助。

四：创建索引的原则依据

索引可以提升数据库查询的速度，但并不是任何情况下都需要创建索引。因为索引本身会消耗系统资源，更重要的是在有索引的情况下，数据库查询会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担，下面列出了创建索引的原则依据。

  1. 表的主键、外键必须有索引。主键具有唯一性，索引值也是唯一的，查询时可以快速定位到数据行。外键一般关联的是另一个表的主键，所以在多表查询时也可以快速定位。
  2. 数据量超过 300 行的表应该有索引。数据量较大时，如果没有索引，需要把表遍历一遍，严重影响数据库的性能。
  3. 经常与其他表进行表连接的表，在连接字段上应该建立索引。
  4. 唯一性太差的字段不适合建立索引。如果索引字段的数据唯一性太差，是不适合创建的。
  5. 更新太频繁的字段不适合创建索引。在表中进行增加、删除、修改操作时，索引也有相应操作产生。字段更新过于频繁，对于系统资源占用也会更多。
  6. 经常出现在 Where 子句中的字段，特别是大表的字段，应该建立索引。
  7. 索引应该建在选择性高的字段上。如果很少的字段拥有相同值，即有很多独特值，则选择性很高。
  8. 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建立索引。

五：索引的具体操作

1：普通索引

命令格式：CREATE INDEX <索引的名字> ON tablename （列的列表）

（1）创建索引

以这表为例，选取某一项创建索引

mysql> desc vvv;
+--------+--------------+------+-----+---------+-------+
| Field  | Type         | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| name   | varchar(128) | NO   |     | NULL    |       |
| age    | int(3)       | YES  |     | NULL    |       |
| height | int(3)       | NO   |     | NULL    |       |
+--------+--------------+------+-----+---------+-------+

以年龄和名字这两项创建索引

mysql> create index putong on vvv(age);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index putong1 on vvv(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看索引

mysql> show index from vvv\G;
*************************** 1. row ***************************
        Table: vvv
   Non_unique: 1
     Key_name: putong
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: vvv
   Non_unique: 1
     Key_name: putong1
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

（2）删除索引再查看

mysql> drop index putong1 on vvv;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

ysql> show index from vvv\G;
*************************** 1. row ***************************
        Table: vvv
   Non_unique: 1
     Key_name: putong
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

（3）修改表时添加索引再查看

mysql> alter table vvv add index putong3(height);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from vvv\G;
*************************** 1. row ***************************
        Table: vvv
   Non_unique: 1
     Key_name: putong
 Seq_in_index: 1
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: vvv
   Non_unique: 1
     Key_name: putong3
 Seq_in_index: 1
  Column_name: height
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

（4）创建表时添加索引再查看

mysql> create table stu(num int(6) not null primary key, name varchar(64) not null, 
age int(3) not null, index putong5(name));
Query OK, 0 rows affected (0.01 sec)

mysql> mysql> show ind stu\G;
*************************** 1. row ***************************
        Table: stu
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: num
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: stu
   Non_unique: 1
     Key_name: putong5
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

ERROR: 
No query specified

2：唯一索引

命令格式：CREATE UNIQUE INDEX <索引的名字> ON tablename （列的列表）

第一种创建方式

  • 重新创建一个表创建唯一索引
mysql> create table xinxi(num int(6) not null primary key, name varchar(64) not null, age int(3) not null, index putong5(name));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into xinxi values(001,'zhangsan',16),(002,'lisi',17),(003,'lisi',18),(004,'wangwu',18);             
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from xinxi;
+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
4 rows in set (0.00 sec)
  • 创建唯一索引

由于名字和年龄都有重复的数据，所以无法创建唯一索引

ERROR 1062 (23000): Duplicate entry 'lisi' for key 'one'
mysql> create unique index one on xinxi(age);
ERROR 1062 (23000): Duplicate entry '18' for key 'one'

num中没有重复的数据，所以可以创建唯一索引

mysql> create unique index one on xinxi(num);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

第二种创建方式

  • 先删除之前创建的唯一索引
mysql> drop index one on xinxi;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
  • 修改时创建唯一索引
mysql> alter table xinxi add unique two(num);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

第三种创建方式

  • 重新创建新表时添加唯一性索引
mysql> create table dog(id int(6),dog_name varchar(64),sta int(1),unique id(id));
Query OK, 0 rows affected (0.00 sec)

mysql> desc dog;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(6)      | YES  | UNI | NULL    |       |
| dog_name | varchar(64) | YES  |     | NULL    |       |
| sta      | int(1)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
  • 查看索引
ysql> show index from dog\G;
*************************** 1. row ***************************
        Table: dog
   Non_unique: 0
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

ERROR: 
No query specified

3：主键索引

  • 用此表创建主键索引
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(6)      | YES  | UNI | NULL    |       |
| dog_name | varchar(64) | YES  |     | NULL    |       |
| sta      | int(1)      | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
mysql> insert into dog(dog_name,sta) values('xiaohei',0);
Query OK, 1 row affected (0.00 sec)

mysql> select * from dog;
+------+----------+------+
| id   | dog_name | sta  |
+------+----------+------+
| NULL | xiaohei  |    0 |
+------+----------+------+
1 row in set (0.00 sec)

4：单列索引与多列索引

  • 创建新表
mysql> create table user(name varchar(64),age int(3),sex tinyint(1),index user(name,age,sex));
Query OK, 0 rows affected (0.00 sec)
  • 查看索引
mysql> show index from user\G;
*************************** 1. row ***************************
        Table: user
   Non_unique: 1
     Key_name: user
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: user
   Non_unique: 1
     Key_name: user
 Seq_in_index: 2
  Column_name: age
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 3. row ***************************
        Table: user
   Non_unique: 1
     Key_name: user
 Seq_in_index: 3
  Column_name: sex
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
3 rows in set (0.00 sec)

ERROR: 
No query specified

5：全文索引

  • 以此表创建全文索引
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(64) | YES  | MUL | NULL    |       |
| age   | int(3)      | YES  |     | NULL    |       |
| sex   | tinyint(1)  | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
mysql> create fulltext index full on user(name(64));
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

事务的概念及特点

一：事务的概念

  • 是一种机制、一个操作序列，包含了一组数据库操作命令,并且把所有的命令作为一个整体一起向系统提交或撤销操作请求,即这一组数据库命令要么都执行，要么都不执行
  • 是一个不可分割的工作逻辑单元,在数据库系统上执行并发操作时，事务是最小的控制单元
  • 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等
  • 通过事务的整体性以保证数据的一致性

二：事务的ACID特点

事务具体由四个特性：原子性(Atomicity)、一致性(Consistency)、隔离性(lsolation)、持久性(Durability)

原子性(Atomicity)

  • 事务是一个完整的操作,事务的各元素是不可分的
  • 事务中的所有元素必须作为一个整体提交或回滚
  • 如果事务中的任何元素失败，则整个事务将失败

一致性(Consistency)

  • 当事务完成时，数据必须处于一致状态
  • 在事务开始前，数据库中存储的数据处于一致状态
  • 在正在进行的事务中，数据可能处于不一致的状态
  • 当事务成功完成时，数据必须再次回到已知的一致状态

隔离性(lsolation)

  • 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
  • 修改数据的事务可在另一个使用相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据

持久性(Durability)

  • 指不管系统是否发生故障，事务处理的结果都是永久的
  • 一旦事务被提交，事务的效果会被永久地保留在数据库中

三：事务控制语句

1：MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交

2：事务控制语句

  • BEGIN或START TRANSACTION > 开始一个事务
  • COMMIT > 提交
  • ROLLBACK > 回滚
  • SAVEPOINT identifier > 创建保存点
  • RELEASE SAVEPOINT identifier > 删除保存点
  • ROLLBACK TO identifier> 回滚到指定保存点
  • SET TRANSACTION> 设置事务的隔离级别

事务的控制方法

1：手动对事务进行控制的方法

事务处理命令控制事务

  • bebin:开始一个事务，后面会有多条数据库操作语句执行
  • commit:提交一个事务，对应前面的 begin 操作，他们之间的数据库操作语句一起完成
  • rollback:回滚一个事务使用，在 begin 和 commit 之间，如果某一个数据库操作语句出现错误，执行 rollback 回滚，数据库回到 begin 之前的状态，也就是操作语句都没执行

2：使用set命令进行控制

  • set autocommit=0:禁止自动提交
  • set autocommit=1:开启自动提交

（1）自动提交默认为开启状态

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

（2）关闭自动默认提交

mysql> set autocommit=0;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

每次重新连接数据库的时候，自动默认提交都处于开启状态

手动事务操作

以此表为例

1：第一种操作

+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
  • 开始一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  • 删除一个数据
mysql> delete from stu where num=1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+-----+--------+-----+
| num | name   | age |
+-----+--------+-----+
|   2 | lisi   |  17 |
|   3 | lisi   |  18 |
|   4 | wangwu |  18 |
+-----+--------+-----+
3 rows in set (0.00 sec)
  • 重新建立新的相同的会话查看数据有没有被提交

此时数据并没有被提交

mysql> select * from stu;
+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
4 rows in set (0.00 sec)
  • 进行回滚操作

被删除的数据又被恢复

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
4 rows in set (0.00 sec)
  • 提交事务

提交之后，两边数据都是一样的

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
4 rows in set (0.00 sec)

2：第二种操作

同样以此表为例进行事务操作

+-----+----------+-----+
| num | name     | age |
+-----+----------+-----+
|   1 | zhangsan |  16 |
|   2 | lisi     |  17 |
|   3 | lisi     |  18 |
|   4 | wangwu   |  18 |
+-----+----------+-----+
  • 开始一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  • 删除某一项数据
mysql> delete from stu where num=1;
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu;
+-----+--------+-----+
| num | name   | age |
+-----+--------+-----+
|   2 | lisi   |  17 |
|   3 | lisi   |  18 |
|   4 | wangwu |  18 |
+-----+--------+-----+
3 rows in set (0.00 sec)
  • 直接提交事务
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+-----+--------+-----+
| num | name   | age |
+-----+--------+-----+
|   2 | lisi   |  17 |
|   3 | lisi   |  18 |
|   4 | wangwu |  18 |
+-----+--------+-----+
3 rows in set (0.00 sec)

3：第三种操作

以此表为例

+-----+--------+-----+
| num | name   | age |
+-----+--------+-----+
|   2 | lisi   |  17 |
|   3 | lisi   |  18 |
|   4 | wangwu |  18 |
+-----+--------+-----+
  • 开始一个事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
  • 添加一项数据

此时并没有提交事务

mysql> insert into stu values(5,'dahuang',20);
Query OK, 1 row affected (0.00 sec)
  • 断开连接再重新连接
mysql> exit
Bye
[root@server4 ~]# mysql -uroot -p123123
  • 进入数据库添加任务

此时并没有提交任务已经生成数据

mysql> insert into aaa.stu values(5,'dahuang',20);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa.stu;
+-----+---------+-----+
| num | name    | age |
+-----+---------+-----+
|   2 | lisi    |  17 |
|   3 | lisi    |  18 |
|   4 | wangwu  |  18 |
|   5 | dahuang |  20 |
+-----+---------+-----+
4 rows in set (0.00 sec)

每次重新连接数据库的时候，自动默认提交都处于开启状态

存储引擎概

一：存储引擎概念介绍

1：MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎

2：存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式

3： MySQL常用的存储引擎

  • MyISAM
  • lnnoDB

4：MySQL数据库中的组件，负责执行实际的数据I/O操作

5：MySQL系统中，存储引擎处于文件系统之上,在数据保存到数据文件之前会传输到存储引擎,之后按照各个存储引擎的存储格式进行存储

二：MyISAM的介绍及特点

1：MyISAM不支持事务，也不支持外键

2：访问速度快

3：对事务完整性没有要求

4：MyISAM在磁盘上存储成三个文件

  • .frm文件存储表定义数
  • 据文件的扩展名为.MYD (MYData)
  • 索引文件的扩展名是.MYI (MYIndex)

5：表级锁定形式,数据在更新时锁定整个表

6：数据库在读写过程中相互阻塞

  • 会在数据写入的过程阻塞用户数据的读取
  • 也会在数据读取的过程中阻塞用户的数据写入

7：数据单独写入或读取,速度过程较快且占用资源相对少

8：MyIAM支持的存储格式

  • 静态表
  • 动态表
  • 压缩表

存储格式解释

  • 静态表：静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速,容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。静态表在数据存储时会根据列定义的宽度定义补足空格，但是在访问的时候并不会得到这些空格，这些空格在返回给应用之前已经去掉。同时需要注意:在某些情况下可能需要返回字段后的空格，而使用这种格式时后面的空格会被自动处理掉。
  • 动态表：动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE
    TABLE 语句或myisamchk -r命令来改善性能，并且出现故障的时候恢复相对比较困难。
  • 压缩表：压缩表由myisamchk工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。

三：MyISAM适用的生产场景

1：公司业务不需要事务的支持

2：单方面读取或写入数据比较多的业务

3：MylSAM存储引擎数据读写都比较频繁场景不适合

4：使用读写并发访问相对较低的业务

5：数据修改相对较少的业务

6：对数据业务一致性要求不是非常高的业务

7：服务器硬件资源相对比较差

四：lnnoDB特点介绍

1：支持4个事务隔离级别

2：行级锁定但是全表扫描仍然会是表级锁定

3：读写阻塞与事务隔离级别相关

4：能非常高效的缓存索引和数据

5：表与主键以簇的方式存储

6：支持分区、表空间，类似oracle数据库

7：支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

8：对硬件资源要求还是比较高的场合

部分注释

  1. 分区：就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表,但底层是由N个物理区块组成的
  2. 分表：就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它
  3. 分库：一旦分表,一个库中的表会越来越多
  4. 表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间

一个数据库可以包含多个表空间，一个表空间只能属于一个数据库

一个表空间包含多个数据文件，一个数据文件只能属于一个表空间

五：lnnoDB适用生产场景

1：业务需要事务的支持

2：行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成

3：业务数据更新较为频繁的场景，如:论坛，微博等

4：业务数据、致性要求较高，如:银行业务

5：硬件设备内存较大，利用lnnodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

六：企业选择存储引擎依据

1：需要考虑每个存储引擎提供的核心功能及应用场景

2：支持的字段和数据类型

  • 所有引擎都支持通用的数据类型
  • 但不是所有的引警都支持其它的字段类型，如二进制对象

3：锁定类型:不同的存储引擎支持不同级别的锁定

  • 表锁定
  • 行锁定

4：索引的支持

  • 建立索引在搜索和恢复数据库中的数据时能显著提高性能
  • 不同的存储引擎提供不同的制作索引的技术
  • 有些存储引擎根本不支持索引

5：事务处理的支持

  • 提高在向表中更新和插入信息期间的可靠性
  • 可根据企业业务是否要支持事务选择存储引擎

七：修改存储引擎

方法1: alter table修改

alter table table_name engine=引|擎;

方法2:修改my.cnf，指定默认存储引擎并重启服务

default-storage-engine=lnnoDB

方法3: create table创建表时指定存储引擎

create table表名 (字段) engine = 引擎

八：具体修改操作示例

查看当前支持的引擎

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

1：第一种修改引擎

  • 查看一个存储引擎
mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE "stu" (
  "num" int(6) NOT NULL,
  "name" varchar(64) NOT NULL,
  "age" int(3) NOT NULL,
  PRIMARY KEY ("num"),
  KEY "putong5" ("name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8            #存储引擎为：InnoDB
1 row in set (0.00 sec)
  • 修改存储引擎
mysql> alter table stu engine=MyISAM;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

修改完后查看

mysql> show create table stu\G;
*************************** 1. row ***************************
       Table: stu
Create Table: CREATE TABLE "stu" (
  "num" int(6) NOT NULL,
  "name" varchar(64) NOT NULL,
  "age" int(3) NOT NULL,
  PRIMARY KEY ("num"),
  KEY "putong5" ("name")
) ENGINE=MyISAM DEFAULT CHARSET=utf8                   #存储引擎为：MyISAM
1 row in set (0.00 sec)

2：第二种修改引擎

  • 退出数据库进入配置文件修改默认引擎
mysql> exit
Bye
[root@server4 ~]# vi /etc/my.cnf
default-storage-engine=MyISAM            #添加
  • 重启数据库
[root@server4 ~]# systemctl restart mysqld
  • 登录数据库进行查看
[root@server4 ~]# mysql -uroot -p123123

mysql> show engines\G;

*************************** 5. row ***************************
      Engine: MyISAM
     Support: DEFAULT
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

3：第三种修改引擎

  • create table 创建表时指定存储引擎

指定引擎

mysql> create table aaa.pp(id int(1)) engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

不指定引擎

mysql> create table aaa.qq(id int(1));
Query OK, 0 rows affected (0.00 sec)
  • 查看引擎
mysql> show create table aaa.pp\G;
*************************** 1. row ***************************
       Table: pp
Create Table: CREATE TABLE "pp" (
  "id" int(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8              #指定为;InnoDB
1 row in set (0.11 sec)
mysql> show create table aaa.qq\G;
*************************** 1. row ***************************
       Table: qq
Create Table: CREATE TABLE "qq" (
  "id" int(1) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8              #系统默认：MyISAM
1 row in set (0.00 sec)
m0_50854537
关注
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySql数据库优化--v1.0
路漫漫兮
10-02 306
选取合适的字段属性 这其中包括最长用到的char和varchar的问题。过长的字段长度会给数据库带来压力 使用JOIN来代替子查询 子查询是先查外表在差内表，如果外表数据量很大，这样速度会明显的下降，JOIN是经过优化的查询，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。 使用联合(UNION)来代替手动创建的临时表 UNION会自动创建多条语句的临时表来完成查询任务，并且在查询任务执行完毕后，自动删除临时表。注意的是：每条查询语句的字段数量要一致。 事务..
GBase 8s 中CLOB的数据迁移
冀辉的博客
10-09 582
GBase 8s 中CLOB的数据迁移生成CLOB测试数据创建演示用的文本文件创建演示表从GBase 8s中导出数据导出用户数据导出CLOB数据到文件导入数据和CLOB到GBase 8s导出数据使用文件更新表中CLOB字段 在GBase 8s中可以存储大文本对象CLOB，最大可以支持4T大小。 对于GBase 8s中的CLOB，不同于MySQL中的MediumBlob（最大支持16M），不能通过INSERT方式，直接对记录进行插入操作，因此当一些应用使用CLOB存储配置信息时，可能遇到如何方便的进行数据库部
mysql 主键重复则覆盖_深入探寻mysql自增列导致主键重复问题的原因
weixin_34835735的博客
12-23 466
废话少说，进入正题。拿到问题后，首先查看现场，发现问题表的中记录的最大值比自增列的值要大，那么很明显，当有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错。首先想办法解决问题，通过人工调大自增列的值，保证大于表内已有的主键即可，调整后，导数据正常。问题是解决了，接下来要搞清楚问题原因，什么操作导致了这种现象的发生呢？这里有一种可能，即业务逻辑包含更新自增主键的代码，由于mysq...
MySQL_插入更新 ON DUPLICATE KEY UPDATE
qq_2300688967的博客
02-28 433
平时我们在设计数据库表的时候总会设计 unique  或者 给表加上 primary key 的限制条件.此时 插入数据的时候 ，经常会有这样的情况：我们想向数据库插入一条记录：  若数据表中存在以相同主键的记录，我们就更新该条记录。  否则就插入一条新的记录。逻辑上我们需要怎么写：$result = mysql_query('select * from xxx where id = 1');$r...
mysql 主键重复则覆盖_mysql 主键重复 Duplicate entry '7192542' for key 'PRIMARY'问题解决方法...
weixin_42196667的博客
12-23 603
首先我们用MySQL自带的可视化管理工具MySQL WorkBench打开数据库及表格，出现此问题的主要原因是主键已经有了名为“XXX”的那项，实际上我写的代码并没有重复也会弹出此报错，下面我们看一下我自己的解决方案。Mysql导入大量数据时，会报如下错误，主键重复，不能再继续执行。Query:INSERT INTO `FNDCN_MNG` VALUES(787390578, 1, '杨明', 9...
深入探讨:MySQL数据库MyISAM与InnoDB存储引擎的比较
12-15
MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。...
MySQL数据库三种常用存储引擎特性对比
12-16
MySQL数据库的存储引擎是其核心特性之一，不同的存储引擎针对不同的业务需求有着独特的优化。本文主要探讨了三种常用的存储引擎：MyISAM、InnoDB和NDBCluster。 1. MyISAM： - 无事务支持：MyISAM不支持事务处理，...
MySQL数据库：MySQL存储引擎.pptx
05-29
而MySQL数据库提供了多种存储引擎，用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎，MySQL的核心就是存储引擎。 ;MySQL 5.7支持的存储引擎有：InnoDB、MyISAM、...
mysql 主键重复则覆盖_mysql insert插入时实现如果数据表中主键重复则更新，没有重复则插入的四种方法...
weixin_32158567的博客
12-23 4366
个人实例：INSERT INTO w_dashboard(dashboard_name, content, createTime)VALUES("aa","cc","2018-01-11 16:58:03")ON DUPLICATE KEY UPDATE dashboard_name=VALUES(dashboard_name);或INSERT IGNORE INTO w_dashboard(da...
大数据——NoSQL综述
蜂蜜柚子加苦茶
09-24 543
NoSQL综述什么是NoSQL为什么使用NoSQLNoSQL和关系型数据库对比NoSQL的特点NoSQL基本概念三大基石（一）三大基石（二）三大基石（三）IndexingQueryMapReduceShardingNoSQL分类键值存储数据库（key-value）列存储数据库（Wide Column Store）文档型数据库（Document Store）图数据（Graph Databases）NoSQL和BI、大数据的关系 什么是NoSQL NoSQL：not only SQL，非关系想数据库 NoSQL
Mybatis：批量插入，主键或唯一索引键存在则覆盖 ON DUPLICATE KEY UPDATE
qq_42709262的博客
06-05 3593
INSERT into t_brokerage (Fuid ,Fsku_id ,Fsku_brokerage) values <foreach collection ="brokerageList" item="brokerage" separator =","> (#{brokerage.fuid} ,...
关于mysql_fetc_array()在左连接时被覆盖问题的解决
vancekq的专栏
07-26 1463
现在有两张表，分别为 表A id   name 1    你好 2    世界 3    天下会 表B id   class 1    太行 2    宇宙 现在执行如下的语                   select * from a left join b on a.id=b.id 结果 id name id class  1 你好 1
MySQL优化建议
Xjzzon的博客
09-21 264
数据库命令规范 所有数据库对象必须使用小写字母并是用下划线分割 所有数据库对象名称禁止使用MySQL的保留关键字(否则查询时需要将其用单引号括起来) 数据库对象的命名要清晰 临时表必须以tmp_为前缀并以日期为后缀，备份表必须以bak_为前缀并以日期为后缀 所有存储相同数据的列名和类型必须一致，因为作为关联列，不一致可能会造成索引失效 数据库基本设计规范 所有表必须使用Innodb存储引擎 数据库和表的字符集统一使用UTF8，emoji表情需要使用utf8mb4字符集 所有表和字段都需要注释 尽量控制
mysql 获取所有的索引 jdbc_jdbc获取mysql、oracle数据库的表、字段、主键、唯一索引...
weixin_42356460的博客
01-27 669
根据数据库的四大参数获取指定库中的所有表//根据数据库的参数获取指定库中的所有表public static List> getDbTables(String driver, String url, String user, String pwd) {Connection conn = null;ResultSet rs = null;List> tables = new ArrayLi...
mysql 获取所有的索引 jdbc_从Oracle使用JDBC获取所有外键唯一索引
weixin_29416037的博客
01-19 387
我有一个表与外键到另一个表中的唯一列。我可以看到外键被正确定义，但是当我尝试使用下面提到的使用JDBC的代码来获得它时，我没有看到它。我已经验证过，当外键引用另一个表的主键时，这会起作用。从Oracle使用JDBC获取所有外键唯一索引所以我的问题是“如何使用JDBC在Oracle中获得唯一索引列的外键？”。ResultSet rset = databaseMetaData.getImportedK...
Mysql4种方式避免重复插入数据！
JAVA葵花宝典
09-16 976
作者：小小猿爱嘻嘻wukong.com/question/6749061190594330891/最常见的方式就是为字段设置主键或唯一索引，当插入重复数据时，抛出错误，程序终止，但这会给...
clob取sql
xionglang7的专栏
01-22 1138
declare   l_cursor   integer := dbms_sql.open_cursor;   l_n        number;   l_long_val varchar2(32767);   l_long_len number;   l_curpos   number;   r_path     varchar2(100) := 'SQL_DIR';  /*需要在
MySQL数据库深度解析：索引、事务与存储引擎
本文档涵盖了MySQL数据库的基础知识，包括数据存储方式、SQL语言、数据库设计、存储引擎、索引、事务处理、锁机制、视图、游标、存储过程、触发器以及常用的SQL语句。以下是对这些知识点的详细说明： 1. **为什么要...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值