通俗易懂！数据库的索引，事务，存储引擎的作用及用法

最新推荐文章于 2024-06-01 23:11:54 发布

原创最新推荐文章于 2024-06-01 23:11:54 发布 · 618 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Mysql 专栏收录该内容

13 篇文章

订阅专栏

本文详细介绍了数据库索引的概念、作用及分类，探讨了索引的优缺点，并提供了创建和管理索引的方法。此外，还讲解了事务的基本概念、特征及相关的SQL语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

一、索引

1.1：什么是索引（index)?

数据库中的索引与书籍中的目录类似
在一本书中，无须阅读整本书，利用目录就可以快速査找所需信息
书中的目录是一个词语列表，其中注明了包含各个词的页码
数据库索引
在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据
数据库中的索引是某个表中一列或者若干列值的集合，以及物理标识这些值的数据页的逻辑指针清单

1.2：索引有什么用？

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率
特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成干倍
可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本
通过创建唯一性索引保证数据表数据的唯一性
可以加快表与表之间的连接
在使用分组和排序时，可大大减少分组和排序时间

1.3：索引的优缺点是什么？

优点
可以快速的找到所需要的的资源
缺点
占用空间

1.4：索引的分类

普通索引
这是最基本的索引类型，而且它没有唯一性之类的限制
唯一性索引
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一（唯一索引是可以为空值的，但是只能出现一次)
主键
主键是一种唯一性索引，但它必须指定为“ PRIMARY KEY
全文索引
MySQL从32323版开始支持全文索引和全文检索。在 MySQL中全文索引的索引类型为 FULLTEXT，全文索引可以在 ARCHAR或者TEXT类型的列上创建
单列索引与多列索引
索引可以是单列上创建的索引，也可以是在多列上创建的索引

1.5：创建索引有什么原则依据?

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

1.6：什么是外键？

主表中的外键是令一张表的主键

1.7：如何创建索引？

准备环境

mysql -u root -p   '//登录数据库'
show databases;    '//查看数据库'
create database school; '//创建数据库school'
use school;  '//进入school数据库'

***创建info表***
mysql> create table info (
 -> id int(4) not null primary key auto_increment,
 -> name varchar(10) not null,
 -> address varchar(50) default 'nanjing',
 -> age int(3) not null);

***info表中插入数据***
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
查询表里的数据
mysql> select * from info;

- 创建普通索引

第一种方法直接创建索引

mysql> create index index_age on info (age);	'//针对info表中，age 创建索引'
mysql> show index from info; '//查看表中的索引'

第二种方法用alter创建普通索引

mysql> alter table info add index index_age (age);

第三种创建表的时候创建普通索引

mysql> create table user (
-> id int(4) not null primary key auto_increment,
-> name varchar(10) not null,
-> score decimal not null,
-> hobby int(2) not null default '1',
-> index index_scrore (score));

- 删除普通索引

mysql> drop index index_age on info;    '//删除索引'
mysql> show index from info; '//查看索引，发现索引已经删除'

- 创建唯一索引

用create unique方法，创建唯一索引

mysql> create unique index unique_name on info (name);

使用alter table方法创建唯一索引

alter table info add unique index_name (name);

- 删除唯一索引

drop index unique_name on info;

- 主键索引

mysql> create table user2 (
 -> id int(4) not null  auto_increment,
 -> name varchar(10) not null,
 -> age int(3) not null,
 -> primary key (`id`));

- 组合索引

如果一次访问很多数据的话，单列索引响应时间就会比较慢，如果设置成多列索引就不会，将多个单列索引组合成一个多列索引，满足需求。

mysql> create table user3 (
-> name varchar(10) not null,
-> age int(3) not null,
-> sex tinyint(1) not null,
-> index user3(name,age,sex));

查看组合索引

发现key_name统统是一样的

mysql> show keys from user3;
| user3 |          1 | user3    |            1 | name        | A         |           0 |     NULL | NULL   |      | BTREE
| user3 |          1 | user3    |            2 | age         | A         |           0 |     NULL | NULL   |      | BTREE
| user3 |          1 | user3    |            3 | sex         | A         |           0 |     NULL | NULL   |      | BTREE   
索引名一样能实现批量调用

创建全文索引

创建表时创建全文索引

