MySQL Learning Note (iii)-index

I. Overview 1. Basic Concepts

In a large database, a table holds tens of thousands of, hundreds of thousands of, or even millions of of the data, and when the tables are connected to other tables, the new number of data is much larger than the original table. When users retrieve such a large amount of data, they often feel slow. This time to improve the database retrieval performance, it is necessary to use the index. Appending a proper index to a table can greatly improve the efficiency of data retrieval and provide database performance.

Indexes are implemented in the storage engine, so the indexes for each storage engine are not necessarily identical, and each storage engine does not necessarily support all index types.

All storage engines support up to 16 indexes per table, with a total index length of up to 256 bytes.

There are two types of storage for indexes in MySQL: The B-tree and Hash,myisam and InnoDB storage engines only support B-tree indexes. Memory and heap can support hash and b-tree indexes.

Setting an index for a table is a price to pay:

(1) Increase the storage space of the database.

(2) It takes more time to insert and modify the data (because the position of the index is changed as well).

2. Advantages and disadvantages of indexes

(1) Advantages

A: The uniqueness of each row of data in a database table is ensured by creating a unique index.

B: Greatly speed up the data query.

C: When you use grouping and sorting for data queries, you can significantly reduce the time to group and sort in queries.

(2) Disadvantages

A: Maintaining indexes consumes database resources.

B: The index takes up disk space, and the index file may reach the maximum file size faster than the data file.

C: When the data of the table and additions and deletions are changed, because to maintain the index, the speed will be affected.

Indexes are built on top of some columns in a database table. When you create an index, you should consider which columns you can create indexes on and which columns you cannot create indexes on. in general, you should create indexes on these columns.

A: on the columns that often need to search, you can speed up the search;

B: on the column that is the primary key, enforce the uniqueness of the column and the arrangement of the data in the organization table;

C: in often used in the connected columns, these columns are mainly foreign keys, you can speed up the connection;

d: Create an index on a column that often needs to be searched by scope, because the index is sorted and its specified range is continuous;

e: Create an index on a column that is often ordered, because the index is sorted so that the query can use the sorting of the index to speed up the sort query time;

F: It is often used to create an index above the column in the WHERE clause to speed up the judgment of the condition.

In general, these columns that should not create an index have the following characteristics

A: you should not create an index for columns that are seldom used or referenced in queries. This is because, since these columns are seldom used, they are indexed or non-indexed and do not improve query speed. Conversely, by increasing the index, it reduces the system maintenance speed and increases the space requirement.

B: you should not increase the index for columns that have only a few data values. This is because, because these columns have very few values, such as the gender column of the personnel table, in the results of the query, the data rows of the result set occupy a large proportion of the data rows in the table, that is, the data rows that need to be searched in the table are large. Increasing the index does not significantly speed up the retrieval.

C: for those columns that are defined as text, the image and bit data types should not increase the index. This is because the amount of data in these columns is either quite large or has very little value.

d: You should not create an index when the performance of the modification is far greater than the retrieval performance. This is because modifying performance and retrieving performance are conflicting . When you increase the index, the retrieval performance is improved, but the performance of the modification is reduced. When you reduce the index, you increase the performance of the modification and reduce the retrieval performance. Therefore, you should not create an index when the performance of the modification is far greater than the retrieval performance.

Ii. classification of the Index 1. General Index

This is the most basic index, it has no restrictions, such as the previous index created for the title field is a normal index, myiasm in the default index of the Btree type, but also in most cases we use the index.

#直接创建索引CREATE INDEX index_name on table (COLUMN (length)), #修改表结构的方式添加索引ALTER table table_name ADD INDEX index_name on (COLU MN (length)); #创建表的时候同时创建索引CREATE table ' table ' (' ID ' INT (one) not NULL auto_increment, ' title ' CHAR (255) CHARACTERSET UTF8 COLLATE utf8_general_ci not NULL, ' content ' text CHARACTERSET UTF8 COLLATE utf8_general_ci null, ' time ' INT (ten) null DEFA ULT NULL, PRIMARY KEY (' id '), index index_name (length)), #删除索引DROP index index_name on TABLE;

2. Unique index

a unique index is one that does not allow any two rows to have the same index value.

the value of the indexed column must be unique, but allow null values, which are different from the primary key.  

When duplicate key values exist in existing data, most databases do not allow a newly created unique index to be saved with the table. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the Employees table, none of the two employees will have a namesake.

#创建唯一索引CREATE unique INDEX indexname on TABLE (column), #修改表结构ALTER table table_name ADD UNIQUE indexname on (column); #创建表的 When directly specifying CREATE TABLE ' table ' (' ID ' INT (one) not NULL auto_increment, ' title ' CHAR (255) CHARACTERSET UTF8 COLLATE Utf8_genera L_ci not NULL, ' content ' text CHARACTERSET UTF8 COLLATE utf8_general_ci null, ' time ' INT (TEN) null DEFAULT null, PRIMARY K EY (' id '), UNIQUE IndexName (title));

3. Full-Text Indexing

The fulltext index can be used for full-text search. Only the MyISAM storage engine supports Fulltext indexes, and only char, varchar, and text types are supported.

#创建表的适合添加全文索引CREATE table ' table ' (' ID ' INT (one) not NULL auto_increment, ' title ' CHAR (255) CHARACTERSET UTF8 COLLATE UTF8 _general_ci not NULL, ' content ' text CHARACTERSET UTF8 COLLATE utf8_general_ci null, ' time ' INT (TEN) null DEFAULT NULL, PR Imary KEY (' id '), fulltext (content), #修改表结构添加全文索引ALTER TABLE article ADD fulltext index_content (content); #直接创建索引CREATE Fulltext INDEX index_content on article (content);

4. Composite Index

A federated index is also called a composite index. For composite indexes: MySQL left-to-right uses fields from the index, and a query can use only one part of the index, but only the leftmost section. For example, the index is key index (A,B,C). can support a | a,b| A,b,c 3 combinations to find, but B,c is not supported. The index is very effective when the leftmost field is a constant reference.

With additional columns in the index, you can narrow the scope of your search, but using an index with two columns differs from using two separate indexes. The structure of a composite index is similar to a phone book, where a person's name consists of a surname and a name, and the phone book is first sorted by last name, and then by name for people with the same last name. If you know the last name, the phone book will be useful, and if you know the first and last names, the phone book is more useful, but if you only know the first name, the phone book will be useless.
So when you create a composite index, you should carefully consider the order of the columns. A composite index is useful when you perform a search on all columns in an index or when you perform a search on only the first few columns, and the composite index is useless when you perform a search on any subsequent column.

ALTER TABLE article ADD INDEX index_titme_time (title (), Time (10));

Creating such a composite index is actually equivalent to establishing the following two sets of composite indexes:

–title,time

–title

Why is there no such combination index as time? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. Not all queries that contain these two columns will use the combined index, as shown in the following SQL:

#会使用到上面的索引SELECT * from article Whree title= ' test ' and time=1234567890; SELECT * from article Whree utitle= ' test '; #不会使用上面的索引SELECT * from article Whree time=1234567890;

MySQL Learning Note (iii)-index