MYSQL教程mysql 超大数据/表管理技巧

发布时间：2020-12-12 00:55:13 所属栏目：MySql教程来源：网络整理

导读：《MYSQL教程mysql 超大数据/表管理技巧》要点：本文介绍了MYSQL教程mysql 超大数据/表管理技巧，希望对您有用。如果有疑问，可以联系我们。如果你对长篇大论没有兴趣,也可以直接看看结果,或许你对结果感兴趣.在实际应用中经过存储、优化可以做到在超过9千

《MYSQL教程mysql 超大数据/表管理技巧》要点：
本文介绍了MYSQL教程mysql 超大数据/表管理技巧，希望对您有用。如果有疑问，可以联系我们。

如果你对长篇大论没有兴趣,也可以直接看看结果,或许你对结果感兴趣.在实际应用中经过存储、优化可以做到在超过9千万数据中的查询响应速度控制在1到20毫秒.看上去是个不错的成绩,不过优化这条路没有终点,当我们的系统有超过几百人、上千人同时使用时,仍然会显的力不从心.MYSQL必读

目录：MYSQL必读

??? 分区存储
??? 优化查询
??? 改进分区
??? 模糊搜索
??? 持续改进的方案MYSQL必读

正文：MYSQL必读

??? 分区存储
??? 对于超大的数据来说,分区存储是一个不错的选择,或者说这是一个必选项.对于本例来说,数据记录来源不同,首先可以根据来源来划分这些数据.但是仅仅这样还不够,因为每个来源的分区的数据都可能超过千万.这对数据的存储和查询还是太大了.MySQL5.x以后已经比较好的支持了数据分区以及子分区.因此数据就采用分区+子分区来存储.MYSQL必读

??? 下面是基本的数据结构定义：MYSQL必读

