一、MySQL介绍

二、MySQL 安装

1.安装前注意事项

（1）确认SELinux 和 系统防火墙 iptables 需要关闭

SELinux

cat /etc/sysconfig/selinux

# This file controls the state of SELinux on the system.

# SELINUX= can take one of these three values:

# enforcing - SELinux security policy is enforced.

# permissive - SELinux prints warnings instead of enforcing.

# disabled - SELinux is fully disabled.

SELINUX=disabled

# SELINUXTYPE= type of policy in use. Possible values are:

# targeted - Only targeted network daemons are protected.

# strict - Full SELinux protection. SELINUXTYPE=targeted

是关闭状态

防火墙：

chkconfig --list|grep iptables iptables        

0:off 1:off 2:off 3:off 4:off 5:off 6:off

chkconfig iptables off

（2）I/O调度系统

I/O调度模式默认是cfq的，建议用deadline模式（root用户才有权限看）

cat /etc/grub.conf

在文件后面加： elevator=deadline

（3）swap分区设置

swap分区简介：

Swap分区在系统的物理内存(这里应该是运行内存)不够用的时候，把物理内存中的一部分空间释放出来，以供当前运行的程序使用。那些被释放的空间可能来自一些很长时间没有什么操作的程序，这些被释放的空间被临时保存到Swap分区中，等到那些程序要运行时，再从Swap分区中恢复保存的数据到内存中。

swappiness 至的范围是0-100，0代表最大限度使用物理内存，然后才使用swap，可能会导致系统内存溢出，OMM错误；100代表积极使用swap分区，并及时把内存数据搬到swap分区中（不建议）

一般不设置swap，或者4G即可

配置swappiness：在  /etc/sysctl.conf 文件中加入  vm.swappiness = 60

（4）文件系统的选择

建议使用xfs文件系统，相比ext4更方便管理，支持动态扩容，删除方便

（5）操作系统的限制

查看当前操作系统限制：ulimit -a

core file size          (blocks, -c) 0

data seg size           (kbytes, -d) unlimited

scheduling priority             (-e) 0

file size               (blocks, -f) unlimited

pending signals                 (-i) 31381

max locked memory       (kbytes, -l) 64

max memory size         (kbytes, -m) unlimited

open files                      (-n) 65535

pipe size            (512 bytes, -p) 8

POSIX message queues     (bytes, -q) 819200

real-time priority              (-r) 0

stack size              (kbytes, -s) 10240

cpu time               (seconds, -t) unlimited

max user processes              (-u) 4096

virtual memory          (kbytes, -v) unlimited

file locks                      (-x) unlimited

p24 - p40

三、MySQL体系结构与存储引擎

3.1 MySQL体系结构包括两层：

1.MySQL server层

（1）连接层

（2）SQL层

2.存储引擎层—（区别于其他数据库）

3.2 query cache

在生产中建议关闭，因为他只缓存静态数据信息；在数据仓库可以考虑开启。

两个重要参数：

show variables like '%query_cache_size%';

| Variable_name    | Value   |

| query_cache_size | 1048576 |

show variables like '%query_cache_type%';

| Variable_name    | Value | 

| query_cache_type | OFF   |

MySQL压力测试软件 sysbench

下载地址：http://dev.mysql.com/downloads/benchmarks.html

3.3 存储引擎

目前两个主流存储引擎是 InnoDB 和 MyISAM 两种，MySQL 8.0将使用 InnoDB 存储数据字典，目前默认也是 InnoDB

3.4 InnoDB 体系结构

3.4.1

MySQL是单进程多线程的数据库

包括下面三个部分

内存结构 + 线程 + 磁盘文件

3.4.2 InnoDB 存储结构

主要分为：表空间、段、区、页

（一）表空间：

（1）系统表空间

命名：ibdata1

安装数据库初始化时就会创建的，存储所有数据信息和回滚段（undo）信息。

数据库自动扩展，默认64MB

 show variables like '%auto%';

ibdata1 大小默认时1MB，建议改为1GB

show variables like '%innodb_data%'; 

| Variable_name         | Value                    |

| innodb_data_file_path | ibdata1:1024M:autoextend |

| innodb_data_home_dir  |                          |

（2）独立表空间

参数设置：innodb_file_oer_table=1

目前默认使用独立表空间，即每个表都有自己的表空间文件，不存放在系统表空间中。

独立表空间文件存储对应表的B+树数据、索引和插入缓冲等信息，其余信息还存储在默认表空间中。

回收表空间：alter table table_name engine=innodb; 或者 执行：pt-online_schema_change

但是每个表都有.frm和.ibd文件两个文件描述，如果表单增长过快就会出现性能问题。

（3）共享表空间

共享表空间不能在线回收表空间

回收方法：将全部InnoDB表的数据备份、删除原表，然后把数据导回到与原表结构一致的新表。

