写的章节可能会比较乱，请选择自己想学的章节进行阅读

MySQL存储引擎

存储引擎相关命令

1.查看存储引擎

show engines;

2.查看默认的存储引擎

show variables like '%storage_engine%'; 
或 
SELECT @@default_storage_engine;

3. 修改默认的存储引擎方式一：

SET DEFAULT_STORAGE_ENGINE=MyISAM;

方式二：修改配置文件

default-storage-engine=MyISAM 
# 重启服务 
systemctl restart mysqld.service

4.创建表的时候使用指定的存储引擎

CREATE TABLE 表名( 建表语句; ) ENGINE = 存储引擎名称;

5.修改表的存储引擎

ALTER TABLE 表名 ENGINE = InnoDB;

存储引擎介绍

MyISAM

MySQL5.5之前默认的存储引擎
优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用不支持事务，表锁，对并发量大的情况下支持性并不是很友好
应用场景：只读应用或者以读为主的业务

INNODB

在MySQL5.5以后，默认的存储引擎就开始使用InnoDB
InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。可以确保事务的完整提交(Commit)和回滚(Rollback)。
MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM 不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
除了增加和查询外，还需要更新、删除操作，那么，应优先选择InnoDB存储引擎。
InnoDB 支持行锁，所以在并发量大的情况下，性能是比较好的

CSV

CVS引擎在创建表的时候还会创建一个 .cvs格式的数据文件，每一条存储的数据以逗号进行分割

Memory

Memory存储引擎，它会将表数据读取到内存中，所以访问的速度会很快，当服务崩溃的时候表的数据会丢失，表的结构还是会存在硬盘上面，要求存储的数据是数据长度不变的格式，比如，Blob和Text类型的数据不可用(长度不固定的)。

--主要 -- Memory同时支持哈希（HASH）索引和 B+树索引。
Memory表至少比MyISAM表要快一个数量级。
MEMORY 表的大小是受到限制的。表的大小主要取决于两个参数，分别是 max_rows 和 max_heap_table_size 。其中，max_rows可以在创建表时指定；max_heap_table_size的大小默认为16MB，可以按需要进行扩大。
数据文件与索引文件分开存储。
缺点：其数据易丢失，生命周期短。基于这个缺陷，选择MEMORY存储引擎时需要特别小心。

对比：

索引

什么是索引：索引是一种排好序的快速查找的数据结构

索引的优点：

（1）提高数据检索的效率，降低数据库的IO成本。

（2）通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。

（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

索引的缺点：

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

索引的数据结构 TODO

不同的执行引擎支持不同的数据结构，

索引分类

从逻辑上进行划分：普通索引、唯一索引、主键索引、全文索引

从物理实现上进行划分：聚簇索引和非聚簇索引。

从字段上进行划分：单列索引和联合索引

不同的存储引擎会支持的索引类型也不一样InnoDB ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； MyISAM ：支持 B-tree、Full-text 等索引，不支持 Hash 索引； Memory ：支持 B-tree、Hash 等索引，不支持 Full-text 索引； NDB ：支持 Hash 索引，不支持 B-tree、Full-text 等索引； Archive ：不支持 B-tree、Hash、Full-text 等索引；

如何去创建一个索引

创建表的时候创建索引

CREATE TABLE table_name[col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length])
[ASC | DESC]

UNIQUE 、 FULLTEXT 和 SPATIAL 为可选参数，分别表示唯一索引、全文索引和空间索引；

INDEX 与 KEY 为同义词，两者的作用相同，用来指定创建索引； index_name 指定索引的名称，为可选参数，如果不指定，那么MySQL默认col_name为索引名；

col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；

length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度； ASC 或 DESC 指定升序或者降序的索引值存储。

创建唯一索引

CREATE TABLE test0
( 
    id INT NOT NULL,
    name varchar(30) NOT NULL, 
    INDEX uk_namex_id(name) 
);

创建唯一索引

CREATE TABLE test1
( 
    id INT NOT NULL,
    name varchar(30) NOT NULL, 
    UNIQUE INDEX uk_idx_id(id) 
);

创建主键索引

CREATE TABLE test2
( 
    id INT NOT NULL,
    NAME VARCHAR(30) NOT NULL, 
    PRIMARY KEY(id)
);

创建一个复合索引

