Mysql database performance optimization 3 (Table sharding, Incremental backup, and restoration), mysql Performance Optimization

Mysql database performance optimization 3 (Table sharding, Incremental backup, and restoration), mysql Performance Optimization

Next, Mysql database performance optimization 2

Horizontal table Division

If a table contains too many records, for example, tens of millions of records and needs to be searched frequently, it is necessary to convert them to zero. If I split 100 tables, each table has only 0.1 million records. Of course, data can be logically divided. A good division basis is conducive to the simple implementation of the program, and can also make full use of the advantages of horizontal table sharding. For example, if the system interface only provides the monthly query function, you can split the table into 12 by month. You can query only one table for each query. If you want to split the table by region, even if the table is smaller, you still need to join all the tables for query. It is better not to split the table. Therefore, a good splitting basis is the most important. Keyword: UNION

Example:

• The Order table is divided into tables based on the order generation time (one table per year)
• Student Status table
• Query the telephone fee, put the data in the last three months into one table, and put the data in another table within one year

Vertical table Division

Some tables have a small number of records, which may contain 2 or 30 thousand records, but the fields are very long. The table occupies a large amount of space and requires a large number of I/O operations during table retrieval, seriously reducing the performance. In this case, you need to split the large field into another table, and the table has a one-to-one relationship with the original table. (JOIN)

Two tables, question content and answer information, are initially added to question information as several fields. We can see that the question content and answer fields are very long, when there are 30 thousand records in the table, the table occupies 1 GB of space and is very slow in the list of questions. After analysis, it is found that the system displays the detailed content of the questions by Page Based on the query conditions such as volume, unit, type, category, and difficulty. These tables are used for join operations for each query, and the 1g table needs to be scanned each time. We can split the content and answer into another table, and read the Big Table only when the detailed content is displayed. As a result, two tables, question content and answer information, are generated.

Select the appropriate field type, especially the primary key.

The general principle of selecting a field is to keep the field small and not big. You can use a field that occupies a small byte without using a large field. For example, we recommend that you use the auto-increment type for primary keys. This saves space and space is efficiency! Locate a record in 4 bytes and 32 bytes. It is too slow and obvious. When several tables are involved in join operations, the effect is more obvious.

We recommend that you use an id that does not include the business logic as the main character, such as 1001. Example:

Int 4 bigint 8 mediumint smallint 2 tinyint 1md5 char (32) id: integer tinyint samllint int bigintstudent table id stuno stuname adress s1001 min Shenzhen

Files, images, and other large files are stored in the file system

The database is only stored in the path. Images and files are stored in the file system, and even stored on a single server (graph bed/Video Server ).

Database parameter configuration

The most important parameter is the memory. We mainly use the innodb engine, so the following two parameters are very tuned.

innodb_additional_mem_pool_size = 64Minnodb_buffer_pool_size =1G

For myisam, you need to adjust the key_buffer_size. Of course, to adjust the parameters, you must check the status. You can use the show status statement to view the current status to determine which parameters to adjust.

In my. ini, modify port 3306, default storage engine and maximum number of connections

In my. ini,. port = 3306 [There are two changes] default-storage-engine = INNODB max_connections = 100

Reasonable hardware resources and Operating Systems

If your machine memory exceeds 4 GB, the 64-bit operating system and 64-bit mysql 5.5.19 or mysql5.6 should undoubtedly be used.

Read/write splitting

If the database is under great pressure and cannot be supported by one machine, you can use mysql replication to synchronize multiple machines to distribute the database pressure.

Master
Slave1
Slave2
Slave3

The master database master is used for writing, the slave1-slave3 is used for select, each database to share a lot of less pressure.
To implement this method, Special Program design is required. Write operations are performed on the master and read operations are performed on the slave, which brings additional burden to program development. Of course, there is already middleware to implement this proxy, and it is transparent to which databases are read and written in the procedure. There is an official mysql-proxy, but it is still alpha. Sina has an ambench for mysql instance, which can also be achieved. The structure is as follows:

Regularly back up databases

To meet the actual project requirements, please back up a database at regular intervals or regularly back up some tables of the database.

In windows, data is backed up every 1 hour.

Windows backs up a table under newsdb at every night

Cmd> mysqldump-u root-p Password Database Name> put the database into a directory

Case: Back up all tables in the mydb Database

Enter the directory where mysqldump is located

Cmd> mysqldump-u root-phsp shop> d:/shop. log [export all tables in the shop database]

Cmd> mysqldump-u root-phsp shop temusers emp> d:/shop2.log [Export temusers and emp of shop database]

How to restore a table

Mysql operation interface

Full path of mysql> source backup file

Scheduled Backup: (write the command to my. bat)

How to regularly back up windows data (every day)

Run the batch processing command periodically using the scheduled tasks that come with windows.

Incremental backup and Restoration

Definition: the mysql database automatically records user operations on the mysql database in binary format to a file. When you want to restore the database, you can use the backup file for restoration.

Incremental backup records dml statements, table creation statements, and select statements. The recorded items include the SQL statement itself, operation time, and location.

Steps and recovery for Incremental Backup

Note: mysql5.0 and earlier versions do not support Incremental backup.

1. Configure the my. ini file or my. conf to enable binary backup.

Open the my. ini file, find log-bin, and configure: log-bin = G: \ Database \ mysqlbinlog \ mylog

Create the mysqlbinlog directory under the G: \ Database directory.

2. Restart the mysql service.

At this time, the following two files are displayed in the mysqlbinlog directory:

Mylog.000001: log backup file. If you want to view the information in this log file, you can use the mysqlbinlog program. The mysqlbinlog program is stored in the bin directory of mysql ("C: \ Program Files \ MySQL Server 5.6 \ bin ").

Execute SQL statements

UPDATE emp set ename='zouqj' where empno=100003;

Start -- run -- cmd, mysqlbinlog backup file path

C:\Program Files\MySQL\MySQL Server 5.6\bin>mysqlbinlog G:\Database\mysqlbinlog\mylog.000001

Mylog. index: log index file, which records the log files. (G: \ Database \ mysqlbinlog \ mylog.000001)

3. If the problem arises, my update operation is incorrect and how to restore it?

Mysql logs record the time and location of each operation. Therefore, you can recover the operation by time or by location.

Now, we can see that the statement was generated at "12:01:36" at 614.

Restore by Time

We can choose one second before the statement generation time

Run the cmd command: mysqlbinlog -- stop-datetime = "12:01:35" G: \ Database \ mysqlbinlog \ mylog.000001 | mysql-uroot-p

At this time, I will execute the SQL statement to view

SELECT * from emp where empno=100003;

The result is changed

Restore by location

Run the cmd command: mysqlbinlog -- stop-position = "614" G: \ Database \ mysqlbinlog \ mylog.000001 | mysql-uroot-p

At this time, execute the SQL statement to view the result and change it back.

The above is all the content of this article. I hope this article will help you in your study or work. I also hope to provide more support to the customer's home!