mysql深度

Mysql高级

mysql 分层

1. 连接层
2. 服务层
3. 引擎层
4. 存储层

索引优化的优劣势

优势:

1. 提高索引效率，降低IO成本
2. 降低数据排序成本，降低CPU的消耗

劣势

1. 索引也要占用空间
2. 索引会降低更新表的速度

Mysql 慢查询

能干什么:

1. 表的读取顺序
   1. id相同，执行顺序由上而下
   2. id不同，id的序号会递增，id的值越大越先被执行。
   3. id相同不同，同时存在。
2. 数据读取操作的操作类型
   1. simple（简单查询）
   2. primary(包含任何的子查询，的最外层叫做primary）
   3. subquery（select或者where列表中包含的子查询）
   4. derived（from中包含的子查询被标记为衍生，mysql会递归执行这些子查询，把结果放在临时表中）
   5. union（若第二个select出现在union之后，责备标记为union，若union包含在from子句的子查询中，外层的select将被标记为derived）
   6. union result(从union表中获取的select的结果集)
3. 那些索引可以试用
4. 那些索引被实际使用
5. 表之间引用
6. 每张表有多少被优化器查询

explain的查询类型

System > const > eq_ref> ref > range > index > all

1. System(表中只用1条数据(近似于系统表)，忽略不计)

2. const(表示通过一次索引就可以找的，const用于比较primary key(主键)或者unique索引，因为只匹配一条数据，所以快，如果将主键至于where中。Mysql就能将该查询转成一个常量)

3. eq_ref(唯一性索引扫描，对每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描)

4. ref(非唯一索引扫描,返回匹配某个单独值的所有行，本质商也是一种索引访问，他返回所有匹配某个单位的行，然而他可能找到多个符合条件的行，所以他应该数据查询和扫描的混合体)

5. range(开始于索引的某一点，结束于某一点，不用全表扫描。比如（between，in等）)

6. index(全索引扫描，index和all的区别，index之遍历了索引树，all是全部遍历。index是索引中读取，all是从硬盘中读取)

7. all(全标扫描)

prossible_keys，key，key_len

1. prossible_keys(有哪些索引可以用)

2. key（实际使用的索引）

3. key_len（使用索引的字节数（越小越好））

覆盖索引

创建的索引和要查的字段，个数和顺序全部一样。叫做覆盖索引

Extra

1. Using filesort（说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引排序进行，mysql无法利用索引来完成排序操作称为文件排序（mysql多了一次排序的操作））（不好尽快优化 严重）

2. Using temporary （使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by）（不好尽快优化 严重）

3. using index

• 1. 表明相应的select操作使用了覆盖索引，避免访问了表的数据行，效率不错

• 2. 如果出现using where 表明索引被用来执行索引的键值查找；

• 3. 如果没有同时出现using where，表明索引被用来读取数据而非执行查找操作。

4. using where（使用where查找）

5. using join buffer (使用join时，使用了连接缓存)

6. impossible where （where子句的值总是false，不能用来获取任何的元组）

优化方法

1. 全值匹配最好
2. 最佳左前缀法则
3. 不在索引列商做任何的计算，函数，or，会导致索引失效，全表扫描。
4. 存储引擎不能使用索引中范围条件的右边列。
5. 尽量使用覆盖索引，减少select *
6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表搜索。
7. is null，is not null 也无法使用索引。
8. like以通配符开头（’%abc’）的mysql索引也会失效变成全表扫描。
9. 字符串不加单引号，索引会失效
10. 少用or，用or来连接时索引失效
11. 尽可能在索引列商完成排序操作，遵照索引键的最佳左前缀匹配原则。

mysql慢查询优化

1. 慢查询开启并捕获
2. explain+慢查询的分析
3. show profile查询sql在mysql服务器里面的执行细节和生命周期情况。
4. sql数据库服务器的参数调优。

in和exists

1. in是包含，exists是被包含。
2. 当主表的数据小于B表的数据时。用exists。
3. 当主表的数据大于B表的数据时。用in。

filesort的排序算法

1. 双路排序:mysql4.1之前使用的是双路排序，字面意思对磁盘进行两次扫描，最终得到数据
2. 单路排序:mysql4.1之后使用的是单路排序。从磁盘中查询所需要的顺序。按照Order by 的顺序在buffer中对他进行排序，然后扫描排序后进行输出。效率更快。避免了第二次IO操作。并且把随机IO变成了顺序IO。但是他会使用更多的空间，因为他每一行都保存在内存中。
3. 单路排序问题：如果过Buffer内存不足。会出现多路排序。从而倒是性能下降。

提高order by 的查询效率

1. 只要使用order by 就不要使用select *。因为如果select * 查询的字段过多。会占用过多的Buffer。从而导致单路算法变成多路算法性能下降。
2. 提高 sort_buffer_size 的参数扩大Buffer内存
3. 提高 max_length_for_sort_data 提高使用单路算法的概率

为排序使用索引

1. mysql的两种排序方法:文件排序(using filesort)和有序索引排序(using index)
2. mysql能为排序与查询使用相同索引
3. order by能使用索引的最左匹配原则
4. 如果where使用索引的最左匹配定义为常量。则order by可以使用索引

group by分组

1. order by 所有原则都对group by适用
2. group by 的实质是先排序后分组。遵照索引键的最最匹配原则
3. where高于having，能写在where条件的就不要用having

慢查询日志

1. 查看是否开启

SHOW VARIABLES LIKE '%show_query_log%';

2. 开启慢查询

set global slow_query_log=1

读写锁

1. 读锁(共享锁):其他链接只能读取，写操作会被阻塞。本链接只能读取这张表。无法读取其他表。也不能写操作。
2. 写锁(排它锁):其他链接不能读写这张表。只能本链接可以读写这张表。

间隙锁

1. 当我们使用范围条件的，而不是相等条件检索数据，请求共享和排它锁时，innoDB会给符合条件的已有数据的索引项添加锁，对于范围在键值内但不存在的记录，叫做间隙，
2. InnoDB会对这些间隙加锁，这种锁的机制叫做间隙锁。

如何锁定某一行

1. select * from user where id = 1 for update 锁定for update 某一行，其他操作这一行会被阻塞，直到被锁定的行的会话提交commit

MVC

MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)是一种基于多版本的并发控制协议，只有在InnoDB引擎下存在。MVCC是为了实现事务的隔离性，通过版本号，避免同一数据在不同事务间的竞争，你可以把它当成基于多版本号的一种乐观锁。当然，这种乐观锁只在事务级别未提交锁和已提交锁时才会生效。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能。具体见下面介绍。

MySQL CPU 使用率高的原因和解决方法

原因:

1. 系统执行应用提交查询（包括数据修改操作）时需要大量的逻辑读（逻辑 IO，执行查询所需访问的表的数据行数），所以系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。
2. 大量行锁冲突、行锁等待或后台任务也有可能会导致实例的 CPU 使用率过高，

解决方法:
实例诊断报告是排查和解决 MySQL 实例性能问题的最佳工具。无论何种原因导致的性能问题，建议您首先参考下实例诊断报告，尤其是诊断报告中的 SQL 优化、会话列表和慢 SQL 汇总分。