& Mysql高级总结

1 mysql架构介绍

1.1 Mysql简介

1.2 MysqlLinux版本的安装--mysql5.5

1.3 Mysql配置文件

mysql --help | grep 'Default options' -A 1
linux查看配置文件位置

linux 默认位置为/etc/my.cnf文件

1.3.1 log-bin 二进制日志

主重复制

1.3.2 log-error 错误日志

默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等.

1.3.3 log 查询日志

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

1.3.4 数据文件

数据库位置
- windows
  - 比如：D:ProgramFilesMySQLMySQLServer5.5data目录下可以挑选很多库(自己建的数据库)
- linux
  - 默认路径：/var/lib/mysql
  - 查看自定义路径位置
  - ls -lF | grep ^d 这个命令就是表示在ls的命令结果中查找以字母d开头的行。其结果就是找出ls结果中的目录文件。
frm文件
- 存放表结构
myd文件
- 存放表数据
myi文件
- 存放表索引

1.4 Mysql逻辑架构介绍

1.5 Mysql存储引擎

查看

对比

2 索引优化分析

2.1 常见通用的join查询

2.2 SQL执行顺序

手写

机读（mysql 是从FROM开始读）

小总结

JOIN神图

2.2 索引简介

2.2.1 对索引的概述

MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。

可以得到索引的本质：索引是数据结构

索引的目的在于提高查询效率

你可以简单理解为"排好序的快速查找数据结构"。

结论
数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上

我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，

复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

2.2.2 索引优势

类似大学图书馆建书目索引，提高数据检索效率，降低数据库的IO成本

通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

2.2.3 索引劣势

1实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的

2虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。

因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，

都会调整因为更新所带来的键值变化后的索引信息

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

2.2.4 mysql索引分类及基本语法

单值索引
- 即一个索引只包含单个列，一个表可以有多个单列索引
- 建议一张表索引不要超过5个
- 优先考虑复合索引
唯一索引
- 索引列的值必须唯一，但允许有空值
复合索引
- 即一个索引包含多个列
基本语法:
- 创建
  - CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
    - 如果是CHAR,VARCHAR类型，length可以小于字段实际长度；
    - 如果是BLOB和TEXT类型，必须指定length。
  - ALTER mytable ADD [UNIQUE] INDEX [indexName] ON(columnname(length));
- 删除
  - DROP INDEX [indexName] ON mytable;
- 查看
  - SHOW INDEX FROM table_nameG
- 使用ALTER命令如下：

2.2.5 mysql索引结构

BTree索引（掌握）

Hash索引（了解）

full-text全文索引（了解）

R-Tree索引（了解）

2.2.6 哪些情况需要创建索引

1.主键自动建立唯一索引

2.频繁作为查询的条件的字段应该创建索引

3.查询中与其他表关联的字段，外键关系建立索引

4.频繁更新的字段不适合创建索引
因为每次更新不单单是更新了记录还会更新索引，加重IO负担

5.Where条件里用不到的字段不创建索引

6.单间/组合索引的选择问题，who？（在高并发下倾向创建组合索引）

7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度

8.查询中统计或者分组字段

2.2.7 哪些情况不要创建索引

1.表记录太少

2.经常增删改的表

3.数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。

注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

2.3 性能分析

2.3.1 MySQL常见瓶颈

CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候

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

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

2.3.2 Explain

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

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

Explain+SQL语句
执行Explain包含的信息

Explain 中 id type key rows Extra 是最重要字段属性！！！

2.3.2.1 Explain-> id

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

三种情况
id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

2.3.2.2 Explain-> select_type

查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询
1.SIMPLE
- 简单的select查询，查询中不包含子查询或者UNION
2.PRIMARY
- 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
3.SUBQUERY
- 在SELECT或者WHERE列表中包含了子查询
4.DERIVED
- 在FROM列表中包含的子查询被标记为DERIVED（衍生）
- MySQL会递归执行这些子查询，把结果放在临时表里。
5.UNION
- 若第二个SELECT出现在UNION之后，则被标记为UNION;
- 若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
6.UNION RESULT
- 从UNION表获取结果的SELECT

2.3.2.3 Explain-> table

显示这一行的数据是关于哪张表的

2.3.2.4 Explain-> type

显示查询使用了何种类型

从最好到最差依次是：

system>const>eq_ref>ref>range>index>ALL

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

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
FullTable Scan,将遍历全表以找到匹配的行

一般来说，得保证查询只是达到range级别，最好达到ref

2.3.2.5 Explain-> possible_keys

显示可能应用在这张表中的索引,一个或多个。

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

2.3.2.6 Explain-> key

实际使用的索引。如果为null则没有使用索引
查询中若使用了覆盖索引，则索引和查询的select字段一致
示例：

2.3.2.7 Explain-> key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

2.3.2.8 Explain-> ref

显示索引那一列被使用了，如果可能的话，是一个常量。那些列或常量被用于查找索引列上的值

2.3.2.9 Explain-> rows

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

越小越好

2.3.2.10 Explain-> Extra

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

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

Using temporary （重要）（点臭要优化）
使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by

USING index （重要）（点赞）

表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！

如果同时出现using where，表明索引被用来执行索引键值的查找；

如果没有同时出现using where，表面索引用来读取数据而非执行查找动作。

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，在找到第一匹配的元组后即停止找同样值的工作

