Mysql optimization, mysql

Source: Internet
Author: User
Tags mysql query optimization database sharding

Mysql optimization, mysql

Mysql optimization (in linux)

Method 1: Add SQL logs to locate slow queries

1. First, add timeout control in the mysql configuration file, and add 2 S timeout under mysqld:

Vi/etc/my. cnf

2. Restart mysql, set log to on, and view slow_query_log to on.

3. Execute an SQL statement that has been queried for 2 seconds, view the log file, and view the following records. Then we can optimize it through logs.

Method 2: Create a partition table.

When a project starts, it generally does not create a partition table. When the project runs for a period of time and you locate a table with a large amount of data, you can consider partitions: RANGE, LIST, HASH, KEY. In actual situations, partitions are generally based on the time range. The following is the SQL statement for adding partitions by month to the table.

Alter tableWD_EnergyMeasure_His PARTITION by range (TO_DAYS (datRecTime ))

(

PARTITIONp20131001 values less than (TO_DAYS ('2017-10-01 00:00:00 ')),

PARTITIONp20131101 values less than (TO_DAYS ('2017-11-01 00:00:00 ')),

PARTITIONp20131201 values less than (TO_DAYS ('2017-12-01 00:00:00 ')),

PARTITIONp20140101 values less than (TO_DAYS ('2017-1-01 00:00:00 ')),

PARTITIONp20140201 values less than (TO_DAYS ('2017-2-01 00:00:00 ')),

PARTITIONp20140301 values less than (TO_DAYS ('2017-3-01 00:00:00 ')),

PARTITIONp20140401 values less than (TO_DAYS ('2017-4-01 00:00:00 ')),

PARTITIONp20140501 values less than (TO_DAYS ('2017-5-01 00:00:00 ')),

PARTITION p20140601VALUES less than (TO_DAYS ('2017-6-01 00:00:00 ')),

PARTITIONp20140701 values less than (TO_DAYS ('2017-7-01 00:00:00 ')),

PARTITIONp20140801 values less than (TO_DAYS ('2017-8-01 00:00:00 ')),

PARTITIONp20140901 values less than (TO_DAYS ('2017-9-01 00:00:00 ')),

PARTITIONp20141001 values less than (TO_DAYS ('2017-10-01 00:00:00 ')),

PARTITIONp20141101 values less than (TO_DAYS ('2017-11-01 00:00:00 ')),

PARTITIONp20141201 values less than (TO_DAYS ('2017-12-01 00:00:00 '))

);

Method 3: Create an index.

Index building is a common technology. Primary keys are indexes. We can also create a joint index on frequently used fields.

Method 4: Create a master-slave partitioned Database

The primary database is used for data insertion, deletion, modification, and other operations. The primary database is mainly used for data query operations, in this way, you can avoid freezing problems caused by time-consuming queries when making a large number of changes to the data. The establishment of a master-slave database and database backup methods are also covered in other articles.

Method 5: Adjust the database parameter configuration

 


Mysql performance can be optimized. Welcome

The information you provided is basically useless. You did not mention the application type of your database and your operating environment.

The configuration file does not need to be changed. The configuration file will not play a significant role in database optimization due to specific minor adjustments.

The optimization is generally divided into several points:
1. Storage engine selection. High concurrency, update, and insert. innodb is applicable for transaction processing. select is used for myisam. Generally, all tables use the same engine for maintenance.
2. You can select table sharding, database sharding, and Master/Slave load based on the data volume, cpu, memory, and disk I/o overhead.
3. The most important thing is SQL and index. with appropriate indexes, writing efficient SQL statements is the basis of optimization.

Optimization is a debugging process, and there is no general mode. You should make a reasonable choice from the actual situation.

MySQL Query Optimization

Select product_id, sum (stock_amount) as stock_amount, sum (stock_branch) as stock_branch from stock_details where product_id in (many, but generally do not exceed 1000) group by product_id

I am not very recommended to use where product_id in (many, but generally not more than 1000)
In this way, the speed will be slow. If optimization is taken into account
Where (Table A. Product_ID = Table B. Product_ID)

In addition, Sum () may take a long time to execute. We recommend that you perform monthly or yearly close processing on this table. Such a settlement type record can be marked with a field, when Sum is used, Sum is performed based on the last settlement record.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.