Table structure and data

Id primary key (id: odd sex=1, ID: even sex=0, sex=0) Sex article = 1500, 00 data

CREATE TABLE `people` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL.`sex` tinyint(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8;
Copy the code
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_initData`() BEGIN DECLARE i INT DEFAULT 1; WHILE I <=100000 DO INSERT INTO people(name, sex) VALUES(CONCAT(' 名', I),0); SET i = i+1; END WHILE; END CALL proc_initData(); UPDATE people SET sex = 1 WHERE MOD(id,2) = 1;Copy the code

The type of index to be added

Test results:

SELECT * FROM people WHERE sex = 0;
SELECT * FROM people WHERE sex = 1;
Copy the code
No sex index Have sex index
sex=0
sex=1

EXPLAIN after adding index:

why

In InnoDB, every table has a clustered index. If the table has a primary key defined, the primary key is the clustered index. A table has only one clustered index and the rest are normal indexes. The index structure is a B+ tree, where non-leaf nodes store keys and leaf nodes store values.

  1. Clustered indexes, leaf nodes store row records, and InnoDB indexes and records are stored together.
  2. The leaf node stores the value of the primary key.

An example of the index structure of the above table is as follows (PS: Index structure is for reference only) clustered index

When a common index is used to query data, the system loads the common index first and searches for the primary key of the actual row through the common index. The primary key is then used to query the corresponding row through the clustered index. This loop queries all rows. If a clustered index is directly searched in full, there is no need to switch back and forth between normal and clustered indexes. Compared with the total overhead of the two operations, scanning the full table may be more efficient.

Reference: mp.weixin.qq.com/s/tmkRAmc1M… Blog. Jcole. Us/innodb/blog.csdn.net/u012978884/… Draveness. Me/mysql – innod…