MySQL Database SQL statement tuning 、
Index Design principles:
Index columns are generally columns in a WHERE clause or a column in a join sentence
Try not to index columns with small cardinality, such as the gender column
Use a short index whenever possible: If you try to specify the minimum length for the character column index.
(Short Keys is Better,integer best)
Create index CityName on the city (city (10));
Compound index prefix attributes, the order of the indexes is important.
Key (A,B,C) Federated Index:
Can walk the combination of indexes: key (a), key (A, B), key (A,b,c)
The following index cannot go index key (b), key (B,c), key (A,c)
Key (A, B) ... where b=5 would not use index
When you create a composite index, you should place the most commonly used restriction columns on the leftmost, descending
Avoid useless indexes. (rarely used or never called)
INNODB: Try to specify primary key, most commonly used shorter data type, unique column master key.
Use the fixed-length character type char as much as possible without varchar
For index optimization, when using a federated index, the commonly used columns on the left side of the index, the less frequently used columns, the first part of the characters, as long as you can accurately find it
For example:
Create INDEX d_a_p on Ad_oldboy_detal (Dateline,), Ader (Ader (), POS (20));
Avoid excessive use of indexes
1. Indexing is useful for improving retrieval capabilities, but it is also very resource-based for database maintenance.
2. The index of the gender column is called an over-indexing.
With only two values, indexing not only has no advantage, it also affects the insertion, update speed,
3, the index consumes disk space, reduces the performance of the update operation, and the execution plan takes into account the individual indexes
4. The number of indexes is not as good as possible.
5 tables with fewer rows can be indexed (within 100 lines)
Syntax for creating indexes
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 example set;
Create INDEX Sage_sdet on student (Sname,sage (100)
Query a statement to see if he has to walk the index;
Explain select * FROM student where sno=6
As long as key is not empty,
If the database has a cache and wants to test the speed, you should
Explain sql_no_cache* from uc_members where email= "1234";
View the unique value of the table how much, with distinct;
Select COUNT (Distinct Sage) from student;
Criteria for working with indexes
1: Index columns cannot contain null values
2: In a composite index, if one column contains a null value, the column will not use the index
3: The column type is a string, in the Where condition the character value is enclosed in quotation marks.
4: If the condition is separated by or, the pre-condition is indexed, and the subsequent column is not indexed, then the design index is not used
5: The condition is not the first part of the index column
5:like Statement Operations
In general, try not to use the like operation. Like "%aaa%" does not use indexes, and like, "aaa%" can use indexes. can establish Fulltext or Sphinx (Sphinx Division)
6: Do not perform calculations on columns
SELECT * from the users where year (adddate) <2007; will operate on each row, which will cause the index to fail and perform a full table scan, so we can change to select* from users where addate< ' 2007-07-01 ';
7: Do not use not in and <> operations
Not-in and <> operations will not use indexes for full-table scans, not-in can not exists instead, id<>3 can use id>3orid<3 instead
Other
Try to use connection queries instead of subqueries (nested queries)
Index issue for ORDER by
Show Processlist View Threads
Show Full Processlist
Uptime
In a production environment, if it is a large table, creating an index can be time-consuming, and it may take a few minutes to put the index at the bottom of the business
Explian See if there's an index
Show Processlist;
Look at the changes in load after the optimization
Causes of the optimization:
1 website problem, very slow show full processlist--->
2: Slow query Statement (log file)
grep Slow MY.CNF
As long as it
Long_query_time=1
Log-slow-queries=/data/3306/slow.log
Don't force all the columns to go index
Let the big table used to index the column, in general, you can
MySQL database SQL statement tuning,
