MySQL——索引
一.索引的意义
索引：提高数据库的性能，索引不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。
但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。
常见索引分为：
- 主键索引(primary key)
- 唯一索引(unique)
- 普通索引(index)
- 全文索引(fulltext)--解决中子文索引问题。
二.MySQL与磁盘
MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新 策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位 就是Page。
为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称 为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数。
MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述，在组织，可以简单理解成一个个独立文件是有一个或者多个Page构成的。
不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表。
因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，目的就是优化查询的效率。
在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。
如果单个页中的数据量过大，则可以引入目录来帮助索引数据。
如果页的数量过多，想要在这么多Page之间快速找到某一个Page并进而得到其数据，则需要再引入目录项，来帮助索引到每个页。
目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。
可是就算是引入了目录项，也还是会有多个，需要去索引，此时，继续再添加目录页：
这种结构，称为B+树，正是提高MySQL索引效率的内部数据结构。
Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减 少了IO次数。
三.索引操作
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.创建普通索引
普通索引的创建需要通过 "index"，同样有三种方式：
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));
create index idx_name on user10(name);-- 创建一个索引名为 idx_name 的索引
普通索引的特点：
- 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
- 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引
4.查询索引
查询索引有三种方法：
show keys from 表名;
show index from 表名;
desc 表名;//消息比较简略
其中第一种方法的结果如下：
5.删除索引
第一种方法-删除主键索引：
alter table 表名 drop primary key;
第二种方法-其他索引（包括唯一键）的删除：
alter table 表名 drop index 索引名；
索引名就是show keys from 表名中的 Key_name 字段
第三种方法方法： drop index 索引名 on 表名
drop index name on user8;
四.索引创建原则
比较频繁作为查询条件的字段应该创建索引。
唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
更新非常频繁的字段不适合作创建索引。
不会出现在where子句中的字段不该创建索引。
原文地址：https://blog.csdn.net/2303_78442132/article/details/144377304
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！