MYSQL索引和事务

Mysql 索引 事务 存储引擎

索引：索引是一个排序的列表，列表当中存储的是索引的值和包含这个值的数据所在行的物理地址

索引的作用加快查询速度

索引的作用：

1. 利用索引数据库可以快速定位，大大加快查询速度，主要作用
2. 表的数据很多，查询需要关联多个表，这个时候索引也可以提高查询速度
3. 加快表与表之间的连接速度
4. 使用分组和排序时，可以大大减少时间
5. 提高数据库恢复数据时的速度

索引创建的原则：

1. 有索引，数据会先进行索引查询，然后定位数据，索引使用不当，反而会增加数据库的负担。主键，外键必须有索引（创建好了主键和外键自动就是索引，不需要额外声明）
2. 一个表超过了300行记录，必须要有索引，否则数据库会遍历表的所有数据。
3. 互相之间有关联的表，在这个关联字段应该设置索引
4. 唯一性太差的字段，不适合创建索引
5. 更新太频繁的字段，不适合做索引
6. 经常被where条件匹配的字段，尤其是表数据比较多的，应该创建索引
7. 在经常进行group by（分组语句） order by（排序语句）的字段上要建立索引
8. 索引的列的字段越小越好，长文本的字段不适合建立索引

索引的类型：

查看表的索引：

show index from 表名

B-树索引 BTREE

树形结构的索引，也是大部分数据库的默认索引类型

根节点：树的最顶端的分支节点

分支节点：指向索引里其他的分支节点，也可以是叶子节点

叶子节点：直接指向表里的数据行

创建BTREE索引（前后要一致）

create index name_index on test (name);

查看表的索引号

show index from test；

哈希索引：散列索引 把任意长度的输入，通过散列算法变换成固定长度的输出。散列

值——分别对应数据里的列和行

Mysql的默认引擎：INNODB默认的索引类型就是Btree

MEMORY引擎可以支持HASH，也是他的默认索引。

先算散列值，然后在对应，速度比较慢，比btree慢

Hash的索引匹配：= in（）<=>

更改索引类型

alter table test engine=memory;

创建HASH索引

create index idx_hash_column on test (sex) using hash;

创建表的时候需要考虑的因素：

1.关联程度 3张表，选好关联字段

1. 每个字段的长度，也要考虑
2. 设计合理的索引列
3. 表数据，要控制在合理的范围之内。可以在牺牲一定性能的条件下，满足需求。5秒以上就要考虑优化了。10秒以上一般是出问题了。（缓存失效，缓存击穿，缓存雪崩）

普通索引：

建表时直接创建索引

create table member (

id int(4) PRIMARY KEY,

name varchar(10),

card_id int(18),

phone int(11),

index name_index (name)

);

show index from member;

修改表方式创建

alter TABLE member add index cardid_index (card_id);

唯一索引：unique

与普通索引类似，唯一索引的每个值都是唯一，唯一索引允许空值

添加唯一键才会创建唯一键。最好是不要为空。unique not null

创建唯一索引

create UNIQUE index phone_index on member (phone);

0表示唯一值，不能为空，不可以重复

create table test1 (

id int(4) PRIMARY key,

name varchar(10),

card_id int(18) not null,

phone int(11) not null,

UNIQUE cardid_index (card_id),

UNIQUE phone_index (phone)

);

show index from test1;

能在建表时创建好的条件，尽量在创建把条件约束好。不要创完之后再添加

主键索引：创建表的指定的主键就是索引。添加主键自动就是主键索引。

主键：值唯一 一个表只能有一个主键 不允许有空值 创建主键，自动主键索引

全文索引：

适合在进行模糊查询的时候使用，可以在一边文章中检索文本信息

建表时创建

create table test2 (

id int(4) PRIMARY key,

name varchar(10),

card_id int(18) not null,

phone int(11) not null,

notes text,

UNIQUE cardid_index (card_id),

UNIQUE phone_index (phone)，

FULLTEXT notes_index (notes)

);

外部添加

create fulltext index notes_index on test2 (notes);

show index from test2;

使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

删除索引：

drop index notes_index on test2;

explain select * from test2 where notes like ‘京东%’;

这里的like是模糊查询的意思

前面添加explain，可以查看索引的使用情况

联合索引：指定一个索引名，一个索引名对应多个列名

create index index_union on test2(card_id,phone);

联合索引，查询时必须按照穿件时的顺序来进行查询

建表时创建

