MySQL进阶

MySQL架构

视图、存储过程、函数、触发器将一些处理过程保存在数据库中，然后调用，但现在的项目中使用较少（阿里手册中禁止使用存储过程，存储过程难以调试和扩展，更没有移植性）

• 连接层：处理客户端连接请求，对用户进行认证
• 服务层：可以接收SQL，调用存储过程，优化SQL，缓存数据…
• 引擎层：负责实际与文件层进行交互操作的，可以有不同的引擎选择
• 物理文件存储层：存储表数据，以及各种日志文件

MySQL引擎

引擎：指的是MySQL在数据操作时所使用的技术（事务、索引、锁、外键…），MySQL中提供了多种引擎技术。不同的引擎，特征不同。

• 查看支持的引擎：show engines;
• 查看表引擎：show table status like `表名`
• 修改表引擎： 方式 1:将 mysql.ini 中 default-storage-engine=InnoDB，重启服务.
方式 2:建表时指定 CREATE TABLE 表名(...)ENGINE=MYISAM;
方式 3:建表后修改 ALTER TABLE 表名 ENGINE = INNODB;
• 存储引擎主要有：1. MyIsam; 2. InnoDB 3. Memory, 4. Blackhole, 5. CSV, 6. Performance_Schema, 7. Archive, 8. Federated , 9 Mrg_Myisam

InnoDB(MySQL默认存储引擎)

支持事务，行级锁（并发量大），外键约束，容量大，支持索引，支持缓存。
支持主键自增，支持全文检索，不存储表的总行数，需要SQL逐行统计

MYISAM

不支持事务，不支持行锁，只有表锁（并发量小），不支持外键，支持全文检索
MYISAM存储表的总行数，统计速度快
在不同的数据操作时，选择合适的引擎操作，提高效率

MySQL索引（排好序的快速查找的数据结构）

数据库表中可以存储大量的数据，如果需要查找到其中的某一行，我们需要从第一行逐行查询，效率低下。我们可以为数据表中的主键建立索引，方便查询。
什么是索引（index）？
索引是排好序的数据结构。（例如二叉树。）我们借助索引，索引类似一个目录，通过目录可以快速定位到数据位置，拿到物理地址。
帮助MySQL提高查询效率的是数据结构。能实现快速定位数据的一种存储结构，其设计思想是以空间换取时间。
本质：通过不断缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机事件变成顺序事件。

索引的优势

• 提高数据检索效率，降低数据库IO成本，次数；
• 通过索引列进行条件查询，排序，分组，可以提高效率

索引的劣势

• 维护索引需要耗费数据库资源；
• 需要占用磁盘空间；
• 对表数据进行增删改的时候，因为要维护索引，速度会受到影响。

索引创建原则

建议添加索引情况：

1. 主键
2. 外键
3. 查询条件 where后面的列
4. 排序，分组使用的列

---

不建议添加索引的情况：

1. 表数据太少
2. 增删改频繁的表，因为更改数据需要更新索引树
3. 不用做查询条件
4. 数据重复率高的，例如性别

索引分类

索引的分类：主键索引、唯一索引、普通索引、复合索引

• 按数据结构分类：B+tree索引、Hash索引、全文索引（*）
• 按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）
• 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引
• 按字段个数分类：单列索引、联合索引

---

InnoDB支持

• 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
• 单值索引（单列索引/普通索引）：即一个索引只包含一个列，一个表中可以有多个单列索引 id (age index) (name index) bir
• 唯一索引：索引列的值必须唯一，允许为空值，且只存在一个null（区别主键索引，值不为null）
• 复合索引：即一个索引包含多个列 id (age name) index 基于多个列的索引

---

MyISAM支持（MySQL5.7版本之前只能用于MyISAM引擎）

