select * from tb_item where title = "Goods No. 25"
Indexing is one of the most common and important means of database optimization, and it usually helps users solve most of MySQL performance optimization problems.
Validating indexes improves query efficiency
In the table structure tb_item we prepared, a total of 3 million records were stored;
Query by ID
select * from tb_item where id = 1999
Query speed is fast, near 0s, mainly because id is the primary key and has index;
Exact query based on title
select * from tb_item where title = "Goods No. 25"
View the execution plan of the SQL statement:
Processing scheme to create an index for the title field:
create index idx_item_title on tb_item(title);
After the index is created, query again:
View the execution plan through explain, using the index you just created when executing SQL
Use of indexes
Prepare the environment
create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','Alibaba','Ali Shop','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','Baidu Technology Co., Ltd.','Baidu Shop','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','Huawei Technology Co., Ltd.','Huawei Shop','e10adc3949ba59abbe56e057f20f883e','0','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast','Communications Podcast Education Technology Co., Ltd.','Wise Podcast','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','Black Horse Programmer','Black Horse Programmer','e10adc3949ba59abbe56e057f20f883e','0','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','Logitech Technology Co., Ltd.','Logitech Shop','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO Technology Co., Ltd.','OPPO only','e10adc3949ba59abbe56e057f20f883e','0','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','Palm Interest Technology Co., Ltd.','Palm Fun Shop','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','Thousand Degrees Technology','Thousand Degree Shop','e10adc3949ba59abbe56e057f20f883e','2','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','Sina Technology Co., Ltd.','Sina Official Flagship Store','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','Xiaomi Tech','Official flagship store of millet','e10adc3949ba59abbe56e057f20f883e','1','Xi'an City','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','Ikea Home','Ikea Home Flagship Store','e10adc3949ba59abbe56e057f20f883e','1','Beijing','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);Avoid index invalidation
1).Full value matches, specifying specific values for all columns in the index.
In this case, the index is effective and efficient.
explain select * from tb_seller where name='Xiaomi Tech' and status='1' and address='Beijing'
2).Leftmost Prefix Rule
If there are multiple columns in an index, the leftmost prefix rule is followed. This means that the query starts in the leftmost front column of the index and does not skip columns in the index.
Match the leftmost prefix rule by index:
explain select * from tb_seller where name='Xiaomi Tech'
explain select * from tb_seller where name='Xiaomi Tech' and status='1'
explain select * from tb_seller where name='Xiaomi Tech' and status='1' and address='Beijing'
Illegal leftmost prefix rule, invalid index:
explain select * from tb_seller where status='1'
explain select * from tb_seller where status='1' and address='Beijing'
If the leftmost rule is met but a column is skipped, only the leftmost column index is valid:
explain select * from tb_seller where name='Xiaomi Tech' and address='Beijing'
3).Range query right column, cannot use index.
According to the first two field name s, the status query is indexed, but the last condition address does not use an index.
4).Do not operate on index columns, the index will fail.
5).String without single quotation marks invalidates the index.
Since, in a query, no single quotation marks are placed on the string, MySQL's query optimizer automatically converts the type, causing the index to fail.
6).Use override index whenever possible, avoid select ing *
Use override indexes whenever possible (queries that access only the index (index columns contain query columns completely)) and reduce select *.
If you query columns, exceeding index columns can also degrade performance.
TIP :
using index : Occurs when overwriting an index is used
using where: When looking for data that uses an index, you need to go back to the table to query the data you need
using index condition: Lookup uses an index but needs to query the data back in a table
using index ; using where: The lookup uses an index, but all required data can be found in the index column, so there is no need to query the data back to the table
7).Conditions separated by or, if a column in the precondition before or has an index and no index in the subsequent column, the index involved will not be used.
For example, the name field is an indexed column, while createtime is not. or joins in the middle and does not leave the index:
explain select * from tb_seller where name='Black Horse Programmer' or createtime = '2088-01-01 12:00:00'\G;
8).Like fuzzy queries starting with% invalidate the index.
If only a tail fuzzy match is used, the index will not fail. If a header fuzzy match is used, the index will fail.
Solution:
Solve by overwriting the index
9).If MySQL evaluation uses indexes more slowly than full tables, no indexes are used.
10).Is NULL, is NOT NULL sometimes has index failures.
11).In walk index, not in index is invalid.
12).Single column index and composite index.
Use composite indexes whenever possible and less single-column indexes.
create index idx_name_sta_address on tb_seller(name, status, address); This is equivalent to creating three indexes: name name + status name + status + address
Create a single-column index
create index idx_seller_name on tb_seller(name); create index idx_seller_status on tb_seller(status); create index idx_seller_address on tb_seller(address);
The database chooses an optimal index (the most recognizable index) to use, not all indexes.
View index usage
show status like 'Handler_read%'; show global status like 'Handler_read%';
Handler_read_first: The number of times the first item in the index has been read. A higher value indicates that the server is performing a large number of full index scans (the lower the value, the better). Handler_read_key: If the index is working, this value represents the number of times a row has been read by the index value. If the value is lower, the performance improvement of the index is not high because the index is not used frequently (the higher the value, the better). Handler_read_next : Number of requests to read the next row in key order. This value increases if you query an index column with a range constraint or if you perform an index scan. Handler_read_prev: Number of requests to read the previous line in key order. This reading method is mainly used to optimize ORDER BY ... DESC. Handler_read_rnd : Number of requests to read a row from a fixed location. If you are executing a large number of queries and need to sort the results, this value is higher. You may be using a large number of requirements MySQL Queries that scan the entire table or your joins do not use the keys correctly. A higher value means inefficient operation and should be indexed to remedy it. Handler_read_rnd_next: Number of requests to read the next row in the data file. This value is higher if you are doing a large number of table scans. Usually this indicates that your table index is incorrect or that the query you are writing does not utilize the index.