初步了解

支持的默认最大连接数：151

最大值是100000个

5.7以前由query_cache缓存，之后没有了

查询流程：

查询--》query_cache--》parser解析器-》pre processor预处理器-》优化器optimizer -》执行计划execution plans-》执行器executor-》存储引擎storage engine
#查询mysql服务器的数据地址
show variables like 'datadir';

InnoDB设定了一个存储引擎从磁盘读取数据到内存的最小的单位，叫做页。操作系统也有页的概念。

操作系统的页大小一般是4kb，Innodb中默认的page大小是16kb。

Buffer pool

InnoDB设计了一个内存的缓冲区。读取数据的时候，先判断是不是在这个内存区域里面，如果是，就直接读取，然后操作，不用每次从磁盘加载。如果不是，读取后就写道这个内存的缓冲区。也就是Buffer Pool。

修改数据的时候也是先写入到buffer pool，而不是直接写到磁盘。内存的数据页和磁盘数据不一致的时候，我们把它叫做脏页，InnoDB里面有专门的后台线程把buffer pool的数据写入到磁盘，每隔一段时间就一次性把多个修改写入磁盘，这个动作就叫做刷脏。buffer pool是为了提高读写的效率。

Redo log

因为刷脏不是实时的，如果buffer pool里面的脏页还没有刷入磁盘时，数据库宕机或者重启，这些数据就会丢失。

InnoDB把所有对页面的修改操作写入一个日志文件。如果有未同步到磁盘的数据，数据库在启动的时候，会从这个日志文件进行恢复操作。这个日志文件就是磁盘的redo log（叫做重做日志）。

特点：

  1. 是InnoDB存储引擎实现的，只有InnoDB有。支持崩溃恢复是他的特性
  2. redo log不是记录数据也更新之后的状态，而是记录在某个数据页上做了什么修改。属于物理日志。
  3. redo log大小是固定的，前面的内容会被覆盖，一旦写满，就会触发buffer pool到磁盘的操作。

Undo log

undo log （撤销日志或回滚日志）记录了事务发生之前的数据状态，分为insert undo log 和update undo log。

可以理解为undo log记录的是反向的操作，比如insert会记录delete，update会记录update原来的值。

索引

什么是索引？

索引就是像书的目录一样的存在。能够加快数据的查询。

索引类型

在InnoDB中，有三种索引类型

  1. Normal普通索引：也是非唯一索引
  2. Unique唯一索引：要求键值不能重复，主键索引是一种特殊的唯一索引，他还有一个限制条件，就是键值不能为空。主键索引用primary key创建。
  3. Fulltext全文索引：针对比较大的数据，只有文本类型的字段才可以创建全文索引。如char、varchar、text。

MyISAM和InnoDB支持全文索引

  • 二叉查找树(Binary Search Tree)：左边的节点都比父节点小，右边的节点都比父节点大
  • 平衡二叉树（AVL Tree-Balanced Binary Search Trees）:左右子树深度差绝对值不超过1。通过左旋，右旋。
  • 多路平衡查找树（Balanced Tree B Trees）:分裂、合并。N+1
  • B+Tree加强版多路平衡查找树：只有叶子节点才存储数据。每个叶子节点间有指针。N
    B+Tree优势：
    扫描、扫表能力更强。磁盘读写、排序能力更强。效率更加稳定。

不要用无序的值作为主键：会产生页分裂。

page=16kb

索引方式：Hash、B Tree

memory存储引擎可以使用Hash索引。

主键索引：存储索引和数据

二级索引：存储索引和主键值

查看数据存储的目录地址：

show VARIABLES LIKE 'datadir';
#Innodb的表有两个文件（.frm和.ibd）,myIsam的表有三个文件（.frm、.MYD、.MYI）
#.frm是存储表结构的文件。
#.MYD文件，d代表data，存放数据记录，.MYI文件，i代表index,是存放索引的文件，通过索引文件中的地址找到文件中的数据。
#innodb中，如果有主键索引，那么主键索引就是聚集索引，其他的索引统一叫作二级索引。二级索引存储的是二级索引的键值，二级索引的叶子节点存的是这条记录对应的主键值。（为什么不存地址，因为地址会变化。）
#如果灭有主键，则innodb会选择第一个不包含有NULL值的唯一索引作为主键索引。如果也没有这样的唯一索引，则innodb会选择内置6字节长的rowid作为隐藏的聚集索引，它会随着行写入而主键递增。