统计分析类、日志类系统不适合共享表空间

5.7版本后增加：

（4）临时表空间（temporary tablespace）

show variables like '%temp%';

| Variable_name              | Value                 |

| avoid_temporal_upgrade     | OFF                   |

| innodb_temp_data_file_path | ibtmp1:12M:autoextend |

| show_old_temporals         | OFF                   |

独立表空间文件名：ibtmp1，默认大小12M

（5）通用表空间（general tablespace）

多个表放在同一个表空间中，根据活跃度来划分表，存放在不同磁盘上，可以减少metadata的存储开销，但目前生产中使用较少

（二）段

表空间是由段组成，也可以把一个表理解为一个段。通常有数据段，回滚段，索引段等

一个段由：N个区和32个零散页组成，段空间扩展是以区为单位进行扩展的

通常创建一个索引的同时会创建两个段，分别为非叶子节点和叶子节点段

一个表有四个段，是索引个数的2倍

（三）区

区是连续的页组成，是物理上连续分配的一段空间，每个区固定大小是1M

（四）页

InnoDB 的最小物理存储分配单位是page，有数据页，回滚页等。一般情况下，一个区由64个连续页组成，页的默认大小是16KB

5.6版本开始可以调低page的大小，如8KB，4KB；5.7版本开始可以调高page大小32KB，64KB

一般情况下，page页会预留1/16空间用于更新数据，真正使用的只有15/16

页的结构如图，一个页最少可以存两行数据：虚拟最小行（infimum）和虚拟最大行（supremum）

（五）行

页里面记录行记录的信息

InnoDB存储引擎有两种文件格式：

（1）Antelope ：包括compact 和 redundant 两种行记录格式

（2）Barracuda ：包括compressed 和 dynamic 两种行记录格式

5.7版本默认使用dynamic行记录格式和Barracuda文件格式

show variables like '%row_format%';

| Variable_name             | Value   | 

| innodb_default_row_format | dynamic |

show variables like '%innodb_file_format%'    

| Variable_name            | Value     |

| innodb_file_format       | Barracuda |

| innodb_file_format_check | ON        |

| innodb_file_format_max   | Barracuda |

3.4.3 内存结构

与Oracle类似，也分为SGA（系统全局区） 和 PGA（程序缓存区）

数据库内存分配参数查看：

show variables like '%buffer%'; 

（一）SGA组成

1.innodb_buffer_pool

用来缓存InnoDB表的数据、索引、插入缓冲、数据字典等信息。

2.innodb_log_buffer

事务在内存中的缓冲，即redo log buffer 的大小

3.Query Cache

高速查询缓存，在生产环境中建议关闭

4.key_buffer_size

只用于MyISAM存储引擎表，缓存MyISAM存储

引擎表的索引文件（区别于innodb_buffer_pool数据和索引都缓存）

5.innodb_additional_men_pool_size

用来保存数据字典信息和其他内部数据结构的内存池的大小，在5.7.4版本中被移除了

（二）PGA组成

1.sort_buffer_size

主要用于SQL语句在内存中的临时排序

2.join_buffer_size

表连接使用，用于BKA。5.6版本之后开始支持

3.read_buffer_size

表顺序扫面的缓存，只能应用与MyISAM表存储引擎

4.read_rnd_buffer_size

MySQL随机读缓冲区大小，用于做mrr，mrr是5.6之后才有的特性

（三）特殊的

1.tmp_table_size

SQL语句在排序或分组时没有用到索引，就会用临时表空间

2.max_heap_table_size

管理heap、memory存储引擎表

show variables like '%heap%';

| Variable_name       | Value    |

| max_heap_table_size | 16777216 |

show variables like '%tmp_table_size%';

| Variable_name  | Value    |

| tmp_table_size | 16777216 |

一般生产环境把这两个参数设置为一样的，如果二者不一样会按照其中小的值起作用

值太小会出现【converted heap to myisam】报错

3.4.4 buffer 状态及其链表结构

page 是 InnoDB 磁盘 I/O 的最小单位，数据存放在page中，对应内存中的一个个buffer，每个buffer分为三个状态：

（1）free buffer ：未被使用

（2）clean buffer ：buffer数据与磁盘数据一致

（3）dirty buffer ：新写入数据还未刷新到磁盘

InnoDB 是双向链表结构，由三种不同的buffer 状态衍生三条链表：free list 、lru list 、flush list

3.4.5 各大刷新线程及其作用

1.master thread

后台线程中的主线程，优先级最高。

内部有四个循环：主循环loop、后台循环background loop、刷新循环flush loop、暂停循环suspend loop

主循环包括每1s和每10s的操作：

每1秒操作：

（1）日志缓冲刷新到磁盘，即使这个事务还没有提交

（2）刷新脏页到磁盘

（3）执行合并插入缓冲操作

