大家好，我是讯享网，很高兴认识大家。

一：Mysql迪卡尔集

：Mysql连接完后对应的表就拥有对应表的字段。
left join : 取A的所有面积+B和A共有的部分 以A为基础 ,A所有数据都展示，A有的B没有的展示空
select from table A left join table B on A.key=B.key

FULL outer join:取AB的所有面积-共有部分
select from table A FULL outer join table B on A.key=B.key
where A.key is null or B.key is null

union ：合并（并且去重）实现全连接
select from table A left join table B on A.key=B.key
union
select from table A right join table B on A.key=B.key

二：索引简介

索引是帮助mysql高效获取数据的数据结构。本质上来说：索引是数据结构，目的在于提高查找效率。
索引：排好序的快速查找数据结构，用于排序和快速查找。
索引会影响到sql的where （查找）和order by（排序）。
索引的优势：提高数据检索效率，降低排序的成本。
数据都是存储到硬盘上。
mysql索引结构：BTree索引、Hash索引、full-text全文索引、R-Tree索引（主要是B树索引）
创建索引的情况：
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题（在高并发下倾向创建组合索引）
7.查询中排序（order by）的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段（group by）
不创建索引的情况：
1.表记录太少
2.经常增删改的表：因为降低更新速度，而且还要保存索引文件
3.如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
性能分析：
MSQL Query optimizer：mysql自带查询优化器。

MSQL常见瓶颈：
1.CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。
2.IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
3.服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

三：explian使用以及其组成

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理你的sql。
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
id:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。
id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被优先执行
id相同不同，同时存在。（先走大的再走顺序执行）

select_type:
1.simple：简单的select查询，查询中不包含子查询或者union
2.primary：查询中若包含任何复杂的子部分，最外层查询则被标记为（相当于最后加载的）
3.subquery：在select或where列表中包含了子查询
4.derived：在from列表中包含的子查询被标记为derived（衍生）mysql会递归执行这些子查询，吧结果放在临时表里。
5.union：若第二个select出现在union之后，则被标记为union；
若union包含在from子句的子查询中，外层select将被标记为derived
6.union result:从union表获取结果的select

type：
从最好到最差依次如下：
system>const>eq_ref>ref>range>index>all
一般来说，得保证查询至少达到range级别，最好能达到ref
system：表只有一行记录，这是const类型的特例，平时包含出现。
const：表示通过索引一次就找到了，const用于比较primary key或者union索引。因为只匹配一行数据，所以很快
如将主键置于where列表中，mysql就能将该查询转换为一个常量
eq_ref:唯一索引扫描，对每个索引建，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between,>,<，in等查询
index：full index scan，index 与all区别为index类型只遍历索引树，虽然都是读全表，但是index从索引读取，而all是从硬盘读取
all：将遍历全表。

possible_keys：显示可能应用在这张表中的索引，一个或多个。但不一定被查询实际使用

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

ref：显示索引的哪一列被使用，如果可能的话，是一个常数。哪些列或常用被用于查找索引列上的值。

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

四：索引优化

Extra：包含不适合在其他列中显示但十分重要的额外信息。
1.using filesort（九死一生）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
mysql中无法利用索引完成的排序操作被称为‘'文件排序’
2.using temporary（十死无生）：使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表
。常见于排序order by和分组group by。
3.using index()：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率不错
如果同时出现using where，表面索引被用来执行索引建值得查找；
如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。
4using where：表面用了where过滤
5.using join buffer:使用了连接缓存
6.impossible where:where的值总是false，不能用来获取任何元组。
两表索引优化：
左右连接都是相反加索引，这样才效果最好（例如左连接，则给右表加索引）
select from table A left join table B on A.key=B.key
Alter Tbale ‘B’add index b(‘b.key’)//给表B添加索引名为b的索引，索引字段是b.key
drop index b on table B//删除索引

三表索引优化：（最重要的一点就是建立覆盖索引后，顺序不能乱1-2-3-4）
也是建立两个索引值，如果左连接连个表，则右边的两个表都加上索引这样能使性能得到优化。
索引优化（索引失效）：
1.**左前缀原则：如果索引了多列，要遵循最左前缀原则，
指的是查询从索引的最左前列开始并且不跳过索引中的列（
如果是AC直接这样的话，只有部分索引生效了），如果都是常量的话就没影响。
(就是建立了复合索引，第一个索引不能没有，否则后面的会失效)比如（A,B,C）where过滤的条件不能没有A直接跳过A的话后面的索引会失效
2.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
3.存储引擎不能使用索引中范围条件右边的列。
4.尽量使用覆盖索引，减少select*。
5.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描。
6.is null,is not null 也无法使用索引。
7.like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。（百分%加右边)
解决like’%字符串%'时索引不被使用的方法？
使用覆盖索引。
8.字符串不加单引号索引失效。
9.少用or，用它来连接时会索引失效。

