MySQL索引、事务

永远的1M2

于 2024-06-27 19:21:02 发布

阅读量958

点赞数 24

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/m0_64000472/article/details/139995395

版权

一、Mysql索引介绍

索引是一个排序的列表，里面存放了索引的值和包含这个值的数据所在行的物理地址。在数据十分庞大的时候，使用索引就可以加快查询的效率，就像书籍的目录一样。

当数据保存在磁盘类存储介质上时，作为一个整体数据块存放，以保持操作的原子性，这种存储结构类似于链表。

若不使用索引进行查询，则时间复杂度为N/2；而对于一个有序的字段，就可以使用二分查找，时间消耗为log2(N)。N表示数据块的个数。但是索引也有自己的缺点，因为在一个表中为字段创建一个索引，将创建另一个数据结构，包含字段数值和指向相关记录的指针，这代表它需要额外的存储空间。

1.1 索引的作用

假设有三个为索引的表t1、t2、t3，分别只包含c1、c2、c3，每个表分别含有1000行数据组成，均为1~1000的数值，查找对应值相等行的查询如下所示：

mysql>select c1,c2,c3 from t1,t2,t3 where c1=c2 and c1=c3;

在没有索引的情况下会将用t1中的每行与t2中的每行数据进行匹配，与t3执行同样的匹配，若已知没找到匹配的结果，就会执行1000*1000*1000此查询结果。

如果对每个表进行索引，就能极大地加快查询速度，利用索引的查询处理如下：

从表t1中选择第一行，查看此行所包含的数据。
使用表t2上的索引，直接定位t2中于t1的值匹配的行。t3同理。
扫描表t1的下一行并重复前面的过程，直到遍历t1中的所有行。

1.2 索引的分类

可以从两个角度来划分：从物理存储角度，索引分为聚簇索引和非聚簇索引，聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索更快。

从逻辑的角度划分，索引分为普通索引、唯一索引、主键索引、组合索引和全文索引。

1.2.1 普通索引

最基本、默认的索引方式，没有任何限制，主要的创建方式有：

①直接创建：

mysql>create index 索引名称 on 表名(索引字段名(字段长度));

②修改表结构的创建：

mysql>alter table 表名 add index 索引名 (索引字段名(字段长度));

③创建表结构同时创建索引：

