Shulou(Shulou.com)06/01 Report--

The following is about the main problems with mysql performance optimization. The secret of the text is that it is close to the topic. So, no gossip, let's go straight to the following, I believe you will benefit from reading this article about the main problems of mysql performance optimization.

A, the table is the rationalization of design (in line with 3 paradigms)

B, add appropriate index (index) [four: general index, primary key index, unique index, unique, full-text index]

C, sub-table technology (horizontal segmentation, vertical segmentation)

D, read and write [write: update/delete/add] separation

E, stored procedure [modular programming, can improve speed]

F. Optimize mysql configuration [configure maximum number of concurrency, my.ini adjust cache size]

Introduction and upgrade of G and Mysql CVM

H, regularly to clear the unwanted data, regularly defragment

1. The design of database table

The first normal form: 1NF is an atomic constraint on attributes, which requires attributes (columns) to be atomic and can not be decomposed; (as long as relational databases meet 1NF)

The second paradigm: 2NF is the uniqueness constraint on the record, which requires the record to have a unique identity, that is, the uniqueness of the entity.

The third paradigm: 3NF is a constraint on field redundancy, which requires that fields have no redundancy. No redundant database design can do this.

2. General steps of sql optimization

Operation steps:

1. Know the execution frequency of various SQL through the show status command.

2. Locate SQL statements with low execution efficiency-(key select)

3. Analyze the execution of inefficient SQL statements by explain.

4. Identify the problem and take corresponding optimization measures.

MySQL can provide CVM status information by using the show [session | global] status command.

Session represents the statistical results of the current connection, and global represents the statistical results since the last time the database was started. The default is session level.

Show status like 'Com_%'

Where Com_XXX represents the number of times the XXX statement was executed. Eg:Com_insert,Com_Select...

Key note: through these parameters, Com_select,Com_insert,Com_update,Com_delete can easily know whether the current database application is mainly to insert updates or query operations, as well as the approximate implementation proportion of all kinds of SQL.

Connections: the number of attempts to connect to the MySQL CVM

Uptime: the working time of the CVM (in seconds)

Slow_queries: the number of slow queries (the default is 10s)

Number of times Show status like'Handler_read%' uses queries

Location slow query:

By default, mysql does not record a full query log, which needs to be specified at startup.

\ bin\ mysqld.exe--safe-mode-slow-query-log [mysql5.5 can be specified in my.ini]

\ bin\ mysqld.exe--log-slow-queries=d:bac.log

The specific operations are as follows:

If slow query is enabled, it is stored here in the mysql.ini file by default

1. Restart mysql, find the strength of datadir, and use cmd to enter the parent directory of data.

2. Run the command\ bin\ mysqld.exe-safe-mode-slow-query-log (note that the mysql service is disabled before execution)

3. The generated log file records all the recorded information.

Display the time of slow query: Show variables like 'long_query_time'

Reset time to full query: Set long_query_time=2

Modify the command Terminator: (in order for the stored procedure to execute normally, we need to modify the command termination symbol.)

Delimiter $$

How to record slow sql statements in our log (mysql is not recorded by default, so you need to specify a slow query when you start mysql).

3. Index

Type of ♥ index:

★ four indexes ① primary key index ② unique index ③ general index ④ full-text index

I. add

1.1 Primary key index addition

When a column of a table is set as the primary key, the column is the primary key index.

Createtable aaa (id int unsigned primary key auto_increment

Name varchar (32) not null default)

1.2 General Index

Generally speaking, a normal index creates a table first, and then a normal index.

For example:

Createindex index name from table name

1.3 create a full-text index

Full-text index, mainly for files, such as articles. Full-text index is useful for MyISAM, but not for innodb.

Create table articles (

Id int unsignedauto_increment not null primary key

Title varchar (20)

Body text

Fulltext (title,body)

) engine=myisam charsetutf8

Misuse:

Select * from articles where body like'% mysql%' [full-text index will not be used]

Proof:

Explain select * from articles body like'% mysql%'

The correct usage:

Select * from article wherematch (title,body) against ('database'); [can]

Description:

1. The fulltest index value in mysql takes effect for myisam.

2. Effective for English, à sphinx (coreseek) technology to deal with Chinese

3. The method used, match (field name, …) Against ('keyword')

4. Full-text indexing of a word called stop. Because in a text, the index is created by an infinite book, some common words and characters are not created. These words are called stop words.

1.4 create a unique index

When a column of a table is specified as a unique constraint, this column is the unique index

First, Create table ddd (id int primary keyauto_increment,name varchar (32) unique)

At this point, name is the only index by default.

The second kind, create table eee (id int primary keyauto_increment,name varchar (32))

Createunique index index name on table name (column name)

To put it simply: PRIMARY KEY=UNIQUE+NOT NULL

The Unique field can be null and can have multiple null, but if it is specific, it cannot be repeated.

