Database - index

Database - index

MySQL's official definition of Index is: Index is a data structure that helps MySQL obtain data efficiently.

There is a big difference between 0.5s for data acquisition and 0.001s for data acquisition!

Therefore, it is necessary to study index!

The essence of index: index is data structure.

1, Principles of indexing

  • Index is not more is better;
  • Do not index process change data;
  • Small data tables do not need to be indexed;
  • Indexes are usually added to fields that are often queried.

2, Classification of indexes

In a table, there can only be one primary key index and multiple unique indexes;

  • PRIMARY KEY index
    • Unique identification. The primary key cannot be duplicate. Only one column can be used as the primary key;
  • UNIQUE KEY
    • Avoid duplicate columns. Unique indexes can be duplicated. Multiple columns can identify unique indexes;
  • General index (KEY/INDEX)
    • The default index. Set by key keyword;
  • Full text
    • Only under a specific database engine, MyISAM;
    • Fast positioning data;

Basic grammar

-- Use of index
-- 1,Index fields when creating tables
-- 2,Add index after creation

-- Show all index information
SHOW INDEX FROM student

-- Add a full-text index (index name) column name
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`studentName`);

-- EXPLAIN Analysis sql Status of implementation
EXPLAIN SELECT * FROM student; -- Non full text index

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('Liu');

3, Test index

Create a user table first

CREATE TABLE `app_user` (
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT 'User nickname',
	`email` VARCHAR(50) NOT NULL COMMENT 'User mail box',
	`phone` VARCHAR(20) DEFAULT '' COMMENT 'Cell-phone number',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Sex (0:Male; 1: female)',
	`password` VARCHAR(100) NOT NULL COMMENT 'Password',
	`age` TINYINT(4) DEFAULT '0' 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=utf8mb4 COMMENT='app User table'

Insert 1 million pieces of data (using database programming)

DELIMITER $$ -- Must write before writing function, flag
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO
app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('use
//Households',i),'24736743@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-
100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
		SET i = i+1;
	END WHILE;
	RETURN i;
END;
SELECT mock_data(); -- I used 1 min 3sec

SELECT * FROM app_user WHERE `name` = 'User 9999'; -- I used 1.220sec

Create index

-- id _ Table name _ Field name
-- CREATE INDEX Index name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

SELECT * FROM app_user WHERE `name` = 'User 9999'; -- Use time 0.001sec
EXPLAIN SELECT * FROM app_user WHERE `name` = 'User 9999'; -- Unique location, time 0.001sec

Disadvantages of index:

  • Index is not very useful when the amount of data is small, its advantages are not obvious, and it may reduce the query speed;
  • But in the case of large amount of data, the advantages are obvious, and the query speed is greatly improved.
57 original articles published, 4 praised, 762 visited
Private letter follow

Tags: Database MySQL SQL Programming

Posted on Tue, 04 Feb 2020 09:52:53 -0800 by peterj