mysql>create table 表名(字段名1 数据类型(字段1的长度),字段名2 数据类型(字段2的长度) index 索引名 (索引字段名(长度));

1.2.2 唯一索引

与普通索引类似，但是索引的字段中不能有重复的值，但允许有空值(只能有一个)。如果在组合索引中创建唯一索引，则组合索引中的多个字段(例如A、B、C字段)，这ABC字段包含的行中不能有一摸一样的行。唯一索引的创建方法和普通索引类似:

①直接创建：

mysql>create unique index 索引名 on 表名(索引字段名(长度));

②修改表结构的创建：

mysql>alter table 表名 add unique index 索引名 (索引字段名(字段长度));

③创建表结构同时创建索引：

mysql>create table 表名(字段名1 数据类型(字段1的长度),字段名2 数据类型(字段2的长度) index 索引名 (索引字段名(长度));

1.2.3 主键索引

主键索引是一中特殊的唯一索引，一个表只能有一个主键，不允许有空值，一般是在建表的时候同时创建主键索引：

mysql>create table 表名 (字段名1 数据类型(字段1的长度),字段名2 数据类型(字段2的长度) primary key (主键字段名));

1.2.4 组合索引

有时在查询时，需要指定多个字段作为限制条件。可以通过同时给多个字段创建索引来提高查询效率，注意单独给每个字段创建索引并在使用时使用多个有索引的字段并不是组合索引。组合索引的创建语法为：

mysql>create table 表名(字段名1 数据类型(字段长度),字段名2 数据类型(字段长度)……index 表名(字段1,字段2……));

使用select查询时，使用的where条件是以此从左到右执行，若使用的组合索引，注意where后面填写的字段要和创建组合索引是设置的字段顺序一致，否则索引无法生效。

1.2.5 全文索引

①直接创建：

mysql>create fulltext index 索引名 on 表名(表中的字段名);

②修改表结构的创建：

mysql>alter table 表名 add fulltext index 索引名(表中的字段名);

③创建表结构同时创建索引：

mysql>create table 表名(字段名1 数据类型(字段长度),字段名2 数据类型(字段长度)…… fulltext (字段名));

1.3 创建索引的原则依据

1.3.1 数据库建立索引的原则

适合建立索引的场景：

字段中的值不会频繁修改。
经常被用作查询条件(经常出现在where中)的字段。
表中数据量较多的表(以300行为准)。
经常与其他表连接的表里的用于连接字段需要创建索引。
在sql语句中经常进行group by、order by的字段。
表中的主键和外键。
在经常存取的多个字段上建立组合索引

不适合建立索引的场景：

表中频繁改动的字段。
表中数量较少的表。
字段的数据类型为大型数据类型的字段。
不同值较少的字段。

1.5 查看索引

查看索引的方法有两种：

mysql>show index from 表名;

或

msyql>show keys from 表名;

在显示的信息中会显示这个索引的数据结构，包含：

Table	表的名称
Non_unique	如果索引不能包括重复词，则为0;如果可以，则为1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从1开始。
Column_name	字段名称。
Collation	字段以什么方式存储在索引中。在 MySQL 中，有值A(升序)或 NULL(无分类)。
Cardinality	索引中唯一值数目的估计值。通过运行 analyze table 或 myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MSQL使用该索引的机会就越大。
Sub_part	如果字段只是被部分地编入索引，则为被编入索引的字符的数目。如果整个字段被编入索引，则为 NULL。
Packed	只是关键字如何被压缩，如果没有被压缩，则为NULL.
Null	如果字段里的值含有 NULL，则含有YES。如果没有，则该字段含有NO.
Index_type	用过的索引方法(btree，fulltext，hash，rtree)。
Comment	备注。

1.6 删除索引

索引也会占用一定的磁盘空间，因此需要删除一些无用的索引。删除的方法有两种：

mysql>drop index 索引名 on 表名;

或

mysql>alter table 表名 drop index 索引名;

二、MySQL事务

mysql事务主要用于处理操作量大，复杂度高的数据。比方说删除一个员工的信息，既要删除他的基本资料，与其相关的信息，任职的部门等信息，这样删除员工的信息的这一整个过程就是一个事务。

在mysql中只有使用了innodb数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么完全执行成功，要么不执行。
事务用来管理insert、update、delete语句。

事务必须满足4个条件：

原子性：一个事务的执行只会成功或不执行，若在执行过程中出现错误，系统就会回滚到事务开始前的状态。
一致性：事务执行前和执行后，数据库的完整性没有被破坏，这表示写入的资料必须完全符合所有的预设规则。
隔离性：数据库允许多个事务并发执行，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离级别包括读未提交(Read uncommit)、读提交(read committed)、可重复读(repeatable read)、串行化(Serializable);
持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不丢失。

因为mysql命令行的默认设置事务自动提交。因此要显式的开启一个事务必须使用命令begin或start transaction，或者在主配置文件中添加“set autocommit=0”，禁止自动提交当前会话。

事务控制语句包含：

begin或start transaction	显式地开启一个事务
commit或commit work	commit会提交事务，并使已对数据库进行的修改变为永久性。
rollback或rollback work	会结束用户的事务，并撤销正在进行的所有未提交的修改。
savepoint 标识符	允许在事务中创建一个保存点，一个事务可以有多个保存点
release savepoint 标识符	删除事务中的一个保存点
rollback to 标识符	把事务回滚到标记点
set transaction	用于设置事务的隔离级别。