如何向数据库快速的写入百万条数据

如何向数据库快速的写入百万条数据

成都创新互联公司专注于七里河网站建设服务及定制，我们拥有丰富的企业做网站经验。 热诚为您提供七里河营销型网站建设，七里河网站制作、七里河网页设计、七里河网站官网定制、成都微信小程序服务，打造七里河网络公司原创品牌,更为您提供七里河网站排名全网营销落地服务。

现在我的处理方式是读取原数据库表的所有数据，大概有一百万条吧，然后放到一个数组里面，循环向目标数据库写数据（两个数据库之间的数据迁移，表结构不同，我是写的php脚本来查询导入的），一开始速度还可以，但是现在几乎一秒钟一条了

a数据库中有几百万条数据，用php从a数据库中取出数据进行处理，速度很慢。

本身从几百万数据中查1000条就慢,你还用for去循环这个过程 不慢就怪了

Es实现百万级数据快速检索

在用户点击一篇采购文章，会匹配到该文章全部相关内容。所有数据是存在ES中的，百万量级。恩~要用python写一个接口。通过查找资料，通过 ES模糊搜索 可以实现。

prefix的匹配一般是处理不分词的场景，将会匹配articleID中以”J”开头的doc。prefix不会计算revelance score，只是作一个过滤的操作，和filter唯一的区别是filter会缓存结果，而prefix不会。前缀越短要处理的doc越多，性能越差。

?会匹配任意字符，*会匹配0个或多个字符。性能根prefix一样差，必须要扫描整个倒排索引。

[0-9]：指定范围内的数字

[a-z]：指定范围内的字幕

.：一个字符

+：前面的正则表达式可以出现一次或多次

正则的搜索同样会扫描全表，性能也会很差

fuzziness参数调整纠正的次数

通常不会直接用上述搜索，而会用下面的搜索：

在es中，使用组合条件查询是其作为搜索引擎检索数据的一个强大之处，在前几篇中，简单演示了es的查询语法，但基本的增删改查功能并不能很好的满足复杂的查询场景，比如说我们期望像mysql那样做到拼接复杂的条件进行查询该如何做呢？es中有一种语法叫bool，通过在bool里面拼接es特定的语法可以做到大部分场景下复杂条件的拼接查询，也叫复合查询

首先简单介绍es中常用的组合查询用到的关键词，

filter:过滤，不参与打分

must:如果有多个条件，这些条件都必须满足 and与

should:如果有多个条件，满足一个或多个即可 or或

must_not:和must相反，必须都不满足条件才可以匹配到 ！非

发生 描述

must

该条款（查询）必须出现在匹配的文件，并将有助于得分。

filter

子句（查询）必须出现在匹配的文档中。然而不像 must查询的分数将被忽略。Filter子句在过滤器上下文中执行，这意味着评分被忽略，子句被考虑用于高速缓存。

should

子句（查询）应该出现在匹配的文档中。如果 bool查询位于查询上下文中并且具有mustor filter子句，则bool即使没有should查询匹配，文档也将匹配该查询 。在这种情况下，这些条款仅用于影响分数。如果bool查询是过滤器上下文 或者两者都不存在，must或者filter至少有一个should查询必须与文档相匹配才能与bool查询匹配。这种行为可以通过设置minimum_should_match参数来显式控制 。

must_not

子句（查询）不能出现在匹配的文档中。子句在过滤器上下文中执行，意味着评分被忽略，子句被考虑用于高速缓存。因为计分被忽略，0所有文件的分数被返回。

下面用实验演示一下上述查询的相关语法，

1、首先，我们创建一个索引，并且在索引里添加几条数据，方便后面使用，

我这里直接批量插入数据，也可以通过PUT的语法单条执行插入，

POST /forum/article/_bulk

{ "index": { "_id": 1 }}

{ "articleID" : "XHDK-A-1293-#fJ3", "userID" : 1, "hidden": false, "postDate": "2019-07-01","title":"java contains hadoop and spark","topic":"java" }

{ "index": { "_id": 2 }}