create table test3 (

id int(4) PRIMARY key,

name varchar(10),

card_id int(18) not null,

phone int(11) not null,

unique index name_cardid_phone (name,card_id,phone)

外部添加

create index index_union on test2(card_id,phone)

Mysql机制：默认会找最短的索引列。最优索引选择

联合索引，从左到右侧开始，不能跳过索引，否则索引会失效

范围查询，有可能右侧的索引会失效

如果索引是字符串，但是不加引号，索引也会失效

使用or语句索引一定失效，使用or作为条件，mysql无法同时使用多个索引

is null

is not null

有时候索引会失效 where is null 数据的绝大多数都是空值，索引失效

                 where is not null 数据多数为不null，索引失效

现在一张表的查询速度是7.62s，你该如何解决

首先查缓存，看是不是请求直接到了后端数据库。

再看索引，请求的列值不是默认的索引，添加一下即可，用explain

Mysql的事务：

事务是一种机制，一个操作序列，包含了一组数据库的操作命令，所有命令都是一个整体，向系统提交或者撤销的操作，要么都执行，要么都不执行。

不可分割的单位

事务的特点ACID：

A：原子性 最小单位，事务里的所有条件都是一个整体，不可分割。要么都成功，要么都失败。

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

事务完成时，数据必须属于一致状态

事务开始前，数据库中的存储数据处于一致状态。

进行中的事务，数据可能处于不一致的状态

在事务最终完成时，必须再次回到已知的一致状态

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

对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的

修改数据的事务可以另一个使用相同数据的事务开始之前访问这些数据。或者在另一个使用相同的事务结束之后访问这些数据。

D：持久性。数据提交，事务的效果将会被永久的保留在数据库中。而且不会被回滚

Mysql支持四种隔离级别：

1. 未提交读（Read Uncommitted），允许脏读，允许一个事务可以看到其他事务未提交的修改
2. 提交读（Read Committed ）：事务只能查看已经提交的修改，未提交的修改是不可见的。防止脏读orcale sql server
3. 可重复读（Repeatable Read），也是mysql的默认隔离级别。确保如果在一个事务中执行两次相同的select语句时，都能得到相同的结果不管其他事务是否提交修改。可以防止脏读以及不可重复读。
4. 串行读（serializable），锁表，完全串行化，每一事务都隔离，读写都阻塞。

脏读：另外一个事务能够看到另一个事务未提交的修改结果

外部未提交，内部查看金额发生了变化，但是外部事务实际上没有提交这就是脏读

内部终端：

create table test (

id int(4) primary key,

money int(10)

);

insert into test values(1,1000);

insert into test values(2,500);

#插入数据

 begin

updata test set money=money-800 where id =1;

#修改金额

select * from test;

事务隔离级别的作用范围：

1. 全局级，对所有的会话有效
2. 会话级：只对当前的会话有效

查询全局事务的隔离级别（两种方式）

show global variables like '%isolation';

select @@global.tx_isolation;

查看会话级别（两种方式）

show session variables like '%isolation';

select @@session.tx_isolation;

set global transaction isolation level read uncommitted;

#设置全局事务的隔离级别。全局修改

set @@global.tx_isolation='read uncommitted';

#设置全局事务的隔离级别。临时修改。重启及失效

set session transaction isolation level read uncommitted;

#修改会话。全局修改。

set @@session.tx_isolation='read uncommitted';

#修改会话，临时修改。重启及失效

不可重复读：一个事务内，多次读同一个数据。前一个事务还没有结束，另一个事务也访问该数据。 在一个事务之内，两次查询结果不一致。读不到相同的数据内容。

内部外部终端都修改全局事务和会话

set session transaction isolation level read committed;

#修改会话。全局修改为提交读。

set global transaction isolation level read uncommitted;

#修改全局事务。全局修改为提交读。

show global variables like '%isolation';

#查询全局事务的隔离级别

flush privileges;

在内部终端里：

select * from test；

begin;

update test set money=money-200 where id =1;

回到外部终端：

select * from test;

commit;

幻读：一个事务对一个表中的数据进行了修改，可能会涉及到表中的全部数据。另一个事务也是修改了表中的数据，插入了一行新的数据。前一个事务会发现表中还有数据没有修改，类似于幻觉。

丢失更新：两个事务同时修改一条记录，A先修改记录，b也修改了记录，b一旦提交会覆盖A的结果

以上这些情况如何避免：

1.权限控制

2.根据情况来使用隔离级别

生产环境——最好是提交读和可重读

测试环境——无所谓

3.生产环境只能允许一个人对一个事务进行操作。其他人不允许操作

总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是最终的结果。

事务的控制语句：

BEGIN

显示的开启事务

START transaction

提交事务

commit

commit work

回滚，撤销正在进行的所有未提交的修改

rollback

rollback work

创建回滚点，一个事务可以有多个回滚点

savepoint s1；

回滚到还原点

rollback to s1；

多点还原，s1 s2

如果说还原到s1，s2将消失

如果

Mysql 提交事务默认是自动提交

可以用set来设备mysql提交方式：

show varchar like ‘autocommit’;

set autocommit=0;

show global variables like '%isolation';

#查询全局事务的隔离级别

select @@global.tx_isolation;

#也是查询全局事务的隔离级别

show session variables like '%isolation';

#查看会话级别

select @@session.tx_isolation;

#查看会话级别

 set AUTOCOMMIT=0;       

#关闭自动提交（仅针对当前会话），默认为1

 set global AUTOCOMMIT=0;   

 #关闭自动提交（针对全局事务），Mysql默认为1