文档章节

mysql优化

ifeixiang
 ifeixiang
发布于 2014/10/31 17:49
字数 1886
阅读 135
收藏 6

下面是一部分比较重要的建议:
1、选择正确的存储引擎
以 MySQL为例,包括有两个存储引擎 MyISAM 和 InnoDB,每个引擎都有利有弊。
MyISAM 适合于一些需要大量查询的应用,但其对于有大量写操作并不是很好。甚至你只是需要update一个字段,整个表都会被锁起来,而别的进程,就算是读进程都无法操作直到读操作完成。另外,MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎,对于一些小的应用,它会比 MyISAM 还慢。但是它支持“行锁” ,于是在写操作比较多的时候,会更优秀。并且,他还支持更多的高级应用,比如:事务。
2、优化字段的数据类型
记住一个原则,越小的列会越快。对于大多数的数据库引擎来说,硬盘操作可能是最重大的瓶颈。所以,把你的数据变得紧凑会对这种情况非常有帮助,因为这减少了对硬盘的访问。
如果一个表只会有几列罢了(比如说字典表,配置表),那么,我们就没有理由使用 INT 来做主键,使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。如果你不需要记录时间,使用 DATE 要比 DATETIME 好得多。当然,你也需要留够足够的扩展空间。
3、为搜索字段添加索引
索引并不一定就是给主键或是唯一的字段。如果在你的表中,有某个字段你总要会经常用来做搜索,那么最好是为其建立索引,除非你要搜索的字段是大的文本字段,那应该建立全文索引。
4、避免使用Select *从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和WEB服务器是两台独立的服务器的话,这还会增加网络传输的负载。即使你要查询数据表的所有字段,也尽量不要用*通配符,善用内置提供的字段排除定义也许能给带来更多的便利。
5、使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。例如,性别、民族、部门和状态之类的这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。
6、尽可能的使用 NOT NULL
除非你有一个很特别的原因去使用 NULL 值,你应该总是让你的字段保持 NOT NULL。 NULL其实需要额外的空间,并且,在你进行比较的时候,你的程序会更复杂。 当然,这里并不是说你就不能使用NULL了,现实情况是很复杂的,依然会有些情况下,你需要使用NULL值。
7、固定长度的表会更快
如果表中的所有字段都是“固定长度”的,整个表会被认为是 “static” 或 “fixed-length”。 例如,表中没有如下类型的字段: VARCHAR,TEXT,BLOB。只要你包括了其中一个这些字段,那么这个表就不是“固定长度静态表”了,这样,MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能,因为MySQL搜寻得会更快一些,因为这些固定的长度是很容易计算下一个数据的偏移量的,所以读取的自然也会很快。而如果字段不是定长的,那么,每一次要找下一条的话,需要程序找到主键。
并且,固定长度的表也更容易被缓存和重建。不过,唯一的副作用是,固定长度的字段会浪费一些空间,因为定长的字段无论你用不用,他都是要分配那么多的空间。
使用“垂直分割”技术,你可以分割你的表成为两个一个是定长的,一个则是不定长的。
8、垂直分割“垂直分割”是一种把数据库中的表按列变成几张表的方法,这样可以降低表的复杂度和字段的数目,从而达到优化的目的。
例如:在User表中有一个字段是家庭地址,这个字段是可选字段,相比起,而且你在数据库操作的时候除了个人信息外,你并不需要经常读取或是改写这个字段。那么,为什么不把他放到另外一张表中呢? 这样会让你的表有更好的性能,大家想想是不是,大量的时候,我对于用户表来说,只有用户ID,用户名,口令,用户角色等会被经常使用。小一点的表总是会有好的性能。
另外,你需要注意的是,这些被分出去的字段所形成的表,你不会经常性地去Join他们,不然的话,这样的性能会比不分割时还要差,而且,会是极数级的下降。
9、EXPLAIN 你的 SELECT 查询;
使用 EXPLAIN 关键字可以让你知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被如何利用的,你的数据表是如何被搜索和排序的……等等,等等。
通常我们可以对比较复杂的尤其是涉及到多表的SELECT语句,把关键字EXPLAIN加到前面。

 

插入大量数据的优化

1。使用多行插入代替单行插入操作。比单条插入快很多;另外,加大bulk_insert_buffer_size的值,例如设置为64M(在MYISAM引擎下)

    insert into t values(),(),();

2.使用LOAD DATA INFILE .......INTO TABLE ..比插入同样多行的insert语句快