（4）产生checkpoint

（5）清除无用的table cache

（6）如果当前没有用户活动，就切换到background loop

每10秒操作：

（1）日志缓冲刷新到磁盘，即使事务没有提交

（2）执行合并插入缓冲的操作

（3）刷新脏页到磁盘

（4）删除无用的undo页

（5）产生checkpoint

以下是四大I/O线程

2.read thread

负责把日志缓冲中的内容刷新到redo log 文件中

3.write thread

4.redo log thread

读写请求线程

5.change buffer thread

负责把插入缓冲（change buffer）中的内容刷新到磁盘

6.page cleaner thread

负责脏页刷新线程，5.7版本后可以增加多个

7.purge thread

负责删除无用的undo页

8.checkpoint线程

在redo log 发生切换时，执行checkpoint

3.4.6 内存刷新机制

与Oracle类似，日志先行策略，即一条DML语句进入数据库之后都会先写日志，再写数据文件

1.redo log

默认至少有两个，磁盘上用ib_logfile(0~N)命名

顺序写，循环写：第一个文件写满，写第二个，直到写道最后一个，又从第一个文件开始写，写满日志文件会产生切换操作，并执行checkpoint，触发脏页的刷新。

2.binlog

DML操作既会写redo log文件，也会写binlog文件，是数据库的二进制文件，主要用于备份恢复和主从赋值

只记录改变信息，不记录查询

二者区别：

（1）记录内容不同：

binlog是逻辑日志，记录所有数据的改变信息；redo log是物理日志，记录的是所有InnoDB表数据的变化

（2）记录内容的时间不同

binlog记录commit完毕的DML和DDL SQL语句；redo log 记录事务发起之后的DML 和 DDL SQL

（3）文件使用方式不同

binlog不是循环使用，在写满或实例重启后会生产新的binlog文件；redo log是循环使用，最后一个文件写满还会再写第一个文件

（4）作用不同

binlog 可以做恢复数据使用，主从复制搭建；redo log 作为异常宕机或者介质故障后的数据库恢复使用

只要binlog写入完成，那么主从复制环境就会正常完成事务

脏页刷新的条件：

（1）重做日志ib_filelog 文件写满后，在切换过程中会执行checkpoint，会触发脏页的刷新

（2）通过innodb_max_dirty_pages_pct 参数控制，该参数是指在buffer pool中dirty page所占百分比，达到设置的值，就会触发脏页的刷新。

show variables like '%innodb_max_dirty_pages_pct'; 

| Variable_name              | Value     |

| innodb_max_dirty_pages_pct | 75.000000 |

默认是75%，生产环境建议25%~50%

（3）由innodb_adaptive_flushing参数控制，该参数影响每秒刷新脏页的数目，替换了innodb_max_dirty_pages_pct 参数设置的值，也会刷新一定数量的脏页，默认开启

show variables like 'innodb_adaptive_flushing';

| Variable_name            | Value |

| innodb_adaptive_flushing | ON    |

3.4.7 InnoDB 的三大特性

1.插入缓冲（change buffer）

先判断插入的普通索引是都在缓冲池中，如果在就直接插入，不在就先放到change buffer中，然后进行change buffer和普通索引的合并操作，可以将多个插入合并到一个操作中，提高普通索引是插入性能。

2.两次写（double write）

生成一个页的副本，如果实例宕机，页坏了，先用副本把页还原出来，再通过redo log进行恢复重做。

3.自适应哈希索引（adaptive hash index）

四、数据库文件

从数据库层面划分为：参数文件（my.cnf）错误日志（error log）、慢查询日志（slow log）、全量日志（general log）、二进制日志文件（binlog）、审计日志（audit log）、中继日志（relay log）、套接字文件（socket）、进程文件（pid）、表结构文件。

从存储引擎层面有：redo log 和 undo log 文件

4.1 参数文件

几个重要参数：

1.innodb_buffer_pool

在主内存中，用来缓存被访问过的表和索引文件，使得常用数据可以直接在内存中被处理，从而提升处理速度。

建议在服务器只跑数据库一个应用的前提下，该参数可设置为物理内存的50% ~ 80%

2.innodb_buffer_pool_instance

默认值是1，MySQL5.6.6版本后可以调整为多个，表示innodb缓冲区可以划分多个区域，可以提高并发性，避免高并发情况下，出现内存争用问题。设置完成后，每个缓冲区各自管理自己的数据，互不干涉。

注意：只有当innodb_buffer_pool 大于1G 时，生成的innodb_buffer_pool 多实例才生效。

P56

4.2 参数类型

分为动态参数和静态参数

1.动态参数

可以在线修改的参数。

通过set global 或者 set session 两个命令设置，global 代表全局，session是只针对当前会话。

2.静态参数

无法在线修改的参数，只能通过改配置文件然后重启数据库才能使修改生效

