MySQL索引与事务

「爱情、让人受尽委屈。」 2021-12-09 23:34 226阅读 0赞

## MySQL索引 ##

**索引基本描述**

索引是用来提高数据库的性能的一种机制。不用加内存，不用改程序，不用调sql，只要执行正确的create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

**索引分类**

*  主键索引(primary key)
 *  唯一索引(unique)
 *  普通索引(index)
 *  全文索引(fulltext)–解决中子文索引问题。

**索引的创建**

主键索引与唯一索引就是创建好主键之后，它就是一个索引。故此我们只需要来看看普通索引的建立。

方式一
    create table user8(id int primary key,
    	name varchar(20),
    	addr varchar(30),
    	index(name) --在表的定义最后，指定某列为索引
    );

方式二
    create table stu(id int primary key, name varchar(20), addr varchar(30));
    alter table stu add index(name); --创建完表以后指定某列为普通索引

方式三
    create table stu(id int primary key, name varchar(20), addr varchar(30));
    -- 创建一个索引名为 idx_name 的索引
    create index idx_name on stu(name);

全文索引的创建  
当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文，例如:

CREATE TABLE articles (
    	id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    	title VARCHAR(128),
    	body TEXT,
    	FULLTEXT (title,body)
    )engine=MyISAM;

**索引查询**

1.  第一种方法: show keys from 表名
2.  第二种方法: show index from 表名
3.  第三种方法: desc 表名；

**删除索引**

1.  第一种方法-删除主键索引： alter table 表名 drop primary key;
2.  第二种方法-其他索引的删除： alter table 表名 drop index 索引名；
3.  第三种方法方法： drop index 索引名 on 表名

**索引的使用注意事项**

*  比较频繁作为查询条件的字段应该创建索引
 *  唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
 *  更新非常频繁的字段不适合作创建索引
 *  不会出现在where子句中的字段不该创建索引

## MySQL事务 ##

**事物的基本描述**

事务就是一组DML语句组成，这些语句在逻辑上存在相关性，这一组DML语句要么全部成功，要么全部失败，是一个整体。MySQL提供一种机制，保证我们达到这样的效果。事务还规定不同的客户端看到的数据是不相同的。

**事物的基本操作**

*  创建一个简单的测试表关于银行转账的例子  
    ![在这里插入图片描述][20190714220421654.png]
 *  开始一个事务 start transaction;  
    ![在这里插入图片描述][20190714220500278.png]
 *  创建一个保存点 savepoint 保存点名；  
    ![在这里插入图片描述][20190714220611934.png]
 *  添加数据  
    ![在这里插入图片描述][20190714220721584.png]  
    ![在这里插入图片描述][20190714220845751.png]![在这里插入图片描述][20190714220832633.png]
 *  查看记录  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTAzMzE1_size_16_color_FFFFFF_t_70]
 *  回到保存点，并再次查看记录（根据具体情况） rollback to 保存点名;  
    ![在这里插入图片描述][20190714221030869.png]

**事务的注意事项**

*  如果没有设置保存点，也可以回滚，只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
 *  如果一个事务被提交了（commit），则不可以回退（rollback）可以选择回退到哪个保存点
 *  InnoDB支持事务，MyISAM不支持事务

**读取数据的概念**

*  1.脏读（Dirty Reads）：所谓脏读就是对脏数据的读取，而脏数据所指的就是未提交的数据。一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

*  2.不可重复读（Non-Repeatable Reads）：一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。也就是说，这个事务在两次读取之间该数据被其它事务所修改。
 *  3.幻读（Phantom Reads）：一个 事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读

**事务的隔离级别**

当MySQL表被多个线程或者客户端开启各自事务操作数据库中的数据时，MySQL提供了一种机制，可以让不同的事务在操作数据时，具有隔离性。从而保证数据的一致性。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTAzMzE1_size_16_color_FFFFFF_t_70 1]  
mysql默认的隔离级别是可重复读,一般情况下不要修改

**事务的ACID特性**

*  1、原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

*  2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。

*  3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

*  4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

[20190714220421654.png]: /images/20211209/0222a593c7b9419faccc752ada6d17ee.png
[20190714220500278.png]: /images/20211209/fc5e26b714f44f0798bf848305a66262.png
[20190714220611934.png]: /images/20211209/7a8c2683177e4bcf9dd1059c9af64b46.png
[20190714220721584.png]: /images/20211209/0cc7da749fbe4c3ca55662cfc546e497.png
[20190714220845751.png]: https://img-blog.csdnimg.cn/20190714220845751.png
[20190714220832633.png]: /images/20211209/b3e211de66974f08b4f6a62bd1627c4b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTAzMzE1_size_16_color_FFFFFF_t_70]: /images/20211209/61281c5a867c4f71a48fe4f4015a158b.png
[20190714221030869.png]: /images/20211209/ed3e816df11d492f95bbd199d4d6e51e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzNTAzMzE1_size_16_color_FFFFFF_t_70 1]: /images/20211209/e305f576b83341e8b9949ff8e65f883b.png