table of Contents
MySQL's official definition of index is: Index is a data structure that helps MySQL obtain data efficiently. The essence of the index can be obtained by extracting the main sentence of the sentence: the index is a data structure.
The index actually extracts a frequently used field to establish a separate data organization structure. Just like a dictionary, we extract their first letters to form a directory. When we query the directory, we can directly query the directory, which can greatly reduce our query speed. While improving the query speed, when we need to modify the data, the modification speed will be correspondingly reduced, because we also need to modify our index at the same time.
So when we have a large amount of data, we use indexes to increase our query speed. When the amount of data is small, it is not worthwhile to index the data specifically.
At the same time, when we build an index, we should consider the fields that should be indexed, and add indexes to the fields that are frequently used for query conditions, and do not do wasteful work.
The index data storage is implemented using BTree. This picture is theoretically used to store our index in a binary tree, but because the h of the B tree is much smaller than the h of the red-black tree, this leads to the disk IO read in the index The efficiency is much higher than that of the red-black tree, so the B-tree is used to realize the storage of the index.
1.1 Classification of the index
Indexes are divided into two categories, one is a single-column index, and the other is a composite index.
Combined index is to combine multiple indexes of the same type together, which is more efficient than multiple single-column indexes.
Basic types of single-column indexes :
primary key primary key index
The primary key index is unique and cannot be empty. A table can only set one field as the primary key index.
The unique index key value is unique (except for null), and one or more null values can appear.
index Ordinary index
Normal index has no constraints
Full-text index, when the amount of data is very large, when we use like %a%; this kind of statement query efficiency is very low, and the use of global index can greatly improve the efficiency of fuzzy query.
1.2 Use of Index
View index instructions
SHOW INDEX FROM table_name
If it is CHAR, VARCHAR type, length can be less than the actual length of the field, shortening the length can improve the storage efficiency of the index.
If it is BLOB and TEXT type, length must be specified
CREATE INDEX index_name ON table_name(PROPERTY(length)); //为table_name表的property属性设置长度为length的index普通索引，索引名字设置为index_name;
How to create an index
--创建普通索引--CREATE INDEX index_name ON test(NAME(10));ALTER TABLE test ADD INDEX index_name(NAME(10))CREATE TABLE test1( cid INT(10), NAME VARCHAR(20), pid INT(10), INDEX index_name(NAME(10)) )--创建唯一索引--CREATE UNIQUE INDEX unique_name ON test(NAME(10));ALTER TABLE test ADD UNIQUE INDEX unique_name(NAME(10));CREATE TABLE test1( cid INT(10), NAME VARCHAR(20), pid INT(10), UNIQUE INDEX unique_name(NAME(10)))--创建全文索引--CREATE FULLTEXT INDEX fulltext_name ON test(NAME(10))ALTER TABLE test ADD FULLTEXT INDEX fulltext_name(NAME(10))CREATE TABLE test3( cid INT(10), NAME VARCHAR(20), pid INT(10), FULLTEXT INDEX fulltext_name(NAME(10)))--创建主键索引--ALTER TABLE test ADD PRIMARY KEY(cid); #不支持指定长度CREATE TABLE test1( cid INT(10), NAME VARCHAR(20), pid INT(10), PRIMARY KEY(cid))--创建组合索引--#传统方式CREATE INDEX index_name ON test(NAME);CREATE INDEX index_cid ON test(cid);CREATE INDEX index_pid ON test(pid);CREATE INDEX index_name_cid_pid ON test(NAME,cid,pid);#两者功能相等，第二种方式会在底层也是创建三种索引，但是会将三种索引进一步聚合，从而使查询效率进一步提高。ALTER TABLE test ADD INDEX index_name_cid_pid(NAME,cid,pid);CREATE TABLE test2( cid INT(10), NAME VARCHAR(20), pid INT(10), INDEX index_name_cid_pid(cid,pid,NAME))
Some parameters are inconsistent when creating different types of indexes, and you need to be more careful when using them.
Ways to delete indexes
--删除普通索引--DROP INDEX index_name ON test;ALTER TABLE test DROP INDEX index_name;--删除唯一索引--DROP INDEX unique_name ON test;ALTER TABLE test DROP INDEX unique_name;--删除全文索引--DROP INDEX fulltext_name ON test;ALTER TABLE test DROP INDEX fulltext_name;--删除主键索引--ALTER TABLE test DROP PRIMARY KEY;--删除组合索引--DROP INDEX index_name_cid_pid ON test;ALTER TABLE test DROP INDEX index_name_cid_pid;
Use index to query data
Analyze keywords explain
explain select name from test where id=1;
Use the keyword explain to analyze the query status of the query statement.
In addition to the global index, we only need to be responsible for creating other indexes without explicit declaration. When in use, you can query directly based on the indexed field.
1.3 Use of full-text index
The full-text index is special. After the full-text index is defined, we need to explicitly call it in a specific format.
Database version requirements
At the same time, full-text indexing also has certain requirements for the version of the database
Prior to MySQL 5.6, only the MyISAM storage engine supports full-text indexing;
MySQL 5.6 and later versions, MyISAM and InnoDB storage engines both support full-text indexing;
Only the data type of the field is char, varchar, text and their series can build a full-text index.
View database engine full-text index parameters
show variables like '%ft%';
InnoDB default keyword maximum length is 84 characters, minimum length is 4 characters.
The maximum amount of data allowed to be queried is 8 million.
Sometimes we want to get search results through a keyword, but the database engine does not support it. At this time, we can manually modify the parameters of the full-text index.
innodb_ft_min_token_size = 1；
The parameter can be modified directly by assigning the variable name.
Two categories of indexes
The index is divided into natural language full-text index and Boolean full-text index
Full-text index of natural language
The natural language search engine will calculate the relevance of each document object and query.
Here, the relevance is based on the number of matched keywords and the number of times the keywords appear in the document. The fewer words that appear in the entire index, the higher the relevance during matching. Conversely, very common words will not be searched,
Boolean full-text index
The Boolean full-text index is similar to the like statement. We use some wildcards to match the data more accurately.
+ Must contain the word
- must not contain the word
> Improve the relevance of the word, the query results are higher
< Reduce the relevance of the word, and the result of the query is lower
(*)Asterisk wildcard, which can only be followed by words
Index query data
select name,cid,pid where match(name) against("张") #自然语言select name,cid,pid where match(name) against("+张" in boolean model) #布尔型select name,cid,pid where match(name) ageainst(">张" in boolean model)select name,pid,cid where match(name) ageainst("*张" in boolean model)