mysql高级

github笔记
 csdn笔记
垂直拆分（分库），水平拆分（分表）

一、 linux

var 目录，变量配置文件
var/lib/mysql/ mysql数据库文件的存放路径
没有提示即可
rpm -qa|grep -i mysql 查看是否按照mysql 。
rpm -ivh mysql.rpm 查看安装进度
chkconfig mysql on 设置开机自启动
ntsysv 查看开机启动项（*为开机启动项）
top 查看开机时间

二、B+树

叶子节点有指针（顺序），叶子节点包含所有的元素，一个节点里面可以有多个元素
（B+树只有叶子节点才存储数据）

B-树（B树）：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；
B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；

为什么使用B Tree（B+Tree）
红黑树也可用来实现索引，但是文件系统及数据库系统普遍采用B/+Tree,为什么？

一般来说，索引本身也很大，不可能全存内存，往往以索引文件的形式存在磁盘
　 (1)单节点能存储更多数据，使得磁盘IO次数更少。
　 (2)叶子节点形成有序链表，便于执行范围操作。
　 (3)聚集索引中，叶子节点的data直接包含数据；非聚集索引中，叶子节点存储数据地址的指针。

InnoDB插入时会按主键排序,提高查询效率
explain + sql语句，查看是否使用索引。性能分析
字段操作（包括类型转换），不使用索引。where a=‘a’（a建立索引，int类型。类型转换后相当于a=0，数字字符可转换为对应数字，其它字符只能转换为0）

三、mysql分层

1.连接层
2.服务层
3.引擎层
innoDB支持事务，外键，行锁，缓存（不仅缓存索引，也缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响），表空间大，关注点事务
myISM，表锁（不适合高并发），缓存（只缓存索引，不缓存真实数据），表空间小，关注点性能（偏重读）

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

4.存储层

四、查询

7种join

image.png

oracle的full outer join
SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key
UNION
SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key;

五、索引

MySQL官方对索引的定义为：索引（INDEX）是帮助MySQL高效获取数据的数据结构。
从而可以获得索引的本质：索引是排好序的快速查找数据结构（where，order by）
300万以上一般需要索引

查找和排序

范围索引会使联合索引失效

5.1 索引数据结构：

BTree索引。
Hash索引。
Full-text全文索引。
R-Tree索引。

5.2.哪些情况需要建索引

主键自动建立主键索引（唯一 + 非空）。
频繁作为查询条件的字段应该创建索引。
查询中与其他表关联的字段，外键关系建立索引。
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。（order by字段顺序很重要）
查询中统计或者分组字段（group by也和索引有关）。

高并发下倾向组合索引

5.3 哪些情况不要建索引

记录太少的表。
经常增删改的表。
频繁更新的字段不适合创建索引。
Where条件里用不到的字段不创建索引。
数据重复且分布平均的表字段，应该只为最经常查询和排序的字段建立索引。（如果某个数据列包含许多重复的内容，为它建立索引就没有太大效果）
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

5.4、聚集索引和非聚集索引

聚集索引和非聚集索引使用的都是B+树结构。

1、非聚集索引
非聚集索引的叶子节点为索引节点，但是有一个指针指向数据节点。

MyISAM是非聚集索引。

2、聚集索引
聚集索引叶子节点就是数据节点。

关于聚集索引，innodb会按照如下规则进行处理：
　　1，如果一个主键被定义了，那么这个主键就是作为聚集索引
　　2，如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引
　　3，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键是一个6个字节的列，该列的值会随着数据的插入自增。

innodb的普通索引，唯一索引，联合索引都是辅助索引，采用非聚集索引结构。InnoDB的所有辅助索引都引用主键作为data域。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

六、性能分析

mysql常见瓶颈
cpu，io，服务器硬件（top，free，iostat，vmstat查看）

6.1.EXPLAIN能干嘛？

可以查看以下信息：

id：表的读取顺序。
select_type：数据读取操作的操作类型。
possible_keys：哪些索引可以使用。
key：哪些索引被实际使用。
ref：表之间的引用。
rows：每张表有多少行被优化器查询。越小越好

id，type，key，rows，extra最重要
\G，竖版显示

6.2.EXPLAIN字段

id 加载顺序

id：表的读取和加载顺序。

值有以下三种情况：
id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
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。

type

type：访问类型排列。索引级别

从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL。除了ALL没有用到索引，其他级别都用到索引了。
(system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All)
一般来说，得保证查询至少达到range级别，最好达到ref。

