mysql手册03_索引

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. 最左前缀原则