MySQL常用优化方案

语句执行后，会显示三个字段： Query_ID（执行ID） | Duration（持续时间）| Query（查询语句） ；

拿到后Query_ID后，可执行 show profile for query Query_ID ，查看详细的准备时间，执行时间、执行结束（ preparing、executing、end ）等。

显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。

显示字段包含： User| Host| db | Command | Time| State| Info 等。

解析语句，查询是否命中索引，及，命中何种索引，用以判断是否符合我们的预期。

返回字段包含： select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 等。

select_type 常见类型：

(1) SIMPLE(简单SELECT，不使用UNION或子查询等)

(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)

(3) UNION(UNION中的第二个或后面的SELECT语句)

(4) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)

table 常见类型：

显示这一行的数据是关于哪张表的.

有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果)

type 常见类型：

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL （从左到右，性能从差到好）

possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）

该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

key

key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中

如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，非实际长度，为最大可能长度。

注：不损失精确性的情况下，长度越短越好。

ref

列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。

rows

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数；

extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

(1).Distinct

一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

(2).Not exists

MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

(3).Range checked for each

Record（index map:#）

没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

(4).Using filesort

看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行；

(5).Using temporary

看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上；

(6).Using index

列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

(7).Using where

使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题。

数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.

1. 优化一览图

2. 优化

笔者将优化分为了两大类,软优化和硬优化,软优化一般是 *** 作数据库即可,而硬优化则是 *** 作服务器硬件及参数设置.

2.1 软优化

2.1.1 查询语句优化

1.首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

2.例:

显示:

其中会显示索引和查询数据读取数据条数等信息.

2.1.2 优化子查询

在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高.

2.1.3 使用索引

索引是提高数据库查询速度最重要的方法之一,关于索引可以参高笔者<MySQL数据库索引>一文,介绍比较详细,此处记录使用索引的三大注意事项:

2.1.4 分解表

对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

2.1.5 中间表

对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

2.1.6 增加冗余字段

类似于创建中间表,增加冗余也是为了减少连接查询.

2.1.7 分析表,,检查表,优化表

分析表主要是分析表中关键字的分布,检查表主要是检查表中是否存在错误,优化表主要是消除删除或更新造成的表空间浪费.

1. 分析表: 使用 ANALYZE 关键字,如ANALYZE TABLE user

2. 检查表: 使用 CHECK关键字,如CHECK TABLE user [option]

option 只对MyISAM有效,共五个参数值:

3. 优化表:使用OPTIMIZE关键字,如OPTIMIZE [LOCAL|NO_WRITE_TO_BINLOG] TABLE user

LOCAL|NO_WRITE_TO_BINLOG都是表示不写入日志.,优化表只对VARCHAR,BLOB和TEXT有效,通过OPTIMIZE TABLE语句可以消除文件碎片,在执行过程中会加上只读锁.

2.2 硬优化

2.2.1 硬件三件套

1.配置多核心和频率高的cpu,多核心可以执行多个线程.

2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.

3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行 *** 作的能力.

2.2.2 优化数据库参数

优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.

2.2.3 分库分表

因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。

2.2.4 缓存集群

如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。

一个完整而复杂的高并发系统架构中，一定会包含：各种复杂的自研基础架构系统。各种精妙的架构设计.因此一篇小文顶多具有抛砖引玉的效果,但是数据库优化的思想差不多就这些了.

此文章主要向大家介绍的是MySQL数据库优化 其中还包括MySQL数据库的性能优化 常用的SQL语句的优化以及MySQL数据库对INSERT语句进行优化的实际 *** 作方案的描述 望你会有所收获

MySQL InnoDB 的性能问题讨论

MySQL性能优化

InnoDB delete from xxx速度暴慢原因

推荐圈子: mysql研究

更多相关推荐 定期分析表和检查表

分析表的语法如下

引用

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name[ tbl_name]