代码如下:
??????? CREATE TABLE `tmp_sampledata` (
??????? `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
??????? `username` varchar(32) DEFAULT NULL,
??????? `passwd` varchar(32) DEFAULT NULL,
??????? `email` varchar(64) DEFAULT NULL,
??????? `nickname` varchar(32) DEFAULT NULL,
??????? `siteid` varchar(32) DEFAULT NULL,
??????? `src` smallint(6) NOT NULL DEFAULT '0′,
??????? PRIMARY KEY (`id`,`src`)
??????? ) ENGINE=MyISAM AUTO_INCREMENT=95660181 DEFAULT CHARSET=gbk
??????? /*!50500 PARTITION BY LIST COLUMNS(src)
??????? SUBPARTITION BY HASH (id)
??????? SUBPARTITIONS 5
??????? (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
??????? PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
??????? PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
??????? PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
??????? PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
??????? PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
??????? PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
??????? PARTITION p62678 VALUES IN (8) ENGINE = MyISAM) */

??? 对于拥有分区及子分区的数据表,分区条件(包括子分区条件)中使用的数据列,都应该定义在primary key 或者 unique key中.详细的分区定义格式,可以参考MySQL的文档.上面的结构是第一稿的存储方式(后文还将进行修改).采用load data infile的方式加载,用时30分钟加载8千万记录.感觉还是挺快的(bulk_insert_buffer_size=8m).
??? 基本查询优化
??? 数据装载完毕后,我们测试了一个查询：MYSQL必读

代码如下:
??????? mysql> explain select * from tmp_sampledata where id=9562468G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tmp_sampledata
??????? type: ref
??????? possible_keys: PRIMARY
??????? key: PRIMARY
??????? key_len: 8
??????? ref: const
??????? rows: 8
??????? Extra:
??????? 1 row in set (0.00 sec)

??? 这是毋庸置疑的,通过id进行查询是使用了主键,查询速度会很快.但是这样的做法几乎没有意义.因为对于终端用户来说,不可能知晓任何的资料的id的.假如需要按照username来进行查询的话：MYSQL必读

代码如下:
??????? mysql> explain select * from tmp_sampledata where username = ‘yourusername'G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tmp_sampledata
??????? type: ALL
??????? possible_keys: NULL
??????? key: NULL
??????? key_len: NULL
??????? ref: NULL
??????? rows: 74352359
??????? Extra: Using where
??????? 1 row in set (0.00 sec)

??????? mysql> explain select * from tmp_sampledata where src between 1 and 7 and username = ‘yourusername'G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tmp_sampledata
??????? type: ALL
??????? possible_keys: NULL
??????? key: NULL
??????? key_len: NULL
??????? ref: NULL
??????? rows: 74352359
??????? Extra: Using where
??????? 1 row in set (0.00 sec)
MYSQL必读

??? 那这个查询就没法用了.根本就没人能等待一个上亿表的全表搜索!这是我们就考虑是否给username创建一个索引,这样肯定会提高查询速度：MYSQL必读

??????? create index idx_username on tmp_sampledata(username);MYSQL必读

??? 这个创建索引的时间很久,似乎超过了数据装载时间,不过好歹建好了.MYSQL必读

代码如下:
??????? mysql> explain select * from tmp_sampledata2 where username = ‘yourusername'G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tmp_sampledata2
??????? type: ref
??????? possible_keys: idx_username
??????? key: idx_username
??????? key_len: 66
??????? ref: const
??????? rows: 80
??????? Extra: Using where
??????? 1 row in set (0.00 sec)

??? 和预期的一样,这个查询使用了索引,查询速度在可接受范围内.
??? 但是这带来了另外一个问题：创建索引需要而外的空间!!当我们对username和email都创建索引时,空间的使用大幅度的提升!这同样不是我们期望看到的(无奈的选择?).MYSQL必读

??? 除了使用索引,并保证其在查询中能使用到此索引外,分区的关键字段是一个很重要的优化因素,比如下面的这个例子：MYSQL必读

代码如下:
??????? mysql> explain select id from tsampledata where username='abcdef'G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tsampledata
??????? type: ref
??????? possible_keys: idx_sampledata_username
??????? key: idx_sampledata_username
??????? key_len: 66
??????? ref: const
??????? rows: 80
??????? Extra: Using where
??????? 1 row in set (0.00 sec)

??????? mysql> explain select id from tsampledata where username='abcdef' and src in (2,3,4,5)G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tsampledata
??????? type: ref
??????? possible_keys: idx_sampledata_username
??????? key: idx_sampledata_username
??????? key_len: 66
??????? ref: const
??????? rows: 40
??????? Extra: Using where
??????? 1 row in set (0.01 sec)MYSQL必读

??????? mysql> explain select id from tsampledata where username='abcdef' and src in (2)G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tsampledata
??????? type: ref
??????? possible_keys: idx_sampledata_username
??????? key: idx_sampledata_username
??????? key_len: 66
??????? ref: const
??????? rows: 10
??????? Extra: Using where
??????? 1 row in set (0.00 sec)MYSQL必读

??????? mysql> explain select id from tsampledata where username='abcdef' and src in (2,3)G
??????? *************************** 1. row ***************************
??????? id: 1
??????? select_type: SIMPLE
??????? table: tsampledata
??????? type: ref
??????? possible_keys: idx_sampledata_username
??????? key: idx_sampledata_username
??????? key_len: 66
??????? ref: const
??????? rows: 20
??????? Extra: Using where
??????? 1 row in set (0.00 sec)
MYSQL必读

??? 同一个查询语句在根据是否针对分区限定做查询时,查询成本相差很大：MYSQL必读

??????? where username='abcdef'??????????????????????????????????????????????????? rows: 80
??????? where username='abcdef' and src in (2,5)??????????? rows: 40
??????? where username='abcdef' and src in (2)??????????????????????? rows: 10
??????? where username='abcdef' and src in (2,3)??????????????????? rows: 20MYSQL必读

??? 从分析中看出,当根据src(分区表的分区字段)进行查询限定时,被影响的数目(rows)在发生着变化.rows:80代表着需要对8个分区进行搜索.
??? 改进数据存储：另一种分区格式
??? 既然在统计应用中,最多用的是通过username,email进行数据查询,那么在表存储时,应该考虑使用username,email进行分区,而不是通过id.因此重新创建分区表,导入数据：MYSQL必读

代码如下:
??????? CREATE TABLE `tmp_sampledata` (
??????? `id` bigint(20) unsigned NOT NULL,
??????? `username` varchar(32) NOT NULL DEFAULT ”,
??????? `email` varchar(64) NOT NULL DEFAULT ”,
??????? primary KEY (`src`,`username`,`email`,`id`)
??????? ) ENGINE=MyISAM DEFAULT CHARSET=gbk
??????? PARTITION BY LIST COLUMNS(src)
??????? SUBPARTITION BY KEY (username,email)
??????? SUBPARTITIONS 10
??????? (PARTITION pose VALUES IN (1) ENGINE = MyISAM,
??????? PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

??? 这个定义没什么问题,按照预期,它将根据primary key来进行数据表分区.但是这有一个非常非常严重的性能问题：数据在load data infile的时候,同时对数据进行索引创建.这大大延长了数据装载时间,同样是不可忍受的情况.上面这个例子,如果建表时启用了 primary key 或者 unique key,在我的测试系统上,load data infile执行了超过12小时.而下面这个：MYSQL必读

代码如下:
??????? CREATE TABLE `tmp_sampledata` (
??????? `id` bigint(20) unsigned NOT NULL,
??????? `src` smallint(6) NOT NULL DEFAULT '0′
??????? ) ENGINE=MyISAM DEFAULT CHARSET=gbk
??????? PARTITION BY LIST COLUMNS(src)
??????? SUBPARTITION BY KEY (username,
??????? PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;

??? 数据装载仅仅用了5分钟：
??? mysql> load data infile ‘cvsfile.txt' into table tmp_sampledata fields terminated by ‘t' escaped by ”;
??? Query OK,74352359 rows affected,65535 warnings (5 min 23.67 sec)
??? Records: 74352359 Deleted: 0 Skipped: 0 Warnings: 51267046

??? So,所有的问题,又回到了2.上
??? 测试查询中的模糊搜索
??? 对于创建好索引的大数据表,一般般的针对性的查询,应该可以满足需要.但是有些查询可能不能通过索引来发挥效率,比如查询以 163.com 结尾的邮箱:MYSQL必读

??????? select … from … where email like ‘%163.com'MYSQL必读

??? 即便数据针对 email 建立有索引,上面的查询是用不到那个索引的.如果我们使用的是 oracle,那么还可以建立一个反向索引,但是mysql不支持反向索引.所以如果发生类似的查询,只有两种方案可以：
??????? 通过数据冗余,把需要的字段反转一遍另外保存,并创建一个索引
??????? 这样上面的那个查询可以通过 where email like ‘moc.361%' 来完成,但是这个成本(存储、更新)太高昂了
??????? 通过全文检索fulltext来实现.不过mysql同样在分区表上不支持fulltext(或许等待以后的版本吧.)
??????? 自己做分词fulltext
??? 没有最终方案MYSQL必读

??????????? 创建一个不含任何索引、键的分区表；
??????????? 导入数据；
??????????? 创建索引；MYSQL必读

??? 因为创建索引要花很久时间,此处做了个小小调整,提高myisam索引的排序空间为1G(默认是8m)：MYSQL必读

??????? mysql> set myisam_sort_buffer_size=1048576000;
??????? Query OK,0 rows affected (0.00 sec)MYSQL必读

??????? mysql> create index idx_username_src on tmp_sampledata (username,src);
??????? Query OK,74352359 rows affected (7 min 13.11 sec)
??????? Records: 74352359 Duplicates: 0 Warnings: 0MYSQL必读

??????? mysql> create index idx_email_src on tmp_sampledata (email,74352359 rows affected (10 min 48.30 sec)
??????? Records: 74352359 Duplicates: 0 Warnings: 0MYSQL必读

??????? mysql> create index idx_src_username_email on tmp_sampledata(src,username,email);
??????? Query OK,74352359 rows affected (16 min 5.35 sec)
??????? Records: 74352359 Duplicates: 0 Warnings: 0MYSQL必读

??? 实际应用中,此表可能不需要这么多索引的,都建立一遍,只是为了展示一下创建的速度而已.
??? 实际应用中的效果
??? 存储的问题暂时解决到这里了,接下来经过了一系列的服务器参数调整以及查询的优化,我只能做到在这个超过9千万数据中的查询响应速度控制在1到20毫秒.听上去是个不错的成绩.但是当我们的系统有超过几百个人同时使用时,仍然显的力不从心.或许日后还有机会能更优化这个存储与查询.让我慢慢期待吧.MYSQL必读

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!