MySQL优化基础技巧

SQL优化基础

识别性能问题

当确定不存在物理系统资源瓶颈之后,就应该把注意力转向MySQL数据库的优化上.

寻找运行缓慢的SQL语句

使用 SHOW FULL PROCESSLIST 命令可以查看有哪些线程在运行.观察time的大小可以找出耗时较大的sql语句,对这个语句进行多次重复执行排除可能是由于锁定或者系统瓶颈等其他因素导致的个别现象.

生成查询执行计划(QEP,query execution plan)

QEP决定了mysql从底层存储引擎中获取信息的方式.在Select语句前加上EXPLAIN关键字前缀可以查看QEP.

不应该做的事

千万不要随意添加索引.当使用alter添加索引时,由于alter语句是阻塞操作,如果表数据很大会很耗时,如果这张表有多个索引的话性能开销更大.

正确的方式

在添加索引之前,首先验证 表 现有的结构,然后确认表的大小.
使用 show table status like '表名’可以查看表大小.

基本的分析命令

EXPLAIN 命令

explain可以查看sql语句的QEP.比如:
explain select user,password where user like ‘r%’.
有两个关键字可以和EXPLAIN一起使用,分别是:partitions和extended.

EXPLAIN PARTITIONS 命令

EXPLAIN命令的关键字PARTITIONS关键字对用于满足在partitions列中的查询的特定表分区提供附加信息.

EXPLAIN EXTENDED 命令

提供额外的filtered列
通过这个命令我们可以了解到一个显然存在的索引最后却没有被用到的原因是输出结果不是由explain extended决定,而是由后面执行的show warning产生,

SHOW CREATE TABLE 命令

该命令告诉用户如何用准确的语法来重新创建数据库表.

mysql dump工具能够快速生成用户模式或数据库实例中所有表的定义.

SHOW INDEXES 命令

查看索引信息,这些信息包括索引的类型和当前报告的mysql索引的基数

Cardinality列的值代表在索引中每一列唯一值的数量的估计值.

SHOW TABLE STATUS 命令

该命令可以查看数据库表的底层大小以及表结构,包括存储引擎类型,版本,数据和索引大小,行的平均长度以及行数.

返回值的准确度取决于数据库使用的存储引擎.例如如果使用MyISAM,MEMORY,ARCHIVE或BLACKHOLE,那么行的平均长度和行数都是精确值,如果是innoDB则上述值都是估计值.

SHOW STATUS 命令

该命令可以用来查看MySQL服务器的当前内部状态信息.这些信息是非常重要的,可以从全局角度帮助用户确定MySQL服务器的负载的各种指标.

SHOW VARIABLES 命令

该命令用来查看MySQL系统变量的当前值,tmp_table_size限制了内部创建的临时表的最大内存使用量.,通过set命令动态的改动当前会话或特殊设置的全局变量的值可以改变sql语句性能.
当没有指定show variables 命令的范围时,默认session范围内执行.