项目实训（七）

一、

        本篇博客主要介绍存储图表文件的过程。

        图表由世界历史疫情数据得到，每个图表文件（html格式）大约为3M，共计3000多张图，大小约为11G，如何对其进行保存和获取成为一个难点。

由于以前没有过保存该类文件的经验，在这个问题上犯了很多的错误。

二、

        首先是存储过程。

        每个图表文件大小为3M，以html文件的形式存在。

        （一）、以mediumText格式保存至数据库

        一开始为了网站后端已经测试过的读取方式，选择以mediumText的方式将html源代码保存至数据库中，并以唯一的文件名指向每一张图表。

        mysql的text类型：

        mediumtext（或text、longtext），text最大支持64kb的文件，mediumtext最大支持16M的文件，longtex最大支持4g，可视情况进行设置。

        虽然mediumtext最大支持16M的文件，但在存储过程中，当文件大小大于4M之后，会出现数据丢失的问题，原因是Mysql中有一个 max_allowed_packet 的参数，执行show variables like 'max_allo%'可查看 max_allowed_packet 参数对应的参数值。

        最终发现，是因为数据库将这个参数值设置为了4M，而待更新字段的数据大小超过了4M。所以出现了数据丢失问题，只要将 max_allowed_packet 的值设置的范围大一些即可。

        保存时将文件转换为String后，base64编码，下载时将内容取出来，base64解码。 

        接下来是使用python将图表读取之后存储到数据库中：

        第一次采用每读一个文件存一次的方式：

with open(path + "\\" + file_name, 'r') as f:
    insert(file_name,f.read())

         将全部文件存入数据库需要非常长的时间，因为每读一个文件后只需一次mysql的插入操作效率很低，全部存入估计需要近一小时。

        因此修改代码，改为批量存入：

