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;
索引查询
- 第一种方法: show keys from 表名
- 第二种方法: show index from 表名
- 第三种方法: desc 表名;
删除索引
- 第一种方法-删除主键索引: alter table 表名 drop primary key;
- 第二种方法-其他索引的删除: alter table 表名 drop index 索引名;
- 第三种方法方法: drop index 索引名 on 表名
索引的使用注意事项
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引
- 不会出现在where子句中的字段不该创建索引
MySQL事务
事物的基本描述
事务就是一组DML语句组成,这些语句在逻辑上存在相关性,这一组DML语句要么全部成功,要么全部失败,是一个整体。MySQL提供一种机制,保证我们达到这样的效果。事务还规定不同的客户端看到的数据是不相同的。
事物的基本操作
-
创建一个简单的测试表关于银行转账的例子
-
开始一个事务 start transaction;
-
创建一个保存点 savepoint 保存点名;
-
添加数据
-
查看记录
-
回到保存点,并再次查看记录(根据具体情况) rollback to 保存点名;
事务的注意事项
- 如果没有设置保存点,也可以回滚,只能回滚到事务的开始。直接使用 rollback(前提是事务还没有提交)
- 如果一个事务被提交了(commit),则不可以回退(rollback)可以选择回退到哪个保存点
- InnoDB支持事务,MyISAM不支持事务
读取数据的概念
- 1.脏读(Dirty Reads):所谓脏读就是对脏数据的读取,而脏数据所指的就是未提交的数据。一个事务正在对一条记录做修改,在这个事务完成并提交之前,这条数据是处于待定状态的(可能提交也可能回滚),这时,第二个事务来读取这条没有提交的数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被称为脏读。
- 2.不可重复读(Non-Repeatable Reads):一个事务先后读取同一条记录,但两次读取的数据不同,我们称之为不可重复读。也就是说,这个事务在两次读取之间该数据被其它事务所修改。
- 3.幻读(Phantom Reads):一个 事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为幻读
事务的隔离级别
当MySQL表被多个线程或者客户端开启各自事务操作数据库中的数据时,MySQL提供了一种机制,可以让不同的事务在操作数据时,具有隔离性。从而保证数据的一致性。
mysql默认的隔离级别是可重复读,一般情况下不要修改
事务的ACID特性
- 1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。
- 2、一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。
- 3、隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。
- 4、持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。