【MySQL】数据库之索引特性，事务管理，视图与用户管理

最新推荐文章于 2024-05-10 21:21:11 发布

小张在努力

最新推荐文章于 2024-05-10 21:21:11 发布

阅读量143

点赞数

分类专栏： MySQL数据库

本文链接：https://blog.csdn.net/qq_43464809/article/details/97416319

版权

MySQL数据库专栏收录该内容

11 篇文章 1 订阅

订阅专栏

索引特性、事务管理、视图、用户管理

索引：

提高数据的检索速度

常见索引：

主键索引
唯一索引
普通索引
全文索引

创建主键索引

主键索引的特点:

一个表中，最多有一个主键索引，当然可以使符合主键
主键索引的效率高（主键不可重复）
创建主键索引的列，它的值不能为null，且不能重复
主键索引的列基本上是int

第一种方式
创建表的时候，直接在字段名后指定

primary key create table user1(id int primary key,name varchar(30));

第二种方式

在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30),primary key(id));

第三种方式

create table user3(id int, name varchar(30));

创建表以后再添加主键

alter table user3 add primary key(id);

普通索引的创建

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

第一种方式

在表的定义最后，指定某列为索引

create table user8(
   id int primary key, 
   name varchar(20), 
   email varchar(30), 
   index(name) 
);

第二种方式

创建完表以后指定某列为普通索引

create table user9(
    id int primary key, 
    name varchar(20), 
    email varchar(30)
); 


alter table user9 add index(name);

第三中方式

创建一个索引名为 idx_name 的索引

create table user10(
   id int primary key,
    name varchar(20), 
    email varchar(30)
); 


create index idx_name on user10(name);

全文索引的创建

注意：

要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。

创建全文索引

select * from articles where body like '%database%';

通过explain分析sql语句

explain select * from articles where match (title,body) against ('database')\G

查询索引

第一种方法：

show keys from 表名

第二种

show index from 表名;

第三种

desc 表名；

删除索引

第一种方法-删除主键索引：

alter table 表名 drop primary key;

第二种方法-其他索引的删除：

alter table 表名 drop index 索引名；索引名就是show keys from 表名中的 Key_name 字段

alter table user10 drop index idx_name;

第三种方法方法： drop index 索引名 on 表名

drop index name on user8;

创建索引原则

比较频繁作为查询条件的字段应该创建索引
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
更新非常频繁的字段不适合作创建索引
不会出现在where子句中的字段不该创建索引

事务管理（重点）

事物就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。

事务基本操作

创建测试表

create table account( id int primary key, name varchar(50) 
not null default '', balance decimal(10, 2) not null default 0.0 );

开启一个事物

start transaction;

创建一个保存点

savepoint 保存点名；

回到保存点
提交事物

commit

事物操作注意事项

如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
如果一个事务被提交了（commit），则不可以回退（rollback）
可以选择回退到哪个保存点
InnoDB支持事务，MyISAM不支持事务
开始事务可以使 start transaction

事物的隔离级别

当MySQL表被多个线程或者客户端开启各自事务操作数据库中的数据时，MySQL提供了一种机制，可以让不同的事务在操作数据时，具有隔离性。从而保证数据的一致性。

无隔离性问题

脏读

是指当一个事务正在访问数据，并对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

一个事物读到了另外一个事物没有提交的数据

不可重复读

是指在一个事务内，多次读同一数据

幻读

是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

注：不可重复读的重点是修改：同样的条件, 你读取过的数据,再次读取出来发现值不一样了幻读的重点在于新增或者删除：同样的条件, 第1次和第2次读出来的记录数不一样

事务隔离级别

设置事务隔离级别

set session transaction isolation level read uncommitted;

查看当前的隔离级别:

select @@tx_isolation;

事物的ACID特性

原子性

事务是应用中最小的执行单位，具有不可再分的特征，要么成功，要么失败。

一致性

事务执行的结果，必须使数据库从一个一致性状态，变到另一个一致性状态。此时数据库就处于一种不正确（不一致）的状态。

隔离性

并发执行的事务之间不能看到对方的中间状态，并发执行的事务之间不能互相影响。

持久性

持久性是指一个事务一旦被提交，它对数据库所做的改变都要记录到永久存储其中（如：磁盘）。

视图

是一个虚拟表，内容由查询定义。和真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。