MySQL Database SQL statement Optimization

MySQL Database SQL statement Optimization

MySQL index design principles:
The index column is generally a column in the where clause or a column in the join clause.
Try not to index columns with a small base, such as gender columns.
Use a short index as much as possible: If you want to specify the minimum length for the character column index.
(Short Keys are better, Integer best)
Create index cityname on city (10 ));
Composite Index prefix, index order is very important.
Key (a, B, c) joint index:
The combination of indexes: key (a), key (a, B), key (a, B, c)
The following indexes cannot be indexed: key (B), key (B, c), key (a, c)
Key (a, B)... where B = 5 will not use index

When you create a composite index, place the most frequently-used column as the condition column on the leftmost and decrease the number in sequence.

Avoid useless indexes. (Rarely used or never called)

INNODB: Specify the primary key as much as possible. Generally, the shorter data type is used, and the unique column is used as the primary key.
Try to use a char of the Fixed Length character type instead of varchar
For the optimization of indexes, when the Union index is used, you can place frequently used columns on the left of the index, not frequently used columns, take the first part of the characters, as long as you can find it accurately.
For example:
Create index d_a_p on ad_oldboy_detal (dateline,), ader (20), pos (20 ));
Avoid over-Indexing
1. index creation is useful for improving search capabilities, but database maintenance is also very resource-intensive.
2. A gender column index is called an over-indexed index.
There are only two values. index creation is not only advantageous, but also affects the insertion and update speed,
3. Indexes occupy disk space and reduce the performance of update operations. Each index must be considered in the execution plan.
4. The more indexes, the better.
Tables with fewer than five rows can be indexed (less than 100 rows)
Index creation syntax
Help create index
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name [USING index_type]
Index_col_name: Col_name: [(length)] [ASC | DESC]
Phpadmin:
Alter table 'pw4 _ group_art 'add index ('tid ')
Create index pw4_group_art_tid on pw4_group_art (tid );
Delete index syntax
Drop INDEX ind_sage on student;
Composite Index and the first N Character index examples;
Create index Sage_Sdet on student (sname, sage (100)
Query a statement to see if it is indexed;
Explain select * from student where Sno = 6
As long as the key is not empty,
If the database has a cache and you want to test the speed, you should
Explain SQL _NO_CACHE * from uc_members where email = "1234 ";
View the unique value of the table. Use distinct;
Select count (distinct Sage) from student;
Conditions for using Indexes
1: The index Column cannot contain null values.
2: As long as one column in the composite index contains null values, this column will not use the index.
3: The column type is a string. Enclose the character values in quotation marks in the where condition.
4
5: the condition is not the first part of the index column.
5: like statement operation
Generally, try not to use the like operation. Like "% aaa %" does not use indexes, while like and "aaa %" can use indexes. You can create fulltext or sph)
6: do not perform operations on columns
Select * from users where YEAR (adddate) <2007; operations are performed on each row, which causes index failure and full table scan, therefore, we can change it to select * from users where addate <'2017-07-01 ';
7: Do NOT use not in and <> operations
Not in and <> do NOT use indexes to scan the entire table. not in can be replaced by not exists. id <> 3 can be replaced by id> 3 orid <3
Others;
Try to replace subquery with connection query (nested query)
Order by index
Show processlist viewing thread
Show full processlist
Uptime
It may take several minutes to create an index for a large table in the production environment. It may take a few minutes to store the index at a low business point.
Explain to see if the index is used
Show processlist;
Check the load changes after optimization.

Cause of optimization:
1. Website problems. Slow show full processlist --->

2: Slow query statement (Log File)
Grep slow my. cnf

If yes
Long_query_time = 1
Log-slow-queries =/data/3306/slow. log
Do not force all columns to be indexed

You can index commonly used columns in a large table.

This article permanently updates the link address: