-
安装位置
路径 解释 /var/lib/mysql/ mysql数据库文件的存放位置 /usr/share/mysql/ 配置文件目录 /user/bin/ 命令目录 /etc/init.d/mysql 启停mysql相关脚本 -
mysql逻辑架构图
-
mysql逻辑架构
- 连接层:最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的tcp/ip通信.主要是完成一些类似连接处理,授权认证及相关的安全方案.在该层引入线程池的概念,为通过认证安全的客户端提供线程.同样在该层可以实现基于ssl的安全连接.服务器也会为安全接入的每个客户端验证它所有的操作权限.
- 服务层:第二层主要完成大多数核心的服务功能,如sql接口,并完成缓存的查询,sql的分析和优化及部分内置函数的执行.所有跨存储引擎的功能也在这一层实现,如过程,函数等.在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化,如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作.如果是select语句,服务器还会查询内部缓存,如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能.
- 引擎层:存储引擎真正的负责了mysql中数据的存储和提取,服务器通过api与存储引擎进行通信.不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行读取.
- 存储层:数据存储层主要是将数据存储在运行于裸机设备的文件系统上,并完成对存储引擎的交互.
-
MyISAM和InnoDB
对比项 MyISAM InnoDB 主外键 不支持 支持 事务 不支持 支持 行表锁 支持表锁,不适合高并发操作 行锁,表锁 缓存 只缓存索引,不缓存数据 缓存索引和数据,系统内存影响效率 表空间 小 大 关注点 性能 事务 默认安装 是 是 -
mysql查询慢的原因
- sql写的烂
- 没有索引
- join过多
- 服务器调优(缓冲,线程等)
-
sql执行顺序
-
七种join(1 union 2 = 6, 3 union 4 = 7)
-
索引(影响where和order by)
- 索引是排好序的具有某种算法的快速查找的数据结构
- 优势
- 提高检索效率,降低IO成本
- 减低排序成本,降低cpu消耗
- 劣势
- 索引也是一张表,会占用空间
- 提高了查询速度,但降低了更新速度
- 如果表数据量大,则需要建立最优的索引
- 分类
- 单值索引:一个索引只包含单个列,一个表可以有多个单值索引
- 唯一索引:索引列的值必须唯一,但可以为空
- 复合索引:一个索引包含多个列
- 索引结构
- BTree:
- Hash:
- full-text:
- R-Tree:
- 适合建立索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段
- 查询字段中与其他表关联的字段,外键关系建立索引
- 高并发环境建议建立复合索引
- 查询中排序的字段和索引条件的字段
- 查询中统计或分组的字段
- 不适合建立索引的情况
- 表记录较少
- 频繁更新的表
- where里用不到的字段
- 数据重复且平均分配的表字段(tinyint,smallint)
-
mysql查询优化器
-
mysql瓶颈
- cpu:数据装入内存或从磁盘读取数据的时候
- io:装入数据远大于内存容量的时候
- 服务器硬件性能:通过top,free,iostat和vmstat等命令查看系统性能
-
mysql执行计划explain(explain + sql)
- id,体现表的执行顺序
- id相同,从上到下依次执行
- id不同,id越大越先执行
- select_type,体现数据读取操作的类型
- simple:简单查询
- primary:复杂查询中的最外层,最后加载
- subquery:select或where中包含的子查询
- derived:from后面的虚表,会增加系统负担
- union:union后的select
- union result:从union中取出结果
- table:体现数据是关于哪一张表
- type:体现访问[效率]类型排序,从好到差依次为:null>system>const>eq_ref>ref>range>index>all
- 全:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>all
- 至少达到range级别
- system:表只有一条记录
- const:只匹配一行数据,一次索引就能找到,用于比较primary key和unique索引,如果将主键置于where中,mysql将其转换为常量
- eq_ref:唯一性索引扫描,表中只有一条记录与之对应,常见于主键或唯一键(用到的索引查到一个值)
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行(用到的索引查到多个值)
- range:只检索给定范围的行where条件中的between,in,>,<等
- index:从索引数中查找(也是扫描全表)
- all:全表扫描
- possible_keys:查询时可能会用到的索引
- key:查询中实际用到的索引
- key_len:索引中使用的字节数(字段的最大长度,并非实际使用长度,查询越精确,长度越大),越短越好
- ref:用于查找索引列上的值,可能是常量
- rows:查询需要读取表的行数
- extra:重要的额外信息
- using filesort:mysql会使用外部的索引排序,而不是按照表中的索引排序,称为"文件排序",需要优化
- using temporary:使用内部临时表,常见于order by和group by,必须优化
- using index:使用了覆盖索引,同时出现using where表示使用索引精确查找,效率较高
- 覆盖索引:查询的列被所建的索引覆盖,直接从索引中查找, 不用从数据中查找
- using where:使用了where过滤
- using join buffer:使用了连接缓存
- impossible where:where子句的值总是false
- select tables optimized away:在没有group的情况下,基于索引优化max/min操作
- distinct:优化distinct,找到第一个值后停止查找相同的值
- id,体现表的执行顺序
-
索引分析
- 单表,范围后的索引无效(between,>,<等)
- 两表,左连接索引加右表(右连接相反)
- 三表
- 小表驱动大表
- 优先优化内层循环
- join语句中被驱动的表上的join字段已被索引
- 设置mysql系统的JoinBuffer
-
索引失效
- 全值匹配不会失效
- 最佳左前缀法则,越靠左边的越不能断(像火车头一样)
- 不在索引列上做任何操作(where后的计算,函数,类型转换(手动或自动)),会导致索引失效
- 范围之后(大于,小于)的索引会失效,范围当前值用于排序
- 尽量使用覆盖索引(只访问索引列的值),减少select * 的使用
- 使用不等于符号时无法使用索引会导致全表扫描
- is null 和 is not null 会导致索引失效
- like以通配符开头会失效(like '%z')
- 百分号放右边
- 使用覆盖索引
- 字符串不加单引号会导致索引失效
- 少用or,用它连接会导致索引失效
-
建议
- group by 需要先排序(和order by一致),可能会产生临时表
- 单值索引尽量选择过滤性更好的列
- 组合索引过滤性好的列向前排
- 组合索引尽量包含where子句的条件
- 多调整query语句达到最优
-
优化步骤
- 开启慢查询并捕获
- explain + 慢sql分析
- show profile查询sql语句在服务器中的执行细节和生命周期情况
- sql服务器参数调优
-
优化方案
- 小表驱动大表
-
select * from A where id in (select id from B); 等价于 select * from A where exists (select 1 from B where B.id = A.id); 当B表的数据集小于A表的数据集时候,in优于exists,反之exists优于in 注意在A和B表的id上建立索引
-
- order by优化
- 使用index的情况:order by满足最佳左前缀原则或where子句与order by子句组合满足最佳左前缀原则
- 使用filesort排序:
- 双路排序:v4.1之前使用双路排序,需要两次扫描磁盘.从磁盘取排序字段,在buffer中排序,再从磁盘取其他字段
- 单路排序:v4.1之后使用单路排序,从磁盘读取需要的所有列在buffer中排序,然后扫描排序后的列表输出
- 优化策略
- 增大sort_buffer_size:增加一次IO的概率
- 增大max_length_for_sort_data:增大这个参数会增加使用单路排序的概率
- order by的时候不要用select *
- 当query字段大小总和小于max_length_for_sort_data而且字段不是text|blob类型时,会使用单路排序,否则使用多路排序
- 两种算法的数据都有可能超出sort_buffer的容量,超出之后导致多次IO, 所以要提高sort_buffer_size
- 适当提高max_length_for_data,但过高超出sort_buffer_size的概率就会增大
- group by优化(和order by较同)
- group by实质是先排序后分组,遵循最佳左前缀法则
- where高于having,能写在where的字段不要写在having
- 开启慢日志查询
-
# 慢查询日志 slow_query_log=1 slow_query_log_file=/var/log/mysql/mysql-slow.log long_query_time = 3 -
mysqldumpslow分析工具
-
s:排序方式
-
c:访问次数
-
l:锁定时间
-
r:返回记录
-
t:查询时间
-
al:平均锁定时间
-
ar:平均返回记录数
-
at:平均查询时间
-
t:返回前面多少条的数据
-
g:搭配正则,忽略大小写
-
-
mysqldumpslow例子
-
-
- 小表驱动大表
-
show profile
-
是mysql提供的用来分析当前会话中语句执行的资源消耗情况
-
开启方式:set profiling = on
-
show profiles:查看sql语句
-
show profile cpu, block io for query queryId:显示sql语句的消息信息
-
查询类型
-
all:显示所有信息
-
block io:显示IO开销情况
-
context switches:显示上下文切换开销
-
cpu:显示cpu相关开销
-
ipc:显示发送和接收相关信息
-
memory:显示内存开销
-
page faults:显示错误页面相关开销
-
source:显示和source_function,source_file,source_line相关的开销
-
swaps:显示和交换次数有关的开销
-
-
如果status出现下列情况,则需要优化
-
converting HEAP to MyISAM:查询结果集太大,往磁盘上移动数据
-
creating tmp table:创建临时表,copy数据到临时表, 用完再删除,耗时耗资源
-
copying to tmp table on dish:把内存中的表复制到磁盘,危险
-
locked:表锁住了
-
-
-
-
全局查询日志
-
配置文件启用:
-
#开启 general_log=1 #日志文件 general_log_file=/var/log/mysql #输出格式 log_output=FiLE
-
-
编码启用:set global general_log=1
-
-
锁机制
- 分类一
- 读锁(共享锁):针对同一份数据,多个读操作同时进行,互补影响
- 写锁(排它锁):当前写操作没有完成,会阻断其它写锁和读锁
- 加锁后
-
读锁 写锁 当前session查询当前表 Y Y 当前session查询其它表 N N 当前session更新当前表 N Y 当前session更新其它表 N N 其它session查询当前表 Y wait 其他session查询其它表 Y Y 其它session更新当前表 wait wait 其它session更新其它表 Y Y -
总结:读锁会阻塞读操作,写锁会阻塞读写操作
-
- 分类二
- 表锁
- 特点:偏向MyISAM存储引擎,开销小,加锁快,无死锁,锁冲突概率高,并发度低
- 手动加锁:lock table 表名 read/write
- 查看表锁加过的锁:show open tables;
- 解锁:unlock tables;
- 查看表锁定或立即读取的情况:show status like 'table%';
- Table_locks_immediate:立即获取锁的次数
- Table_locks_waited:等待获取锁的次数
- 行锁
- innoDB更新时自动行锁
- 索引失效行锁变表锁(int和char自动类型转换)
- 间隙锁:使用范围检索数据时,InnoDB会给复合条件的数据加锁,其中断裂的数据叫做间隙[增删改]
- 手动加行锁:select * from student where id = 1 for update;
- 查看加锁情况:show status like 'innodb_row_lock%';
- 页锁:粒度介于表锁和行锁之间
- MyISAM(表锁)的读写调度是写优先,所以偏读
- InnoDB(行锁)
- 表锁
- 分类一
-
mysql主从复制
- 配置
-
##主机 # 主从配置 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log log_error = /var/log/mysql/mysql-err.log # 主机可读写 read-only = 0 # 忽略的数据库 binlog-ignore-db = mysql # 需要备注的数据 #binlog-do-db = mytest #从机 # 开启日志,进行主从复制 server-id = 3304 log_bin = mysql-bin log_error = mysql-err - 授权账号读取文件复制
-
GRANT replication SLAVE ON *.* TO 'admin'@'从机地址' IDENTIFIED BY 'admin'; FLUSH PRIVILEGES; show master status; - 从机获取授权
-
stop slave; CHANGE MASTER TO MASTER_HOST='主机地址', MASTER_USER = 'admin', MASTER_PASSWORD = 'admin', MASTER_LOG_FILE = '日志名',MASTER_LOG_POS=日志位置; start slave; show slave status;
-
锁表处理
-
SHOW PROCESSLIST; -- 查询表被锁进程
-
SHOW OPEN TABLES WHERE In_use > 0; -- 当前锁表状态
-
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; -- 查看正在锁的事务
-
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; -- 查看等待锁的事物
-
SELECT CONCAT('KILL ',ID, ';') FROM INFORMATION_SCHEMA.PROCESSLIST;
-
-
注意
- 建议建立复合索引,建议一张表建立5个以下是索引
- 建立多个复合索引
- 少用全查询,不要真删除
mysql高级
最新推荐文章于 2022-10-28 20:46:37 发布
6727
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
