国外的域名注册网站哪个好,用cms做单页网站怎么做,免费做ppt的软件,网站建设除凡科外还有哪些1. 数据库服务器的优化步骤
当我们遇到数据库调优问题的时候#xff0c;该如何思考呢#xff1f;这里把思考的流程整理成下面这张图。
整个流程划分成了 观察#xff08;Show status#xff09; 和 行动#xff08;Action#xff09; 两个部分。字母 S 的部分代表观察该如何思考呢这里把思考的流程整理成下面这张图。
整个流程划分成了 观察Show status 和 行动Action 两个部分。字母 S 的部分代表观察会使用相应的分析工具字母 A 代表的部分是行动对应分析可以采取的行动。 主要的优化手段
加缓存例如redis调整服务器参数索引的设计优化SQL语句是否使用到索引读写主从分离分库分表垂直分库垂直分表水平分表 2. 系统参数
在MySQL中可以使用 SHOW STATUS 语句查询一些MySQL数据库服务器的 性能参数 、 执行频率 。SHOW STATUS语句语法如下
SHOW [GLOBAL|SESSION] STATUS LIKE 参数;
global全局 session当前会话
一些常用的性能参数如下
Connections连接MySQL服务器的次数。UptimeMySQL服务器的上线时间。Slow_queries慢查询的次数。Innodb_rows_readSelect查询返回的行数Innodb_rows_inserted执行INSERT操作插入的行数Innodb_rows_updated执行UPDATE操作更新的行数Innodb_rows_deleted执行DELETE操作删除的行数Com_select查询操作的次数。Com_insert插入操作的次数。对于批量插入的 INSERT 操作只累加一次。Com_update更新操作的次数。Com_delete删除操作的次数。
show status like last_query_cost查询最近一次查询涉及到的页数
3. 慢查询日志
具体允许时长超过long_query_time值的sql会被记录到慢查询日志中默认为10注意不包含10秒需要超过10秒才会被记录到慢查询日志中
默认情况下是不开启慢查询日志的需要手动打开
show variables like %slow_query_log 开启慢查询日志
set global slow_query_logON;
查看慢日志文件位置
show variables like %slow_query_log% 查询慢查询的时间阈值
show variables like %long_query_time%; 设置慢查询阈值 set global long_query_time 1;
设置global的方式对当前session的long_query_time失效。对新连接的客户端有效。所以可以一并执行下述语句
set global long_query_time 1;
set long_query_time 1;mysql服务器重启后设置的慢查询时间阈值就会失效可以通过配置文件的方式进行永久设置
slow_query_logON
slow_query_log_file/var/lib/mysql/xxxx-slow.log
long_query_time1
log_outputFILE如果不指定存储路径则默认将慢查询日志存储到mysql数据库的数据文件夹下不指定文件名的话默认文件名为hostname-slow.log
查看慢查询数目 SHOW GLOBAL STATUS LIKE %Slow_queries%;
4. 定位慢sql
4.1 开启慢查询日子
set global slow_query_logON;
4.2 设置慢查询阈值
set global long_query_time 1;
4.3 查看慢查询数目
查询当前系统中有多少条慢查询记录 SHOW GLOBAL STATUS LIKE %Slow_queries%;
4.4 min_examined_row_limit
除了 long_query_time外控制慢查询日志的还有一个系统变量min_examined_row_limit。这个变量的意思是查询扫描过的最少记录数。这个变量和查询执行时间共同组成了判别一个查询是否是慢查询的条件如果查询扫描过的记录数大于等于这个变量的值并且查询执行时间超过了 long_query_time的值那么这个查询就被记录到慢查询日志中反之则不会被记录到慢查询日志中 这个值默认是0可以通过set命令来修改或者修改my.ini文件来进行修改
4.5 具体分析 以上表所示数据量4000000。 执行两条sql查询时长在1s以上
现在我们的慢sql有5条了之前有过别的操作增加了慢sql数量
4.6 慢查询日志分析工具mysqldumpslow
在生产环境中如果要手工分析日志查找、分析SQL显然是个体力活MySQL提供了日志分析工具 mysqldumpslow 。
查看mysqldumpslow的帮助信息 mysqldumpslow --help mysqldumpslow 命令的具体参数如下 -a: 不将数字抽象成N字符串抽象成S 不加-a参数就会以N和S替代 -s: 是表示按照何种方式排序 c: 访问次数 l: 锁定时间 r: 返回记录 t: 查询时间 al:平均锁定时间 ar:平均返回记录数 at:平均查询时间 默认方式 ac:平均查询次数 -t: 即为返回前面多少条的数据 6 rows in set (2.39 sec) show status like ‘slow_queries’; mysqldumpslow --help -g: 后边搭配一个正则匹配模式大小写不敏感的
常用举例
#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g left join /var/lib/mysql/atguigu-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more分析慢日志 mysqldumpslow -s -t -t 10 /var/lib/mysql/master-slow.log 可以观察到我们的慢sql语句整个顺序是按照耗时降序的
开发中尽量不打开慢查询日志会耗费一些性能
如何删除慢查询日志文件 rm命令进行删除
如何重置慢查询日志文件mysqladmin -uroot -p flush-logs slow
注意mysqladmin -uroot -p flush-logs 如果不加slow是重置所有日志文件包括redoundo等等。
5 explain
定位了慢查询日志后我们可以通过explain或describe工具对SQL语句进行分析。 describe的使用方法与explain一样并且分析结果也是一样。
mysql中会为查询语句提供它认为最优的执行计划但是并不一定是最优的
版本情况 MySQL 5.6.3以前只能 EXPLAIN SELECT MYSQL 5.6.3以后就可以 EXPLAIN SELECTUPDATEDELETE
在5.7以前的版本中想要显示 partitions 需要使用 explain partitions 命令想要显示filtered 需要使用 explain extended 命令。在5.7版本后默认explain直接显示partitions和filtered中的信息。 5.1 语法
EXPLAIN 或 DESCRIBE语句的语法形式如下
EXPLAIN SELECT select_options
DESCRIBE SELECT select_options例如 explain的具体各个结果的意义 5.2 explain各列作用
准备表结构与数据每张表含10000条数据
5.2.1 table
table表名
注意这里的table的个数不一定是我们sql语句里的个数还有可能包含临时表等。查询的每一行记录都对应着一个单表临时表也会对应一个记录。
5.2.2 id
在一个大的查询中每个select关键字都对应一个id该语句的唯一标识。如果explain的结果包括多个id值则数字越大越先执行而对于相同id的行则表示从上往下依次执行。id号的每个号码代表一趟独立的查询一个sql语句查询趟数越少越好
举例一个select
SELECT * FROM s1 INNER JOIN s2 ON s1.key1 s2.key1 WHERE s1.common_field a;两个select EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2虚表的情况
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;特殊情况
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field a);为什么上述sql有两个select但是id就只有1呢因为mysql优化器对sql语句进行了重写原sql复杂度是n*n优化器给优化为外连接的sql即2n复杂度。
5.2.3 select_type 5.2.4 partitions
代表分区的命中情况非分区表该值为null一般情况下查询语句的partitions列都为null
5.2.5 type
性能由好到最坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALLSQL 性能优化的目标至少要达到 range 级别要求是 ref 级别最好是 consts级别。阿里巴巴开发手册要求
5.2.5.1 system
当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的比如 MyISAMMemory那么对该表访问方法就是system
CREATE TABLE t(i int) EngineMyISAM;INSERT INTO t VALUES(1);EXPLAIN SELECT * FROM t;5.2.5.2 const
当我们根据主键或者唯一二级索引列与常数进行等值匹配时对单表的访问方法就是 const
EXPLAIN SELECT * FROM s1 WHERE id 10005;5.2.5.3 eq_ref
在连接查询时如果被驱动表时通过主键或者唯一二级索引列等值匹配的方式进行访问的如果该主键或者唯一二级索引是联合索引的话所有的索引列都必须进行等值比较则对该被驱动表的访问方法就是eq_ref
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id s2.id;5.2.5.4 ref_or_null
当通过普通的二级索引进行等值匹配查询该索引列的值也可以是null值时那么对该表的访问方法就可能是ref_or_null
explain select * from s1 where key1 a or key1 is null5.2.5.5 index_merge
在某些场景下可以使用索引合并的方法来执行查询
explain select * from s1 where key1 a or key3 a但是如果把or换成and
explain select * from s1 where key1 a and key3 a5.2.5.6 unique_subquery
它是针对在一些包含in子查询的查询语句中如果查询优化器决定将in子查询转为exists子查询并且子查询可以使用到主键进行等值匹配的话那么该子查询的type就是unique_subquery
EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1
s2.key1) OR key3 a;5.2.5.7 range
如果使用索引获取某些范围的记录那么有可能使用到range
EXPLAIN SELECT * FROM s1 WHERE key1 IN (a, b, c);5.2.5.8 index
当我们可以使用索引覆盖但是需要扫描全部索引记录时访问方法就是index
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 a;按照最左匹配原则来看应该是用不上索引的但是由于我们查询的列和筛选条件都在联合索引中所以就用上了索引
例如我们在查询字段中多加一列
EXPLAIN SELECT key1,key_part2 FROM s1 WHERE key_part3 a;5.2.5.9 all
EXPLAIN SELECT * FROM s1;5.2.6 key和possible_keys
possible_keys代表可能用到的索引
key实际使用的索引
5.2.7 key_len
实际使用到的索引长度即字节数值越大越好主要针对于联合索引
5.2.8 ref
当使用索引列进行等值查询时与索引进行等值匹配的对象的信息
select * from s1 where key1 aselect * from s1 inner join s2 on s1.id s2.idselect * from s1 inner join s2 on s2.key1 upper(s1.key1)5.2.9 rows
预估需要读取的记录的条目数该值越小越好
SELECT * FROM s1 WHERE key1 z;5.2.10 filtered
某个表经过搜索条件过滤后剩余记录数的百分比
对于单表查询来说这个filtered没什么意义例如
SELECT * FROM s1 WHERE key1 z;rows是359filtered是100那么查询的数据也是359
在连接查询中驱动表对应的执行计划记录的filtered值它决定了被驱动表要执行的次数即 rows*filtered
SELECT * FROM s1 WHERE key1 z AND common_field a;即key1z’的rows有359条数据然后执行and common_field‘a’时满足的数据量在 359*10%
5.2.11 Extra
用来说明一些额外信息包含不适合在其他列中显示但十分重要的额外信息我们可以通过这些额外信息来更准确的理解Mysql到底如何执行给定的查询语句。
例如
5.2.11.1. No Tables used
select 15.2.11.2. Impossible WHERE
SELECT * FROM s1 WHERE 1 ! 1;5.2.11.3. Using where
当我们使用全表扫描来执行对某个表的查询并且该语句的where子句中有针对该表的搜索条件时在Extra中会提示Using where
SELECT * FROM s1 WHERE common_field a;5.2.11.4. No matching min/max row
当查询列表处有min或者max聚合函数但是并没有符合where自居中的搜索条件记录时就会提示该信息
SELECT MIN(key1) FROM s1 WHERE key1 abcdefg;那如果不使用函数呢
SELECT key1 FROM s1 WHERE key1 abcdefg;5.2.11.5. Using index
当我们的查询列表以及搜索条件中只包含属于某个索引的列也就是在可以使用覆盖索引的情况下在Extra列将会提示Using index
SELECT key1 FROM s1 WHERE key1 a;5.2.11.6. Using index condition
有些搜索条件虽然使用到了索引列但是却不能使用到索引
SELECT * FROM s1 WHERE key1 z AND key1 LIKE %a;如果查询语句中的执行过程将要使用索引下推的特性则extra会显示Using index condition
5.2.11.7. join buffer
当被驱动表不能有效的利用索引来加快访问速度mysql会为其分配一块 join buffer的内存块来加快查询速度也就是基于块的嵌套循环算法
SELECT * FROM s1 INNER JOIN s2 ON s1.common_field s2.common_field;5.2.11.8. Using where; Not exists
当我们使用左外连接时如果where子句中包含要求被驱动表的某个列等于null的搜索条件但是那个列又是不允许为null的那么在该表的执行计划的extra列就会提示 not exists
SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 s2.key1 WHERE s2.id IS NULL; 5.2.11.9. Using intersect(…) 、 Using union(…) 和 Using sort_union(…)
就是索引合并的意思
SELECT * FROM s1 WHERE key1 a OR key3 a;5.2.11.10. Zero limit
SELECT * FROM s1 LIMIT 0;5.2.11.11. Using filesort SELECT * FROM s1 ORDER BY common_field LIMIT 10;因为我们的common_field没有索引但是如果要做排序则只能读取到内存中进行排序 using filesort。mysql把这种在内存中或者磁盘上进行排序的方式统称为文件排序filesort。
如果列有索引例如key1 SELECT * FROM s1 ORDER BY key1 LIMIT 10;5.2.11.12. Using temporary
使用临时表。在许多查询的执行过程中mysql可能会借助临时表来完成一些功能比如去重排序等比如许多查询中包含distinctgroupbyunion等子句的查询过程中如果不能有效利用索引来完成查询mysql很有可能寻求建立内部临时表来执行查询如果使用到了临时表则extra会显示using temporary SELECT DISTINCT common_field FROM s1; SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;例如有索引的情况下
SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;5.2.11.14 总结
EXPLAIN不考虑各种CacheEXPLAIN不能显示MySQL在执行查询时所作的优化工作EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况部分统计信息是估算的并非精确值,例如rows
5.3 explain的进一步使用
5.3.1. 传统格式
传统格式简单明了输出是一个表格形式概要说明查询计划。 5.3.2 json格式
json提示的信息量会更全面一些。例如查询成本 query_cost
EXPLAIN FORMATJSON SELECT ....EXPLAIN FORMATJSON SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;{query_block: {select_id: 1,cost_info: {query_cost: 1013.75},grouping_operation: {using_filesort: false,table: {table_name: s1,access_type: index,possible_keys: [idx_key1],key: idx_key1,used_key_parts: [key1],key_length: 303,rows_examined_per_scan: 9895,rows_produced_per_join: 9895,filtered: 100.00,using_index: true,cost_info: {read_cost: 24.25,eval_cost: 989.50,prefix_cost: 1013.75,data_read_per_join: 17M},used_columns: [id,key1]}}}
}eval_cost 是这样计算的 检测 rows × filter 条记录的成本。 prefix_cost 就是单独查询 s1 表的成本也就是read_cost eval_cost data_read_per_join 表示在此次查询中需要读取的数据量。
如果是针对多表连接查询 被驱动表可能被读取多次这里的 read_cost 和 eval_cost 是访问多次被驱动表后累加起来的值大家主要关注里边儿的 prefix_cost 的值代表的是整个连接查询预计的成本也就是单次查询驱动表和被驱动表后的成本的和
5.3.3 TREE格式
EXPLAIN FORMATtree SELECT ....TREE格式是8.0.16版本之后引入的新格式主要根据查询的 各个部分之间的关系和各部分的执行顺序来描述如何查询。 5.4 索引下推
Using index condition
有些搜索条件虽然使用到了索引列但是却不能使用到索引
SELECT * FROM s1 WHERE key1 z AND key1 LIKE %a;key1z’可以使用到索引但是key1 like %a’却无法使用到索引在以前的mysql版本中是按照如下步骤进行查询
根据key1z’来获取满足条件的二级索引记录根据步骤1的结果得到主键值进行回表回表中进行like操作
但是like操作也只涉及到了key1列所以mysql对上述进行了优化
根据key1z’获取二级索引从获取的二级索引来进行like比对过滤如果有值则回表如果没有就结束回表
mysql将这个改进成为索引下推
如果查询语句中的执行过程将要使用索引下推的特性则extra会显示Using index condition
