MySQL高级部分学习——NUW_LK

MySQL高级部分学习

ubuntu上安装MySQL

更新apt-get索引：
```
sudo apt-get update
```
安装MySQL：
```
sudo apt-get install mysql-server
```
（mysql 5.7.30会在安装时要求设置登录密码）
查看MySQL版本：
```
mysql -V
```
进入MySQL：
```
mysql -u root -p 自己设置的密码
```
退出mysql界面：
```
exit
```

其他mysql相关操作：

启动mysql服务：
```
sudo service mysql start
```
（安装完默认启动）
重启mysql服务：
```
sudo service mysql restart
```
停止mysql服务：
```
sudo service mysql stop
```

MySQL的主要配置文件

文件名称	作用
二进制日志log-bin	用于主从复制
错误日志log-error	用于记录严重警告信息，启停信息
查询日志log	记录查询的sql语句
.frm文件	存放表结构
.myd	存放表数据
.myi	存放表索引

MySQL的存储引擎

在mysql中用命令

show engines;

查看所有的存储引擎。以下对两种常用引擎对比：

对比项	MyISAM	Innodb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，操作一条记录锁住整个表，不适合高并发	行锁，操作时只锁住一行，适合高并发
缓存	只缓存索引，不缓存真实数据	索引和数据都缓存，对内存要求高
表空间	小	大
关注点	性能	事务
默认安装	是	是

MySQL的索引

查询性能下降的可能原因：

查询语句写的差
索引失效
关联查询，表的连接太多
服务器调优及各个参数的设置

MySQL读取语句时的顺序：
FROM、ON、WHERE、GROUP BY、HAVING、SELECT、DISTINCT、ORDER BY、LIMIT

索引：帮助MySQL高效获取数据的数据结构，这些数据结构以某种方式指向数据，这样就可以在该数据结构的基础上实现高级算法查找，默认说的索引就是B树索引（聚集索引、次要索引、复合索引、前缀索引、唯一索引等都是B树索引），但还有其他索引，比如哈希索引。缺点就是需要额外的空间保存索引信息，并且每次增、删、改数据的同时还需要修改索引信息，降低了效率

索引分类：

单值索引：一个索引包含单个列，一张表可以有多个单值索引
唯一索引：索引列的值必须唯一，可以包含空值
复合索引：一个索引包含多个列

语法:

创建：

create index 索引名 on 表名(属性)

修改：

alter 表名 add index 索引名 on 表名(属性)

删除：
```
drop index 索引名 on 表名
```
查看索引：
```
show index from 表名
```

MySQL的索引结构：
B-tree索引、哈希索引、全文索引、R-tree索引

哪些字段适合建立索引：

主键自动建立唯一索引
频繁作为查询条件的字段适合建立索引
查询中与其他表关联的字段，外键关系建立索引
查询中统计或分组的字段

哪些字段不适合建立索引：

频繁更新的表不适合建立索引
出现在where子句中的片段
当某列的值重复值极多的情况下不用建立索引
表记录太少不适合建立索引
高并发下倾向于复合索引

当客户端向MySQL请求一条查询语句时，命令解析器模块完成请求分类，区别出是MySQL

explain性能分析

使用explain关键字可以模拟MySQL的查询优化模块去执行SQL查询语句，从而知道MySQL是如何处理查询语句的。从而可以得出性能瓶颈

作用：通过explain+SQL来实现，执行计划包含的信息

id： select查询的序列号，表示查询中执行select子句或操作表的顺序，id越大越先执行，id相等时从上到下执行
select_type：查询类型，包括以下几种。
1. PRIMARY：查询中若包含任何复杂的查询，最外层的查询被标记为该类。
2. SUBQUERY：在where中包含的子查询，并且该查询查的是一个值，若是一组值则不是SUBQUERY
3. DEPENDENT SUBQUERY：在where中包含的子查询，并且该查询查的是一组值，MySQL5.7取消了该类型
4. DERIVED：在from列表中包含的子查询被标记为该类，MySQL会递归的执行这些子查询，将结果放到临时表。
5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION出现在from的子查询中，外层的SELECT则被标记为DERIVED
6. UNION RESULT：两种表的合并，5.7也没有
table：这行数据是关于哪张表的
partitions：查询数据的分区
type：查询的访问类型。最好到最差排序依次是system > const > eq_ref > ref > range > index > all，一般来说优化要达到range，最好达到ref级别
1. const：针对主键或唯一索引的等值查询扫描, 最多只返回一行数据。查询速度非常快, 因为它仅仅读取一次即可
2. eq_ref：唯一性索引。此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高。
3. ref：非唯一性索引，此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的多个结果，要求后表的该字段具有非唯一性索引。
4. range：只检索给定范围的行，一般用在where中的between and、in等操作
5. index：sql使用了索引但没有通过索引过滤，一般是索引覆盖或者利用了索引进行了排序分组
6. all：全表扫描
possible_keys：显示可能应用到这张表的索引，一个或多个，查询涉及到的字段上若存在索引则会被列出来，但不一定被实际使用。
key：实际用的索引
key_len：表示索引中使用的字节数，越小越好，显示的值为索引字段的最大可能长度，并非实际使用长度。
ref：显示索引的哪一列被使用了，最好是常量
rows：表示MySQL执行查询时必须检查的行数，越小越好。
extra：包含不适合在其他列展示的但重要的额外信息
1. using temporary：用临时表保存中间结果，常见于order by 和 group by。只需要给分组字段或排序字段建立索引就能取消该信息。
2. using index：效率不错。表示相应的select操作中使用了覆盖索引，比如查的字段为包含索引的字段时会有该信息
3. using where：表明使用了where过滤
4. using join buffer：连接查询时会出现
5. Select tables optimized away：select中使用了在sum或者count函数时会出现

