飞嗨,欢迎您的光临,本博所发布之文章皆为作者亲测通过,如有错误,欢迎通过各种方式指正。(本博已于2015.12.6升级到php7,运行环境php7 php-fpm + nginx1.8.0)

mysql数据库调优

MySQL lf 1516℃ 0评论

以我有限的经历见解,我觉得对于我们后端web猿来说,重点和难点在于:大数据、大流量、高并发。大流量,大数据,要求程序要尽可能减少cpu和内存的消耗,大数据,即要能够快速的海量数据中找出所需要的数据…

一、数据库设计。基于三范式建立的模型是最有效保存数据和最容易扩展的模式。三范式最大的问题在于查询时通常需要join很多表,导致查询效率很低。所以有时候基于性能考虑,我们需要有意的违反三范式,适度的做冗余,以达到提 高查询效率的目的。
第一范式:关系R中所有属性的值域都是单纯域。特点:1、有主关键字 2、主键不能为空 3、主键不能为空 4、字段不可以再分

第二范式:满足第一范式情况下,关系中每一个非主属性不部分依赖于主键

第三范式:满足第二范式情况下,不存在非主属性对码的传递性依赖以及部分性依赖

二、适当建立索引:说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。由于索引的存储结构不同于表的存储,一个表的索引所占空间比数据所占空间还大的情况经常发生。这意味着我们在写数据库的时候做了很多额外的工作,而这个工作只是为了提高读的效率。因此,我们建立一个索引,必须保证这个索引不会“亏本”。一般需要遵守这样的规则:

索引的字段必须是经常作为查询条件的字段;

如果索引多个字段,第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件,这个索引不会起到作用;

索引的字段必须有足够的区分度;

Mysql 对于长字段支持前缀索引;

三、拆分表:

1、水平拆分

(1)表数据量很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,加快了查询速度。
(2)表中的数据本来就有独立性,例如表中分别记录各个地区的数据或不同时期的数据,特别是有些数据常用,而另外一些数据不常用。
(3)需要把数据存放到多个介质上。
(4)需要把历史数据和当前的数据拆分开。
优点:
1:降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,加快了查询速度。
缺点:
1:水平分割会给应用增加复杂度,它通常在查询时需要多个表名,查询所有数据需要union操作。在许多数据库应用中,这种复杂性会超过它带来的优点,因为只要索引关键字不大,则在索引用于查询时,表中增加两到三倍数据量,查询时也就增加读一个索引层的磁盘次数。

2.垂直分割
垂直分割表(不破坏第三范式),把主码(主键)和一些列放到一个表,然后把主码(主键)和另外的一些列放到另一个表中。将原始表分成多个只包含较少列的表。如果一个表中某些列常用,而另外一些列不常用,则可以采用垂直分割。
优点:
1:垂直分割可以使得行数据变小,一个数据块(Block)就能存放更多的数据,在查询时就会减少I/O次数(每次查询时读取的Block 就少)。
2:垂直分割表可以达到最大化利用Cache的目的。
缺点:
1:表垂直分割后,主码(主键)出现冗余,需要管理冗余列
2:会引起表连接JOIN操作(增加CPU开销)需要从业务上规避

3. 库表散列
表散列与水平分割相似,但没有水平分割那样的明显分割界限,采用Hash算法把数据分散到各个分表中, 这样IO更加均衡。一般来说,我们会按照业务或者功能模块将数据库进行分离,不同的模块对应不同的数据库或者表,再按照一定的策略对某个页面或者功能进行更小的数据库散列,比如用户表,按照用户ID进行表散列,散列128张表,则应就能够低成本的提升系统的性能并且有很好的扩展性

四:sql语句优化:

1、开启慢查询日志。配置mysql参数,mysql会自己记录下来慢的sql语句。配置很简单,参数文件里配置:

slow_query_log=/var/log/slow_query.log

long_query_time = 2

就可以在/var/log/slow_query.log里找到执行时间超过2秒的语句了.

2.EXPLAIN:通过慢查询日志,知道是哪句语句慢,就可以用explain select语句,mysql将解释它如何处理SELECT,提供有关表如何联接和联接的次序。

表的结构

表的结构

没有建立索引的tel

没有建立索引的tel

没有建立索引的tel

没有建立索引的tel

EXPLAIN 结果的每行记录显示了每个表的相关信息:

id 本次 SELECT 的标识符。在查询中每个 SELECT 都有一个顺序的数值。

select_type SELECT 的类型,可能会有以下几种:

SIMPLE – 简单的 SELECT (没有使用 UNION 或子查询
PRIMARY – 最外层的 SELECT
UNION – 第二层,在SELECT 之后使用了 UNIO
DEPENDENT UNION – UNION 语句中的第二个 SELECT,依赖于外部子查
SUBQUERY – 子查询中的第一个 SELEC
DEPENDENT SUBQUERY – 子查询中的第一个 SUBQUERY 依赖于外部的子查
DERIVED – 派生表 SELECT(FROM 子句中的子查询)

possible_keys
possible_keys 字段是指MySQL在搜索表记录时可能使用哪个索引。注意,这个字段完全独立于 EXPLAIN 显示的表顺序。这就意味着 possible_keys 里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是 NULL,就表示没有索引被用到。这种情况下,就可以检查 WHERE 子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样,创建一下索引,然后再用 EXPLAIN 检查一下。详细的查看章节”14.2.2 ALTER TABLE Syntax”。想看表都有什么索引,可以通过 SHOW INDEX FROM tbl_name 来看。

key
key 字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候,这个字段的值就是 NULL。想要让MySQL强行使用或者忽略在 possible_keys 字段中的索引列表,可以在查询语句中使用关键字FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 类型表,可以使用 ANALYZE TABLE 来帮助分析使用使用哪个索引更好。如果是 MyISAM 类型表,运行命令 myisamchk –analyze 也是一样的效果。详细的可以查看章节”14.5.2.1 ANALYZE TABLE Syntax”和”5.7.2 Table Maintenance and Crash Recovery”。

key_len
key_len 字段显示了MySQL使用索引的长度。当 key 字段的值为 NULL 时,索引的长度就是 NULL。注意,key_len 的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

ref
ref 字段显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。

rows
rows 字段显示了MySQL认为在查询中应该检索的记录数。

Extra
本字段显示了查询中MySQL的附加信息。

转载请注明:飞嗨 » mysql数据库调优

喜欢 (0)or分享 (0)
发表我的评论
取消评论
表情
粤ICP备15018643号-1