Organize MySQL optimized content

1, when only one row of data is used with LIMIT 1
If you explicitly take only one piece of data, add limit 1;

2, Avoid SELECT *, get fields as needed
You should develop a good habit of taking whatever you need.

3, use 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. In this way, using this field to make a list of options becomes quite perfect. If you have a field such as "gender", "Country", "nation", "state" or "department", you know that the values of these fields are limited and fixed, then you should use ENUM instead of VARCHAR.

4, save the IP address as UNSIGNED INT
Making the right choice and small space-intensive fields, especially those that need to be indexed, requires minimizing the length of the field to reduce the size of the table to increase the speed of the query.
If the IP address is to be saved using an unsigned integer int unsigned as far as possible, use tinyint unsigned for the identity on the state and type as far as possible, and if you use varchar, do not exceed 16 bytes in length.
Convert IP to Integer $ip_num = Ip2long (' 211.157.144.20 ');
Converts an integer to Ip$ip = Long2ip (' 3718090772 ');

5, randomly go to 5 data
SELECT * from User ORDER by rand () LIMIT 5; Avoid
5 rows in Set (15.33 sec)
SELECT * from User as T1 joins (select round (rand () * (select Max (user_id) from user) as user_id) as T2 where t1.user_id > t2.user_id ORDER BY t1.user_id ASC limit 5;
5 rows in Set (0.00 sec)
However, this will produce 5 consecutive records. The solution can only be one query at a time, query 5 times. Even so it is worth it, because 150,000 of the tables, the query only need 0.01 seconds less than.

6, minimize the table connection query, preferably a single table query

7, avoid full table scan
To optimize queries, avoid full table scans, and first consider indexing on the columns involved in where and order by.

8, the left prefix matching principle, very important principle, MySQL will always match right until a range query (>, <, between, like) stops matching, such as a = 1 and B = 2 and C > 3 and D = 4 if established (A,B,C,D) The index of the order, D is not indexed, if the establishment (A,B,D,C) of the index can be used, the order of a,b,d can be arbitrarily adjusted.

9, try to choose a high-sensitivity column as the index, the formula for the degree of sensitivity is count (distinct col)/count (*), indicating the scale of the field is not repeated, the larger the proportion of the number of records we scan, the difference between the unique key is 1, and some states, The gender field may be 0 in front of big data, and one might ask, what is the empirical value of this ratio? Using different scenarios, this value is also difficult to determine, generally need to join the field we are required to be more than 0.1, that is, the average 1 scan 10 records

10, the knowledge point--explain
MySQL explanatory statement
Type shows the type of access, is an important indicator, the result values from good to bad are:
System > Const > Eq_ref > Ref > Fulltext > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range > Index > All
Generally , you must ensure that the query reaches at least the range level and is best to reach ref.
Explain select * from User where num > 5 and status = ' normal ' limit 2;
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| id | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
| 1 | Simple | User | Ref | Status | Status | 258 | Const | 164739 | Using where |
+----+-------------+-------+------+---------------+--------+---------+-------+--------+-------------+
1 Row in Set (0.00 sec)

ID: query serial number;
Select_type: Query type, common values are simple (i.e. no table joins or subqueries), PRIMARY (main query, outer query), union (second or subsequent query statement in Union) , subquery (the first SELECT in the subquery), and so on;
Table: The tables of the output result set;
Type: Scan mode, all for full table scan, representing table connection type, performance from good to bad connection type
System > Const & Gt Eq_ref > Ref > Ref_or_null > Index_merge > Unique_subquery > Index_subquery > Range > Index > All
Possible_keys: Indexes that may be used
Key: Actual index
Key_len: Index field length
rows: How many rows the SQL scanned, possible data rows
Extras: Additional information, such as sort by

Mysql> Explain select * from User where Update_ts < ' 2016-01-01 ' limit 10;
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+
| 1 | Simple | User | Range | Update_ts | Update_ts | 4 | NULL | 164739 | Using where |
+----+-------------+-------+-------+---------------+-----------+---------+------+--------+-------------+

Mysql> Explain select * from User where year (UPDATE_TS) < limit 10;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
| 1 | Simple | User | All | NULL | NULL | NULL | NULL | 329478 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------------+
The same situation can occur when a numeric field is calculated:
Mysql> Explain select user_id from user where total=4;
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| 1 | Simple | User | Ref | Total | Total | 5 | Const | 3308 | Using where; Using Index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in Set (0.00 sec)

Mysql> Explain select user_id from user where total/2=2;
+----+-------------+-------+-------+---------------+-------+---------+------+--------+------------------------- -+
| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+------------------------- -+
| 1 | Simple | User | Index | NULL | Total | 5 | NULL | 331815 | Using where; Using Index |
+----+-------------+-------+-------+---------------+-------+---------+------+--------+------------------------- -+
1 row in Set (0.00 sec)

Organize MySQL optimized content