mysql的索引 事务 存储引擎

二、mysql的索引 事务 存储引擎

1、索引的概念：

索引是一个排序的列表，在列表中存储索引的值以及索引对应数据所在的物理行。
索引值和数据是一个映射的关系。

2、索引的作用：

​ 使用索引之后就不需要扫描全表定位某行的数据
​ 加快数据库的查询速度

索引可以是表中的一列，也可以是多个列
1、设置了索引之后，数据库可以利用索引快速定位，能够大大提高查询速度。索引的主要目的
2、尤其是在表的数据很大，以及涉及多个表查询时，索引可以大大提高查询速度。（select）
3、建立索引，不仅能够提供查询速度，在恢复数据库的数据时，也能提高性能
4、可以加快表与表之间连接查询速度。

3、索引副作用：

1、创建的索引需要额外占用磁盘空间。INNODB存储引擎表数据和索引文件在一块，一体，相对来说占的空间小一点。
2、更新一个包含索引的表比没有索引的表需要花费更多的时间
表需要更新，索引也需要更新，所以速度慢很多。

**理想的做法：**经常被作为搜索条件的列上面创建索引。

创建索引的原则和依据

4、索引的原则：

1、表的主键和外键必须有索引，主键是唯一的，外键是关联主表的，查询时可以快速定位。
2、一张表的记录超过300行的数据，应该要创建索引
3、经常与其他表进行连接的表，在连接字段上应该创建索引
4、更新太频繁的字段不适合创建索引
5、经常作为where的条件列，应该创建索引
6、经常使用group by 和 order by 的字段要建立索引。
7、选择一个性能高的字段作为索引。（唯一性越高越适合作为索引）字段的值不同的越多越好。
8、索引要建立在小字段上，字符串的长度比较小的字段，对于长文本字段和超长字段不适合建立索引。（越短越好）

5、索引的类型

5.1b-tree索引

b-树
绝大部分的数据都是使用B-树 索引
p x c y
/ |
px c y

哈希索引：
索引对应的hash值的方法获取表的记录行，速度比较慢，用的比较少。

5.2创建索引（默认为B-树）

create table member (
id int(10),
name varchar(10),
cardid int(18),
phone int(11),
address varchar(50),
remark text,
#text也是数据类型的一种，和char，varchar性质相同，都是字符串
#不需要长度的参数，可以作为大文本的列。可以储存65535个字符
INDEX name_index (name)  
#创建表的的时候直接创建索引
);

ALTER TABLE member add index id_index(id);  #创建之后修改索引或新增索引

EXPLAIN SELECT * FROM member WHERE  id=1;
#EXPLAIN查看当前语句使用索引的情况
unique key 唯一索引：可以用key的方式创建，也可以用索引的方式创建。

create UNIQUE INDEX cardid_index on  member(cardid);    #创建索引

ALTER TABLE member ADD UNIQUE INDEX phone_index(phone);  #添加为唯一索引

主键索引是一种特殊的唯一索引。

show INDEX FROM member;    #查看索引使用信息

5.3删除索引

删除索引
drop  INDEX  phone_index on member;

ALTER TABLE member  DROP INDEX id_index;

5.4普通索引

create UNIQUE INDEX cardid_index on  member(cardid);    #创建索引
INDEX vip_name_index(vip_name)

5.5组合索引

组合索引：一次性给多个列创建索引。形成一个组合。
ALTER TABLE member ADD CONSTRAINT us_name_cardid_phone UNIQUE (name,cardid,phone);

SELECT * FROM member where name ='FBB' and cardid ='411421' and phone ='155172';

5.6全文索引

全文索引：适用于模糊查询，检索大文本使用的。

show INDEX FROM member;
#创建全文索引
一、CREATE FULLTEXT INDEX remark_index     on member (remark);
   CREATE FULLTEXT INDEX     索引名        on member (remark);
二、ALTER TABLE member ADD  FULLTEXT  INDEX remark_index(remark);
   ALTER TABLE 表名  ADD  FULLTEXT  INDEX 索引名(需要索引的列名);
模糊查询
SELECT * from  member where MATCH(remark) AGAINST('vip')

索引题目

商场做一个会员卡系统，包含一下字段:
会员编号  int 5
会员姓名 varchar 10
会员的身份证号 int 11
会员的地址 varenar 50
会员的入会时间  当前时间  timestamp
会员备注信息  text
创建时设计好该表的索引:
主键 ID
姓名创建 普通索引
身份证 唯一索引
全文索引
会员信息 模糊査询

CREATE TABLE shopping (
vip_id INT(5) PRIMARY KEY,
vip_name VARCHAR(10) NOT null ,
vip_IIDD INT(11) NOT NULL UNIQUE KEY ,
vip_address VARCHAR(50),
vip_time TIMESTAMP,
vip_bz  text,
INDEX vip_name_index(vip_name),
FULLTEXT INDEX vip_bz_index(vip_bz)
);

DESC shopping;


SELECT * FROM shopping;
show INDEX FROM shopping;

INSERT INTO shopping VALUES (1,'LYH','44441','13372','2024-08-26','null');
INSERT INTO shopping VALUES (2,'FBB','44444','13333',now(),'牛');
INSERT INTO shopping VALUES (4,'Ly','45587','15555',NOW(),'三人行');

三、mysql的事务