索引的使用

  • 列的离散度
    离散度公式：count(distinct(column_name)):count(*)
    离散度越大越适合建立索引
  • 联合索引
    最左匹配原则，不能跳过开始的列。
    like右匹配不走索引
  • 覆盖索引
    什么是回表？
    当查询的所有列包含在了用到的索引中就不需要去数据索引中查询数据。
  • 索引条件下推

创建索引：

  1. 在用于where判断、order排序、join、group by字段上创建索引
  2. 索引的个数不要过多
  3. 区分度低的字段，例如性别，不要建索引
  4. 频繁更新的值，不要作为主键或者索引
  5. 不建议用无序的值作为索引
  6. 复合索引把散列性高的值放在前面
  7. 创建复合索引，而不是修改单列索引
  8. 过长的字段，建立前缀索引

什么时候用不到索引？

  1. 索引列使用函数
  2. 字符串没有加引号，出现隐式转换
  3. like条件中前面带%（有特殊情况可以用到，但是需要索引下推）
  4. <> != NOT IN

事务和锁

Innodb和做集群的NDB支持事务

#查询数据库版本
select version();
#查询存储引擎
show variables like '%engine%';
#查询事务的隔离级别
show variables like 'transaction_isolation';

事物的四大特性

  • 原子性 在InnoDB里面是通过undo log来实现的，它记录了数据修改之前的值（逻辑日志），一旦发生异常，就可以用undo log来实现回滚操作
  • 隔离性（指两个事务是互不影响的）Isolation
  • 持久性（永久性的。redo log是做崩溃恢复的，双写缓冲使页有完整的数据。操作数据的时候，会先写到内存的buffer pool里面，同时记录redo log，如果在刷盘之前出现异常，在重启后就可以读取redo log的内容，写入到磁盘，保证数据的永久性。恢复数据的前提是数据页本身没有被破坏，是完整的，这个通过双写缓冲保证。）
  • 一致性（一般在代码中控制。原子性，隔离性，持久性，都是为了实现一致性。）

手动开启事务有两种方式：一种是begin；一种是start transaction.

结束事务也有两种方式：一种是回滚事务rollback；另一种是提交一个事务，事务结束。

show variables like 'autocommit';
#默认值是ON。如果它的值是true/on的话，操作数据的时候会自动提交事务。否则的话，数据库的事务需要我们手动的结束。
  1. 脏读（在一个事务中执行相同的查询，由于另一个事务执行了修改或删除操作，但是没有提交数据，导致两次查询的结果不一致的情况）
  2. 不可重复读（在一个事务中执行相同的查询，由于另一个事务执行了修改或删除操作，并且提交了数据，导致两次查询的结果不一致的情况）
  3. 幻读（在一个事务中执行相同的查询，由于另一个事务执行了插入操作，并且提交了数据，导致两次查询的结果不一致的情况）

事务的隔离级别

//Read Uncommit  脏读
//Read commit  幻读
//Repeable commit 不可重复读
//Serialable 串行

MVCC

Multi Version Concurrency Control

MVCC核心原理：建立了一个快照，在同一个事务中无论查询多少次都是相同的数据。解决幻读问题。

MVCC的原则：

一个事务能看到的数据版本：第一次查询之前已经提交的事务的修改；本事务的修改

一个事务不能看到的数据版本：在本事务第一次查询之后创建的事务；活跃（未提交）的事务的修改。

InnoDB中，一条数据的旧版本，是存放在undo log。因为修改了多次，这些undo log会形成一个链条，叫做undo log 链。

MVCC 会产生Read View
RR和RC都有MVCC
RR的Read View是事务第一次查询的时候建立的
RC的Read View是事务每次查询的时候建立的

锁的粒度

Innodb支持行锁，Myisam最小支持表锁。

锁的作用：解决数据竞争的问题。

表锁

意向共享锁、意向排他锁

#意向锁 当表中没有任何一行数据加锁，才能给表加锁

行锁

行锁锁的是索引。

  • 行锁之共享锁:只能读，不能修改。
select * from table where id=1 LOCK IN SHARE MODE;
  • 排他锁：
#自动 delete/update/insert 默认加锁
#手动：select * from table where id=1 for update;
#默认50s会报错

意向锁：一行数据加上共享锁之前，数据库会自动在这张表上面加一个意向排他锁。

  • 记录锁：唯一性索引等值查询，精准匹配
  • 间隙锁（阻塞插入）：锁定范围。（-∞，1）（1，4）（4，7）（7，10）（10，+∞）
  • 临键锁：锁定范围加记录。左开右闭。（-∞，1]（1，4]（4，7]（7，10]（10，+∞]

