MySQL table and index use specification detailed

This article is a MySQL table and index usage specification for a detailed analysis of the introduction, the need for friends to reference the  

One, MySQL build table, the field needs to be set to Non-empty, you need to set the field default value.
Two, MySQL build table, the field is null, you need to set the field default value, the default value is not NULL.
Three, MySQL build table, if the field is equivalent to foreign keys, should be indexed in the field.
Four, MySQL build table, the same property values between different tables of the field, column type, type length, whether non-null, whether the default value, you need to be consistent, otherwise the index can not be used correctly for correlation comparison.
Five, when MySQL is in use, an SQL statement can only use one index of one table. All field types can be indexed, and the multiple-column index has a maximum of 15 properties.
VI, if you can choose from multiple indexes, MySQL usually uses the index with the least number of rows, and the index with the highest unique value.
Seven, indexed index (PART1,PART2,PART3), equivalent to the establishment of index (PART1), index (PART1,PART2) and index (PART1,PART2,PART3) three indexes.
Viii. MySQL must use the index for the like syntax in the following format:
SELECT * from T1 where key_col like ' ab% ';
Nine, SELECT COUNT (*) syntax in a statement without a Where condition Row efficiency is not faster than select COUNT (col_name), but it is efficient to execute in statements that have a where condition.
10, in the condition of multiple and in a where condition, must be a Key_part property of a multiple-column index and must contain KEY_PART1. For each single index, use only the index that iterates through the least rows.
11, in the condition of multiple or in a where condition, each condition must be a valid index.
12, the conditions following the order by must be the properties of the same index, and the sort orders must be consistent (for example, all ascending or descending).
13, all GROUP by columns reference the properties of the same index, and the index must save its keywords sequentially.
14, JOIN index, all fields that match on and where should have an appropriate index established.
15, the Smart Scan full table using force index to tell MySQL, using the index more efficient.
16, Periodic analyze table tbl_name updates the keyword distribution for the scanned table.
17, use slow log check statements regularly, perform explain, and analyze indexes that may be improved.
18, conditionsIf allowed, set larger key_buffer_size and Query_cache_size values (global parameters), and Sort_buffer_size values (session variables, not recommended over 4M). The naming of the
memo
primary key takes the following rule: The
primary Key name begins with Pk_ followed by the name of the table in which the primary key is located. The primary key name cannot exceed 30 characters in length. If it is too long, the table name can be abbreviated. Abbreviated rules with table names. The primary key name is expressed in lowercase English words. The name of the

Foreign key takes the following rule: The
foreign key name begins with Fk_, followed by the table name of the foreign key and the corresponding primary table name (excluding T_). The child table name and the parent table name are separated by an underscore (_). A foreign key name cannot exceed 30 characters in length. If it is too long, the table name can be abbreviated. Abbreviated rules with table names. Foreign key names are expressed in lowercase English words. The naming of the

Index takes the following rule:
1) The index name is expressed in lowercase letters and digits. The index name cannot be longer than 30 characters in length.
2) The primary key corresponds to the same index as the primary key. The
3) Uniqueness index starts with uni_ followed by the table name. The generic index starts with ind_ followed by the table name.
4) If the index length is too long, the table name can be abbreviated. Abbreviation rules with table names

index-related syntax
Example:
CREATE index log_url on logaudit_log (URL);
Show Index fro M logaudit_log
Drop index log_request_time on Logaudit_log

SQL execution efficiency detection MySQL explain
Explain shows how MySQL uses indexes to process SELECT statements and join tables. can help you choose better indexes and write more optimized query statements.


using the method, add explain to the SELECT statement:


such as: Explain select Surname,first_name form a,b where A.id=b.id

The
analysis results form as follows:
Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra

explanation of the
explain column:
Table
shows the data in this row about which table is


Type
This is an important column that shows what type of connection is being used. The connection types from best to worst are const, EQ_REG, ref, range, Indexhe, and all


