Mysql - > index maintenance [20180504]

Learn index maintenance (adding and deleting) of tables in Mysql database
 
Benefits of indexing:
Improve query efficiency
Limited specific data (if unique)
Lack of index:
If there are too many indexes, the rate of Insert and Update will be affected, because data insertion and Update will also maintain indexes.
Consume more storage space
Index can be divided into single column index and combined index by column. A single column contains only one column in the table, and a composite index can contain multiple columns in the current table.
 
Create a normal index
        CREATE INDEX <index_name> ON <tab_name>(<col_name>(length));
If the column type is CHAR, VARCHAR may not specify a value for length.
If the column type is BLOB, TEXT needs to specify the value of length
 
Index on table creation
        CREATE TABLE <tab_name>(
            <col_name> TYPE,....
            [INDEX_TYPE] <index_name>(<col_name>(length))
            engine=InnoDB default charset=utf8;
 
mysql> create table index_tab03(
    ->     id int,
    ->    col01 varchar(20),
    ->     index index_01(col01)
    ->      )engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)
 
mysql> desc index_tab03;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| col01 | varchar(20) | YES  | MUL | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
 
mysql> show index from index_tab03\G
*************************** 1. row ***************************
       Table: index_tab03
  Non_unique: 1
    Key_name: index_01
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: BTREE
     Comment:
1 row in set (0.00 sec)
 m

 

    
ALTER statement maintenance index
 
Alter table < tab? Name > add primary key (< column? List >);? Add a primary key, unique and non empty
Alter table < tab > name > add unique < index > name > (< column > list >); ා add a unique index, which can be NULL in the column and can have multiple times
Alter table < tab > name > add index < index > name > (< column > list >); add a common index
Alter table < tab ﹣ name > Add fulltext < index ﹣ name > (< column ﹣ list >); add a full-text index. Note that only the MyISAM engine supports
 
mysql> alter table index_tab01 add primary key(id,col01);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add index indx_01(col01);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add fulltext indx_02(col01,col02(300));
ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes
 
mysql> alter table index_tab01 engine=MyISAM;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> alter table index_tab01 add fulltext index_02(col01,col02(30));
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from index_tab01\G
*************************** 1. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
   Collation: A
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: PRIMARY
Seq_in_index: 2
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 3. row ***************************
       Table: index_tab01
  Non_unique: 0
    Key_name: index_un
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: 0
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 4. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: indx_01
Seq_in_index: 1
Column_name: col01
   Collation: A
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 5. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: index_02
Seq_in_index: 1
Column_name: col01
   Collation: NULL
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: FULLTEXT
     Comment:
*************************** 6. row ***************************
       Table: index_tab01
  Non_unique: 1
    Key_name: index_02
Seq_in_index: 2
Column_name: col02
   Collation: NULL
Cardinality: NULL
    Sub_part: NULL
      Packed: NULL
        Null: YES
  Index_type: FULLTEXT
     Comment:
6 rows in set (0.00 sec)
 

 

Index delete
 
        DROP INDEX <index_name> ON <tab_name>;
        ALTER TABLE <tab_name> DROP PRIMARY KEY;
        ALTER TABLE <tab_name> DROP INDEX <index_name>;
 
mysql> alter table index_tab01 drop primary key;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
 
mysql> alter table index_tab01 drop index index_02;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> drop index index_01 on index_tab01;

 

 
View index structure
        SHOW INDEX FROM <tab_name>\G

Tags: MySQL Database

Posted on Sat, 21 Mar 2020 12:34:04 -0400 by kellerkind