索引:提高数据库的性能,索引是物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行
正确的create index ,查询速度就可能提高成百上千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的IO。所以它的价值,在于提高一个海量数据的检索速度。
常见索引分为:
--构建一个8000000条记录的数据
--构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解
-- 产生随机字符串
delimiter $$
create function rand_string(n INT)
returns varchar(255)
begin
declare chars_str varchar(100) default
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i = i + 1;
end while;
return return_str;
end $$
delimiter ;
--产生随机数字
delimiter $$
create function rand_num()
returns int(5)
begin
declare i int default 0;
set i = floor(10+rand()*500);
return i;
end $$
delimiter ;
--创建存储过程,向雇员表添加海量数据
delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into EMP values ((start+i)
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());
until i = max_num
end repeat;
commit;
end $$
delimiter ;
-- 执行存储过程,添加8000000条记录
call insert_emp(100001, 8000000);
select * from EMP where empno=998877;
可以看到耗时4.93秒,这还是在本机一个人来操作,在实际项目中,如果放在公网中,假如同时有1000个人并发查询,那很可能就死机。
alter table EMP add index(empno);
select * from EMP where empno=123456;
MySQL 给用户提供存储服务,而存储的都是数据,数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备,相比于计算机其他电子元件,磁盘效率是比较低的,在加上IO本身的特征,可以知道,如何提高效率,是MySQL 的一个重要话题。
数据库文件,本质其实就是保存在磁盘的盘片当中,就是一个一个的文件,通过/var/lib/mysql这个目录可以看到创建的数据库文件;
所以,最基本的,找到一个文件的全部,本质就是在磁盘找到所有保存文件的扇区。
而我们能够定位任何一个扇区,那么便能找到所有扇区,因为查找方式是一样的。
我们现在已经能够在硬件层面定位,任何一个基本数据块了(扇区)。那么在系统软件上,就直接按照扇区(512字节,部分4096字节),进行IO交互吗?不是
因此尽管相邻的两次IO操作在同一时刻发出,但如果它们的请求的扇区地址相差很大的话也只能称为随机访问,而非连续访问。
磁盘是通过机械运动进行寻址的,随机访问不需要过多的定位,故效率比较高。
MySQL 作为一款应用软件,可以想象成一种特殊的文件系统。它有着更高的IO场景,所以,为了提高基本的IO效率, MySQL 进行IO的基本单位是16KB;
mysql> SHOW GLOBAL STATUS LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| Innodb_page_size | 16384 | -- 16*1024=16384
+------------------+-------+
1 row in set (0.01 sec)
也就是说,磁盘这个硬件设备的基本单位是512 字节,而MySQL InnoDB引擎使用16KB 进行IO交互。
即MySQL 和磁盘进行数据交互的基本单位是16KB。这个基本数据单元,在MySQL 这里叫做page(注意和系统的page区分)
建立测试表:
create table if not exists user (
id int primary key, --一定要添加主键,只有这样才会默认生成主键索引
age int not null,
name varchar(16) not null
);
mysql> show create table user \G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`id` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 --默认就是InnoDB存储引擎
1 row in set (0.00 sec)
插入多条记录:
mysql> insert into user (id, age, name) values(3, 18, '杨过');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(4, 16, '小龙女');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(2, 26, '黄蓉');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user (id, age, name) values(5, 36, '郭靖');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user (id, age, name) values(1, 56, '欧阳锋');
Query OK, 1 row affected (0.00 sec)
查看插入结果:
mysql> select * from user;
+----+-----+-----------+
| id | age | name |
+----+-----+-----------+
| 1 | 56 | 欧阳锋 |
| 2 | 26 | 黄蓉 |
| 3 | 18 | 杨过 |
| 4 | 16 | 小龙女 |
| 5 | 36 | 郭靖 |
+----+-----+-----------+
5 rows in set (0.00 sec)
MySQL 中要管理很多数据表文件,而要管理好这些文件,就需要先描述,再组织,我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的;
页目录
单页情况:
多页情况:
当MySQL 中有多个页来存储数据,使用链表结构管理这些page,在上面说过效率是很低的,对于多页结构,同样可以引入目录对页进行管理:
但是每次检索数据的时候,该从哪里开始呢?虽然顶层的目录页少了,但是还要遍历啊;
Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
查找的时候,自定向下找,只需要加载部分目录页到内存,即可完成算法的整个查找过程;
页目录里面,不存数据,16KB的空间全都可以保存目录的映射,可以管理很多的下级page,会使整棵树的结构变得矮胖,有效减少了IO的次数;
B树:
B+树:
B树节点,既有数据,又有Page指针,而B+,只有叶子节点有数据,其他目录页,只有键值和Page指针;
B+叶子节点,全部相连,而B没有;
选择B+树的原因:
MyISAM 存储引擎-主键索引;
MyISAM 引擎同样使用B+树作为索引结果,叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引, Col1 为主键。
其中, MyISAM 最大的特点是,将索引Page和数据Page分离,也就是叶子节点没有数据,只有对应数据的地址。
相较于InnoDB 索引, InnoDB 是将索引和数据放在一起的。
可以从Linux中MySQL的文件目录中看出:
两种引擎的数据库创建的文件数量不一致;
当然, MySQL 除了默认会建立主键索引外,我们用户也有可能建立按照其他列信息建立的索引,一般这
种索引可以叫做辅助(普通)索引。
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);
主键索引的特点:
create table user4(id int primary key, name varchar(30) 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);
这样会重命名索引属性的key_name属性,如果没有重命名,一般key_name和列名是一致的;
普通索引的特点:
当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。
创建数据库并插入数据,设置title和body的全文索引:
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 ...');
查询有没有database数据:
show keys from 表名;
show index from 表名;
desc 表名;
alter table 表名 drop primary key;
alter table 表名 drop index 索引名;
索引名就是show keys from 表名中的 Key_name 字段;
drop index 索引名 on 表名;
创建复合索引,同时用name和qq作为索引,但是只创建一颗B+树;
查看复合索引属性:
索引名都是myindex;
复合索引的好处是:
最左匹配原则: