mysql索引

基础知识

• 定义
  索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
  索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。

• 目的
  索引的功能就是加速查找

• 优点
  1.通过建立唯一索引或者主键索引，保证数据库中每一行数据的唯一性
  2.大大提高检索的数据的效率，以及减少表的检索行数
  3.在实现数据的参考完整性方面可以加速表与表之间的连接。
  4.在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

• 缺点
  1.在创建索引和维护索引会耗费时间，随着数据量的增加而增加
  2.索引文件会占用物理空间
  3.当对表的数据进行增删改的时候，索引也要动态的维护，这样就会降低数据的维护速度

• 基础原则
  当查询多的时候，可以适当的使用多个索引，当增删改多的时候，要慎重考虑多个索引。

索引分类

存储方式区分

根据存储方式的不同，MySQL 中常用的索引在物理上分为 B-树索引和 HASH 索引两类，两种不同类型的索引各有其不同的适用范围。

• 1. B-树索引
     B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。
     B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：
     叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
     分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
     根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。
     基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：
1.查询必须从索引的最左边的列开始。
2.查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
3.存储引擎不能使用索引中范围条件右边的列。

• 2. 哈希索引
     哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
     哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B-树索引和 HASH 索引，且将 HASH 当成默认索引。HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：
     1.MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B-树索引来说，建立哈希索引会耗费更多的时间。
     2.不能使用 HASH 索引排序。
     3.HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
     4.HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

逻辑区分

• 普通索引 INDEX 或 KEY
  普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
• 唯一索引 UNIQUE
  唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。
• 主键索引 PRIMARY KEY
  主键索引就是专门为主键字段创建的索引，主键索引是一种特殊的唯一索引，不允许值重复或者值为空。不能使用 CREATE INDEX 语句创建主键索引。
• 空间索引 SPATIAL
  了解就好，几乎不用
• 全文索引 FULLTEXT
  用于搜索很长一篇文章的时候，效果最好。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

实际使用区分

• 单列索引
  单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。
  单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
• 多列索引
  组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
  提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

实战

• 创建索引
  创建普通索引

# 建表的时候就创建索引
create table mytable (
id int(20),
name varchar(20),
card_no varchar(20),
amount varchar(20),
key myid (id) );
# 建表结束后，再创建索引
create index  新增的索引名  on 表名(列名);

create index  mycardno  on mytable(card_no);
# 修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

alter table mytable add COLUMN phone varchar(20);
alter table mytable add index myphone(phone);

创建唯一索引

# 直接创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

create UNIQUE index myid on mytable (id);
# 修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))

alter table mytable add COLUMN id_no varchar(20);
alter table mytable add unique index myid_no(id_no);
# 创建表的时候直接指定
CREATE TABLE mytable2(  
 ID INT NOT NULL,   
 username VARCHAR(16) NOT NULL,  
 UNIQUE id (id)  
 ); 

创建联合索引

CREATE INDEX index_name ON table(c1,c2);
create index myphone on mytable (phone,id_no);
该索引创建好了以后，查询条件中必须有 phone 字段才能使用索引。

• 删除索引

# [indexName] 指索引名，不是列名，可能和列名称一致，也可能不一致,一次只能删除一个索引
DROP INDEX [indexName] ON mytable; 
drop index  mycardno  on mytable;

• 使用ALTER 命令添加和删除索引

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE mytable ADD  PRIMARY KEY(amount);

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE mytable ADD  UNIQUE  KEY(amount);

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
ALTER TABLE mytable ADD INDEX (amount);

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引

# 删除
ALTER TABLE testalter_tbl DROP INDEX c;
ALTER TABLE mytable DROP INDEX amount;

• 显示索引信息

SHOW INDEX FROM table_name;
SHOW INDEX FROM mytable;

• 查看建表语句

show create table tablename;
show create table mytable;
查询结果：
CREATE TABLE `mytable` (
  `id` int(20) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `card_no` varchar(20) DEFAULT NULL,
  `amount` varchar(20) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `id_no` varchar(20) DEFAULT NULL,
  UNIQUE KEY `myid` (`id`),
  UNIQUE KEY `myid_no` (`id_no`),
  KEY `myphone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

- 返回当前数据库名
select database();
结果展示：
test

• 查看表结构

desc mytable;

• explain 查看select查询的情况，可用于查看sql的执行时间及是否命中索引

explain select id from loan_application_history where id>0;

image.png

查看截图中的最后一列，使用了where条件，使用了索引

MySQL索引的设计原则

1. 选择唯一性索引
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
5. 尽量使用数据量少的索引
6. 数据量小的表最好不要使用索引
7. 尽量使用前缀来索引
8. 删除不再使用或者很少使用的索引