Mysql-索引

Mysql-索引

• 索引原理
• 索引优缺点
• 索引分类
• 索引创建
• 索引失效

一、索引原理

• B树原理(实例：红黑树)

• B+树原理
  1、叶节点

  (1) 最多包含n-1个搜索码值，n个指针；搜索码值按顺序排放
  
  (2)叶节点包含搜索码值的数量不能少于celling(n-1/2);例如对于一个n = 4的B+树，叶子中最少包含2个搜索码值，最多有3个搜索码值

  2、非页节点

3、根节点
4、查询操作
5、更新操作
6、删除操作
- 聚集索引
- 非聚集索引

二、索引优缺点

• 优点
  提高查询效率，快速定位数据
• 缺点
  读数据时需要从磁盘读入索引到内存，然后进行索引查找，增加了磁盘的开销
  写数据的时候需要维持索引的平衡性，降低了表更新添加和删除的操作

三、索引分类和创建

1、何时创建索引

• 在where子句中出现的列

• 在join子句中出现的列

2、索引的分类和创建

• 普通索引

• 唯一索引
  1、特点：如果在一个列上面创建一个唯一索引，那么这个索引列的值必须唯一，可以为空
  2、创建唯一索引语法

  CREATE UNIQUE IDNEX indexName on tablename()

  3、主键索引：是一种特殊的唯一索引，值必须唯一，且不为空

  create table peoples(id_p int(11) auto_increment,
                   lastname varchar(50) default null,
                   firstname varchar(50) default null,
                   address varchar(100) default null,
                   city varchar(100) default null,
                   primary key(id_p))engine=Innodb charset=utf8;

  其中id_p是一个主键索引，不允许为空

• 组合索引
  1、一个索引包含多个列,创建一个组合三列的组合索引如下

  CREATE INDEX lastname_firstname_address on peoples(firstname,lastname,address);

  2、组合索引需要注意的就是最左前缀的原则，如上面创建的组合索引(firstname,lastname,address),相当于创建了一下三种组合索引

  firstname
  firstname,lastname
  firstname,lastname,address

• 全文索引

四、索引失效

• 以% 或者_通配符开头作为查询

• 在创建了索引的列上进行运算

案例

create database w3c;
use w3c;

create table orders(id_o int(11) auto_increment not null,
                    orderno varchar(8) default null,
                    id_p int(11) not null,
                    primary key(id_o))engine=Innodb charset=utf8;

create table peoples(id_p int(11) auto_increment not null,
                     lastname varchar(50) default null,
                     firstname varchar(50) default null,
                     address varchar(100) default null,
                     city varchar(100) default null,
                     primary key(id_p))engine=Innodb charset=utf8;

insert into peoples(lastname,firstname,address,city)  values('Baitao','Zou','HengDong','Hunan');
insert into peoples(lastname,firstname,address,city)  values('Yanwu','Yan','NanShan','ShenZhen');
insert into peoples(lastname,firstname,address,city)  values('Zhengnan','Cui','HaiErbin','HeiLongJiang');
insert into peoples(lastname,firstname,address,city)  values('Lei','Hou','XinYan','HeNan');
insert into peoples(lastname,firstname,address,city)  values('Jingyu','Yi','ZhuZhou','HuNan');


insert into orders(orderno,id_p) values('77895',3);
insert into orders(orderno,id_p) values('123456',2);
insert into orders(orderno,id_p) values('156756',3);
insert into orders(orderno,id_p) values('498789',4);
insert into orders(orderno,id_p) values('232314',1);
insert into orders(orderno,id_p) values('123489',10);

+------+----------+-----------+----------+--------------+
| id_p | lastname | firstname | address  | city         |
+------+----------+-----------+----------+--------------+
|    1 | Baitao   | Zou       | HengDong | Hunan        |
|    2 | Yanwu    | Yan       | NanShan  | ShenZhen     |
|    3 | Zhengnan | Cui       | HaiErbin | HeiLongJiang |
|    4 | Lei      | Hou       | XinYan   | HeNan        |
|    5 | Jingyu   | Yi        | ZhuZhou  | HuNan        |
+------+----------+-----------+----------+--------------+

+------+---------+------+
| id_o | orderno | id_p |
+------+---------+------+
|    1 | 77895   |    3 |
|    2 | 123456  |    2 |
|    3 | 156756  |    3 |
|    4 | 498789  |    4 |
|    5 | 232314  |    1 |
|    6 | 123489  |   10 |
+------+---------+------+