查找和排序最好按照覆盖索引的顺序来，不然容易出现using filesort（index_test_1_2_3_4）
比如覆盖索引为1-2-3-4
order by 3，2就会出现，如果用2，3就不会
有个特例的话，就是where 索引用的时候排序字段没有where里面的字段
比如，where c2= order by c3，c2 就不会出现using filesort
如果 where c1 order by c3,c2就会出现using filesort了，因为where 里面没有c3，c2字段

五：group by 使用及优化

如果是group by和order by 出现一样的情况下 ，则会最槽糕

假设index（a，b,c）
where 语句 索引是否被使用
where a=3 Y，使用到 a
where a=3，and b =5 Y，使用到a,b
where a=3 and b=5 and c=4 Y，使用到a,b,c
where b=3 或者 where b=3 and c=4 where c =4 N
where a=3 and c=5 使用到a,但是c不可以，b中间断了
where a=3 and b>4 and c=5 使用到 a和b，c不能用在范围之后，b断了
where a=3 and b like ‘kk%’ and c=4 Y,使用到 a，b，c
where a=a=3 and b like ‘%kk’ and c=4 Y，只用到a
where a=a=3 and b like ‘%kk%’ and c=4 Y，只用到a
where a=a=3 and b like ‘k%kk%’ and c=4 Y，使用到a,b,c

------分析-----
1.观察，至少跑一天，看看生产的慢sql情况。
2.开启慢查询日志，设置阙值，比如超过5秒钟的就是慢sql，并将它抓取出来
3.explain+慢sql分析
4.show profile
5.运维经理OR DBA，进行sql数据库服务器的参数调优。

==总结
1.慢查询的开启并捕获
2.explain +慢sql分析
3.show profile查询sql 在mysql服务器里面的执行细节和生命周期情况
4.sql数据库服务的参数调优。

exists
select from table where exists（子查询语句）
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的数据结果时是否得以保留

2.exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，克进行实际校验以确定是否有效率问题

3.exists子查询往往可以用条件表达式、其他子查询或者join来替代，何种最有需要具体问题具体分析

order by 子句，尽量使用index方式排序，避免filesort方式排序

oreder by子句依旧遵循**左前缀原则

order by如果不在索引列上，filesort有两种算法：
mysql就要启动双路排序和单路排序
双路排序：mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据（两次读取）
单路排序：mysql4.1之后，出现了第二种改进的算法，就是单路算法
从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，并且把随机IO变成了顺序IO
但是他会使用更多的空间（一次读取）

为排序使用索引：
mysql两种排序小方式：文件排序或扫描有序索引排序
mysql能为排序与查询使用相同的索引
order by能使用索引最左前缀key_a_b_c
order by a
order by a,b
order by a,b,c
order by a desc ,b desc ,c des

不能使用索引进行排序
order by a ASC b，DESC，c DESC 排序不一样
where g order by b，c 丢失索引 a

group by 实际是先排序后进行分组，遵循索引建的**左前缀

六：慢查询日志

是什么：
mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应实际超过阙值得语句，具体指运行实际超过
long_query_time值得sql，则会被记录到慢查询日志中。

怎么做：
默认情况下，mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数，如果不是调优一般默认关闭不然
影响性能

查看是否开启及如何开启：
默认查看-show variables like ‘%slow_query_log%’;
开启慢查询日志-set global slow_query_log=1;

查询当前系统有多少条慢查询sqlshow global status like ‘%slow_queries%’

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数

show variables like ‘log_bin_trust_function_creators’;

讯享网

set global log_bin_trust_function_creators=1;

设置参数log_bin_trust_function_creators

show profile
是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量
默认情况下，参数处于关闭状态，并保存最后的15次结果

查看是否开启show variables like ‘profiing’;

开启set profiling =on

查看结果show profile；

诊断sql，cpu

set global log_output=‘table’

七：mysql锁

锁是计算机协调多个进程或县城并发访问某一资源的机制

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

释放表：unlock tables;

