MySQL从入门到高级 --- 10.索引

第十章：

10.索引

• 索引通过某种算法，构建出一个数据模型，用来快速查找在某个列中有一特定值的行，若表中查询的列有一个索引，MySQL能快速到达一个位置去搜索数据文件，而不必查看所有数据，将会节省很多时间

10.1 分类

• Hash索引

• B+Tree索引

Hash：

B+Tree：

功能划分：

• 单列索引：一个索引包含单个列，但一个表中可有多个单列索引

• 普通索引：允许在定义索引的列中插入重复值和空值，没太多限制

10.2 创建索引

10.2.1 单列索引 - 普通索引

语法：

-- 法1 创建表时直接指定
create database mydb12;
use mydb12;
create table stu(
    sid int primary key,
    name varchar(20),
    gender varchar(20),
    age int,
    score double,
    index index_name(name) -- name列创建索引
);

-- 法2 直接创建
-- create index indexname on tablename(columnname);
create index index_gender on stu(gender);

-- 法3 修改表结构
-- alter table tablename add index index_name(columnname);
alter table stu add index index_age(age);

10.2.2 查看索引

10.2.3 删除索引

语法：

drop index 索引号 on 表名

或

alter table 表名 drop index 索引名

drop index index_gender on stu;
alter table stu drop index index_name;

10.2.4 单列索引 - 唯一索引

• 索引列的值必须唯一，但允许有空值

语法：

-- 法1 创建表时直接指定
create table stu2(
    sid int primary key,
    id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    score double,
    unique index_id(id) -- id列创建索引
);

-- 法2 直接创建
-- create unique index index_name on tablename(columnname);
create unique index index_gender on stu2(gender);

-- 法3 修改表结构
-- alter table tablename add unique index_name(columnname);
alter table stu2 add unique index_age(age);

10.2.5 单列索引 - 主键索引

• 一种特殊的唯一索引，在创建表时，会自动在主键列上建立一个索引

10.2.6 组合索引

• 也被称为复合索引，在建立索引时使用多个字段。使用复合最左原则

语法：

create index indexname on table_name(column(length, column2(length));

create index index_age_score on stu2(age,score);

操作：

select * from stu2 where score = 88; -- 1
select * from stu2 where age = 20 ; -- 2
select * from stu2 where age = 20 and score = 88; -- 3
select * from stu2 where score = 88 and age = 20; -- 4
-- 2、3、4因为条件里必须包含索引前面的字段才能进行匹配，可用到索引index_age_score
-- 3、4相比where条件顺序不一样，在mysql中它们等价

10.3 全文索引

10.3.1 概述

关键字：fulltext

作用：查找文本中的关键字

使用like + %可实现模糊匹配，在大量数据里，全文索引比它快很多倍，不是一个量级，但全文索引精度可能存在问题

变量：

• 最小搜索长度

• 最大搜索长度

搜索时，词语需在最小搜索长度和最大搜索长度的区间内

-- 查看这两个变量的默认值
show variables like '%ft%';

10.3.2 使用

关键字：match、against

语法：match(col1, col2, …) against(expr [search_modifier])

create table article(
    id int primary key auto_increment,
    title varchar(255),
    content varchar(999),
    writing date,
    fulltext(content) -- 创建全文索引
);

-- 添加全文索引
create fulltext index index_content on article(content);

-- 修改表结构添加全文索引
alter table article add fulltext index_content(content);
drop table article;
insert into article values(null,'zoo', 'yesterday one zoo animal hav dog and cat', '2004-01-26');
insert into article values(null,'food', 'you one food hav milk you you and bread', '2004-02-16');

insert into article values(null,"hair", "yesterday you one hair hav you short you",'2004-05-27');

select * from article where match(content) against('yo'); -- 无
select * from article where match(content) against('you'); -- 有 
select * from article where content like '%you%';

10.4 空间索引

• 空间索引是对空间数据类型的字段建立的索引

• 创建空间索引的列，必须将其声明NOT NULL

• 使用SPATIAL关键字进行扩展，使能够用于创建正规索引类型的语法创建空间索引

数据类型：

• GEOMETRY

• POINT

• LINESTRING

• POLYGON

10.4.1 操作

10.5 原理

索引本身很大，不可能全部存储在内存中，索引往往以索引文件的形式存储磁盘上。查找过程要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高很大，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

10.5.1 相关算法

Data Structure Visualization (usfca.edu)

Hash算法

• 优点：通过字段值计算的hash值，定位数据快

• 缺点：不能进行范围查找，散列表中的值是无序的，无法进行大小比较

二叉树

• 特征：分为左子树、右子树和根节点，左子树比根节点值小，右子树比根节点值大

• 缺点：有可能产生不平衡

平衡二叉树

特征：

• 左子树和右子树都是平衡二叉树

• 左子树比中间值小， 右子树比中间值大

• 左子树和右子树的深度之差的绝对值不超过1

缺点：

• 插入操作要旋转

• 支持范围查询，但回旋查询效率较低

• 树高度越高，查询效率会越慢

BTREE树

• 目前大多数数据库系统及文件系统都采用B-Tree或B+Tree作为索引结构，Btree结构可有效解决之前相关算法所遇到的问题

B-Trees树

B+Trees树

10.6 MyISAM引擎使用B+Tree

10.7 InnoDB引擎使用B+Tree

• 叶节点的data域存放数据，比MyISAM效率高一点，但比较占硬盘内存大小

10.8 索引特点

优点：

• 加快查询数据的速度

• 显著减少查询时分组和排序的时间

• 创建唯一索引，能保证数据库每一行数据的唯一

• 实现数据的参考完整性方面，可加速表与表之间的连接

缺点：

• 随着数据量的增加，消耗时间也会增加

• 需要占磁盘空间

• 对数据表中数据进行增删改时，索引也要动态维护，降低了维护的速度

10.9 使用原则

• 更新频繁的列不设索引

• 数据量小的表不使用索引

• 重复数据多的字段不设索引

• 有限考虑对where和order by所涉及的列上建立索引