MySQL索引与存储引擎、事物

数据库索引

是一个排序的列表，存储着索引值和这个值所对应的物理地址

无须对整个表进行扫描，通过物理地址就可以找到所需数据

是表中一列或者若干列值排序的方法

需要额外的磁盘空间

类型

普通索引

最基本的索引类型，没有唯一性之类的限制

创建普通索引的方式

直接创建

create index a1 on kgc.kc65(id);

修改表的方式创建

alter table kgc.kc65 add index a2 (id);

创建表时创建

create table kgc.kc1 (id int, name varchar(255),age int,index a1(id));

唯一索引

与“普通索引”基本相同

与普通索引的区别就是索引列的所有值只能出现一次，即必须唯一

创建唯一索引的方式

create unique index a3 on kgc.kc65(id);

alter table kgc.kc65 add unique a4 (id);

create table kgc.kc2 (id int, name varchar(255),age int,unique a1(id));

主键索引

是一种特殊的唯一索引，指定为“PRIMARYKEY”

一个表只能有一个主键，不允许有空值

创建主键索引的方式

CREATE TABLE `kc3` (`id` int(11) NOT NULL AUTO_INCREMENT ,`title` char(255) NOT NULL ,PRIMARY KEY (`id`)
);

组合索引(单列索引与多列索引)

可以时单列上创建的索引，也可以时在多列上创建的索引

最左原则，从左往右依次执行

创建组合索引的方式

全文索引

MySQL从3.23.23版开始支持全文索引和全文检索

创建索引的原则依据

                创建索引的原则依据
                        表的主键、外键必须有索引
                        记录数超过300行的表应该有索引
                        经常与其他表进行连接的表，在连接字段上应该建立索引
                        唯一性太差的字段不适合建立索引
                        更新太频繁的字段不适合创建索引

经常出现在where子句中的字段，特别是大表的字段，应该建立索引

索引应该建在选择性高的字段上

索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

查看索引

show index from kc65;
show keys from kc65;

唯一性索引为0，主键索引为1

删除索引

alter table kgc.kc65 drop index a1;
drop index  a3 on kgc.kc65;

事务的概念

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致

事务控制的方法

事务处理命令控制事务

begin：开始一个事务

commit：提交一个事务

rollback：回滚一个事务

使用set命令进行控制

set autocommit=0;禁止自动提交

set autocommit=1;开启自动提交

存储引擎

MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制，索引技巧、锁定水平并最终提供不同的功能和能力、这些不同的技术以及配套的功能在MySQL中称为存储引擎

MySQL数据库中的组件，负责执行实际的数据I/O操作

myisam的介绍

MyISAM不支持事务，不不支持外键

访问速度快

对事务完整性没有要求

MyISAM在磁盘上存储成三个文件

.frm文件存储表定义

数据文件的扩展名为.MYD(MYData)

索引文件的扩展名是.MYI(MYIndex)

表即锁定形式，数据在更新时锁定整个表

数据库在读写过程中相互阻塞

会在数据写入的过程阻塞用户数据的读取

也会在数据读取的过程中阻塞用户的数据写入

数据单独写入或读取，速度过程较快且占用资源相对少

MyISAM支持的存储格式

静态表

动态表

压缩表

MyISAM适用的生产场景举例

公司业务不需要事务的支持

单方面读取或写入数据比较多的业务

MyISAM存储引擎数据读写都比较频繁场景不适合

适用读写并发访问相对较低的业务

数据修改相对较少的业务

InnoDB

支持4个事务隔离级别

1. 读未提交（READ UNCOMMITTED）

概述：在此级别下，事务可以读取到其他未提交事务中的数据。这可能导致“脏读”问题，即一个事务读取到了另一个尚未提交的事务所做的修改。
- 特点：并发性最高，但一致性最差。
  - 应用场景：一般不推荐在生产环境中使用，因为它无法保证数据的一致性。

2. 读已提交（READ COMMITTED）

概述：事务只能读取到其他已提交事务中的数据，避免了脏读的问题。但是，由于每次查询都会返回最新的已提交数据，因此在同一个事务中可能会出现“不可重复读”的问题。
- 特点：每次读取都会获取最新的一致性视图，避免了脏读，但可能出现不可重复读。
  - 应用场景：这是互联网最常用的隔离级别之一，适用于需要避免脏读但可以接受不可重复读的应用场景。

3. 可重复读（REPEATABLE READ）

概述：在可重复读级别下，事务在开始时读取的数据快照会被保存，并在事务结束时释放。这意味着在同一个事务中多次读取同一数据时，这些数据是一致的，避免了不可重复读的问题。但是，仍然可能出现“幻读”问题。
- 特点：通过多版本并发控制（MVCC）实现，确保了事务内数据的一致性。
  - 应用场景：这是InnoDB的默认隔离级别，适用于需要保证数据一致性和可重复读的应用场景。

4. 串行化（SERIALIZABLE）

概述：通过完全串行的方式处理事务，避免了脏读、不可重复读和幻读等所有并发问题。每个事务都会按照提交的顺序依次执行，不允许任何并发操作。
- 特点：一致性最好，但并发性最差。
  - 应用场景：适用于对一致性要求极高，且可以接受较低并发性的应用场景。

行级锁定，但是全表扫描仍然会是表级锁定

读写阻塞与事务隔离级别相关

能非常高效的缓存索引和数据

表与主键以组的方式存储

支持分区、表空间，类似Oracle数据库

支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

对硬件资源要求还是比较高的场合

grep default-storage-engine /etc/my.cnf

show create table t1\G;  //查看建表语句

修改方式

CREATE TABLE `t1` (`c1` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8