Mysql index details

Mysql index details

Mysql index Overview

All MySQL column types can be indexed. Using indexes for related columns is the best way to improve the performance of SELECT operations. Define the maximum number of indexes and the maximum index length for each table based on the storage engine. All storage engines Support at least 16 indexes for each table, with a total index length of at least 256 bytes. Most storage engines have higher limits.

In MySQL 5.1, for MyISAM and InnoDB tables, the prefix length can reach 1000 bytes. Note that the prefix limit should be measured in bytes, while the prefix length in the create table statement is interpreted as the number of characters. You must consider specifying the prefix length for a column that uses a multi-byte character set.

You can also create FULLTEXT indexes. This index can be used for full-text search. Only the MyISAM storage engine supports FULLTEXT indexes and only CHAR, VARCHAR, and TEXT columns. The index always applies to the entire column and does not support partial (prefix) indexes. You can also create an index for the spatial column type. Only the MyISAM storage engine supports the space type. Spatial indexes use the R-tree. By default, the MEMORY (HEAP) storage engine uses hash indexes, but also supports B-tree indexes.

Index Design Principles

1) The index column to be searched is not necessarily the column to be selected.

In other words, the column most suitable for indexing is the column that appears in the WHERE clause or the column specified in the join clause, rather than the column in the selection list after the SELECT keyword.

2) use a unique index.

Consider the distribution of values in a column. For columns with unique values, the index effect is the best, while for columns with multiple duplicate values, the index effect is the worst. For example, columns with different age values can easily distinguish rows. The columns used to record gender only contain "M" and "F", so it is not very useful to index this column (no matter which value is searched, it will produce about half of the rows ).

3) use short indexes.

If you index a string or column, you should specify a prefix length. If possible, you should do this. For example, if a CHAR (200) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Indexing the first 10 or 20 characters can save a lot of index space and make the query faster. A smaller index involves less disk I/O, and a shorter value is faster. More importantly, for shorter key values, the index cache blocks can accommodate more key values. Therefore, MySQL can also accommodate more values in the memory. This increases the possibility of finding rows without reading more data from the index. (Of course, some common sense should be used. If you use only the first character of the column value for indexing, there will be no different values in this index .)

4) use the leftmost prefix.

When creating an index with n columns, you actually created n indexes available for MySQL. Multi-column indexes can act as several indexes, because the leftmost column set in the index can be used to match rows. Such a column set is called the leftmost prefix. (This is different from the prefix of an indexed column. The prefix of an indexed column uses the first n characters as the index value .)

5) do not over-indexing.

Do not think that the index is "more and better". It is wrong to use indexes for anything. Each additional index takes up extra disk space and reduces write performance. We have already discussed this. When modifying the table content, the index must be updated and may sometimes need to be reconstructed. Therefore, the more indexes, the longer the time it takes. If an index is rarely used or never used, the modification speed of the table is not necessarily slowed down. In addition, it takes time for MySQL to consider indexes when generating an execution plan. Creating redundant indexes brings more work to query optimization. Too many indexes may cause MySQL to fail to select the best index to be used. Maintaining only the required indexes is conducive to query optimization. To add an index to an indexed table, consider whether the index to be added is the leftmost index of an existing multi-column index. If yes, you do not need to add this index because it already exists.

6) Compare the types of columns.

Indexes can be used for operations such as <, <= "," = ","> = ","> ", and. When the pattern has a direct volume prefix, the index is also used for the LIKE operation. If you only use a column for other types of operations (such as STRCMP (), there is no value for indexing it.

B-tree index and hash Index

For B-tree and HASH indexes, when the =, <=>, IN, IS NULL, or IS NOT NULL operator IS used, the comparison between key elements and constant values corresponds to a range condition. Hash indexes also have some other features: they are used only for Equality comparison using the = or <=> operator (but very quickly ). The optimizer cannot use hash indexes to accelerate the order by operation. (This type of index cannot be used to search for the next entry in order ). MySQL cannot determine the number of rows between two values (this is used by the range optimizer to determine which index to use ). If you change a MyISAM table to a hash-index MEMORY table, some queries will be affected. You can only use the entire keyword to search for a row. (Use B-tree indexes. You can use the leftmost prefix of any keyword to find the row ).

For B-tree indexes, when >,<=, <=, BETWEEN, and ,! = Or <>, or LIKE 'pattern' (where 'pattern' does not start with a wildcard) operator, the comparison between the key element and the constant value corresponds to a range condition. "Constant Value" refers to the expression that queries the constants in a string, the const in the same join, the columns in the system table, the results of non-correlated subqueries, and completely composed of the preceding types of subexpressions.

The following are examples of queries with range conditions in the WHERE clause.