事务是一个机制，一个操作序列。一组或者一条数据库的操作命令。
把所有的命令作为一个整体向系统提交或者撤销的操作。要么都成功，要么都失败。
数据的一致性。数据的一致性非常重要。
事务是一个不可分割的工作逻辑单元，在数据库上执行并发操作时，事务是最小的控制单元。
数据库通过事务的控制和事务的整体性保证数据的一致性。

1、ACID

**事务的特点：**ACID 在数据库的管理系统重，事务的特性有ACID这四种：
A：ATOMICITY
**原子性 ：**事务的最小控制单位，不可分割，要么都成功，要么都失败。
C：CONSISTENCY
**一致性：**事务开始之前和事务结束之后，数据库的完整性没有被破坏。开始的时候数据一致，结束的时候数据还是一致的。
在事务进行的时候，数据可以处于不一致，但是一旦结束，数据必须回到一致。
I：isolation
**隔离性：**并发环境中，不同的事务同时操作相同的数据时，每个事务都有各自完整的数据空间。
对数据修改所发生的并发事务是隔离的，每个事务之间都是独立的。
一个用户的事务不被其他事务所干扰的。

数据库的隔离性：
1、未提交读，read uncommitted RU
允许脏读，一个事务可以看到其他事务未提交的修改。
2、提交读，read committed RC
一个事务只能看到其他事务已经提交的修改，未提交的修改不可见，防止脏读。 oracle 和sql server

show global variables like '%isolation%';
#查看全局隔离
show session variables like '%isolation%';
#查看当前隔离
set global transaction isolation level read committed;
#修改全局隔离改为提交读
set session transaction isolation level read committed;
#修改当前隔离改为提交读

3、可重复读 repeatable read RR
一个事务在执行中，执行两次相同的select语句，得到的结果都是相同的
mysql的默认隔离选项 防止脏读和不可重复读
4、串行读 相当于锁表，完全串行化的读，一个事务在使用其他事务的读写都会阻塞。
D：Durability
持久性：一旦写入数据库，数据不可更改。

2、事务的控制语句：

update cost set money=money+100 where id= 1;
对数据进行修改，设置我还原点为1
savepoint s1;
还原到还原点1
可以设置多个还原点，但是还原只能还原一次，还原过后所有的还原点消失。
commit提交后，所有还原点也将消失，这就是数据库的持久性。

begin； start transaction
开启一个事务

commit
提交事务

rollback: 回滚

savepoint 回滚点名称
设置回滚点

rollback to savepoint 回滚点名称
回滚到指定的点。

show global variables like'gisolation%';
查看全局配置

不可重复读：在一个事务内，多次读同一数据，一个事务没有结束，另外一个事务也访问该数据。
其中一个事务连续两个查询发现结果不一致。另外一个事务在对这个数据进行修改。两次看到的数据不能一致

幻读：一个事务对一个表的数据进行了修改，另一个表也修改了表中数据，前一个事务会返现修改的结果不正确，类似于出现幻觉。

不可更新：两边同时对数据进行修改，但是一方先提交，一方后提交，后提交会覆盖先提交的。

3、存储引擎

**概念：**存储引擎就是存储的机制，是数据库存储数据的一种机制，索引的技巧，锁定水平。
存储的方式和存储的格式。
存储引擎，也属于mysql当中的组件，实际上操作和执行的根本就是数据的读写 I/O

3.1mysql存储引擎的分类：

Mysql 5.5 之后默认使用innodb,事务型速记存储引擎。支持ACID，支持行锁定。（主流）

MyISAM: 5.5之前默认的存储引擎，插入数据的速度和查询速度很快，但不支持事务。

**Memory：**内存型存储引擎，数据在写时都保存在内存当中，一旦重启所有数据全部消失。

**CSV：**逗号分隔数据的存储引擎，数据文件都是以 .csv文件保存的， execl. 保存的文件就是一个普通文本文件。不支持索引。

3.2Innodb存储引擎：

1、读写阻塞（锁表）和事务的隔离级别
2、能高效的缓存数据，支持多种类的索引。
3、表的索引的类型默认是btree
4、支持外键，支持全文索引
5、对硬件的资源要求比较高
6、行级锁定，当对表进行同事操作时，会把行锁住，禁止操作
模糊查询：
1、like 进行查询时，会进行全表扫描，在扫描的过程中会锁定整个表。
2、没有创建索引的列，进行查询时，也会锁定整个表。
3、使用的是索引列，锁定条件的行，行锁定。

show ENGINES;  查看索引类型

3.3InnoDB行锁和索引的关系：

1、行锁是通过索引来实现的。
2、如果没有索引，InnoDB会使用默认的隐藏索引，来对记录进行加锁。
3、加了索引就锁行，不加索引就锁表
4、当指定条件不是索引的时候，会锁住全表

死锁：

事务相互等待对方的资源最后形成一个环路造成的

如何解决死锁：

1、发生了死锁，数据库会自动选择一个事务作为受害者，回滚该事务以解除死锁。
2、Mysql 会终止其中一个事务，但不会回滚。
for update 排他锁，当一个事务的操作未完成时，其他事务可以读取但是不能写入。写锁。

如何避免死锁的情况出现：

1、以固定的顺序访问表和行。
2、大事务尽量拆分为小事务。
3、为表添加合理的索引。

Mysql默认自动提交写入。
Oracle是提交才能写入