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;
