MySQL的索引和事务

MySQL的索引

索引的概念：

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

索引值和数据是一个映射关系。

索引的作用：

使用索引之后，就不需要扫描全表来定位某行的数据。

加快数据库的查询速度。

索引可以是表中的一列，也可以是多个列

1、设置了索引之后，数据库可以利用索引快速定位，大大提高查询速度，这也是索引的主要原            因。

2、尤其的表的数据很大，以及涉及多个表查询时，索引可以大大的提高查询速度。

3、建立索引不仅能够提高查询速度，在恢复数据库的数据时，也能提高性能。

4、可以加快表与表

索引的负作用：

1、创建的索引也需要占用额外的磁盘空间。 INNODB存储引擎表数据文件和索引文件在一块           （一体）相对来说占用的空间小一点。

2、一但创建了索引，更新一个包含索引的表比没有索引的表需要花费更多的时间，表需要更新，索引也要更新，所以速度要慢很多。

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

之间连接查询的速度。

创建索引的原则和依据：

1、表的主键和外键必须有索引，主键是唯一的，外键是关联主表的，查询时可以快速定位

2、一张表有超过300行的数据，应该要创建索引。

3、经常与其他表进行连接的表，在连接字段上应该创建索引。

4、更新太频繁的字段不适合创建索引

5、经常作为where语句的条件列，应该创建索引

6、经常使用grour by 和order by 的字段上要建立索引。

7、选择一个性能高的字段作为索引，字段的值不同的越多越好。

8、索引要建立在小字段上，字符串的长度比较小的字段，对于长文本字段，超长的字段不适合建立索引。

索引的类型：

b-tree 索引，

b-树索引

绝大部分的数据都是使用b-树索引

索引左边的列开始，从左到右按顺序进行排列

哈希索引：

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

---

创建索引

第一种：在创建表的时候创建索引

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)

);

show index from member；

查看表的状态

第二种：后天添加索引

alter table member add index id_index(id);

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

怎么一次性添加多个数据

insert into member values (内容),

(内容)；

查询当前语句使用索引的情况

explain select * from member where id = 1;

唯一索引：可以用key(唯一)的方式创建，也可以用index(索引)的方式创建

create unique index 列名_index on 表名(列名)

 alter table 表名 drop index 列名_index;

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

 删除索引：

drop index 列名_index on 表名；

alter table 表名 drop index 列名_index;

组合索引：

一次性给多个列创建索引，形成一个组合。

alter table member add constraint uc_name_cardid_phone unique(name,cardid,phone);

查询内容

select * from member where name =(内容) and cardid =（内容） and phone =（内容） ;

全文索引：

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

创建

create fulltext index 列名_index on 表名 (列名);

查询

select * from 表名 where match (列名) against ('关键字');

 

---

MySQL的总结：

索引是 MySQL 中用于提高数据库查询性能的重要结构。

一、索引的类型

1. 主键索引（Primary Key）：表中的一列或列组合，其值唯一标识表中的每一行，一个表只能有一个主键索引。

   • 例如，在学生表中，学号通常被设为主键。

2. 唯一索引（Unique Index）：确保表中某一列的值是唯一的，但可以存在 NULL 值。

   • 比如，身份证号可以设置为唯一索引。

3. 普通索引（Index）：可以加快数据的查询和排序速度。

   • 假设在订单表中，经常按照订单创建时间查询订单，那么订单创建时间列可以设置普通索引。

4. 全文索引（Fulltext Index）：用于对文本类型的列（如 CHAR 、 VARCHAR 、 TEXT ）进行全文搜索。

   • 例如，在文章表中，对文章内容列建立全文索引，以便快速搜索包含特定关键词的文章。

二、索引的优点

1. 大大提高数据的查询速度，特别是在大型数据表中。
2. 有助于优化数据库的排序和分组操作。

三、索引的缺点

1. 索引会增加数据插入、更新和删除的时间，因为这些操作需要同时维护索引。
2. 过多的索引会增加数据库的存储空间。

四、创建索引的注意事项

1. 只在经常用于查询、连接、排序和分组的列上创建索引。
2. 避免在小数据量的表上创建过多索引。
3. 对于经常更新的列，要谨慎创建索引。

总之，合理地使用索引可以显著提高数据库的性能，但过度使用或不当使用可能会带来负面影响，需要根据实际的业务需求和数据特点进行权衡和设计。

---

MySQL的事务

mysql的事务

事务是一个机制，一个操作序列。一组或者一条数据库的操作命令。

把所有的命令作为一个整体向系统提交或者撤销的操作。要么都成功，要么都失败。

数据的一致性，数据的一致性非常重要。

事务是一个不可分割的工作逻辑单元，在数据库上执行并发操作时，事务时最小的控制单元。

数据库通过事务的控制和事务的整体性保证数据的一致性。

事务的特点：ACID 在数据库的管理系统中，事务的特性有ACID这四种

A：Atomicity    原子性  ---- 事务的最小控制单位，不可分割，要么成功，要么失败

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

                        开始的时候数据是一致的，结束的时候数据也是一致的。

                        在事务进行的时候，数据可以处于不一致的状态，但是一但结束，

                        数据必须是一致的。

I：lsolation     隔离性 ---- 在并发环境中，不同的事务同时操作相同的数据时，每个事务都有自

                       己完整的数据空间。

                       对数据的修改所发生的并发事务是隔离的，每个事务之间都是独立的

                       一个用户的事务不给其他事务所干扰。

数据的隔离性：

1、未提交读：read uncommitted RU

                       允许脏读，一个事务可以看到其他事务未提交的修改

2、提交读：read committed RC

                    一个事务只能看到其他事务已经提交的修改，未提交的修改不可见，防止脏读

3、可重复读：repeatable read RR

                       一个事务在执行中，执行两次相同的select语句，得到的结果都是相同

                       mysql的默认隔离选项 防止脏读和不可重复读。

4、串行读：相当于锁表，完全串行化的读，一个事务在使用，其他事务的读写都会阻塞(没人用)

不可重复读：在一个事务内，多次读同一数据，一个事务没有结束，另外一个事务也访问该数据

其中一个事务连续两次查询发现结果不一致。原因是另外一个事务在对这个数据进行修改。两次看到的数据不能一致。

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

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

D：Durability      持久性 ---- 一但写入数据库，数据不可更改。

事务控制语句：

开启一个事务：begin或者start transaction

提交事务：commitl

回滚：rollback

设置回滚点：savepoint +名称

回到回滚点：rollback to savepoint +名称

回滚点可以有多个，回滚其中一个，其他的回滚点将会没有作用。

一但提交事务，不管设置了多少回滚点将不会存在。