MySQL数据库之索引、事务

用户7103831970572
117 阅读9分钟

索引的作用

优点

  • 功能：查找索引后，直接指向数据所在的物理地址

  • 意义：加速查询速度，为字段排序

缺点

  • 1）索引需要占用额外的磁盘空间。

    • 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
    • 而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）

  • 2）在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

创建索引的原则依据

  • 表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是主表的主键，查询时可以快速定位。
  • 记录数超过300行（或500行）的表应该有索引。如果没有索引，每次查询都需要把表遍历一遍，会严重影响数据库的性能。（会影响IO和CPU的性能）
  • 经常与其他表进行连接的表，在连接字段上应该建立索引。
  • 唯一性太差的字段不适合建立索引。（因为字段值会有重复）
  • 更新太频繁地字段不适合创建索引。
  • 经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
  • 在经常进行GROUP BY、ORDER BY的字段上建立索引。
  • 索引应该建在选择性高的字段上。（即重复性低的字段）
  • 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。（大字段建索引会增加磁盘空间，而且搜索起来会影响性能）

注：

当一个表写入多、读取很少的时候，不需要建立索引。

唯一性太差的字段、更新太频繁地字段、大字段，不适合做索引。

应用场景

  • 唯一性较好的字段
  • 超过300行的字段
  • 经常表连接的字段
  • 不常更新的字段
  • 小字段

创建索引

1）普通索引：

最基本的索引类型，没有唯一性之类的限制


create index 索引名 on 表名 (字段);

alter table 表名 add index 索引名 (字段(4));

create table 表名 (字段 数据类型.... , index 索引名(字段));

2）唯一索引：

唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。 唯一索引 允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

创建唯一键或者创建唯一索引都可实现。


create unique index 索引名 on 表名 (字段);

alter table 表名 add unique 索引名(字段);

create table 表名 (字段 数据类型.... , unique 索引名(字段));

3）主键索引：

是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引

alter table 表名 add primary key (字段);

create table 表名 (字段 数据类型.... , primary key (字段));

create table 表名 (字段 数据类型primary key, ... );

4）组合索引（单列、多列索引）:

可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效


create index XXX_index on 表名 (字段1,字段2, ... , 字段n);

alter table 表名 add index XXX_index (字段1,字段2, ... , 字段n);

create table 表名(列名1 数据类型,列名2 数据类型,列名3 数据类型, INDEX 索引名(字段1,字段2,字段3));

#### 使用时要注意 where 的最左原则：

select * from 表名 where 字段1=XXX and 字段2=XXX and ....

5）全文索引：

适合在进行模糊查询的时候使用


create fulltext index 索引名 on 表名 (字段);

alter table 表名 add fulltext 索引名 (字段);

create table 表名 (字段 数据类型.... , fulltext 索引名(字段));

#### 查询时只能匹配完整的字符串：

select * from 表名 where match(字段) against('查询字符串');

删除索引

删除主键索引的方法：


alter table 表名 drop primary key;

删除其他索引：

drop index 索引名 on 表名;

alter table 表名 drop index 索引名;

查询表中有哪些索引


show index from 表名; //能查看索引的字段和细节，建议使用\G纵向查看

show keys from 表名;

show create table 表名; //只能查看索引的字段和名称

查询结果中，各字段的含义如下：

字段含义
Table表的名称
Non_unique如果索引不能包括重复词，则为0；如果可以，则为1
Key_name索引的名称
seq_in_index索引中的列序号，从1开始
column_name列名称
collation列以什么方式存储在索引中。在 MySQL中，有值"A"（升序）或 NULL（无分类）
Cardinality索引中唯一值数目的估计值
sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL
Packed指示关键字如何被压缩。如果没有被压缩，则为NULL
Null如果该列含有NULL值，则显示YES。如果没有，则显示NO或为空
lndex_type用过的索引方法（BTREE，FULLTEXT，HASH，RTREE）
comment备注

使用explain命令分析：

可以使用explain命令分析一下，这个 select语句是否使用了索引或者索引使用是否正确