• Full Text全文索引：全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引。需要模糊查询时，一般索引无效，这时候就可以使用全文索引了。 CREATE FULLTEXT INDEX 索引名 ON 表名(字段名) WITH PARSER ngram;
SELECT 结果 FROM 表名 WHERE MATCH(列名) AGAINST(‘搜索词');

---

一、普通(单值)索引创建的两种方式：

1. 建表之前创建(craete table t_user(id primary key,name,key(name));)
2. 建表之后创建（create index name_index on t_user(name);）

二、唯一索引创建的两种方式：

1. 建表之前创建(craete table t_user(id primary key,name,unique(name));)
2. 建表之后创建（create unique index name_index on t_user(name);）

三、复合索引创建的两种方式：

1. 建表之前创建(craete table t_user(id primary key,name,age,key(name,age));)
2. 建表之后创建（create index nameAge_index on t_user(name,age);）

组合索引最左前缀原则

利用复合索引：

1. 最左前缀原则/左包含原则；（比如根据name,age,bir设置的索引，只能利用name/name,age/name,age,bir来索引）
2. MySQL引擎在查询为了更好利用索引，在查询过程中会动态调整查询字段顺序以便利用索引

---

索引的原理

1. 插入id无序数据
2. 查询结果发现id居然排序了
3. 说明主键索引进行了排序：方便快速查询（排序之后查询更快）

---

索引数据结构

MySQL使用自增主键，二叉树会像链表一样单边增长。红黑树虽然会旋转平衡，但是也是一边增长，树的高度会很大。
B树（平衡多路查找树，叶子节点之间没有联系）

B+树(叶子节点之间有联系)

MySQL最终选择了B+Tree：

1. 非叶子节点只存储键值信息，不存储数据，可以放更多的索引
2. 排好序的，一个节点可以存储多个数据
3. 所有叶子节点之间都有一个链指针
4. 数据记录都存放在叶子节点中
   （注：MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，减少一次对磁盘的I/O操作）

聚簇索引和非聚簇索引

聚簇索引和非聚簇索引：

• 聚簇索引：将数据存储和索引放到了一块，索引结构的叶子节点保存了行数据。找到了索引就是找到了需要的数据，例如innodb中的主键索引（一级索引），就是聚簇索引。
  
  

• 非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置。需要根据索引上的值（主键）再次回表查询，非聚簇索引（MYISAM默认非聚簇索引）
  

在InnoDB中，在聚簇索引之上创建的索引称为辅助索引，非聚簇索引都是辅助索引，像复合索引、普通索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问位置总是需要二次查找。例如为name列添加单列索引，我们通过name并不能找到数据，而是需要再找到主键索引，通过主键索引找到数据，这种索引也成为非聚簇索引

---

在 MySQL 的 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询，因此我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个：

• 聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引（通常是主键 ID）。
• 聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引。
• 聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制。

回表查询

评判标注就是查询是否直接能够找到想要的数据。
主键，一级索引，可以直接找到数据，不需要回表查询。
二级索引，如果通过索引列还查询了其他列，需要回表查询，如果只需要查询索引列本身，就不需要回表查询。

事务

一次对数据库操作过程，这个过程有多条SQL执行，这多条SQL在一次执行过程要么成功执行，要么都不执行。保证数据的完整性，MySQL中只有InnoDB支持事务

事务特性

一般来说，事务必须满足四个条件（ACID）：原子性（或称不可分割性）、一致性、隔离性、持久性。

原子性

一个事务中多条SQL要么都成功执行，要么都不执行，回滚到事务执行前的状态

持久性

事务处理结束后，对数据的修改是永久的，即便宕机数据也不会丢失。

隔离性

数据库事务是可以有多个同时执行的，需要对多个事务进行隔离。
数据库允许多个并发事务同时对其数据进行读和写的能力。事务隔离分为不同级别：读未提交（Read uncommitted）、读提交(read Committed)、可重复读(repeatable read)、串行化(Serializable)

一致性

前面的原子性、隔离性、持久性都是为了保证一致性。（保证数据是完整可靠的），例如转账，保证操作后的结果与预期一致。

事务设置

默认情况下，MySQL启动自动提交模式（变量autommit为ON）。这意味着，只要你执行DML操作的语句，MySQL会立即隐式提交事务。

• 关闭自动事务提交
  SET GLOABLE autommit=0；
• 查看事务自动提交开启情况
  SHOW GLOABLE VARIABLES LIKE ‘autommit’;
• 开启事务
  BEGAIN;
• 回滚事务(提交事务前可以回滚)
  ROLLBACK;
• 提交
  COMMIT;

事务隔离级别

只有Innodb支持事务，所以这里说的事务隔离级别指的是InnoDB下的事务隔离级别。
事务隔离分为不同级别：读未提交（Read uncommitted）、读提交(read Committed)、可重复读(repeatable read)、串行化(Serializable)
查看隔离级别：
select @@session.transaction_isolation,@@transaction_isolation;

读未提交（Read uncommitted）

一个事务可以读取另一个事务还未提交的数据，造成脏读（垃圾数据，别的数据可能会回滚），不可重复读，幻读
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

读已提交（Read Committed）

一个事务只能读取到另一个事务已经提交后的数据，解决了脏读问题，但是没有解决可重复读和幻读问题；
不可重复读问题：一个事务在同一次读操作中，先后读取两次数据，结果两次数据不一致，称为不可重复读。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED

可重复读（Repeatable read）—MySQL默认隔离级别

一个事务在同一次事务中，多次读取同一个数据是一致的，即使别的事务修改并提交了数据，也是一致的（第二次与第一次读取数据结果一致）；
解决了不可重复读、脏读问题，但是没有完全解决幻读问题。

幻读：同一个事务中，读取了两次，结果读到的数量不同，称为幻读
可重复读可以解决正常查询语句中出现的幻读问题，但是在查询语句后面添加FOR UPDATE就会出现幻读问题。
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

串行化（Serializable）

事务串行执行，避免了以上所有问题。缺点：效率非常低
事务只能一个一个执行，即使一个事务写，另一个事务读都不可以。
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE

事务实现原理

原子性实现

实现原子性的关键就是当事务回滚时能够撤销所有已经成功执行的SQL语句。InnoDB实现回滚，靠的是 undolog：当事务对数据库进行修改时，InnoDB会生成对应的 undo log；如果事务执行失败或调用了 rollback，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。
mysql使用undolog日志文件，用来记录增删改的反向操作。当出现异常，事务回滚时，执行对应操作的反向操作即可还原。

undo log 属于逻辑日志，它记录的是 sql 执行相关的信息。当发生回滚时，InnoDB 会根据 undo log 的内容做与之前相反的工作：对于每个 insert，回滚时会执行 delete；对于每个 delete，回滚时会执行 insert；对于每个 update，回滚时会执行一个相反的 update，把数据改回去

持久性实现

redo log 叫做重做日志，是保证事务持久性的重要机制。
当数据库事务提交后，保证数据是不可撤销的。当SQL发送到MySQL后事务还未提交之前，如果发生断电/宕机，会先将SQL保存到redo log日志文件中，在MySQL重新启动时执行redo log中的SQL。

隔离性实现

读已提交—每次读都是最新的，提交到数据库的数据；
可重复读

隔离级别实现原理（MVCC多版本并发控制）

为了提高并发访问，读写可以同时进行。
MVCC（多版本并发控制 Multi-Version Concurrent Control），是
MySQL 提高性能的一种方式，配合 Undo log 和版本链,让不同事务的读-写、写-读操作可以并发执行，从而提升系统性能。
MVCC 使得数据库读不会对数据加锁，普通的 SELECT请求不会加锁，
提高了数据库的并发处理能力 。借助MVCC，数据库可以实 现 READ COMMITTED，REPEATABLE READ 等隔离级别.
读已提交级别: 称为当前读,当每个事物每次读取时,会生成一个readVew(读视图/快照),读取的是最新数据.
可重复读级别: 称为快照读,当一个事务第一次查询时,会生成一个 readView（快照）,第二次查询时仍会从当前 readView 中读数据，所以可以实现可重复读。

锁机制

行锁 间隙锁 表锁（按锁的粒度分）

表锁

表级锁，操作（增删改）时，会给整张表加锁。MyISAM支持表级锁，InnoDB支持表级锁，但默认不使用。虽然加锁开销小，但并发性能低。
表级锁是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加
锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。最常使用的
MYISAM 与 INNODB 都支持表级锁定。表级锁定分为表共享锁与表排他锁。
特点：开销小，加锁快；锁定粒度大，发出锁冲突的概率最高，并发度最低。

间隙锁

满足某些条件，获取某个区间
锁的是一个区间,当我们用范围条件而不是相等条件检索数据，InnoDB 会给
符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙",InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。

行锁

**一个事务对某行操作，其他事务不能对本行操作（增删改）。**行级锁是 Mysql 中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：加锁开销大，加锁慢；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

共享锁 排它锁（针对查询语句）

可以为查询语句添加共享锁和排它锁：

共享锁（又称读锁）

又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据
集的排他锁。若事务 T 对数据对象 A 加上共享锁，则事务 T 可以读 A,但其他事务只能再对 A 加共享锁（其他事务只能读），而不能加排他锁（其他事务不能修改），直到 T 释放 A 上的共享锁。这保证了其他事务可以读 A，但在事物 T 释放 A 上的共享锁之前,其他不能对 A 做任何修改。
加共享锁可以使用 select … lock in share mode 语句。

排它锁（又称写锁）

又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得
相同的数据集共享读锁和排他写锁（其他事务既不能读也不能写）。若事务 T 对数据对象 A 加上排他锁，事务 T可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到 T 释放 A 上的锁。
update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果加排他锁可以使用 select …for update 语句.

SQL优化

项目上线初期，由于业务数据量相对较少，一些 SQL 的执行效率对程序运
行效率的影响不太明显，而开发和运维人员也无法判断 SQL 对程序的运行效率有多大，故很少针对 SQL 进行专门的优化，而随着时间的积累，业务数据量的增多，SQL 的执行效率对程序的运行效率的影响逐渐增大，此时对 SQL 的优化就很有必要。

SQL优化的一些办法

1.查询 SQL 尽量不要使用 select *，而是具体字段

节省资源、减少开销。

2.避免在 where 子句中使用 or 来连接条件

反例:SELECT * FROM user WHERE id=1 OR salary=5000
正例:使用 union all 把两个两个 SQL 结果合并
使用 or 可能会使索引失效，从而全表扫描；
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary
查询条件时，它还得全表扫描；

3. 尽量使用数值替代字符串类型

正例
主键（id）：primary key 优先使用数值类型 int
性别（gender）：0 代表女，1 代表男；数据库没有布尔类型，mysql
推荐使用 tinyint
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；
而对于数字型而言只需要比较一次就够了；
字符会降低查询和连接的性能，并会增加存储开销；

4. 使用 varchar 代替 char

varchar 变长字段按数据内容实际长度存储，可以节省存储空间；
char 按声明大小存储，不足补空格；
其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

5. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by group by 涉及的列上建立索引

6. 应尽量避免索引失效

6.1 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如:select id from t where num=10 or num=20

6.2 in 和 not in 也要慎用，否则会导致全表扫描，如：select id from t where num in(1,2,3),对于连续的数值，能用 between 就不要用 in ,select id from t where num between 1 and 3

6.3 模糊查询也将导致全表扫描

select id from t where name like ‘%abc%’

6.4 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如: select id from t where substring(name,1,3)=‘abc’

7. 提高 group by 语句的效率

反例:先分组，再过滤
正例:先过滤，后分组

8. 清空表时优先使用 truncate

truncate table比 delete 速度快，且使用的系统和事务日志资源少. delete 语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate
table 通过释放存储表数据所用的数据页来删除数据.

9. 表连接不宜太多，索引不宜太多，一般 5 个以内

联的表个数越多，编译的时间和开销也就越大
每次关联内存中都生成一个临时表
应该把连接表拆开成较小的几个执行，可读性更高

10. 深度分页问题

反例
select id,name from account limit 100000,10;
正例
select id,name FROM account where id > 100000 order by id limit 10;

11. 使用 explain 分析 SQL 执行计划

执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL
是如何处理你的 SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

EXPLAIN 作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用

EXPLAIN 使用

在 select 语句之前增加 explain 关键字，执行查询会返回执行计划的信息，
而不是执行 SQL。
EXPLAIN SELECT * FROM USER WHERE id = 1
expain 出来的信息有 12 列，分别是:
id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra

概要描述

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
rows:扫描出的行数(估算的行数)

---

id
SELECT 识别符,这是 SELECT 的查询序列号, id 如果相同，可以认为是一组，从上往下顺序执行, 在所有组中，id 值越大，优先级越高，越先执行, EXPLAIN SELECT * FROM employee e,dept d WHERE e.deptId = d.id
EXPLAIN SELECT * FROM employee e WHERE e.deptId = (SELECT id FROM dept d WHERE d.id = 1)

---

select_type
表示查询中每个 select 子句的类型
1.SIMPLE(简单 SELECT，不使用 UNION 或子查询等)
2.PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的
select 被标记为 PRIMARY)
3.SUBQUERY(子查询中的第一个 SELECT，结果不依赖于外部查询)
4.DERIVED(派生表的 SELECT, FROM 子句的子查询)
5.UNION(UNION 中的第二个或后面的 SELECT 语句)

---

type
对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。
常用的类型有：system>const>eq_ref>ref>range>index>ALL（从左到右，
性能从好到差）.

---

system
表只有一行记录（等于系统表），平时不会出现，这个也可以忽略不计.

---

const:
表示通过索引一次就找到了，const 用于比较 primary key 或者 unique索引。

---

eq_ref:
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描. ref: 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体.

---

range:
只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的 where 语句中出现了 between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

---

index:
Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常比 ALL 快，因为索引文件通常比数据文件小。也就是说虽然 all 和 Index 都是读全表，但 index 是从索引中读取的，而 all 是从硬盘中读的）

---

All:
Full Table Scan，将遍历全表以找到匹配的行 一般来说，得保证查询至少达到 range 级别，最好能达到 ref.

---

possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

---

key
实际使用的索引。如果为 NULL，则没有使用索引,或者索引失效. ken_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好.