行锁的原理：

  • 没有索引的表：第一个事务，影响后面所有事务的锁表操作
  • 有主键索引的表：用相同的id值去加锁，冲突；使用不同的id加锁，可以加锁成功。
  • 唯一索引：第一个操作了带索引的字段，其他的事务再操作数据时就会别锁。

综上分析可知，行锁锁定的是索引，数据是存在主键索引的地址上的。所以通过辅助索引锁定一行数据的时候，它跟检索数据的步骤是一样的，会通过主键值找到主键索引，然后也锁定。

事务隔离级别的选择

RC和RR的主要区别

  1. RR的间隙锁会导致锁定范围的扩大
  2. 条件列未使用到索引，RR锁表，RC锁行
  3. RC的半一致性读可以增加update操作的并发性
#查看锁信息
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	从系统启动到现在总共等待的次数

#当前运行的所有事务和具体的语句
select * from information_schema.INNODB_TRX;

#当前出现的锁
select * from information_schema.INNODB_LOCKS;

#锁等待的对应关系
select * from information_schema.INNODB_LOCK_WAITS;

SQL优化

连接–配置优化

客户端连接到服务器，可能服务端连接数不够导致的问题。

#连接数不够的时候修改最大连接数
show variables like 'max_connections';

#释放不活动的连接，默认超时时间都是28800秒，8小时，可以调小这个值。
show global variables like 'wait_timeout';

可以引入连接池（DBCP、C3P0、Druid（最大连接默认8）、Hikari（最大默认10））

Hikari建议是：机器核数*2+1。

缓存–架构优化

缓存

数据缓，对于实时性不是特别高的业务，可以定时更新。

集群，主从复制

通过bin log实现主从复制。对于读多写少的项目可以读写分离。

分库分表

优化器–SQL语句分析与优化

生成执行计划，分析执行结果。

可以查询服务端的慢查询日志。

#慢查询日志
show variables like 'slow_query%';
#多长时间的查询算是慢SQL，默认10s
show variables like '%long_query%';

#1开启，0关闭，重启后失效
set @@global.slow_query_log=1;
#默认10s,另开一个窗口才能查到最新值
set @@global.long_query_time=3;

执行计划分析

  1. id：查询序列编号，每张表都是单独访问的，一个select一个序号。先大后小，从上往下。
  2. select_type:
  1. SIMPLE 简单查询，不包含子查询，不包含关联查询union
  2. PRIMARY：子查询SQL语句中的主查询
  3. SUBQUERY：子查询中所有的内层查询都是
  4. DERIVED:衍生查询，表示在得到最终结果之前会用到临时表
  1. type连接类型：system>const>eq_ref>ref>range>index>all 除了all都能用到索引
  1. system：只有一行满足条件，是一种特殊的const
  2. const：主键索引或者唯一索引
  3. eq_ref：被驱动表通过唯一索引进行访问，此时被驱动表的访问方式就是eq_ref
  4. ref：查询用到了非唯一索引，或者关联操作只使用了索引的最左前缀。
  5. range：索引范围扫描。在where后面是between and 、or 、>=、<=、in
  6. index：查询全部索引中的字段。SELECT tid FROM teacher；tid是索引。
  7. all：全表扫描
  8. NULL：不用访问表或索引就能得到结果。SELECT　１　FRＯＭ　DUAL
  1. possible_key、key：可能用到的索引和实际用到的索引。可能用到的索引不代表一定能用到索引。
  2. key_len:索引的长度（字节数）。跟索引字段的类型，长度有关。
  3. rows：扫描多少行才能返回请求的数据，是一个预估值。一般来说行数越少越好。
  4. filtered：表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，是一个百分比。如果比例低，说明存储引擎层返回的数据需要经过大量过滤，这个是会消耗性能的，需要关注。
  5. ref：使用哪个列或者常数和索引一起从表中筛选数据
  6. EXtra：执行计划给出的额外的信息说明
  1. using index：用到了覆盖索引，不需要回表
  2. using where：使用了where过滤，表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层过滤。
  3. using index condition：索引条件下推
  4. using filesort：不能使用索引来排序，用到了额外的排序。需要优化
  5. using temporary：用到了临时表

存储引擎

为不同的业务表选择不同的存储引擎，查询插入操作多的业务表，用MyISAM。临时数据用Memeroy。常规的并发大更新多的表用InnoDB。