MySQL高级

MySQL高级

MySQL高级

mysql的架构介绍

Mysql简介

​ 关系型数据库，Mysql支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB

mysql内核

MysqlLinux版本安装

​ mysql在Linux上面安装比较麻烦，

​ 去Mysql官网里面去下载安装包， 下载完成之后是rpm格式的软件包

​ 在安装之前需要检查当前系统是否安装过Mysql， rpm -qa|grep -i mysql

​ 如果安装之后就会有软件的名字，

​ 先安装server端， rpm -ivh MySql-serverxxxxx 这个命令会有进度条来进行显示，是rpm格式独有的

​ 然后安装客户端， ps -ef|grep mysql

​ service mysql start 把mysql服务器启动， service mysql stop 关闭mysql

​ root密码设置和开机自启动：5.7之后的mysql版本都会默认生成密码，还得查看，所以我们安装完了之后应该修改一下登录密码， /usr/bin/mysqladmin -u root password xxxxx 设置密码

​ chkconfig mysql on 设置mysql开机自启动

​ chkconfig --list | grep mysql

​ ntsysv 可以看到一个窗口，上面显示哪些服务都是自启动

​ mysql的安装位置， 启动了myslq之后，通过 ps -ef|grep mysql 可以看到 datadir 后面就是mysql的安装位置

​ /var/lib/mysql/ mysql数据库文件的存放路径

​ /usr/share/mysql 配置文件目录

​ /usr/bin 相关命令目录

​ /etc/init.d/mysql 启停相关脚本

Mysql配置文件

​ mysql安装完成之后默认是无法显示中文的，如果往里面插入中文会变成了问号，

​ 如果安装了mysql之后就要把mysql默认编码变成 utf-8，

​ 查看字符集： show variables like ‘character%’

​ 修改配置文件， 加上一行 default-character-set=utf8

​ character_set_server=utf8

​ character_set_client=utf8

​ collation-server=utf8_general_ci

​ sort_buffer_size = 2M 复杂sql，

​ 注意：我们修改过之后那原来的数据库还是那个样子，是不会变的，所以我们往里面存储中文还是会显示乱码，并不是配置文件没有修改完毕，再重新建立一个数据库即可

​ mysql配置文件里面配置有日志文件的记录地址， log-bin= 二进制日志 log-err= 错误日志，

​ 查询日志：默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需压迫消耗系统资源的

​ 数据文件：可以配置，默认在data目录

​ frm：存放表结构 myd文件：存放表数据 myi：存放表索引

Mysql逻辑架构介绍

​ 和其他数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎

​ Optimizer，这个组件可以帮助我们分析sql，让其按照mysql想要的最好的效率去执行，可以不用它，

​ Cache Buffers

​ 存储引擎： MyISAM InnoDB 这是最常用的两种了

​ MySQL一共有四层架构：

​ 1.连接层：在该层引入了线程池的概念

​ 2.服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程，函数等，在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作，如果是select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能

​ 3.引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取，

​ 4.存储层：数据存储层，主要是将数据存储运行在裸设备的文件系统上，并完成与存储引擎的交互

Mysql存储引擎

​ show engines; 查看所有引擎， 默认的存储引擎是 InnoDB，

​

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表	行锁，操作只锁一行，适合高并发操作
缓存	只会缓存索引，不缓存真实数据	不仅缓存索引，也缓存真实的数据对内存要求比较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

​ 大公司都用哪些：Percona为MySQL数据库连服务器进行了改进，在功能和性能上较MySQL有着很显著的提升，该版本提升了在高负载情况下InnoDB的性能，为DBA提供一些非常有用的性能诊断工具，另外有更多的参数和命令来控制服务器行为，

​ 该公司新建了一款存储引擎叫xtradb 完全可以替代innodb，并且在性能和并发上做得更好，

​ ali大部分mysql数据库其实使用的percona的原型加以修改

​ AliSql+AliRedis

索引优化分析

性能下降SQL慢

执行时间长

等待时间长

​ 查询语句写的烂

​ 索引失效 单值 复合

​ create index idx_user_name on user(name);

​ create index idx_user_nameEmail on user(name,email);

​ 关联查询太多join（设计缺陷或不得己的需求）

​ 服务器调优及各个参数设置（缓冲，线程数）

常见通用的Join查询

​ A、B有交集，求A B的交集，： select <select_list> from Table A inner join Table B on A.key = B.key

​ 左表的全部和A、B的共有：select <select_list> from Table A left join Table B on A.key=B.key

