MYSQL高级
一、Mysql的架构介绍
1、Mysql配置文件
- 二进制日志log-bin—主从复制
- 错误日志log-error
- 默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等
- 查询日志log
- 默认不安比，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
- 数据文件
- 两个系统
- windows—d:\devSoft\MySQLServer5.5\data目录下可以挑选很多库
- linux—/var/lib/mysql
- frm文件：存放表结构
- myd文件：存放表数据
- myi文件：存放表索引
- 两个系统
2、Mysql逻辑架构介绍
和其他数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取相分离。这种架构可以根据业务的实际需要选择合适的存储引擎。
[外链图片转存失败(img-g5SRmEIh-1562814200340)(https://s2.ax1x.com/2019/07/01/Z3HL3n.png)]
-
连接层
- 最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
-
服务层
- 第二层架构主要完成大多的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
-
引擎层
- 存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
-
存储层
- 数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互
3、存储引擎
-
查看命令
- show engines
- show variables like ‘%storage_engine%’
-
MyISAM和InnoDB
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 主外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作 | 行锁，操作时只锁某一行，不对其它行有影响 ，适合高并发的操作 |
| 缓存 | 只缓存索引，不缓存真实数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响 |
| 表空间 | 小 | 大 |
| 关注点 | 性能 | 事务 |
| 默认安装 | Y | Y |
- 阿里巴巴、淘宝用的哪个？
- Percona为MySQL数据库服务器进行了改造，在功能和性能上较MySQL有着明显的提升。该版本提升了在高负载情况下的InnoDB的性能、为DBA提供一些非常有用的性能诊断工具，另外有更多的参数和命令来控制服务器行为。
- 该公司新建了一款存储引擎叫xtradb完全可以替代innodb，并且在性能和并发上做的更好。
- 阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改
- AliSql+AliRedis
二、索引优化
1、性能下降SQL慢，执行时间长，等待时间长
-
查询语句写的烂
-
索引失效
- 单值
create index idx_user_name on user(name);- 复合
create index idx_user_nameEmail on user(name,email); -
关联查询太多join（设计缺陷或不得已的需求）
-
服务器调优及各个参数设置（缓冲、线程数等）
2、常见的join查询
-
SQL执行顺序
- 手写
select distinct <select_list> from <left_table> <join_type> join <right_table> on <join_condition> where <where_condition> group by <group_by_list> having <having_condition> order by <order_by_condition> limit <limit number>- 机读
from <left_table> on <join_condition> <join_type> join <right_table> where <where_condition> group by <group_by_list> having <having_condition> select distinct <select_list> order by <order_by_condition> limit <limit_number>- 总结
-
SQL join图
[外链图片转存失败(img-YiWk8dVK-1562814200342)(https://s2.ax1x.com/2019/07/01/Z8s8pQ.jpg)]
3、索引简介
1、是什么？
-
MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构。
-
索引的本质：索引是数据结构
-
可以简单理解为“排好序的快速查找数据结构”
-
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，下图就是一种可能 的索引方式示例：
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出复合条件的记录。
-
-
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
-
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中含有聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。
2、优势
- 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了cpu的消耗
3、劣势
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用空间的
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询
4、MySQL索引分类
-
单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
-
唯一索引
- 索引列的值必须唯一，但允许有空值
-
复合索引
- 即一个索引包含多个列
-
基本语法
- 创建
create [unique] index indexName on mytable(columnnname(length)); alter table mytable add [unique] index [indexName] on (columnname(length));- 删除
drop index [indexName] on mytable;- 查看
show index from table_name- 修改
#添加一个主键，这意味着索引值必须是唯一的，且不能为NULL alter table tbl_name add primary key(column_list); #创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次） alter table tbl_name add unique index_name(column_list); #添加普通索引，索引值可出现多次 alter table tbl_name add index index_name(column_list); #指定索引为FULLTEXT,用于全文索引 alter table tbl_name add FULLTEXT index_name(column_list);
5、MySQL索引结构
如上图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
6、哪些情况需要创建索引
-
主键自动建立唯一索引
-
频繁作为查询条件的字段应该创建索引
-
查询中与其他表关联的字段，外键关系建立索引
-
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
-
where条件里用不到的字段不创建索引
-
单键/组合索引的选择问题（在高并发下倾向创建组合索引）
-
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
-
查询中统计或者分组字段
7、哪些情况不要创建索引
- 表记录太少
- 经常增删改的字段
- 提高了查询速度，同时却会降低更新表的速度，如对表进行insert,update,delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
- 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
- 假如一个表有10万行数据，有一个字段A只有T和F两个值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
- 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
4、性能分析
1、MySQL Query Optimizer
- MySQL中有专门负责优化select语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）
- 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是select并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转化，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行些相应的计算分析，然后再得出最后的执行计划。
2、MySQL常见瓶颈
- CPU：cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
3、Explain
-
是什么（查看执行计划）
- 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
-
能干嘛
- 表的读取顺序（由id决定）
- 数据读取操作的操作类型(select_type)
- 哪些索引可以使用（possible_key）
- 哪些索引被实际使用(key)
- 表之间的作用
- 每张表有多少行被优化器查询（rows）
-
怎么玩
- Explain+SQL语句
- 执行计划包含的信息
-
各字段解释
-
id
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
- id相同，执行顺序由上至下
- id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- id相同不同，同时存在
- id如果相同，可以认为是一组，从上往下顺序执行；
- 在所有组中，id值越大，优先级越高，越先执行
- select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
-
select_type
-
有哪些
- SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
-
查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询
-
-
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
-
访问类型从最好到最差依次是：
- 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
-
-
possible_keys
- 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
-
keys
- 实际使用的索引，如果为null，则没有使用索引
- 查询中若使用了覆盖索引，则该索引仅出现在key列表中
- 覆盖索引（索引覆盖）：
- 就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
- 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
- 注意：
- 如果使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降
- 覆盖索引（索引覆盖）：
-
key_len
- 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好
- key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
-
ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
-
rows
- 根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
-
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，不能用来获取任何元组
-
-
-
select tables optimized away
在没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
-
-
-
distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
-
-
-
5、索引优化
1、索引分析
1、单表
-
explain select id,author_id from 'article' where category_id=1 and comments>1 order by views desc limit 1; explain select id,author_id from 'article' where category_id=1 and comments=3 order by views desc limit 1; -
结论：
- type变成了range，这是可以忍受的，但是extra里使用using filesort仍是无法接受的，但是我们已经建立了索引，为啥没用呢？这是因为按照BTree索引的工作原理，先排序category_id,如果遇到相同的category_id则再排序comments，如果遇到相同的comments则再排序views，当comments字段在联合索引里处于中间位置时，因comments>1条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。
-
删掉第一次建立的索引
-
drop index idx_article_ccv on article;
-
-
第二次新建索引
-
#alter table 'article' add index idx_article_cv('category_id','views'); create index idx_article_cv on article(category_id,views);
-
-
第三次explain
-
explain select id,author_id from 'article' where category_id=1 and comments>1 order by views desc limit 1; -
结论
- 可以看到，type变为ref，extra中的using filesort也消失了，结果非常理想
-
2、两表
-
explain select * from class left join book on class.card = bood.card;- 结论：type有all
-
添加索引优化
-
alter table 'book' add index y ('card');
-
-
第二次explain
-
explain select * from class left join book on class.card = bood.card; -
可以看到第二行的type变为了ref，rows也变了，优化比较明显。这是由左连接特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
-
-
删除旧索引+新建+第三次explain
-
drop index y on book; alter table class add index x(card); explain select * from class left join book on class.card = book.card; -
效果不好
-
-
改成右连接查询
- 优化较明显，这是因为right join条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引
3、三表
-
alter table phone add index z (card) alter table book add index y (card) explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; -
后两行的type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中
-
join语句的优化
- 尽可能减少join语句中的NestedLoop的循环总次数：”永远用小结果集驱动大的结果集“
- 优先优化NestedLoop的内层循环
- 保证join语句中被驱动表上join条件字段已经被索引
- 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬joinbuffer的设置
2、索引失效（应该避免）
-
全值匹配我最爱
- 索引了多列，查询条件完全按照索引的顺序和个数查找
-
最佳左前缀法则
- 如果索引了多列，要遵守最佳左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列
-
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
-
explain select * from staffs where left(name,4) = 'July';
-
-
存储引擎不能使用索引中范围条件右边的列
- 查询条件使用范围查询后，右边的索引失效
-
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
-
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
-
is null,is not null也无法使用索引
-
like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作
- 问题：解决like’%字符串 %'时索引不被使用的方法？
- 使用覆盖索引解决
- 问题：解决like’%字符串 %'时索引不被使用的方法？
-
字符串不加单引号索引失效
- 比如字段类型是varchar，查询条件使用int类型，mysql底层会做隐式类型转换，导致索引失效
-
少用or，用它来连接时会索引失效
3、面试题讲解
-
问题，我们创建了复合索引idx_test03_c1234， 根据一下sql分析下索引使用情况
- explain select * from test03 where c1=‘a1’;
- explain select * from test03 where c1=‘a1’ and c2=‘a2’;
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3=‘a3’;
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3=‘a3’ and c4=‘a4’;
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ and c3=‘a3’;
- 4个都用到了
- explain select * from test03 where c4=‘a4’ and c3=‘a3’ and c2=‘a2’ and c1=‘a1’;
- 4个都用到了
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c3>‘a3’ and c4=‘a4’;
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4>‘a4’ and c3=‘a3’;
- 用到3个，mysql底层会重新排序
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c4=‘a4’ order by c3;
- 用到2个，c3的作用在排序而不是查找，也算用到了，但没有统计到explain中
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c3;
- 用到2个
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c4;
- 用到2个，出现using filesort
- explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c2,c3;
- 只用c1一个字段索引，但是c2、c3用于排序，无filesort
- explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c3,c2;
- 出现了filesort，我们建的索引是1234，它没有按照顺序来，3 2颠倒了
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ order by c2,c3;
- 用到了2个索引，无问题
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c2,c3;
- 用c1,c2两个字段索引，但是c2,c3用于排序，无filesort
- explain select * from test03 where c1=‘a1’ and c2=‘a2’ and c5=‘a5’ order by c3,c2;
- 用到2个索引，没有出现filesort
- 本例有常量c2的情况，相当于order by c3,常量；
- explain select * from test03 where c1=‘a1’ and c5=‘a5’ order by c3,c2;
- 用到1个索引，出现filesort
- explain select * from test03 where c1=‘a1’ and c4=‘a4’ group by c2,c3;
- 用到1个索引
- explain select * from test03 where c1=‘a1’ and c4=‘a4’ group by c3,c2;
- 出现using where，using temporary, usingfilesort
- 分组之前必排序
[外链图片转存失败(img-aq0YRKpZ-1562814200344)(https://s2.ax1x.com/2019/07/09/ZyChGt.png)]
-
定值、范围还是排序，一般order by是给个范围
-
group by基本上都需要进行排序，会有临时表产生
4、一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
- 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
5、优化总结口诀
- 全值匹配我最爱，最左前缀要遵守；
- 带头大哥不能死，中间兄弟不能断；
- 索引列上少计算，范围之后全失效；
- LIKE百分写最右，覆盖索引不写星；
- 不等空值还有or，索引失效要少用；
- VAR引号不可丢，SQL高级也不难！
三、查询截取分析
分析步骤：
- 观察，至少跑1天，看看生产的慢SQL情况
- 开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来
- explain+慢SQL分析
- show profille
- 运维经理或DBA，进行SQL数据库服务器的参数调优
总结：
- 慢查询的开启并捕获
- explain+慢SQL分析
- show Profile查询SQL在Mysql服务器里面的执行细节和生命周期情况
- SQL数据库服务器的参数调优
1、查询优化
1、永远小表驱动大表(即小的数据集驱动大的数据集)，类似嵌套循环Nested Loop
- exists
- SELECT… FROM table WHERE EXISTS(subquery)
- 该语法可以理解为：将主查询的数据放到子查询中做条件验证，根据验证结果（true或false）来决定查询的数据结果是否得以保留。
- 提示
- EXISTS(subquery)只返回true或false，因此子查询中的select*也可以是select 1或其他，官方说法是实际执行时会忽略select清单，因此没有区别
- EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实验以确定是否有效率问题
- EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
2、order by关键字优化
1、order by子句，尽量使用index方式排序，避免使用FileSort方式排序
-
案例（已建age，birth复合索引）
- explain select * from tblA where age>20 order by birth;
- 产生using filesort
- explain select * from tblA order by birth;
- 产生using filesort
- explain select * from tblA order by age ASC,birth DESC;
- 产生using filesort
- explain select * from tblA where age>20 order by birth;
-
MySQL支持二种方式的排序，filesort和Index，index效率高，它指MySQL扫描索引本身完成排序。filesort方式效率较低
-
order by满足两情况，会使用index方式排序
- order by语句使用索引最左前列
- 使用where子句与order by子句条件列组合满足索引最左前列
2、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
3、如果不在索引列上，filesort有两种算法：双路排序和单路排序
-
双路排序
- MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
- 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
-
取一批数据，要对磁盘进行两次扫描，众所周知，I/O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序
-
单路排序
- 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了
-
结论及引申出的问题
- 由于单路是后出的，总体而言好过双路
- 但是用单路有问题
- 在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排…从而多次I/O。
- 本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失
4、优化策略
- 增大sort_buffer_size参数的设置
- 增大max_length_for_sort_data参数的设置
- 提高order by的速度
- order by时select*是一个大忌只query需要的字段，这点非常重要。在这里的影响是：
- 当query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序
- 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size
- 尝试提高sort_buffer_size
- 不管用那种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
- 尝试提高max_length_for_sort_data
- 提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率
- order by时select*是一个大忌只query需要的字段，这点非常重要。在这里的影响是：
[外链图片转存失败(img-M4xbCffC-1562814200345)(https://s2.ax1x.com/2019/07/09/ZynABt.png)]
3、group by关键字优化
- group by实质是先排序后进行分组，遵照索引建的最佳左前缀
- 当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
2、慢查询日志
1、是什么？
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
- 具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句
- 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析
2、怎么玩？
-
说明
- 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数
- 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
-
查看是否开启及如何开启
- 默认
- show variables like ‘%slow_query_log%’
- 开启
- set global slow_query_log=1;
- 使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效
- 如果要永久生效，就必须修改配置文件my.cnf(其他系统变量也是如此)，修改my.cnf文件[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后，然后重启Mysql服务器。也即将如下两行配置进my.cnf文件
- slow_query_log=1
- slow_query_log_file=/var/lib/mysql/xxx-slow.log
- 关于慢查询的参数slow_query_log_file,它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log(如果没有指定参数slow_query_log_file的话)
- 默认
-
开启了慢查询日志后，什么样的sql才会记录到慢查询日志里面呢？
- 这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：show variables like ‘long_query_time%’;
- 可以使用命令修改，也可以在my.cnf参数里面修改
- 假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于
-
case
-
查看当前多少秒算慢
- show variables like ‘long_query_time%’;
-
设置慢的阙值时间
- set global long_query_time=3;
-
为什么设置后看不出变化？
- 需要重新连接或新开一个会话才能看到修改值
- show variables like ‘long_query_time%’;
- show global variables like ‘long_query_time’;
-
记录慢SQL并后续分析
-
查询当前系统中由多少条慢查询记录
- show global status like ‘%slow_queries%’
-
-
配置版
- 【mysqld】下配置
- slow_query_log=1;
- slow_query_log_file=xxx/xxx/xxx-slow.log
- long_query_time=3;
- log_output=FILE
- 【mysqld】下配置
3、日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志、查找、分析sql，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow
- 查看mysqldumpslow的帮助信息
- s:是表示按照何种方式排序；
- c:访问次数
- l：锁定时间
- r:返回记录
- t:查询时间
- al：平均锁定时间
- ar：平均返回记录数
- at:平均查询时间
- t:即为返回前面多少条的数据
- g：后边搭配一个正则匹配模式，大小写不敏感的
- 工作常用参考
- 得到返回记录集最多的10个SQL
- mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log
- 得到访问次数最多的10个SQL
- mysqldumpslow -s c -t 10 /var/lib/mysql/xxx-slow.log
- 得到按照时间排序的前10条里面含有左连接的查询语句
- mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/xxx-slow.log
- 另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
- mysqldumpslow -s r -t 10 /var/lib/mysql/xxx-slow.log|more
- 得到返回记录集最多的10个SQL
3、批量数据脚本
-
往表里插入1000w数据
-
建表
-
设置参数log_bin_trust_function_creators
- 创建函数，假如报错：This function has none of DETERMINISTIC…
- 由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数
- show variables like ‘log_bin_trust_function_creators’;
- set global log_bin_trust_function_creator=1;
- 这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法
- windows
- my.ini[mysqld]加上log_bin_trust_function_creators=1
- linux
- /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
- windows
- 创建函数，假如报错：This function has none of DETERMINISTIC…
-
创建函数，保证每条数据都不同
- 随机产生字符串
delimiter $$ CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255) BEGIN DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR DEFAULT ''; DECLARE i INT DEFAULT 0; WHILE i< n DO set return_str = CONCAT(return_str,SUBSTRING(chars_str,floor(1+rand()*52),1); SET i=i+1; END WHILE; RETURN return_str; END $$- 随机产生部门编号
delimiter $$ CREATE FUNCTION rand_num() RETURNS INT(5) BEGIN DECLARE i INT DEFAULT 0; SET i = FLOOR(100+rand()*10); return i; END $$ -
创建存储过程
- 创建往emp表中插入数据的存储过程
delimiter $$ CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; SET autocommit = 0; REPEAT set i = i+1; INSERT INTO emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES ((start+i),rand_string(6),'SALEMAN',0001,curdate(),2000,400,rand_num()); UNTIL i = max_num END REPEAT; commit; END $$- 创建往dept表中插入数据的存储过程
delimiter $$ CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGIN DECLARE i INT DEFAULT 0; set autocommit = 0; REPEAT set i = i+1; INSERT INTO dept (deptno,dname,loc) VALUES ((START+i),rand_string(10),rand_string(8)); UNTIL i = max_num END REPEAT; END $$ -
调用存储过程
delimiter ; call insert_dept(100,10); call insert_emp(100001,500000); -
4、show profile
- 是什么
- 是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql的调优的测量
- 官网
- 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
- 分析步骤
-
- 是否支持，看看当前的mysql版本是否支持
- show variables like ‘profiling’
- 开启功能，默认是关闭，使用前需要开启
- set profiling = on;
- 运行sql
- select * from emp group by id%10 limit 150000;
- select * from emp group by id%20 order by 5;
- 查看结果，show profiles
- 诊断sql，show profile cpu,block io for query上一步前面的问题sql数字号码
- type:
- ALL 显示所有的开销信息
- BLOCK IO 显示块IO相关开销
- CONTEXT SWITCHES 上下文切换相关开销
- CPU 显示CPU相关开销信息
- IPC 显示发送和接受相关开销信息
- MEMORY 显示内存相关开销信息
- PAGE FAULTS 显示页面错误相关开销信息
- SOURCE 显示和Source_function,Source_file,Source_line相关的开销信息
- SWAPS 显示交换次数相关开销的信息
- type:
- 日常开发需要注意的结论（出现以下信息，危险！）
- converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了
- Creating tmp table 创建临时表
- 拷贝数据到临时表
- 用完再删除
- Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
- locked
- 是否支持，看看当前的mysql版本是否支持
-
5、全局查询日志
-
配置启用
-
在mysql的my.cnf中，设置如下：
-
开启
general_log = 1
-
记录日志文件的路径
general_log_file=/path/logfile
-
输出格式
log_output=FILE
-
-
-
编码启用
- set global general_log = 1;
- set global log_output=‘TABLE’;
- 此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
- select * from mysql.general_log;
-
永远不要在生产环境开启这个功能
四、MySql锁机制
1、概述
-
定义
- 锁是计算机协调多个进程或线程并发访问某一资源的机制
- 在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用之外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂
-
举例
-
锁的分类
- 从对数据操作的类型（读\写）
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
- 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁
- 从对数据操作的粒度
- 表锁
- 行锁
- 从对数据操作的类型（读\写）
2、三锁
-
表锁（偏读）
-
特点
- 偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
-
案例分析
-
建表（使用MyISAM存储引擎）
- 手动增加表锁
lock table 表名字 read(write),表名字2 read(write),其他；- 手动解锁
unlock tables;- 查看表上加过的锁
show open tables; -
加读锁【我们为mylock表加read锁（读阻塞写例子）】
session_1 session_2 获取表mylock的read锁定（lock table mylock read;） 连接终端 当前session可以查询该表记录 其他session也可以查询该表的记录 当前session不能查询其他没有锁定的表 其他session可以查询或者更新未锁定的表 当前session中插入或者更新锁定的表都会提示错误 其他session插入或者更新锁定表会一直等待获取锁 释放锁 session2获得锁，插入操作完成 -
加写锁【我们为mylock表加write锁（MyISAM存储引擎的写阻塞读例子）】
session_1 session_2 获取表mylock的write锁定（lock tables mylock write;） 待session1开启写锁后，session2再连接终端 当前session对锁定表的查询+更新+插入操作都可以执行 其他session对锁定表的查询被阻塞，需要等待锁被释放（备注：如果可以，请换成不同的id来进行测试，因为mysql聪明有缓存，第2次的条件会从缓存取得，影响锁效果演示） 释放锁 session2获得锁，查询返回 -
-
案例结论
-
MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁
-
MyISAM的表级锁有两种模式
-
表共享读锁（Table Read Lock）
-
表独占写锁（Table Write Lock）
锁类型 可否兼容 读锁 写锁 读锁 是 是 否 写锁 是 否 否
-
-
结论
- 结合上表，所以对MyISAM表进行操作，会有以下情况
- 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作
- 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作
- 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞
- 结合上表，所以对MyISAM表进行操作，会有以下情况
-
-
表锁分析
- 看看哪些表被加锁了
- show open tables;
- 如何分析表锁定
- 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定:show status like ‘table%’
- table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
- table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况；
- 此外，MyISAM的读写锁调度是写优先，这也是MyISAM不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞
- 看看哪些表被加锁了
-
-
行锁（偏写）
-
特点
- 偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高
- InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁
-
由于行锁支持事务，复习老知识
-
事务（Transaction）及其ACID属性
-
并发事务处理带来的问题
- 更新丢失
- 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题——最后的更新覆盖了由其他事务所做的更新
- 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
- 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题
- 脏读
- 事务A读取到了事务B已修改但尚未提交的数据
- 不可重复读
- 事务A读取到了事务B已经提交的修改数据，不符合隔离性
- 幻读
- 事务A读取了事务B已提交的新增数据，不符合隔离性
- 更新丢失
-
事务隔离级别
-
show variables like ‘tx_isolation’
读数据一致性及允许的并发副作用隔离级别 读数据一致性 脏读 不可重复度 幻读 未提交读（Read uncommitted） 最低级别，只能保证不读取物理上损坏的数据 是 是 是 已提交读（Read committed） 语句级 否 是 是 可重复读（Reapeatable read） 事务级 否 否 是 可序列化（Serializable） 最高级别，事务级 否 否 否
-
-
-
案例分析
-
建表SQL
- 使用InnoDB存储引擎
- 分别创建a字段，和b字段两个索引
-
行锁定基本演示
session_1 session_2 set autocommit=0; set autocommit=0; 更新但是不提交，没有手写commit session_2被阻塞，只能等待 提交更新 解除阻塞，更新正常进行 commit命令执行 session_1更新a=1（互不影响） session_2更新a=9（互不影响） -
无索引行锁升级为表锁
- 索引失效后，会导致行锁变表锁
- 例如update隐式类型转换导致索引失效
-
间隙锁危害
- 什么是间隙锁
- 当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
- 危害
- 因为Query执行过程中通过范围查找的话，它会锁定整个范围内所有的索引键值，即使这个键值并不存在。
- 间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害
[外链图片转存失败(img-XrXBwuGN-1562814200346)(https://s2.ax1x.com/2019/07/10/Zcdzc9.png)]
- 什么是间隙锁
-
面试题：常考如何锁定一行
- select xxx… for update锁定某一行后，其它的操作会被阻塞，直到锁定行的会话提交commit
begin; select * from test_innodb_lock where a=8 for update; commit; -
-
案例结论
- InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差
-
行锁分析
- 如何分析行锁定
- 通过检查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:系统启动后到现在总共等待的次数
- 比较重要的是2，3，5
- 尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划
-
- 如何分析行锁定
-
优化建议
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能较少检索条件，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能低级别事务隔离
-
-
页锁
- 开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般
五、主从复制
1、复制的基本原理
-
slave会从master读取binlog来进行数据同步
-
三步骤+原理图
[外链图片转存失败(img-zDcreuRx-1562814200347)(https://s2.ax1x.com/2019/07/10/ZcLDI0.png)]
- MySQL的复制过程分三步
- master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events;
- slave将master的binary log events拷贝到它的中继日志（relay log）；
- slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的
- MySQL的复制过程分三步
2、复制的基本原则
- 每个slave只有一个master
- 每个slave只能有一个唯一的服务器ID
- 每个master可以有多个slave
3、复制的最大问题
- 延时
4、一主一从常见配置
-
mysql版本一致且后台以服务运行
-
主从都配置在【mysqld】结点下，都是小写
-
主机（windows）修改my.ini配置文件
-
- 【必须】主服务器唯一ID
- server-id = 1
- 【必须】启动二进制日志
- log-bin=自己本地的路径/mysqlbin
- log-bin=d:/devSoft/MySQLServer5.5/data/mysqlbin
- 【可选】启用错误日志
- log-err=自己本地的路径/mysqlerr
- log-err=d:/devSoft/MySQLServer5.5/data/mysqlerr
- 【可选】根目录
- basedir=“自己本地路径”
- basedir=“d:/devSoft/MySQLServer5.5/”
- 【可选】临时目录
- tmpdir=“自己本地路径”
- tmpdir=“d:/devSoft/MySQLServer5.5/”
- 【可选】数据目录
- datadir=“自己本地路径/Data”
- datadir=d:/devSoft/MySQLServer5.5/Data
- read-only = 0
- 主机，读写都可以
- 【可选】设置不要复制的数据库
- binlog-ignore-db=mysql
- 【可选】设置需要复制的数据库
- binlog-do-db=需要复制的主数据库名字
- 【必须】主服务器唯一ID
-
-
从机（linux）修改my.cnf配置文件
- 【必须】从服务器唯一ID
- 【可选】启用二进制日志
-
因修改过配置文件，请主机+从机都重启后台mysql服务
-
主机从机都关闭防火墙
- windows手动关闭
- 关闭虚拟机linux防火墙 service iptables stop
-
在windows主机上建立账户并授权slave
- GRANT REPLICATION SLAVE ON *.* TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’
- flush-privileges;
- 查询master的状态
- show master status;
- 记录下File和Position的值
- 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
-
在linux从机上配置需要复制的主机
- CHANGE MASTER TO MASTER_HOST=‘主机IP’,MASTER_USER=‘zhangsan’,MASTER_PASSWORD=‘123456’,MASTER_LOG_FILE=‘File名字’,MASTER_LOG_POS=Position数字；
- 启动从服务器复制功能
- start slave;
- show slave status/G
- 下面两个参数都是Yes，则说明主从配置成功！
- Slave_IO_Running:Yes
- Slave_SQL_Running:Yes
- 下面两个参数都是Yes，则说明主从配置成功！
-
主机新建库、新建表、insert记录，从机复制
-
如何停止从服务复制功能
- stop slave;
err
4. 【可选】根目录
+ basedir=“自己本地路径”
+ basedir=“d:/devSoft/MySQLServer5.5/”
5. 【可选】临时目录
+ tmpdir=“自己本地路径”
+ tmpdir=“d:/devSoft/MySQLServer5.5/”
6. 【可选】数据目录
+ datadir=“自己本地路径/Data”
+ datadir=d:/devSoft/MySQLServer5.5/Data
7. read-only = 0
+ 主机，读写都可以
8. 【可选】设置不要复制的数据库
+ binlog-ignore-db=mysql
9. 【可选】设置需要复制的数据库
+ binlog-do-db=需要复制的主数据库名字
-
从机（linux）修改my.cnf配置文件
- 【必须】从服务器唯一ID
- 【可选】启用二进制日志
-
因修改过配置文件，请主机+从机都重启后台mysql服务
-
主机从机都关闭防火墙
- windows手动关闭
- 关闭虚拟机linux防火墙 service iptables stop
-
在windows主机上建立账户并授权slave
- GRANT REPLICATION SLAVE ON *.* TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’
- flush-privileges;
- 查询master的状态
- show master status;
- 记录下File和Position的值
- 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化
-
在linux从机上配置需要复制的主机
- CHANGE MASTER TO MASTER_HOST=‘主机IP’,MASTER_USER=‘zhangsan’,MASTER_PASSWORD=‘123456’,MASTER_LOG_FILE=‘File名字’,MASTER_LOG_POS=Position数字；
- 启动从服务器复制功能
- start slave;
- show slave status/G
- 下面两个参数都是Yes，则说明主从配置成功！
- Slave_IO_Running:Yes
- Slave_SQL_Running:Yes
- 下面两个参数都是Yes，则说明主从配置成功！
-
主机新建库、新建表、insert记录，从机复制
-
如何停止从服务复制功能
- stop slave;
1555
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
