Home Database Mysql Tutorial MySQL insert性能优化笔记

MySQL insert性能优化笔记

Jun 07, 2016 pm 05:21 PM

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长。我们就有一个业务系统,每天的

对于一些数据量较大的系统,面临的问题除了是查询效率低下,还有一个很重要的问题就是插入时间长。我们就有一个业务系统,每天的数据导入需要4-5个钟。这种费时的操作其实是很有风险的,假设程序出了问题,想重跑操作那是一件痛苦的事情。因此,提高大数据量系统的MySQL insert效率是很有必要的。

经过对MySQL的测试,发现一些可以提高insert效率的方法,供大家参考参考。

1. 一条SQL语句插入多条数据。

常用的插入语句如:

  • , 0); 
  • , 1); 
  • 修改成:

    修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个,一是减少SQL语句解析的操作, 只需要解析一次就能进行数据的插入操作,二是SQL语句较短,可以减少网络传输的IO。

    这里提供一些测试对比数据,分别是进行单条数据的导入与转化成一条SQL语句进行导入,,分别测试1百、1千、1万条数据记录。

    记录数 单条数据插入 多条数据插入

    1百 0.149s 0.011s

    1千 1.231s 0.047s

    1万 11.678s 0.218s

    linux

    Statement of this Website
    The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

    Hot Article Tags

    Notepad++7.3.1

    Notepad++7.3.1

    Easy-to-use and free code editor

    SublimeText3 Chinese version

    SublimeText3 Chinese version

    Chinese version, very easy to use

    Zend Studio 13.0.1

    Zend Studio 13.0.1

    Powerful PHP integrated development environment

    Dreamweaver CS6

    Dreamweaver CS6

    Visual web development tools

    SublimeText3 Mac version

    SublimeText3 Mac version

    God-level code editing software (SublimeText3)

    Reduce the use of MySQL memory in Docker Reduce the use of MySQL memory in Docker Mar 04, 2025 pm 03:52 PM

    Reduce the use of MySQL memory in Docker

    How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

    How do you alter a table in MySQL using the ALTER TABLE statement?

    How to solve the problem of mysql cannot open shared library How to solve the problem of mysql cannot open shared library Mar 04, 2025 pm 04:01 PM

    How to solve the problem of mysql cannot open shared library

    What is SQLite? Comprehensive overview What is SQLite? Comprehensive overview Mar 04, 2025 pm 03:55 PM

    What is SQLite? Comprehensive overview

    Run MySQl in Linux (with/without podman container with phpmyadmin) Run MySQl in Linux (with/without podman container with phpmyadmin) Mar 04, 2025 pm 03:54 PM

    Run MySQl in Linux (with/without podman container with phpmyadmin)

    How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? Mar 18, 2025 pm 12:00 PM

    How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)?

    Running multiple MySQL versions on MacOS: A step-by-step guide Running multiple MySQL versions on MacOS: A step-by-step guide Mar 04, 2025 pm 03:49 PM

    Running multiple MySQL versions on MacOS: A step-by-step guide

    How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

    How do I configure SSL/TLS encryption for MySQL connections?

    See all articles