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

This article shows you the principle of MySQL indexing and query optimization, the content is concise and easy to understand, absolutely can make your eyes bright, through the detailed introduction of this article, I hope you can get something.

First, introduction 1. What is an index?

In general application systems, the read-write ratio is about 10:1, and insert operations and general update operations rarely have performance problems, in the production environment, we encounter the most, but also the most prone to problems, or some complex query operations, so the optimization of query statements is obviously the top priority. When it comes to speeding up queries, we have to mention indexes.

two。 Why should there be an index?

An index, also known as a "key" in MySQL, is a data structure used by the storage engine to quickly find records. Indexes are important for good performance

It is critical, especially as the amount of data in the table becomes larger and larger, the impact of indexes on performance becomes more and more important.

Index optimization should be the most effective way to optimize query performance. Indexes can easily improve query performance by several orders of magnitude.

The index is equivalent to the sequence table of a dictionary. If you want to look up a word, if you do not use the sequence table, you need to look it up page by page from hundreds of pages.

Second, the principle of index-the principle of index

The purpose of the index is to improve the query efficiency, which is the same as the catalogue we use to consult the book: first locate the chapter, then locate to a section under the chapter, and then find the number of pages. There are similar examples: look up the dictionary, look up the number of trains, flights, etc.

The essence is to filter out the final results by constantly narrowing the scope of the data you want, while turning random events into sequential events, that is to say, with this indexing mechanism, we can always lock the data in the same way.

The database is the same, but obviously much more complex, because there are not only equivalent queries, but also scope queries (>, select * from S1 where email='egon333@oldboy';.) Rows in set (0.36 sec) 3, plus index # 1. You must create an index for the fields of the search criteria, such as select * from T1 where age > 5; you need to index age # 2. In the case of a large amount of data in the table, indexing will be very slow, and take up hard disk space, insert and delete updates are very slow, only fast query such as create index idx on S1 (id); will scan all the data in the table, and then take id as the data item to create an index structure and store it in the table on the hard disk. After the construction is finished, the query will be quick # 3. It should be noted that the index of the innodb table will be stored in the s1.ibd file, while the index of the myisam table will have a separate index file table1.MYI.

6. Correct use of index 1, overwrite index # Analysis select * from S1 where id=123; the sql hit the index, but did not overwrite the index. Use id=123 to locate the location of the id in the hard disk, or in the data table, in the data structure of the index. But the field of our select is *, and we need other fields besides id, which means that it is not enough for us to fetch id through the index structure, and we also need to use the id to find other field values of the line where the id is located, which takes time. Obviously, if we only select id, we subtract this distress, as shown in select id from S1 where id=123. This is to overwrite the index, hit the index, and get the address of id on the hard disk directly from the data structure of the index, very fast.

II. Joint index

3. Index merging # Index merging: combining multiple single-column indexes using # Analysis: we can use index merging to solve all the things that can be done by combinational indexes, such as create index ne on S1 (name,email); # combinational indexes we can create indexes separately for name and email. Combinatorial indexes can hit: select * from S1 where name='egon'; select * from S1 where name='egon' and email='adf' Index merging can hit: select * from S1 where name='egon'; select * from S1 where email='adf';select * from S1 where name='egon' and email='adf'; at first glance, it seems that index merging is better: it can hit more situations, but in fact, it depends on the situation. If it is name='egon' and email='adf', then the efficiency of combined indexes is higher than that of index merging. If it is a single condition, it is more reasonable to use index merging.

If we want to use indexes to achieve the desired effect of improving query speed, we must follow the following principles when adding indexes

# 1. Leftmost prefix matching principle, very important principle, create index ix_name_email on S1 (name,email,)-leftmost prefix matching: must match select * from S1 where name='egon'; # from left to right can select * from S1 where name='egon' and email='asdf'; # can select * from S1 where email='alex@oldboy.com' # No, mysql will match to the right until it encounters a range query (>, 3 and d = 4). If you build an index in the order of (ameminbrecy c), d does not need an index, but if you build an index (a meme breco d d), you can use it, and the order of ameme bforce d can be adjusted at will. # 2. = and in can be out of order, such as a = 1 and b = 2 and c = 3. The index can be built in any order, and mysql's query optimizer will help you optimize it into a form that the index can recognize. Try to select a highly differentiated column as the index. The formula for distinguishing degree is count (distinct col) / count (*), indicating the proportion of non-repetitive fields. The larger the proportion, the less the number of records we scan, and the differentiation degree of the only key is 1. While some status and gender fields may be 0 in front of big data, then some people may ask, is there any empirical value for this ratio? With different scenarios, this value is also difficult to determine. Generally, the fields that need join are required to be above 0.1, that is, an average of 1 scan 10 records # 4. Index columns can not participate in the calculation, keep the column "clean", for example, from_unixtime (create_time) = '2014-05-29' can not use 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 high. So the statement should be written as create_time = unix_timestamp ('2014-05-29')

