mysql学习笔记

一、mysql体系结构

从上到下，连接层，服务层，引擎层，存储层

不同存储引擎对比

二、索引优化分析

mysql索引分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
• 唯一索引：索引列的值必须唯一，但允许有空值
• 复合索引：即一个索引包含多个列

哪些情况需要创建索引

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• where条件里用不到的字段不创建索引
• 单键/组合索引的选择问题，who？（在高并发下倾向创建组合索引）
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段

哪些情况不需要创建索引

• 表记录太少
• 经常增删的表
• 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重IO负担
• 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

explain性能分析

能干嘛

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的应用
• 每张表有多少行被优化器查询
  

各字段解释

1.id:select查询的序列号

查询中执行select子句或操作表的顺序

三种情况：
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在，id值大的先被执行

2.select_type：查询的类型

主要是用于区别普通查询、联合查询、子查询等的复杂查询

SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里。
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT：从UNION表中获取结果的SELECT。

3.table：显示这一行的数据是关于哪些表的

4.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索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

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。

5.possible_keys ：可能应用在这张表中的索引

查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。

6.key：实际使用的索引

如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数一一吻合）

7.key_len：表示索引中使用的字节数

可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

8.ref：显示索引的哪一列被使用了

如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

9.rows：mysql认为必须要逐行去检查和判断的记录的条数

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

10.Extra：包含不适合在其他列中显示但十分重要的额外信息

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。

Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

Using where：表明使用了where过滤。

Using join buffer：使用了连接缓存。

impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）

select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

索引优化

join语句优化

尽可能减少Join语句中的循环总次数：“永远用小结果集驱动大的结果集”。
优先优化嵌套查询中的内层循环。
保证Join语句中被驱动表上Join条件字段已经被索引。
当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置。

索引失效问题优化

1.全值匹配我最爱
2.最佳左前缀法则：
聚合索引遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断）
3.不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
4.聚合索引不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
7.is null，is not null也无法使用索引
8.like
like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。
like通配符在右边，后面的索引还会走。
利用覆盖索引解决两边%的优化问题。
9.sql不规范会导致自定类型转换，例如字符串不加单引号索引失效
10.少用or，用它来连接时会索引失效

优化总结口诀：

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

三、查询截取分析

filesort

filesort有两种排序算法：双路排序和单路排序。

双路排序：在MySQL4.1之前使用双路排序，就是两次磁盘扫描，得到最终数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段，在buffer进行排序，再从磁盘取其他字段。

如果使用双路排序，取一批数据要对磁盘进行两次扫描，众所周知，I/O操作是很耗时的，因此在MySQL4.1以后，出现了改进的算法：单路排序。

单路排序：从磁盘中查询所需的列，按照order by列在buffer中对它们进行排序，然后扫描排序后的列表进行输出。它的效率更高一些，避免了第二次读取数据，并且把随机I/O变成了顺序I/O，但是会使用更多的空间，因为它把每一行都保存在内存中了。

单路排序的问题

当读取数据超过sort_buffer的容量时，就会导致多次读取数据，并创建临时表，最后多路合并，产生多次I/O，反而增加其I/O运算。

优化策略

• 增大sort_buffer_size参数的设置

• 增大max_length_for_sort_data参数的设置
  

GROUP BY(优化时参照order by处理)

group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
where高于having，能写在where限定的条件就不要去having限定了。

慢查询日志

慢查询日志是什么

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%';
开启慢查询日志(如果要永久生效需要在my.cnf中设置)
set global slow_query_log=1;
查看当前多长时间的查询算是慢查询
SHOW VARIABLES LIKE 'long_query_time%';
设置慢查询阈值
set global long_query_time=3;
查询当前系统中有多少条慢查询记录
show global status like '%Slow_queries%';

show profile

mysql提供的可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量。默认处于关闭状态，并保存最近15次运行结果。

如何使用

查看当前的mysql版本是否支持
show variables like 'profiling';
开启show profile
set profiling = on;
查看结果
show profiles;
诊断SQL
show profile cpu, block io for query 1;

需要注意的查询结果

converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了

Creating tmp table：创建临时表(1.拷贝数据到临时表，2.用完再删除)
Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！

locked

全局查询日志

两种启用方式(永远不要在生产环境开启这个功能!)

• 配置启用
  

• 编码启用
  

四、mysql锁机制

• 锁的分类
  • 从对数据操作的类型（读/写）分
    • 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
    • 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
  • 从对数据操作的粒度分
    • 表锁
    • 行锁

表锁(偏读)

特点：

偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

表锁操作

• 手动增加表锁
  • lock table 表名字 read(write), 表名字2 read(write), 其他;
• 查看表上加过的锁
  • show open tables;
• 释放表锁
  • unlock tables;
    

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

表锁分析

• 看看哪些表被加锁了：show open tables;
• 如何分析表锁定：可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。
• show status like ‘table%’;
• 这里有两个状态变量记录MySQL内部表级锁定的情况，两个变量的说明如下：
  • Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
  • Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。
