建设商城网站的难点,我想卖东西去哪个网站,wordpress建博客,中兴通讯的网站建设分析目录
一.概念
二.查看SQL执行频率
三.定位低效率执行SQL
定位低效率执行SQL—慢查询日志
操作
定位低效率执行SQL—show processlist
四.explain分析执行计划
字段说明
explain中的id explain中的select_type explain中的type
explain中的table
explain中的rows
ex…目录
一.概念
二.查看SQL执行频率
三.定位低效率执行SQL
定位低效率执行SQL—慢查询日志
操作
定位低效率执行SQL—show processlist
四.explain分析执行计划
字段说明
explain中的id explain中的select_type explain中的type
explain中的table
explain中的rows
explain中的key
explain中的extra 一.概念
在应用的的开发过程中由于初期数据量小开发人员写SQL语句时更重视功能上的实现但是当应用系统正式上线后随着生产数据量的急剧增长很多SQL语句开始逐渐显露出性能问题对生产的影响也越来越大此时这些有问题的SQL语句就成为整个系统性能的瓶颈因此我们必须要对它们进行优化.
MySQL的优化方式有很多大致我们可以从以下几点来优化MySQL:
从设计上优化从查询上优化从索引上优化从存储上优化
二.查看SQL执行频率
MySQL客户端连接成功后通过show [session/global] status 命令可以查看服务器状态信息。通过查看状态信息可以查看对当前数据库的主要操作类型。
-- 下面的命令显示了当前 session 中所有统计参数的值
show session status like Com_______;-- 查看当前会话统计结果
show global status like Com_______;-- 查看自数据库上次启动至今提及结果show status like Innodb_row_%;-- 查看针对Innodb引擎的统计结果 参数含义Com_select执行select操作的次数一次查询只累加1。Com_insert执行INSERT操作的次数对于批量插入的INSERT 操作只累加一次。Com_update执行UPDATE操作的次数。com_delete执行DELETE操作的次数。Innodb_rows_readselect查询返回的行数。Innodb_rows_inserted执行INSERT操作插入的行数。lnnodb_rows_updated执行UPDATE操作更新的行数。lnnodb_rows_deleted执行DELETE操作删除的行数。Connections试图连接MySQL服务器的次数。Uptime服务器工作时间。Slow_queries慢查询的次数。
三.定位低效率执行SQL
可以通过以下两种方式定位执行效率较低的sQL语句。
慢查询日志:通过慢查询日志定位那些执行效率较低的SQL语句。show processlist:该命令查看当前MysQL在进行的线程包括线程的状态、是否锁表等可以实时地查看sQL的执行情况同时对一些锁表操作进行优化。
定位低效率执行SQL—慢查询日志
操作
-- 查看慢日志配置信息
show variables like %slow_query_log%;
-- 开启慢日志查询
set global slow_query_log1;
-- 查看慢日志记录SQL的最低阈值时间
show variables like long_query_time%;
-- 修改慢日志记录SQL的最低阈值时间
set global long_query_time4; 定位低效率执行SQL—show processlist
操作
show processlist; id列用户登录mysql时系统分配的connection_id可以使用函数connection_id()查看user列显示当前用户。如果不是root这个命令就只显示用户权限范围的sql语句host列显示这个语句是从哪个ip的哪个端口上发的可以用来跟踪出现问题语句的用户db列显示这个进程目前连接的是哪个数据库command列显示当前连接的执行的命令一般取值为休眠(sleep)查询(query)连接(connect)等time列显示这个状态持续的时间单位是秒state列显示使用当前连接的sq语句的状态很重要的列。state描述的是语句执行中的某一个状态。一个sqi语句以查询为例可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成info列显示这个sql语句是判断问题语句的一个重要依据
四.explain分析执行计划
通过以上步骤查询到效率低的SQL语句后可以通过EXPLAIN命令获取MysQL如何执行SELECT语句的信息包括在SELECT语句执行过程中表如何连接和连接的顺序。 链接https://pan.baidu.com/s/1uaxnHLdWm-f5iY4zNc-n9g 提取码1234 use test_optimize;explain select * from user where uid1;explain select * from user where uname张飞; 字段说明
字段含义idselect查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。select_type表示SELECT的类型常见的取值有SIMPLE (简单表即不使用表连接或者子查询)、PRIMARY(主查询即外层的查询)、UNION (UNION中的第二个或者后面的查询语句) 、SUBQUERY(子查询中的第一个SELECT)等table输出结果集的表type表示表的连接类型性能由好到差的连接类型为( system .-- const ..… eq_ref …….ref .…. ref_or_nul.--- index_merge --- index_subquery ----- range ---- index ---… all )possible_keys表示查询时可能使用的索引key表示实际使用的索引key_len索引字段的长度rows扫描行的数量extra执行情况的说明和描述explain中的id
id字段是select查询的序列号是一组数字表示的是查询中执行select子句或者是操作表的顺序。id情况有三种:
1、id相同表示加载表的顺序是从上到下
2、id不同 id值越大优先级越高越先被执行
3、id有相同也有不同同时存在id相同的可以认为是一组从上往下顺序执行在所有的组中id值越大优先级越高
-- 1、id相同表示加载表的顺序是从上到下
explain select * from user u,user_role ur ,role r where u.uid ur.uid and ur.rid r.rid ;
-- 2、id不同 id值越大优先级越高越先被执行
explain select * from role where rid(select rid from user_role ur where uid(select uid from user where uname张飞));
-- 3、id有相同也有不同同时存在id相同的可以认为是一组从上往下顺序执行在所有的组中id值越大优先级越高
explain select * from role r,(select * from user_role ur where ur.uid(select uid from user where uname张飞)) t where r.rid t.rid; explain中的select_type
表示SELECT的类型常见的取值如下表所示:
select_type含义SIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复杂的子查询最外层查询标记为该标识SUBQUERY在SELECT或 WHERE列表中包含了子查询DERIVED在FROM列表中包含的子查询被标记为DERIVED(衍生)MYSQL会递归执行这些子查询把结果放在临时表中UNION若第二个SELECT出现在UNION之后则标记为UNION;若UNION包含在FROM子句的子查询中外层SELECT将被标记为:DERIVEDUNION RESULT从UNION表获取结果的SELECT连表查询也是simple -- derived :在from中包含子查询被标记为衍生表
explain select * from (select * from user limit 2) t; explain中的type
type显示的是访问类型是较为重要的一个指标可取值为:
type 含义 NULLMySQL不访问任何表索引直接返回结果system系统表少量数据往往不需要进行磁盘lO;如果是5.7及以上版本的话就不是system了而是all即使只有一条记录const命中主键(primary key)或者唯一(unique)索引;被连接的部分是一个常量(const)值;eq_ref对于前表的每一行后表只有一行被扫描。(1) join查询;(2)命中主键(primary key)或者非空唯一(unique not null)索引;(3)等值连接;ref非唯一性索引扫描返回匹配某个单独值的所有行。对于前表的每一行(row)后表可能有多于一行的数据被扫描。range只检索给定返回的行使用一个索引来选择行。where之后出现 between , , , in等操作。index需要扫描索引上的全部数据。all全表扫描此时id上无索引
结果值从最好到最坏以此是: system const eq_ref ref range index ALL
-- explain
-- all
explain select * from user;
-- null 不访问任何表任何索引直接返回结果
explain select now();
-- system 查询系统表表示直接从内存读取数据不会从磁盘读取但是5.7及以上版本不再显示system直接显示all
explain select * from mysql.tables_priv tp ;
-- const
explain select * from user where uid2;
explain select * from user where uname张飞;-- 在没有创建唯一索引之前type为all创建唯一索引之后为const创建普通索引为refcreate unique index index_uname on user (uname);-- 创建唯一索引
drop index index_uname on user;-- 删除索引create index index_uname on user (uname);-- 创建普通索引
系统表指系统自带的表 注
eq_ref指左表有主键而且左表的每一行和右表的每一行刚好匹配 explain中的table
显示这—步所访问数据库中表名称有时不是真实的表名字可能是简称
explain中的rows
扫描行的数量
explain中的key
possible_keys :显示可能应用在这张表的索引一个或多个。key :实际使用的索引如果为NULL则没有使用索引。key_len:表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好。列类型key_len 备注 id intkey_len 41 5允许NULL加1-byteid int not nullkey_len 4不允许NULLuser char(30) utf8key_len 30*31允许NULLuser varchar(30) notnull utf8key_len 30*32动态列类型加2-bytesuser varchar(30) utf8key_len 30*321动态列类型加2-bytes ;允许NULL再加1-bytedetail text(10) utf8key_len 30*321TEXT列截取部分被视为动态列类型加2-bytes ;且允许NULL
explain中的extra
其他的额外的执行计划信息在该列展示。
extra 含义 using filesort说明mysq|会对数据使用一个外部的索引排序而不是按照表内的索引顺序进行读取称为“文件排序 ,效率低。using temporary需要建立临时表(temporary table)来暂存中间结果常见于order by和group by;效率低using indexSQL所需要返回的所有列数据均在一棵索引树上避免访问表的数据行效率不错。
