mysql索引

索引

https://www.bilibili.com/video/BV12b411K7Zu?p=334&spm_id_from=pageDriver P333 P334 索引相关

B tree：

1、数据 2、向下指针 3、指向数据指针

 

B+ Tree

1、数据 2、向下指针 3、数据指针在叶子节点

 

#查看索引
show index from table_name;
    mysql> show index from user;
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--
    | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | N
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+--
    | user  |          0 | PRIMARY  |            1 | Host        | A         |           3 |     NULL |   NULL |  
    | user  |          0 | PRIMARY  |            2 | User        | A         |           6 |     NULL |   NULL |  
    +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+-
#创建索引
create index idx_name on table_name(name1,name2,name3);
    mysql> create index idx_a on t(a);
    Query OK, 0 rows affected (0.32 sec)
    Records: 0  Duplicates: 0  Warnings: 0

alter table table_name add primary key(column_list) ;
    mysql> alter table t add primary key(a) ;
    Query OK, 3 rows affected (1.27 sec)
    Records: 3  Duplicates: 0  Warnings: 0