​ 有表的全部和共有：select <select_list> from Table A right join Table B on A.key=B.key

​ 如果A中单独的：select <select_list> from Table A left join Table B on A.key=B.key where B.key is null

​ 如果差B中单独的：select <select_list> from Table A right join Table B on A.key=B.key where A.key is null

​ 如果要查询A和B的并集：select <select_list> from Table A full outer join Table B on A.key=B.key

​ 如果要查询A和B各自独有的：select <select_list> from Table A full outer join Table B on A.key=B.key where A.key is null or B.key is null

​ SQL解析 - -> FROM ON -----> join where ----> group by having ----->orderBy ---->limit

​ inner join查询到的只有两个表公共部分的内容，

​ left join 查询的内容有左表全部的内容，如果右表没有，就补null，

​ right join 查询的内容是右边全部的内容，左表没有的补null

​ full outer join 是全部都有，左表的内容可能为空，右表的内容也可能为空，

​ 但是这种情况下重复的内容会被计算两次

​ union 可以把 left join 和 right join 两种情况做一个去重即可得到

​ 同理，如果想要求出左表和右表中各自的内容，那就是把坐表独有的 union 右表独有的即可

索引简介

​ 索引是帮助MySQL高效获取数据的数据结构，索引是数据结构，

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

​ 为了加快 Col2的查找，可以维护一个二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录，

​ 索引需要时不时的重建一下，因为索引需要维护， 频繁删改的字段不适合建立索引，

​ 索引的优势：提高数据检索的效率，降低数据库的IO成本，通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

​ 劣势：索引大大提高了查询速度，同时却会降低更新表的速度，新增删除修改时MySQL不仅要保存树，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息，

​ 索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立适合的索引，或优化查询

​ 单值索引：一个索引只包含单个列，一个表可以有多个单列索引

​ 唯一索引：索引列的值必须唯一，但允许有空值

​ 复合索引：即一个索引包含多个列

​ 基本语法： create index indexName on mytable(columnname (length));

​ alter mytable add index on (columnname(length));

​ 删除索引： drop index [indexName] on mytable;

​ 查看：show index from table_name

​ 使用alter命令：

​ mysql索引结构：

​ BTree索引：

​ Hash索引

​ full-text全文索引

​ R-Tree索引

​ 哪些情况需要建立索引：不重复，唯一，

​ 哪些情况不适合建立索引：重复比较高，

性能分析

​ MySQL Query Optimizer

​ 1.Mysql中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划，（它认为的最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）

​ 2.当客户端向MySQL请求一条Query，命令解析器模块完全请求分类，区别出是selet并转发给MySQL Query Optimize时，它首先会对整条Query进行优化，处理掉一些常量表达式的运算，直接换算成常量值，并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件，结构调整等，然后分析Query中的Hint信息，看显示Hint信息是否可以完全确定该Query的执行计划，如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划

​ MySQL常见瓶颈：

​ CPU：CPU再饱和的适合一般发生在数据装入内存或从磁盘上读取数据的时候，

​ IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

​ 服务器硬件的性能瓶颈： top free iostat 和 vmstat 来查看系统的性能状态

​ Explain：

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

​ 怎么玩？ explain+SQL语句，

​ 执行计划包含的信息：

​ id select_type table partitions type possible_keys key key_len ref rows filtered Extra

​ 1 SIMPLE users ALL 1 100

​ 各个字段的解释

​ id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，

​ 它的值有三种情况， 1. id相同的情况下，执行顺序由上至下，

​ 2.id不同， 如果是子查询，id的序号会递增，id值越大优先级越高，越优先被执行，从查询语句的角度来看也就是最里面的最先被执行，

​ 3.id相同不同，同时存在，id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行

​ select_type： simple 简单的select查询，查询中不包含子查询或者union

​ primary：查询中若包含子查询，最外层为primary，

​ subquery：在select或者where列表中包含子查询

​ derived：在from列表中包含的子查询标记为derived，mysql会递归执行这些子查询，把结果放在临时表里

​ union：若第二个select出现在union之后，就被标记为union，若union包含在from子句的子查询中，外层select将被标记为derived

​ union result：从union表获取结果的select

​ table：显示这一行数据是关于哪一张表

​ type： all index range ref eq_ref const,system null

​ type从最好到最差依次是： system>const >eq_ref> ref > range > index > all

​ 一般来说，得保证查询至少达到range级别，最好能达到ref，

​ system：表只有一行记录，这是const类型的特例，平时不会出现，可以忽略不计