4.3 错误日志文件（error log）

4.4 二进制日志文件（binary log）

show variables like '%binlog_format%';

| Variable_name | Value |

| binlog_format | ROW   |

三种模式：

1.row：基于行变更情况记录，会记录行变更前的样子和行变更后的内容，简称RBR，生产中建议使用row。

2.statement：记录的是一条完整的sql语句，

3.mixed：是5.1版本中row和statement的过渡，不建议使用

4.5 慢查询日志（slow log）

慢查询日志会把超过参数long_query_time时间的所有SQL语句记录进来。

推荐工具：percona-toolkit，

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST

通过以下命令生成慢SQL报告：

/usr/local/percona-toolkit-3.0.3/bin/pt-query-digest --since=24h /data/mysql/slow.log > query.log

4.6 全量日志（general log）

记录MySQL数据库所有操作：包括select和show，一般情况下不开启，因为log会很大，个别情况可能临时开启，用于故障检测。

参数介绍：

show variables like '%log_output%';

| Variable_name | Value |

log_output    | FILE  |

log_output：全局动态变量，可取FILE TABLE NONE 三个值，其中FILE可以方便按条件检索，若指定NONE，则即使开启general_log 也不会记录log，若指定TABLE，则会在MySQL数据库下创建一个general_log表。

该参数不仅影响general的存储方式，也会影响slow log的存储方式，建议使用FILE。

4.7 审计日志（audit log）

实时记录网络上数据库活动，对数据库进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行告警，对攻击进行阻断。

通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户事后生成合规报告、事故追溯更远，同时加强内外数据库网络行为记录，提高数据资产安全。

推荐审计插件：libaudit.plugin.so

下载地址：https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files

查看audit是否开启

show variables like '%audit%';

Empty set (0.07 sec)

开启audit

set global audit_json_file=1;

在MySQL目录下会多出一个，mysql-audit.json 审计日志。

4.8 中继日志（relay log）

主从复制中，从服务器上上的文件。从服务器I/O线程将主服务器的二进制日志读取过来并记录到从服务器本地文件（relay log）中，然后从服务器上的sql线程会读取relay-log日志的内容并应用到从服务器上。

4.9 Pid 文件

MySQL 是一个单线程，多进程模型的数据库，实例启动完成后会将自己唯一的进程号记录到自己的Pid文件中。

pid文件存放在数据目录下。命名规则是将主机名作为前缀。

4.10 Socket 文件

mysql.sock 文件时服务器与本地客户端进行通信的UNIX套接字文件。

4.11 表结构文件

在MySQL 8.0之前，以.frm 结尾的文件为表结构文件。

从MySQL8.0开始，frm表的定义文件被消除掉，把文件中的数据写入了系统表空间中，通过innodb 存储引擎，实现表的DDL语句操作的原子性（之前版本无法实现，如truncate无法回滚）

4.12 innodb 存储引擎文件

两种日志：redo + undo 

支持事务，支持MVCC多版本并发控制（undo+回滚段）

innodb_undo_tablespaces 参数代表undo tablespace 的个数，默认0，一般最少2个，因为undo log的truncate是由purge协调线程发起，为保证在线truncate ，需要有一个undo log tablespace 能提供给用户使用。

show variables like '%undo_tablespaces%';

+-------------------------+-------+

| Variable_name | Value |

+-------------------------+-------+

| innodb_undo_tablespaces | 0 |

+--------------------------+------------+

5.7版本后增加 innodb_max_undo_log_size 参数，默认是1GB，控制最大undo tablespace文件大小，超过该阈值，会触发truncate undo logs，truncate后的undo logs 大小默认恢复为10MB。

show variables like '%innodb_max_undo_log_size%';

+--------------------------+------------+

| Variable_name | Value |

+--------------------------+------------+

| innodb_max_undo_log_size | 1073741824 |

+--------------------------+------------+

5.7.5版本后，支持在线删除无用undo logs 默认关闭的

show variables like '%innodb_undo_log_truncate%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_undo_log_truncate | OFF |

+--------------------------+-------+

5.7版本后还新增的参数：innodb_purge_rseg_truncate_frequency参数，用来控制回收undo log的频率，默认128：表示purge undo 轮询128次后，进行一次undo 的truncate

show variables like '%innodb_purge_rseg_truncate_frequency%';

+--------------------------------------+-------+

| Variable_name | Value |

+--------------------------------------+-------+

| innodb_purge_rseg_truncate_frequency | 128 |

+--------------------------------------+-------+

第五章 表

5.1 整形

常用int，最大可达42亿。

int(n)：n代表的显示宽度，不是多少位数。无论n是多少，都是占用4个字节

5.2 浮点型

包括：float（4字节），double（8字节），decimal（decimal（M,D），如果M>D则为M+2，否则D+2字节）

常用的是decimal

