在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化。
查看操作增删改查的次数:show status like ‘Com_______’;
Com_select 执行 select 操作的次数,一次查询只累加 1。
Com_insert 执行 INSERT 操作的次数,对于批量插入的 INSERT 操作,只累加一次。
Com_update 执行 UPDATE 操作的次数。
Com_delete 执行 DELETE 操作的次数。
查看对InnoDB 存储引擎增删改查操作的行数:show status like ‘Innodb_rows_%’;
定位低效率执行SQL
使用show processlist命令查看当前MySQL正在进行的线程,包括线程的状态、是否
锁表等,可以实时地查看 SQL 的执行情况,同时对一些锁表操作进行优化。
explain分析执行计划
,可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句
的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
id: select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。id的值越大,优先级越高,越先执行。
select_type: 表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(子查询中的第一个 SELECT)
table: 查询那张表
type: 表示表的连接类型,性能由好到差的连接类型为( system --> const --> eq_ref --> ref–> ref_or_null–> index_merge --> index_subquery --> range --> index -->all )
一般来说, 我们需要保证查询至少达到 range 级别, 最好达到ref 。
possible_keys: 表示查询时,可能使用的索引
key: 表示实际使用的索引
key_len: 索引字段的长度
rows: 扫描行的数量
extra: 执行情况的说明和描述
show profile查询SQL执行时间
首先查看show profile是否开启
@@profiling代表未开启,set profiling=1; //开启profiling 开关;
开启之后我们可以写一些sql语句进行查询,然后输入show profiles;查看我们执行sql语句所耗费的时长:
Duration就是所耗费的时长,当然我们还可以根据它的Query_ID查询该sql语句详细耗费时长:show profile for query 6(这个6代表你的Query_ID的值);
其中关注Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端,而不仅仅是返回个客户端。由于 在Sending data状态下,MySQL线程往往需要做大量的磁盘读取操作,所以经常是整各查询中耗时最长的状态。