MySQL Performance optimizations: table, incremental backup, or

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. Keyword: 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.

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

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 the My.ini.

port=3306 [modified in two places]

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 and restore

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 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, 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 G:\Database\mysqlbinlog\mylog.000001 | Mysql-uroot-p

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

SELECT * from EMP where empno=100003;

The result becomes

Restore by Location

Execute cmd command: Mysqlbinlog G:\Database\mysqlbinlog\mylog.000001 | Mysql-uroot-p

This time the SQL is executed to see the results, and then back again. The text is forwarded by CLS CC International: http://www.cnblogs.com/jiekzou/p/5399160.html

MySQL Performance optimizations: table, incremental backup, or