Mysql数据库(6)-索引，事务与引擎

一.Mysql索引

1.作用：提高查询的速度

2.说明：索引本身会占用存储空间，因为建立索引实际上是在原来的文件上增加一个利于查询的数据结构，比如说建立二叉树可以将原来的“全表查询”优化为折半查找，从而提升查询速度

3.基本使用

create index 索引名字 on 表（列）：在什么表的什么列上创建索引

-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp 表的 empno 列创建索引
CREATE INDEX empno_index ON emp (empno)

4.代价：索引本身会占用存储空间；

        因为索引是对数据进行的，所以当执行dml语言时，它们会互相影响。对dml语言（影响数据的操作：insert，update，delete）的效率影响。

5.类型：主键索引，唯一索引--（主键约束，唯一约束）；

        普通的索引：index

        全文索引：fulltext（mysql自带）补充：一般开发中使用全文搜素Solr和ElasticSearch（ES）

6.实例

--查询表是否有索引
SHOW INDEXES FROM table; 
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX idindex ON table01 (id); 
-- 添加普通索引方式 1
CREATE INDEX idindex ON table02 (id);
-- 如果某列的值，是不会重复的，则优先考虑使用 unique 索引, 否则使用普通索引
-- 添加普通索引方式 2
ALTER TABLE table03  ADD INDEX idindex (id）;
添加主键索引
ALTER TABLE t04 ADD PRIMARY KEY (id）
-- 删除索引
DROP INDEX id_index ON t05
-- 删除主键索引
ALTER TABLE t06 DROP PRIMARY KEY
-- 修改索引 ， 先删除，在添加新的索引即可
   --一般我们在频繁作为查询条件的列上设置索引                             
                                        

二.Mysql事务

1.作用：用来保证数据的一致性，由一组相关的dml数据组成。

  我们对数据库的数据进行操作时，开启事务，之后的dml要么全成功，要么全失败，来保证数据的一致性。

  当执行事务操作dml时，mysql会在对应的表上加锁，防止其他用户改数据问题-（我感觉类似多线程中的同步问题）

2.基本操作

开始事务
START TRANSACTION
 --设置保存点A
SAVEPOINT A
-- 执行 dml 操作
dml语句1
SAVEPOINT b
-- 执行 dml 操作
dml语句2
-- 回退到 b
ROLLBACK TO b
-- 继续回退 A
ROLLBACK TO A
--ROLLBACK表示直接回退到事务开始的状态. 
ROLLBACK
COMMIT 提交（结束）事务，一旦提交，就不能再回退了

3.说明

3.1 不开启事务：start transaction /set autocommit = off，dml操作会默认提交，不能回滚（rollback）

3.2 innodb 引擎才支持事务机制

3.3事务的隔离级别：一个时间段内，多个连接同时操作数据库，dbms需要负责隔离操作，给它们不同的隔离级别，用来保证它们各自获取数据的准确性。

常见问题：脏读（其他事务未提交的改变让你给读上了）；

   不可重复读（其他事务提交了（修改，删除）让你前后读取结果不一样了）

   幻读（其他事务提交了（插入）让你前后读取结果不一样了）

3.4事务隔离级别

（READ UNCOMMITTED）读未提交，三种问题都会出现

（READ COMMITTED）读已提交，不会脏读

 （REPEATTABLE READ ）可重复读，不加锁，三种问题都避免了

（Serializable）可串行化，加锁三种问题都避免了

3.5语句

-- 查看当前会话隔离级别
SELECT @@tx_isolation
-- 查看系统当前隔离级别
SELECT @@global.tx_isolation
-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL [你设置的级别]

3.6事务的ACID

原子性（不可分割），一致性（dml命令同生共死），隔离性，持久性（事务一旦提交，数据永久改变，不可回滚）

三.mysql引擎

1.分类               

1.1 事务安全型- innodb（批量插入速度低，只有它支持外键和事务，行锁）

1.2 非事务安全型-mysiam，memory（都是表锁，批量插入速度mysiam高，memory非常高，memory创建的表存在内存中，访问快，默认hash索引，一旦mysql服务关闭，memory创建的表中留下表结构，内容丢失）

2.修改表的存储引擎

ALTER TABLE 表名 ENGINE =存储引擎；