# 插入file_l(name,file)
    start_time = time.time()
    print("start_time = " + str(start_time))
    count = 0
    index = 0
    for file in tqdm(files):

        if count == 0:
            file_list = []
        # 存一千条测试.存所有数据时删除index
        if count < 10 and index < len(files) and index < 1000:
            # 检查是否是目录，不是目录才能打开
            if not os.path.isdir(file):
                file_name = file
                with open(path + "\\" + file_name, 'r') as f:
                    file_list.append((file_name, f.read()))
                    count += 1
                    index += 1
                    f.close()
        else:
            insert_many(file_list)
            count = 0
            del file_list
            gc.collect()

            if index >= 1000:
                break

    end_time = time.time()

        count计数，读取多少个文件后进行一次批量存储，index表示存入多少做测试。这里为了读文件时不把内存卡爆，我们每次存完后将保存文件的列表内存释放。

        在测试时将index设为最大，count设置为50，也就是每读50个文件批量存一次所需时间如下：

 

        全部存入只需原来的一半时间，在批量存时会存在时间消耗，但仍然需要大量时间来保存文件，这显然不合理，我们再次增大count值为100后，存入速度没有明显提升，因为此时保存速度的主要限制在于读取文件上。 

        不过由于这些图表大部分情况下也不涉及批量插入，所以我们这次将其全部插入后做一下测试：   

        下面是一些SQL执行过程：

        因为以文件名作指向文件，故在其上创建索引，文件名包含的内容为地区和图表类型（地区在前，图表类型在后），因此在查询时会用到模糊查询like：

        第一次查询，like ‘查询%’的形式，执行情况很快，取出某个国家的所有图表

        执行情况如下，使用到了索引，仅仅遍历了所需的数据。

 

        第二次查询，like ‘%查询’的形式 ，此时出现问题，sql执行过程非常慢，因为需要对每个file_name进行一次匹配

 

         查看执行计划，type：ALL，执行了全表扫描，因此导致查询速度缓慢。

 

          第三次查询，采用 like ‘%查询%’的形式，返回21条数据，不管是查询速度，还是fetch的速度都很慢，查看执行计划后发现仍然是进行了全表扫描。

        那么这是怎么回事呢，由上面查看执行计划也能得出：

    type 为 range 表示这是一个索引的范围扫描，Extra 为 Using index condition 表示用到了索引，但是还需要进行过滤。这样也就证明了使用 like x% 查询是可以用得到索引的。

       type为ALL 使用了全表扫描，证明了使用 like %x% 查询是用不到索引的，使用 like %x 查询也是用不到索引的。

        因为索引是一种有序的 B+ Tree 数据结构，叶子节点都是按照顺序从左向右排的，如果使用 like %x% 和 like %x 查询的话，不知道开头是哪个，就会去进行全表扫描。

        那证明单纯的使用索引无法优化取的速度，因为这是设计上的问题，那我们根据文件名中的内容，将文件名划分为两个字段，一个字段代表国家（地区），另一个字段代表 图表类型，建立表file_m，进行对比测试。

        在添加索引前根据国家查询图表文件，可见执行了全表扫描：

         先在表上对两列分别建立单列索引：

         建立索引后，对单个国家进行查询，使用索引后仅遍历对应字段，查询速度大大增加。                

        但是我们大部分查询都要同时使用这两个字段，所以我们建立组合索引。

        多列索引的列顺序至关重要，如何选择索引的列顺序有一个经验法则:将选择性最高的列放到索引最前列（但是不是绝对的）。

        因为这里我们页面上必定是由某个国家的某个图表的查询方式，也就是说在查询中，国家这个字段更重，更容易在查询中使用，因此建立组合索引的顺序为（region，type），国家字段在前。

        下面我们展示一下测试之前的表结构（以文件名作为唯一指向），和后来的表结构（国家和表类型分开）查询的差异：

        根据类型查询，可以看到语句的执行速度有明显差异，因为一个通过索引直接找到，另一个需遍历全表并进行匹配。但是这里出现了另一个问题，那就是整个查询过程主要的时间消耗出现在了fetch time上，也就是取数据，这里也能理解，因为这里返回50行数据，也就是需要读取50个3M的字符串，这是很低效的，我们迫切需要另一种保存文件的方式，否则查询非常的慢。

 

 

        优化到了这里，在sql语句的执行上已经基本没有优化空间，因为不需要联表查询，表结构也并不复杂，那么主要问题在于取出文件的时间上。

        由于以前没有保存 大文件的经验，到这里我们选择是依然是mediumtext，以文本的方式存放一个网页，这样做其实问题非常大，首先是读的时候会将该字段上整个字符串取出，这样导致IO很大，有很大的时间开销，同时有将内存读满的危险，因为所有文件大小综合为10多个g。

        针对取出整个字符串很慢这个问题，尝试换一种存储文件的方式，也就是blob，以二进制形式保存文件，因为本质上我们要保存的是html文件而不是字符串形式的源代码。

        最后其实也没有采用二进制文件的保存方式，而是使用一种更加简便的方式，也就是不将文件直接保存至数据库中，因为这样会导致取出数据时IO负担较重。我不再读取文件再将它存入数据库，而是只向数据库中存入文件的保存路径，而文件本身仍然保存在磁盘上，在读的时候简单取出文件的路径，再根据文件路径去读入这个文件才是最合理的方式，这样5000多条数据完全不会有存取速度上的问题，将时间问题转移至按路径寻找文件并读取上。

        到这里保存图表文件的过程已经结束。

---

        这里是介绍一下我在这个过程中遇到的一点问题，因为将文件以mediumtext保存入数据库时，会将10个g的文件全部存入，而我有又进行了数次的存入删除测试，导致生成的日志文档非常大，直接将我的硬盘空间占满，导致mysql根本无法启动，所以我也就难以使用正常方式删除。

        在删除之前存入的文件时使用delete，InnoDB 数据库在使用 delete 进行删除操作的时候，只会将已经删除的数据标记为删除，并没有把数据文件删除，因此并不会彻底的释放空间。这些被删除的数据会被保存在一个链接清单中，当有新数据写入的时候，MySQL 会重新利用这些已删除的空间进行再写入。

        因此操作不会减少数据或索引所占用的空间。

       所以为了能让mysql启动，我只能手动进入mysql的保存数据的data目录下对日志文件binlog.****进行删除。

        但是删除后发现仍然不能启动mysql，经过检查发现其实不能简单的删除日志文件，还要讲同目录下的binlog.index文件（保存的是日志文件名称表，若是这个映射表与实际日志文件无法对应，会导致mysql无法正常的启动）中已经删除那些日志文件名，在binlog中也删除掉。

        注意：永远不要直接对mysql目录下的文件进行操作，这是一个非常不好的习惯，因为一不小心就会导致mysql出错，而这些错误很难发现。