MySQL高级部分学习一

1. mysql的架构介绍

1.1 mysql简介

1.2 mysqlLinux版的安装

1. 检查当前系统是否安装过mysql：rpm -qa|grep -i mysql
2. 查看mysql安装时创建的mysql用户和mysql用户组：cat /etc/password|grep mysql、cat /etc/group|grep mysql
3. mysql服务的启动和停止：service mysql start,查看是否启动成功:ps -ef|grep mysql
4. root密码设置和设置开机随机启动:chkconfig mysql on
5. mysql的安装位置：/var/lib/mysql

1.3 MySQL的配置文件:my.cnf

1. client:客户端的配置

1.4 Mysql逻辑架构介绍

1.5 mysql存储引擎

1. 查看mysql提供的存储引擎:show engines;
2. 查看mysql抢天默认的存储引擎:show variables like '%storage_engine%';

2. 索引优化分析

2.1 SQL性能下降原因

1. 索引失效
2. 关联查询太多join(设计缺陷或不得已的需求)
3. 服务器调优及各个参数设置(缓冲、线程数等)

2.2 SQL执行加载顺序

from–on–join–where–group by–having–select–distinct–order by –limit

2.3 七种JOIN理论

2.4 七种JOIN的SQL编写

2.5 索引是什么

索引(index)是帮助mysql高效获取数据的数据结构。可以理解为“排好顺序快速查找数据结构”

2.6 索引优势劣势

优势：

1. 提高检索效率，降低数据库的IO成本
2. 通过索引对数据进行排序，降低数据排序的成本，降低了COU的小号

劣势：

1. 实际上索引也是一张表，该表保存了主键与索引字段，并且指向实体表记录，所以索引也是要占用空间的
2. 虽然索引大大提高了查询速度，但是却会降低更新表的速度
3. 都会调整因为更新所带来的键值变化后的索引信息

2.7 索引分类和建索引命令语句

分类

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

语法

1. 创建create [unique] index indexname on mytable(columnname(length))``alter mytable add [unique] index [indexname] on (columnname(length))
2. 删除：drop index [indexname] on mytable
3. 查看：show index from table_name\G

2.8 索引结构与检索原理

BTree索引、Hash索引、full-text全文索引、R-Tree索引

