Shulou(Shulou.com)06/01 Report--

Principle of index optimization

1, leftmost prefix matching principle, joint index, mysql will do right matching until encountered range query (>, 3 and d = 4 if you build an index in the order of (a), d does not need an index, if you build an index (a), you can use it, and the order of a book can be adjusted at will.

2, = and in can be out of order, for example, a = 1 and b = 2 and c = 3 indexes can be built in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize.

3, the index column can not participate in the calculation, keep the column "clean", for example, from_unixtime (create_time) = '2014-05-29' can not be used to the index, the reason is very simple, the b + tree is stored in the data table field values, but for retrieval, all elements need to be compared with the application function, obviously the cost is too large. So the statement should be written as create_time = unix_timestamp ('2014-05-29')

4. When using an index, the index field should be small and unique, avoiding the situation of select *

5. Expand the index as much as possible, do not create a new index. For example, if you already have an index of an in the table, and now you want to add an index of (aforme b), you only need to modify the original index. Building an unnecessary index will increase the MySQL space.

6. If you determine how many pieces of data there are, use limit to limit them. When MySQL finds the corresponding number of pieces of data, it will stop searching.

7. Using query cache, MySQL will often cache the query results, but the corresponding "dynamic" data will not be cache

For example: 1. SELECT username FROM user WHERE signup_date > = CURDATE () cannot use cache 2, SELECT username FROM user WHERE signup_date > = '2017-05-06' can cache when using the first write function of MySQL, MySQL cannot determine that the result is changeable, so there is no cache, and now (), and rand () also does not enable cache.

8. Join syntax, try to put the small table in front, keep the data type consistent on the fields that need on, and set the corresponding index, otherwise MySQL cannot use index to get join query.

9. When a large number of updates are made on a large table, if the whole table is locked, it needs to be split to avoid locking the table for a long time, resulting in too much accumulation of other requests (InnoDB supports row locking, but only if the Where clause needs to be indexed, and locking the whole table without an index)

While (1) {/ / only do 1000 mysql_query at a time ("DELETE FROM logs WHERE log_date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.