MySQL索引、事务与存储引擎

最新推荐文章于 2021-04-13 15:27:57 发布
最新推荐文章于 2021-04-13 15:27:57 发布
阅读量88 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuqingyunCSDN/article/details/111625748
版权

文章目录

一、索引的概念

1.1 数据库索引

  • 是一个排序的列表，存储着索引值和这个值所对应的物理地址
  • 无需对整个表进行扫描，通过物理地址就可以找到所需数据
  • 是表中一列或者若干列值排序的方法
  • 需要额外的磁盘空间

1.2 索引的作用

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

1.3 索引的分类

普通索引、唯一索引、主键索引、组合索引、全文索引
索引创建的方法：
1）、直接创建索引
2）、修改表结构方式添加索引
3）、创建表结构时创建索引

1.3.1 普通索引

1）直接创建

mysql> create index id on student(id);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.student;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY  |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | id       |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

2）创建表时创建

mysql> create table teacher(id int(2) not null primary key, name char(16),adress int(10),index id_index(id));
Query OK, 0 rows affected (0.01 sec)
mysql> show index in school.teacher;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | id_index |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

3）修改表结构方式创建

mysql> alter table student add index name_index(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 mysql> show index in school.student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | id         |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | name_index |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

1.3.2 唯一索引

  • 与“普通索引”基本相同
  • 与普通索引的区别是索引列的所有值只能出现一次，即必须唯一
  • 创建唯一索引的方式

1）直接创建

mysql> create unique index adress on teacher(adress);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.teacher;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          0 | adress   |            1 | adress      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| teacher |          1 | id_index |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2）创建表时创建

mysql> create table lisi(studynum char(10),name char(16) not null primary key,adress ,adress int(10),unique idexName(studynum));
Query OK, 0 rows affected (0.01 sec)
mysql> show index in school.lisi;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lisi  |          0 | PRIMARY  |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| lisi  |          0 | idexName |            1 | studynum    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3）修改表结构创建

mysql> alter table lisi add unique index adress_index(adress);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.lisi;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lisi  |          0 | PRIMARY      |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| lisi  |          0 | idexName     |            1 | studynum    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| lisi  |          0 | adress_index |            1 | adress      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1.3.3 主键索引

  • 主键索引是一种特殊的唯一索引，指定为“PRIMARY KEY”
  • 一个表只能有一个主键，不允许有空值

1）创建表时创建

 mysql> create table zhangsan(studynum int(2) not null primary key,name char(48),adress 8),adress int(10));
Query OK, 0 rows affected (0.01 sec)

mysql> create table wangwu(studynum int(2) not null, name char(48),adress int(10),primary key(studynum));
Query OK, 0 rows affected (0.01 sec)

 mysql> show index in school.wangwu;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| wangwu |          0 | PRIMARY  |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2）修改表时创建

mysql> create table xiaoming(studynum int(2) not null,name char(48),adress int(10));
Query OK, 0 rows affected (0.01 sec)   //创建一个新的表
mysql> alter table xiaoming add primary key(studynum);  //修改表结构创建主键索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.xiaoming;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xiaoming |          0 | PRIMARY  |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1.3.4 组合索引

  • 可以是单列上创建的索引，也可以是在多列上创建的索引
  • 最左原则，从左往右依次执行
mysql> create table xiaohong(id int(2) not null, name char(48),adress int(10),index xiaohong(id,name,adress));
Query OK, 0 rows affected (0.01 sec)

mysql> show index in school.xiaohong;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xiaohong |          1 | xiaohong |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| xiaohong |          1 | xiaohong |            2 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| xiaohong |          1 | xiaohong |            3 | adress      | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

1.3.5 全文索引

  • MySQL从3.23.23版开始支持全文索引和全文检索
  • 索引类型为FULLTEXT
  • 可以在CHAR、VARCHAR或者TEXT类型的列上创建

1）创建表时创建全文索引

