mysql optimization-use case of clustering index and index coverage

 

 

 

 

Table smth has 10,000 rows of data with two fields of 3,000 length.

select id from smth order by id; very slow

select id from smth order by id,ver; faster

Reason?

 

 

create  table smth(

id int(11) not null default 0,

ver int(11) default null,

content varchar(3000)  default null,

intro varchar(3000) default null,

primary key (id),

key idver(id,ver)

)engine =InnoDB default charset=utf8;



create  table mysmth(

id int(11) not null default 0,

ver int(11) default null,

content varchar(3000)  default null,

intro varchar(3000) default null,

primary key (id),

key idver(id,ver)

)engine =myisam default charset=utf8;


 

delimiter $$

create procedure myproc ()

begin

declare num int;

set num=1;

while num< 10001 do

insert into smth(id,ver,content,intro)

values

(num,num+200,concat('name',num),concat('in',num));

set num=num+1;

end

while;

end$$

 

Generate 10000 rows of data;

 

call myproc()$$

 


 

mysql> show create table smth;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                                                                          |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| smth  | CREATE TABLE `smth` (

  `id` int(11) NOT NULL DEFAULT '0',

  `ver` int(11) DEFAULT NULL,

  `content` varchar(3000) DEFAULT NULL,

  `intro` varchar(3000) DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `idver` (`id`,`ver`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set

mysql> select count(1) from  smth;

+----------+

| count(1) |

+----------+

|    10000 |

+----------+

1 row in set


 

//Open profiling

mysql> set profiling =1;

Query OK, 0 rows affected







mysql> show profiles;

+----------+------------+--------------------------------------+

| Query_ID | Duration   | Query                                |

+----------+------------+--------------------------------------+

|        1 | 0.01072775 | select id  from smth order by id     |

|        2 |   0.036068 | select id from  smth order by id,ver |

+----------+------------+--------------------------------------+

3 rows in set











mysql> show profile for  query 1;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000122 |

| checking permissions | 2.7E-5   |

| Opening tables       | 3.1E-5   |

| init                 | 2.8E-5   |

| System lock          | 1.5E-5   |

| optimizing           | 6E-6     |

| statistics           | 2.3E-5   |

| preparing            | 3.1E-5   |

| Sorting result       | 8E-6     |

| executing            | 3E-6     |

| Sending data         | 0.009794 |

| end                  | 8.2E-5   |

| query end            | 1.8E-5   |

| closing tables       | 1.2E-5   |

| freeing items        | 0.000498 |

| cleaning up          | 3.4E-5   |

+----------------------+----------+

16 rows in set





mysql> show profile for  query 2;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| starting             | 0.000116 |

| checking permissions | 1E-5     |

| Opening tables       | 6.1E-5   |

| init                 | 2.5E-5   |

| System lock          | 1.1E-5   |

| optimizing           | 4E-6     |

| statistics           | 1.6E-5   |

| preparing            | 1.1E-5   |

| Sorting result       | 3E-6     |

| executing            | 2E-6     |

| Sending data         | 0.009261 |

| end                  | 1.3E-5   |

| query end            | 2.7E-5   |

| closing tables       | 1.1E-5   |

| freeing items        | 0.000354 |

| cleaning up          | 0.026144 |

+----------------------+----------+

16 rows in set



 

Issues involved

1: the direction of innodb, myisam index;

myisam's index points to the location of data on disk

innodb's index points to a reference to the primary key value

innodb does not have contiguous data blocks, data files

 

 

 

2) Cluster Index

1. Cluster index is not a separate way, so it is a way of data storage.

InnoDB's clustering index actually saves the B-Tree index and data rows in the same structure.

2. When a table has a clustered index, its data rows are actually stored in the leaf pages of the index. "Cluster" denotes that rows of data are compactly stored together with adjacent key values.

3. For InnoDB, if the table does not define a primary key, a unique non-empty index will be selected instead. Without such an index InnoDB implicitly defines a primary key as a clustered index. InnoDB only aggregates records on the same page.

4. Advantages of Cluster Index:

(1) Relevant data can be saved together.

(2) Data access is faster. Data and indexes are stored in the same B-Tree.

(3) Queries scanned with overlay index can directly use the primary key value of the page node

 

5. The disadvantage of clustering index:

(1) Cluster index greatly improves the performance of I/O intensive applications, but if all the data are put in memory, the order of data is less important, and cluster index has no advantage.

 

(2) The insertion speed depends heavily on the insertion order. Primary key insertion is InnoDB's fastest way, but if the data is not loaded in the order of primary keys, it's better to reorganize the following table with OPTIMIZE TABLE command after loading.

(3) The cost of updating clustered index columns is high. Because InnoDB is forced to move each updated row to a new location

3) Overlay Index

 

1.mysql can use the index to get the data of the column directly, so that it can no longer need to read the data rows.

If the leaf node of the index already contains the data to be queried, then what is the need to query back to the table? If an index contains (overrides) the values of all fields to be queried, it is called a "override index"

2. Overlay index can improve the performance of query, and it does not need to know tables. The advantages are as follows:

(1) Index entries are usually smaller than data rows, and mysql reduces the number of visits if only the index is read

(2) Indexes are stored in the order of column values, and index I/O-intensive range queries are much less than I/O that randomly reads each row of data from disk.

(3) Some storage engines, such as MyISAM, only cache indexes in memory, and the data is cached by the operating system, so accessing the data requires a system call, which can cause serious performance problems, especially those scenarios where system calls account for the most overhead in data access.

(4)InnoDB clustering index, covering index is particularly useful for InnoDB tables. InnoDB's secondary index keeps the primary key value of the row in the leaf node, so if the secondary primary key can override the query, the secondary query of the primary key index can be avoided.

 

3

select id from order where user_id between 1 and 3

At this time, we only need to look up the value of the ID, which is already in the user_id index tree, so we can directly provide the query results without requiring a return table.

 

select * from order where user_id between 1 and 3

Once you use select *, there will be other columns to read, and then you will need to read data to return the results after reading index.

 

The performance of these two methods is very different, especially when the number of rows returned is large and I/O is needed to read data, there may be dozens or hundreds of times difference. It is therefore recommended that select be used as needed.*

 

 

 

 

 

 

 

Tags: MySQL less

Posted on Sat, 10 Aug 2019 04:15:37 -0700 by .Stealth