【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`)
)

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
  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. 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

Insert image description here

  1. 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

Insert image description here

SELECT * FROM student WHERE studentNo = '1000';

Insert image description here

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
  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. 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

Insert image description here

  1. 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;
  • 59
    点赞
  • 52
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值