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
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: 
## The type of query.
table: The table that outputs the result set 
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.
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 partitioningHorizontal 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 MySQLAfter 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:/ /m.sbmmt.com/
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!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
