How to use index in Mysql 02/23 Update SLTechnology News&Howtos

How to use index in Mysql

2025-02-23 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article shows you how to use the index in Mysql, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Why is there 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.

What is 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.

30 10 40 5 15 35 661 6 11 19 21 39 55 100

Do you have any misunderstanding about the index?

Indexing is an important aspect of application design and development. If there are too many indexes, the performance of the application may be affected. Too few indexes will have an impact on query performance, which is very important to the performance of the application to find a balance. Some developers always think of adding indexes in hindsight-I have always thought that this is due to a wrong development model. If you know how to use the data, you should add indexes where needed from the beginning. Developers often use the database at the application level, such as writing SQL statements, stored procedures, etc., they may not even know the existence of the index, or think that the relevant DBA can be added afterwards. DBA often does not know enough about the data flow of the business, and adding an index requires monitoring a large number of SQL statements to find problems. This step must take much longer than it takes to initially add the index, and some indexes may be missed. Of course, the more indexes, the better. I have encountered such a problem: a MySQL server iostat shows that the disk utilization has been at 100%. After analysis, it is found that the developers have added too many indexes, and after deleting some unnecessary indexes, the disk utilization immediately dropped to 20%. It can be seen that the addition of index is also very technical.

Second, the principle of index

I. 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 complicated, because there are not only equivalence queries, but also range queries (>, show create table T1; | T1 | CREATE TABLE `t1` (`id`int (11) DEFAULT NULL, `name` char (1) DEFAULT NULL, `age`int (11) DEFAULT NULL, `sex`enum ('male','female') DEFAULT NULL, UNIQUE KEY `uni_ id` (`id`), KEY `ix_ name` (`name`), KEY `ix_ age` (`name`), KEY `ix_ sex` (`sex`) ENGINE=InnoDB DEFAULT CHARSET=latin1

VI. Test index

I. preparation

# 1. Prepare table create table S1 (id int,name varchar (20), gender char (6), email varchar (50)); # 2. Create a stored procedure to insert records in batch delimiter $$# declare that the ending symbol of the stored procedure is $$create procedure auto_insert1 () BEGIN declare i int default 1; while (I select * from S1 where id=333333333;Empty set (0.33 sec)

Third, under the premise that a large amount of data already exists in the table, the establishment speed of indexing a certain field segment will be very slow.

4. after the index is established, when the field is used as the query condition, the query speed is improved obviously.

PS:

1. Mysql goes to the index table first. According to the search principle of b + tree, it is quickly found that the record with id equal to 333333333 does not exist, and IO is greatly reduced, so the speed is obviously improved.

two。 We can find the table in the data directory of mysql, and we can see that it takes up more hard disk space.

3. Please note, as shown in the following figure

V. Summary

# 1. You must create an index for the fields of search criteria, such as select * from S1 where id = 333; you need to index id

# 2. When there is already a large amount of data in the table, indexing will be very slow and take up hard disk space, and the query speed will be accelerated after construction.

For example, create index idx on S1 (id) scans all the data in the table, and then uses id as the data item to create an index structure and store it in the table on the hard disk.

After it is finished, it will be quick to inquire again.

# 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.

The MySAM index file is separate from the data file, and the index file holds only the address of the data record.

In innodb, the table data file itself is an index structure organized by B+Tree (BTree, that is, Balance True), and the data field of the leaf node of this tree keeps the complete data record.

The key of this index is the primary key of the data table, so the innodb table data file itself is the primary index.

Because inndob data files are aggregated according to primary keys, innodb requires tables to have primary keys (Myisam may not be available)

If it is not explicitly defined, the mysql system automatically selects a column that uniquely identifies the data record as the primary key

If such a column does not exist, mysql automatically generates an implied field for the innodb table as the primary key, which is 6 bytes long and of type long integer.

VII. Correct use of indexes

I. the index missed

This is not to say that creating an index will necessarily speed up the query. If we want to use the index to achieve the desired effect of improving the query speed, we must follow the following questions when adding the index.

First delete all the indexes in the table, let's focus on the problem of division.

We write a stored procedure to add records to table S1 in batches, and the value of the name field is all duoduo, which means that the name field has a low degree of differentiation (the same is true for the gender field. We'll talk about it later).

Recall the structure of the b + tree, the query speed is inversely proportional to the height of the tree, in order to control the height of the tree very low, we need to ensure that: in a certain layer, the data items are arranged in the order from left to right, from small to large, that is, the order of left 1b-> c

# 3. How or works

Conditions:

A = 10 or b = 'xxx' or c > 3 or d = 4

Index:

Create a joint index (dmaine a recorder bpcro c)

How it works:

Consecutive or:mysql will be judged in the order of conditions, from left to right, that is, a-> b-> c-> d.

In the case where the condition on the left holds but the discrimination of the index field is low (name, accelerated query)

6 the leftmost prefix matching principle, a very important principle, for the combined index mysql will always match to the right until it encounters a range query (>, 3 and d = 4 if you build an index in the order of (a) and d = 4), d does not need an index, if you build an index (a meme bforce d d), then you can use it, and the order of a book bdepartment d can be adjusted at will.

7. Other circumstances

-use the function select * from tb1 where reverse (email) = 'duoduo';-the type is 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; # if the sort condition is an index, 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 by index, the field queried by select is still very slow if it is not an index. Select email from S1 order by email desc; Special: if the primary key is sorted, it is still fast: select * from tb1 order by nid desc -the leftmost prefix of the composite index if the composite index is: (name,email) name and email-hit index name-hit index email-missed index-count (1) or count (column) instead of count (*) there is no difference in mysql-create index xxxx on tb (title (19)) # text type, length must be specified

Other considerations

-avoid using select *

-count (1) or count (column) instead of count (*)

-try to replace varchar with char when creating tables

-the order of the fields of the table and the fields of fixed length are preferred.

-Composite indexes instead of multiple single-column indexes (when multiple conditional queries are often used)

-try to use short indexes

-use joins (JOIN) to replace subqueries (Sub-Queries)

-pay attention to the consistency of condition types when joining tables

-Index hash values (less repetition) are not suitable for indexing, for example, gender is not suitable

VIII. Joint index and overlay index

I. Joint index

A federated index refers to the combination of multiple columns on a table to make an index. The method of creating a federated index is the same as that of a single index, except that there are multiple index columns, as follows

Mysql > create table t (- > an int,-> b int,-> primary key (a),-> key idx_a_b (a)->); Query OK, 0 rows affected (0.11 sec)

So when do you need to use a federated index? Before we discuss this issue, let's take a look at the results within the federated index. In essence, a federated index is a B + tree, except that the number of keys worth is not 1, but > = 2. Then let's discuss the federated index of two integer columns, assuming that the two keys are worthy of the names an and b, respectively, as shown in the figure.

You can see that this is no different from the B+ tree of a single key that we saw before, the key values are sorted, and all the data can be read logically and sequentially through the leaf node, in the case of the above example, that is, (1prime1), (1magin2), (2magin1), (2magin1), (3magin1), (3pyr2), the data are stored in the order of (areb).

Therefore, it is obvious that the federated index can be used for the query select * from table where a=xxx and b=xxx, and the index can also be used for the query select * from table where a=xxx for a single column.

However, for the query select * from table where b=xxx of column b, you can not use the (afort b) index. In fact, it is not difficult to find out the reason. The values of b on the leaf node are 1, 2, 1, 4, 1, 2 are obviously not sorted, so the query of column b can not use the (aforce b) index.

The second advantage of a federated index is that when the first key is the same, the second key has already been sorted. For example, in many cases, the application needs to query a user's shopping situation and sort it by time. Finally, the purchase records of the last three times are taken out. Using the federated index can help us avoid one more sorting operation, because the index itself is already sorted at the leaf node, as follows

# = preparation list = create table buy_log (userid int unsigned not null, buy_date date); insert into buy_log values (1meme 2009-01-01'), (2meme 2009-01-01'), (3meme 2009-01-01'), (1J-02-01'), (3Jing-02-01'), (1Jing-03-01'), (1Jing-04-01'); alter table buy_log add key (userid) Alter table buy_log add key (userid,buy_date); # = verify = mysql > show create table buy_log | | buy_log | CREATE TABLE `buy_ log` (`userid` int (10) unsigned NOT NULL, `userid` date DEFAULT NULL, KEY `userid` (`userid`), KEY `userid` (`userid`, `buy_ date`) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # you can see that possible_keys has two indexes available here, namely a single index userid and a federated index userid_2, but the optimizer finally chooses userid because the leaf node of the index contains a single key value. | So in theory, more records can be stored on a page than mysql > explain select * from buy_log where userid=2. +-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | | Extra | +-- + | 1 | SIMPLE | buy_log | ref | userid | Userid_2 | userid | 4 | const | 1 | | +-+ row in set (0.00 sec) # then Suppose you want to take out the records of the last 3 purchases with userid 1 The federated index userid_2 is used, because in this index, in the case of userid=1, buy_date has sorted mysql > explain select * from buy_log where userid=1 order by buy_date desc limit 3. +-+-- + | id | Select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +- -+ | 1 | SIMPLE | buy_log | ref | userid Userid_2 | userid_2 | 4 | const | 4 | Using where Using index | +-+ row In set (0.00 sec) # ps: if the sort of extra shows Using filesort Then it means that a second sort is required after the data is found. For a federated index (aforme b), the following statement can use the index directly without the need for a second sort select. Then for the federated index (from table where a=xxx order by BBJ c), the following statements can also get the result select directly from the index. From table where a=xxx order by b, select... From table where a=xxx and b=xxx order by cten # but for federated indexes, the following statements cannot get the results directly through the index, and you need to perform a filesort operation yourself, because the index (ameme c) does not sort select. From table where a=xxx order by c

II. Overlay index

The InnoDB storage engine supports overriding indexes (covering index, or index overrides), that is, query records can be obtained from the secondary index without the need to query records in the clustered index.

One advantage of using an overlay index is that the secondary index does not contain all the information of the entire row of records, so its size is much smaller than the clustered index, so a large number of IO operations can be reduced.

Note: override indexing technology was first completed and implemented in InnoDB Plugin, which means that the InnoDB storage engine does not support override indexing for those with InnoDB version less than 1.0 or MySQL database version less than 5.0.

For the secondary index of InnoDB storage engine, because it contains primary key information, the data stored by its leaf node is (primary key1,priamey key2,...,key1,key2,...). For example

Another benefit of overriding an index is for some statistical problems. Based on the table buy_log created in the previous summary, the query plan is as follows

The innodb storage engine does not choose to query the clustered index for statistics. Because the buy_log table has a secondary index, and the secondary index is much smaller than the clustered index, selecting the secondary index can reduce the IO operation, so the optimizer chooses key as the userid secondary index.

Generally speaking, the so-called query condition in b cannot be selected for the federated index in the form of (aformab). However, if it is a statistical operation and the index is overwritten, the optimizer still chooses to use the index, as follows

# Joint index userid_2 (userid,buy_date). In general, we cannot use this index according to buy_date, but in special cases: if the query statement is a statistical operation and overrides the index, then according to buy_date as a query condition, you can also use the federated index mysql > explain select count (*) from buy_log where buy_date > = '2011-01-01' and buy_date

< '2011-02-01';+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+| 1 | SIMPLE | buy_log | index | NULL | userid_2 | 8 | NULL | 7 | Using where; Using index |+----+-------------+---------+-------+---------------+----------+---------+------+------+--------------------------+row in set (0.00 sec) 九、查询优化神器-explain 关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。执行计划：让mysql预估执行操作(一般正确) all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const id,email 慢： select * from userinfo3 where name='alex' explain select * from userinfo3 where name='alex' type: ALL(全表扫描) select * from userinfo3 limit 1; 快： select * from userinfo3 where email='alex' type: const(走索引) 参考文中：https://www.jb51.net/article/140759.htm 十、慢查询优化的基本步骤 0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE 1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高 2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询） 3.order by limit 形式的sql语句让排序的表优先查 4.了解业务方使用场景 5.加索引时参照建索引的几大原则 6.观察结果，不符合预期继续从0分析十一、慢日志管理慢日志 - 执行时间 >

ten

-missed the index

-Log file path

Configuration:

-memory

Show variables like'% query%'

Show variables like'% queries%'

Set global variable name = value

-configuration Fil

Mysqld-- defaults-file='E:\ wupeiqi\ mysql-5.7.16-winx64\ mysql-5.7.16-winx64\ my-default.ini'

My.conf content:

Slow_query_log = ON

Slow_query_log_file = DVOGUGUP....

Note: after modifying the configuration file, you need to restart the service

MySQL log management

Error log: record MySQL server startup, shutdown, and running errors

Binary log: also known as binlog log, which records operations in the database except SELECT in the form of binary files

Query log: recording query information

Slow query log: records operations that take more than a specified time to execute

Relay log: the standby library copies the binary log of the main library to its own relay log for local playback.

General log: which account to audit, during which time period, what events have been done

Transaction log or redo log: records Innodb transaction-related issues such as transaction execution time, checkpoints, etc.

1. Bin-log

1. Enable

# vim / etc/ my.cnf [mysqld] log-bin [= dir\ [filename]] # service mysqld restart

two。 suspend

/ / current session SET SQL_LOG_BIN=0;SET SQL_LOG_BIN=1 only

3. View

View all:

# mysqlbinlog mysql.000002 by time: # mysqlbinlog mysql.000002-- start-datetime= "2012-12-05 10:02:56" # mysqlbinlog mysql.000002-- stop-datetime= "2012-12-05 11:02:54" # mysqlbinlog mysql.000002-- start-datetime= "2012-12-05 10:02:56"-- stop-datetime= "2012-12-05 11:02:54"

By byte:

# mysqlbinlog mysql.000002-start-position=260# mysqlbinlog mysql.000002-stop-position=260# mysqlbinlog mysql.000002-start-position=260-stop-position=930

4. Truncate bin-log (generate a new bin-log file)

a. Restart the mysql server

B. # mysql-uroot-P123-e 'flush logs'

5. Delete bin-log Fil

# mysql-uroot-P123-e 'reset master'

Second, query log

Enable generic query log

# vim / etc/my.cnf [mysqld] log [= dir\ [filename]] # service mysqld restart

Third, slow query log

Enable slow query log

# vim / etc/ my.cnf [mysqld] log-slow-queries [= dir\ [filename]] long_query_time=n# service mysqld restart

MySQL 5.6:

Slow-query-log=1slow-query-log-file=slow.loglong_query_time=3

View slow query log

Test:

BENCHMARK (count,expr) SELECT BENCHMARK (50000000) SELECT BENCHMARK; the above is how to use the index in Mysql. Have you learned any 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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.