2.9 哪些情况适合建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
5. where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who(在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
8. 查询中统计或者分组的字段

2.10 哪些情况不适合建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据建立索引。⚠️如果某个数据列包含许多重复内容，为他建立索引就没有太大的实际效果

2.11 性能分析

2.11.1 性能分析前提知识

MySQL常见瓶颈：

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

2.11.2 explain使用简介

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

使用方法：explain + 对应的查询语句

能干啥：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

2.11.3 explain之ID介绍

select查询的序列号，包含一组数字，表示查询中select子句或操作表的顺序
三种情况：

1. id相同，执行顺序由上至下
2. id不相同，如果是子查询，id的序号会递增，id越大优先级越高，越先被执行
3. id相同和不同同时存在，id越大，越先执行，id相同的，同时执行

2.11.4 explain之select_type和table介绍

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获取的结果

table:是表示这一行的数据表示哪张表

2.11.5 explain之type介绍

显示查询使用了何种类型：
从最好到最差依次是：system>const>eq_ref>ref>range>index>all

system:表只有一行记录等于系统表，这是const类型的特例，平常不会出现，可以忽略不计
const:表示通过索引一次就找到了，const用于比较primary key或者是unique索引。因为之匹配一行数据，所以很快如将主键置于where列表中，mysql就能将该查询装换为一个常量
eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref:非唯一性索引扫描，返回匹配某个值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行。然而，他可能会找到多个复合条件的行，所以它属于查找和扫描的混合体
range:只检索给定范围的行，使用一个索引来选定行，key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好。因为它只需要开始与索引的某一个点，而结束于另一点，不用扫描全部索引
index:full index sacn。index与all区别为index类型值遍历索引树。通常比all快。index从所有的索引中取值。
all:full table scan将遍历全表以找到适合的行

2.11.6 explain之possible_key和key介绍

possible_key:显示可能应用在这张表中的索引，一个或者多个。查询涉及到字段上若存在索引，则该索引将被列出。但不一定被查询实际使用
key:实际使用的索引。如果没有则显示为null.查询中若使用了覆盖索引。则该索引仅出现在key列表中

2.11.7 explain之key_len介绍

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len现实的值为索引字段的最大可能长度，并非实际使用长度，即key_len是分局表定义计算而得。不是通过表内检索出的

2.11.8 explain之ref介绍

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

2.11.9 explain之rows介绍

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

2.11.10 explain之extra介绍

包含不在其他列展示，但是又十分重要的额外信息

1. using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”
2. using temporary:使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
3. using index:表示相应的select操作中使用了覆盖索引(Covering index),避免访问了表的数据行，效率不错！如果同时出现using where ，表明索引被用来执行索引键值的查找。如果没有同时出现 using where ，表明索引用来读取数据而非执行查找动作。
4. using where：表明使用了where过滤
5. using join buffer
6. impossible where:where子句的值总是false,不能用来获取任何元组
7. select tables optimized away
8. distinct:

2.12 索引优化

1. 全值匹配我最爱
2. 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中列
3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列(范围之后的索引全都失效)
5. 尽量使用覆盖索引(只访问索引的查询(索引和查询列一致))，减少select *
6. mysql在使用不等于(!= 或者<>)的时候无法使用索引或导致全表扫描
7. is null,is not null 也无法使用索引
8. like以通配符开头，mysql索引失效会变成全表扫描的操作
9. 字符串不加单引号索引失效
10. 少用or,用它来连接时会索引失效

3.查询截取分析

3.1 查询优化

3.1.1 小表驱动大表

小表驱动大表，即小的数据集驱动大的数据集
当B表的数据集必须小于A表的数据集时，用in优于exists
当A表数据集小于B表的数据集时，用exist优于in

3.1.2 in和exists

3.1.3 使用索引orderby优化

1. order by子句，尽量使用index方式排序，避免使用filesort的方式排序
2. 尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
3. 如果不在索引列上 filesort有两种算法

3.1.4 group by优化

基本上和order by的情况相同

3.2 慢查询日志

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定性能影响。慢查询日志支持将日志记录写入文件

1. 查看是否开启及如何开启
   默认：show variables like '%slow_query_log%'
   开启：set global slow_query_log = 1，只对当前数据库生效，mysql重启后失效
2. 开启后什么样的sql会写入
   
3. 设置慢的阙值时间：set global long_query_time = 3;
4. 看不到改变的原因:需要重新连接或者新开一个会话才能看到修改值。 show variables like 'long_query_time%'、show global variables like 'long_query_time'
5. 记录慢SQL后并后续分析
6. 日志分析工具mysqldunpslow
   
   

3.3 批量数据脚本

3.4 show Profile

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

1. 是否支持，看看当前的mysql版本是否支持
   

2. 开启功能，默认是关闭的，使用前需要开启
   

3. 运行sql

4. 查看结果，show profiles

5. 诊断SQL：show profile cpu,block io for query 上一步前面问题sql数字号码
   

6. 日常开发需要注意的结论

   • converting heap to myisam 查询结果太大，内存都不够用了，往磁盘上办了
   • creating tmp table 创建临时表(拷贝数据到临时表，用完在删除)
   • copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
   • locked

     3.5 全局查询日志

7. 配置启用
   

8. 编码启用
   

9. 永远不要在生产环境中启用

   4. MySQL锁机制

   4.1 数据库锁理论概述

   

锁的分类：
从对数据操作的类型(读/写)分
读锁(共享锁)：针对同一份数据，多个读操作可以同时进行而不会互相影响
行锁(排他锁)：当前写操作没有完成前，他会阻断其他写锁和读锁
从对数据操作的粒度分：表锁/行锁

4.2 读锁案例讲解

---

---

---

4.3 行锁理论

4.4 索引失效行锁变表锁

4.5 间隙锁危害

4.6 如何锁定一行

4.7 行锁总结

5. 主从复制

1. 复制的基本原理
   slave会从master读取binlog来进行数据同步
   三个步骤：
   

2. 复制的基本原则
   每个slave只有一个master

3. 复制的最大问题
   延时

4. 主从常见配置