【WEEK7】 【DAY1】Indexes【English Version】

2024.4.8 Monday

7. Indexes

7.1. Essence

The official MySQL definition of an index is: An index (Index) is a data structure that helps MySQL retrieve data efficiently.
Extracting the main point gives the essence of an index: An index is a data structure.
https://blog.codinglabs.org/articles/theory-of-mysql-index.html

7.2. Purpose of Indexes

1. Improve query speed.
2. Ensure the uniqueness of the data.
3. Can accelerate the connection between tables, achieving referential integrity between tables.
4. When using the grouping and sorting clauses for data retrieval, it can significantly reduce the time for grouping and sorting.
5. Optimize search in full-text search fields.

7.3. Types of Indexes

7.3.1. Primary Key Index (PRIMARY KEY)

A unique identifier, the primary key cannot be duplicated, and only one column can serve as the primary key.

7.3.1.1. Primary Key: A set of attributes that can uniquely identify a record.

7.3.1.2. Characteristics:

• The most common type of index.
• Ensures the uniqueness of data records.
• Determines the specific location of a data record in the database.

7.3.2. Unique Index (UNIQUE KEY)

7.3.2.1. Purpose: To prevent duplication of values in the same data column within a table.

7.3.2.2. Difference from Primary Key Index:

• There can only be one primary key index.
• There can be multiple unique indexes.

CREATE TABLE `Grade`(
  `GradeID` INT(11) AUTO_INCREMENT PRIMARY KEY,
  `GradeName` VARCHAR(32) NOT NULL UNIQUE
   -- Or UNIQUE KEY `GradeID` (`GradeID`)
)
1
2
3
4
5

7.3.3. Regular Index (KEY/INDEX)

7.3.3.1. Purpose: Quickly locate specific data.

7.3.3.2. Note:

• Both the index and key keywords can set a regular index.
• Should be added to the fields used in query conditions.
• It’s not advisable to add too many regular indexes as it affects the insert, delete, and modify operations of the data.

CREATE TABLE `result`(
   -- Some code omitted
  INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- Added when creating the table
)
-- Adding after creation
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
1
2
3
4
5
6

7.3.4. Full-Text Index (FULLTEXT)

7.3.4.1. Search on Baidu: Full-Text Index

7.3.4.2. Purpose: Quickly locate specific data.

7.3.4.3. Note:

• Only applicable to MyISAM type data tables.
• Only applicable to CHAR, VARCHAR, TEXT data column types.
• Suitable for large datasets.

7.3.4.4. Version Declaration, etc.

/*
Before starting, let's discuss the version, storage engine, and data type support for full-text indexes.

Before MySQL version 5.6, only the MyISAM storage engine supported full-text indexes;
From MySQL 5.6 and later versions, both MyISAM and InnoDB storage engines support full-text indexes;
Only data types such as char, varchar, and text and their series can have a full-text index.
When testing or using full-text indexes, check your MySQL version, storage engine, and data type for support.
*/
1
2
3
4
5
6
7
8

7.3.4.5. Using Indexes

/*
#Method 1: When creating a table
  　　CREATE TABLE table_name (
               column_name1 data_type [integrity constraints…],
               column_name2 data_type [integrity constraints…],
               [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
               [index_name] (column_name[(length)] [ASC |DESC])
               );


#Method 2: CREATE index on an existing table
       CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX index_name
                    ON table_name (column_name[(length)] [ASC |DESC]) ;


#Method 3: ALTER TABLE to create an index on an existing table
       ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                            index_name (column_name[(length)] [ASC |DESC]) ;
                           
                           
#Delete an index: DROP INDEX index_name ON table_name;
#Delete a primary key index: ALTER TABLE table_name DROP PRIMARY KEY;


#Show index information: SHOW INDEX FROM student;
*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

7.3.4.6. Examples

1. Show all index information

-- Indexes --
/* Using indexes
1. Add indexes to fields when creating a table
2. After the table is created, add or remove indexes
*/
-- Show all index information
use `school`
SHOW INDEX FROM student
1
2
3
4
5
6
7
8

2. Add a full-text index (index name) column name