​ const：表示通过索引一次就找到了，const用于比较primary key 或者 unique索引，因为只匹配一行数据，所以很快，如果将主键置于where列表中，mysql就能将该查询转换为一个常量

​ eq_ref: 唯一性索引扫描,对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描

​ ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

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

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

​ all：全表扫描，

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

​ keys：实际使用的索引，如果为null，则灭一使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中，

​ key_len : 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好，这并非是实际使用长度，根据表定义计算而得，不是通过表内检索出的

​ ref：显示索引的哪一列被使用了，如果可能的话，是一个常数，

​ rows：根据表统计信息和查询出所需要的信息，需要查询多少行记录

​ **extra：**包含不适合在其他列显示，但是十分重要的信息，

​ 1.Using filesort 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行，不太好，主要的原因可能是因为我们创建了联合索引，这个联合索引是排好序的，如果我们按照这个联合索引的字段进行查询，那么很方便就可以查找到排好序的数据，但是如果我们根据其中单独的几个字段进行排序，那么就不太可能了，因为这个排序我需要自己去找，需要我们自己找出来，然后再进行排序

​ 2.Using temporary：这个更加的严重，使用了临时表保存中间的结果，MySQL再对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by 建立的索引在字段和顺序上一定要符合这些后面的内容

​ 3.Using index：表示相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错，如果同时出现using where，表明索引被用来执行索引键值的查找，如果没有同时出现using where ，表明索引用来读取数据而非执行查找动作， 覆盖索引：就是我建立的是一个联合索引，然后我刚好查询的也是联合字段，这样直接可以在存储索引的地方就可以找到答案

​ 注意：如果要使用覆盖索引，一定要注意select列表中只能取出需要的列，不可 select * 因为如果将所有的字段一起左索引会导致索引文件过大，查询性能下降

​ 4.Using where，使用了where过滤

​ 5.Using join buffer，使用了连接缓存

​ 6.impossible where where的值总是false，不能用来获取元素，

​ 7.select tables optimized away 在没有groupby子句的情况下，基于索引优化min/max操作或者对于myISAM存储引擎优化count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化，

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

​ 能干吗？

​ 表的读取顺序

​ 数据读取操作的操作类型

​ 哪些索引可以使用

​ 哪些索引被实际使用

​ 表之间的引用

​ 每张表有多少行被优化器查询

​

索引优化

##### 索引分析

单表： 查询category_id为1且comments大于1的情况下，views最多的article_id

low: select id, auth_id from article where category_id = 1 and comments>1 order By views Desc limit 1;

这个也可以查询出来，但是需要全表扫描，还要排序，也没有使用索引， type是all

优化：新建索引， create index idx_article_ccv on article(category_id,comments,views);这个时候使用了索引

​ 然后 type 也变成了 range，但是还是会Using fileSort

​ 删除前面建立的三个字段的联合索引，我们新建一个两个字段的联合索引，就变得比较好了，

两表：一般都要使用一个表作为驱动表，这是不可避免的，因为总要查询一个表的全部内容，这个时候肯定是查询内容比较少的那个表比较好， 左连接应该应该加到后面那个表比较好，因为这是由于左连接的性质所决定的，left join条件用于确定如何从右表搜索行，左边一定都有，左连接的话左边不管加不加索引，一定都是会被全部扫描的，索引说应该在大表建立索引，查询小表来驱动大表，

​ 如果是右连接的话，从左表搜索行，右边一定都有，所以左边是关键点，一定要建立索引

三表：三表连接

​ select * from class left join book on class.card=book.card left join phone on book.card=phone.card;

​ 这个时候需要在book，和phone都属于左连接的右表，所以我们需要对它进行一系列的优化，建立索引

结论：尽可能减少join语句中的nestedLoop的循环总次数，永远用小表来驱动大表

​ 优先优化内层循环

​ 保证join语句中被驱动表上join条件字段已经被建立索引

​ 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置

索引失效（应该避免）

如果我们按照一定的顺序将三个字段联合建立索引，那么我们按照顺序去查询是可以使用到索引的，但是如果我们按照后面两个去查询的话，那么这个索引就无法使用了，因为一楼没有了，是不可能直接上到二楼的，

最佳左前缀法则：如果索引了多列，要遵守最左前缀法则，就是查询从索引的最左前列开始并且不跳过索引中的列，

不要再索引列上做任何操作：（计算，函数 、自动或者手动的类型转换）会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列： 范围之后的索引全部失效

