2024.4.8 Monday
Contents
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
- Improve query speed.
- Ensure the uniqueness of the data.
- Can accelerate the connection between tables, achieving referential integrity between tables.
- When using the grouping and sorting clauses for data retrieval, it can significantly reduce the time for grouping and sorting.
- 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`)
)
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`);
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.
*/
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;
*/
7.3.4.6. Examples
- 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
- 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
- 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
SELECT * FROM student WHERE studentNo = '1000';
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 ')
7.3.4.8. Testing Indexes
- 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'
- 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
- 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
- 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
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;