2.3.2.11 小练习

2.4 索引优化

2.4.1 索引分析

单表

两表

join特性
left join 右表建立索引
right join 坐标建立索引

三表

2.4.2 索引失效（应该避免）

2.4.2.1 索引失效（重点）

全值匹配我最爱
最佳左前缀法则
1. 如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3. 假设表user有字段为id，u1，u2，u3，u4
4. 为u1-4字段创建索引 create index index_u1234 on user(u1,u2,u3,u4);
5. u1 查询：select * from user where u1 = 'xx';---->索引不失效, 索引 u1 用到了
6. u2 查询：select * from user where u2 = 'xx';---->索引失效, 带头大哥不能死
7. u1 u3 查询：select * from user where u1 = 'xx' and u3 = 'xx';---->索引失效, 中间兄弟不能断
8. u1 u2 u3 u4 查询：select * from user where u1 = 'xx' and u2 = 'xx' and u3 = 'xx' and u4 = 'xx';---->索引不失效, 索引全用到了
9. u1 u2 u4 u3 查询：select * from user where u1 = 'xx' and u2 = 'xx' and u4 = 'xx' and u3 = 'xx';---->索引不失效, 索引全用到了
10. u4 u3 u2 u1 查询：select * from user where u4 = 'xx' and u3 = 'xx' and u2 = 'xx' and u1 = 'xx';---->索引不失效, 索引全用到了
11. 为什么9和10中的索引不失效？
12. 因为mysql中的查询优化器，会把我们的sql进行调整和优化，已达到最佳的效果。
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列，即范围之后的查询列索引全失效；
1. 假设表user有字段为id，u1，u2，u3，u4
2. 为u1-4字段创建索引 create index index_u1234 on user(u1,u2,u3,u4);
3. 无范围查询：select * from user where u1 = 'xx' and u2 = 'xx' and u3 = 'xx' and u4 = 'xx';---->索引不失效, 索引全用到了
4. 范围查询：select * from user where u1 = 'xx' and u2 = 'xx' and u3 > 'xx' and u4 = 'xx';---->u1 u2 u3 索引没失效，但u4索引失效因此范围之后的查询列索引全失效
5. 其他例子
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select*
mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描操作
1. like KK%相当于=常量 %KK和%KK% 相当于范围所以会失效
2. 如何解决like '%字符串%' 时索引失效？
  1. 可以使用主键索引
  2. 使用覆盖索引，即查询字段必须是建立复合索引的字段或者主键索引
  3. 当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！
  4. 详情可翻阅本目录下《解决like ’%str’ 时索引不被使用》
字符串不加单引号索引失效
1. 假设表user有一个字段为name
2. 为name字段创建索引 create index index_n on user(name);
3. 加单引号查询：select * from user where name = 'uu';---->索引不失效
4. 不加单引号查询：select * from user where name = uu;---->索引失效
5. 原因：varchar类型如果不加单引号，mysql底层会进行隐式的类型转换，从而导致索引失效
少用or,用它连接时会索引失效
总结

like KK%相当于=常量 %KK和%KK% 相当于范围

2.4.2.2 索引面试题讲解

2.4.3 一般性建议

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

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

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

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

3 查询截取分析

永远小表驱动大表
永远小表驱动大表
永远小表驱动大表

3.1 查询优化

优化原则
永远小表驱动大表
永远小表驱动大表
永远小表驱动大表

3.1.1 exists 和 in 的优化

优化原则：小表驱动大表，即小的数据集驱动大的数据集

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

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

当A表的数据集一定小于B表的数据集的时候，用exists优于in

EXISTS
- SELECT ... FROM table WHERE EXISTS (subquery)
- 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留
提示
- EXISTS (subquery) 只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是 SELECT 1 或 SELECT 'X'，官方说法是实际会忽略SELECT清单，因此没有区别
- EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际校验以确定是否有效率问题
- EXISTS 子查询往往也可以使用条件表达式、其他子查询或者JOIN来代替。何种最优需要绝提问题具体分析

3.1.2 order by关键字优化

一
ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
1. MySQL支持二种方式的排序，FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
2. ORDER BY满足两情况，会使用Index方式排序
  1. ORDER BY语句使用索引最左前列（索引带头大哥）
  2. 使用where子句与OrderBy子句条件列组合满足索引最左前列
二
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
三
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序


双路排序	MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
	取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。
单路排序	从磁盘读取查询需要的所有列，按照orderby列在buffer（个人理解缓冲区概念）对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
结论	由于单路是后出来的，总体而言好过双路但是用单路有问题

四
单路排序存在的问题以及如何优化？

存在的问题：

优化策略：
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

五
小总结

文件排序： Using filesort
扫描索引拍讯排序： Using index

3.1.3 GROUP BY关键字优化

order by 的优化就用于 group by 的优化两者基本一致（group by多了一个having）

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

3.2 慢查询日志

3.2.1 配置慢查询

一
说明
二
查看是否开启及如何开启
SHOW VARIABLES LIKE '%slow_query_log%'

set global slow_query_log = 1
三
什么样的SQL参会记录到慢查询里面？
四
查看当前多少秒算慢
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并后续分析
八
查询当前系统中有多少条慢查询记录
配置版本