mysql性能问题_MySQL-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

mysql性能问题_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:44 PM

mysql

bitsCN.com

最近项目使用mysql数据库遇到了性能问题。单表400W以上数据时，增，删，改，查的速度都明显下降。
我们是做呼叫中心的，平均1秒钟就要处理20个呼叫，所以最最保守的计算 1秒钟也要对单表进行20次插入操作，还有更多的查询操作，所以对性能要求略高。(有点儿跑题。。。。)

遇到了问题就要解决，优化！我们优化的步骤如下(过程中遇到的困难略)：
1.优化索引，将所有sql语句，尤其是速度慢的都拿出来分析，一条一条的分析。（利用 EXPLAIN ）要让所有查询都使用到索引。
2.优化mysql数据库本身，对my.cnf（my.ini）文件进行优化。但是个人觉得效果不是很明显（请牛人指导。）
   my.cnf文件配置如下。（4核cpu 4G 内存）这个my.cnf 具体问题具体分析，配置起来也有很多事儿比较有讲究的。
所以需要逐步调试。（切忌不可大量修改后再重启mysql....有可能启动失败。）

Java代码
[client]
port=3306
[mysql]
default-character-set=utf8

[mysqld]
port=3306
basedir="D:/Program Files/MySQL/MySQL Server 5.1/"
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.1/Data/"
default-character-set=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=1000
query_cache_size=120M
table_cache=1024M
tmp_table_size=32M
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=64M
key_buffer_size=512M

max_allowed_packet = 32M
max_heap_table_size = 32M
table_open_cache = 512M
thread_concurrency = 8
innodb_lock_wait_timeout = 50
bulk_insert_buffer_size = 64M
key_cache_block_size=2048

skip-external-locking
skip-name-resolve

read_buffer_size=8M
read_rnd_buffer_size=8M

sort_buffer_size=64M

innodb_additional_mem_pool_size=20M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=8M
innodb_buffer_pool_size=512M
innodb_log_file_size=24M
innodb_thread_concurrency=10

3.分表分表是解决大数据量的最佳方案。
    我们的mysql数据库单表400万以上数据的时候就不符合性能要求了(我们对性能要求有点儿高)。但是我们一天产生的数据量就达到了单表200万。。总不能1天就建立一套表出来吧？？那1,2年以后数据库就会有600多套表（一套表有几十张呢，也就是上万张单表），看着都头疼呀。。。。。

4.从业务上解决，实现曲线救国。(这也是我们最后采用的方法，这个方法不一定适用其他业务)
   我们的业务是这样，1小时对流水数据结转一次，生成统计数据。
   最后决定生成插入流水数据的时候分2套流水表插入（一套用来保存流水数据，另一套用来结转统计数据。）
   为什么要分成2套呢？
   为了性能，用来保存流水数据的那套表就不去对它进行操作了。（起到备份数据之用。）
   用来结转成统计数据的那套流水表，在结转结束之后就会删除掉以结转的数据。
   这样一来，结转用的那套流水表的数据量就不会持续增长，也就保证了数据结转的效率。
   希望对其他人有点儿小启示。。。。。。
   请大家不要笑我。。我的mysql 是盗版的。。。没有mysql DBA给我调优。更没有人会给我订制我们专用的mysql
   只能这样曲线救国的解决了。。。。

作者“xsxjb”

bitsCN.com

Statement

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

Explain the InnoDB Buffer Pool and its importance for performance.Apr 19, 2025 am 12:24 AM

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

MySQL vs. Other Programming Languages: A ComparisonApr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

Learning MySQL: A Step-by-Step Guide for New UsersApr 19, 2025 am 12:19 AM

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

See all articles