mysql> create table xiaobai(id int(2) not null,name char(48) primary key,adress int(10),fulltext (name));
Query OK, 0 rows affected (0.03 sec)
mysql> show index in school.xiaobai;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xiaobai |          0 | PRIMARY  |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| xiaobai |          1 | name     |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

2）在已存在的表上创建全文索引

mysql> create fulltext index name_index on xiaobai(name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.xiaobai;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xiaobai |          0 | PRIMARY    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| xiaobai |          1 | name       |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
| xiaobai |          1 | name_index |            1 | name        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

3）通过SQL语句alter table创建全文索引

mysql> alter table student add fulltext index studynum_index(studynum);
Query OK, 0 rows affected, 1 warning (0.36 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index in school.student;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY        |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | id             |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | name_index     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | studynum_index |            1 | studynum    | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1.4 创建索引的原则依据

  • 表的主键，外键必须有索引
  • 记录数超过300行的表应该有索引
  • 经常与其他表进行连接的表，在连接字段上应该建立索引
  • 唯一性太差的字段不合适建立索引
  • 更新太频繁的字段不适合创建索引
  • 经常出现在where子句中的字段，特别是大表的字段，应该建立索引
  • 索引应该建在选择性高的字段上
  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

1.5 查看索引语法

SHOW INDEX FROM table_name;
SHOW KEYS FROM table_name;

查看索引的实例

show index in school.student;
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY        |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | id             |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | name_index     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | studynum_index |            1 | studynum    | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

1.6 删除索引语法

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP INDEX index_name;

删除索引的示例：

mysql> drop index studynum_index on student;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index in school.student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | id         |            1 | id          | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| student |          1 | name_index |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+


mysql> alter table student drop index id;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
show index in school.student;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| student |          0 | PRIMARY    |            1 | studynum    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| student |          1 | name_index |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

二、事务

2.1 事务的概念

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

2.2 事务的ACID特点

  • 原子性（Atomicity）
    • 事务是一个完整的操作，事务的各元素是不可分的
    • 事务中的所有元素必须作为一个整体提交或回滚
    • 如果事务中的任何元素失败，则整个事务将失败
  • 一致性（Consistency）
    • 当事务完成时，数据必须处于一致状态
    • 在事务开始前，数据库中存储的数据处于一致状态
    • 在正在进行的事务中，数据可能处于不一致的状态
    • 当事务成功完成时，数据必须再次回到已知的一致状态
  • 隔离性（LsoLation）
    • 对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务
    • 修改数据的事务可在另一个相同数据的事务开始之前访问这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
  • 持久性（Durablilty）
    • 指不管系统是否发生故障，事务处理的结果都是永久的
    • 一旦事务被提交，事务的效果会被永久的保留在数据库中

2.3 事务控制语句

  • MySQL事务默认是自动提交的，当SQL语句提交时事务便自动提交
  • 事务控制语句
    • BEGIN或START TRANSACTION //开始一个事务
    • COMMIT // 提交
    • ROLLBACK // 回滚
    • SAVEPOINT identifier // 创建保存点
    • RELEASE SAVEPOINT identifier //删除保存点
    • ROLLBACK TO identifier // 回滚到指定保存点
    • SET TRANSACTION //设置事务的隔离级别

2.4 事务的控制方法

  • 手动对事务进行控制的方法
    • 事务处理命令控制事务
      • bebin：开始一个事务
      • commit：提交一个事务
      • rollback：回滚一个事务
    • 使用set命令进行控制
      • set autocommit=0：禁止自动提交
      • set autocommit=1：开启自动提交
mysql> begin;   //开始一个事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student value(1,'lisi',10);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student value(2,'xiaobai',20);
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into student value(3,'xiaoming',30);
Query OK, 1 row affected (0.00 sec)

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

mysql> select * from student;
+------+----------+----------+
| id   | name     | studynum |
+------+----------+----------+
|    1 | lisi     | 10       |
|    2 | xiaobai  | 20       |
|    3 | xiaoming | 30       |
+------+----------+----------+
3 rows in set (0.00 sec)

mysql> rollback to a;  //回滚一个事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
+------+---------+----------+
| id   | name    | studynum |
+------+---------+----------+
|    1 | lisi    | 10       |
|    2 | xiaobai | 20       |
+------+---------+----------+
2 rows in set (0.00 sec)

mysql> rollback to b;  //回滚一个事务b，但是只能向前回滚，无法向后回滚，所以会出现报错
ERROR 1305 (42000): SAVEPOINT b does not exist
mysql> commit;				//结束一个事务
Query OK, 0 rows affected (0.01 sec)

三、存储引擎

3.1 存储引擎的概念介绍

  • MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎
  • 存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式
  • MySQL常用的存储引擎
    • MyISAM
    • InnoDB
  • MySQL数据库中的组件，负责执行实际的数据I/O操作
  • MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

3.2 MyISAM的介绍

  • MyISAM不支持事务，也不支持外键
  • 访问速度快
  • 对书屋完整性没有要求
  • MyISAM在磁盘上存储成三个文件
    • .frm文件存储表定义
    • 数据文件的扩展名为.MYD（MYData）
    • 索引文件的扩展名为.MYI（MYIndex）
  • 表级锁定形式，数据在更新时锁定整个表
  • 数据库在读写过程中相互阻塞
    • 会在数据写入的过程阻塞用户数据的读取
    • 也会在数据读取的过程中阻塞用户数据的写入
  • 数据单独写入或读取，速度过程较快且占用资源相对少

3.3 - MyISAM支持的存储格式

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

3.4 MyISAM适用的生产场景举例

  • 公司业务不需要事务的支持
  • 单方面读取或写入数据比较多的业务
  • MyISAM存储引擎数据读写都比较频繁场景不适合
  • 使用读写并发访问相对较低的业务
  • 数据修改相对较少的业务
  • 对数据业务一致性要求不是非常高的业务
  • 服务器硬件资源相对比较差

3.5 InonDB特点介绍

  • 支持4个事务隔离级别
  • 行级锁定，但是全表扫描仍然会是表级锁定
  • 读写阻塞与事务隔离级别相关
  • 能非常搞笑的缓存索引和数据
  • 表与主键以簇的方式存储
  • 支持分区、表空间、类似Oracle数据库
  • 支持外键的约束，5.5前不支持全文索引，5.5后支持全文索引
  • 对硬件资源要求还是比较高的场合

3.6 InnoDB使用生产场景分析

  • 业务需要事务的支持
  • 行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
  • 业务数据更新较为频繁的场景
    如：论坛、微博等
  • 业务数据一致性要求较高
    如：银行业务
  • 硬件设备内存较大（因为事务都先放内存），利用innodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

3.7 企业选择存储引擎依据

  • 需要考虑每个存储引擎提供的核心功能及应用场景
  • 支持的字段和数据类型
    所有引擎都支持通用的数据类型
    但不是所有的引擎都支持其它的字段类型，如二进制对象
  • 锁定类型：不同的存储引擎支持不同级别的锁定
    表锁定
    行锁定

3.8 修改存储引擎

3.8.1 alter table 修改

未修改之前：

mysql> mysql> show table status from school;
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| lisi     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2020-12-24 14:58:24 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| student  | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |        32768 |         0 |           NULL | 2020-12-24 15:28:53 | 2020-12-24 15:42:43 | NULL       | utf8_general_ci |     NULL |                |         |
| teacher  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2020-12-24 14:48:41 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| wangwu   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-24 15:03:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaobai  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        32768 |         0 |           NULL | 2020-12-24 15:19:51 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaohong | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |        16384 |         0 |           NULL | 2020-12-24 15:12:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaoming | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-24 15:08:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| zhangsan | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2020-12-24 15:01:55 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+

修改zhangshan存储引擎为MyISAM

mysql> alter table zhangsan engine=myisam;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改之后：

mysql> show table status from school;
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| lisi     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 14:58:24 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| student  | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |                 0 |        32768 |         0 |           NULL | 2020-12-24 15:28:53 | 2020-12-24 15:42:43 | NULL       | utf8_general_ci |     NULL |                |         |
| teacher  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 14:48:41 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| wangwu   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2020-12-24 15:03:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaobai  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        32768 |         0 |           NULL | 2020-12-24 15:19:51 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaohong | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 15:12:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaoming | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2020-12-24 15:08:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| zhangsan | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 43065671436730367 |         1024 |         0 |           NULL | 2020-12-24 15:52:32 | 2020-12-24 15:52:32 | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
8 rows in set (0.00 sec)

3.8.2 修改my.cnf，指定默认存储引擎并重启服务

在/etc/my.cfg配置文件棕修改[mysqld]，下面添加default-storage-engine=InnoDB，然后重启服务

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

mysql> create table xusi(id int(2) not null primary key,name char(48),adress int(10)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)
mysql> show table status from school;
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| lisi     | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 14:58:24 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| student  | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |                 0 |        32768 |         0 |           NULL | 2020-12-24 15:28:53 | 2020-12-24 15:42:43 | NULL       | utf8_general_ci |     NULL |                |         |
| teacher  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 14:48:41 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| wangwu   | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2020-12-24 15:03:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaobai  | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        32768 |         0 |           NULL | 2020-12-24 15:19:51 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaohong | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |        16384 |         0 |           NULL | 2020-12-24 15:12:05 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xiaoming | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |                 0 |            0 |         0 |           NULL | 2020-12-24 15:08:15 | NULL                | NULL       | utf8_general_ci |     NULL |                |         |
| xusi     | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 43065671436730367 |         1024 |         0 |           NULL | 2020-12-24 15:55:45 | 2020-12-24 15:55:45 | NULL       | utf8_general_ci |     NULL |                |         |
| zhangsan | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 43065671436730367 |         1024 |         0 |           NULL | 2020-12-24 15:52:32 | 2020-12-24 15:52:32 | NULL       | utf8_general_ci |     NULL |                |         |
+----------+--------+---------+------------+------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
9 rows in set (0.00 sec)
liuqingyunCSDN
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL 索引--事务--存储引擎 详解
CSDN著名博主
09-10 540
文章目录一、索引1.1、索引的概念1.2、索引的作用1.3、索引的分类1.3.1、普通索引1.3.2、唯一索引1.3.3、主键索引1.3.4、组合索引1.3.5、全文索引1.4、创建索引的原则依据1.5、索引的查看1.6、索引的删除二、事务2.1、事务的概念2.2、事务的ACID 特点2.2.1、原子性（Atomicity）2.2.2、一致性（Consistency）2.2.3、隔离性（Isolation）2.2.4、持久性（Durability）2.3、事务的操作2.3.1、事务控制语句2.3.2、事务示
MySQL事务与存储引擎相关基础命令与理论
Gengchenchen的博客
01-27 1224
文章目录一、MySQL 事务（一）、事务的概念（二）、事务的 ACID 特点（三）、事务控制语句（四）、使用 set 设置控制事务二、MySQL存储引擎（一）、存储引擎概念介绍（二）、MyISAM的特点介绍（三）、MyISAM 适用的生产场景举例（四）、InnoDB 特点介绍（五）、InnoDB适用生产场景分析（六）、企业选择存储引擎依据（七）、相关命令 一、MySQL 事务 （一）、事务的概念 1、事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作
MySQL索引，事务与存储引擎的相关知识
不知道是谁的博客
04-13 283
文章目录一.MySQL索引（一）索引的概念（二）索引的作用索引的副作用（三）创建索引的原则依据（mysql的优化）二.索引的分类和创建1.普通索引2.唯一索引3.主键索引4.组合索引（单列索引与多列索引）5.全文索引6.查看索引7.删除索引三.MySQL事务（一）事务的概念(二)事务的ACID特点1.ACID2.特点（三）事务之间相互影响的分类（四）MySQL及事务隔离级别 一.MySQL索引 （一）索引的概念 索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语
MYSQL——事务操作+视图+存储引擎
huhaiyangFIVE的博客
11-02 200
目录一、事务的概念二、事务的ACID特点三、事务的操作 一、事务的概念 事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。 事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。 适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。 通过事务的整体性以保证数据的一致性。 二、事务的ACID特点 原子性(Atomicity) 事务是一个完整的
MySQL数据库备份、事务、存储引擎
cly_32的博客
04-05 235
1、语法备份 备份：mysqldump -u用户名 -p密码 > 保存路径 还原： 1、登录数据库 mysql -u用户名 -p密码; 2、创建数据库 create database 数据库名称; 3、使用数据库 use 数据库名称; 4、执行文件 source 文件路径 2、图形化工具备份 备份：选中数据库右键备份导出 还原：执行sql脚本 事务： 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作步骤要么同时成功要么同时失败。 操作步骤： 1、开启事务：start transaca
Mysql InnoDB引擎的索引与存储结构详解
12-16
而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。 MySQL主要存储引擎的区别 MySQL默认的存储引擎是MyISAM，其他常用的就是...
MySQL存储引擎 InnoDB与MyISAM的区别
09-10
MySQL中的InnoDB和MyISAM是两种非常重要的存储引擎，它们各自有着独特的特性和适用场景。InnoDB以其对事务处理和行级锁定的支持而受到重视，而MyISAM则以其高性能和简单性受到青睐。 InnoDB存储引擎的核心优势在于...
MySQL常用存储引擎功能与用法详解
09-09
MySQL存储引擎是数据库管理系统的核心组件，它决定了数据的存储方式、事务处理机制以及查询效率等方面。本篇文章将详细解析MySQL中的几种常见存储引擎，包括它们的功能、用途和操作注意事项。 首先，MySQL的存储...
mysql架构与存储引擎（MySQL逻辑架构、InnoDB引擎、MyISAM引擎、存储引擎选择）.docx
最新发布
07-24
- **基础描述**：MyISAM是MySQL早期版本的默认存储引擎，不支持事务处理和行级锁，但提供了全文索引支持。 - **特点描述**： - **锁表机制**：在读取数据时使用共享锁，在写入数据时使用排他锁。 - **全文索引**...
MySql 索引、锁、事务知识点小结
09-09
MySQL中的索引、锁和事务是数据库管理中的关键概念，对于数据库性能和数据一致性有着深远影响。 **索引** 索引是一种数据结构，用于快速定位和访问数据库表中的数据。它们提高了查询速度，但同时也占用额外的存储...
快速定位，加快查询数据库速率的方法？--在数据库中操作保护？ ----Mysql数据库（索引！事务！存储引擎）
我命由我！不由天！
09-10 339
个人整理，感谢观看一，索引1.1概念1.2作用优点1.3分类二，创建索引的原则依据三，查看, 删除，索引的方法四，事务4.1概念4.2事务的ACID特点4.3事务控制语句五，存储引擎5.1介绍5.2 MyISAM5.3 MyISAM适用的生产场景5.4 InnoDB介绍5.5 适用生产场景分析5.6 企业选择存储引擎依据六，修改存储引擎方法 一，索引 1.1概念 排序的列表，索引值和这个值所对应的位置，物理地址； 方便查询，通过物理地址就可以找到所需数据； 表中一列或若干列值排序的方法； 占用额外的磁
JTA和JTS
xmh_sxh_1314的博客
09-16 1132
 JTA，即Java Transaction API，JTA允许应用程序执行分布式事务处理–在两个或多个网络计算机资源上访问并且更新数据。JDBC驱动程序的JTA支持极大地增强了数据访问能力。 JTA事务比JDBC事务更强大。一个JTA事务可以有多个参与者，而一个JDBC事务则被限定在一个单一的数据库连接。下列任一个Java平台的组件都可以参与到一个JTA事务中:JDBC连接、JDO PersistenceManager 对象、JMS 队列、JMS 主题、企业JavaBeans(EJB)、一个用J2E.
MySQL数据库引擎详解
wo4641771的博客
12-03 341
作为经常使用MySQL数据库的人，不得不对MySQL的数据库引擎有一定的了解，在之前我也是只会使用MySQL，但两次的面试中都被面试官问及项目用到的数据库引擎（血与泪），因此，有必要写一篇文章，好好总结一下MySQL的数据库引擎。 1. 数据库引擎定义 数据库引擎是数据库用于存储、处理和保护数据的核心服务，不同的数据库引擎有其各自的特点，如存储机制、索引技巧、主键的处理、锁的粒度等特点便随着引擎的不同而变化。因此，针对自己项目特点选择合适的数据库引擎可以改善服务器端存储性能。 常见的M...
mysql数据库表更新_MySQL更新表数据
weixin_39983051的博客
01-19 5087
更新数据是使用数据库时最重要的任务之一。 在本教程中，您将学习如何使用MySQL UPDATE语句来更新表中的数据。1. MySQL UPDATE语句简介我们使用UPDATE语句来更新表中的现有数据。也可以使用UPDATE语句来更改表中单个行，一组行或所有行的列值。下面说明了MySQL UPDATE语句的语法：UPDATE [LOW_PRIORITY] [IGNORE] table_nameSET...
更新mysql表
OYY_90的博客
09-25 215
CREATE TABLE new_table SELECT * FROM old_table CREATE TABLE 新表 SELECT * FROM 旧表 WHERE 1=2 insert into tb_item select * from tb_item CREATE TABLE new_table SELECT * FROM tb_item   https://mp.weixin...
MySQL批量更新表数据
qq_34377273的博客
09-15 243
UPDATE `db_smart_common`.`sys_project_info` spi, `db_sensor`.`dust_project_info` dpi SET spi.enterprise_project_manager = dpi.sgdwxmjl, spi.enterprise_project_manager_phone = dpi.sgdwxmjldh, spi.enterprise_safe_leader = dpi.qyaqfgld, spi.enterpri
MySQL 更新基础数据表
10-10 206
在PACS的MySQL数据库里，收费项目与HIS收费项目不能同步，需要手工更新。 一、使用INSERT INTO TABLE   ON DUPLICATE KEY UPDATE 语句，将数据直接更新。数据库会判断，如果主键存在，则更新相应字段，如果主键不存在，则插入该行数据。 相当于是 UPDATE 和 INSERT 的集合。 优点：语句执行失败不会影响业务。要执行的语句数少，减少失误。 ...
mysql更新一张表的数据到另一张表
qq_30908729的博客
05-07 2840
mysql更新一张表的数据到另一张表 解决方法： 例如，把tb_account表的ac_name字段的值更新到tb_account_channelgroup表（存有tb_account表的ac_id字段，也就是说tb_account是一方，tb_account_channelgroup是多方）的scg_name字段 sql:http://www.yayihouse.com/yayish...
mysql 更新系统表_快速升级MySQL系统表
weixin_29876033的博客
01-19 150
一些MySQL发布对MySQL数据库中的系统表的结构进行了更改，添加了新权限或特性。当你更新到新版本MySQL，你应同时更新系统表，以确保它们的结构最新。首先备份mysql数据库，然后按照下面的程序操作。 在Unix或Unix类系统中，运行mysql_fix_privilege_tables脚一些MySQL发布对MySQL数据库中的系统表的结构进行了更改，添加了新权限或特性。当你更新到新版本MyS...
MySQL存储引擎与索引优化笔记
"MySQL笔记-fxf，记录了MySQL的学习与复习要点，主要关注存储引擎、索引优化和性能调整。" 在MySQL数据库系统中，存储引擎是数据库管理数据的核心组件，不同的存储引擎有不同的特性和功能。InnoDB和MyISAM是最常见...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值