php处理上百万条的数据库如何提高处理查询速度(小白数据库优化)

本文提供了30条关于如何优化PHP应用程序中的数据库查询速度的具体建议,包括避免全表扫描、合理使用索引、优化WHERE子句等,帮助开发者提高处理上百万条数据时的查询效率。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

php 处理上百万条的数据库如何提高处理查询速度

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20

5.in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描:
select id from t where name like '%abc%'
若要提高效率,可以考虑全文检索。

7.如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num

8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)='abc'--name以abc开头的id
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30'生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(...)

13.很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

15.索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。


18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27.与临时表一样,游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

/** * @notes 京东收派服务费明细导入(优化版本) * @notes 技术核心优势在于通过分块读取和分批处理,将原本可能导致内存溢出的大数据导入任务,转化为多个小而可控的操作单元,从而在处理超大型 Excel 文件时保持稳定性 * @notes 几十上百万的excel表格可能导入效率会慢 但是很稳定 不至于报错502或者超时等错误... * @notes 如果遇到上百万的excel表格请同步设置nginx的如下配置: * 代理相关超时配置(2个小时或者更长) * proxy_connect_timeout 7200; * proxy_read_timeout 7200; * proxy_send_timeout 7200; * fastcgi相关超时配置(2个小时或者更长) * fastcgi_connect_timeout 7200; * fastcgi_read_timeout 7200; * fastcgi_send_timeout 7200; * 可以确保虽然慢但是稳定的导入! * @return Json * @author 胡军 */ public function importExcel(): Json { // 设置脚本执行时间无限制,避免大数据处理超时 set_time_limit(0); // 设置脚本最大内存使用量为2GB,处理大文件时避免内存溢出 ini_set('memory_limit', '2048M'); // 获取请求参数(包含上传的文件信息) $params = $this->request->param(); // 验证文件是否上传 if (empty($params["file"])) { return $this->fail('请上传要导入文件'); } // 定义Excel表头与数据库字段的映射关系 $titleArr = [ '商家编号' => 'merchant_code', '业务单号' => 'business_order_number', '平台订单号' => 'platform_order_number', '商家订单号' => 'merchant_order_number', '退回件关联运单号' => 'return_waybill_number', '店铺名称' => 'shop_name', '下单时间' => 'order_time', '始发省' => 'origin_province', '始发市' => 'origin_city', '目的省' => 'destination_province', '目的市' => 'destination_city', '计费重量' => 'billing_weight', '实际重量(kg)' => 'actual_weight_kg', '实际体积(cm3)' => 'actual_volume_cm3', '首重单价' => 'first_weight_price', '续重单价' => 'continued_weight_price', '包裹/耗材数量' => 'package_material_quantity', '销售平台订单号' => 'sales_platform_order_number', '费用类型' => 'fee_type', '原始金额' => 'original_amount', '折扣/促销金额' => 'discount_promotion_amount', '计费时间' => 'billing_time', '结算金额' => 'settlement_amount', '签收时间' => 'sign_time', '商品编码' => 'product_code', '商品名称' => 'product_name' ]; try { // 构建上传文件的完整路径 $path = app()->getRootPath() . "public/" . $params['file']; // 1. 创建Excel读取器并设置为只读取数据模式(优化内存) $reader = IOFactory::createReaderForFile($path); $reader->setReadDataOnly(true); // 2. 获取工作表基本信息(总行数和最大列字母),避免加载整个文件 $worksheetInfo = $reader->listWorksheetInfo($path); $totalRows = $worksheetInfo[0]['totalRows']; $highestColumn = $worksheetInfo[0]['lastColumnLetter']; // 3. 读取表头行(仅加载第一行数据) $chunkFilter = new ChunkReadFilter(); $chunkFilter->setRows(1, 1); $reader->setReadFilter($chunkFilter); $spreadsheet = $reader->load($path); $sheet = $spreadsheet->getSheet(0); $title = $sheet->rangeToArray('A1:' . $highestColumn . '1', null, true, true, true)[1]; // 释放不再使用的Spreadsheet对象,减少内存占用 $spreadsheet->disconnectWorksheets(); unset($spreadsheet); // 检查表头是否存在 if (empty($title)) { throw new \Exception('Excel表头为空'); } // 初始化数据库模型和计数器 $model = new DeliveryPrimaryRegionsItemizationModel(); //TODO:根据自身mysql所在服务的性能来控制批量插入大小 不然报错或者导入很慢 该值需要反复测验找到最合适的大小 $insertBatchSize = 2000; // 数据库插入批次大小 $total = 0; // 总记录数 $success = 0; // 成功处理记录数 $failedBatches = []; // 失败批次记录 $batchData = []; // 批次数据临时存储 $processedRows = 0; // 已处理行数 // 4. 分块处理Excel数据,每次处理$chunkSize行 //TODO:根据自身服务器性能来决定每次分块读取excel表格数据的数 不然报错或者导入很慢 该值需要反复测验找到最合适的大小 $chunkSize = 5000; // 每次加载5000行 $chunkFilter = new ChunkReadFilter(); $reader->setReadFilter($chunkFilter); // 循环处理每个数据块 for ($startRow = 2; $startRow <= $totalRows; $startRow += $chunkSize) { $endRow = min($startRow + $chunkSize - 1, $totalRows); $chunkFilter->setRows($startRow, $chunkSize); // 5. 加载当前数据块 $spreadsheet = $reader->load($path); $sheet = $spreadsheet->getSheet(0); // 6. 处理当前块中的每一行数据 for ($row = $startRow; $row <= $endRow; $row++) { // 获取当前行数据 $rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true)[$row]; $item = []; // 将Excel列名映射为数据库字段名 // 将Excel列名映射为数据库字段名 foreach ($rowData as $colIndex => $value) { $colName = $title[$colIndex] ?? ''; if ($colName && isset($titleArr[$colName])) { $field = $titleArr[$colName]; $rawValue = trim($value ?? ''); // 原始值去除首尾空格 // 处理decimal类型字段 $decimalFields = [ 'billing_weight', 'theoretical_weight', 'weight_rounded', 'weight_difference', 'actual_weight_kg', 'actual_volume_cm3', 'first_weight_price', 'continued_weight_price', 'original_amount', 'discount_promotion_amount', 'settlement_amount', 'verification_difference', 'theoretical_billing', 'theoretical_difference', 'non_cross_warehouse_fee', 'cross_warehouse_difference', 'continued_weight_price2' ]; if (in_array($field, $decimalFields)) { // 空值处理:空字符串/空格 → 转为null(需数据库允许null)或0 if ($rawValue === '') { $item[$field] = null; // 若数据库不允许null,可改为0 } // 非数值处理:过滤为0(或记录错误日志) elseif (!is_numeric($rawValue)) { // 可选:记录错误行号和内容,方便排查 // $this->recordError($row, $colName, "非数值内容: {$rawValue}"); $item[$field] = 0; } // 数值格式化:保留2位小数(根据数据库字段定义调整) else { $item[$field] = round((float)$rawValue, 2); } } // 其他字段保持原处理(如字符串、整数等) else { $item[$field] = $rawValue; } } } // print_r($item);die; // 如果行数据不为空,则添加到批次数据中 if (!empty($item)) { $batchData[] = $item; $processedRows++; } // 7. 当批次数据达到插入批次大小时,执行数据库插入操作 if (count($batchData) >= $insertBatchSize) { $this->processBatch($model, $batchData, $success, $failedBatches, $total); $batchData = []; // 清空批次数据 } $total++; // 总记录数递增 } // 8. 释放当前数据块占用的内存 $spreadsheet->disconnectWorksheets(); unset($spreadsheet); gc_collect_cycles(); // 触发垃圾回收 // 9. 输出处理进度信息 $progress = round(($endRow / $totalRows) * 100, 2); echo "处理进度: {$progress}% | 已处理: {$processedRows} 行\n"; } // 10. 处理剩余不足一个批次的数据 if (!empty($batchData)) { $this->processBatch($model, $batchData, $success, $failedBatches, $total); } // 返回处理结果 if (empty($failedBatches)) { $resultMsg = "导入成功,共{$total}数据,成功导入{$success}"; return $this->success($resultMsg); } else { $errorCount = $total - $success; $errorMsg = "失败 {$errorCount} ,第一个错误: {$failedBatches[0]['error']}"; return $this->fail('导入失败', ['remark' => $errorMsg]); } } catch (\Exception $e) { // 异常处理,返回错误信息 return $this->fail('导入过程中出错: ' . $e->getMessage()); } } /** * 处理数据批次(优化SQL构建) */ private function processBatch($model, &$batchData, &$success, &$failedBatches, &$total) { // 如果批次数据为空则直接返回 if (empty($batchData)) return; try { // 使用参数绑定构建SQL语句,防止SQL注入 $tableName = $model->getTable(); $fields = array_keys($batchData[0]); $fieldList = '`' . implode('`, `', $fields) . '`'; $placeholders = '(' . implode(',', array_fill(0, count($fields), '?')) . ')'; $sql = "INSERT INTO {$tableName} ({$fieldList}) VALUES "; $values = []; $params = []; // 构建批量插入的SQL语句和参数数组 foreach ($batchData as $row) { $values[] = $placeholders; foreach ($fields as $field) { $params[] = $row[$field] ?? null; } } $sql .= implode(',', $values); // 执行带参数绑定的SQL语句 // 一定不要使用模型去操作数据库 不然效率很低 原生的最好! Db::execute($sql, $params); // 更新成功记录数 $success += count($batchData); // 清空批次数据 $batchData = []; } catch (\Exception $e) { // 记录失败批次信息 $failedBatches[] = [ 'startRow' => $total - count($batchData) + 1, 'endRow' => $total, 'error' => $e->getMessage() ]; // 出错时清空批次数据,避免重复处理 $batchData = []; } } /** * 分块读取过滤器 * 用于控制只读取Excel文件中的指定行范围 * common.php代码不规范导致本地git有差异直接还原 直接写在控制器里算了! */ class ChunkReadFilter implements \PhpOffice\PhpSpreadsheet\Reader\IReadFilter { private $startRow = 0; private $endRow = 0; // 设置要读取的起始行和行数 public function setRows($startRow, $chunkSize) { $this->startRow = $startRow; $this->endRow = $startRow + $chunkSize; } // 判断当前单元格是否应该被读取 public function readCell($column, $row, $worksheetName = '') { // 只读取指定范围内的行 if ($row >= $this->startRow && $row <= $this->endRow) { return true; } return false; } } 我需要导入100万的数据,并且excel表格当中26个字段 每个字段都特别的长,导入的时候总是报错502,字段少的时候就没事,请为我分析具体原因,另外请帮我针对问题进行代码优化,要求提供完整的代码哈 不要忘记注释信息
最新发布
07-09
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值