事务的概念

所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。 事务用来管理 insert,update,delete 语句 主要用于处理操作量大，复杂度高的数据。

事务的ACID特点

原子性：事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性：指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

隔离性：指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务之间的相互影响

脏读：事务B读取了事务A修改的，还未提交的数据

不可重复度：事务B在事务A提交事务前后发现数据不一致，数据被修改

幻读：事务B提交事务修改了全部数据，事务A同时也提交事务插入了数据。操作前一个事务的用户会发现表中还有没有修改的数据行

丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录(B不知道A修改过)，B提交数据后B的修改结果覆盖了A的修改结果。

Mysql 及事物隔离级别

（1）read_uncommitted : 读取尚未提交的数据 ：不解决脏读

（2）read_committed：读取已经提交的数据 ：可以解决脏读

（3）repeatable_read：重读读取：可以解决脏读 和 不可重复读 —mysql默认的

（4）serializable：串行化：可以解决 脏读 不可重复读 和 虚读—相当于锁表

事务隔离级别脏读不可重复读幻读第一类丢失更新第二类丢失更新
read uncommitted允许允许允许禁止允许
read committed禁止允许允许禁止允许
repeatable read禁止禁止允许禁止禁止
serializable禁止禁止禁止禁止禁止

查询事务的隔离级别

查询全局事务隔离级别：

variables 指变量， global variables 指全局变量。

 show global variables like '%isolation%';   #方法一
 ​
 SELECT @@global.tx_isolation;               #方法二
 ​
 注释：
 like：表示模糊查询。
 '%isolation%'：表示包含isolation字符串。
 'isolation%'：以isolation字符串开头。
 '%isolation'：以isolation字符串结尾。

查询会话事务隔离级别：

session variables 指会话变量。

 show session variables like '%isolation%';    #方法一
 ​
 SELECT @@session.tx_isolation;                #方法二
 ​
 SELECT @@tx_isolation;                        #方法三

 设置事务的隔离级别

设置全局事务隔离级别：

全局级别设置之后，当前会话需要退出重新进入才会生效。

 set global transaction isolation level 隔离级别；  #永久生效

设置会话事务隔离级别：

会话事务隔离级别只对当前连接有效，退出连接后失效。在其他终端连接无效。

再次连接后会恢复为全局事务的隔离级别。

 set session transaction isolation level 隔离级别；

事务控制语句：

  • BEGIN 或 START TRANSACTION： 显式地开启一个事务。
  • COMMIT 或 COMMITWORK： 提交事务，并使已对数据库进行的所有修改变为永久性的。
  • ROLLBACK 或 ROLLBACK WORK： 回滚会结束用户的事务，并撤销正在进行的所有未提交的修改。
  • SAVEPOINT S1： 使用SAVEPOINT 允许在事务中创建一个回滚点，一个事务中可以有多个SAVEPOINT；“S1”代表回滚点名称。
  • ROLLBACK TO [SAVEPOINT] S1： 把事务回滚到标记点。

使用 set 设置控制事务

在mysql中执行单独的命令会立即生效，是因为Mysql默认开启自动提交。

使用 set 设置控制事务：

 set AUTOCOMMIT=0;       #禁止自动提交（仅针对当前会话）
 set AUTOCOMMIT=1;       #开启自动提交（仅针对当前会话），Mysql默认为1
 ​
 set global AUTOCOMMIT=0;    #禁止自动提交（针对全局事务）
 set global AUTOCOMMIT=1;    #开启自动提交（针对全局事务），Mysql默认为1
 ​
 show variables like 'AUTOCOMMIT';   #查看当前会话的AUTOCOMMIT值
 show global variables like 'AUTOCOMMIT';  #查看全局事务的AUTOCOMMIT值
  • 如果没有开启自动提交，当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback; 或 commit;当前事务才算结束。当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
  • 如果开起了自动提交，mysql 会把每个sql 语句当成一个事务，然后自动的commit。
  • 当然无论开启与否，begin; commit | rollback; 都是独立的事务。
avatar
文章
阅读
粉丝