MySQL高级

一、索引

可以简单理解为排好序的快速查找数据结构

1、MySQL索引类型

主键索引 Primary key
Innodb中又叫聚簇索引,InnoDB存储引擎的表会存在主键（唯一非null）如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚集索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。

单列索引:索引中只包含一个列。

组合索引:在多个字段上建立的索引,只有在查询条件中顺序的使用了这些索引,索引才有效果。使用组合索引遵循最左前缀原则。

Unique(唯一索引):索引列必须唯一,但允许有空值,若是组合索引,则列值的组合必须保持唯一。

Key(普通索引),是MySQL中基本的索引类型,允许列中有空值,重复值。

FULLTEXT(全文索引):全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建

SPATIL(空间索引):空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列必须声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

2、基本语法

创建 CREATE [UNIQUE ] INDEX [indexName] ON table_name(column))
删除 DROP INDEX [indexName] ON mytable;
查看 SHOW INDEX FROM table_name

使用Alter命令
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list) 
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
ALTER TABLE tbl_name ADD INDEX index_name (column_list):
添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
该语句指定了索引为 FULLTEXT ，用于全文索引。

3、索引的创建时机

3.1 适合创建索引的情况

1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其它表关联的字段，外键关系建立索引
4. 单键/组合索引的选择问题， 组合索引性价比更高
5. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
6. 查询中统计或者分组字段

3.2 不适合创建索引的情况

1. 表记录太少
2. 经常增删改的表或者字段
3. Where 条件里用不到的字段不创建索引
4. 过滤性不好的不适合建索引

4、聚簇索引和非聚簇索引

在Mysql中B+树索引按照存储方式的不同分为聚集索引和非聚集索引。

聚集索引:,以InnoDB作为存储引擎的表,表中的数据都会有一个主键,即使你不创建主键,系统也会隐式的创建一个主键,这是因为InnoDB是把所有的数据都放到了B+树里面,而B+树的键值就是主键,在B+树的叶子节点存放了所有的数据。
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。术语‘聚簇’表示数据行和相邻的键值聚簇的存储在一起。数据行在磁盘的排列和索引排序保持一致。

非聚集索引:以主键以外的键值构建的B+树索引,我们称之为非主键索引。非聚集索引与聚集索引的区别在于非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

聚簇索引的好处：
按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的 io 操作。

聚簇索引的限制： 对于 mysql 数据库目前只有 innodb 数据引擎支持聚簇索引，而 Myisam并不支持聚簇索引。由于数据物理存储排序方式只能有一种，所以每个 Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性，所以 innodb 表的主键列尽量选用有序的顺序id，而不建议用无序的 id，比如 uuid 这种。

二、存储引擎

show engines:查看所有的数据库引擎
show variables like '%storage_engine%' 查看默认的数据库引擎

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响，适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	读性能	并发写、事务、资源
默认安装	Y	Y
默认使用	N	Y
自 带 系 统 表使用	Y	N

三、锁

#查看数据库表锁的命令
SHOW OPEN TABLES;
#给mylock表上读锁，给book表上写锁
LOCK TABLE `mylock` READ, `book` WRITE;
#查看当前表的状态
SHOW OPEN TABLES;
#释放给表添加的锁
UNLOCK TABLES;

读写锁

读锁只能查当前表 不能改 不能读其他表
其他session也能查 但是改要等锁释放
写锁 当前session可以查询更新插入 其他session查找要阻塞等待

MyISAM引擎在执行查询语句SELECT之前，会自动给涉及到的所有表加读锁，在执行增删改之前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

• 表共享读锁（Table Read Lock）。
• 表独占写锁（Table Write Lock）。

対MyISAM表进行操作，会有以下情况：

• 対MyISAM表的读操作（加读锁），不会阻塞其他线程対同一表的读操作，但是会阻塞其他线程対同一表的写操作。只有当读锁释放之后，才会执行其他线程的写操作。
• 対MyISAM表的写操作（加写锁），会阻塞其他线程対同一表的读和写操作，只有当写锁释放之后，才会执行其他线程的读写操作。

SHOW STATUS LIKE 'table%';

可以通过Table_locks_immediate和Table_locks_waited状态变量来分析系统上的表锁定。具体说明如下：
Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在较严重的表级锁争用情况。

MyISAM的读写锁调度是写优先，这也是MyISAM不适合作为主表的引擎。因为写锁后，其他线程不能进行任何操作，大量的写操作会使查询很难得到锁，从而造成永远阻塞

行锁(偏写)

行锁特点：

• 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。

InnoDB存储引擎和MyISAM存储引擎最大不同有两点：一是支持事务，二是采用行锁。

索引失效行锁变表锁

四、事务

#开启MySQL数据库的手动提交
SET autocommit=0;

1、事务的 ACID

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

2、事务并发问题

脏读
事务A读到了事务B已修改但尚未提交的数据 在这个数据基础上进行操作 如果事务B回滚 A读取的数据无效 不符合一致性要求

幻读
一个事务按相同的查询条件重新读取以前检索过的数据 却发现其他事务插入了满足其查询条件的新数据 事务A读到了事务B已提交的新增数据 不符合隔离性

不可重复读
一个事务在读取某些数据后的某个时间 再次读取以前读过的数据 却发现数据已经改变 事务A读到了事务B已提交的修改数据 不符合隔离性

脏读和幻读区别

• 脏读 事务B修改了数据
• 幻读 事务B新增了数据

更新丢失
比如用户转账的操作。a 账户总共1000，事务1和事务2查询账户都是1000，然后事务1账户扣减100，提交。事务2扣减800提交。这时候账户余额为200，事务1扣减的100 会不翼而飞，这会导致严重的问题。
select … for update 手动上锁 悲观锁
乐观锁 悲观锁
for update 和 lock in share mode 的区别：
前一个上的是排他锁（X 锁），一旦一个事务获取了这个锁，其他的事务是没法在这些数据上执行 for update ；后一个是共享锁，多个事务可以同时的对相同数据执行 lock in share mode。

SHOW STATUS LIKE 'innodb_row_lock%'
Innodb_row_lock_current_waits：当前正在等待锁定的数量。
Innodb_row_lock_time：从系统启动到现在锁定总时间长度（重要）。
Innodb_row_lock_time_avg：每次等待所花的平均时间（重要）。
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花的时间。
Innodb_row_lock_waits：系统启动后到现在总共等待的次数（重要）。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化策略。

3、Mysql的四种隔离级别

隔离级别	脏读	幻读	不可重复读
Read Uncommitted（读取未提交内容）	√	√	√
Read Committed（读取提交内容）	×	√	√
Repeatable Read（可重读）	×	×	√
Serializable（可串行化）	×	×	×

Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（DirtyRead）。

Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（NonrepeatableRead），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（PhantomRead）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影”行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion ConcurrencyControl）机制解决了该问题。

Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

#设置隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {
  READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
set session transaction isolation level read uncommitted;
#查看隔离级别是否设置成功
select @@transaction_isolation （mysql版本 8.0 以后）
select @@tx_isolation （mysql版本 8.0 之前）

• 其中作用域可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写。

五、日志

1. 慢查询日志

慢查询日志是什么？

• MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
• long_query_time的默认值为10，意思是运行10秒以上的语句。
• 由慢查询日志来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒钟的SQL，结合之前explain进行全面分析。

特别说明

**默认情况下，MySQL数据库没有开启慢查询日志，**需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看慢查询日志是否开以及如何开启

• 查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。

• 开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

# 1、查看慢查询日志是否开启
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name       | Value                                |
+------------