Sorting out mysql performance optimization issues

Mysql optimization comprehensive issues:

A. The table design is rationalized (in line with 3 paradigms)

B. Add appropriate index (index) [four types: Ordinary index, primary key index, unique index, unique, full-text index]

C. Table splitting technology (horizontal split, vertical split)

D. Read and write [write: update/delete/add ] Separation

E. Stored procedures [modular programming, which can increase speed]

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

G. Mysql server recommended upgrade

H. Clear unnecessary data regularly and defragment regularly

Recommended Mysql related video tutorials:https://www. php.cn/course/list/51/type/2.html

1. Database table design

First normal form: 1NF is the atomicity of attributes Constraints require attributes (columns) to be atomic and cannot be decomposed; (as long as the relational database satisfies 1NF)

Second Normal Form: 2NF is a unique constraint on records, requiring records to have unique identifiers. That is, the uniqueness of the entity;

Third normal form: 3NF is a constraint on field redundancy, which requires that fields are not redundant. No redundant database design can do that.

2. General steps for sql optimization

Operation steps:

1. Use the show status command to understand the execution frequency of various SQLs.

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

3. Analyze the execution of low-efficiency SQL statements through explain

4. Determine the problem And take corresponding optimization measures

MySQL can provide server 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 database was last started. The default is session level.

show status like ‘Com_%’;

Com_XXX represents the number of times the XXX statement has been executed. Eg:Com_insert,Com_Select...
Key note: Com_select,Com_insert,Com_update,Com_delete. Through these parameters, you can easily understand whether the current database application is mainly based on insert and update operations or query operations, as well as various types of What is the approximate execution ratio of SQL.
Connections: The number of attempts to connect to the MySQL server
Uptime: The time the server works (in seconds)
Slow_queries: The number of slow queries (the default is slow query time 10s)

Show status like 'Handler_read%' Number of queries used

## Locating slow queries:

By default, mysql does not record full query logs. It needs to be started at startup When specifying

\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 queries are enabled, they will be stored here in the mysql.ini file by default

1. Restart mysql, find the path of datadir, and use cmd to enter the upper-level directory of data

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

3. The generated log file records all the record information

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

Reset the full query time: Set long_query_time=2;

Modify the command end symbol: (In order for the stored procedure to execute normally, we need to modify the command end symbol)

Delimiter $$

How to record the slow query sql statement into our log (mysql will not record it by default, you need to specify the slow query when starting mysql).

3. Index

♥ Types of indexes:

