MySQL Optimization (i)

Database operations are increasingly becoming a bottleneck for the entire application, and MySQL optimization is a priority in improving application performance, and today, some of the most recent MySQL performance optimizations

Performance optimization for MySQL

(a) Open query cache optimization of your query speed

How do I turn on the query cache for MySQL? Does your MySQL database support MySQL query caching?? (the query cache is a double-edged sword, and here is not much to say)

Workflow for Query caching:

1. server receives SQL, with SQL and some other criteria for key Lookup cache table (additional performance consumption)

2. If the cache is found, return the cache directly (performance boost)

3. If the cache is not found, execute the SQL query, including the original SQL parsing, and so on.

4. After executing SQL query results, put SQL query results into the Cache table (additional performance consumption)

Open the command line terminal input show variables like "%query_cache%"; See if you turn on caching

The parameters here

Have_query_cache: Does your MySQL version support query caching

Query_cache_size: The total amount of memory space used by the cache, in bytes, this value must be an integer multiple of 1024, or the actual MySQL allocation may be different from this value

Query_cache_type: Cache mode has three values 1) off: Off 2) on: Always open 3) DEMAND: Only queries that explicitly write Sql_cache will inhale the cache

Query_cache_min_res_unit: Minimum unit size when allocating memory blocks

If your query_cache_type =0 or query_chache_size = 0 means that the cache is not turned on, you can modify the configuration file to turn on

When many of the same queries are executed multiple times, the results of these queries are placed in a cache so that subsequent identical queries do not have to manipulate the table directly to access the cached results.

If your query condition has built-in functions that include some MySQL such as time Now (), Rand (), and so on, then it will not be cached.

Compare the SQL statements I executed below, when I turn on the query cache, the first execution consumes the same time as the second execution and the same query semantics but not the same case. (SQL statements are absolutely equal)

(2) Explain your query statement

Explain keyword allows you to know how to use the index, how to search for data, scan the number of rows, etc.

can help you analyze the bottleneck of your SELECT statement so that you can optimize your SELECT statement

Select a complex SQL statement

You can see how MySQL is handling your SQL statements.

Select_type: There are three parameters (Simple,primary, union,dependent union,union result) Simple It represents a plain select, there is no union and subquery (here is simply a brief introduction)

table: From which Sheet

Type: the type of access that is displayed, from the best performance to the worst, this is System > Const > EQ_REF > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Inde X_subquery > Range > Index > All

only one row in the table; const type the Special case

2) Const: The table has the most one row match, the CONST user compares primary key or unique index, because there is only one row, so soon

3) Eq_ref: the mysql manual says: "For each row combination from the previous table, read one row from the table. This may be the best type of join, except for the const type. It is used in all parts of an index to be joined and the index is unique or primary KEY ". That is, comparing columns with indexes

4) Ref: For each row combination from the preceding table, all rows with matching index values are read from this table. The index here does not include primary and unique.

5) Rang: Index within a given range, such as EXPLAIN SELECT * from user WHERE ID in (1,5) or between

6) All: Full table scan

Possible_key: shows which index is used to find rows in the table

key: the index to use for this query

Key_len: Using the length of the index

Ref: The ref column shows which column or constant is used together with key to select rows from the table.

Rows : Displays the number of rows scanned at the time of the query, and the larger the value, the better it is, so you can judge the quality of the MySQL statement and build the index optimization

Extra: additional information

Can be optimized according to the query statement of explain you select

(3) Make a reasonable index of your table

Why this is reasonable, the index is not built more than the better, too many indexes for UPDATE DELETE INSERT efficiency will have an impact,

The explain SELECT statement mentioned above can be analyzed

The number of rows scanned in table user (and Table a) is 180207 rows. And it's a full-table scan, no index,

At the command line we will execute the following SQL, the query time is 0.63sec

We can add an index to the school_id in the User Table CREATE index schoolindex on ' user ' (school_id);

Then look at the time of the query and the explain SELECT statement,

Summary: It is clear that the execution time is greatly reduced, and that in explain you can see that the type is ref (index) compared to the previous all, and row has significantly improved performance compared to 180207 rows to 2385 rows.

It is also important to note that when the condition behind your where is a.name like% Chen; This is a non-query statement. Even if you add an index to name, it doesn't make sense.

1. When creating multiple tables (three tables or more) associated views, use the fields of the primary table (especially the primary key of the main table) if it is a field of both the primary and secondary tables
2, the field of the sub-table (whether ordinary or primary key, index field) as a query condition does not help the query, all need to do a full table search

(4) If you use limit when querying a single piece of data

For example: SELECT * from user WHERE name= ' VDOBGB ';

When you know that name= ' VDOBGB ' has only one piece of data in the database, using limit will greatly improve efficiency, and when MySQL finds that line, it will return this data without continuing to look down

(5) In the Join table, the field type of the join condition should be consistent and indexed

If you use a number of table join queries in your app, you should confirm that the table and table join fields are indexed, and that the two field types are consistent.

To me. The field types connected to the above two tables are of type int and have been indexed. If you want to concatenate fields of decimal (decimal) type and int (shaping) type, then MySQL cannot use their indexes

(6) Avoid using SELECT *

The more data you read from the database, the slower the query becomes. And, if your database server and Web server are two separate servers , this also increases the load on the network transport.

Should develop, what data to take data

(7) Establish primary key index as ID

Establish a primary key index ID for each table, and this ID or auto_increment is preferably an int type,

If you have a table name that is unique, and you set the Name field as the primary key, the efficiency is reduced because the primary key using the varchar type is lower than the int type.

Also, under the MySQL data engine, there are some operations that need to use primary keys, in which case the primary key performance and settings become very important, such as:MySQL sub-table, cluster, etc.

(8) In some cases, enum is used instead of varchar (but there are some who use the enum type sparingly) .

If you have a field in your table such as a province, this field often appears and only appears in all provinces of Guangdong province, Fujian Province, Hainan Province and other countries.

Then you should give the field the type should be enum instead of varchar.

The ENUM type is very fast and compact. In fact, it holds the tinyint, but it appears as a string on its appearance.

For example, specify ENUM("one", "two", "three") a column that can have any of the values shown below. The index value for each value is also as follows:

Value	Index value
NULL	NULL
""	0
"one"	1
"two"	2
"three"	3

(9) Get advice using procedure analyse ()

Which Optimal_fieldtype will recommend us to use what kind of data type, when the data in the table the larger the time, the more accurate, but not necessarily completely accurate, you need to think about. haha haha

(10) Use not NULL when setting up the table, and try to set default value for table

NULL requires extra space, as the documentation on MySQL says

If the field of your table is int then you should give the default value of defaults 0 if it is the varchar type default '

The MySQL section optimizes for a moment to speak so much, if there is doubt or a welcome comment with other insights.

Today PHP7 released, PHP7 performance compared to the original increase of 40%-200%.

MySQL Optimization (i)