Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the meaning of MySQL SHOW INDEX grammar". In daily operation, I believe that many people have doubts about what MySQL SHOW INDEX grammar means. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful for you to answer the doubts about "what is the meaning of MySQL SHOW INDEX grammar?" Next, please follow the editor to study!

Such as: show index from `order`

1. Table table name

2. Non_unique is 0 if the index cannot include duplicate values, or 1 if it can. That is, what is usually called the only index.

3. Key_name index name. If the name is the same, it means the same index, rather than duplicating it. For example, the second and third pieces of data in the figure above are all named index_fID_lastTime, which is actually a federated index.

4. The column sequence number in the Seq_in_index index, starting with 1. The two or three pieces of data in the figure above, Seq_in_index is 1 and 2, which indicates the order in the federated index, so we can infer the order before and after the index in the federated index.

5. Column name of the Column_name index.

6. How Collation columns are stored in the index, which roughly means character order. In MySQLSHOW INDEX syntax, there is a value of'A'(ascending order) or NULL (no classification). The default type is utf8_general_ci, which is case-insensitive. For example, the following two sql will have the same query result:

Select * from Table where content = 'Yes'

Select * from Table where content = 'yes'

This may not meet your requirements, you need to be case-sensitive, you can modify the field character set type, as follows: sql

Alter table bbs_posts modify column content varchar (5000) NOT NULL collate utf8_bin

After this modification, it will be OK.

7. Cardinality cardinality, an estimate of the number of unique values in the index. The cardinality is counted based on statistics stored as integers, so even for small tables, the value does not need to be accurate. The higher the cardinality, the greater the chance that MySQL will use the index when federating. We know that the fewer duplicate values of a field, the more suitable for indexing, so we usually judge whether the index is highly selective based on Cardinality. If this value is very small, we need to re-evaluate whether the field is suitable for indexing. Because there are different storage engines in MySQL database, and each storage engine has different implementation of B + tree index. So the Cardinality statistics are carried out in the storage engine layer, as for how it is updated in detail here will not do any more.

8. Sub_part pre-indexing means the number of characters indexed if the column is only partially indexed. NULL if the entire column is indexed. As shown in the figure above, except for the line 4 in index_content, everything else is NULL, indicating that index_content is a pre-index of length 4. For BLOB,TEXT, or very long columns of type VARCHAR, you must use a prefix index because MySQL does not allow you to index the full length of these columns, which makes the index large and slow. The trick of choosing length is to choose a prefix long enough to ensure high selectivity, but not too long to save space. The following is a general method for calculating the length of the leading index:

Select count (distinct left (content,3)) / count (*) from bbs_posts as sel3

Select count (distinct left (content,4)) / count (*) from bbs_posts as sel4

Select count (distinct left (content,5)) / count (*) from bbs_posts as sel5

Finally, it is calculated that the selected row whose cardinality of that length is close to the complete column is OK, and the complete column select count (distinct content) / count (*) from bbs_posts

9. Packed indicates how keywords are compressed. NULL if it is not compressed. Compression generally includes compression transport protocol, compression column solution and compression table solution.

10. Null if the column contains NULL, it contains YES. For example, the lastOperateTime in the figure above contains null. We know that indexed columns are not allowed to be Null, single-column indexes do not store null values, and composite indexes do not store all Null values. If the columns are allowed to be Null, you may get a result set that "does not meet expectations". I am here to better show you the deliberate construction of some data.

11. Index_type index types, Mysql currently has the following main index types: FULLTEXT,HASH,BTREE,RTREE.

1)。 FULLTEXT

That is, full-text indexing, which is currently supported only by the MyISAM engine. It can be used in CREATE TABLE, ALTER TABLE, CREATE INDEX, but currently only CHAR, VARCHAR, and TEXT columns can create full-text indexes. Full-text index is not born with MyISAM, it appears to solve the problem of low efficiency of fuzzy query for text, such as WHERE name LIKE "% word%".

2)。 HASH

Because HASH is unique (almost 100% unique) and in the form of similar key-value pairs, it is suitable for use as an index. HASH indexes can be located at once and do not need to be searched layer by layer like a tree index, so it is extremely efficient. However, this efficiency is conditional, that is, it is efficient only under the conditions of "=" and "in", but it is still not efficient for range queries, sorting, and combinatorial indexes.

3)。 BTREE BTREE index is a kind of index value according to a certain algorithm, stored in a tree-shaped data structure (binary tree). Each query starts from the entry root of the tree, traverses the node in turn, and gets leaf. This is the default and most commonly used index type in MySQL.

4)。 RTREE

RTREE is rarely used in MySQL and only supports the geometry data type. The only storage engines that support this type are MyISAM, BDb, InnoDb, NDb, and Archive. The advantage over BTREE,RTREE is scope lookup.

12. The meaning of Comment Index_comment comments.

At this point, the study of "what is the meaning of MySQL SHOW INDEX grammar" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.