Mysql高级

1.逻辑架构

连接层，服务层，引擎层，存储层

2.存储引擎比较

查看命令

show variables like '%storage_engine';

MyISAM VS InnoDB

外键：InnoDB 支持外键。
事务：InnoDB 是事务型的，可以使用 Commit 和 Rollback 语句。
并发：MyISAM 只支持表级锁，而 InnoDB 还支持行级锁。
缓存：MyISAM只缓存索引，InnoDB不仅缓存索引还缓存真实数据。
关注点：MyISAM关注性能，InnoDB关注事务。

3.Join查询

4.索引简介

索引是帮助Mysql高效获取数据的数据结构。可以理解为排好序的快速查找的数据结构。

B+树和红黑树比较

红黑树等平衡树也可以用来实现索引，但是文件系统及数据库系统普遍采用 B+ Tree 作为索引结构，这是因为使用 B+ 树访问磁盘数据有更高的性能。
（一）B+ 树有更低的树高
平衡树的树高 O(h)=O(logdN)，其中 d 为每个节点的出度。红黑树的出度为 2，而 B+ Tree 的出度一般都非常大，所以红黑树的树高 h 很明显比 B+ Tree 大非常多。
（二）磁盘访问原理
操作系统一般将内存和磁盘分割成固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。数据库系统将索引的一个节点的大小设置为页的大小，使得一次 I/O 就能完全载入一个节点。
（三）磁盘预读特性
为了减少磁盘 I/O 操作，磁盘往往不是严格按需读取，而是每次都会预读。预读过程中，磁盘进行顺序读取，顺序读取不需要进行磁盘寻道，并且只需要很短的磁盘旋转时间，速度会非常快。并且可以利用预读特性，相邻的节点也能够被预先载入。

B+树索引和Hash索引比较

B+ Tree 的有序性，所以除了用于查找，还可以用于排序和分组。可以指定多个列作为索引列，多个索引列共同组成键。适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找。如果不是按照索引列的顺序进行查找，则无法使用索引。

哈希索引能以 O(1) 时间进行查找，但是失去了有序性：无法用于排序与分组；只支持精确查找，无法用于部分查找和范围查找。

InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点，比如快速的哈希查找。

5.使用Explain进行分析

Explain包含的字段

id

表示操作表的顺序。
id相同，执行顺序由上至下。
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

select_type

表示查询的类型，有以下情况：

• SIMPLE
  简单的select查询，查询中不包含子查询或者UNION
• PRIMARY
  查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
• SUBQUERY
  在SELECT或者WHERE列表中包含了子查询
• DERIVED
  在FROM列表中包含的子查询被标记为DERIVED（衍生），子查询的结果放在临时表里
• UNION
  若第二个SELECT出现在UNION之后，则被标记为UNION
• UNION RESULT
  从UNION表获取结果的SELECT

table

操作的表

type

表示访问的类型，有以下情况：

• system
  表只有一行记录（等于系统表），这是const类型的特例
• const
  表示通过索引一次就找到了，常见于主键或唯一索引扫描等于常量
• eq_ref
  唯一性索引，对于每个索引键，表中只有一条记录与之匹配
  
• ref
  非唯一性索引，返回匹配某个单独值的所有行
• range
  只检索给定范围的行，使用一个索引来选择行，常见where语句中出现了between、in等的查询
• index
  全索引扫描，比如select id from table, id为主键
• all
  全表扫描，没有用到索引

possible_keys

可能用到的索引

key

实际用到的索引

key_len

表示索引中使用的字节数

ref

显示索引哪一列被使用了

rows

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

Extra

• Using filesort(要避免)
  说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
  MySQL中无法利用索引完成排序操作成为“文件排序”
• Using temporary(要避免)
  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。
• Using index
  表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错
• Using where
  表示使用了where过滤

Explain CASE:

