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 |
+------+---------+------+