{ "articleID" : "KDKE-B-9947-#kL5", "userID" : 1, "hidden": false, "postDate": "2019-07-02",title":"php contains admin","topic":"java and php" }

{ "index": { "_id": 3 }}

{ "articleID" : "JODL-X-1937-#pV7", "userID" : 2, "hidden": false, "postDate": "2019-07-03" ,title":"spark is new language","topic":"spark may use java"}

{ "index": { "_id": 4 }}

{ "articleID" : "QQPX-R-3956-#aD8", "userID" : 2, "hidden": true, "postDate": "2019-07-04" ,title":"hadoop may involve java","topic":"big data used"}

或者使用put语法

PUT /forum/article/4

{

"articleID": "QQPX-R-3956-#aD8",

"userID": 2,

"hidden": true,

"postDate": "2019-07-04",

"title": "hadoop may involve java",

"topic": "big data used"

}

4条数据插入成功，

2、termQuery,term查询不分词，类似于mysql的where filedName = ? 语法，即精准匹配，比如我们查询articleID = XHDK-A-1293-#fJ3的这条数据，

GET /forum/article/_search

{

"query": {

"term": {

"articleID.keyword":"XHDK-A-1293-#fJ3"

}

}

}

2、must查询，即查询的条件中必须匹配的字段，例如，查询title中必须包含java的数据，

GET /forum/article/_search

{

"query": {

"bool": {

"must": [

{"term":{"title":"hadoop"}}

]

}

}

}

查出两条数据

如果是should呢？如下语法，即查询title中包含hadoop或者topic中包含spark，二者满足其一即可，

GET /forum/article/_search

{

"query": {

"bool": {

"should": [

{"term":{"title":"hadoop"}},

{"term": {"topic": "spark"}}

]

}

}

}

查询出3条数据，

must和should结合使用，

最后再来一个比较复杂的嵌套查询，我们先看一下这条sql语句，

select *

from forum.article

where article_id=‘XHDK-A-1293-#fJ3’

or (article_id=‘JODL-X-1937-#pV7’ and post_date=‘2017-01-01’)，

对应着转化为es的复合查询语法是怎样的呢？拆分来看，就是一个should语句的嵌套，

GET /forum/article/_search

{

"query": {

"bool": {

"should": [

{

"term": {

"articleID.keyword": "XHDK-A-1293-#fJ3"

}

},

{

"bool": {

"must": [

{

"term": {

"articleID.keyword":"JODL-X-1937-#pV7"

}

},

{

"term": {

"postDate":"2019-07-01"

}

}

]

}

}

}

}

查询到一条结果，按照这种思路，如果我们对一个复杂的查询不知道如何构建查询语句时，可以考虑先按照sql的语法进行拆分，然后再组织es查询语句是个不错的突破口，

到这里，可能我们会有疑问，复合条件中的term查询和单纯的match区别在哪里呢？既然都是查询，究竟原理有何不同呢？

我们知道match query是需要全文检索的，是进行full text的全文检索，当然如果搜索的字段值做了not_analyzed，match query也相当于是term query了，比如下面这个搜索，由于在插入数据的时候我们没有对title这个字段进行规定，默认就是text类型的，会被自动分词，这样查询的时候只要title中包含了 hadoop，就可以匹配到，

GET /forum/article/_search

{

"query": {

"match": {

"title": "hadoop"

}

}

}

2、有些情况下，假如我们直接使用match进行查询，又希望查出来的结果尽可能是我们期望的包含更多关键词的结果，则在match进行匹配的时候可以添加其他的条件，以便提升结果的匹配精确度，

GET /forum/article/_search

{

"query": {

"match": {

"title": {

"query": "java hadoop",

"operator": "and"

}

}

}

}

这样匹配出来的结果包含了更多我们期望的关键词，即query中可以指定我们查询的结果中包含的关键词，

es还有其他的语法达到上述的效果，minimum_should_match ，通过这个语法，可以指定匹配的百分数，就是查询的关键词至少要达到的百分数，下面这个表示全部匹配，只查询到一条结果，

假如我们将百分数调低点，比如75%，可以看到查到两条结果，

3、当然，我们也可以将bool和match结合起来使用，如下，

GET /forum/article/_search

{

"query": {

"bool": {

"must": [

{"match": {"title": "java"}}

],

"must_not": [

{ "match": { "title": "spark"}}

]

, "should": [

{

"match": {

"title": "php"

}

}

]

}

}

}

通过这种方式，也可以达到更精准的匹配我们期望的查询结果，

简单总结来说，当我们使用match进行查询的时候，如果查询的field包含多个词，比如像下面这个，

{

"match": { "title": "java elasticsearch"}

}

其实es会在底层自动将这个match query转换为bool的语法bool should，指定多个搜索词，同时使用term query，则转化后的语法如下，

{

"bool": {

"should": [

{ "term": { "title": "java" }},

{ "term": { "title": "elasticsearch" }}

]

}

}

而上面所说的match中加and的查询，对应于bool查询，转化后为 term+must 的语法如下，

{

"match": {

"title": {

"query": "java elasticsearch",

"operator": "and"

}

}

}

{

"bool": {

"must": [

{ "term": { "title": "java" }},

{ "term": { "title": "elasticsearch" }}

]

}

}

对于minimum_should_match这种语法来说，道理类似，

{

"match": {

"title": {

"query": "java elasticsearch hadoop spark",

"minimum_should_match": "75%"

}

}

}

{

"bool": {

"should": [

{ "term": { "title": "java" }},

{ "term": { "title": "elasticsearch" }},

{ "term": { "title": "hadoop" }},

{ "term": { "title": "spark" }}

],

"minimum_should_match": 3

}

}

我们来看一个具体的操作实例，也就是说必须至少包含3个关键词的数据才会出现在搜索结果中，

3、在搜索中，我们有这样一种需求，期望搜索的结果中包含java 如果标题中包含hadoop或spark就优先搜索出来,同时呢，如果一个帖子包含java hadoop，一个帖子包含java spark，包含hadoop的帖子要比spark优先搜索出来,

对于这样的需求，通俗来讲，就是需要通过增大某些搜索条件的权重，从而在搜索的结果中，更多符合和满足我们业务场景的数据靠前搜索出来，在es中可以通过boost关键词来增加搜索条件的权重，

GET /forum/article/_search

{

"query": {

"bool": {

"must": [

{

"match": {

"title": "java"

}

}

],

"should": [

{

"match": {

"title": {

"query": "hadoop"

}

}

},

{

"match": {

"title": {

"query": "spark",

"boost":2

}

}

},

{

"match": {

"title": {

"query": "php"

}

}

},

{

"match": {

"title": {

"query": "hadoop",

"boost": 5

}

}

}

]

}

}

}

上面这个例子意思是我们赋予搜索的title中包含hadoop的条件权重更大，hadoop的结果会有限被搜索出来

4、dis_max语法，也叫best_field,在某些情况下，假如我们在bool查询中用多个字段进行查询，但是查询一样，就可能导致说查询出来的结果并不是按照我们期望的那个字段将其排在前面，也就是说，我们只需要包含指定字段的内容展示在前面，如下，

GET /forum/article/_search

{

"query": {

"bool": {

"should": [

{ "match": { "title": "java solution" }},

{ "match": { "content": "java solution" }}

]

}

}

}

title和content的搜索条件相同，但我们希望的是结果中title 包含java solution的靠前展示，但直接这样查询可能达不到预期的效果，如果使用dis_max进行拼接就可以了，

GET /forum/article/_search

{

"query": {

"dis_max": {

"queries": [

{ "match": { "title": "java solution" }},

{ "match": { "content": "java solution" }}

]

}

}

}

通过这样的方式，使得查询的结果更符合预期值，

5、但是使用dis_max，只取某一个query最大的分数，完全不考虑其他query的分数，即假如说某个结果中包title含了java，但topic中没有包含java，另一却是相反，还有的结果是两者都包含java，在dis_max语法下，只会拿到相关度得分最高的那一个，而不会考虑其他的结果，这时，如果需要获取其他的title或者topic包含java的结果，可以使用tie_breaker进一步包装，如下，

GET /forum/article/_search

{

"query": {

"dis_max": {

"queries": [

{ "match": { "title": "spark" }},

{ "match": { "topic": "java"}}

],

"tie_breaker": 0.6

}

}

}

这样查到3条结果，综合来说，最终还是需要结合实际业务场景进行使用，但是在大多数情况相爱，我们还是希望搜索的结果中是按照我们给定的条件包含更多的关键词的内容被优先搜索出来，

php写个循环往mysql数据库插入100w条数据，每次插入几万条就自动停止了。

有两种情况, 一是 php可运行的最大时间是不是有设置. 虽然你加了set_time_limit(0). 但很有可能没设置成功,因为php有安全模式, 你可以修改php.ini

2是系统资源不够, 可能是php宕了或者 数据库宕了.

Python/PHP MySQL语句解析器解决业务分表

自己曾经做过一个网盘项目。刚开始由于需要快速地从0到1建设上线，所以没有对核心文档表进行分表。当然我的架构理念也是“按需架构设计”。产品需求在没有明确的长远计划的情况下以“小步快跑，赶超竞品”为主。后期由于产品功能触达目标用户群需求点、产品用户体验不断提升、产品多方位导流、加强产品推广文档表每天有百万数据增长量。不得不对文档表进行按用户id分表。当时产品功能已全覆盖文档的生命周期。产品功能已丰富多彩。修改所有关联文档表的业务代码为按用户id分表开发测试成本非常高。上线后线上问题不可控。经过考虑在业务代码最底层DB层进行SQL语句解析来进行用户id分表处理。这样的话开发测试成本都非常低。上线后有问题方便回滚和追查原因。

今天为大家介绍Python/PHP两种MySQL语句解析器。当时网盘项目用的是PHP编程语言开发。

Python的SQL语句解析器 。个人推荐使用moz_sql_parser库。经调研官方的sqlparse库解析出来的语句段无法满足需求也很难理解。

1、Python moz_sql_parser库安装

2、Python moz_sql_parser SQL语句解析

3、Python moz_sql_parser总结

PHP的SQL语句解析器。 个人推荐使用PhpMyAdmin的sql-parser组件。PhpMyAdmin是经过 历史 检验可信赖的。

1、PHP PhpMyAdmin/sql-parser安装

2、PHP PhpMyAdmin/sql-parser SQL语句解析

3、PHP PhpMyAdmin/sql-parser总结

大家有什么问题可以发评论沟通。

php+mysql优化，百万至千万级快速分页mysql性能到底能有多高

php+Mysql 优化，百万至千万级快速分页

MySql 性能到底能有多高？用了php半年多，真正如此深入的去思考这个问题还是从前天开始。有过痛苦有过绝望，到现在充满信心！MySql 这个数据库绝对是适合dba级的高手去玩的，一般做一点1万篇新闻的小型系统怎么写都可以，用xx框架可以实现快速开发。可是数据量到了10万，百万至千万，他的性能还能那么高吗？一点小小的失误，可能造成整个系统的改写，甚至更本系统无法正常运行！好了，不那么多废话了。用事实说话，看例子：

数据表 collect ( id, title ,info ,vtype) 就这4个字段，其中 title 用定长，info 用text, id 是逐渐，vtype是tinyint，vtype是索引。这是一个基本的新闻系统的简单模型。现在往里面填充数据，填充10万篇新闻。

最后collect 为 10万条记录，数据库表占用硬盘1.6G。OK ,看下面这条sql语句：

select id,title from collect limit 1000,10; 很快；基本上0.01秒就OK，再看下面的

select id,title from collect limit 90000,10; 从9万条开始分页，结果？

8-9秒完成，my god 哪出问题了？？？？其实要优化这条数据，网上找得到答案。看下面一条语句:

select id from collect order by id limit 90000,10; 很快，0.04秒就OK。为什么？因为用了id主键做索引当然快。网上的改法是：

select id,title from collect where id=(select id from collect order by id limit 90000,1) limit 10;

这就是用了id做索引的结果。可是问题复杂那么一点点，就完了。看下面的语句

select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了8-9秒！

到了这里我相信很多人会和我一样，有崩溃感觉！vtype 做了索引了啊？怎么会慢呢？vtype做了索引是不错，你直接 select id from collect where vtype=1 limit 1000,10; 是很快的，基本上0.05秒，可是提高90倍，从9万开始，那就是0.05*90=4.5秒的速度了。和测试结果8-9秒到了一个数量级。从这里开始有人提出了分表的思路，这个和discuz 论坛是一样的思路。思路如下：

建一个索引表： t (id,title,vtype) 并设置成定长，然后做分页，分页出结果再到 collect 里面去找info 。 是否可行呢？实验下就知道了。

10万条记录到 t(id,title,vtype) 里，数据表大小20M左右。用

select id from t where vtype=1 order by id limit 90000,10; 很快了。基本上0.1-0.2秒可以跑完。为什么会这样呢？我猜想是因为collect 数据太多，所以分页要跑很长的路。limit 完全和数据表的大小有关的。其实这样做还是全表扫描，只是因为数据量小，只有10万才快。OK，来个疯狂的实验，加到100万条，测试性能。

加了10倍的数据，马上t表就到了200多M，而且是定长。还是刚才的查询语句，时间是0.1-0.2秒完成！分表性能没问题？错！因为我们的limit还是9万，所以快。给个大的，90万开始

select id from t where vtype=1 order by id limit 900000,10; 看看结果，时间是1-2秒！

why 　 分表了时间还是这么长，非常之郁闷！有人说定长会提高limit的性能，开始我也以为，因为一条记录的长度是固定的，mysql 应该可以算出90万的位置才对啊？ 可是我们高估了mysql 的智能，他不是商务数据库，事实证明定长和非定长对limit影响不大？怪不得有人说 discuz到了100万条记录就会很慢，我相信这是真的，这个和数据库设计有关！

难道MySQL 无法突破100万的限制吗？？？到了100万的分页就真的到了极限？？？

答案是： NO !!!! 为什么突破不了100万是因为不会设计mysql造成的。下面介绍非分表法，来个疯狂的测试！一张表搞定100万记录，并且10G 数据库，如何快速分页！

好了，我们的测试又回到 collect表，开始测试结论是： 30万数据，用分表法可行，超过30万他的速度会慢道你无法忍受！当然如果用分表+我这种方法，那是绝对完美的。但是用了我这种方法后，不用分表也可以完美解决！

答案就是：复合索引！有一次设计mysql索引的时候，无意中发现索引名字可以任取，可以选择几个字段进来，这有什么用呢？开始的select id from collect order by id limit 90000,10; 这么快就是因为走了索引，可是如果加了where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。然后测试

select id from collect where vtype=1 limit 90000,10; 非常快！0.04秒完成！

再测试: select id ,title from collect where vtype=1 limit 90000,10; 非常遗憾，8-9秒，没走search索引！

再测试：search(id,vtype)，还是select id 这个语句，也非常遗憾，0.5秒。

综上：如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

完美解决了分页问题了。可以快速返回id就有希望优化limit ， 按这样的逻辑，百万级的limit 应该在0.0x秒就可以分完。看来mysql 语句的优化和索引时非常重要的！

好了，回到原题，如何将上面的研究成功快速应用于开发呢？如果用复合查询，我的轻量级框架就没的用了。分页字符串还得自己写，那多麻烦？这里再看一个例子，思路就出来了：

select * from collect where id in (9000,12,50,7000); 竟然 0秒就可以查完！

mygod ，mysql 的索引竟然对于in语句同样有效！看来网上说in无法用索引是错误的！

有了这个结论，就可以很简单的应用于轻量级框架了：

代码如下：

$db=dblink();

$db-pagesize=20;

$sql="select id from collect where vtype=$vtype";

$db-execute($sql);

$strpage=$db-strpage(); //将分页字符串保存在临时变量，方便输出

while($rs=$db-fetch_array()){

　 $strid.=$rs['id'].',';

}

$strid=substr($strid,0,strlen($strid)-1); //构造出id字符串

$db-pagesize=0; //很关键，在不注销类的情况下，将分页清空，这样只需要用一次数据库连接，不需要再开；

$db-execute("select id,title,url,sTime,gTime,vtype,tag from collect where id in ($strid)");

php while($rs=$db-fetch_array()):

tr

　 td$amp;amp;$amp;nbsp; php echo $rs['id']; $amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp; php echo $rs['url']; $amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp; php echo $rs['sTime']; $amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp; php echo $rs['gTime']; $amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp; php echo $rs['vtype']; $amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp;a act=showid= php echo $rs['id']; $amp;quot;$ target="_blank"$amp;amp;$lt; php echo $rs['title']; $amp;amp;$lt;/a$amp;amp;$lt;/td

　 td$amp;amp;$amp;nbsp; php echo $rs['tag']; $amp;amp;$lt;/td

/tr

php endwhile;

/table

php

echo $strpage;

通过简单的变换，其实思路很简单：1）通过优化索引，找出id，并拼成 "123,90000,12000" 这样的字符串。2）第2次查询找出结果。

小小的索引+一点点的改动就使mysql 可以支持百万甚至千万级的高效分页！

通过这里的例子，我反思了一点：对于大型系统，PHP千万不能用框架，尤其是那种连sql语句都看不到的框架！因为开始对于我的轻量级框架都差点崩溃！只适合小型应用的快速开发，对于ERP,OA，大型网站，数据层包括逻辑层的东西都不能用框架。如果程序员失去了对sql语句的把控，那项目的风险将会成几何级数增加！尤其是用mysql 的时候，mysql 一定需要专业的dba 才可以发挥他的最佳性能。一个索引所造成的性能差别可能是上千倍！

PS: 经过实际测试，到了100万的数据，160万数据，15G表，190M索引，就算走索引，limit都得0.49秒。所以分页最好别让别人看到10万条以后的数据，要不然会很慢！就算用索引。经过这样的优化，mysql到了百万级分页是个极限！但有这样的成绩已经很不错，如果你是用sqlserver肯定卡死！而 160万的数据用 id in (str) 很快，基本还是0秒。如果这样，千万级的数据，mysql应该也很容易应付。

网页标题：百万数据php 百万数据排序
路径分享：http://lswzjz.com/article/hhchph.html