session1 — ------------------------------ session2
对表加表锁（偏读，共享锁）— ------------------------------连接
当前session可以查询该记录— ------------------------------该session也可以连接该表的记录
当前session不能查询其他没有锁定的表— ------------------------------其他session可以查询或者更新未锁定的表
当前session中插入或更新锁定的表都会提示错误— ------------------------------其他session插入或者更新锁定表会一直等待获得锁

行锁（偏写）

session1 — ------------------------------ session2
对表加写锁 — ------------------------------ 连接
当前session对锁定的表查询、更新、插入都可以执行 — ------------------------------ 其他session对表锁定的查询被阻塞，需要等待锁被释放

MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改查操作前，会自动给涉及的表加
写锁
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）

结论：
结合上表，所以对MyISAM表进行操作，会有以下情况：
1.对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会组设对同一表的
写请求。只有当读锁释放后，才会执行其他进程的写操作。
2.对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写释放后，才会执行其他进程的读写操作
MYSQL的读写锁有啥区别？
读锁会阻塞写（），但是不会阻塞读，而写锁则会把读和写都阻塞

如何分析表锁定
可以通过检查tabl_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
sql：show status like ‘table%’
table_locks_immediate:产生标记锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1；
table_locks_waited:出现表级锁定争用而发生等待的次数，此值高则说明存在着较严重的标记锁争用情况；

MyISAM的读写锁调度是写优先，这容易导致别人被阻塞

行锁（支持事务）：偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁粒度最小，发生锁冲突的概率最低，并发度也最高

八：mysql事务

事务：是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性简称ACID属性，就比如转账一次就是一次事务
原子性：
一致性：
隔离性：
持久性

并发事务：
脏读、不可重复读、幻读
事务的隔离级别：
未提交读、已提交读、可重复读（mysql默认的），可序列化

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

页锁

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

InnodB也会对这个间隙加锁，这种机制叫做间隙锁

危害：
因为query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个
键值并不存在
间隙锁有一个比较知名的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜
的锁定，而造成在锁定的时候无法插入锁定键值范围内的数据。

如何锁定一行？
begin；
select * from table where id=？ for update 锁定某行就选哪一行。

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_time_avg:每次等待说花平均时间
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits :系统启动后到现在总共等待的次数；
比较重要的是：
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_waits :系统启动后到现在总共等待的次数；
Innodb_row_lock_time_avg:每次等待说花平均时间

行锁优化建议：
1.尽可能让所有数据检索都通过索引来完成，避免无索引引行锁升级为表锁
2.合理涉及索引，尽量缩小锁的范围
3.尽可能较少检索条件，避免间隙锁，如范围检索条件
4.尽量控制事务大小，减少锁定资源量和时间长度
5.尽可能低级别事务隔离

九：MySql主从复制

复制的基本原理：slave会从master读取binlog来进行数据同步
MySQL复制过程分成三步：
1.master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2.slave将master的binary log events拷贝到它的中继日志（relay log）
3.salve 重做中继日志中的事件，将改变应用到自己的数据库，MYSQL复制是异步且串行化的。

复制的基本原则：
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个Master可以有多个slave

一主一从常见配置：
1.mysql版本一致且后台以服务运行
2.主从都配置在mysqld节点下，都是小写
3.主机修改my.ini配置文件
必须项：
从服务器唯一id：server-id=1；
启用二进制文件：
log-bin=自己本地的路径/mysqlbin
可选：
启用错误日志
配置根路径
临时目录
数据目录
read-only=0主机读写都可以
设置需要复制的数据库
设置不需要复制的数据库

4.从机修改my.cnf配置文件
必须：从服务器唯一id（不能和主机一样）
可选:启用二进制文件

5.因为修改过配置文件，主机+从机都重启mysql服务器
service mysql stop
service mysql start
主机从机都关闭防火墙：
service iptables stop

6.在windows主机上建立账户并授权slave
GRANT REPLICATION SLAVE ON . TO ‘zhangsan’‘@’从机的ip‘
IDENTIFIED BY’’

flush privileges；刷新命令
查询master的状态：
show master status；
记录下File和Position的值

7.在从机上配置需要复制的主机
CHANGE MASTER TO MASETR_HOST=‘主机ip’,
MASTER_USER=‘zhangsan’
MASTER_PASSWORD='’，
MASTER_LOG_FILE=‘刚才主机记录的file’，MASTER_LOG_POS=341;

start slave：启动从服务器复制功能

show slave status\G
下面两个参数都是YES，则说明主从配置成功！
Slave_IO_Running:YES
Slave_SQL_Running:YES

8.主机新建库、新建表、insert记录，从机复制

9.stop slave 停止从服务复制功能