• 此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞

行锁(偏写)

特点

偏向Innodb存储引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高。
Innodb与MyISAM的最大不同有两点：
一是支持事务（TRANSACTION）
二是采用了行级锁

数据库事务（Transaction）的ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的					完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间					状态对外部是不可见的，反之亦然。
持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

数据库事务隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted 、Read committed 、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读 、不可重复读 、幻读 这几类问题。

	脏读	不可重复读	幻读
Read uncommitted	√	√	√
Read committed	×	√	√
Repeatable read	×	×	√
Serializable	×	×	×

注意：我们讨论隔离级别的场景，主要是在多个事务并发 的情况下，因此，接下来的讲解都围绕事务并发。

查看当前数据库的事务隔离级别:

SHOW VARIABLES LIKE '%tx_isolation%';

Read uncommitted 读未提交

公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高 兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有 2000元，singo空欢喜一场。

出现上述情况，即我们所说的脏读 ，两个并发的事务，“事务A：领导给singo发工资”、“事务B：singo查询工资账户”，事务B读取了事务A尚未提交的数据。

当隔离级别设置为Read uncommitted 时，就可能出现脏读，如何避免脏读，请看下一个隔离级别。

Read committed 读提交

singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在 singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为 何......

出现上述情况，即我们所说的不可重复读 ，两个并发的事务，“事务A：singo消费”、“事务B：singo的老婆网上转账”，事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

当隔离级别设置为Read committed 时，避免了脏读，但是可能会造成不可重复读。

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。如何解决不可重复读这一问题，请看下一个隔离级别。

Repeatable read 可重复读

当隔离级别设置为Repeatable read 时，可以避免不可重复读。当singo拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），singo的老婆就不可能对该记录进行修改，也就是singo的老婆不能在此时转账。

虽然Repeatable read避免了不可重复读，但还有可能出现幻读 。

singo的老婆工作在银行部门，她时常通过银行内部系统查看singo的信用卡消费记录。有一天，她正在查询到singo当月信用卡的总消费金额 （select sum(amount) from transaction where month = 本月）为80元，而singo此时正好在外面胡吃海塞后在收银台买单，消费1000元，即新增了一条1000元的消费记录（insert transaction ... ），并提交了事务，随后singo的老婆将singo当月信用卡消费的明细打印到A4纸上，却发现消费总额为1080元，singo的老婆很诧异，以为出 现了幻觉，幻读就这样产生了。

注：Mysql的默认隔离级别就是Repeatable read。

Serializable 序列化

Serializable 是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。

行锁定基本演示

无索引行锁升级为表锁

如果在更新数据的时候出现了强制类型转换导致索引失效，使得行锁变表锁，即在操作不同行的时候，会出现阻塞的现象。

间隙锁危害

• 什么是间隙锁：当我们用范围条件而不是相等条件索引数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
• 危害：
  间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

如何锁定一行

select * from 表名 where 条件 for update;

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

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：系统启动后到现在总共等待的次数。

对于这5个变量，比较重要的是:

innodb_row_lock_time_avg（等待平均时长）
innodb_row_lock_waits（等待总次数）
innodb_row_lock_time（等待总时长）

这三项
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

行锁总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

五、主从复制

主从复制原理

Binary log(又binlog )：主数据库的二进制日志。

Relay log：从服务器的中继日志。

1、master将改变记录到Binary log，这些记录过程叫做二进制日志事件，Binary log events。

2、salve开启一个I/O Thread，该线程在master打开一个普通连接，主要工作是binlog dump process。如果读取的进度已经跟上了master，就进入睡眠状态并等待master产生新的事件。I/O线程最终的目的是将这些事件写入到中继日志(relay log)。

3、SQL Thread会读取中继日志，并串行化执行该日志中的SQL事件，从而与主数据库中的数据保持一致。Mysql复制是异步且串行化的。

搭建mysql主从

安装mysql

https://blog.csdn.net/qq_36582604/article/details/80526287

修改master配置(/etc/my.cnf)

server-id=1
log-bin=mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_authentication_plugin=mysql_native_password

修改slave配置(/etc/my.cnf)

server-id=2
log-bin=mysql-bin
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

修改配置后重启master和slave。

master添加允许的从服务器ip和账号

grant replication slave on *.* to 'slaveA'@'192.168.137.201' identified by 'Flzxsqc2021!';
flush privileges;

查看slaveA的授权

show grants for slaveA@192.168.137.201;

查看master mysql状态

show master status;

设置主服务器ip，同步账号密码，同步位置

change master to master_host='192.168.137.200',master_user='slaveA',master_password='Flzxsqc2021!',master_log_file='mysql-bin.000002',master_log_pos=908;
# 开启同步功能
start slave;
# 查看slave状态
show slave status\G;# 看到Slave_IO_Running和Slave_SQL_Running都是yes说明配置成功

验证主从同步

在master创建数据库、表并插入数据，slave同步成功。