四舍五入原则

5.3 时间类型

类型                   大小（字节）      格式

DATE                  3                         YYYY-MM-DD

TIME                   3                          HH:MM:SS

YEAR                  1                          YYYY

DATETIME           8/5                      YYYY-MM-DD HH:MM:SS

TIMESTAMP         4                         YYYYMMDDHHMMSS

说明：DATETIME 在5.6版本前占用8个字节，之后占用5个字节，较常用

可以通过两个函数转换而来：unix_timestamp，from_unixtime

select unix_timestamp('2020-01-13 15:46:00');

+---------------------------------------+

| unix_timestamp('2020-01-13 15:46:00') |

+---------------------------------------+

| 1578901560 |

+---------------------------------------+

select from_unixtime(1578901560);

+---------------------------+

| from_unixtime(1578901560) |

+---------------------------+

| 2020-01-13 15:46:00 |

+---------------------------+

5.4 字符串类型

char  varchar  

blob  text：存大量文字或图片的大数据类型，建议不要与业务表放在一起，主要业务切忌出现

说明：存储IP建议使用int，可以使用函数：inet_aton 和 inet_ntoa 进行转换

select inet_aton('192.168.56.102');

+-----------------------------+

| inet_aton('192.168.56.102') |

+-----------------------------+

| 3232249958 |

+-----------------------------+

select inet_ntoa(3232249958);

+-----------------------+

| inet_ntoa(3232249958) |

+-----------------------+

| 192.168.56.102 |

+-----------------------+

5.5 字符集

常用字符集：GBK（占用2字节）、Latin1(5.0,5.1中的默认字符集，目前不使用，占用1字节)、UTF8（占用3字节）、UTF8mb4（是UTF8的超集，占4字节）

保证以下三点统一就不会出现乱码。

（1）连接终端的字符集：UTF8

（2）操作系统的字符集：UTF8

查看：

cat /etc/sysconfig/i18n

LANG="en_US.UTF-8"

SYSFONT="latarcyrheb-sun16"

（3）数据库的字符集：UTF8（针对5.7版本建议使用：UTF8mb4）

查看

(root@localhost) [(none)]> \s;

或

show variables like '%char%';

+--------------------------+----------------------------------+

| Variable_name | Value |

+--------------------------+----------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

+--------------------------+----------------------------------+

5.6 表碎片产生原因

删除数据导致

5.7 碎片计算方式和整理过程

1.计算碎片

show table status like '%testclob%'\G;

*************************** 1. row ***************************

Name: testclob

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 61791

Avg_row_length: 42

Data_length: 2637824

Max_data_length: 0

Index_length: 0

Data_free: 4194304

Auto_increment: 65544

Create_time: 2019-12-26 15:48:01

Update_time: 2020-01-13 16:13:48

Check_time: NULL

Collation: utf8_general_ci

Checksum: NULL

Create_options:

Comment:

碎片大小 = 数据总大小 - 实际表空间文件大小

数据总大小 = data_length + index_length = 2637824 + 0 = 2637824

实际表空间文件大小 = rows * avg_row_length = 61791 * 42 = 2595222

碎片大小 = （2637824 -  2595222）/1024/1024 = 0.04M

2.清除碎片的两种方法：

（1）alter table table_name engine = innodb;

优点：整理全表数据，整理好后数据连续性好，全表扫描快，表空间文件变小，节约磁盘空间，清除碎片

缺点：需要先给整表加一个写锁，耗时较长，不建议在业务高峰使用

（2）备份原表数据，然后删掉，重新导入到新表中（与原表结构一样）--备份恢复会用到

推荐使用percona-toolkit 工具集，使用pt-query-digest 命令捕获慢SQL，查看慢查询日志。

pt-online-schema-change：可以整理表结构、收集碎片、给大表添加字段和索引，避免出现锁表扫之读写操作，5.7版本不需要使用这个命令，可以直接在线：online DDL

5.8 表统计信息

1.统计每个库大小：

select table_schema,sum(data_length)/1024/1024/1024 as data_length,sum(index_length)/1024/1024/1024 as index_length,sum(data_length+index_length)/1024/1024/1024 as sum_data_index from information_schema.tables where table_schema !='information_schema' and table_schema != 'mysql' group by table_schema;

+--------------------+----------------+----------------+----------------+

| table_schema | data_length | index_length | sum_data_index |

+--------------------+----------------+----------------+----------------+

| performance_schema | 0.000000000000 | 0.000000000000 | 0.000000000000 |

| sys | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test | 0.001876831055 | 0.000000000000 | 0.001876831055 |

| test1 | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test2 | 0.000015258789 | 0.000000000000 | 0.000015258789 |

| test_ljing | 0.002456665039 | 0.000000000000 | 0.002456665039 |

+--------------------+----------------+----------------+----------------+

5.9 统计信息的收集方法

