mysql手册03_索引

Love The Way You Lie 2022-11-27 08:59 102阅读 0赞

**mysql手册03\_索引**

--------------------

**索引**是帮助MySQL进行高效率数据**查询**的**数据结构**

**索引的优势和劣势：**

优势：提高数据检索和排序的效率
    劣势：索引占用空间，更新表时，MySQL不仅要更新数据，还要更新索引信息。

BTREE结构：
    以5叉BTree树为例，每层节点超出4个时，中间节点将分裂为父节点。
    插入数据：C N G A H E K Q M F W L T Z D P R X Y S
    插入前4个字母：
    ACGN
    插入H，G向上分裂:
       G
    AC   HN
    插入E K Q，不需分裂:
    	 G
    ACE		HKNQ
    插入M，M向上分裂:
    	 G		 M
    ACE		HK		NQ
    插入F W L T，不需分裂:
    	 G		   M
    ACEF	 HKL	  NQTW
    插入Z，T向上分裂：
    	 G		  M		   T
    ACEF	HKL		  NQ		WZ
    插入D，D向上分裂：
       D    G      M    T
    AC   EF    HKL   NQ   WZ
    插入P R X Y，不需分裂：
       D    G      M        T
    AC   EF    HKL    NPQR     WXYZ
    插入S,Q向上分裂，M向上分裂：
    			    M
       D     G             Q      T
    AC   EF     HKL    NP     RS     WXYZ
    
    BTree相比二叉树，层级结构小，查询效率高！

**准备工作：**

创建数据库demo01
    create database demo01 default charset=utf8mb4;
    use demo01;
    
    创建city表和country表并插入数据
    create table city(
    city_id int(11) not null auto_increment,
    city_name varchar(50) not null,
    country_id int(11) not null,
    primary key(city_id)
    ) engine=innodb default charset=utf8;
    
    create table country(
    country_id int(11) not null auto_increment,
    country_name varchar(100) not null,
    primary key(country_id)
    ) engine=innodb default charset=utf8;
    
    insert into city values(1,'西安',1);
    insert into city values(2,'NewYork',2);
    insert into city values(3,'北京',1);
    insert into city values(4,'上海',1);
    
    insert into country values(1,'China');
    insert into country values(2,'America');
    insert into country values(3,'Japan');
    insert into country values(4,'UK');

**索引的基本语法：**

创建普通索引：
    create index idx_city_name on city(city_name);
    
    查看索引（数据表拥有默认的主键索引）：
    show index from city\G
    
    *************************** 1. row ***************************
            Table: city
       Non_unique: 0
         Key_name: PRIMARY
     Seq_in_index: 1
      Column_name: city_id
        Collation: A
      Cardinality: 4
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
          Visible: YES
       Expression: NULL
    *************************** 2. row ***************************
            Table: city
       Non_unique: 1
         Key_name: idx_city_name
     Seq_in_index: 1
      Column_name: city_name
        Collation: A
      Cardinality: 4
         Sub_part: NULL
           Packed: NULL
             Null:
       Index_type: BTREE
          Comment:
    Index_comment:
          Visible: YES
       Expression: NULL
    
    删除索引：
    drop index idx_city_name on city;
    创建普通索引：
    alter table city add index idx_city_name(city_name);
    创建唯一索引：
    alter table city add unique idx_city_name(city_name);
    创建全文索引：
    alter table city add fulltext idx_city_name(city_name);

**索引的设计原则：**

1. 对查询频次高，数据量大的表建立索引
    2. 针对查询条件中的字段建立索引
    3. 尽量使用唯一索引，区分度高，索引效率就高
    4. 索引不是越多越好
    5. 尽量使用短字段索引
    6. 最左前缀原则