CREATE TABLE test3
( 
    id INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    author VARCHAR(30) NOT NULL,
    KEY  multi_idx(`name`,author)
);

创建全文索引

CREATE TABLE test4
( 
    id INT NOT NULL,
    NAME VARCHAR(30) NOT NULL,
    author VARCHAR(30) NOT NULL,
    info VARCHAR(255),
    FULLTEXT KEY  idx_info(info)
);

创建空间索引

CREATE TABLE test5(
    geo GEOMETRY NOT NULL,
    SPATIAL INDEX spa_idx_geo(geo)
) ENGINE=MYISAM;

除了以上的创建方式还可以使用alter的方式和create index来创建索引

alter 方式的创建语法：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] 
[INDEX | KEY] [index_name] (col_name[length],...) 
[ASC | DESC]

create index 创建方式：

CREATE [UNIQUE | FULLTEXT | SPATIAL] 
INDEX index_name ON table_name (col_name[length],...) 
[ASC | DESC]

如何进行索引的删除

alter

ALTER TABLE table_name DROP INDEX index_name;

index

DROP INDEX index_name ON table_name;

查看创建的索引

SHOW INDEX FROM 表名

索引的设计原则

在那些情况下适合创建索引

1、经常进行where查询的字段适合创建索引

假如现在要查询student_id = 123110 的字段

没有添加索引之前查找的时间大约是0.7秒

添加索引之后在进行查询

2、如果某个字段的值是唯一不重复的，那么就适合创建索引，例如主键的Id

3、经常进行group by 或者 order by的字段适合创建索引

4、UPDATE、DELETE 的 WHERE 条件列

5、DISTINCT 字段需要创建索引

6、多表 JOIN 连接操作时，可以创建索引，注意点：

首先，连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率。其次，对 WHERE 条件创建索引，因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下，没有 WHERE 条件过滤是非常可怕的。最后，对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致。比如 course_id 在 student_info 表和 course 表中都为 int(11) 类型，而不能一个为 int 另一个为 varchar 类型。

7、使用列的类型小的创建索引

8、使用字符串前缀创建索引

字符串的长度截取多少比较合适：

计算公式：count(distinct left(列名, 索引长度))/count(*)

9、区分度高(散列性高)的列适合作为索引

10、使用最频繁的列放到联合索引的左侧（最左前缀原则）

11、在多个字段都要创建索引的情况下，联合索引优于单值索引

在那些情况下不适合创建索引

1、在where中使用不到的字段，不要设置索引

2、数据量小的表最好不要使用索引

3、有大量重复数据的列上不要建立索引

4、避免对经常更新的表创建过多的索引

5、不建议用无序的值作为索引

6、不要定义冗余或重复的索引

分析查询语句：EXPLAIN

接下来就一个个的认识上面的这一堆东东

这个id会有三种情况 Id相同、Id不同、Id相同不同

Id相同

在Id相同的情况下，他是进行顺序执行的，先执行表s1,然后是s2

Id不同

Id大的先执行，首先驱动s2然后去驱动s1

select_type

type

Type有以下几种情况：system ， const ， eq_ref ， ref ， fulltext ， ref_or_null ， index_merge ， unique_subquery ， index_subquery ， range ， index ， ALL 。

从最好到最坏的顺序依次是：system>const>eq_ref>ref>range>index>all

system

一张表只有一个记录（等同于系统表），这是const类型的特例，在平时也不会出现

const 常量

表示这通过索引一次性就可以找到了，const用于比较primary key 或者 unique key。因为只是匹配一行数据，所以很快

eq_ref

唯一性的索引扫描，对于每一个索引键，表中只有一条记录与之匹配，常常用在主键索引或者唯一索引

非唯一性的索引扫描，返回匹配某个单独值的所有行，他的本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行。

range

只是检索一定范围内的行数，使用一个索引来进行选择 key列显示使用了哪一个索引，一般就是在where 语句种出现了between < > in 等子句

index

全索引扫描，遍历索引树，比All快

全盘扫描，

**possible key 和 key **

Possible key 如果一张表有多个索引，查询所涉及到的字段将会被列出，但是在实际种不一定被应用得到

key 实际使用到的索引，如果为null，则证明没有使用到索引，如果查询种使用了覆盖索引，则该索引仅仅会出现在key的列表中

