做亚马逊运营要看哪些网站,无锡建设建设局网站,网站备案回访电话号码,网站在线制作软件MySQL – 索引 文章目录 MySQL -- 索引一、索引简介1.简介2.索引效率的案例 二、认识磁盘1.磁盘2.结论3.磁盘随机访问(Random Access)与连续访问(Sequential Access) 三、MySQL 与磁盘交互基本单位1.基本单位2.MySQL中的数据管理 五、索引的理解1.索引案例2.单页mysql page3.管…MySQL – 索引 文章目录 MySQL -- 索引一、索引简介1.简介2.索引效率的案例 二、认识磁盘1.磁盘2.结论3.磁盘随机访问(Random Access)与连续访问(Sequential Access) 三、MySQL 与磁盘交互基本单位1.基本单位2.MySQL中的数据管理 五、索引的理解1.索引案例2.单页mysql page3.管理多个mysql page4.B树和B树5.聚簇索引 VS 非聚簇索引 六、索引操作1.创建主键索引2.唯一索引的创建3.普通索引的创建4.全文索引的创建5.查询索引6.删除索引7.索引创建原则8.复合索引 一、索引简介
1.简介
索引提高数据库的性能索引是物美价廉的东西了。不用加内存不用改程序不用调sql只要执行 正确的create index 查询速度就可能提高成百上千倍。但是天下没有免费的午餐查询速度的提高是以插入、更新、删除的速度为代价的这些写操作增加了大量的IO。所以它的价值在于提高一个海量数据的检索速度。
常见索引分为
主键索引(primary key)唯一索引(unique)普通索引(index)全文索引(fulltext)–解决中子文索引问题
2.索引效率的案例
创建海量数据表
--构建一个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(1rand()*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(10rand()*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 ((starti)
,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);查询员工编号为998877的员工
select * from EMP where empno998877;可以看到耗时4.93秒这还是在本机一个人来操作在实际项目中如果放在公网中假如同时有1000个人并发查询那很可能就死机。
解决方法创建索引
alter table EMP add index(empno);换一个员工编号测试看看查询时间
select * from EMP where empno123456;二、认识磁盘
MySQL 给用户提供存储服务而存储的都是数据数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备相比于计算机其他电子元件磁盘效率是比较低的在加上IO本身的特征可以知道如何提高效率是MySQL 的一个重要话题。
1.磁盘 扇区 数据库文件本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中就是我们经常所说的扇区。当然数据库文件很大也很多一定需要占据多个扇区。
数据库文件本质其实就是保存在磁盘的盘片当中就是一个一个的文件通过/var/lib/mysql这个目录可以看到创建的数据库文件
所以最基本的找到一个文件的全部本质就是在磁盘找到所有保存文件的扇区。 而我们能够定位任何一个扇区那么便能找到所有扇区因为查找方式是一样的。
定位扇区 柱面(磁道): 多盘磁盘每盘都是双面大小完全相等。那么同半径的磁道整体上便构成了一个柱面每个盘面都有一个磁头那么磁头和盘面的对应关系便是1对1的所以我们只需要知道磁头Heads、柱面(Cylinder)(等价于磁道)、扇区(Sector)对应的编号。即可在磁盘上定位所要访问的扇区。这种磁盘数据定位方式叫做CHS 。不过实际系统软件使用的并不是CHS 但是硬件是而是LBA 一种线性地址可以想象成虚拟地址与物理地址。系统将LBA 地址最后会转化成为CHS 交给磁盘去进行数据读取。
2.结论
我们现在已经能够在硬件层面定位任何一个基本数据块了(扇区)。那么在系统软件上就直接按照扇区(512字节部分4096字节)进行IO交互吗不是
如果操作系统直接使用硬件提供的数据大小进行交互那么系统的IO代码就和硬件强相关换言之如果硬件发生变化系统必须跟着变化从目前来看单次IO 512字节还是太小了IO单位小意味着读取同样的数据内容需要进行多次磁盘访问会带来效率的降低之前学习文件系统就是在磁盘的基本结构下建立的文件系统读取基本单位就不是扇区而是数据块。
3.磁盘随机访问(Random Access)与连续访问(Sequential Access)
随机访问本次IO所给出的扇区地址和上次IO给出扇区地址不连续这样的话磁头在两次IO操作之间需要作比较大的移动动作才能重新开始读/写数据。连续访问如果当次IO给出的扇区地址与上次IO结束的扇区地址是连续的那磁头就能很快的开始这次IO操作这样的多个IO操作称为连续访问。
因此尽管相邻的两次IO操作在同一时刻发出但如果它们的请求的扇区地址相差很大的话也只能称为随机访问而非连续访问。 磁盘是通过机械运动进行寻址的随机访问不需要过多的定位故效率比较高。
三、MySQL 与磁盘交互基本单位
1.基本单位
MySQL 作为一款应用软件可以想象成一种特殊的文件系统。它有着更高的IO场景所以为了提高基本的IO效率 MySQL 进行IO的基本单位是16KB
mysql SHOW GLOBAL STATUS LIKE innodb_page_size;
-------------------------
| Variable_name | Value |
-------------------------
| Innodb_page_size | 16384 | -- 16*102416384
-------------------------
1 row in set (0.01 sec)也就是说磁盘这个硬件设备的基本单位是512 字节而MySQL InnoDB引擎使用16KB 进行IO交互。 即MySQL 和磁盘进行数据交互的基本单位是16KB。这个基本数据单元在MySQL 这里叫做page注意和系统的page区分
实际上mysqld服务端是运行在内存中的一个进程其对数据的所有增删查改CURD都是在内存中进行的后期mysqld会进行持久化mysql处于应用层通过系统调用接口向操作系统发送文件操作申请由操作系统读取磁盘中的数据 文件必须提前被打开也就是由SO读取到内存中对文件内容做任何操作都不是在磁盘设备上操作的 任何磁盘数据在进程中要进行操作本质都必须在内存中进行如果需要访问的数据此时不在内存中MySQL内部有自己的内存管理会进行换入、换出操作MySQL在启动的时候一定会预先申请一批内存空间
2.MySQL中的数据管理
mysql page单位为16KBLinux kernel page单位为4KB磁盘扇区单位为512B他们之间的数据读取关系如下图所示 MySQL 中的数据文件是以page为单位保存在磁盘当中的。MySQL 的CURD 操作都需要通过计算找到对应的插入位置或者找到对应要修改或者查询的数据。而只要涉及计算就需要CPU参与而为了便于CPU参与一定要能够先将数据移动到内存当中。所以在特定时间内数据一定是磁盘中有内存中也有。后续操作完内存数据之后以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互也就是IO了。而此时IO的基本单位就是Page。为了更好的进行上面的操作MySQL 服务器在内存中运行的时候在服务器内部就申请了被称为Buffer Pool的大内存空间来进行各种缓存其实就是很大的内存空间来和磁盘数据进行IO交互。IO效率低下的最主要矛盾不是IO单次数据量的大小而是IO的次数因此为了更高的效率一定要尽可能的减少系统和磁盘IO的次数
五、索引的理解
1.索引案例
建立测试表
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)
) ENGINEInnoDB DEFAULT CHARSETutf8 --默认就是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)可以看出查询结果是按照主键排好序的
2.单页mysql page
MySQL 中要管理很多数据表文件而要管理好这些文件就需要先描述再组织我们目前可以简单理解成一个个独立文件是有一个或者多个Page构成的
单个page的结构 一个page的单位是16KB使用prev 和next 构成双向链表 因为有主键的问题MySQL 会默认按照主键给我们的数据进行排序从上面的Page内数据记录可以看出数据是有序且彼此关联的。 排序的目的 插入数据时排序的目的就是优化查询的效率。页内部存放数据的模块实质上也是一个链表的结构链表的特点也就是增删快查询修改慢所以优化查询的效率是必须的。 正是因为有序在查找的时候从头到后都是有效查找没有任何一个查找是浪费的。
3.管理多个mysql page
上面页模式中只有一个功能就是在查询某条数据的时候直接将一整页的数据加载到内存中以减少硬盘IO次数从而提高性能。但是我们也可以看到现在的页模式内部实际上是采用了链表的结构前一条数据指向后一条数据本质上还是通过数据的逐条比较来取出特定的数据。如果有1千万条数据一定需要多个Page来保存1千万条数据多个Page彼此使用双链表链接起来而且每个Page内部的数据也是基于链表的。那么查找特定一条记录也一定是线性查找效率很低。
页目录
看书的时候前面会有页目录来指向每一个章节的起始页码本质上书中的目录是多花了纸张的但是却提高了效率 所以目录是一种“空间换时间的做法”
单页情况
针对上面的单页Page我们能否也可以引入目录 当前在一个Page内部我们引入了目录。比如我们要查找id4记录之前必须线性遍历4次才能拿到结果。现在直接通过目录2[3]直接进行定位新的起始位置提高了效率。 因此MySQL 会通过键值自动排序的原因就是可以很方便引入目录
多页情况 当MySQL 中有多个页来存储数据使用链表结构管理这些page在上面说过效率是很低的对于多页结构同样可以引入目录对页进行管理
使用一个目录项来指向某一页而这个目录项存放的就是将要指向的页中存放的最小数据的键值。和页内目录不同的地方在于这种目录管理的级别是页而页内目录管理的级别是行。其中每个目录项的构成是键值指针。 存在一个目录页来管理页目录目录页中的数据存放的就是指向的那一页中最小的数据。有数据就可通过比较找到该访问那个Page进而通过指针找到下一个Page。其实目录页的本质也是页普通页中存的数据是用户数据而目录页中存的数据是普通页的地址。
但是每次检索数据的时候该从哪里开始呢虽然顶层的目录页少了但是还要遍历啊
可以再增加目录页对这些目录页进行管理 这种数据结构就是B树选择B树的原因 节点不存储data这样一个节点就可以存储更多的key。可以使得树更矮所以IO操作次数更少。叶子节点相连更便于进行范围查找 具有主键的表一表就是一颗B树没有主键的表目前我们认为是所有的数据是线性组织的。(但是如果表中没有主键mysql 会自动形成隐藏主键)B中所有的叶子节点路上节点不需要全部加载到内存可以按需进行load mysql page
Page分为目录页和数据页。目录页只放各个下级Page的最小键值。 查找的时候自定向下找只需要加载部分目录页到内存即可完成算法的整个查找过程; 页目录里面不存数据16KB的空间全都可以保存目录的映射可以管理很多的下级page会使整棵树的结构变得矮胖有效减少了IO的次数
4.B树和B树 B树 B树
B树节点既有数据又有Page指针而B只有叶子节点有数据其他目录页只有键值和Page指针 B叶子节点全部相连而B没有
选择B树的原因
节点不存储data这样一个节点就可以存储更多的key可以使得树更矮所以IO操作次数更少叶子节点相连更便于进行范围查找 5.聚簇索引 VS 非聚簇索引
MyISAM 存储引擎-主键索引 MyISAM 引擎同样使用B树作为索引结果叶节点的data域存放的是数据记录的地址。下图为MyISAM表的主索引 Col1 为主键。 其中 MyISAM 最大的特点是将索引Page和数据Page分离也就是叶子节点没有数据只有对应数据的地址。 相较于InnoDB 索引 InnoDB 是将索引和数据放在一起的。 可以从Linux中MySQL的文件目录中看出 两种引擎的数据库创建的文件数量不一致
MyISAM 这种用户数据与索引数据分离的索引方案叫做非聚簇索引InnoDB 这种用户数据与索引数据在一起索引方案叫做聚簇索引
当然 MySQL 除了默认会建立主键索引外我们用户也有可能建立按照其他列信息建立的索引一般这 种索引可以叫做辅助普通索引。
对于MyISAM建立辅助普通索引和主键索引没有差别无非就是主键不能重复而非主键可重复。 下图就是基于MyISAM 的Col2 建立的索引和主键索引没有差别 InnoDB 除了主键索引用户也会建立辅助普通索引我们以上表中的Col3 建立对应的辅助索引如下图 所以通过辅助普通索引找到目标记录需要两遍索引首先检索辅助索引获得主键然后用主键到主索引中检索获得记录。这种过程就叫做回表查询 为何InnoDB 针对这种辅助普通索引的场景不给叶子节点也附上数据呢原因就是太浪费空间了。
六、索引操作
1.创建主键索引
第一种方式 在创建表的时候直接在字段名后指定 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);主键索引的特点
一个表中最多有一个主键索引当然可以使用复合主键主键索引的效率高主键不可重复创建主键索引的列它的值不能为null且不能重复主键索引的列基本上是int
2.唯一索引的创建
第一种方式 在表定义时在某列后直接指定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);唯一索引的特点
一个表中可以有多个唯一索引查询效率高如果在某一列建立唯一索引必须保证这列不能有重复数据如果一个唯一索引上指定not null等价于主键索引
3.普通索引的创建
第一种方式 在表的定义最后指定某列为索引
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和列名是一致的
普通索引的特点
一个表中可以有多个普通索引普通索引在实际开发中用的比较多如果某列需要创建索引但是该列有重复的值那么我们就应该使用普通索引
4.全文索引的创建
当对文章字段或有大量文字的字段进行检索时会使用到全文索引。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) -- 指定全文索引
)engineMyISAM;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数据
如果使用如下查询方式虽然查询出数据但是没有使用到全文索引 可以用explain工具看一下是否使用到索引 使用全文索引 通过explain来分析这个sql语句
5.查询索引
第一种方法
show keys from 表名;第二种方法:
show index from 表名;第三种方法信息比较简略
desc 表名;6.删除索引
第一种方法 – 删除主键索引
alter table 表名 drop primary key;第二种方法 – 其他索引的删除
alter table 表名 drop index 索引名;索引名就是show keys from 表名中的 Key_name 字段;
第三种方法
drop index 索引名 on 表名;7.索引创建原则
比较频繁作为查询条件的字段应该创建索引;唯一性太差的字段不适合单独创建索引即使频繁作为查询条件;更新非常频繁的字段不适合作创建索引;不会出现在where子句中的字段不该创建索引;
8.复合索引
创建复合索引同时用name和qq作为索引但是只创建一颗B树 查看复合索引属性 索引名都是myindex
复合索引的好处是
查询条件是nameselect想要取出的数据是qq查到name后就直接返回qq就可以不用像普通索引一样再去主键的B树中再次查找qq这就是索引覆盖
最左匹配原则
复合索引查询的时候一般是按照从左向右匹配的
