Mysql索引特性(重要)

目录

1.没有索引，可能会有什么问题

(1)关于索引

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

(2)常见索引

(3)案例

drop database if exists `bit_index`;
create database if not exists `bit_index` default character set utf8;
use `bit_index`;
-- 构建一个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 ;
-- 雇员表
CREATE TABLE `EMP` (
  `empno` int(6) unsigned zerofill NOT NULL COMMENT '雇员编号',
  `ename` varchar(10) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(9) DEFAULT NULL COMMENT '雇员职位',
  `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT '雇员领导编号',
  `hiredate` datetime DEFAULT NULL COMMENT '雇佣时间',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '工资月薪',
  `comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',
  `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT '部门编号'
);
-- 执行存储过程，添加8000000条记录
call insert_emp(100001, 8000000);

①查询员工编号为998866的员工

②解决方法，创建索引，创建索引也是需要花费时间的

③换一个员工编号，测试看看查询时间

2.认识磁盘

(1) Mysql与存储

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。磁盘是计算机中的一个机械设备，相比于计算机其他电子元件，磁盘效率是比较低的，在加上IO本身的特征，可以知道，如何提交效率，是 MySQL 的一个重要话题。

(2)研究磁盘

①平面图

②磁盘中一个盘片

(3)扇区

①数据库文件，本质其实就是保存在磁盘的盘片当中。也就是上面的一个个小格子中，就是我们经常所说的扇区。当然，数据库文件很大，也很多，一定需要占据多个扇区。

②我们在使用Linux，所看到的大部分目录或者文件，其实就是保存在硬盘当中的。(当然，有一些内存文件系统，如proc ， sys 之类，我们不考虑)

③数据库文件，本质其实就是保存在磁盘的盘片当中，就是一个一个的文件

④最基本的，找到一个文件的全部，本质就是在磁盘找到所有保存文件的扇区。

而我们能够定位任何一个扇区，那么便能找到所有扇区，因为查找方式是一样的。

(4)定位扇区

(5)结论

(6)磁盘随机访问(Random Access)与连续访问(Sequential Access)

①所谓的IO效率低指的是哪方面效率低？

②你怎么保证，我未来要访问的数据和我当前你的page是同一个?

3. MySQL 与磁盘交互基本单位

(1)基本概念

MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率MySQL 索引， MySQL 进行IO的基本单位是 16KB (后面统一使用 InnoDB 存储引擎)就是说，磁盘这个硬件设备的基本单位是 512 字节，而 MySQL InnoDB引擎 使用 16KB 进行IO交互。即， MySQL和 磁盘进行数据交互的基本单位是 16KB 。这个基本数据单元，在 MySQL 这里叫 做 page （ 注意和系统的page区分 ）

(2)示意图

4. 建立共识

5. 索引的理解

(1)建立测试表

(2)插入多条记录

(3)查看结果

(4)为何IO交互以Page为单位

(5)理解单个Page

①Mysql在运行的时候，一定有大量的page需要被换入换出，一个时间点内，MySQL内部一定存在着大量的Page ! MySQL要不要把这个Page也管理起来呢? 先描述，在组织!!

②为什么数据库在插入数据时要对其进行排序呢？我们按正常顺序插入数据不是也挺好的吗？

(6)理解多个Page

(7)页目录

(8)单页情况

(9)多页情况

①page页相连

②提升查找page页的效率

1) 可在Page之间，也是需要 MySQL 遍历的，遍历意味着依旧需要进行大量的IO，将下一个Page加载到内存，进行线性检测。这样就显得我们之前的Page内部的目录，有点杯水车薪了。

2) 解决方案，用之前的思路，给Page也带上目录。

3) 存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。

③其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

(10)小结

不保存任何用户数据，只保存它所管理的所有的page的最小记录ID为什么?

6.数据库引擎和索引分类

(1)InnoDB 在建立索引结构来管理数据的时候，为何不使用其他数据结构

(2)B树 vs B+树

①B树

②B+树

③目前这两棵树，对我们最有意义的区别是：

④为何选择 B+

节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。

叶子节点相连，更便于进行范围查找

(3)聚簇索引 VS 非聚簇索引

①MyISAM 存储引擎 - 主键索引

② MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

③InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引

(4)其他索引

①下图就是基于 MyISAM 的 Col2 建立的索引，和主键索引没有差别

②InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col3 建立对应的辅助索引如下图

③小结

7.索引操作

(1)创建主键索引

//法1
// 在创建表的时候，直接在字段名后指定 primary key 
create table user1(id int primary key, name varchar(30));
//法2
// 在创建表的最后，指定某列或某几列为主键索引 
create table user2(id int, name varchar(30), primary key(id));
//法3
create table user3(id int, name varchar(30)); 
// 创建表以后再添加主键 
alter table user3 add primary key(id)

主键索引的特点：

(2)唯一索引的创建

//法1
// 在表定义时，在某列后直接指定unique唯一属性。 
create table user4(id int primary key, name varchar(30) unique);
//法2
// 创建表时，在表的后面指定某列或某几列为
unique create table user5(id int primary key, name varchar(30), unique(name));
//法3
create table user6(id int primary key, name varchar(30));
alter table user6 add unique(name);

唯一索引的特点：

(3)普通索引的创建

//法1
create table user8(
id int primary key, 
name varchar(20), email varchar(30), 
index(name)  //在表的定义最后，指定某列为索引
);
//法2
create table user9(
id int primary key, 
name varchar(20), 
email varchar(30)
); 
alter table user9 add index(name); //创建完表以后指定某列为普通索引
//法3
create table user10(
id int primary key, 
name varchar(20), email varchar(30)
); 
//创建一个索引名为 idx_name 的索引 
create index idx_name on user10(name);

普通索引的特点：

(4)全文索引的创建

①创建表并插入数据

②查询有没有database数据

③可以用explain工具看一下，是否使用到索引

④如何使用全文索引

⑤再次使用explain查看

(5)查询索引

(6)删除索引

①第一种方法-删除主键索引： alter table 表名 drop primary key;

②第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的Key_name 字段

③第三种方法： drop index 索引名 on 表名

(7)索引创建原则

(8)如果我的表没有设置主键呢? 查找会不会很慢?

（编辑：财气旺网 - 财气网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!