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)