const：表示通过索引一次就找到了，const用于比较primary key或则unique索引。单表
eq_ref：联表。const是通过索引一次就找到唯一一条数据.eq是连表查找,(..对于每个索引键..),说明访问的就不止一个索引列,需要访问多个索引,但是值还是唯一的.相同点是值唯一,不同点是访问的索引列数量不同. const只有一条记录，eq-ref是可能多条，但是A表的外健对应B表中只有一条记录
index：Full Index Scan，全索引扫描，index和ALL的区别为index类型只遍历索引树。也就是说虽然ALL和index都是读全表，但是index是从索引中读的，ALL是从磁盘中读取的。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

Extra

Extra：包含不适合在其他列中显示但十分重要的额外信息。
最重要的字段：Using filesort（较差），Using temporary（很差），using index（好）

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为"文件内排序"。
(order by字段要和组合索引中的字段和顺序相同，否则 )
Using temporary：使用了临时表保存中间结果，MySQL在対查询结果排序时使用了临时表。常见于排序order by和分组查询group by。临时表対系统性能损耗很大。(where...in与group by字段要和组合索引中的字段和顺序相同，否则 )
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作

七、索引失效

单表索引优化：
建立复合索引时，范围参数不建索引，否则索引会失效
索引两表优化：
左连接时，索引建在右表。右连接，建左表
索引三表优化：
从表建立索引

1.JOIN语句的优化：

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

2.题目

/* 创建复合索引 */
CREATE INDEX idx_test03_c1234 ON `test03`(`c1`,`c2`,`c3`,`c4`);

/* 1.用到索引c1 c2 c3 c4全字段 MySQL的查询优化器会优化SQL语句的顺序*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` > 'a4' AND `c3` = 'a3';
/* 2.用到了索引c1 c2 c3三个字段，c1和c2两个字段用于查找, c3字段用于排序了但是没有统计到key_len中*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c3`;
  /* 3.用到了索引c1 c2 c3三个字段, c1和c2两个字段用于查找,  c3字段用于排序了但是没有统计到key_len中，c4字段失效
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c4` = 'a4' ORDER BY `c3`;
/* 
   4.用到了索引c1 c2两个字段，c4失效，c1和c2两个字段用于查找，c4字段排序产生了Using filesort说明排序没有用到c4字段 
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' ORDER BY `c4`;
/* 5.1 用到了索引c1 c2 c3三个字段，c1用于查找，c2和c3用于排序.没有出现filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c2`, `c3`;
/* 5.2.用到了c1一个字段，c1用于查找，c3和c2两个字段索引失效，产生了Using filesort */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c5` = 'a5' ORDER BY `c3`, `c2`;
/* 
   5.3. 用到了c1 c2 c3三个字段，c1 c2用于查找，c2 c3用于排序 没有产生Using filesort 
      因为之前c2这个字段已经确定了是'a2'了，这是一个常量，再去ORDER BY c3,c2 这时候c2已经不用排序了！
      所以没有产生Using filesort .和(5.2)进行对比学习！
*/
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c2` = 'a2' AND `c5` = 'a5' ORDER BY c3, c2;


/* GROUP BY 表面上是叫做分组，但是分组之前必定排序。 */

/* 14.用到c1 c2 c3三个字段，c1用于查找，c2 c3用于排序，c4失效 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c2`,`c3`;

/* 15.用到c1这一个字段，c4失效，c2和c3排序失效产生了Using temporary；Using filesort，性能下降厉害 */
EXPLAIN SELECT * FROM `test03` WHERE `c1` = 'a1' AND `c4` = 'a4' GROUP BY `c3`,`c2`;

定值、范围还是排序，一般order by是给个范围
group by基本上都需要进行排序，顺序不对会有临时表产生

3.索引优化的一般性建议：

对于单值索引，尽量选择针对当前query过滤性更好的索引。
在选择复合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择复合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

利用覆盖索引解决两边%的优化问题,同时不写星

kk%,前面是定值，可以用到索引，不影响后面字段
%kk,使用不到索引

优化总结口诀：
全值匹配我最爱（<ref项的const字段>无关顺序），最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VARCHAR引号不可丢，SQL高级也不难！

八、分析慢SQL的步骤

分析：

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

总结（大纲）：

1、慢查询的开启并捕获。
2、explain + 慢SQL分析。
3、show Profile查询SQL在MySQL数据库中的执行细节和生命周期情况。
4、MySQL数据库服务器的参数调优。

