MySQL Database performance optimization three (sub-table, incremental backup, restore) _mssql

Next MySQL database performance optimization two

Horizontal partitioning of a table

If a table has too many records, such as thousands, and needs to be searched frequently, then we need to be fragmented. If I split into 100 tables, then there are only 100,000 records per table. Of course this requires data to be logically divided. A good division of the basis, is conducive to the simple implementation of the program, but also can make full use of the advantages of horizontal table. For example, the system interface only provides a monthly query function, then the table is divided into 12 months, each query only query a table is enough. If you want to according to the geographical points, even if the table is smaller, the query or to unite all tables to check, it is better not to dismantle. So a good split up basis is the most important. Key word: UNION

Cases:

• The order form is divided according to the time of order generation (one per year)
• Student Status Table
• Query telephone charges, nearly three months of data into a table, one year into another table

Vertical partitioning of a table

Some table records are not many, may also be 2, 30,000, but the field is very long, the table occupies a lot of space, retrieving the table needs to perform a large number of I/O, severely reducing performance. This time you need to split the large field into another table, and the table is one-to-one with the original table. (JOIN)

"Question content", "answer information" two tables, initially as a few fields added to the "test information", you can see the question content and answer these two fields are very long, in the table has 30,000 records, the tables have occupied 1G space, in the list of questions is very slow. After analysis, found that the system is often based on "book", "unit", type, category, difficulty degree, such as query conditions, pagination display the details of the question. And each retrieval is a join of these tables, each time to scan a 1G table. We can completely split the content and the answer into another table, only to show the details of the time to read this large table, resulting in "question content", "answer information" two tables.

Select the appropriate field type, especially the primary key

The general principle of selecting a field is to protect small and large, can use the small size of the field is not used in big characters. For example, the primary key, the proposed use of the type, such as space, space is the efficiency! Press 4 bytes and press 32 bytes to locate a record, who quickly who is slow is too obvious. The effect is even more pronounced when several tables are involved in the join.

It is recommended to use an ID that does not contain business logic to do the protagonist such as s1001. Cases:

int 4 bigint 8 mediumint smallint 2 tinyint 1
MD5 char (triple)
ID: integer tinyint samllint int bigint
student table
ID Stuno  stuname  adress
 s1001 Wang   Zhen

File system storage for large files such as files and pictures

The database stores only paths. Pictures and files are stored in the file system, even on a separate server (Figure 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 greatly tuned

Innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1g

For MyISAM, you need to adjust the key_buffer_size, of course, adjust the parameters to see the state, with the show status statement can see the current state, to decide which parameters to adjust

Modify Port 3306 on My.ini, default storage engine and maximum number of connections

In the My.ini.
port=3306 [two places modified]
default-storage-engine=innodb 
max_connections=100

Reasonable hardware resources and operating system

If your machine has more than 4G of RAM, there is no doubt that you should use a 64-bit operating system and 64-bit MySQL 5.5.19 or mysql5.6

Read and write separation

If the database pressure is very large, a machine cannot support, then can use MySQL replication to achieve multiple machines synchronization, the pressure of the database dispersed.

Master
Slave1
Slave2
Slave3

The main library master is used for writing, SLAVE1-SLAVE3 is used for select, and the pressure on each database is much smaller.
To achieve this, the program needs to be specially designed, write all operations master, read all operations slave, the development of the program brings an additional burden. Of course, there are already middleware to implement this agent, to read and write to the program which database is transparent. The official has a mysql-proxy, but still an alpha version. Sina has a amobe for MySQL, also can achieve this goal, the structure is as follows

Perform a backup of the database at timed intervals

The actual requirements of the project, please complete the scheduled backup of a database, or scheduled to back up some of the database operations

Backup data Newsdb Every 1 hours under windows

Windows nightly 2:00 backup newsdb a table below

cmd> mysqldump–u root–p Password database name > put the database into a directory

Case, backing up all tables in the MyDB library

Enter the directory where mysqldump is located

cmd> mysqldump–u ROOT–PHSP shop> d:/shop.log [export all tables of the shop database]

cmd> mysqldump–u ROOT–PHSP shop temusers emp > d:/shop2.log [Shop Database Temusers and EMP Export]

Table for how to recover data

Access to the MySQL operator interface

Mysql>source full path to backup files

Scheduled backups: (write commands to My.bat)

How Windows timed Backups (2:00 per day)

Schedule a batch command with a scheduled task that is run with Windows.

Incremental backup and restore

Definition: MySQL database will be in binary form, automatically the user to the MySQL database operations, records to the file, when users want to restore, you can use Backup files to restore.

An incremental backup records a DML statement, creates a table statement, and does not record a select. The things recorded include: SQL statement itself, operation time, location

Steps and restores for incremental backups

Note: mysql5.0 and previous versions are not supported for incremental backups

1, configure My.ini file or my.conf, enable binary backup.

Open My.ini file, find Log-bin, configure: Log-bin=g:\database\mysqlbinlog\mylog

Create a new directory under the G:\Database directory Mysqlbinlog

2, restart the MySQL service

This is the time to see the following two files under the Mysqlbinlog directory:

MYLOG.000001: Log backup file. If you want to view the information in this log file, we can use the Mysqlbinlog program to view, the Mysqlbinlog program is stored under the MySQL Bin directory ("C:\Program files\mysql\mysql Server 5.6\bin ”）。

Execute SQL statement

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 file. (G:\Database\mysqlbinlog\mylog.000001)

3, assuming that the problem now, I this update is a misoperation, how to restore

The time and location of each operation is recorded in the MySQL log, so we can recover it either by time or by location.

Well, now we can see from the figure above that this statement produces the time "2016-04-17 12:01:36", where the position is 614

To recover by time

We can choose one second before statement generation time

Execute cmd command: Mysqlbinlog--stop-datetime= "2016-04-17 12:01:35" G:\Database\mysqlbinlog\mylog.000001 | Mysql-uroot-p

This time I'm going to execute the SQL statement to view

SELECT * from emp where empno=100003;

The result becomes

Restore by position

Execute cmd command: Mysqlbinlog--stop-position= "614" G:\Database\mysqlbinlog\mylog.000001 | Mysql-uroot-p

This time to run SQL to see the results, and then changed back.

The above is the entire content of this article, I hope the content of this article for everyone's study or work can bring some help, but also hope that a lot of support cloud Habitat community!