key_len

表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度。在不丢失精度的情况下，长度越短越好，key_len显示的值是索引字段的最大可能的长度，并非实际使用的长度。

key_len 的计算公式：

varchar(10)变长字段且允许NULL = 10 * ( character set： utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) varchar(10)变长字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(变长字段) char(10)固定字段且允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) char(10)固定字段且不允许NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)

ref

显示索引的那一列被使用了，如果可能的话，是一个常数。那些列或者常量被用于查找索引列上的值。

row

根据表统计信息以及索引的选用情况，大致估算出来找到所需的记录所需要读取的行数。

Extra

索引失效

1、全值匹配我最爱

2、最左前缀法则

如果说是使用了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不会跳过索引中的列

首先创建索引：

//索引的顺序依次是 student_id,course_id,name
CREATE INDEX idx_stuId_courseId_name ON student_info(student_id,course_id,`name`);

接下来看以下语句的执行结果：

1、 EXPLAIN SELECT * FROM student_info WHERE student_id=3027

2、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009

3、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = 'ewTdJm'

索引失效的语句：

1、EXPLAIN SELECT * FROM student_info WHERE course_id = 10009

变成了全盘扫描

2、EXPLAIN SELECT * FROM student_info WHERE name = 'ewTdJm'

同样变成了全盘扫描

3、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND name = 'ewTdJm'

只用到了一个索引，后续的索引四失效了，

口诀：带头大哥不能死，中间兄弟不能断

3、不在索引列上做任何操作（计算，函数，类型转换），会导致索引失效而转向全盘扫描。

1、 EXPLAIN SELECT * FROM student_info WHERE student_id = 3027

没问题，可以使用到索引

2、 EXPLAIN SELECT * FROM student_info WHERE CONCAT(student_id)

从索引扫描转向全盘扫描

4、存储引擎不能使用索引中范围条件右边的列

1、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = 'ewTdJm'

2、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id > 10009 AND name ='ewTdJm'

可以看到并没有使用到name的索引列

范围后面全部失效。

5、尽量使用覆盖索引减少select 星（只访问索引查询，索引列和查询列一致）

1、EXPLAIN SELECT * FROM student_info

全盘扫描

2、 EXPLAIN SELECT student_id,course_id,name FROM student_info

只扫描索引

6、mysql在使用不等于 != <> 的时候无法使用索引会导致全盘扫描

1、 EXPLAIN SELECT * FROM student_info WHERE student_id = 3027

在使用 = 号的情况下是可以使用到索引的

2、 EXPLAIN SELECT * FROM student_info WHERE student_id != 3027

实际上变成了全盘扫描

7、is null is not null 也无法使用索引

1、EXPLAIN SELECT * FROM student_info WHERE student_id IS NULL

2、EXPLAIN SELECT * FROM student_info WHERE student_id IS NOT NULL

8、like以通配符开头（"%123..."） mysql索引会失效变成全盘扫描的操作

百分like加到右边，如果非得使用两边% ，可以利用覆盖索引

9、字符串不加单引号索引会失效

1、 EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = '1001'

用到了这三个的索引

2、EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = 1001

最后一个字段的name索引是没有被用到的。

10、少用OR，用它来链接的时候会导致索引失效

1、 EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = '1001'

2、 EXPLAIN SELECT * FROM student_info WHERE student_id=3027 AND course_id = 10009 AND name = '1001' OR name = '10002'

变成全盘扫描了

索引优化建议

对于单值索引，尽量选择针对当前Query过滤性更好的索引

在选择组合索引的时候，当前Query中过滤性最好的字段在索引吮吸中，位置悦靠左越好，尽量包含当前Query中的where子句中更多字段的索引。

尽可能通过分析统计信息和调整Query的写法来达到选择合适索引的目的。

事务

什么是事务？

事务是一组逻辑操作的单元，使数据从一个状态变成另外一个状态

事务的处理原则

保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交( commit )，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚( rollback )到最初状态。简单来说就是要么都成功，要么都失败。

事务的四大特性ACID

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性(consistency)

根据定义，一致性是指事务执行前后，数据从一个合法性状态变换到另外一个合法性状态。这种状态是语义上的而不是语法上的，跟具体的业务有关。数据要复合具体的业务场景

