Optimization of SQL Optimization index

This article is a summary of QI, if there is a wrong hope can be pointed out.

Junior developers, generally, are not exposed to architecture, big data such an optimization scheme, and the primary developers to do optimizations generally only SQL optimization.

One, give a small example:

In the website, generally will be designed to login registration, but there are many ways to login, such as: User name login, mobile login, Mailbox Landing, Weibo, QQ, ... And so many kinds of login methods.

So many login methods if you put in a table, there are a lot of problems in writing SQL statements:

If the SQL statement that puts the login method in a table should look like this:

SELECT * from user where uname= ' Zhangsan ' and upass= ' 123456 ' or email= ' [email protected] ' and email_pass= ' 123456 ' ...

It is not recommended to use * query all, here only for testing, a high-quality developer, should take their own required fields, if the data table structure is modified one day, will not affect its efficiency;

Back to the optimization scheme, such a table of SQL statements if it is a relatively large site, will greatly occupy the system resources,

Optimization scheme: You can split the different login methods into different tables, so that the query is only need to determine its login method to query the corresponding table, greatly increasing the query speed.

Cons: Because the separation of many tables, also affect the writing, deletion, modification efficiency.

Here Qi number summary, any optimization solution is not out of thin air optimization, will reduce the efficiency of a part, improve part of the performance , if cost, is reasonable optimization scheme

Second, the optimization of small means

Before optimization, the query is very important, all the optimization scheme is based on the actual requirements to optimize.

1. Check the number of changes and additions to the current program

MySQL can record the current site all the increase, delete, change, check the number of times, can be based on these to take the optimization program

The code is as follows:

Show [global|session] status like ' Com_select ';

Parameter description:

Global---When the MySQL server is launched, we recommend that you use the Globals property when testing

Session (default)---The current session window to perform additions and deletions to change the number of

The number of query statements that like ' Com_select ' com_select find

Like ' Com_select ' com_delete find the number of deleted statements

Like ' Com_select ' com_update find the number of modified statements

Like ' Com_select ' Com_insert find the number of INSERT statements

The work to be done:

These times are stored in the MySQL library regularly, and distinguish time period, in the implementation of the optimization scheme to query the time period of the number of additions and deletions, and based on these analysis to optimize;

Optional statement

Show global status like ' connections '; See how many times MySQL is connected

Show global status like ' Uptime '; MySQL Work run time (real, not human modification)

2. Log

MySQL has three kinds of logs: These logs are generally present in the Mysql/data directory

Bin_log log (mysql-bin.00001): for 1. Data Recovery 2. Synchronizing data between master and slave databases

Error log (PC-20170406LOQL.ERR): Errors log: Logs information such as MySQL server startup, shutdown, and runtime errors

Slow log: Admin-pc-slow.log if the SQL statement exceeds the defined time of the slow log, it will be stored in slow log information

To view slow log definition time:

Mysql> Show variables like ' long_query_time ';

Modify the time to define slow logs
Set global long_query_time=0.0001;//as long as more than 0.0001 of statements are written to slow logs

You can customize the optimization scheme according to the SQL statements that are stored in the slow log;

The above is the preparation that needs to be done before starting the optimization, and then the optimization plan is developed.

Three-Start optimization

About the concept of index, QI in the previous article mentioned, you can go to see

1. Index optimization

Optimization tools: Parsing explanatory statements

The code is as follows:

Explain/desc SELECT * from t1 where name= ' user99999 ' \g;//parsing explanatory statements

Execution results

1. Row ***************************
Id:1 the number of bars to find
Select_type:simple simple query, there is no left connection, right connection, sub-query is simply
Table name of the TABLE:T1 operation
Type:all->ref (), const (constant), system index level
Possible_keys:null indexes that may be used
Key:null the actual index used
Key_len:null Length of index
Ref:null
rows:991159 the number of rows performed by this statement for a total scan. (according to the influence function to optimize)
Extra:using where
1 row in Set (0.00 sec)

When indexing is established, it is seldom indexed with one condition

Case Study: analyze the user's search behavior find the most common search method for users to build a federated index
SELECT * from goods where cate_id=1 and prices = 100 found users are mostly first searched category at search price
Create a federated index for cate_id and price
ALTER TABLE good add index c_p (cate_id,price)

SELECT * from good where cate_id=1 and price=2 and sell=100; Most users first search the category at the search price in the search sales
Create a federated index for CATE_ID price sell
ALTER TABLE good add index c_p_s (Cate_id,price,sell)

Index redundancy is present in both cases, but indexes are redundant in real-world projects, in order to improve the efficiency of each search

Note: The index's left front able (the more left-leaning index takes effect)
SELECT * from good where cate_id=1 and price=2 and sell=100; Index can take effect
SELECT * from good where sell=100; Index does not take effect
SELECT * from good where cate_id=1 and sell=100; CATE_ID index in effect

Creating a single index doesn't make sense because the user rarely has a single price adjustment query in the actual project
ALTER TABLE T1 ADD index C (cate_id);
ALTER TABLE T1 ADD index P (price);

Index overrides: InnoDB indexes and data are bound together, and the index is found to find the data such behavior is called Index overlay
Clustered index (INNODB) non-clustered index (MYISAM)

Return line: MyISAM engine index file-"The process of executing a data file is called back to the line

Secondary index: In addition to the primary key index is the secondary index

MyISAM Secondary index points to the address of the data file
The secondary index of the InnoDB is not bundled with the data, and the secondary index of the InnoDB points to the reference of the primary key index, and then calls the data

Well, the optimization of the index is these, qi number with you to share this, about the SQL optimization of the article actually have a lot of, bo friends can also go to collect. At last

Today is also the usual to share with you a simple face test.

Topic:

From 0,1,2,3,4,5,6,7,8,9, the 10 numbers randomly selected three different numbers, "three numbers without 0 and 5" Probability is

Bloggers who know the answers can comment below.

Chicken soup:

Today's share of the proverb is qi number in Beijing West two flag a company saw a monument flamboyant wrote:

Empty talk endangers, hard work hing Bang

At that time is also a little touch, I hope you can actually act up, in frustration can look up to the sky 45 degrees, roar loudly: "PHP is the best language", very cool.

Optimization of SQL Optimization index