MySQL index type

I. MySQL index type

MySQL currently only supports four types of indexes: Full-text, B-tree, hash, and R-tree.

B-tree indexes should be the most extensive indexes in MySQL, except for archive, which is supported by almost all storage engines.

1. Full-text index

Full-text only supports MyISAM in MySQL, and only char, varchar, and text data types are supported for full-text fields.

Full-text is mainly used to replace like "% *** %" with low efficiency.

2. B-tree indexes

The form of B-tree in MyISAM is slightly different from that in InnoDB

In InnoDB, there are two forms: the first is the primary key form. The leaf node stores data, not only the index key data, data of other fields is also stored. The second is secondary index. Its leaf node is similar to a common B-tree, but it also stores information pointing to the primary key.

In MyISAM, the primary key is not much different from other ones. However, unlike InnoDB, it is stored in MyISAM, and the leaf node does not store the primary key information, but points to the corresponding data row information in the data file.

3. Hash Index

All I know is that memory and NDB cluster support this index.

Due to its structure, the hash index is directly put in place at each query, not as a little forward as B-tree. Therefore, the efficiency of hash indexes is higher than that of B-tree, but hash has the following Disadvantages:

(1) because it stores hash values, only the <=> and in operations are supported.

(2) The hash index cannot be sorted by operating the index. This is because the hash value is calculated during storage, but the calculated hash value is not necessarily the same as the stored hash value, so it cannot be sorted.

(3) The index cannot be used for part of the combination.

(4) full table scan cannot be avoided, but because non-unique hash indexes are supported in the memory table, different index keys may have the same hash value.

(5) When there are a large number of identical hash values, the efficiency of the hash index will be reduced.

4. R-tree Index

R-tree is rarely used in MySQL and only supports geometry data types. The storage engines supporting this type are only MyISAM, bdb, InnoDB, NDB, and archive.

Compared with B-tree, R-tree has the advantage of range search.

Ii. Notes about SQL statements in MySQL

1. The length of all keys in MyISAM is only 1000 bytes, and InnoDB is 767.

2. Blob and text fields only support prefix indexing.

3. Use! = And <> not equal to, MySQL does not use an index.

4. mySQL cannot use indexes when Fields use functions. MySQL cannot use indexes when the types of conditional fields are inconsistent during join operations; when using a non-first index in a composite index, no index is used.

5. When using like, indexes cannot be used starting with %, that is, "% ***". When using or, indexes are required for both the fields before and after or.

Sometimes MySQL query optimizer considers that using an index is not the optimal plan, so no index is used. You can use force index in SQL statements. Of course, sometimes it is not faster to use than to use it. Therefore, you need to ignore the ignore index method.

Disable query cache SQL _no_cache

Select SQL _no_cache * From table_name;

In this way, some rarely used statements are not stored in the cache, and will not be searched in the cache; corresponding to the mandatory cache SQL _cache

Select SQL _cache * From table_name;

In addition, if query_cache_type = 2 is set in my. CNF, the cache is used only after SQL _cache is used;

In addition, hight_priority in MySQL allows MySQL to perform this statement preferentially.

Select high_priority * From table_name;

It corresponds to low_priority;

In MySQL, insert delayed is also inserted in a delayed manner.

Insert delayed into table_name ....;

# After submission, MySQL returns OK, but does not insert it immediately. Instead, MySQL inserts it when it is free. If the server crashes while waiting, all data is lost and the auto-increment ID is not returned for insertion.

Iii. Skills

1. Force join order: straight_join

Select table1.field1, table2.field2 from Table1 straight_join Table2 where...

According to the preceding SQL statement, straight_join forces MySQL to connect tables in the order of Table1 and table2. If you think it is more efficient to connect in the order recommended by MySQL, you can use straight_join to determine the connection sequence.

2. Force use of temporary table: SQL _buffer_result

Select SQL _buffer_result * From Table1 where...

When there is a large amount of data in the query result set, you can use SQL _buffer_result to forcibly place the result set in a temporary table, in this way, you can quickly release the MySQL table lock (so that other SQL statements can query these records) and provide a large record set for the client for a long time.

3. Use the temporary tables SQL _big_result and SQL _small_result for grouping.

Select SQL _buffer_result field1, count (*) from Table1 group by field1;

It is generally used for grouping or distinct keywords. This option notifies MySQL that if necessary, the query results will be placed in the temporary table, or even sorted in the temporary table. SQL _small_result is rarely used compared to SQL _big_result.

Reprinted: http://www.cnblogs.com/wdpp/archive/2011/07/07/2386768.html