Leftmost prefix demonstration

Mysql > select * from S1 where id > 3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.39 sec) mysql > create index idx on S1 (id,name,email,gender); # not following the leftmost prefix Query OK, 0 rows affected (15.27 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > select * from S1 where id > 3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.43 sec) mysql > drop index idx on S1 Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > create index idx on S1 (name,email,gender,id); # follow the leftmost prefix Query OK, 0 rows affected (15.97 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > select * from S1 where id > 3 and name='egon' and email='alex333@oldboy.com' and gender='male';Empty set (0.03 sec) 16. Id must appear in the leftmost prefix matching 2 index (id,age,email,name) 3 # condition (as long as there is id, it will speed up) 4 id 5 id age 6 id email 7 id name 8 9 email # No, if you can't increase the speed by 10 mysql > select count (*) from S1 where id=3000 alone. 11 +-+ 12 | count (*) | 13 +-+ 14 | 1 | 15 +-+ 16 1 row in set (0.11 sec) 17 18 mysql > create index xxx on S1 (id,name,age,email); 19 Query OK, 0 rows affected (6.44 sec) 20 Records: 0 Duplicates: 0 Warnings: 021 22 mysql > select count (*) from S1 where id=3000 23 +-+ 24 | count (*) | 25 +-+ 26 | 1 | 27 +-+ 28 1 row in set (0.00 sec) 29 30 mysql > select count (*) from S1 where name='egon' 31 +-+ 32 | count (*) | 33 +-+ 34 | 299999 | 35 +-+ 36 1 row in set (0.16 sec) 37 38 mysql > select count (*) from S1 where email='egon3333@oldboy.com' 39 +-+ 40 | count (*) | 41 +-+ 42 | 1 | 43 +-+ 44 1 row in set (0.15 sec) 45 46 mysql > select count (*) from S1 where id=1000 and email='egon3333@oldboy.com' 47 +-+ 48 | count (*) | 49 +-+ 50 | 0 | 51 +-+ 52 1 row in set (0.00 sec) 53 54 mysql > select count (*) from S1 where email='egon3333@oldboy.com' and id=3000 55 +-+ 56 | count (*) | 57 +-+ 58 | 0 | 59 +-+ 60 1 row in set (0.00 sec) to build a federated index, the leftmost match

If the index fails to hit, it should be noted:

-like'% xx' select * from tb1 where email like'% cn';-use the function select * from tb1 where reverse (email) = 'wupeiqi';-or select * from tb1 where nid = 1 or name =' seven@live.com'; Special: when there is an unindexed column in the or condition, the following will be indexed select * from tb1 where nid = 1 or name = 'seven' Select * from tb1 where nid = 1 or name = 'seven@live.com' and email =' alex'-inconsistent if the column is a string type, the incoming condition is that it must be enclosed in quotation marks, otherwise. Select * from tb1 where email = 999 An ordinary index is not equal to not going to the index -! = select * from tb1 where email! = 'alex' Special: if it is a primary key, it will still go to index select * from tb1 where nid! = 123-> select * from tb1 where email >' alex' Special: if it is a primary key or the index is an integer type If the index select * from tb1 where nid > 123 select * from tb1 where num > 123 # sort condition is index, then the select field must also be an index field, otherwise it cannot be hit-order by select name from S1 order by email desc When sorting according to the index, if the field queried by select is not an index, the index select email from S1 order by email desc; is not left. Special: if the primary key is sorted, the index is still left: select * from tb1 order by nid desc -the leftmost prefix of a composite index if the composite index is: (name,email) name and email-- use index name-- use index email-- No index-- count (1) or count (column) instead of count (*) there is no difference in mysql-create index xxxx on tb (title (19)) # text type Must set length-avoid using select *-count (1) or count (column) instead of count (*)-try to create tables when char replaces varchar- fields in the order of fixed-length fields first-combined indexes instead of multiple single-column indexes (when multiple conditional queries are often used)-try to use short indexes-use joins (JOIN) instead of subqueries (Sub-Queries)-join Note that the condition type should be consistent-index hash values (with less repetition) are not suitable for indexing. Ex.: gender is not suitable

7. The basic steps of slow query optimization. First run to see if it is really slow, pay attention to set the SQL_NO_CACHE1.where condition sheet look-up, lock the minimum return record table. This sentence means that the where of the query statement is applied to the table with the smallest number of records returned in the table, and each field in a single table is queried separately to see which field has the highest degree of differentiation 2.explain to check the execution plan, and whether it is consistent with the expectation of 1 (starting with locking the table with fewer records) the sql statement in the form of 3.order by limit gives priority to the sorted table. Understand the business side usage scenario 5. When adding an index, refer to several major principles of indexing 6. Observation results, do not meet expectations continue to analyze the above content from zero is the MySQL index principle and query optimization is how, have you learned the knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

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.