1.遍历information_schema.tables 表

select * from information_schema.tables where table_name = 'test_ljing'\G;

2.重启MySQL实例

3.show table status like '%table_name%';

5.10 MySQL库表常用命令总结

SHOW TABLE STATUS;     获取表基础信息

SHOW INDEX FROM TABLE_NAME;  查看当前表下索引情况

SHOW FULL PROCESSLIST;       查看数据库当前连接情况

第六章 索引

主要索引：B+tree 索引  和   哈希索引

B+tree 是由：二叉树 → 平衡二叉树 → B-tree 演化来的

6.1 二叉树结构

每个节点至多有两个子节点，有左右序之分，次序不能颠倒。左子树的值要比右子树小，并且小于根键值

6.2 平衡二叉树结构

左右两个子树的高度差绝对值不超过1，保证插入后的整颗二叉树是平衡的，通过左旋或者右旋使不平衡的树变平衡

6.3 B-tree 结构

也称Btree，Btree和B+tree并不一样。

最多子节点数变成4个，可以理解为四阶的B树结构，树中每个节点最多含有4个子节点，除根节点和叶子节点，其他节点至少有2个子节点

特别说明：所有叶子节点都出现在同一层，叶子节点不包括任何关键字信息。

6.4 B+tree

是Btree的变体，也是一种多路搜索树，

与Btree的区别：所有关键字信息都在叶子节点层，并且包含这些关键字记录的指针。叶子节点可以按照关键字的大小顺序链接，他所有数据都保存在叶子节点中。

特别说明：B+tree索引是双向链表结构，而且用B+tree结构做检索会比B-tree快，访问关键字的顺序是连续的，不用再访问上一个节点，而且叶子节点包含所有数据信息

6.4.1 聚集索引和普通索引

聚集索引是一种索引组织形式，其逻辑顺序决定了表数据行的物理存储顺序。叶子节点存档表中所有行数据记录的信息。

我们在创建一张表的时候，要显示地创建一个主键（聚集索引），如果不主动创建，那么Innodb会选择第一个不包含null值的唯一索引作为主键，如果没有唯一索引，就会默认生成一个6个字节的roeid作为主键。

普通索引：在叶子节点并不包含所有行的数据记录，只是会在叶子节点存有本身的键值和主键的值。在检索数据时，通过普通索引叶子节点上的主键来获取想要查找的行的数据记录。

语法：alter table table_name add index index_name(column);

或者：create index index_name on table_name(column)。

查询执行计划：

explain select * from testclob WHERE NAME = 'll'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: testclob

partitions: NULL

type: ALL   (代表全表扫描)

possible_keys: NULL

key: NULL （代表没有使用索引）

key_len: NULL

ref: NULL

rows: 45024（row越大，需要扫描的行数就越多）

filtered: 10.00（返回行数占读取行数的百分比）

Extra: Using where

SQL优化问题的正确思路：

（1）看表的数据类型设计是否合理，遵循数据类型越简单越小原理

（2）表中碎片是否整理

（3）表的统计信息是否收集，统计信息准确，执行计划才能帮助我们优化SQL

（4）查看执行计划，检查索引使用情况，没有用到索引，考虑创建

（5）创建前：看索引的选择性；指：不重复的索引值和数据表的记录总数的比值，选择性越高，查询效率越高

如：name列的选择性：select distinct(name)/count(*) from testclob;

+--------------------------------+

| count(distinct(name))/count(*) |

+--------------------------------+

| 0.0001 |

+--------------------------------+

选择性很低，试试创建索引：create index idx_name on testclob(name);

（6）创建索引后再查看下执行计划，对比两次结果，看是否有所提高

explain select * from testclob WHERE NAME = testclo 'll'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: testclob

partitions: NULL

type: ref（使用了索引）

possible_keys: ind_name（使用索引名称）

key: ind_name

key_len: 63

ref: const

rows: 4096（需要扫描的行，较之前减少了，已经不是全表扫描了）

filtered: 100.00（返回行数占读取行数的百分比）

Extra: NULL

合理创建索引参考：

（1）经常被查询的列（where后面的列）

（2）经常用于表连接的列

（3）经常排序分组的列（order by 或group by 后面的列）

6.4.2 ICP、MRR、BKA

（1）ICP（Index Condition Pushdown）是MySQL使用索引从表中检索行数据的一种优化方式。

通过optimizer_switch参数中的index_condition_pushdown选项控制，默认开启

show variables like '%optimizer_switch%'\G;

*************************** 1. row ***************************

Variable_name: optimizer_switch

Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on

当使用ICP优化时，执行计划的extra列会显示using index condition

修改：set optimizer_switch="index_condition_pushdown=on|off";

（2）MRR（Multi-Range Read Optimization）

也是通过optimizer_switch参数中两个重要参数控制的