尽量使用覆盖索引（只访问索引的查询（索引和查询列一致））：也就是说尽量访问索引的字段

在使用不等于 的时候无法使用索引会导致全表扫描

is null ,is not null也无法使用索引

like以通配符开头，mysql索引失效会变成全表扫描的操作， 这一下子就想的出来，索引也是有顺序的

​ 但是有的时候确实也需要like在前面加上通配符，这个时候可以使用覆盖索引，将比较常用的联合起来建立

字符串不加单引号索引失效， varchar类型绝对不能失去单引号， 如果不加上单引号，数据库可能会以为我们是其他类型的，会隐式的做一个类型转换，那就无法使用索引了，

少用or，用它来连接时索引会失效，不过在之后的版本好像是可以了

总结：

面试题讲解

当我们建立了一个联合索引，但是我们查询的条件顺序不对时，那么我们不需要太在意，mysql会自动优化顺序的

如果索引是连续的 查询 c1 然后根据 c2,c3排序，这个时候可以直接根据索引排序，不会显示filesort

如果复合索引的 c2=‘a2’ order by c3,c2, 与 order by c3 没有什么区别，因为c2 是固定的

order by 一般情况下只要没有与索引的顺序一致，都会产生 filesort

group by 基本上都需要进行排序，会有临时表产生，一定要按照索引的顺序来

一般性建议

对于单键索引，尽量选择针对当前query过滤性更好的索引

在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好，

在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引，

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

注意：如果是> < 这种索引条件，之后就失效了，但是如果是 like 后面% 这个还是会继续使用后面的索引，

但是如果%在前面，那它自己也不会使用到索引，

优化总结口诀：

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

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

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

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

查询截取分析

查询优化

开启慢查询日志记录，并捕获，

explain+慢查询分析，

show profile 查询sql在mysql服务器里面的执行细节和生命周期情况

sql数据库服务器的参数调优

select * from A where id in (select id from B)

等价于：

for select id from B

for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in 优于exists 反之一样

exists 是将主查询的数据，放入子查询中做条件严重，根据验证结果来决定主查询的数据结果是否得以保留

in 里面查找小表，因为 in 会先查里面的表，exists 里面放大表，因为exists 会先将外面的表先查了，再跟里面的表数据做匹配

order by 排序优化， 尽量使用index方式排序，避免使用 filesort方式排序 index效率高，它是指mysql扫描索引本身完成排序，

order by 满足两种情况，会使用index方式排序， order by 语句使用索引最左前列， 使用where 子句与 order by子句条件组合满足索引最左前列

如果不在索引列上，filesort有两种算法， 双路排序 和 单路排序

​ 双路：以前两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照顺序进行输出 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段，

​ 这是之前的方式，后来改进了一下，变成单路排序，

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

单路也会有一些问题，每次的容量比较小，如果需要排序的数据容量比较大，可能会执行多次进行多路合并，

order by 时应该只query需要的字段，这点非常重要，

where a in () order by b,c 也是无法使用索引的，因为对于排序来说，多个相等的条件也是范围查询

group by 实质时先排序后进行分组，遵照索引建的最佳左前缀，

当无法使用索引列，增大 max_length_for_sort_data 参数的设置，+ 增大 sort_buffer_size 参数的设置

where 高于 having， 能写在where限定的条件就不要去having限定了

慢查询日志

这是mysql 提供的一种日志记录，用来记录在mysql中响应时间超过阈值的语句，及具体指运行时间超过long_query_time 值的sql，会被记录到慢查询日志中

long_query_time 默认是 10 ，意思就是10s

默认mysql没有开启慢查询，需要手动来设置这个参数，如果不是调优需要的话，一般不建议启动，因为会影响性能，

查看是否开启： show variables like ‘%slow_query_log%’ 可以看到，

可以通过设置 slow_query_log 的值来进行开启， set global slow_query_log=1;

如果想要让慢查询日志永久生效，那么需要修改 my.cnf 文件，

slow_query_log=1

slow_query_log_file=/var/lib/mysql/xx-slow.log

查询当前的阈值：show variables like ‘long_query_time’;

设置慢的阈值时间 ：set global long_query_time=3;

修改完了之后重新连接才可以看到修改之后设置的阈值，

日志分析工具 mysqldumpslow

mysqldumpslow --help

得到返回记录集最多的10个SQL： mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个sql：mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g ‘left join’ /var/lib

s:按照何种方式排序 c：访问次数 l：锁定时间， r：返回记录 t：查询时间 al：平均锁定时间