隔离性（Isolation）

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

持久性（durability）

数据的持久性指的是一旦事务进行提交，数据就会永久性的保存在数据库中。持久性是通过事务日志来保证的。日志包括了 重做日志 和 回滚日志 。当我们通过事务对数据进行修改的时候，首先会将数据库的变化信息记录到重做日志中，然后再对数据库中对应的行进行修改。这样做的好处是，即使数据库系统崩溃，数据库重启后也能找到没有更新到数据库系统中的重做日志，重新执行，从而使事务具有持久性。

如何使用事务

使用事务有两种方式，分别为 显式事务 和 隐式事务 。

显示事务

开启事务

begin 或者 start transaction
    操作语句
commit 或者 rollback

使用Start transaction 开始事务可以指定相应的修饰符号

① READ ONLY ：标识当前事务是一个只读事务，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

② READ WRITE ：标识当前事务是一个读写事务，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据。

③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

隐式事务

MySQL中有一个系统变量 autocommit ：

关闭事务的自动提交方法

SET autocommit = OFF; 或 SET autocommit = 0; 或者 set autocommit = false

MySQL中那些情况会被事务隐式的提交？当autocommit = ON的状态下

情况一、当我们在一个事务还没提交或者回滚时就又使用 START TRANSACTION 或者 BEGIN 语句开启了另一个事务时，会隐式的提交上一个事务。

情况二、当前的 autocommit 系统变量的值为 OFF ，我们手动把它调为 ON 时，也会隐式的提交前边语句所属的事务。

情况三、数据定义语言也会自动的提交事务 alter、create、drop ...

情况四、使用 LOCK TABLES 、 UNLOCK TABLES 等关于锁定的语句也会隐式的提交前边语句所属的事务。

情况五、使用Insert、update、delete、truncate 语句的时候，也会进行事务的隐式提交

保存点 SAVEPOINT 事务回滚点

begin
    语句1 ...
    语句2 ...
    savepoint s1
    rollback to s1

事务的隔离级别

表数据：

CREATE TABLE student (
    studentno INT,
    NAME VARCHAR(20),
    class VARCHAR(20),
    PRIMARY KEY (studentno)
) ENGINE=INNODB CHARSET=utf8;

INSERT INTO student VALUES(1, '小谷', '1班');

可能产生的问题

脏写

对于两个事务 Session A、Session B，如果事务Session A 修改了另一个未提交事务Session B 修改过的数据，那就意味着发生了脏写

脏读

对于两个事务 Session A、Session B，Session A 读取了已经被 Session B 更新但还没有被提交的字段。之后若 Session B 回滚，Session A 读取的内容就是临时且无效的。

幻读

对于两个事务Session A、Session B, Session A 从一个表中读取了一个字段, 然后 Session B 在该表中插入了一些新的行。之后, 如果 Session A 再次读取同一个表, 就会多出几行。那就意味着发生了幻读。

不可重复读

对于两个事务Session A、Session B，Session A 读取了一个字段，然后 Session B 更新了该字段。之后 Session A 再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

四种隔离级别

读未提交(READ UNCOMMITTED)

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

读已提交(READ COMMITTED)

读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。 可重复读(REPEATABLE READ)
读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。 串行化(SERIALIZABLE)
确保事务可以从一个表中读取相同的行。在这个事务持续期间，禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免，但性能十分低下。能避免脏读、不可重复读和幻读。

性能指标：

隔离级别的查看

# 查看隔离级别，MySQL 5.7.20的版本之前： 
mysql> SHOW VARIABLES LIKE 'tx_isolation';


# 查看隔离级别，MySQL 5.7.20的版本及之后：
SHOW VARIABLES LIKE 'transaction_isolation';


#或者不同MySQL版本中都可以使用的： 
SELECT @@transaction_isolation;

MySQL 5.7 的默认隔离级别

隔离级别的设置

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别; 
#其中，隔离级别格式： 
> READ UNCOMMITTED 
> READ COMMITTED 
> REPEATABLE READ 
> SERIALIZABLE

或者采用以下的方式

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔离级别' 
#其中，隔离级别格式： 
> READ-UNCOMMITTED 
> READ-COMMITTED 
> REPEATABLE-READ 
> SERIALIZABLE

事务日志

待更新--------

MySQL学习下篇