默认的：mrr=on,mrr_cost_based=on

修改：set global optimizer_switch='mrr=on|off,mrr_cost_based=on|off';

原理：对于索引列存在重复值的情况：MRR把普通索引的叶子节点上找到的主键值的集合存储到read_rnd_buffer中，然后在该buffer中对主键值进行排序，然后再利用排好序的主键值的集合去访问表中的数据，这样原来的随机I/O就会变成顺序I/O，降低查询中的I/O开销

（3）BAK（Batched Key Access）

提高表join性能的算法，作用是在读取被join表的记录的时候使用顺序I/O

通过optimizer_switch 参数中的batched_key_access=off 选项控制的，默认关闭

修改：必须保证MRR开启的情况下才能开启BKA

set global optimizer_switch = 'mrr=on,mrr_cost_based=off';

set global optimizer_switch = 'batched_key_access=on';

原理：对于多表join情况，当MySQL使用索引访问第二个join表时，使用join buffer 来手机第一个操作对象生成的相关列值，BKA构建好key后，批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的，这样MRR使得查询更加高效。

6.4.3 主键索引和唯一索引

主键索引：聚集索引，只有一个主键，由表的一个或多个字段组成。

满足条件：主键值唯一；不包含null；保证该值是自增属性（提高存取效率）

创建：alter table table_name add primary key(column);

唯一索引：不重复；允许null；可以多个

创建：alter table table_name add unique(column);

6.4.4 覆盖索引

id是主键索引，name是普通索引，普通索引包含主键的值，

查询：select id from testclob where name = 'll';

相当于（name,id）索引，即覆盖索引

注意：使用覆盖索引不能用select * ，要列出所需的列

6.4.5 前缀索引

对于BLOB、TEXT或者很长的VARCHAR类型的列，可以为他们前几个字符创建索引。叫前缀索引。

注意：前缀索引不能在order by 或 group by中使用，也不能做覆盖索引

创建：alter table table_name add key(column_name(prefix_length));

6.4.6 联合索引

也叫复合索引，即在表中的两个或两个以上的列上建立索引。

注意：创建时把选择性高的列放在前面，使用中必须满足最左前缀原则

创建：create index ind_c1_c2 on t(c1,c2);

6.5 哈希索引

使用哈希算法，把键值转换成新的哈希值。

注意：哈希值只能进行等值查询，不能进行排序，模糊查询，范围查询等

检索时：不需要从根节点到叶子节点逐级查找，只需一次哈希算法即可定位相应位置，速度很快

6.6 索引总结

创建索引的四个不要：

1.选择性低的字段不要创建

2.很少查询的列不要创建

3.大数据类型字段不要创建

4.尽量避免使用null

使用不到索引的情况：

1.通过索引扫描行记录超过全表30%，会变成全表扫描

2.联合索引中第一个查询条件不是最左索引列/最左前缀列

3.联合索引第一个索引列使用范围查询，只使用部分索引（范围查询包括：<、=、<=、between and）

4.模糊查询最左以通配符%开始

5.两个单列索引，一个用于检索，一个用于排序，之中情况下只能使用一个索引，因为查询中最多只能使用一个索引，可以考虑建立联合索引

6.查询字段上面有索引，但是使用了函数运算

第七章 事务

7.1事务的特性

原子性（要么都做，要么都不做）

一致性（转账前后总金额一致）

隔离性（防止多个事务交叉执行，造成数据不一致）

持久性（修改是永久的）

7.2 事务语句

show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

默认开启自动提交，关闭：set autocommit=0；不建议关闭自动提交模式

隐式提交：DDL语句；再次输入begin 或 start transaction

隐式回滚：退出会话、连接超时、关机

7.3 truncate 和 delete

truncate：清空后自增序列从1开始

delete：清空后自增序列继续之前的值开始

7.4 事务隔离级别

默认隔离级别是：REPEATABLE-READ

show variables like '%tx_isolation%';

+---------------+-----------------+

| Variable_name | Value |

+---------------+-----------------+

| tx_isolation | REPEATABLE-READ |

+---------------+-----------------+

（1）读未提交（read uncommitted）RU--脏读

（2）读已提交（read committed）RC -----也叫不可重复读，允许幻读，是Oracle的默认隔离级别

（3）可重复读（repeatable read）RR-------MySQL默认隔离级别，避免脏读，不可重复读，幻读

（4）串行（serializable）---------------------读：加表共享锁；写：加表排他锁；会造成innodb并发能力下降，大量超时和锁竞争会发生，不建议生产环境使用

7.5 脏读、不可重复读、幻读、可重复读

7.5.1 脏读

读取其他事务未提交的数据

7.5.2 不可重复读与幻读

不可重复读：读取其他事务针对旧数据的修改记录（常见update delete操作）

幻读：读取其他事务新增的数据（常见insert操作）

7.5.3 可重复读

