Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve Mysql database performance optimization", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve Mysql database performance optimization" this article.

Divide the table horizontally

If there are too many records in a table, such as tens of millions of records, and need to be searched frequently, then it is necessary for us to break them up. If I split into 100 tables, there are only 100000 records for each table. Of course, this requires that the data can be logically divided. A good division basis is conducive to the simple realization of the program, but also can make full use of the advantages of horizontal sub-table. For example, the system interface only provides monthly query function, then the table is divided into 12 monthly, each query only one table is enough. If you have to divide it according to the region, no matter how small the table is, the query still has to check all the tables, it is better not to open it. So a good basis for separation is the most important. Keyword: UNION

Example:

The order table is divided into tables (one a year) according to the time when the order is made. The student situation table queries the telephone charges. The data of the last three months are put into one table, and those within a year into another table.

Divide the table vertically

Some tables have only a small number of records, perhaps only 20,000 or 30,000, but the fields are very long, the table takes up a lot of space, and a large number of Icano is needed to retrieve the table, which seriously degrades the performance. At this point, you need to split the large fields into another table, and the table has an one-to-one relationship with the original table. (JOIN)

The two tables, [question content] and [answer Information], were initially added to [question Information] as several fields. You can see that the two fields of question content and answer are very long. When there are 30,000 records in the table, the table already takes up 1G of space and is very slow in listing the list of questions. After analysis, it is found that the system often displays the details of the test questions in pages according to the query conditions such as [volume], [unit], type, category, degree of difficulty and so on. And each search is these tables to do join, each time to scan the 1G table. We can split the content and answers into another table, which is read only when the details are displayed, resulting in two tables: question content and answer information.

Select the appropriate field type, especially the primary key

The general principle of selecting fields is to keep small but not big. Fields that can take up a small byte do not need a large field. For example, the primary key, it is recommended to use self-increasing type, so save space, space is efficiency! It is too obvious who is fast and who is slow to locate a record by 4 bytes and 32 bytes. When it comes to several tables to do join, the effect is even more obvious.

It is recommended to use an id without business logic as the protagonist, such as s1001. Example:

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

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

The database stores only paths. Pictures and files are stored in the file system, or even on a separate server (picture bed / video server).

Database parameter configuration

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

Innodb_additional_mem_pool_size = 64Minnodb_buffer_pool_size = 1G

For myisam, you need to adjust the key_buffer_size. Of course, adjusting the parameters depends on the status. You can see the current state with the show status statement to decide which parameters to adjust.

Modify port 3306 in my.ini, 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 memory, there is no doubt that you should use a 64-bit operating system and 64-bit mysql 5.5.19 or mysql5.6

Separation of reading and writing

If the database is too stressful to be supported by one machine, mysql replication can be used to synchronize multiple machines and disperse the pressure on the database.

Master Slave1 Slave2 Slave3

The main library master is used to write, and slave1-slave3 is used to do select, so each database shares much less pressure. In order to achieve this way, the program needs to be specially designed, both write and write operate master, and read all operate slave, which brings additional burden to program development. Of course, there is already middleware to implement this agent to read and write which databases are transparent to the program. There is an official mysql-proxy, but it is still an alpha version. Sina has an amobe for mysql, which can also achieve this goal, with the following structure

Regularly complete the backup of the database

For the actual requirements of the project, please back up a database or some tables in the database regularly.

Backup data newsdb every hour under windows

Windows backs up a table under newsdb at 2:00 every evening

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 mysqldump is located

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

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

How to restore a table of data

Enter the mysql operating interface

Full path of mysql > source backup files

Scheduled backup: (write the command to my.bat question)

How does windows back up regularly (2:00 every morning)

Batch commands are executed regularly using the scheduled tasks that come with windows.

Incremental backup and restore

Definition: mysql database will be in binary form, automatically record the user's operations on the mysql database to files, when the user wants to restore, you can use backup files to restore.

Incremental backups record dml statements, statements that create tables, but not select. The things recorded include: sql statement itself, operation time, location

Steps and recovery for incremental backup

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

1. Configure my.ini file or my.conf, and enable binary backup.

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

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

2. Restart the mysql service

At this point, you will see the following two files under the mysqlbinlog directory:

Mylog.000001: log backup files. To view the information in this log file, we can use the mysqlbinlog program, which is stored in the bin directory of mysql ("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, in which all log files are recorded. (G:\ Database\ mysqlbinlog\ mylog.000001)

3. Suppose the problem comes now, my update is a misoperation, how to restore it

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

So, as we can see right now from the picture above, the time and position of this statement is "2016-04-17 12:01:36".

Recover by time

We can choose the second before the statement is generated.

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

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

SELECT * from emp where empno=100003

Restore by location

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

At this point, SQL is executed to see the results, and it changes back.

The above is all the contents of the article "how to optimize the performance of Mysql database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.