Primary key field, which cannot be null or repeated

II. Inquiry

1.Desc table name [drawback of this method, unable to reality index name]

2.Show index from table name

Select index from table name\ G

3.show keys from table name

3. Delete

Altertable table name drop index index name

The Altertable table name is drop primary key. (delete primary key index name)

IV. Modification

Delete first, in all

Second, for the slow SQL caused by SQL writing, it is relatively convenient to optimize. As mentioned in the previous section, the correct use of indexes can speed up queries, so we need to pay attention to the rules related to indexes when writing SQL:

1. Field type conversion results in no index, such as string type without quotation marks, numeric type with quotation marks, etc., which may lead to full table scan without using index.

2.mysql does not support function conversion, so you cannot add a function in front of the field, otherwise the index will not be used.

3. Do not add or subtract before the field

4. If the string is long, you can consider the part of the index to reduce the size of the index file and improve the writing efficiency.

5.like% does not need an index in the front

6. No index is needed for individual queries based on the second and subsequent fields of the federated index

7. Do not use select *

8. Please use ascending order as much as possible.

Try to use union instead of Innodb for 9.or query.

10. The highly selective fields of the composite index are at the top.

The 11.order by / groupby field is included in the index to reduce sorting, which is more efficient.

In addition to the above rules for using indexes, there are a few points that SQL needs to pay special attention to when writing:

1. Try to avoid the SQL of large transactions. The SQL of large transactions will affect the concurrent performance of the database and master-slave synchronization.

two。 The problem of paging statement limit

3. To delete all records in the table, please use truncate, not delete

4. Don't let mysql do superfluous things, such as calculating

5. Input and write SQL with fields to prevent problems caused by later table changes, and the performance is also excellent (when it comes to data dictionary parsing, please query the information yourself)

6. Use select count (*) on Innodb because Innodb stores statistics

7. Use Oder by rand () with caution.

3. Display the number of slow queries: show status like 'slow_queries'

HEAP is an earlier version of mysql

4. Explain analyzes inefficient SQL statements:

The following information is generated:

Select_type: indicates the type of query.

Table: table that outputs the result set

Type: indicates the connection type of the table

Possible_keys: indicates the index that may be used when querying

Key: represents the index actually used

Key_len: the length of the index field

Rows: number of rows scanned (estimated rows)

Extra: description and description of implementation

Select_type type:

Primary: outermost query in a subquery

Subquery: the first select in the inner layer of the subquery. The result does not depend on the external query.

Dependent subquery: the first select in the inner layer of the subquery, depending on the external query

The second select in the union:union statement starts with all subsequent select

Simple: simple mode

Union result: merge result in union

Type type:

All: a full table scan is usually not good

System: the table has only one row (= system table) this is a special case of the const join type

Const: the table has at most one matching row

Extra type:

No table: use from dual in query statements or do not contain any from clauses

Using filesort: when the query contains order by operations and cannot be sorted using the index

Impossible WHERE noticed after readingconst tables:Mysql query optimizer

There can be no results by collecting statistics.

Using temporary: some operations must use temporary tables, common group by, order by

Using where: instead of reading all the information in the table, you can get the required data only through the index

4. Why the query speed is faster after using the index

If there is no index for an ordinary query, he will always execute it, and he will continue to query when it is matched in time, and there is no guarantee that there is no query to be made later. I want a full-text index.

Considerations for the use of ■ indexes

Cost of the index:

1. Take up disk space

2. Affect the operation of DML (insert,update,create) and slow down

■ summary: index should be created only if the following conditions are met

A. Must be used frequently in where.

B. the contents of this field are not the only values (sex)

C, the content of the field does not change frequently

Considerations for using indexes in ■:

Alter table dept add index myind (dname,loc); / / dname is the column on the left and loc is the column on the right

An index may be used in the following situations

a. For a multi-column index created, explain select * from dept where dname='aaa' is generally used as long as the query condition uses the leftmost column.

b. For queries that use like, indexes are not used if the query condition is'% aaa', and indexes are used by 'aaa%'

Indexes are not used in the following situations:

a. If there is an or in the condition, it will not be used even if there is a conditional index. in other words, all fields used are required to create an index. It is recommended that you avoid using the or keyword as much as possible.

b. For multi-column indexes, which are not the first part of the use, the index will not be used

When explain select * from dept where loc='aaa';// multi-column index, loc is the right column, the index will not use the

The c.like query starts with%. If you must use it, use a full-text index to query.

d. If the column type is a string, be sure to enclose the data in quotation marks in the condition, otherwise the index is not used

e. If MySQL estimates that using full table scans is better than using index blocks, indexes are not used

How to choose the storage engine of mysql

1:myISAM

If the transaction requirements of the table are not high, colleagues query and add the

For example, posts and replies in BBS.

2:InnoDB