可以避免脏读，幻读，不可重复读

用于对事务要求较高的数据库系统，如：电子交易类网站

第八章 锁

不同存储引擎支持不同锁机制，innodb支持行锁，又是也会升级为表锁，myisam 只支持表锁。

表锁：开销小、加锁快；不会出现思索；锁粒度大，发生锁冲突的概率高，并发度相对低

行锁：开销大、加锁慢；会出现死锁；锁粒度小，发生锁冲突的概率低，并发度相对高

8.1 InnoDB 的锁类型

读锁（共享锁）、写锁（排他锁）、意向锁、MDL锁

8.1.1 读锁

简称S锁，一个事务获取一个行的读锁，其他事务也可以获得该行对应的读锁，但不能获得写锁。

即：一个事务在读取数据行时，iqta事务也可以读，但是不能对该数据行进行增删改操作

包括两种：

1）自动提交的select查询语句，不加任何锁

2）select... lock in share mode 在被读取的行记录或行记录的范围加上一个读锁，让其他事务可以读，但是如果想申请写锁，就会被阻塞

8.1.2 写锁

简称X锁，一个事务获取了一个数据行的写锁，其他事务就不能再获取该行的其他锁，写锁优先级最高。

DML操作都会对行记录加写锁。

select for update ，也会对读取的行记录加写锁

8.1.3 MDL 锁

MySQL 5.5版本开始引入，全称：meta data lock ，用于保证表中元数据的信息。

会话A中表开启查询事务后，会自动获得一个MDL锁，会话B就不剋有执行任何DDL操作。

8.1.4 意向锁

是表级锁。分为两种：

1）意向共享锁（IS）：在给一个数据行加共享锁前必须先取得该表的IS锁

2）意向排他锁（IX）：在给一个数据行加排他锁前必须先取得该表的IX锁

作用类似于MDL锁：防止在事务进行过程中，执行DDL语句导致数据不一致

8.2 InnoDB 行锁种类

在默认隔离级别为RR（repeatable read），且参数innodb_locks_unsafe_for_binlog=0 模式下，行锁有三种：

单个行记录锁（record lock）

间隙锁（GAP Lock）

记录锁和间隙锁的组合叫next-key lock

说明：普通索引默认就是：next-key lock 模式

8.2.1 单个行记录的锁

更新同一行数据会出现锁等待的现象。

实验发现：InnoDB的行锁是加在索引项上面的，

当更新某个没有索引的字段时（where score=60），会把所有行记录都上锁，所以在其他会话更新其他行（where score=20）时，也会报锁超时的错误。

前提是：set autocommit = off;

查询某个表上所有索引：

show index from testclob\G;

*************************** 1. row ***************************

Table: testclob

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 45024

Sub_part: NULL

Packed: NULL

Null:

Index_type: BTREE

Comment:

Index_comment:

删除索引：drop index ind_name on testclob;

8.2.2 间隙锁（Gap lock）

在RR隔离级别，为避免幻读，引入Gap lock，但是他只锁定行记录数据范围，不包含记录本身，即不允许在此范围内插入任何数据

RC（read committed）隔离级别下，允许幻读

8.2.3 Next-key Locks

是记录锁（Record Lock）和间隙锁（Gap Lock）的组合，当InnoDB扫描索引记录时，会先对选中的索引记录加上记录锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。

例：

会话A：select  * from testclob where name = 'ljing' for update;

会话B：insert into test(name) values ('ljing');

会出现锁超时。

8.3 锁等待和死锁

锁等待：在事务过程中产生的锁，其他事务要等待上一个事务释放他的锁，才能占用该资源。超过锁等待时间就会报错：等待超时。MySQL通过参数 innodb_lock_wait_timeput 参数控制，单位秒，默认100。

show variables like '%innodb_lock_wait_timeout%';

+--------------------------+-------+

| Variable_name | Value |

+--------------------------+-------+

| innodb_lock_wait_timeout | 100 |

+--------------------------+-------+

死锁：指两个或两个以上的进程在执行过程中，因争夺资源造成的互相等待的现象，就是所谓的锁资源请求产生回路现象，即死循环。常见报错：Deadlock found when trying to get lock;try restarting transaction

查看死锁：show engine innodb status;

避免死锁的方法：

（1）如果不同程序会并发存取多个表，或者涉及多行记录时，尽量约定以相同顺序访问表

（2）业务中尽量采用小事务，避免使用大事务，及时提交或者回滚事务，可以减少死锁产生的概率

（3）在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率

（4）对于非常容易死锁的业务，可以尝试使用升级锁粒度，通过表锁定减少死锁概率

8.4 锁问题的监控

判断事务中锁问题情况：

show full processlist;

show engine innodb status;

另外还有三张比较重要的表：

information_schema 下的：innodb_trx、innodb_locks、innodb_lock_waits