ar：平均返回记录数， at：平均查询时间 t：返回前面多少条数据， g：后面搭配一个大小写不敏感的正则表达式

批量数据脚本

建立函数 create function,

创建函数会报错，我们需要配置一下才可以

show variables like ‘log_bin_true_function_creators’;

set global log_bin_trust_function_creators=1; 这样开启创建存储函数的权限

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8RrjpdNU-1613484473320)(%E5%9B%BE%E7%89%87%E6%96%87%E4%BB%B6/1610528344213.png#alt=1610528344213)]

上面是我们创建了一个函数，这个函数用来

创建存储过程：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qiRVxVAd-1613484473330)(%E5%9B%BE%E7%89%87%E6%96%87%E4%BB%B6/1610528658034.png#alt=1610528658034)]

这是直接往里面插入了很多数据的存储过程，这个存储过程也使用到了那个函数

调用存储过程：delimiter; call inser_dept(100,10); 从100开始添加10个数据

这个用于往数据库里面插入大量的数据，

show Profile

性能分析脚本

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量

show variables like ‘profiling’ set profiling on

这个查询跟查询一个表一样，可以直接在表里面进行修改，

开启了这个功能之后，在后端自动就会记录下来我们执行的sql操作，

show profiles 可以查看我们执行过的sql，

show profile cpu,block io for query 查询的ID编号 可以查看详细的内容 ，可以看到每一步的具体数据，

all:显示所有的开销信息， block io 显示块IO相关开销 context switches 上下文切换相关开销

cpu：显示cpu相关开销 IPC：显示发送和接收相关开销信息 memory：显示内存开销信息

page faults：显示页面错误相关开销信息 source：显示

swaps：显示交换次数相关开销的信息，

但是怎么分辨是不太好的，

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

creating tmp table 创建临时表，拷贝数据到临时表 用完再删除

copying tmp table on disk 把内存中临时表复制到磁盘，

locked

全局查询日志

不要再生产环境开启这个功能，

配置启用 general_log=1,

记录日志文件的路径 general_log_file=/path/logfile

输出格式 log_output=FILE

此后，所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看

select * from mysql.general_log;

MySql锁机制

锁有利有弊，可以对有限的资源进行保护，

读锁和写锁

读锁：共享锁，针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁：当前写操作没有完成之前，它会阻断其他写锁和读锁 （排他锁）

行锁（偏写）

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

InnoDB与MyISAM的最大不同：支持事务，采用行锁，

事务：ACID 原子性，一致性，隔离性，持久性

并发事务处理带来的问题：

更新丢失：两个或者多个事务选择同一行，由于每个事务都不知道其他事务的存在，版本覆盖的问题，如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可以避免此类问题，

脏读：一个事务读取到了另一个事务已经修改，但是尚未提交的数据，还在这个数据基础上做了操作，如果另一个事务回滚，则一个事务读取的数据无效，不符合一致性要求，

不可重复读：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除了，这种现象就叫做不可重复读， A读取到了事务B已经提交的修改数据，不符合隔离性

幻读：幻读和脏读有些类似，脏读时事务B里面修改了数据，幻读是事务B新增了数据，

事务隔离级别：

读数据一致性及允许的并发副作用隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读	语句级	否	是	是
可重复读	事务级	否	否	是
可序列化	最高级别，事务级	否	否	否

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行，这显然与并发是矛盾的，同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对不可重复读和幻读并不敏感，可能更关系数据并发访问的能力

查看当前数据库的事务隔离级别： show variables like ‘tx_isolation’;

默认的隔离级别是可重复读，

设置不让自动提交，set autocommit=0; 这时更新但是不提交，更新了之后，在另外一个事务发送更新同一行的请求后会出现阻塞，原本的更新提交之后，阻塞被放开， 但是如果他们更新的不是同一行，那么不会被阻塞，两边都commit，然后都可以看到自己和对方修改之后的数据，如果不是自动提交的话，需要先把本次session提交之后才可以看到别的session提交的数据，

无索引行锁升级为表锁：

如果我们失误varchar没有加引号，那么就无法使用索引，也就无法依据索引结构确定到底去那一行进行操作，然后在操作期间就会把整张表给锁住，原本对其他行的操作是可以进行的，现在表被锁之后也会被排斥

间隙锁的危害：

操作一个在索引范围内的数据，但是这个范围内缺少一个数据， 那么在执行期间，我们是无法插入这个缺少的数据的，这个数据也被阻塞了，

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但不存在的记录，叫做 间隙

InnoDB也会对 间隙 进行加锁，这种锁机制就是所谓的间隙锁，

危害： 因为Query执行过程通过范围查找的话，会锁定整个范围内所有的索引键值，即便这个键值并不存在，

间隙锁有一个比较致命的弱点，就是当一个范围键值被锁定之后，即便某些不存在的键值也会被无辜的锁定，而造成在锁定的时间无法插入锁定键值范围内的任何数据，在某些场景下这可能会对性能造成很大的伤害，

面试：如何锁定一行：

begin;

select * from test_innodb_lock where a=8 for update;

commit;

当锁定一行后，其他的操作会被阻塞，直到 commit之后会释放

总结：

查看锁的参数 show status like ‘inno_row_lock%’；

可以查看各种锁所消耗的时间

比较重要的时：

Innodb_row_lock_time_avg 等待平均时长

Innodb_row_lock_waits 等待总次数

Innodb_row_lock_time 等待总时长

分析这些基本上可以判断出来了，尤其时等待次数很高，并且每次等待时长也不小的时候，需要优化，

优化建议：

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁，

合理设计索引，尽量缩小锁的范围，

尽可能减少检索条件，避免间隙锁，

尽量控制事务大小，减少锁定资源量和时间长度，

尽可能低级别事务隔离，

表锁

偏向于读操作多一些

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

手动增加表锁 lock table 表名字 read(write);

查看表上加过的锁：show open tables;

unlock tables 解锁

SELECT * from users
lock table users READ;
UNLOCK TABLEs;
UPDATE users set username='xxx' WHERE id=1

加了读锁之后无法修改，并且只能读取当前加锁的表，也不能修改当前表，没有解锁之前读取不了其他的表，

如果session1给表加了读锁，session2想要更改，这是可以执行的，但是操作会被阻塞，只要解锁，这个修改的操作可以立即被执行

如果加上了写锁，当前session可以查询和修改，但是其他session不能修改和查询，

myISAM在执行查询语句之前，会自动给涉及的所有表加读锁，在执行增删改操作之前，会自动给涉及的表加写锁，Mysql的表级锁有两种模式：

表共享读锁，表独占写锁

对MyISAM表的读操作，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求，只有当读锁释放后，才会执行其他进程的写操作，

对MyISAM表的写操作，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会进行其他进程的读写操作

简而言之，读锁会阻塞写，但是不会阻塞读，写锁会阻塞读和写

有两个变量状态记录MySQL内部表锁定的情况

Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁级+1

Table_locaks_waited:出现表级锁定争用而发生等待的次数，此值较高则说明存在着较严重的表级锁争用情况

MyISAM的读写锁调度时写优先，所以不适合左写为主表的引擎

页锁

开销和加锁时间界于表锁和行锁之间，会出现死锁，锁定粒度界于表锁和行锁之间，并发度一般，

了解即可

主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

复制过程有三部，

master将改变记录到二进制日志 binlog 这些记录过程叫做二进制日志事件

slave将master的binlog 拷贝到它的中继日志 relay log

slave 重做中继日志中的事件，将改变应用到自己的数据库中，mysql复制时异步的且串行化的

复制的基本原则

每个slave只有一个master

每个slave只能有一个唯一的服务器ID

每个master可以有多个slave

复制的最大问题

延时

一主一从常见配置

mysql版本一致且后台以服务运行

主从都配置在 mysqld 节点下，都是小写

主机修改 my.ini 配置文件，

server-id=1 主服务器唯一ID

log-bin= 路径 必须启用二进制日志

log-err=路径 启用错误日志，可以不写

basedir=路径 根目录

tmpdir=路径 临时目录

read-only=0 主机读写都可以

binlog-ignore-db=mysql 设置不要复制的数据库

binlog-do-db=mysql 设置需要复制的数据库

从机修改配置文件，

从服务器唯一ID

启用二进制日志

改动过配置文件，主机从机都重启一下，

主机和从机都要关闭防火墙 service iptables stop

在主机上建立账户并授权slave,代表着从机数据库的Ip可以通过指定的账号密码访问我们数据库

grant replication slave on . to ‘zhangsan’@‘从数据库IP’ Identified by ‘123456’;

flush privileges; 刷新

show master status; 查询主机状态

从机也要配置：

change master to master_host=‘主机IP’ master_user=‘zhangsan’,

master_password=‘123456’,

master_log_file=‘mysqlbin.具体数字’, master_log_pos=具体值

start salve; 启动从服务器的复制功能

show slave status \G 查看状态

stop slave； 停止从机复制功能