Possible_keys
Displays the indexes that may be applied to this table. If empty, there is no possible index. You can select an appropriate statement for the related field from the Where statement


Key
The index that is actually used. If NULL, the index is not used. In rare cases, MySQL chooses to optimize an index that is insufficient. In this case, the use
can be used in the SELECT statement

Index (IndexName) to force the use of an index or ignore index (indexname) to force MySQL to ignore the index


Key_len
the length of the index used by the . Without loss of accuracy, the shorter the length the better


ref
Shows which column of the index is used and, if possible, a constant


rows
MySQL considers the number of rows that must be checked to return the requested data


Extra
Additional information about how MySQL resolves queries. will be discussed in Table 4.3, but the bad example you can see here is using temporary and using filesort, meaning MySQL cannot use indexes at all, and the result is a slow retrieval


Extra column returns the meaning of the description


Distinct
once MySQL finds a row that matches the row, it stops searching for


not exists
MySQL optimizes the left join, once it finds a row that matches the left join criteria,


no longer searched
.

Range checked for each
Record (index map:#)
did not find the ideal index, so for each row from the previous table, MySQL checks which index is used and uses it to return rows from the table. This is one of the slowest connections using the index


Using Filesort
when sees this, the query needs to be optimized. MySQL needs to take extra steps to find out how to sort the rows returned. It sorts all rows based on the type of connection and the row pointers for all rows that store the sort key values and matching criteria


Using Index
column data is returned from a table that uses only the information in the index without reading the actual action, which occurs when the entire Request column for the table is part of the same index


Using Temporary
When you see this, the query needs to be optimized. Here, MySQL needs to create a temporary table to store the results, which typically occurs when a different set of columns is on an order by, rather than a group by on


Where used
uses the WHERE clause to restrict which rows match the next table or return to the user. If you do not want to return all the rows in the table, and the connection type all or index, this occurs, or there is a problem with the query


interpretation of different connection types (sorted in order of efficiency)


System
The table has only one row: the system table. This is a special case of the const connection type


Const
the maximum value of a record in the table can match this query (the index can be a primary key or a unique index). Because there is only one line, this value is actually constant, because MySQL first reads the value and treats it as a constant to


Eq_ref
in connection, MySQL when querying, from the previous table, the union of each record reads a record from the table, which is used when the query uses all of the index primary key or unique key


ref
This connection type occurs only if the query uses a key that is not a unique or primary key or is part of these types (for example, using the leftmost prefix). For each row of the previous table, all records are read from the table. This type relies heavily on the number of records matched according to the index-the less the better


Range
This connection type uses the index to return a range of rows, such as using > or


FAQ
1
The table contains 100,000 records with a datetime-type field.


the statement to fetch data:


SELECT * from my_table WHERE created_at < ' 2010-01-20 ';


With the EXPLAIN check, the type is all, the key is NULL, and the index is not available at all.


can be sure that the Created_at field is indexed.


what reason?
with SELECT COUNT (*) to see the total number of records that match the WHERE condition, which is more than 6W!!


no wonder no index, at this time with the index meaningless, like 100,000 records of the user table, have personality antonyms paragraph, not male or female, in such a field set index is wrong decision.


the above statement slightly:


SELECT * from my_table WHERE created_at BETWEEN ' 2009-12-06 ' and ' 2010-01-20 ';


solve this problem!
eligible records are only hundreds of, EXPLAIN type is Range,key Created_at,extra is the Using where.


yourself to summarize the guidelines, the purpose of the index is to minimize the result set, so as to do a quick query.




The
60,000 records are eligible and are already over half the total number of records, and the index is meaningless, so MySQL discards the use of the index.


This is similar to setting the gender field, indexed, and when you want to select all the male records, the number of eligible records is about half of the total, and MySQL will not use this index.


fields with more unique values, the better the use of indexes.


when you set up a federated index, the more unique values, the more you should put on the left.