Mysql高级
mysql 分层
- 连接层
- 服务层
- 引擎层
- 存储层
索引优化的优劣势
优势:
- 提高索引效率,降低IO成本
- 降低数据排序成本,降低CPU的消耗
劣势
- 索引也要占用空间
- 索引会降低更新表的速度
Mysql 慢查询
能干什么:
- 表的读取顺序
- id相同,执行顺序由上而下
- id不同,id的序号会递增,id的值越大越先被执行。
- id相同不同,同时存在。
- 数据读取操作的操作类型
- simple(简单查询)
- primary(包含任何的子查询,的最外层叫做primary)
- subquery(select或者where列表中包含的子查询)
- derived(from中包含的子查询被标记为衍生,mysql会递归执行这些子查询,把结果放在临时表中)
- union(若第二个select出现在union之后,责备标记为union,若union包含在from子句的子查询中,外层的select将被标记为derived)
- union result(从union表中获取的select的结果集)
- 那些索引可以试用
- 那些索引被实际使用
- 表之间引用
- 每张表有多少被优化器查询
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,不能用来获取任何的元组)
优化方法
- 全值匹配最好
- 最佳左前缀法则
- 不在索引列商做任何的计算,函数,or,会导致索引失效,全表扫描。
- 存储引擎不能使用索引中范围条件的右边列。
- 尽量使用覆盖索引,减少select *
- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表搜索。
- is null,is not null 也无法使用索引。
- like以通配符开头(’%abc’)的mysql索引也会失效变成全表扫描。
- 字符串不加单引号,索引会失效
- 少用or,用or来连接时索引失效
- 尽可能在索引列商完成排序操作,遵照索引键的最佳左前缀匹配原则。
mysql慢查询优化
- 慢查询开启并捕获
- explain+慢查询的分析
- show profile查询sql在mysql服务器里面的执行细节和生命周期情况。
- sql数据库服务器的参数调优。
in和exists
- in是包含,exists是被包含。
- 当主表的数据小于B表的数据时。用exists。
- 当主表的数据大于B表的数据时。用in。
filesort的排序算法
- 双路排序:mysql4.1之前使用的是双路排序,字面意思对磁盘进行两次扫描,最终得到数据
- 单路排序:mysql4.1之后使用的是单路排序。从磁盘中查询所需要的顺序。按照Order by 的顺序在buffer中对他进行排序,然后扫描排序后进行输出。效率更快。避免了第二次IO操作。并且把随机IO变成了顺序IO。但是他会使用更多的空间,因为他每一行都保存在内存中。
- 单路排序问题:如果过Buffer内存不足。会出现多路排序。从而倒是性能下降。
提高order by 的查询效率
- 只要使用order by 就不要使用select *。因为如果select * 查询的字段过多。会占用过多的Buffer。从而导致单路算法变成多路算法性能下降。
- 提高 sort_buffer_size 的参数扩大Buffer内存
- 提高 max_length_for_sort_data 提高使用单路算法的概率
为排序使用索引
- mysql的两种排序方法:文件排序(using filesort)和有序索引排序(using index)
- mysql能为排序与查询使用相同索引
- order by能使用索引的最左匹配原则
- 如果where使用索引的最左匹配定义为常量。则order by可以使用索引
group by分组
- order by 所有原则都对group by适用
- group by 的实质是先排序后分组。遵照索引键的最最匹配原则
- where高于having,能写在where条件的就不要用having
慢查询日志
- 查看是否开启
SHOW VARIABLES LIKE '%show_query_log%';
- 开启慢查询
set global slow_query_log=1
读写锁
- 读锁(共享锁):其他链接只能读取,写操作会被阻塞。本链接只能读取这张表。无法读取其他表。也不能写操作。
- 写锁(排它锁):其他链接不能读写这张表。只能本链接可以读写这张表。
间隙锁
- 当我们使用范围条件的,而不是相等条件检索数据,请求共享和排它锁时,innoDB会给符合条件的已有数据的索引项添加锁,对于范围在键值内但不存在的记录,叫做间隙,
- InnoDB会对这些间隙加锁,这种锁的机制叫做间隙锁。
如何锁定某一行
- 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 使用率高的原因和解决方法
原因:
- 系统执行应用提交查询(包括数据修改操作)时需要大量的逻辑读(逻辑 IO,执行查询所需访问的表的数据行数),所以系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。
- 大量行锁冲突、行锁等待或后台任务也有可能会导致实例的 CPU 使用率过高,
解决方法:
实例诊断报告是排查和解决 MySQL 实例性能问题的最佳工具。无论何种原因导致的性能问题,建议您首先参考下实例诊断报告,尤其是诊断报告中的 SQL 优化、会话列表和慢 SQL 汇总分。