An index is a data structure that helps Mysql obtain data efficiently. Further, we can see that the essence of an index is a data structure.

I. Classification of indexes

  • PRIMARY KEY index: also known as the PRIMARY KEY, there is only one column as the PRIMARY KEY, unique identifier, cannot be repeated.
  • A UNIQUE index can be set for multiple columns in the same table.
  • Normal INDEX: KEY/INDEX, the default type, by keywordINDEXorKEYTo set up.
  • Full-text index: FullText, which is only supported under specific database engines and is used to quickly locate data.

Use indexes

You can add indexes to a field either after the table is created or after it is created.

For example, when I create a test table, I add the first three types of indexes:

CREATE TABLE student (' StudentNo 'INT(4) NOT NULL COMMENT "student ",' LoginPwd 'VARCHAR(20) DEFAULT NULL, 'StudentName' VARCHAR(20) DEFAULT NULL COMMENT "StudentName ", 'Sex' TINYINT(1) DEFAULT NULL COMMENT" 0-1", 'GradeId' INT(11) DEFAULT NULL COMMENT "GradeId ", 'Phone' VARCHAR(50) NOT NULL COMMENT" Phone ", 'Address' VARCHAR(255) NOT NULL COMMENT "Address ", 'BornDate' DATETIME DEFAULT NULL COMMENT" date ", 'Email' VARCHAR(50) NOT NULL COMMENT "iD ", 'IdentityCard' VARCHAR(18) DEFAULT NULL COMMENT" iD ", PRIMARY KEY (' StudentNo '), -- UNIQUE KEY 'IdentityCard' (' IdentityCard '), INNODB DEFAULT CHARSET=utf8; INNODB DEFAULT CHARSET=utf8;Copy the code

The SQL database is successfully created. At this point I continue to add a full-text index.

ALTER TABLE 'school'. 'student' ADD FULLTEXT INDEX 'StudentName' (' StudentName ');Copy the code

Mysql > alter table student select * FROM student;

There is also a third type, CREATE INDEX name on table (field), which will be shown later.

Three, millions of data test index effect

1. Create another test table

CREATE TABLE 'app_user' (' id 'BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, 'name' VARCHAR(50) DEFAULT 'COMMENT ',' email 'VARCHAR(50) DEFAULT NULL COMMENT" 'phone' VARCHAR(20) DEFAULT NULL COMMENT "phone number ", 'gender' TINYINT(4) DEFAULT NULL COMMENT" gender 0-male, 1-female ", 'password' VARCHAR(100) NOT NULL COMMENT "password ", 'age' TINYINT(4) NOT NULL COMMENT" age ", `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP, `update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (' id '))ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='app ';Copy the code

The creation was successful. Now insert data.

2. Insert data

DELIMITER $$CREATE FUNCTION mock_data() RETURNS INT BEGIN DECLARE num INT DEFAULT 1000000; DECLARE i INT DEFAULT 0; WHILE I < num DO -- INSERT INTO 'school'. 'app_user' (' name ', 'email', 'phone', 'gender', 'password', 'age')VALUES (CONCAT (' users', I), '123456 @qq.com' CONCAT (' 18 'FLOOR (RAND () * (100000000) (999999999-100000000) +)), FLOOR (RAND () * 2), UUID(), FLOOR(RAND()*100)); SET i = i+1; END WHILE; RETURN i; END;Copy the code

This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary

If so, run set Global log_BIN_trust_function_Creators =TRUE and try again.

After the command is successfully executed, run the following command again:

SELECT mock_data();
Copy the code

It should last about a minute. Don’t worry.

3. Test the query

Without index

Query a piece of data.

SELECT * FROM `app_user` WHERE `name`='用户9999'
Copy the code

Execute the query for many times, and find that the time is stable at about 0.63s. This is a little slow to be perceived by the naked eye from click the execution to see the result.

We can add the keyword EXPLAIN to analyze SQL execution.

EXPLAIN SELECT * FROM 'app_user' WHERE 'name' =' user 'Copy the code

As you can see, this statement checked 99W+ entries, which is all time consuming.

Add an index Add a regular index in the third way described above.

CREATE INDEX id_app_user_name ON app_user(`name`);
Copy the code

Run the query again:

SELECT * FROM `app_user` WHERE `name`='用户9999'
Copy the code

Query time is greatly shortened, only need to 0.1s+.

Reanalyze the indexed query.

EXPLAIN SELECT * FROM 'app_user' WHERE 'name' =' user 'Copy the code

We only ran one. It was an accurate search.

3. Principles of index use

Indexes are useful, but not overused. Here are a few principles to keep in mind:

  • More indexes are not always better.
  • Do not index data that changes frequently.
  • Tables with small data volumes do not need indexes.
  • Indexes are generally added to fields that are commonly queried.

The above is a simple introduction to the index, but the MySQL index behind the data structure and algorithm principle, things can be more, there is a big man said very detailed, interested can turn down, portal