MYSQL的索引及底层原理详解

MYSQL中的索引及底层原理详解

索引介绍

1.有关概念：
定义：创建在表上，对数据库表中的一列或者多列的值进行排列得到的结果（相当于表中数据的向导）。
工作方式：一个表创建的索引以文件的形式存储下来，要用该表的数据就先把索引从磁盘上加载到内存（磁盘IO），从内存中先读索引，再根据索引找对应的数据。

作用：提高查询效率（优势）
劣势：

• 索引也要存储，过多的索引会占用空间
• 索引并非越多越好，过多的索引会导致cpu使用率降低（过多的索引会导致cpu处理索引的时间过多，处理数据的时间相对少）
• 由于数据改动会影响索引的改动，过多的索引会引起磁盘IO频繁而造成cpu负载过重

2.索引的分类

• 普通索引：没有任何限制条件，可以给任意类型的字段添加普通索引
• 唯一性索引：使用unique修饰的字段，值是不能重复的，主键索引就隶属于唯一性索引
• 主键索引：使用primary key修饰的字段MYSQL会自动创建为其创建索引，InnoDB存储引擎中不设置主键也会自动找一个字段创建主键索引，一个表只能有一个主键索引
• 单列索引：在一个字段上创建的索引
• 多列索引：在表的多个字段上创建的索引
• 全文索引：使用fulltext参数设置全文索引，只支持char、varchar、text类型的字段上，常用于数据量比较大的 字符串类型中，可以提高查询速度，只有myisam存储引擎支持
• 空间索引：空间型数据的索引，使用spatial修饰

索引创建和删除的SQL语句：

创建：
1.创建表的时候创建索引

CREATE TABLE table_name(
属性 数据类型,
[unique|fulltext|spatial|primary] [index|key] [索引名] (属性(属性长度) [asc|desc])
);
注意：primary后面必须是key而不能是index
索引名一般以 idx_属性名 这样的形式命名
指定属性长度指建立前缀索引（例如：name属性值’abcde‘，长度3，即以'abc'为索引）
 [asc|desc]:指定数据按索引升序还是降序排列
 例：创建一个student表，表中有id、name、sex三个属性，id为索引，索引名为idx_id
create table student(
id int,
name varchar(20),
sex varchar(10),
index idx_id (id)
);

创建后用show create table 表名; 来看创建表的详细语句:
-----------------------------------------------+
student | CREATE TABLE student (
id int(11) DEFAULT NULL,
name varchar(20) DEFAULT NULL,
sex varchar(10) DEFAULT NULL,
KEY idx_sex (sex)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±--------±---------------------------------
第四行可以看出id是一个普通索引，索引名为idx_id

2.在已经创建的表上添加索引

2.1 ：create [unique|fulltext|spatial|pr