3.对使用InnoDB存储引擎的表,你可以在一个事务中完成insert操作,这样InoDB将在事务,结束时刷新改变。而不是每一条insert语句后都刷新改变。同样的操作可以应用到update。

4.如果是非空表,使用alter table table_name disable keys,然后load data infile,导入完数据在执行:

alter table table_name enable keys. 如果是空表,就不需要这个操作,因为myisam表在空表中导入数据时,是先导入数据然后建立indexs。

 

例如下面一段代码,关闭事务commit,等待更新完成后再一次性提交,可以将原来10几个小时的工作变成10几分钟。这里读的是一个700多万行的文件,更新记录约300多万条。

my $db_handle = DBI->connect("DBI:mysql:database=$database;host=$host", $db_user, $db_pass, {'RaiseError' => 1,AutoCommit => 0})|| die "Could not connect to database: $DBI::errstr";eval {while( !eof($fd) )
        {$CloudID = <$fd> ;chomp $CloudID;$crc_code = <$fd> ;chomp $crc_code;my $sql = "call `room_match`.`crcWriteCode`($CloudID,'$crc_code');" ;my $affect_rows = $db_handle->do($sql);
        }$db_handle->commit();
    };

开始的时候是第一次都执行sql,这样速度极慢!设置 autocommit = 0,再 commit后,速度极大提升。

 

© 著作权归作者所有

共有 人打赏支持
ifeixiang
粉丝 16
博文 270
码字总数 99506
作品 0
广州
程序员
私信 提问
MySQL优化器的成本模型

MySQL优化器的成本模型 一个故事@MySQL DBA2016-08-27116 阅读 优化mysql 这是最近在内部的一个分享,概述了MySQL优化器的成本模型。MySQL执行计划选择--成本模型v1.1 全文完。 点赞 优化mys...

一个故事@MySQL DBA
2016/08/27
0
0
《高性能MySQL》读书笔记之MySQL 优化

关于MySQL的执行过程: 1 MySQL中存在对SQL语句的改写,通过改写SQL达到优化SQL语句的目的,如将子查询改为关联查询. 2 MySQL并没有像Oracle 11g那样缓存执行计划,在Java开发中PreparedStateme...

周翔
2016/11/02
28
0
这份MySQL索引实践清单,已超300人在学习,推荐你看看

特邀嘉宾 叶 金 荣 知数堂联合创始人 & 3306π社区负责人 MySQL优化课程讲师 资深MySQL专家,Oracle MySQL ACE,曾任职搜狐畅游DBA主管,精通MySQL数据库,10年以上MySQL相关工作经验,擅长M...

n88lpo
2018/05/25
0
0
Mysql:优化分析(值得深思)

Mysql优化分析 Mysql作为现在的主流关系型数据库,在优化上值得深思啊! 不能只停留在如何使用Mysql,而不去思考如何优化的问题啊。 在谈到Mysql优化方面,Mysql在这方面有专门的文档说明这个...

王木东
2018/07/13
0
0
第六章 查询性能优化 高性能MySql阅读总结

前面的章节我们介绍了如何设计最优的库表结构、 如何建立最好的索引, 这些对于高性能来说是必不可少的。 但这些还不够一一还需要合理的设计查询。 如果查询写得很糟糕,即使库表结构再合理、...

好好学习Sun
2018/07/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

使用Maven打包Docker镜像-Jenkins版

前提:Jenkins服务器上安装好Docker,创建Jenkins任务,配置好代码来源,和Build配置: pom.xml中添加docker-maven-plugin: <plugin> <groupId>com.spotify</groupId> <artifactI......

莫在全
10分钟前
0
0
rabbitmq

灰暗
50分钟前
1
0
Flink

flink HA部署 flink搭建,采用分布式部署方式,分别为A,B,C三个节点。其中A为master;A,B,C为worker。 本文使用的用户是hadoop用户(自己新建) 先决条件 Java 1.8.x or higher scala 自己使用...

-九天-
今天
2
0
数据中台和传统数仓的区别

中台系统把业务层同性的算法能力,服务能力,业务能力高度集成,有效组织 ,动态规划。更好的帮助上层业务。 今天就让我们看看关于数据中台的问答吧。 1 Q : 什么是数据中台? A : 数据中台是...

hblt-j
今天
4
0
Java在什么时候会出现内存泄漏

在Java中,内存泄漏就是存在一些被分配的对象,这些对象有下面两个特点,首先,这些对象是可达的,即在有向图中,存在通路可以与其相连;其次,这些对象是无用的,即程序以后不会再使用这些对...

群星纪元
今天
2
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部