九、查询优化

1.小表驱动大表

优化原则：对于MySQL数据库而言，永远都是小表驱动大表。

/**
* 举个例子：可以使用嵌套的for循环来理解小表驱动大表。
* 以下两个循环结果都是一样的，但是对于MySQL来说不一样，
* 第一种可以理解为，和MySQL建立5次连接每次查询1000次。
* 第一种可以理解为，和MySQL建立1000次连接每次查询5次。
*/
for(int i = 1; i <= 5; i ++){
    for(int j = 1; j <= 1000; j++){
        
    }
}
// ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
for(int i = 1; i <= 1000; i ++){
    for(int j = 1; j <= 5; j++){
        
    }
}

IN和EXISTS

/* IN适合B表比A表数据小的情况*/
SELECT * FROM A WHERE id IN (SELECT id FROM B)

/* EXISTS适合B表比A表数据大的情况 */
SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id);
EXISTS：

语法：SELECT....FROM tab WHERE EXISTS(subquery);该语法可以理解为：
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或是false）来决定主查询的数据结果是否得以保留。
提示：

EXISTS(subquery)子查询只返回true或者false，因此子查询中的SELECT *可以是SELECT 1 OR SELECT X，它们并没有区别。
EXISTS(subquery)子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
EXISTS(subquery)子查询往往也可以用条件表达式，其他子查询或者JOIN替代，何种最优需要具体问题具体分析。

2.order by优化

ORDER BY子句，尽量使用索引排序，避免使用Using filesort排序。

MySQL支持两种方式的排序，FileSort和Index，Index的效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

ORDER BY语句使用索引最左前缀。
使用WHERE子句与ORDER BY子句条件列组合满足索引最左前缀。

结论：尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀原则。

如果不在索引列上，File Sort有两种算法：MySQL就要启动双路排序算法和单路排序算法

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

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在MySQL4.1之后，出现了改进的算法，就是单路排序算法。

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

由于单路排序算法是后出的，总体而言效率好过双路排序算法。

但是单路排序算法有问题：如果SortBuffer缓冲区太小，导致从磁盘中读取所有的列不能完全保存在SortBuffer缓冲区中，这时候单路复用算法就会出现问题，反而性能不如双路复用算法。

单路复用算法的优化策略：

增大sort_buffer_size参数的设置。
增大max_length_for_sort_data参数的设置。

提高ORDER BY排序的速度：

ORDER BY时使用SELECT *是大忌，查什么字段就写什么字段，这点非常重要。在这里的影响是：
- 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会使用单路排序算法，否则使用多路排序算法。
- 两种排序算法的数据都有可能超出sort_buffer缓冲区的容量，超出之后，会创建tmp临时文件进行合并排序，导致多次IO，但是单路排序算法的风险会更大一些，所以要增大sort_buffer_size参数的设置。
尝试提高sort_buffer_size：不管使用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。
尝试提高max_length_for_sort_data：提高这个参数，会增加用单路排序算法的概率。但是如果设置的太高，数据总容量sort_buffer_size的概率就增大，明显症状是高的磁盘IO活动和低的处理器使用率。

3.GORUP BY优化

GROUP BY实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列时，会使用Using filesort进行排序，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置，会提高性能。
WHERE执行顺序高于HAVING，能写在WHERE限定条件里的就不要写在HAVING中了。

4.总结

为排序使用索引

MySQL两种排序方式：Using filesort和Index扫描有序索引排序。
MySQL能为排序与查询使用相同的索引，创建的索引既可以用于排序也可以用于查询。

十、慢查询日志

10.1 查看慢查询日志是否开以及如何开启

查看慢查询日志是否开启：SHOW VARIABLES LIKE '%slow_query_log%';。

开启慢查询日志：SET GLOBAL slow_query_log = 1;。使用该方法开启MySQL的慢查询日志只对当前数据库生效，如果MySQL重启后会失效。

查看当前多少秒算慢：SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阈值时间：set global long_query_time=3;
为什么设置后看不出变化（设置3之后，查询依然显示10）：
show global variables like 'long_query_time';

需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIKE 'long_query_time%';

查询当前系统中有多少条慢查询记录：

show global status like '%Slow_queries%';

配置版

image.png

10.2 日志分析工具mysqldumpslow

如何在windows下使用mysqldumpslow命令

11.Show Profile

Show Profile是什么？

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