The requirements for transactions are high, and the saved data are all important data.

Such as orders, account forms

3:Memory:

The data changes frequently and does not need to be stored and queried and modified at the same time.

The difference between myISAM and InnoDB:

1. MyISAM batch insertion is fast, InnoDB insertion is slow, and myISAM insertion is not sorted.

2. InnoDB supports transactions, but myISAM does not.

3. MyISAM supports full-text indexing

4. Lock mechanism. MyISAM is a table lock and InnoDB is a row lock.

5. MyISAM does not support foreign keys, while InnoDB supports foreign keys.

In ① applications with high schedule requirements, it is recommended to use fixed-point data to store numerical values. Group U-1 ensures the accuracy of the data. The progress of deciaml is higher than that of float, so try to use it.

② for the database of the storage engine's myISAM, if you want to delete and modify the database, you should regularly perform the optimize_table_name function to defragment the table.

③ date type to select the earlier type of minimum storage referenced according to the actual need

Back up the database manually:

1. Enter cmd

2. Mysqldump-uroot-proot database [table name 1, table name 2... ] > File path

Eg: mysqldump-uroot-proot temp > d:/temp.bak

Restore backup file data:

Source d:/temp.bak (on the mysql console)

Reasonable hardware resources and operating system

Master

Slave1

Slave2

Slave3

The main library master is used to write, slave1-slave3 is used to do select, and each database

The pressure of sharing is much less.

In order to achieve this method, the program needs to be specially designed to operate both master and read.

Slave brings extra burden to program development. Of course, there is already middleware to implement this.

Agent, which databases are transparent to the program to read and write. There's an official mysql-proxy, but

It's the alpha version. Sina has an amobe for mysql, which can also achieve this goal, structure

As follows:

5. Table segmentation

Split horizontally:

For a table with a large amount of data, when we provide retrieval, we should find the standard of the table according to the needs of the business, restrict the retrieval mode of the user on the retrieval page, and cooperate with paging.

Case: user table with a large amount of data

Three tables: qqlogin0,qqlogin1,qqlogin2

Put the user id%3 into different tables according to the result

Create tableqqlogin0 (

The id of id int unsigned not null primary key,/* cannot be set for self-growth * /

Name varchar (32) not null default''

Pwd varchar (32) not null default''

) engine = myisam default charset = utf8

Create the table qqlogin1 (

Id int unsigned not null primary key, / * this id cannot be set to self-growing * /

Name varchar (32) not null default''

Pwd varchar (32) not null default''

) engine = myisam default charset = utf8

Create the table qqlogin2 (

Id int unsigned not null primary key, / * this id cannot be set to self-growing * /

Name varchar (32) not null default''

Pwd varchar (32) not null default''

) engine = myisam default charset = utf8

Vertical split:

Put some fields of a table, these fields are not related at the time of query, but the amount of data is very large, we recommend putting these fields into a table to improve efficiency.

6. Optimized configuration of mysql

MY.INI

Port = 3306 default port is 3306

If you want to modify port port = 3309, in mysql_connect ('localhost:3309','root','root'); pay attention to

Query_cache_size = 15m this is the size of the query cache

The parameters of InnoDB can also be adjusted by the following two parameters

Innodb_additional_mem_pool_size = 64m

Innodb_buffer_pool_size = 1G

Myisam needs to adjust key_buffer_size

Adjusting the parameters depends on the status. You can see the current status with show status to decide which parameters should be adjusted.

7. Incremental backup

Actual case:

How to perform incremental backup and restore

Steps:

As shown in figure 1, the configured my.ini file or my.cof enables binary backup

2, restarted MySQL

After startup, you will find that the file is generated in the mylog directory

Among them: e:\ binary log\ mylog.index index file, which backup files are there

E:\ binary log\ mylog.000001 stores files for user object database operations

3, when we do the operation (select)

Check the bin that needs to be entered into the installation directory of MySQL, and then execute mysqlbinlog to file, followed by appending the file path.

Restore to the point in time of a statement, as shown in figure 4

4 reply according to the time point

Mysqlbinlog-stop-datetime = "2013-01-17 12:00:23" d:/binlog/mylog.000001 | mysq-uroot-p

(restore all data before the stop time)

Mysqlbinlog-start-datetime = "2013-01-17 12:00:23" d:/binlog/mylog.000001 | mysq-uroot-p

(all data after the recovery start time)

4Jing 2 restore according to position

Mysqlbinlog-stop-position = "234" d:/binlog/mylog.000001 | mysq-uroot-p

(restore all data before the stop time)

Mysqlbinlog-start-position = "234" d:/binlog/mylog.000001 | mysq-uroot-p

(all data after the recovery start time)

What are the main problems with the above mysql performance optimization, is there anything you don't understand? Or if you want to know more about it, you can continue to follow our industry information section.

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.