本章主要讲解MYSQL数据库中的索引这一重要知识点
提高数据库的性能,索引是物美价廉的东西了:不用加内存,不用改程序,不用调sql,只要执行正确的create index ,查询速度就可能提高成百上千倍,但是查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的IO,所以索引的价值,在于提高一个海量数据的检索速度
说明:可以看到耗时5秒多,这还是在本机一个人来操作,在实际项目中,如果放在公网中,假如同时有1000个人并发查询,那很可能就死机
通过磁头(Heads)、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号,即可在磁盘上定位所要访问的扇区,这种磁盘数据定位方式叫做 CHS 。不过实际系统软件使用的并不是 CHS (但是硬件是),而是 LBA ,一种线性地址,可以想象成虚拟地址与物理地址,系统将 LBA 地址最后会转化成为 CHS ,交给磁盘去进行数据读取,使得上下层数据交互关系解耦
针对上面的单页Page,我们也能引入目录
MySQL 中每一页的大小只有 16KB ,单个Page大小固定,所以随着数据量不断增大, 16KB 不可能存下所有的数据,那么必定会有多个页来存储数据
B树?最值得比较的是 InnoDB 为何不用B树作为底层索引?
B树节点,既有数据,又有Page指针,而B+,只有叶子节点有数据,其他目录页,只有键值和Page指针B+叶子节点,全部相连,而B没有
MyISAM 引擎使用B+树作为索引结果,叶节点的data域存放的是数据记录的地址
-- 终端A
mysql> create database myisam_test; -- 创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> use myisam_test;
Database changed
mysql> create table mtest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=MyISAM; -- 使用engine=MyISAM
Query OK, 0 rows affected (0.01 sec)
-- 终端B
[root@VM-0-3-centos mysql]# ls myisam_test/ -al --mysql数据目录下
total 28
drwxr-x--- 2 mysql mysql 4096 Jun 13 13:33 .
drwxr-x--x 13 mysql mysql 4096 Jun 13 13:32 ..
-rw-r----- 1 mysql mysql 61 Jun 13 13:32 db.opt
-rw-r----- 1 mysql mysql 8586 Jun 13 13:33 mtest.frm -- 表结构数据
-rw-r----- 1 mysql mysql 0 Jun 13 13:33 mtest.MYD -- 该表对应的数据,当前没有数据,所以是0
-rw-r----- 1 mysql mysql 1024 Jun 13 13:33 mtest.MYI -- 该表对应的主键索引数据
注:MyISAM 这种用户数据与索引数据分离的索引方案,叫做非聚簇索引
-- 终端A
mysql> create database innodb_test; -- 创建数据库
Query OK, 1 row affected (0.00 sec)
mysql> use innodb_test;
Database changed
mysql> create table itest(
-> id int primary key,
-> name varchar(11) not null
-> )engine=InnoDB; -- 使用engine=InnoDB
Query OK, 0 rows affected (0.02 sec)
-- 终端B
[root@VM-0-3-centos mysql]# ls innodb_test/ -al
total 120
drwxr-x--- 2 mysql mysql 4096 Jun 13 13:39 .
drwxr-x--x 14 mysql mysql 4096 Jun 13 13:38 ..
-rw-r----- 1 mysql mysql 61 Jun 13 13:38 db.opt
-rw-r----- 1 mysql mysql 8586 Jun 13 13:39 itest.frm -- 表结构数据
-rw-r----- 1 mysql mysql 98304 Jun 13 13:39 itest.ibd -- 该表对应的主键索引和用户数据,虽然现在一行数据没有,但是该表并不为0,因为有主键索引数据
注:InnoDB 这种用户数据与索引数据在一起索引方案,叫做聚簇索引
数据大部分是储存在磁盘这个外设当中,而磁盘是计算机中的一个机械设备,相比于计算机其他电子元件,磁盘效率是比较低的
在硬件中通过磁头、柱面(等价于磁道)、扇区对应的编号,即可在磁盘上定位所要访问的扇区,而实际中先通过一层软件层将物理地址进行虚拟化,通过地址转化再交给磁盘去进行数据读取,便于上下层关系解耦
InnoDB 主键索引和普通索引:
MyISAM 主键索引和普通索引:
其他数据结构为何不能作为索引结构,尤其是B+和B:
聚簇索引 VS 非聚簇索引:
-- 在创建表的时候,直接在字段名后指定 primary key
create table user1(id int primary key, name varchar(30));
-- 在创建表的最后,指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));
create table user3(id int, name varchar(30));
-- 创建表以后再添加主键
alter table user3 add primary key(id);
-- 在表定义时,在某列后直接指定unique唯一属性。
create table user4(id int primary key, name varchar(30) unique);
-- 创建表时,在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);
create table user8(id int primary key,
name varchar(20),
email varchar(30),
index(name) -- 在表的定义最后,指定某列为索引
);
create table user9(id int primary key, name varchar(20), email varchar(30));
alter table user9 add index(name); -- 创建完表以后指定某列为普通索引
create table user10(id int primary key, name varchar(20), email varchar(30));
-- 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (title,body)
)engine=MyISAM;
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');
-- 索引名就是 show keys from 表名中的 Key_name 字段
mysql> alter table user10 drop index idx_name;