★Four kinds of indexes ① Primary key index ② Unique index ③ Ordinary index ④ Full-text index

    1. Adding

  1.1 Adding a primary key index

                                                                                                                                                                                                                                         

             Createtable aaa(id int unsigned primary key auto_increment,

                                                                   

In general, ordinary indexes create tables first, and then create ordinary indexes.

For example:

CreateINDEX index FROM table name

1.3 Create the full text index

full text index, mainly for documents Useful for MyISAM, but not useful for innodb. Title varchar(20),

Body text,

                                                                                                                                                                                                Body text, #                                                                                           Body text,                          Select * from articles where body like ' %mysql%'[Full-text index will not be used]

                                                                                                                                                                   %mysql%'

## SELECT * FROM Article WHEREMATCH (Title, Body) Against ('database'); [Yes]

## ：:

## 1. In mysql, the index value of Myisam takes effect.

2, effective in English, àsphinx (Coreseek) technology processing Chinese

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

                                                                                                                                                                                                                                                                                                         ​Because in a text, an infinite book is created as an index, therefore, some common words and characters will not be created. These words are called stop words

1.4 Create a unique index

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

The first one, Create Table DDD (ID Int Primary Keyauto_increment, name varchar (32);

At this time, name is the only index by default

                                            create table eee(id int primary keyauto_increment, name varchar(32));

                                                                      Create table eee(id int primary keyauto_increment, name varchar(32));

##                                                Simply put: Primary Key = Unique Not NULL

unique field can be null and can have multiple NULL, but if it is specific content, it cannot be repeated.

2. Query

1. Desc table name [disadvantage of this method, index name cannot be realized]

2. Show index from table name;

                                                                                                                                                Show index from table name;                              show keys from table name

 Three, delete

 Altertable table name drop index index name,

 Altertable table name drop primary key. (Delete the main barbenic name)

Four. Modify

first delete first. In all

二, slow SQL caused by writing SQL, 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 need for indexes, such as string types Do not use quotation marks for numeric types, etc. This may not use the index and cause a full table scan;

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

3. Do not add or subtract in front of the field;

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

5.like % The index is not used in the front;

6. The index is not used in separate queries based on the second and subsequent fields of the joint index;

7. Don’t Use select *;

8. Please try to use ascending order for sorting;

9. Try to use union instead (Innodb) for or queries;

10. High selectivity of compound index The fields are ranked first;

11. The order by / groupby fields are included in the index to reduce sorting and the efficiency will be higher.

In addition to the above index usage rules, you need to pay special attention to the following points when writing SQL:

1. Try to avoid large-transaction SQL, which will affect the concurrency performance and performance of the database. Master-slave synchronization;

2. Problem with paging statement limit;

3. Please use truncate to delete all records in the table, do not use delete;

4. Don’t let mysql do it Redundant things, such as calculation;

5. Enter and write SQL with fields to prevent problems caused by subsequent table changes. The performance is also relatively good (it involves data dictionary analysis, please query the information yourself);

6. Use select count(*) on Innodb, because Innodb will store statistical information;

7. Use Oder by rand() with caution.

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

##HEAP is Earlier mysql version

4. Explain analysis of inefficient SQL statements:

will produce the following information:

## The type of query.

table: The table that outputs the result set

type: Indicates the connection type of the table

possible_keys: Indicates the indexes that may be used when querying

key: Indicates the actual index used

Key_len: The length of the index field

# ROWS: The number of rows scanned (estimated)

Extra: Description and description of execution

select_type type ：

                                                                                                    :

                                              use   using using               use       use ’s ’ out ’s ’ use   out ’s ’     out through ’s ’ s   through using ’’s’ ’ through ‐ to ‐‐ ‐‐‐‐ lead to The first select in the inner layer depends on the external query

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

Simple: Simple mode

Union result: union Merge result

type type:

all: A complete table scan is usually not good

system: The table has only one row (=system table) This is a const join type A special case

const: The table has at most one matching row

extra type:

no table: from dual is used in the query statement or does not contain any from clause

USING FILESORT: When the Query contains Order By operation, and it is impossible to use the index to complete the sorting

impossible where noticed after readingConst Tables: MySQL Query Optimizer

## does not have results.

USING TEMPORARY: Some operations must use temporary tables. Common group by, order by

Use where: You can obtain the required data from all the information in the table without reading the table.

##4. Why does the query speed become faster after using the index?

If a normal query does not have an index, it will continue to be executed. If a match is found in time, the query will continue. There is no guarantee that the query will be performed later. Do you have anything to inquire about? Full-text indexing is required.

■Notes on using indexes

The cost of indexing:

1. Occupying disk Space

2. It has an impact on DML (insert, update, create) operations and slows down

■Summary: Indexes should be created only when the following conditions are met

A. Definitely

is often used in where. B. The content of the field is not a unique value (sex).

C. The content of the field does not change frequently.

■ Precautions for using indexes ：

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

It is possible to use the index in the following situations

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

b. For those using like Query, if the query condition is '�a', the index will not be used, and 'aaa%' will use the index

The index will not be used in the following situations:

a. If there is or, even if there is a conditional index, it will not be used. In other words, all fields used are required to create indexes. Suggestion: Try to avoid using the or keyword

b. For multi-column indexes, it is not the first part used , the index will not be used

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

c.like The query starts with %. If it must be used, use the full-text index to query

d. If the column type is a string, the data must be enclosed in quotes in the condition, otherwise the index will not be used

e. If MySQL estimates that using a full table scan is better than using an index block, then do not use an index.

How to choose the storage engine for mysql

1: myISAM

The requirements for the affairs are not high. Colleagues mainly query and add them, such as posting and replying in BBS.

2：InnoDB

The requirements for transactions are high, and the data saved is important data. While entering the database, you can also enter the site for query and modification.

The difference between myISAM and InnoDB:

1. MyISAM batch insertion is fast, InnoDB insertion is slow, and myISAM does not sort when inserting.

2. InnoDB supports transactions, but myISAM does not support transactions.

3. MyISAM supports full-text index,

4. Locking mechanism, myISAM is a table lock, InnoDB is a row lock

5. MyISAM does not support foreign keys, and InnoDB supports foreign keys. Jian

① In applications with high progress requirements, it is recommended to use fixed-point data to store values, group U to ensure the accuracy of the data, deciaml progress is higher than float, try to use

② For storage If the engine's myISAM database needs to be deleted and modified, the optimize_table_name function must be executed regularly to defragment the table.

③ The date type should select the early type with the smallest storage reference based on actual needs.

Manually back up the database:

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 (in the mysql console)

Reasonable hardware resources and operating system

Master

Slave1

Slave2

Slave3

The main database master is used for writing, and slave1-slave3 are used for selection. Each database

shares less pressure a lot of.

To implement this method, the program needs to be specially designed. The master is used for writing and the

slave is used for reading, which brings extra burden to program development. Of course, there is already middleware to implement this

proxy, which is transparent to the program to read and write which databases. There is an official mysql-proxy, but

is still an alpha version. Sina has amobe for mysql, which can also achieve this purpose. The structure

is as follows:

5. Table partitioning

Horizontal partitioning :

large data quantity table, when we provide retrieval, we should find the standard of the table according to the business needs, and restrict the retrieval method of users in the retrieval page, and to cooperate with the pages,

#          Case: User table with large amount of data

Three tables: qqlogin0, qqlogin1, qqlogin2

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

create tableqqlogin0(

id int unsigned not null primary key,/* This id cannot be set to auto-increment*/

name varchar (32) not null default'',

pwd varchar(32)not null default''

)engine = myisam default charset = utf8;

Create table qqlogin1(

id int unsigned not null primary key, / *This id cannot be set to auto-increment* /

name varchar (32) not null default'',

pwd varchar (32) not null default''

) engine = myisam default charset = utf8;

Create table qqlogin2 (

id int unsigned not null primary key, /*This id cannot be set to auto-increment * /

name varchar(32)not null default''，

pwd varchar(32)not null default''

)engine = myisam default charset = utf8;

Vertical segmentation:

Put some fields of a table into one table. These fields are not relevant when querying, but the amount of data is large. We recommend putting these fields into a table. , thereby improving efficiency

6. Optimized mysql configuration

MY.INI

port = 3306The default port is 3306,

If you want to modify the port port = 3309, in mysql_connect ('localhost: 3309', 'root', 'root'); note that

query_cache_size = 15M this is the size of the query cache

InnoDB parameters can also be used Increase the following two parameters

innodb_additional_mem_pool_size = 64M

innodb_buffer_pool_size = 1G

myisam needs to adjust key_buffer_size

Adjusting parameters also depends on the status, use show status You can see the current status to decide which parameters to adjust

7. Incremental backup

Actual case:

How to perform incremental backup and recovery

Steps:

As shown in Figure 1, configure the my.ini file or my.cof to enable binary backup

2 , restart MySQL

After startup, you will find that a few files are generated in the mylog directory

Among them: E:\binary log\mylog.index index file, what backup files are there

E:\Binary Log\mylog.000001 File that stores user object database operations

3, when we operate (select)

View the installation that needs to enter MySQL bin in the directory, and then execute mysqlbinlog to find the file, and append the file path

as shown in Figure 4 to restore to a certain The time point of the statement

4,1Reply 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 stop time)

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

(restore all data after the start time)

4,2Restore 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

(Restore all data after the start time)

For more related questions, please visit the PHP Chinese website: https:/ /www.php.cn/

The above is the detailed content of Sorting out mysql performance optimization issues. For more information, please follow other related articles on the PHP Chinese website!