T N T

美食博主

0%

$4 索引相关操作

Posted on Edited on In

本节记录索引的增删查

查看索引

  1. SHOW INDEX FROM table 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    (root@localhost) [book]> show index from student\G              表内容太宽,使用键值对方式显示,G一定要大写
    *************************** 1. row ***************************
            Table: student
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: stu_id
        Collation: A
      Cardinality: 3
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
          Visible: YES
       Expression: NULL
    *************************** 2. row ***************************
            Table: student
       Non_unique: 1
         Key_name: index_stu
     Seq_in_index: 1
      Column_name: stu_name
        Collation: A
      Cardinality: 3
         Sub_part: 2
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
          Visible: YES
       Expression: NULL
    2 rows in set (0.00 sec)

  2. ANALYZE TABLE student 刷新表 

    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> analyze table student;
    +--------------+---------+----------+----------+
    | Table        | Op      | Msg_type | Msg_text |
    +--------------+---------+----------+----------+
    | book.student | analyze | status   | OK       |
    +--------------+---------+----------+----------+
    1 row in set (0.03 sec)

创建索引

  1. CREATE INDEX index ON table 

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    (root@localhost) [book]> create index index_stu
        -> on student(stu_name(2) asc);
    Query OK, 0 rows affected (0.37 sec)
    Records: 0  Duplicates: 0  Warnings: 0


    (root@localhost) [book]> create index index_book        联合索引
        -> on book(b_id,b_name);
    Query OK, 0 rows affected (0.04 sec)
    Records: 0  Duplicates: 0  Warnings: 0

  2. ALTER TABLE table ADD INDEX index 

    1
    2
    3
    4
    (root@localhost) [book]> alter table student_copy
        -> add index index_stucp(stu_name,major);
    Query OK, 0 rows affected (0.03 sec)
    Records: 0  Duplicates: 0  Warnings: 0

  3. CREATE TABLE table 创建表的同时创建索引 

    1
    2
    3
    4
    5
    6
    7
    (root@localhost) [book]> create table book_cp(
        -> bid int not null auto_increment,
        -> title varchar(50),
        -> author varchar(50),
        -> primary key(bid),
        -> index index_bookcp(title));
    Query OK, 0 rows affected (0.05 sec)

删除索引

  1. ALTER TABLE table DROP INDEX index 

    1
    2
    3
    4
    (root@localhost) [book]> alter table student_copy
        -> drop index index_stucp;
    Query OK, 0 rows affected (0.02 sec)
    Records: 0  Duplicates: 0  Warnings: 0

  2. DROP INDEX index ON table 

    1
    2
    3
    (root@localhost) [book]> drop index index_book on book;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0  Duplicates: 0  Warnings: 0