mysql> create table user4 (
 -> id int(11) not null auto_increment,
 -> tile char(255) character set utf8 collate utf8_general_ci not null,
 -> content text character set utf8 collate utf8_general_ci not null,
 -> primary key (`id`),
 -> fulltext (content));  '//给文章内容进行索引'（注：这边的文章内容是类似于一个整体）

mysql> show keys from user4;
| user4 |          1 | content  |            1 | content     | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |

用alter创建全文索引

mysql>alter table user4 add fulltext index_content(content);

删除全文索引

 drop index content on user4; '//删除全文索引'

二、事务

2.1：事物的定义

Transaction
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)
一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成
事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同

2.2：事物四大特征（ACID）

原子性(A)：事务是最小单位，不可再分
一致性©：事务要求所有的DML语句操作的时候，必须保证同时成功或者同时失败
隔离性(I)：事务A和事务B之间具有隔离性
持久性：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)

2.3：关于事物的一些术语

开启事务：Start Transaction
事务结束：End Transaction
提交事务：Commit Transaction
回滚事务：Rollback Transaction

2.4：和事务相关的两条重要的SQL语句(TCL)

commit : 提交
rollback : 回滚

2.5：举例

mysql>begin;    '//开始事务'
mysql> insert into info (name,address,age) values ('wangwu','beijing',15);
mysql> select * from info;
mysql> savepoint a;  '//在上一条数据添加后 设置一个标记'
mysql> insert into info (name,address,age) values ('zhaoliu','beijing',16);
mysql> savepoint b;
mysql> select * from info;
mysql> rollback to a;  '//返回到标记时的那个状态，类似于快照'
mysql> select * from info;
mysql> rollback to b  '//因为上面已经回到a的状态了，当时还没有标记b，所以回滚不回去'
mysql>commit;	'//提交事务'

三.存储引擎

3.1：什么是存储引擎？

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

存储引擎就是 MySQL将数据存储在文件系统中的存储方式或者存储格式
目前 MySQL常用的两种存储引擎
MyISAM
InnoDB
MySQL存储引擎是 MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作（输出/输入操作)
使用特殊存储引擎的主要优点之一在于：
仅需提供特殊应用所需的特性
数据库中的系统开销较小
具有更有效和更高的数据库性能
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储

3.2：MyISAM

3.2.1:什么是MyISAM？

MyISAM存储引擎是 MySQL关系数据库系统5.5版本之前默认的存储引擎，前身是ISAM
ISAM是一个定义明确且历经时间考验的数据表格管理方法，在设计之时就考虑到数据库被查询的次数要远大于更新的次数
ISAM的特点
优点：ISAM执行读取操作的速度很快
优点：不占用大量的内存和存储资源
缺点：不支持事务处理
缺点：不能够容错
MyISAM管理非事务表，是lSAM的扩展格式
提供ISAM里所没有的索引和字段管理的大量功能
MyISAM使用一种表格锁定的机制，以优化多个并发的读写操作
MyISAM提供高速存储和检索，以及全文搜索能力，受到web开发的青睐

3.2.2:MyISAM有什么特点？

不支持事务
表级锁定形式，数据在更新时锁定整个表
数据库在读写过程中相互阻塞
会在数据写入的过程阻塞用户数据的读取
也会在数据读取的过程中阻塞用户的数据写入
可通过key_buffer_size来设置缓存索引，提高访问性能，减少磁盘I/O的压力
但缓存只会缓存索引文件，不会缓存数据
釆用 MyISAM存储引擎数据单独写入或读取，速度过程较快且占用资源相对少
MyISAM存储引擎它不支持外键约束，只支持全文索引
每个 MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型
MyISAM在磁盘上存储的文件
frm文件存储表定义
数据文件的扩展名为.MYD（ MYData）
索引文件的扩展名是.MYI（ MYIndex)

3.2.3:什么生产场景适合使用MyISAM

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

3.3:InnoDB

3.3.1:InnoDB有什么特点？

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

3.3.2:什么生产场景适合使用InnoDB？

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

3.4:配置存储引擎

查看数据库默认引擎

mysql> show engines; '//查看数据库的默认引擎'

修改表的存储引擎

mysql> alter table info engine=MyISAM;

查看表存储引擎

mysql> show create table info \G

进入这个配置文件，修改数据库的引擎

vi /etc/my.cnf
default-storage-engine=MyISAM
systemctl restart mysqld  '//重启数据库'