Summary of common MySQL database optimizations

Index1. Primary KEY index

Role: Unique constraints and improved query speed

#建表时创建主键索引Create Table' table_name ' (' ID ')intUnsigned not NULLAuto_increment,Primary Key(' id ')); #删除主键索引Alter Table' table_name 'Drop Primary Key(' id '); #已有表增加主键索引Alter Table' table_name 'Add Primary Key(' id ');

2. General Index

Role: Improve query speed

#建表时创建普通索引Create Table' table_name ' (' name 'Char( -) not NULL,    Index [index_name](' name ')); #创建普通索引Create IndexIndex_name ontable_name (name); #删除普通索引Drop IndexIndex_name fromtable_name, #查看索引show indx fromtable_name;

3. Unique index

Role: Unique constraints and improved query speed

#建表时创建唯一索引Create Table' table_name ' (' name 'Char( -) not NULL,    Unique [index_name](' name ')); #创建唯一索引Create Unique IndexIndex_name ontable_name (name); #删除唯一索引Drop Unique IndexIndex_name fromtable_name;

4. Differences between a primary key and a unique index

A primary key is a constraint, and a unique index is an index, and the two are essentially different. The primary key must be created with a unique index, and the uniqueness index is not necessarily the primary key. The uniqueness index column allows null values, while primary key columns are not allowed to be null. When the primary key column is created, it is already default to a null value + unique index.

A primary key can be referenced by another table as a foreign key, and a unique index cannot. A table can create a maximum of one primary key, but multiple unique indexes may be created. Primary keys are more suitable for unique identities that are not easily changed, such as auto-increment columns, Social Security numbers, and so on.

In RBO mode, the primary key's execution plan priority is higher than the unique index. Both can improve the speed of queries.

use less SELECT *

When using SELECT, you should take the data we want to use, not the full fetch. Because when we select *, will increase the burden of the Web server, increase the load of network transmission, the query speed naturally decline.

EXPLAIN SELECT

Explain shows how MySQL uses indexes to process SELECT statements and join tables. Can help select better indexes and write more optimized query statements. The main usage is to add explain before select.

SELECT [ find field name ]  from Tab_name ...

With the help of EXPLAIN, you'll know when to add an index to the table to use the index to find records and make the SELECT run faster.

turn on query caching

Most MySQL servers have query caching turned on. This is one of the most effective ways to improve sex, and this is handled by the MySQL database engine. 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.

The first step is to set Query_cache_type to ON, and then query whether the system variable Have_query_cache is available:

 like ' Have_query_cache '

After that, the memory size is allocated to the query cache, which controls the maximum value of the cached query results. Related actions are modified in the configuration file.

Use not NULL

Many tables contain nullable (NULL) columns, even if the application well does not need to save NULL, because nullable is the default property of the column. It is generally preferable to specify column NOT NULL unless you really need to store a null value.
If the query contains nullable columns, it is more difficult for MySQL to optimize because nullable columns make indexing, index statistics, and value comparisons more complex. Nullable columns use more storage space, and special handling is required in MySQL. When a nullable column is indexed, each index record requires an extra byte, and in MyISAM it can even cause a fixed-size index (for example, an index of only one integer column) to become a variable-size index.
It is generally not necessary to change a nullable column to not NULL for a small performance boost, so there is no need to first modify this situation in the existing schema, unless you are sure that this will cause problems. However, if you plan to build indexes on columns, you should try to avoid designing nullable columns. Of course there are exceptions, for example, it is worth mentioning that InnoDB uses a separate bit to store NULL values, so there is good space efficiency for sparse data. But this does not apply to MyISAM.

selection of storage engines

For how to choose MyISAM and InnoDB, if you need transaction processing or foreign keys, then InnoDB may be a better way. If you need full-text indexing, then generally speaking, MyISAM is a good choice because it is built in the system, however, we do not actually test 2 million rows of records in a regular manner. So, even slower, we can get full-text indexing from InnoDB by using Sphinx.
The size of the data is an important factor in what kind of storage engine you choose, and large datasets tend to choose the INNODB approach because they support transactional processing and failback. The small database determines the length of time to recover, and InnoDB can use the transaction log for data recovery, which is faster. While MyISAM may take hours or even days to do these things, InnoDB only takes a few minutes.
Your habit of manipulating database tables can also be a factor that has a significant impact on performance. For example, COUNT () can be very fast in the MyISAM table, and it can be painful under the InnoDB table. While the primary key query will be quite fast under InnoDB, it is important to be careful that if our primary key is too long it can cause performance problems. A large number of inserts statements will be faster under MyISAM, but updates will be faster under innodb-especially when concurrency is large.
So, which one do you use to check? From experience, if it is a small application or project, then MyISAM may be more appropriate. Of course, the use of MyISAM in large-scale environments can be a great success, but it's not always the case. If you are planning to use a project with a large amount of data and require transactional or foreign key support, then you should really use the InnoDB method directly. But it is important to remember that InnoDB tables require more memory and storage, and converting 100GB MyISAM tables to InnoDB tables may make you have a very bad experience.

avoid using or in the WHERE clause to connect

If a field has an index and a field is not indexed, it will cause the engine to discard full table scans using the index, such as:

Select  from Table where num=tenor='admin'

You can query this:

Select  from Table where = Ten Union  All Select  from Table where = ' Admin '

avoid large data volumes to return

Consider using limit to limit the amount of data returned, which can slow down the query if you return a large number of data that you do not need each time.

WHERE clause optimization

The use of parameters in the WHERE clause causes a full table scan because SQL resolves local variables only at run time, but the optimizer cannot defer the selection of access plans to run time; it must be selected at compile time. However, if an access plan is established at compile time, the value of the variable is still unknown and therefore cannot be selected as an input for the index.
You should try to avoid expression operations on fields in the WHERE clause, and avoid function operations on fields in the WHERE clause which will cause the engine to discard full table scans using the index. Do not perform functions, arithmetic operations, or other expression operations on the left side of the "=" in the WHERE clause, or the index may not be used correctly by the system.

(Note: article source)

Summary of common MySQL database optimizations