-- Add a full-text index (index name) column name
ALTER TABLE school.student ADD FULLTEXT INDEX `studentName`(`StudentName`)	-- The name before the brackets is the index name, inside the brackets is the column name
-- Run SHOW INDEX FROM student again after adding, and notice an additional row in the indexes
1
2
3

3. EXPLAIN to analyze the performance of SQL statement execution

-- EXPLAIN to analyze the performance of SQL statement execution
EXPLAIN SELECT * FROM student;	-- Non-full-text index
1
2

SELECT * FROM student WHERE studentNo = '1000';
1

7.3.4.7. Use full-text indexes

/* Use the full-text index */
The full-text search is done using the MATCH() function.
- The search string is given as an argument against(). The search is performed ignoring the case of letters. For each row in the table, MATCH() returns a correlation value. That is, the similarity scale between the search string and the text of the column specified by the row in the MATCH() list.
SELECT * FROM student WHERE MATCH(studentName) AGAINST(' Wu ')
1
2
3
4

7.3.4.8. Testing Indexes

1. Create Table

-- Stored in database `school`
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 Email',
	`phone` VARCHAR(20) DEFAULT '' COMMENT 'Phone Number',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Gender (0: Male; 1: Female)',
	`password` VARCHAR(100) NOT NULL COMMENT 'Password',
	`age` TINYINT(4) DEFAULT '0' COMMENT 'Age',
	-- `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,	-- This line (including default value) can't run in MySQL 5.5
	`create_time` DATETIME DEFAULT '1999-01-01 01:01:01' COMMENT 'Trying this',
	`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT = 'app user table'
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2. Insert Data

DROP FUNCTION IF EXISTS mock_data
-- Inserting 1 million records.
delimiter $$	-- Must be written before writing a function, as a marker
-- set global log_bin_trust_function_creators=TRUE;

CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
-- declare statement
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	
-- 	Loop operation
	WHILE i<num DO
		-- 	Insert statement
		INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)
		VALUES(CONCAT('User',i),'19224305@qq.com',CONCAT('18',FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
-- 		UUID() is an abbreviation for Universally Unique Identifier
		SET i=i+1;
	END WHILE;
	RETURN i;
	
END;
SELECT mock_data(); -- Execute this function to generate one million records. Running this function requires some time
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3. Query the 9999th Record

• Without Index

SELECT * FROM app_user WHERE `name` = 'User9999';	-- > Time: 0.344s
EXPLAIN SELECT * FROM app_user WHERE `name` = 'User9999';		-- > rows as 991749
1
2

• With Index

-- Creating an index
-- Naming the new index as id_table_column
-- CREATE INDEX index_name ON table(column)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- Retrieval time decreases after creating the index
SELECT * FROM app_user WHERE `name` = 'User9999';	-- > Time: 0s
EXPLAIN SELECT * FROM app_user WHERE `name` = 'User9999';	-- > rows as 1
1
2
3
4
5
6
7
8

7.4. Index Guidelines

7.4.1. More indexes are not always better.
7.4.2. Do not add indexes to data that changes frequently.
7.4.3. It’s advised not to add indexes to tables with a small amount of data.
7.4.4. Indexes should generally be added to the fields used in search conditions.

7.5. Data Structures of Indexes

-- When creating the above index, we can specify the index type, divided into two categories:
hash type index: Fast for single-row queries, slow for range queries;
btree type index: B+ tree, the more layers, the exponential growth of data volume (we use it because InnoDB supports it by default).

-- Different storage engines support different types of indexes:
InnoDB supports transactions, row-level locking, B-tree, Full-text, and other indexes, but does not support Hash indexes;
MyISAM does not support transactions, supports table-level locking, B-tree, Full-text, and other indexes, but does not support Hash indexes;
Memory does not support transactions, supports table-level locking, B-tree, Hash, and other indexes, but does not support Full-text indexes;
NDB supports transactions, row-level locking, supports Hash indexes, but does not support B-tree, Full-text, and other indexes;
Archive does not support transactions, supports table-level locking, does not support B-tree, Hash, Full-text, and other indexes;
1
2
3
4
5
6
7
8
9
10