1.首先执行id=4的，select_type是union，表示是union语句中的第二个select，最先执行select name, id from t2。
2.然后执行id=3的，select_type是derived，表示from后有子查询，执行的语句是select id, name from t1 where other_column=’ '。
3.然后执行id=2的，select_type是subquery，表示select或where后有子查询，执行的语句是select id from t3。
4.然后执行id=1的，select_type是primary，表示是复杂查询的最外层查询，执行的语句是select d1.name, d2 where d1。
5.最后执行id=null的，select_type是union result，table是<union1, 4>，表示把id=1和id=4的查询结果合并起来。

6.查询优化

1.全值匹配我最爱
2.最佳左前缀法则
3.不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列，如col1、col2、col3三个字段建索引，而where条件是col1 = 1 and col2 > 1 and col3 = 1，那么col3则用不到索引
5.尽量使用覆盖索引（索引列和查询列一致），减少select*，这样会使用覆盖索引，在索引中取数据。
6.mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
7.is null,is not null 也无法使用索引
8.like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作
9.字符串不加单引号，相当于进行了类型转换，从而导致索引失效
10.少用or,用它连接时会索引失效
总结：
全值匹配我最爱，最左前缀要遵守。（1，2）
索引列上少计算，范围之后全失效。（3， 4）
like百分写右边，覆盖索引不写星。（5， 8）
不等空值还有or，索引失效要少用。（6，7，10）
字符串引号不要丢，sql高级也不难。（9）

1.解决like’%字符串%'索引不被使用的方法

可以使用覆盖索引，查询字段必须是建立覆盖索引字段。这样可以在索引上取数据，type会从all变为index。如name,age建了索引
select name from table where name = “%a%”
select age from table where name = “%a%”
select name,age from table where name = “%a%”
以上三种情况都可以用到索引，type=index
而select * from table where name = "%a%"就用不到索引，type=all

2.小表驱动大表

注：
exists 可以理解为将主查询的结果放到字查询中去验证，根据验证结果(true, false)来判断是否保留。
exists只返回true或false，因此子查询的select * 可以写为select 1或其他，实际执行时会忽略select。

3.order by 优化

避免Order by 产生Using filesort

4.group by 优化

groupby实质是先排序后进行分组，遵照索引建的最佳左前缀。
能写在where限定的条件就不要去having限定。

7.查找慢sql

开启慢查询日志

默认情况下不开启慢查询日志

(1)通过命令查看：

show variables like '%slow_query_log%'

slow_query_log 表示是否开启
slow_query_log_file 表示日志文件的路径，默认文件名是主机名-slow.log

(2)开启：

set global slow_query_log = 1;

注：只对当前数据库生效，mysql重启后就会失效。

(3)设置时间阙值

show variables like 'long_query_time%';

表示超过10秒的sql将会被记录到日志。

可以通过下面命令修改为3秒

set global long_query_time = 3;
show global variables like 'long_query_time%';

(4)用mysqldumpshow进行分析

可以直接查看日志文件，建议用mysqldumpslow进行筛选。

mysqldumpshow -s c -t 10 E:\mysql\data\LAPTOP-SUN6L72T-slow.log

返回访问次数最多的前10条sql。
-s 表示以何种方式排序
c 表示访问次数
-t 表示返回前多少条数据

Show profiles

(1)通过命令查看：

show variables like 'profiling'

(2)查看运行时间：

show profiles;

(3)诊断时间长的sql

show profile cpu, block io for query 上一步Query_ID

cpu 显示cpu相关开销
block io 显示io相关开销

注意两个State值：
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘
这两个操作一把都比较耗时

8.主从复制

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

• binlog 线程：Master将数据更改写入二进制日志（Binary log）中。
• I/O 线程：Slaver将Master的二进制日志写入自己的中继日志（Relay log）。
• SQL 线程：Slaver读取中继日志，解析出主服务器已经执行的数据更改并进行重放(Replay)。

9.读写分离

主服务器处理写操作以及实时性要求比较高的读操作，而从服务器处理读操作。

读写分离能提高性能的原因在于：

• 主从服务器负责各自的读和写，极大程度缓解了锁的争用；
• 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；
• 增加冗余，提高可用性。

读写分离常用代理方式来实现，代理服务器接收应用层传来的读写请求，然后决定转发到哪个服务器。