Management of B + tree indexes in MySQL

You can create and delete indexes in two ways. One is alter table and the other is create/drop index. alter table index creation Syntax: alter table tbl_name | add {index | key} {index_name} {index_type} (index_col_name ,......) [index_option] ...... | drop {index | key} index_namealter table tbl_name drop primary key; the syntax of create/drop index is also very simple; Create [unique] index index_name [index_type] On tbl_name (index_col_name ...) drop index index_name on tbl_name; drop index inde X_name on tbl_name; the index can index the data of the entire column, or only index the data at the beginning of the column, such as the table t and column B we created earlier as varchar (8000 ), we only index the first 100 fields, such as; mysql> alter table t add key idx_ B (B (100); query OK, 0 rows affected (0.05 Sec) records: 0 duplicates: 0 Warnings: 0 mysql> currently, a common problem in the MySQL database is that, for all index addition or deletion operations, MySQL database creates a temporary table first, import the data to the temporary table, delete the original table, and rename the temporary table to the original table name ,. Therefore, it takes a long time to add or delete indexes for a large table. The InnoDB Storage engine supports a fast index creation method starting from the InnoDB plugin version. Of course, this method only applies to secondary indexes. You still need to create a new table for primary key creation and deletion, for secondary index creation, the InnoDB Storage engine adds an S lock to the table ,. During the creation process, the table does not need to be rebuilt, so the speed is extremely fast. However, because the S lock is applied during the creation process, the table can only be read during the creation process. It is easier to delete the secondary index, you only need to update the InnoDB internal view, mark the space of the secondary index as available, and delete the index definitions for the table in the MySQL internal view; to view the index information in a table, you can use the show index statement. For example, if we use table t and add a composite index, for example, mysql> alter table t add key idx_a_ B (a, c ); query OK, 0 rows affected (0.12 Sec) records: 0 duplicates: 0 Warnings: 0 mysql> show index from T \ G; * *************************** 1. row ************************** table: T non_unique: 0 key_name: Primary seq _ In_index: 1 column_name: A collation: a cardinality: 4 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: * *************************** 2. row ************************** table: T non_unique: 1 key_name: idx_c seq_in_index: 1 column_name: C collation: a cardinality: 4 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: * *************************** 3. R Ow ************************** table: T non_unique: 1 key_name: idx_ B seq_in_index: 1 column_name: B collation: a cardinality: 4 sub_part: 100 packed: NULL: Yes index_type: btree comment: index_comment: * *************************** 4. row ************************** table: T non_unique: 1 key_name: idx_a_ B seq_in_index: 1 column_name: A collation: a cardinality: 4 sub_part: NULL packed: NULL n Ull: index_type: btree comment: index_comment: ***************************** 5. row ************************** table: T non_unique: 1 key_name: idx_a_ B seq_in_index: 2 column_name: C collation: a cardinality: 4 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: 5 rows in SET (0.00 Sec) error: the NO query specifiedmysql> table has three indexes. One primary key index, the index on column A and column C, and the index on the first 100 bytes of Column B. The following are definitions of each column: table: Index In the table name; non_unique: Non-unique index, we can see that the primary key is 0, because it must be unique; key_name: Index name, we can use this name to drop the index; seq_in_index: the position of the column in the index. It is intuitive to see the combined index idx_a_c; column_name: the column of the index; Collation: How the column stores the index, it can be a or 'null '. B + tree index is always a, that is, sorting. If the Heap Storage engine is used and a hash index is created, null is displayed here, Because hash stores index data according to the hash bucket, instead of sorting data; cardinality: a critical value that represents the estimated number of unique values in an index. Cardinality/(the number of rows in the table) should be as close as possible to 1. If it is very small, you need to consider whether to re-create this index. Sub_part: whether it is a partial index of the column. If it is a number, the field is null if it is an index of the entire column. Packed: How to compress the keyword. If it is not compressed, it is null; NULL: whether the index column contains a null value. We can see that idx_ B is yes here, because we have defined Column B to run as null; index_type: The index type; the InnoDB Storage engine only supports B + tree indexes, so btree is displayed here; Comment: comment; the cardinality value is very critical. The optimizer will determine whether to use this index based on this value, however, this value is not actually updated. It is not updated every time the index is updated. Because of the high cost, this value is not accurate and is only a rough value; to update the cardinality information of an index, you can use the analyze table command, for example, mysql> analyze table t \ G; * *************************** 1. row *************************** table: test. T op: analyzemsg_type: statusmsg_text: ok1 row in SET (0.01 Sec) error: No query specifiedmysql> show index from T \ G; * *************************** 1. row ************************** table: T non_unique: 0 key_name: Primary seq_in_index: 1 column_name: A collation: a cardinality: 5 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: * *************************** 2. row ************************** table: T non_unique: 1 key_name: idx_c seq_in_index: 1 column_name: C collation: a cardinality: 5 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: * *************************** 3. row ************************** table: T non_unique: 1 key_name: idx_ B seq_in_index: 1 column_name: B collation: a cardinality: 2 sub_part: 100 packed: NULL: Yes index_type: btree comment: index_comment: * *************************** 4. row ************************** table: T non_unique: 1 key_name: idx_a_ B seq_in_index: 1 column_name: A collation: a cardinality: 5 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: * *************************** 5. row ************************** table: T non_unique: 1 key_name: idx_a_ B seq_in_index: 2 column_name: C collation: a cardinality: 5 sub_part: NULL packed: NULL: index_type: btree comment: index_comment: 5 rows in SET (0.00 Sec) error: No query specifiedmysql>