单表索引失效情况

最佳左前缀法则：如果使用了复合索引，要遵循该准则，指的是查询从最左前列开始并且不能跳过索引中的列。如果符合索引为（属性1，属性2，属性3），那么过滤条件中出现属性1才能用到属性2，出现属性2才能用到属性3
索引列上做计算：不在索引列上做任何操作，比如计算、函数、类型转换等。否则会导致索引失效而转向全表扫描。
索引列上进行范围查询：过滤条件中某字段出现范围过滤的话，该字段后边的字段用不到索引，所以建议在建立索引时，将可能出现范围查询的字段放到最后一列
覆盖索引：尽量不要写
```
select *
```
，select后面尽量用索引中的字段
使用
```
!=
```
或者
```
<>
```
使用
```
IS NOT NULL
```
，但是IS NULL可以用到索引
使用 like时，将%放到前面不可用，只有
```
like \'a%\'
```
的形式可以用到索引
使用or也会使索引失效，尽量使用union或者union all代替

关联查询优化

外连接：从表上建立的索引有效，给从表连接字段建立索引
内连接：给任意一个表建立索引都可以，优化器会自动将建立索引的表放到后面执行（inner join 没有主从表之分，straight join和 inner join作用相同，但是强制将左表设置为主表）
小表驱动大表：将小表放到前面

子查询优化

进行优化时，尽量不使用not null，not in ，not exist等，可以通过连接加条件判断代替
例子：
原SQL：

select age,count(*) from emp where id not in （select ceo from dept where ceo is not null） group by age；

优化SQL：

select age,count(*) from emp left join dept on emp.id=dept.ceo where dept.id is null group by age；

排序分组优化

order by若要使用到索引必须使用到过滤条件，可以用where或者是limit来实现过滤。因此排序分组时若没有任何过滤条件也不会用到索引。此处的过滤必须是包含在索引字段内的。group by不用过滤也可以直接使用索引。
排序分组语句中有多个索引包含的字段时，这些字段的顺序不能错，否则索引不起作用
排序分组语句中有多个索引包含的字段时，这些字段的方向不能反，不能是一个字段升序一个字段降序。否则索引不起作用

慢查询日志

MySQL的日志记录，用来记录执行时间超过long_query_time的查询语句。

查看慢查询日志是否开启：

show variables like \"%slow_query_log%\"

开启慢查询日志：
```
set global slow_query_log=1
```

查看超时阈值：

show variables like \"%long_query_time%\"

修改超时阈值：
```
set long_query_time=0.001
```
查看超时的SQL条数：
```
show status like \'slow_queries\'
```

日志分析工具：MySQL dump slow（Linux命令行使用）
参数介绍：

得到返回记录集最多的10个SQL：

mysqldumpslow -s r -t 10 日志文件

得到访问次数最多的10个SQL：

mysqldumpslow -s c -t 10 日志文件

MySQL锁机制

从不同的粒度划分，锁可以分为读锁和写锁。

读锁：即共享锁，针对同一个数据，多个操作可以同时运行而不会相互影响
写锁：即排他锁，在对数据进行操作时，它会阻断其他的写锁和读锁。

还可以分为表锁，行锁，页锁。

表锁：偏向MyIsam引擎，在执行查询语句之前会给所涉及的表加读锁，在执行修改操作之前会给所涉及表加写锁。表锁的开销小，加锁快，无死锁，发生锁冲突的概率最高，并发度最低。
例子：比如有两个session在操作一张表。
1. 加写锁，session1对某个表加了写锁后只可以读或者修改该表，并且也不能读其他表；此时，session2如果读取或者更新该表则会发生阻塞，直至session1将写锁删除。
表锁有关SQL语句：
1. 查看所有上锁的表：
```
show open tables
```
  ；
2. 上锁的SQL语句：
```
lock table 表1 read或者write，表2 read或者write
```
3. 解锁的SQL语句：
```
unlock tables
```
4. 查看具体的表级锁定信息：
```
show status like \'table%\'
```
  包括表级锁锁定的次数，竞争锁的次数
行锁：偏向Innodb引擎，开销大，加锁慢，会出现死锁，发生锁冲突的概率低，并发度最高。
例子：比如有两个session在操作一条记录。
行锁有关SQL语句：
1. 锁定一行：
```
sql语句 for update
```
2. 产看具体的行锁信息：
```
show status like \'innodb_row_lock\'
```
注意：
1. 行锁变表锁：当某个属性有索引时，若在一定情况下造成了索引失效，那么会引起行锁变表锁，性能极大降低。
2. 间隙锁：当用范围条件检索数据，并请求读锁或者写锁时，Innodb会给符合条件的已有数据记录的索引项加锁，对于键值在范围内，但并不存在的记录叫做间隙。比如当session1修改id为1，2，3，4四条记录时，若session2插入id为2的数据时会造成阻塞。因此很容易对性能造成影响
页锁：开销和加锁时间介于表锁和行锁直接，会出现死锁并发度一般，了解。

主从复制

所有的读写操作都在一个MySQL服务器上面进行会造成服务器压力很大，那么可以将读写操作分开，将写操作放到主机上面，读操作放到从机上，从机可以有多个。主机会将所有的操作记录到binary log（二进制日志）中。从机会去读取主机的binary log，并将其放到各自的Relay log（中继日志）中，再由中继日志同步数据。