MySQL performance optimization three (table, incremental backup, restore)

Horizontal partitioning of a table

If a table has too many records, such as thousands, and needs to be retrieved frequently, then we need to piecemeal. If I break into 100 tables, then there are only 100,000 records for each table. Of course this requires data to be logically divided. A good division basis, in favor of the simple implementation of the program, can also make full use of the advantages of the horizontal table. For example, the system interface only provides monthly query function, then the table by month split into 12, each query query only one table is enough. If you want to divide by region, even if the table is smaller, the query or to unite all the tables to check, it is better not to dismantle. So a good basis for splitting is the most important. UNION
Cases:

The order form is divided into tables according to the time of order generation (one per year)

Student Status Table
Enquiry telephone fee, nearly three months of data into a table, within a year into another table

To divide a table vertically

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

"Question content", "answer information" two tables, initially as a number of fields added to the "question information", you can see the question content and answer the two fields are very long, in the table has 30,000 records, the table has accounted for 1G of space, in the column list of questions very slow. After analysis, it is found that the system is often based on "book", "unit", type, category, difficulty degree and other query conditions, the page shows the details of the question. Each search 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 the big table, resulting in the "question content", "answer information" two tables.

Select the appropriate field type, especially the primary key

The general principle of selecting a field is to keep it small, so that you can use a field that is small in size without large pieces of space. For example, the primary key, it is recommended to use the self-increment type, so save space, space is efficiency! Position a record by 4 bytes and by 32 bytes, who is fast and who is slow too obvious. When it comes to several tables for joins, the effect is even more pronounced.
S1001, it is recommended to use an ID that does not contain business logic as the protagonist
int 4 bigint 8 mediumint smallint 2 tinyint 1
MD5 Char (32)
ID: integer tinyint samllint int bigint
Student table
ID Stuno stuname Adress
1 s1001 Wang Shenzhen

File system storage for large files such as files and pictures

The database only stores paths. Pictures and files are stored in the file system, even on a single server (Picture 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 large
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 determine which parameters to adjust
On My.ini Modify Port 3306, default storage engine and maximum number of connections

In My.ini. port=3306 [There are two places to modify]default-storage-engine=innodb max_connections=100

Reasonable hardware resources and operating system

If your machine has more than 4G of memory, there's no doubt that you should use a 64-bit OS and 64-bit MySQL 5.5.19 or mysql5.6
Read/write separation
If the database pressure is very large, a machine can not support, then use MySQL replication to achieve multiple machine synchronization, the pressure of the database is dispersed.

Master
Slave1
Slave2
Slave3
Master Library Master is used to write, slave1-slave3 are used to make select, each database share a lot less pressure.
To achieve this, the program needs special design, write operations master, read all operations slave, to the development of the program brings an additional burden. Of course, there is now a middleware to implement this agent, the program to read and write which databases are transparent. The official has a mysql-proxy, but it's alpha version. Sina has a amobe for MySQL, also can achieve this purpose, the structure is as follows

Complete backup of the database at timed intervals

The actual requirements of the project, please complete the scheduled backup of a database, or scheduled backup of some tables of the database operations
Backup data once every 1 hours under Windows Newsdb
Windows nightly 2:00 backup newsdb a table
cmd> mysqldump–u root–p Password database name > put the database into a directory
Case, backing up all tables of the MyDB library
Enter the directory where the mysqldump is located
cmd> mysqldump–u ROOT–PHSP shop> d:/shop.log [export all tables of shop database]
cmd> mysqldump–u ROOT–PHSP shop temusers emp > d:/shop2.log [Shop Database Temusers and EMP Export]
How to recover data from a table
Access to the MySQL operator interface
Mysql>source full path of backup files
Scheduled backup: (write the command to My.bat Q)
How Windows is scheduled to be backed up (2:00 every day)
Use the Windows-brought scheduled task to execute batch commands on a timed basis.

Incremental backup

Definition: MySQL database will be in binary form, automatically the user to the MySQL database operation, log to the file, when the user wants to recover, you can use the backup file for recovery.

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

Steps and recoveries for incremental backups

Note: mysql5.0 and previous versions are not supported for incremental backups
1. Configure the My.ini file or my.conf to enable binary backup.
Open a. my.ini file, look for Log-bin, configure: Log-bin=g:\database\mysqlbinlog\mylog
Create a new directory under the G:\Database directory Mysqlbinlog
2. Restart MySQL Service
This time you will 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 stored in the MySQL Bin directory ("C:\Program files\mysql\mysql Server 5.6\bin ”）。

Execute SQL statement

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

Start-run--cmd,mysqlbinlog backup file path

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, suppose now the problem comes, I this update is 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.
So, as we can see right now, this statement is produced "2016-04-17 12:01:36", the position is 614

Recover by Time

We can choose one second before the time of the statement generation

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 view

SELECT *  from where empno=100003;

The result becomes

Restore by Location

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

This time the SQL is executed to see the results, and then back again.

MySQL performance optimization three (table, incremental backup, restore)