The following range of queries apply to the btree index and hash index:
Copy codeThe Code is as follows: SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15, 18, 20 );
The following range query applies to the btree index.
Copy codeThe Code is as follows: SELECT * FROM t1 WHERE key_col> 1 AND key_col <10;
SELECT * FROM t1 WHERE key_col LIKE 'AB %' OR key_col BETWEEN 'bar' AND 'foo ';

How to use indexes in Mysql

The index is used to quickly find rows with a specific value in a column. If no index is used, MySQL must start with 1st records and read the entire table until related rows are found. The larger the table, the more time it takes. If the column to be queried in the table has an index, MySQL can quickly find the data file in the middle of a location, and there is no need to read all the data. If a table has 1000 rows, this is at least 100 times faster than sequential reading. Note that if you want to access most rows, sequential reading is much faster, because disk search is avoided.

Most MySQL indexes (primary key, UNIQUE, INDEX, and FULLTEXT) are stored in Tree B. Only the R-tree is used for spatial column indexes, and the MEMORY table also supports hash indexes.

For more information about the conditions in which the database uses indexes and when the database does not use indexes, see the optimization chapter.

Mysql index types

Indexes are divided into single-column indexes and composite indexes. A single-column index contains only one column. A table can have multiple single-column indexes, but this is not a combination index. A composite index contains multiple columns.
MySQL indexes include:
(1) Common Index
This is the most basic index with no restrictions. It can be created in the following ways:
◆ Create an index
Create index indexName ON mytable (username (length ));
For CHAR and VARCHAR types, the length can be smaller than the actual length of the field; For BLOB and TEXT types, the length must be specified, the same below.
◆ Modify Table Structure
ALTER mytable add index [indexName] ON (username (length ))
◆ When creating a table, specify
Create table mytable (id int not null, username VARCHAR (16) not null, INDEX [indexName] (username (length )));
Syntax for deleting an index:
Drop index [indexName] ON mytable;
(2) unique index
It is similar to the previous normal index. The difference is that the value of the index column must be unique, but null values are allowed. If it is a composite index, the combination of column values must be unique. It can be created in the following ways:
◆ Create an index
Create unique index indexName ON mytable (username (length ))
◆ Modify Table Structure
ALTER mytable add unique [indexName] ON (username (length ))
◆ When creating a table, specify
Create table mytable (id int not null, username VARCHAR (16) not null, UNIQUE [indexName] (username (length )));
(3) Primary Key Index
It is a special unique index and does not allow null values. Generally, when creating a table, you can create a primary key index at the same time:
Create table mytable (id int not null, username VARCHAR (16) not null, primary key (ID ));
Of course, you can also use the ALTER command. Remember: A table can only have one primary key.
(4) Composite Index
To visually compare Single-Column indexes and composite indexes, add multiple fields to the table:
Create table mytable (id int not null, username VARCHAR (16) not null, city VARCHAR (50) not null, age int not null );
To further extract MySQL efficiency, you must consider establishing a composite index. Create name, city, and age in an index:
Alter table mytable add index name_city_age (name (10), city, age );
The usernname length is 16 when the table is created, and 10 is used here. This is because the name length generally does not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.
If a single column index is created on usernname, city, and age respectively, the table has three single column indexes, and the query efficiency will be significantly different from that of the preceding composite indexes, far lower than our combined index. Although the remaining full text is...>

Mysql Index

Secondary index ??
In mysql, each table has a clustered index (clustered index). In addition, each non-clustered index in the table is a secondary index, also known as secondary index (secondary indexes ).

In InnoDB, each InnoDB table has a special index called a clustered index. If your table defines a primary key, the primary key index is a clustered index. If you do NOT define it as the primary key of your table, MySQL takes the first unique index (unique) and only contains non-NULL columns (not null) as the primary key, innoDB uses it as a clustered index. Without such a column, InnoDB generates such an ID value, which has six bytes and is hidden and used as a clustered index.

Clustering indexes are mainly used to facilitate storage .. Therefore, secondary indexes should be indexes of clustered indexes.
The following is the original statement on Mysql Manual, which may be incorrect.
Every InnoDB table has a special index called the clustered index where the data for the rows is stored. if you define a primary key on your table, the index of the primary key is the clustered index.

If you do not define a primary key for your table, MySQL picks the first UNIQUE index that has only not null columns as the primary key and InnoDB uses it as the clustered index. if there is no such index in the table, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. the rows are ordered by the ID that InnoDB assigns to the rows in such a table. the row ID is a 6-byte field that increases monotonically as new rows are inserted. thus, the rows ordered by the row ID are physically in insertion order.

Accessing a row through the clustered index is fast because the row data is on the same page where the index search leads. if a table is large, the clustered index architecture often saves a disk I/O operation when compared to storage organizations that store row data using a different page from the index record. (For example, MyISAM uses one file for data rows and another for index recor ...... remaining full text>