【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;
  • 63
    点赞
  • 55
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
Idea shared indexes是一种通过共享索引来加强创意和思维的方法。该方法旨在促进团队成员之间的合作和交流,以获得更好的创新结果。 在传统的创意生成过程中,每个团队成员通常只能看到自己的创意和想法。然而,这种隔离的方式可能会限制创意的范围和质量。因此,在使用idea shared indexes之前,团队成员将创意和想法记录在共享的索引中,使其他成员能够看到并提供反馈和改进建议。 通过使用共享索引,团队成员能够互相学习和影响彼此的创意。这种开放和合作的环境可以激发创造力和想象力,同时也增加了创意的多样性和质量。团队成员可以从其他成员的思维过程中获得灵感,并从中发展出更好的创意。 Idea shared indexes还可以提高团队之间的沟通和合作。成员之间可以通过共享索引来交流想法,并通过评论和讨论来深入理解和发展创意。这种协作可以帮助团队成员共同解决问题,并找到最佳的解决方案。 此外,idea shared indexes还可以提高创意的迭代和持续改进。通过不断记录、摘录和分享创意,团队成员可以进一步改进和完善创意,并在每一次迭代中获得更好的结果。 综合而言,idea shared indexes是一种通过共享索引来加强创意和思维的方法。它提供了一个开放和合作的环境,促进团队成员之间的交流和合作,提高创意的多样性和质量,并促进创意的迭代和持续改进。

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值