mysql索引、事务、存储、引擎的应用

最新推荐文章于 2024-08-07 00:15:00 发布

H . C . Y

最新推荐文章于 2024-08-07 00:15:00 发布

阅读量210

点赞数

分类专栏：数据库服务文章标签： mysql 数据库

本文链接：https://blog.csdn.net/HCY_2315/article/details/105578316

版权

数据库服务专栏收录该内容

15 篇文章 1 订阅

订阅专栏

1、索引

1）索引的作用：

设置合理的引擎后，数据库利用各种快速定位的技术，大大加快查询的速度特别是表很大的时候，或者涉及到多个表时，使用索引可使查询加快成千倍
可以降低数据库I/O成本，并且索引还可以降低数据库的排序成本
通过创建唯一性索引保证数据的唯一性,可以加快表与表之间的连接,在使用分组和排序时，可大大减少分组和排序时间
2）索引的类型
普通索引：最基本的索引类型，没有唯一性之类的限制
唯一索引：它和普通索引基本相同，区别：索引列的所有值都只能出现一次，
事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避
免数据出现重复
主键：是一种唯一索引，但必须指定为“primary key”

全文索引：索引的类型为fulltext，全文索引可以在varchar或text类型的列
上创建，用户查询一个数据的次数越多，速度越快

单列索引和多列索引：索引可以是单列上的索引，也可以是多列上的索引，允许引用相同的索引值
3）创建索引的原则
表的主键、外键必须有索引
数据量超过300行的表应该有索引
经常与其他表进行连接的表，在连接字符上应该建立索引
唯一性太差的列不建议建立索引
更新太频繁的列不建议创建索引
经常出现在where命令中的列，特别是大表的字段
索引应该建立在选择性高的字段上
索引所在列的数据越短越好

4）创建普通索引
mysql> create index 学历_index on it(学历); 将it表中的学历设置为普通索引，名字为学历_index
5）创建唯一索引，一张表可以有多个唯一索引，创建主键的时候不需要单独在创建唯一索引
mysql> create unique index 姓名_unique on it(姓名); 将名字创建为唯一索引
6）查看it表中的索引信息
mysql> show index from it; 查看的内容相同
mysql> show keys from it;
7）删除创建的索引
mysql> drop index 姓名_unique on it;

2、事务

1）事务的作用

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

2）事务的ACID特点
原子性（Atomicity）：

事务是一个完整的操作，事务的各元素是不可分割的
事务中的所有元素必须作为一个整体提交或者回滚
如果事务中的任何元素失败，则整个事务将失败
一致性（Consistency）：

当事务完成时，数据必须处于一致的状态：在事务开始之前，数据库中存储的数据处于一致的状态；在正在进行的事务中，数据可能处于不一致的状态；当事务成功完成时，数据必须再次回到已知的一致状态
隔离性（Isolation）：

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

事务持久性指不管系统是否发生故障，事务处理的结果都是永久的，一旦事务被提交，事务的效会被永久地保留在数据库中
3）mysql提交事务的方式
mysql> set autocommit=0; 关闭自动提交
mysql> set autocommit=1; 开启自动提交，数据库默认的方式
4）手动运行事务处理数据
mysql> begin; 启动事务
mysql> insert into users value (‘关羽’,‘pwd@123’); 插入数据
mysql> commit; 提交事务，并结束，能看到插入的数据
5）手动回滚
mysql> begin; 启动事务
mysql> insert into users value (‘张飞’,‘pwd@123’); 插入数据
mysql> rollback; 提交事务，并结束，无法看到插入的数据

3、数据库引擎

1）存储引擎的概念
○ mysql中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平，并最终提供不同的功能和能力，这些不同的技术以及配套的功能在mysq中成为存储引擎
○ 存储引擎就是mysql将数据存储在文件系统中的存储方式和存储格式
○ MySQL存储引擎是MySQL数据库服务器中的组件，负责为数据库执行实际的数据I/O操作
○ 使用特殊存储引擎的主要优点之一在于，仅需提供特殊应用所需的特性，数据库中的系统开销较小，具有更有效和更高的数据库性能
○ MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储
2）mysql处理数据引擎的类型
MYISAM
InnoDB
ISAM
3）ISAM引擎
mysql5.5版本前使用的默认引擎
ISAM执行读取速度快
不占用大量的内存和存储资源
它不支持事务处理
不能够容错
3）MYISAM
○ 不支持事务和容错功能
○ 读取速度块，占用内存等资源相对少
○ 可以高并发访问，适合web使用
○ 数据库读写过程中相互阻塞

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

○ 可通过key_buffer_size来设置混村索引，提高访问性能，减少磁盘I/O的压力

但缓存只会缓存索引文件，不会缓存数据

○ MyISAM存储引擎它不支持外键约束，只支持全文索引
○ 每个MyISAM在磁盘上存储成三个文件，每一个文件的名字以表的名字开始，扩展名指出文件类型
○ MyISAM在磁盘上存储的文件

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

○ 适合的生产场景

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

4）InnoDB
○ 支持事务：支持4个事务隔离级别
○ 行级锁定，但是全表扫描仍然会是表级锁定
○ 读写阻塞与事务隔离级别相关
○ 具有非常高效的缓存特性：能缓存索引，也能缓存数据
○ 表与主键以簇的方式存储
○ 支持分区、表空间，类似oracle数据库
○ 支持外键约束，5.5以前不支持全文索引，5.5版本以后支持全文索引
○ 对硬件资源要求还是比较高的场合
○ 适合的生产场景
业务需要事务的支持
行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成
业务数据更新较为频繁的场景，如：论坛，微博等
业务数据一致性要求较高，例如：银行业务
硬件设备内存较大，利用Innodb较好的缓存能力来提高内存利用率，减少磁盘IO的压力

4、查看和修改索引

1）查看特定表的索引类型
mysql> show create table 地址;
mysql> mysql> show table status from auth where name=‘地址’;
2）查看mysql数据库支持的引擎
mysql> show engines;
3）修改auth库中的地址表的引擎为myisam
mysql> alter table auth.地址 engine=myisam;
4）修改默认的存储引擎
[root@mysql ~]# vim /etc/my.cnf
7 [mysqld]
9 default-storage-engine=myisam
[root@mysql ~]# systemctl restart mysqld
5）将it表的存储引擎转化成myisam（库和表的名字不能是中文）
[root@mysql ~]# yum -y install perl-DBI perl-DBD-mysql
[root@mysql ~]# mysql_convert_table_format --host=localhost --user=root --password=pwd@123 --sock=/tmp/mysql.sock --engine=Innodb auth it;