Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the concept and usage of mysql index cardinality. Share with you for your reference, the details are as follows:

Cardinality (index cardinality) is a very important concept of mysql index.

The index cardinality is the number of different values contained in the data column. For example, if a data column contains values 1, 2, 3, 4, 5, 1, its cardinality is 5. The index works best when the cardinality of the index is higher than the number of rows in the data table (that is, the column contains many different values, with few duplicates). If a data column contains many different ages, the index quickly distinguishes the data rows. If a data column is used to record gender (there are only "M" and "F" values), then the index is of little use. If the probability of occurrence of values is almost equal, then no matter which value you search for, you may get half of the data rows. In these cases, it is best not to use an index at all, because when the query optimizer finds that a value appears in a high percentage of the table's data rows, it generally ignores the index and performs a full table scan. The usual percentage limit is "30%"

Another concept is called index selectivity.

Index selectivity = index cardinality / total number of data, which can be viewed through the show index from table name.

The closer 1 is, the more likely it is to use the index, and it can also be understood that 1 means 100%.

The advantage of high index selectivity is that mysql can filter more rows when looking for matches, and unique indexes have the best selectivity, with a value of 1.

Here is a table of my own, the amount of data is not high, just to test the index selectivity

Select * from articlesid title name 15 Title 0 Big Bear 16 Title 1 Big Bear 17 Title 2 Big Bear 18 Title 3 Big Bear 19 Title 4 Big Bear 20 Title 5 Big Bear 21 Title 6 Big Bear 22 Title 7 Big Bear 23 Title 8 two Bear 24 Title 9

Id is the default integer self-increment primary key

Now add the author column index and view all the indexes of the articles table

ALTER TABLE `articles` ADD INDEX (`author`) SHOW INDEX FROM articlestable non_unique key_name seq_in_index column_name collation cardinality sub_part packed null index_typearticles 0 PRIMARY 1 id A 10 NULL NULL BTREEarticles 1 author 1 author A 2 NULL NULL BTREE

Say the meaning of each field.

The table table name non_unique is 0 if the index cannot include duplicate words. 1, if possible. Key_name index name the column sequence number in the seq_in_index index, starting with 1, how the column_name column name collation column is stored in the index. In MySQLSHOW INDEX syntax, there is a value'A'(ascending order) or NULL (unclassified) cardinality index cardinality sub_part. If the column is only partially indexed, it is the number of characters indexed. NULL if the entire column is indexed. Packed indicates how keywords are compressed. NULL if it is not compressed. Null contains YES if the column contains NULL. If not, the column contains NO. Index storage methods used by index_type (BTREE, FULLTEXT, HASH, RTREE)

As you can see, the table already has two indexes.

Id index does not contain repeating words as primary key column name id ascending order index cardinality is 10 no partial index is not compressed without null storage mode is btree

Author index contains repeating words index name author column name author ascending sort index cardinality 2 no partial index no compression no null storage mode for btree

According to the algorithm of index selectivity, the selectivity of id index is 10 author 10 = 1 and the selectivity of index 2 picks 10 seconds 0.2. Let's test it and use explain to take a look at the sentence analysis.

Explain select * from articles where id = 15id select_type table partitions type possible_keys key key_len ref rows filtered extra1 SIMPLE articles NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL

Here, let's talk about the explain command again.

Explain shows how mysql uses indexes to process select statements and join tables. It can help select better indexes and write more optimized query statements. Tell me the meaning of each field.

1.id SELECT identifier. This is the SELECT query serial number. This is not important. The query sequence number is the order in which the sql statement is executed.

2.select_type select Typ

When 2.1.SIMPLE makes a simple select query that does not require Union operations or subqueries, the select_type that responds to the query statement is simple. No matter how complex the query statement is, there must be only one unit query with simple select_type in the execution plan.

2.2.PRIMARY in a select query execution plan that requires a Union operation or a subquery, the outermost select_type is primary. Like simple, there is only one unit select query for which select_type is primary

2.3.union in a unit select query made up of union operations, except for the first, the select_type of all subsequent unit select queries is union. The first unit of union, the select_type of select is not union, but DERIVED. It is a temporary table that stores query results after Union

2.4.DEPENDENT UNION dependent, like UNION select_type, dependent union appears in a collection query formed by union or union all. The dependent here indicates that the unit query formed by the combination of union or union all is externally affected

2.5.union result union result is a datasheet containing union results

3.table table name

4.type connection type, which has multiple parameters, is also the focus of this article from the best type to the worst type.

4. 1 const, the table has at most one matching row, and const is used to compare primary key or unique indexes. Because only one row of data is matched, so very quickly, it can also be understood as an optimized index, constant lookup.

4.2 eq_ref 's interpretation of eq_ref, the mysql manual says: "for each row combination from the previous table, read a row from that table. This is probably the best join type except for the const type."

4.3 ref for each row combination from the previous table, all rows with matching index values will be read from this table. Use ref if the join uses only the leftmost prefix of the key, or if the key is not UNIQUE or PRIMARY KEY (in other words, if the join cannot select a single row based on the keyword). This join type is good if the key used matches only a small number of rows.

Ref_or_null the join type is like ref, but MySQL is added to specifically search for rows that contain null values. The optimization of this join type is often used in solving subqueries.

4. 5 index_merge this join type indicates that the index merge optimization method is used. In this case, the key column contains a list of indexes used, and key_len contains the longest key elements of the index used

4.6 unique_subquery

4.7 index_subquery

4.8 range searches within a given range, using an index to check rows

Index the join type is the same as ALL, except that only the index tree is scanned. This is usually faster than ALL because index files are usually smaller than data files. (that is, although both all and Index read the whole table, index is read from the index, and all is read from the hard disk)

4.10 ALL performs a complete table scan for each row combination from the previous table. If the table is the first table that is not marked with const, this is usually not good and is usually bad in its case. It is usually possible to add more indexes instead of using ALL so that rows can be retrieved based on constant values or column values in the previous table

It is not important that 5.possible_keys hints which index to use will find rows in the table.

6.keys indicates the index used by the MYSQL query

Index length used by 7.key_len MYSQL

8.ref shows which column or constant to use to select rows from the table with key

9.rows shows the number of rows in which MYSQL executes the query. The higher the number, the worse, indicating that the index is not well used.

10.Extra this column contains the details of the MySQL query resolution

You can see that the id query uses the id index, simple query, constant query with the optimal query type.

Then let's try another query, this time using the author index.

Explain select * from articles where author = "Big Bear" 1 SIMPLE articles NULL ALL author NULL NULL NULL 10 80.00 Using where

You can clearly see the full table scan with the worst query type using a simple query using the author index

Do not rush to explain, or this sentence, let's change it to two bears

Explain select * from articles where author = "two bears" 1 SIMPLE articles NULL ref author author 1022 const 2 100.00 NULL

You can see that the query type this time is ref

In other words, because the number of rows of data written by Big Bear is more than 30 percent of the total data, mysql believes that full table scanning is faster than using indexes.

This is the meaning of the concept of index cardinality and index selectivity, so when building an index, we should pay attention to building the index on a column with a high index base.

Readers who are interested in MySQL-related content can check out this site's special topics: "Summary of MySQL Index Operation skills", "Summary of MySQL Common functions", "Collection of MySQL Log Operation skills", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

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.