以上语句用于分析和存储表的关键字分布 分析的结果将可以使得系统得到准确的统计信息 使得SQL能够生成正确的执行计划 如果用户感觉实际执行计划并不是预期的执行计划 执行一次分析表可能会解决问题 在分析期间 使用一个读取锁定对表进行锁定 这对于MyISAM DBD和InnoDB表有作用

例如分析一个数据表

引用

*** yze table table_name

检查表的语法如下

引用

CHECK TABLE tb _name[ tbl_name] [option] option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

检查表的作用是检查一个或多个表是否有错误 CHECK TABLE 对MyISAM 和 InnoDB表有作用 对于MyISAM表 关键字统计数据被更新

CHECK TABLE 也可以检查视图是否有错误 比如在视图定义中被引用的表不存在

定期优化表

MySQL数据库优化表的语法如下

引用

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb _name [ tbl_name]

如果删除了表的一大部分 或者如果已经对含有可变长度行的表(含有 VARCHAR BLOB或TEXT列的表)进行更多更改 则应使用OPTIMIZE TABLE命令来进行表优化 这个命令可以将表中的空间碎片进行合并 并且可以消除由于删除或者更新造成的空间浪费 但OPTIMIZE TABLE 命令只对MyISAM BDB 和InnoDB表起作用

例如 optimize table table_name

注意 *** yze check optimize执行期间将对表进行锁定 因此一定注意要在数据库不繁忙的时候执行相关的 *** 作

常用的SQL优化

我们在开发的时候常常用到的SQL语句 无非是INSERT GROUPBY等等 对于这些SQL语句 我们怎么进行优化？

大批量插入数据

当用load命令导入数据的时候 适当的设置可以提高导入的速度

对于MyISAM存储引擎的表 可以通过如下方式快速的导入大量的数据

引用

ALTER TABLE tb _name DISABLE KEYS

loading the data

ALTER TABLE tb _name ENABLE KEYS

DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭MyISAM表非唯一索引的更新 在导入大量的数据到一个非空的MyISAM表时 通过设置这两个命令 可以提高导入的效率

对于导入大量的数据到一个空的MyISAM表时 默认就是先导入数据然后才创建索引的 索引不用进行设置

引用

load data infile /home/mysql/text_txt into table text

对于InnoDB类型的表 这种方式不能提高导入数据的效率 但也有几种针对InnoDB类型的表进行MySQL数据库优化的方式

因为InnoDB类型的表式按照主键的顺序保存的 所以将导入的数据按照主键的顺序排序 可以有效提高导入数据的效率

在导入数据前执行 SET UNIQUE_CHECKS= 关闭唯一性校验 在导入结束后执行SET UNIQUE_CHECKS= 恢复唯一性校验 可以提高导入的效率

如果应用使用自动提交的方式 建议在导入前执行SET AUTOMIT= 关闭自动提交 导入结束后执行SET AUTOMIT= 打开自动提交 也可以提高导入效率

MySQL数据库优化INSERT语句

当进行数据INSERT的时候 可以考虑采用以下几种方式进行优化

如果同时从一个客户插入很多行 尽量使用多个值表的INSERT语句 这种方式将大大缩短客户端与数据库的链接 关闭等消耗 使得效率比分开执行的单个INSERT语句快

例如

insert into test values( )

insert into test values( )

insert into test values( )

将上面三句改为:insert into test values( ) ( ) ( )

如果从不同客户插入很多行 能通过使用INSERT DELAYED 语句得到更高的速度

DELAYED 的含义是让INSERT 语句马上执行 其实数据都被放在内存的队列中 并没有真正写入磁盘 这比每条语句分别插入要快得多 LOW_PRIORITY刚好相反 在所有其他用户对表的读写完后才进行插入

将索引文件和数据文件分在不同的磁盘上存放

如果进行批量插入 可以增加bulk_insert_buffer_size变量值的方法来提高速度 但是 这只能对于MyISAM表使用

当从一个文本文件中装载一个表时 使用LOAD DATA INFILE 这通常比使用很多insert语句快 倍左右

lishixinzhi/Article/program/MySQL/201311/29324

---