MySQL具体解释（16）-----------海量数据建议

以下是一部分比較重要的建议：
1、选择正确的存储引擎
以 MySQL为例。包含有两个存储引擎 MyISAM 和 InnoDB，每一个引擎都有利有弊。
MyISAM 适合于一些须要大量查询的应用，但其对于有大量写操作并非非常好。甚至你仅仅是须要update一个字段，整个表都会被锁起来。而别的进程，就算是读进程都无法操作直到读操作完毕。

另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个很复杂的存储引擎。对于一些小的应用。它会比 MyISAM 还慢。

可是它支持“行锁” 。于是在写操作比較多的时候。会更优秀。而且，他还支持很多其它的高级应用，比方：事务。

2、优化字段的数据类型
记住一个原则，越小的列会越快。对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。

所以。把你的数据变得紧凑会对这样的情况很有帮助。由于这降低了对硬盘的訪问。
假设一个表仅仅会有几列罢了（比方说字典表。配置表），那么，我们就没有理由使用 INT 来做主键。使用 MEDIUMINT, SMALLINT 或是更小的 TINYINT 会更经济一些。假设你不须要记录时间，使用 DATE 要比 DATETIME 好得多。当然，你也须要留够足够的扩展空间。

3、为搜索字段加入索引
索引并不一定就是给主键或是唯一的字段。假设在你的表中，有某个字段你总要会经经常使用来做搜索，那么最好是为其建立索引。除非你要搜索的字段是大的文本字段。那应该建立全文索引。
4、避免使用Select *从数据库里读出越多的数据。那么查询就会变得越慢。

而且。假设你的数据库server和WEBserver是两台独立的server的话，这还会添加网络传输的负载。即使你要查询数据表的全部字段，也尽量不要用*通配符，善用内置提供的字段排除定义或许能给带来很多其它的便利。
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搜寻得会更快一些，由于这些固定的长度是非常easy计算下一个数据的偏移量的，所以读取的自然也会非常快。而假设字段不是定长的，那么，每一次要找下一条的话，须要程序找到主键。
而且。固定长度的表也更easy被缓存和重建。

只是，唯一的副作用是，固定长度的字段会浪费一些空间，由于定长的字段不管你用不用。他都是要分配那么多的空间。

使用“垂直切割”技术，你能够切割你的表成为两个一个是定长的。一个则是不定长的。
8、垂直切割“垂直切割”是一种把数据库中的表按列变成几张表的方法，这样能够减少表的复杂度和字段的数目，从而达到优化的目的。
比如：在User表中有一个字段是家庭地址。这个字段是可选字段。相比起，并且你在数据库操作的时候除了个人信息外，你并不须要常常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能。大家想想是不是，大量的时候，我对于用户表来说，仅仅实用户ID，username，口令。用户角色等会被常常使用。

小一点的表总是会有好的性能。
另外，你须要注意的是，这些被分出去的字段所形成的表，你不会常常性地去Join他们，不然的话，这种性能会比不切割时还要差。并且，会是极数级的下降。

9、EXPLAIN 你的 SELECT 查询。
使用 EXPLAIN keyword能够让你知道MySQL是怎样处理你的SQL语句的。这能够帮你分析你的查询语句或是表结构的性能瓶颈。EXPLAIN 的查询结果还会告诉你你的索引主键被怎样利用的，你的数据表是怎样被搜索和排序的……等等，等等。
通常我们能够对照较复杂的尤其是涉及到多表的SELECT语句，把keywordEXPLAIN加到前面。

 

插入大量数据的优化：

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后，速度极大提升。

转载于:https://www.cnblogs.com/jzdwajue/p/6782989.html