MySql高级

MySql进阶

一、存储引擎

MyISAM和InnoDB

二、索引优化

1.join查询

2.索引简介

1.什么是索引

帮助mysql高效获取数据的数据结构，排好序的快速查找数据结构。在数据之外，数据库系统还维护者满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样在这些数据结构上实现高级查找算法。这种数据结构就是索引。索引以文件的方式存储在磁盘上。

2.优劣势

1. 优势：
   提高数据检索效率，降低数据库的IO成本
   通过索引对数据进行排序，降低数据排序成本
2. 劣势
   索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引也要占用空间
   索引会降低更新表的速度，MySQL在更新表时，不仅要保存数据，还要保存索引文件每次更新添加了索引列的字段

3.分类

1. 单值索引
   一个索引只包含当单个列，一个表可以有多个单值索引
2. 唯一索引
   索引列的值必须唯一，但允许有空值
   3 .复合索引
   一个索引包含多个列

4.创建

5.结构

Btree索引

6.是否建立索引的情况

适合建立索引的情况

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 查询中排序字段
5. 查询中统计或分组字段

不适合建立索引的情况

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段

7.性能分析EXPLAIN

Explain+sql语句

id:

select查询的序列号，包含一组数字，查询中执行select语句或操作表的顺序
id相同，执行顺序由上至下
id不相同，先执行大的
id同时存在相同和不相同，先执行大的，相同的由上至下执行

select_type:

查询类型

1. SIMPLE 简单select查询，查询中不包含子查询或者UNION
2. PRIMARY 查询中若包含任何的复杂子查询，最外层会被标记为
3. SUBQUARY 子查询
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

1. system 表只有一行记录（系统表），const类似的特例，平时不会出现
2. const 通过一次索引就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快；如将主键置于where列表中，mysql就能将该查询转换为一个常量
3. eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
   4.ref 非唯一索引，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的行，可能会找到多个符合条件的行，所以属于查找和扫描的混合体
4. range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等查询，这种范围扫描比全表扫描要好
5. index Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件少
6. ALL 遍历全表以找到匹配的行

possible_keys:

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，该索引将被列出，但不一定被查询实际使用

key:

实际上使用到的索引

key_len:

表示在索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。显示的值为索引字段的最大可能长度，并非实际使用的长度

ref:

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

rows:

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

Extra:

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

1. Using filesort mysql对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为文件排序
2. Using temporary 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
3. Using index 相应的select操作中使用了覆盖索引（Covering Index），避免访问表的数据行，效率不错；如果同时使用了using where，表明索引被用来执行索引键值的查找；如果没用同时出现using where，表明索引用来读取数据而非执行查找动作。
   覆盖索引：select的数据列只用于从索引中就能获取，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
4. Using where 使用where过滤
5. Using join buffer 使用连接缓存
6. impossible where where子句的值总是false，不能用来获取任何元组
7. select tables optimized 在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化
8. distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

8.索引优化

1.索引失效

1. 全值匹配
2. 最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。查询从索引的最左前列开始并且不跳过索引中的列
3. 不在索引列上做任何操作（计算、函数、类型、转换），会导致索引失效进行全表扫描
4. 范围查询后的索引失效
5. 尽量使用覆盖索引
6. 使用！=或者<>无法使用索引
7. is null,is not null 无法使用索引
8. like以通配符开头，索引会失效（以通配符开头，使用覆盖索引）
9. 字符串不加单引号索引失效
10. 少用or，用来连接会导致索引失效

2.建议

1. 对于单值索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序的最左端
3. 在选择组合索引的时候，尽量选择可以包含当前query中where子句中更多的字段
4. 尽可能通过分析统计信息和调整query的写法来达到选择最合适索引的目的
5. 小表驱动大表
6. order by 排序
   1. 尽量使用Index排序方式，避免使用FileSort方式排序；index效率高于filesort
   2. 优化sort_buffer
7. order by 先排序，再分组，和order by类似

三、慢查询日志

1.设置

1.查询是否开启

SHOW VARIABLES LIKE ‘%slow_query_log%’

2.开启

set global slow_query_log=1;

3.修改慢查询时间

set global long_query_time=3;(3秒)

2.分析 mysqldumpslow

四、show profile分析查询生命周期和过程

1.是什么

用来分析当前会话中语句执行的资源消耗情况，SQL调优测量，默认关闭，保存最近15条运行结果

2.设置

1.查看

show variables like ‘profiling’;

2.开启

set frofiling=on;

3.查看结果

show profiles;

4.诊断sql

show profile cpu,block io for query Query_ID

1. converting HEAP to MyISAM 查询结果太大，内存不够使用磁盘
2. Creating tem table 创建临时表，拷贝数据到临时表，删除临时表
3. Copying to tmp table on disk 把内存中临时表复制到磁盘
4. locked

五、全局查询日志

1.启用

set global general_log=1;
set global log_output=‘TABLE’

2.启用

select * from mysql.general_log

六、锁

1. 读锁：

本线程可以读加锁表，不可以读其他表，不可以修改其他表。
其他线程可以读加锁表，可以读其他未加锁表，修改加锁表阻塞

2. 写锁：

本线程可以读加锁表，可以改加锁表，不可以读其他表
其他线程读加锁表阻塞，修改加锁表阻塞

读锁会阻塞写，不会阻塞读，写锁读和写都阻塞

3.查看加锁表

show open tables；
show status like ‘table%’;

4.表锁（偏读）

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

5.行锁（偏写）

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁的粒度小，发生锁冲突的概率最低，并发度最高。

6.索引失效导致行锁变表锁

7.间隙锁

使用范围条件查找，InnoDB会给符合条件的已有数据记录加锁，也会给范围内不存在的记录加锁，称之为间隙锁

8.锁住一行

begin;
select * from table where 条件 for update;
commit;

9.隔离级别

mysql 默认隔离级别 可重复读

七、主从复制

是异步串行化的

1.主从复制过程

1. master将改变的记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events；
2. slave将master的binary log events拷贝到中继日志（relay log）；
3. slave重做中继日志中的事件，将改变应用到自己的数据库中。

2.复制基本原则

1. 每个slave只有一个master
2. 每个slave只能有一个唯一的服务器ID
3. 每个master可以有多个slave

3.配置

数据库版本一致