mysql索引、事务与存储引擎
一、索引
索引:是排序的快速查找的特殊数据结构,定义作为查找条件的字段上,又称为键key,索引通过存储引擎实现。
1.索引的概念
索引是一个排序的列表,包含索引字段的值和其相对应的行数据所在的物理地址。
2.索引的实现原理
没有索引的情况下,要查询某行数据时,需要先扫描全表,再来定位某行数据,对于表数据很多的情况下,效率较低。
有了索引后,会先通过查找条件的字段值找到其索引对应的行数据的物理地址,然后根据物理地址访问相应的行数据。
3.索引的作用
加快表的查询速度,还可以对字段排序。
优点
1)设置了合适的索引之后,数据库利用各种快速定位技术,能够极大地加快查询速度,这是创建索引的最主要的原因;
2)当表很大或查询涉及到多个表时,使用索引可以成千上万倍地提高查询速度,避免排序和使用临时表;
3)可以降低数据库的IO成本(减少io次数),并且索引还可以降低数据库的排序成本,将随机I/O转为顺序I/O;
4)通过创建唯一性索引,可以保证数据表中每一行数据的唯一性;
5)可以加快表与表之间的连接;
6)在使用分组和排序时,减少分组和排序的时间;
7)建立索引在搜索和恢复数据库中的数据时能显著提高性能。
缺点
1)会额外占用磁盘空间;
2)更新包含索引的表会花费更多时间,效率会更慢。
4.创建索引的依据
1)表中的记录行数较多时,一般超过300行的表建议要有索引;
2)建议在表中的主键字段、外键字段、多表连接使用的公共关键字段、唯一性较好的字段、不经常更新的字段、经常出现在 where、group by、order by 子语句的字段、小字段上面创建索引;
3)不建议在唯一性较差的字段、更新太频繁的字段、大文本字段上面创建索引。
5.索引的分类和创建
create table hx.test (id int,name varchar(10),address varchar(10),age int);
insert into test (id,name,address,age) values(1,'test1','aaa',20);
insert into test (id,name,address,age) values(2,'test2','bbb',20);
insert into test (id,name,address,age) values(3,'test3','ccc',22);
普通索引 index
最基本的索引类型,没有唯一性之类的限制。
直接创建索引
create index 索引名 on 表名 (字段);
#索引名建议以“_index”结尾
#举个例子 #创建 create index address_index on test (address); #查看 show create table test \G;
# \G
选项,命令会以更易读的方式显示结果,将每个字段的信息单独显示在一行上,而不是在表格中列出。
修改表方式创建
alter table 表名 add index 索引名 (字段);
#举个例子
alter table test add index address_index(address);
创建表的时候指定索引
create table 表名 ( 字段1 数据类型,字段2 数据类型[,...],index 索引名 (列名));
#举个例子 create table test2 (id int ,idd int,name varchar(10),index id_index(id));
唯一索引 unique
与普通索引类似,但区别是唯一索引列的每个值都唯一。
唯一索引允许有空值(注意和主键不同)。
如果是用组合索引创建,则列值的组合必须唯一。
添加唯一键将自动创建唯一索引。
直接创建唯一索引
create unique index 索引名 on 表名 (字段);
#举个例子
create unique index idd_index on test2(idd);
修改表方式创建
alter table 表名 add unique 索引名 (字段);
#举个例子
alter table test add unique age_index(age);
创建表的时候指定
create table 表名 (字段1 数据类型,字段2 数据类型[,...],unique 索引名 (列名));
主键索引 primary key
主键索引是一种特殊的唯一索引,必须指定为“PRIMARY KEY”。
一个表只能有一个主键,不允许有空值。
添加主键将自动创建主键索引。
创建表的时候指定
create table 表名 ([...],primary key (列名));
#举个例子 create table test2 (id int,pid int,primary key(id));
修改表的方式创建
alter table 表名 add primary key (字段);
#举个例子 alter table test add primary key(id);
组合索引(单列索引与多列索引)
可以是单列上创建的索引,也可以是在多列上创建的索引。
需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。
组合索引 create index 索引名 on 表名 (字段1, 字段2, 字段3,....); alter table 表名 add index 索引名 (字段1, 字段2, 字段3,....); create unique index 索引名 on 表名 (字段1, 字段2, 字段3,....); alter table 表名 add unique 索引名 (字段1, 字段2, 字段3,....); select ... from 表名 where 字段1=XX and 字段2=XX and 字段3=XX #用 and 做条件逻辑运算符时,要创建组合索引且要满足最左原则 #用 or 做条件逻辑运算符时,所有字段都要单独创建单列索引
以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息,用来查看查询优化器如何执行查询
参考资料: https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
EXPLAIN SELECT clause
#例子 explain select * from stundets where stuid=20;
说明 | |
---|---|
id | 执行编号,标识select所属的行。如果在语句中没子查询或关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置 |
select_type | 简单查询:SIMPLE|复杂查询:PRIMARY(最外面的SELECT)、DERIVED(用于FROM中的子查询)、UNION(UNION语句的第一个之后的SELECT语句)、UNIONRESUlT(匿名临时表)、SUBQUERY(简单子查询) |
table | 访问引用哪个表(引用某个查询,如“derived3”) |
type | 关联类型或访问类型,即MySQL决定的如何去查询表中的行的方式 |
possible_keys | 查询可能会用到的索引 |
key | 显示mysql决定采用哪个索引来优化查询 |
key_len | 显示mysql在索引里使用的字节数 |
ref | 当使用索引列等值查询时,与索引列进行等值匹配的对象信息 |
rows | 为了找到所需的行而需要读取的行数,估算值,不精确。通过把所有rows列值 |
Extra |
说明: type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ,一般来说,得保证查询至少达到range级别,最好能达到ref
select * from students where name='xu xian'; #找许仙 create index idx_name on students(name(5)); #已name 为字段建立索引 show index from students; #查看 索引 会多一条 explain select * from students where name='xu xian'; #可以看到使用了索引 [root@localhost ~]# mysql -uroot -p'自己的密码' hellodb < testlog.sql #生成测试环境 (root@localhost) [hellodb]> call sp_testlog; #生成 一百万条数据 create index index_name on testlog(name); #建立索引 drop index inx_name on testlog; #删除索引 create index index_name on students(name); #给students 表加 name表 explain select * from students where name like 's%'; #找s开头的人看是否会用索引 explain select * from students where name like '%s'; #找s结尾的人是否会用 explain select * from students where name like '%s%'; #包含s的是否会调用索引 explain select * from students where name like 'x%'; #是否使用索引
6.查看索引
show create table 表名;
show index from 表名;
show keys from 表名;
字段 | 含义 |
---|---|
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 |
Index_type | 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE) |
Comment | 备注 |
7.删除索引
直接删除索引
drop index 索引名 on 表名;
修改表方式删除索引
alter table 表名 drop index 索引名;
删除主键索引
alter table 表名 drop primary key;
二、MySQL事务
1.事务的概念
事务就是一组数据库操作序列(包含一个或者多个操作命令)。
事务会把所有操作看作一个不可分割的整体向系统提交或撤销操作,所有操作要么都执行,要么都不执行。
2.事务的ACID特性
在可靠数据库管理系统(DBMS)中,事务(transaction)应该具有的四个特性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)。
这是可靠数据库所应具备的几个特性。
名称 | 描述 |
---|---|
原子性(Atomicity) | 原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生 |
一致性(Consistency) | 事务前后数据的完整性必须保持一致 |
隔离性(Isolation) | 事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离 |
持久性(Durability) | 事务一旦被提交则不可逆,被持久化到数据库中,接下来即使数据库发生故障也不应该对其有任何影响 |
在事务管理中,原子性是基础,隔离性是手段,一致性是目的,持久性是结果。
3.事务并发导致的问题
名称 | 描述 |
---|---|
脏读 | 指一个事务读取了另外一个事务未提交的数据 |
不可重复读 | 在一个事务内读取表中的某一行数据,多次读取结果不同 |
虚读(幻读) | 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致 |
丢失更新 | 两个事务同时读取同一条记录,导致修改结果覆盖 |
4.事务的隔离级别
事务的隔离级别决定了事务之间可见的级别。
四种隔离级别
1)未提交读( Read Uncommitted(RU))
允许脏读,即允许一个事务可以看到其他事务未提交的修改。
2)提交读(Read Committed(RC))
允许一个事务只能看到其他事务已经提交的修改,未提交的修改是不可见的,防止脏读。
3)可重复读(Repeatable Read(RR))—mysql默认的隔离级别
确保如果在一个事务中执行两次相同的SELECT语句,都能得到相同的结果,不管其他事务是否提交这些修改;
可以防止脏读和不可重复读。
4)串行读(Serializable)—相当于锁表
完全串行化的读,将一个事务与其他事务完全地隔离;
每次读都需要获得表级共享锁,读写相互都会阻塞;
可以防止脏读,不可重复读取和幻读,(事务串行化)会降低数据库的效率。
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交(read-uncommitted) | 是 | 是 | 是 |
读已提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
mysql默认的事务处理级别是 repeatable read ,而Oracle和SQL Server是 read committed 。
事务隔离级别的作用范围分为两种:
● 全局级:对所有的会话有效
● 会话级:只对当前的会话有效
管理事务隔离级别
1)设置隔离级别
设置全局事务隔离级别
#全局级的隔离级别,可在所有会话有效,需要重新登录才可生效
set global transaction isolation level 隔离级别名称;
set global transaction isolation level read committed; set @@global.tx_isolation='read-committed'; #重启服务后失效
设置会话事务隔离级别
#会话级的隔离级别,在当前会话中立即生效
set session transaction isolation level 隔离级别名称;
set session transaction isolation level repeatable read; set @@session.tx_isolation='repeatable-read';
2)查询隔离级别
查询全局事务隔离级别
show global variables like '%isolation%'; SELECT @@global.tx_isolation
查询会话事务隔离级别
show session variables like '%isolation%'; SELECT @@session.tx_isolation; SELECT @@tx_isolation;
5.事务控制语句
#显式地开启一个事务
begin;
#提交事务,并使已对数据库进行的所有修改变为永久性的。
commit;
#回滚
#回滚会结束用户的事务,并撤销正在进行的所有未提交的修改。
rollback;
#创建回滚点,相当于存档
#一个事务中可以有多个
#XX代表回滚点名称。
savepoint XX ;
#把事务回滚到标记点,相当于读档
rollback to XX;
举个例子
#创建测试用表 use hx; create table account( id int(10) primary key not null, name varchar(40), money double ); insert into account values(1,'A',1000); insert into account values(2,'B',1000);
#测试提交事务 begin; update account set money= money - 100 where name='A'; commit; quit mysql -u root -p use hx; select * from account;
#测试回滚事务 begin; update account set money= money + 100 where name='A'; rollback; mysql -u root -p use byyb; select * from account;
#测试多点回滚 begin; update account set money= money + 100 where name='A'; SAVEPOINT S1; update account set money= money + 100 where name='B'; SAVEPOINT S2; insert into account values(3,'C',1000); select * from account; ROLLBACK TO S1; select * from account;
6.使用 set 设置控制事务(自动提交)
如果没有开启自动提交
当前会话连接的mysql的所有操作都会当成一个事务直到你输入rollback或者commit;
,当前事务才算结束。
当前事务结束前新的mysql连接时无法读取到任何当前会话的操作结果。
如果开起了自动提交
mysql会把每个sql语句当成一个事务,然后自动的commit。
当然无论开启与否,begin;
commit|rollback;
都是独立的事务。
#禁止自动提交 #临时修改 SET AUTOCOMMIT=0; #开启自动提交,Mysql默认为1 SET AUTOCOMMIT=1; #查看Mysql中的AUTOCOMMIT值 SHOW VARIABLES LIKE 'AUTOCOMMIT';
use hx; select * from account; SET AUTOCOMMIT=0; update account set money= money + 100 where name='B'; select * from account; quit mysql -u root -p use hx; select * from account;
三、MySQL 存储引擎
1 存储引擎的概念
MyISAM 表支持 3 种不同的存储格式:
1)静态(固定长度)表
静态表是默认的存储格式。静态表中的字段都是非可变字段,这样每个记录都是固定长度的,这种存储方式的优点是存储非常迅速,容易缓存,出现故障容易恢复;缺点是占用的空间通常比动态表多。
2)动态表
动态表包含可变字段,记录不是固定长度的,这样存储的优点是占用空间较少,但是频繁的更新、删除记录会产生碎片,需要定期执行 OPTIMIZE TABLE 语句或 myisamchk -r 命令来改善性能,并且出现故障的时候恢复相对比较困难。
3)压缩表
压缩表由 myisamchk 工具创建,占据非常小的空间,因为每条记录都是被单独压缩的,所以只有非常小的访问开支。
2.常用存储引擎(区别)
常用存储引擎:InnoDB、MyISAM
MyISAM:不支持事务、外键约束,只支持表级锁定,适合单独的查询或插入的操作,读写并发能力较弱,支持全文索引,硬件资源占用较小,数据文件和索引文件是分开存储的。存储成三个文件:表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景:适用于不需要事务处理,单独的查询或插入数据的业务场景
InnoDB:支持事务、外键约束,支持行级锁定(在全表扫描时仍然会表级锁定),读写并发能力较好,支持全文索引(5.5版本之后),缓存能力较好可以减少磁盘IO的压力,数据文件也是索引文件。存储成两个文件:表结构文件.frm、数据文件.ibd
使用场景:适用于需要事务的支持,一致性要求高的,数据会频繁更新,读写并发高的业务场景
3.语句
查看
1)查看系统支持的存储引擎
show engines;
2)查看表使用的存储引擎
方法一show table status from 库名 where name='表名'\G
#举个例子 show table status from hx where name='test'\G
方法二
use 库名;
show create table 表名;
#举个例子
use hx;
show create table test;
修改存储引擎
方法一:通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;
方法二:修改配置文件
通过修改 /etc/my.cnf
配置文件,指定默认存储引擎并重启服务。
此方法只对修改了配置文件并重启mysql服务后新创建的表有效,已经存在的表不会更改。
vim /etc/my.cnf ...... [mysqld] ...... default-storage-engine=INNODB systemctl restart mysqld
#此方法只对修改了配置文件并重启mysql服务后新创建的表有效,已经存在的表不会更改。
创建
通过 create table
创建表时指定存储引擎
use 库名;
create table 表名(字段1 数据类型,...) engine=MyISAM;