df的multiindex怎么设置索引名称 index索引怎么用

转载

mob64ca140e4022 2024-03-19 22:04:34

文章标签 数据库 mysql 服务器聚簇索引主键 文章分类 数据仓库大数据

数据库系统原理与应用教程（035）—— MySQL 的索引（一）：索引（INDEX）概述

索引是对数据表中一列或多列的值进行排序的一种结构。MySQL 索引的建立对于 MySQL 的高效运行非常重要，索引可以大大提高 MySQL 的检索速度。合理使用索引，可以大大提升 SQL 查询的性能，随着业务数据量的不断增长，优化系统的响应速度，很大程度上就集中在对索引的优化上。

索引包含了对表中所有记录的引用指针，索引好比是一本书前面的目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以从目录中查找，定位到 xx 内容所在页面，如果没有设置目录（索引），则只能逐字逐页阅读文本查找。

索引用于快速找出在某个列中有一特定值的行。当执行查询操作时，如果不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据。

MySQL 索引的存储类型有两种：BTREE 和 HASH，也就是用树或者 Hash 值来存储索引字段。

一、索引的优缺点

既然索引可以大大提高 MySQL 的检索速度，为什么不对表中的每一个列创建一个索引呢？其实创建索引虽然可以提高检索的速度，但创建过多的、不必要的索引还会影响数据增删改的效率。

1、索引的优点

（1）索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

（2）索引可以帮助服务器避免排序和创建临时表。

（3）索引可以将随机 IO 变成顺序 IO。

（4）索引对于 InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性。

（5）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（6）可以加速表和表之间的连接。

（7）在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间。

2、索引的缺点

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大。

（3）对表中的数据进行增、删、改的时候，索引也要动态的维护，降低了数据的维护速度。

（4）对于非常小的表，大部分情况下简单的全表扫描更高效。

二、创建索引的原则

创建索引的时候，应仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

1、应该创建索引的列

（1）在经常需要搜索的列上创建索引可以加快搜索的速度。

（2）在作为主键的列上创建聚簇索引可以保证该列的唯一性和组织表中数据的排列顺序。

（3）在经常用在连接的列上，一般是表中的外键创建索引，可以提高连接的速度。

（4）在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引。

（4）在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

（5）在经常使用在 WHERE 子句中的列上面创建索引，可以加快条件的判断速度。

2、不应该创建索引的列

（1）对于那些在查询中很少使用的列不应该创建索引。对于很少使用的列创建索引，反而会降低系统的维护速度和增大了空间需求。

（2）对于那些重复值过多的列也不应该增加索引。这些列的取值很少，例如学生的性别列，创建索引并不能明显加快检索速度。

（3）对于 text、image 和 bit 数据类型的列不应该创建索引。

（4）当某一列修改性能要求远远高于检索性能时，不应该创建索引。

三、和索引有关的文件

索引是需要占用物理空间的，在不同的存储引擎中，索引存在的文件也不同。分别使用 MyISAM 和 InnoDB 存储引擎建立两张表。

/*
create table innodb_t1(
    id int primary key,
    name char(20)
);

create table myisam_t2(
    id int primary key,
    name char(20)
) engine = myisam;
*/

mysql> use mydb;
Database changed
mysql> create table innodb_t1(
    ->     id int primary key,
    ->     name char(20)
    -> );
Query OK, 0 rows affected (0.13 sec)

-- 创建索引
mysql> alter table innodb_t1 add index idx_name(name);
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table myisam_t2(
    ->     id int primary key,
    ->     name char(20)
    -> ) engine = myisam;
Query OK, 0 rows affected (0.06 sec)

-- 创建索引
mysql> alter table myisam_t2 add index idx_name(name);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看数据表对应的文件：

[root@mysql data]# cd /usr/local/mysql/data
[root@mysql data]# ll
总用量 253988
-rw-r----- 1 mysql mysql       56 7月   1 09:10 auto.cnf
-rw-r----- 1 mysql mysql      397 7月  15 13:56 ib_buffer_pool
-rw-r----- 1 mysql mysql 79691776 7月  19 13:43 ibdata1
-rw-r----- 1 mysql mysql 50331648 7月  19 13:43 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 7月  19 13:43 ib_logfile1
-rw-r----- 1 mysql mysql 79691776 7月  18 18:04 ibtmp1
drwxr-x--- 2 mysql mysql       62 7月  19 13:43 mydb
drwxr-x--- 2 mysql mysql     4096 7月   1 09:10 mysql
drwxr-x--- 2 mysql mysql     8192 7月   1 09:10 performance_schema
drwxr-x--- 2 mysql mysql     8192 7月   1 09:10 sys

[root@mysql data]# cd mydb

[root@mysql mydb]# ll
总用量 144
-rw-r----- 1 mysql mysql     61 7月  19 13:41 db.opt
-rw-r----- 1 mysql mysql   8586 7月  19 13:45 innodb_t1.frm
-rw-r----- 1 mysql mysql 114688 7月  19 13:45 innodb_t1.ibd
-rw-r----- 1 mysql mysql   8586 7月  19 13:45 myisam_t2.frm
-rw-r----- 1 mysql mysql      0 7月  19 13:45 myisam_t2.MYD
-rw-r----- 1 mysql mysql   1024 7月  19 13:45 myisam_t2.MYI

# 表 myisam_t2 的存储引擎为MyISAM，数据表对应的数据文件如下：
# （1）*.frm：保存与表相关的元数据信息，包括表结构的定义信息等。
# （2）*.MYD：MyISAM DATA，用于存储 MyISAM 表的数据信息。
# （3）*.MYI：MyISAM INDEX，用于存储 MyISAM 表的索引相关信息。

# 表 innodb_t1 的存储引擎为 InnoDB，数据表对应的数据文件如下：
# （1）*.frm：保存与表相关的元数据信息，包括表结构的定义信息等。
# （2）*.ibd：InnoDB DATA，存放表数据和索引的文件。

四、索引的分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

1、逻辑分类

（1）按功能划分索引可以分为四类：主键索引、唯一索引、普通索引和全文索引。

主键索引：主键索引默认创建为聚簇索引，主键索引会改变表中记录的物理顺序。一张表只能创建一个主键索引，主键索引所包含的列不允许重复、不允许为 NULL。

/*
create table t11(
    id int primary key,
    name char(20)
);

insert into t11 values(10,'Jack');
insert into t11 values(7,'Tom');
*/
mysql> create table t11(
    ->     id int primary key,
    ->     name char(20)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> insert into t11 values(10,'Jack');
Query OK, 1 row affected (0.07 sec)

mysql> insert into t11 values(7,'Tom');
Query OK, 1 row affected (0.08 sec)

mysql> select * from t11;
+----+------+
| id | name |
+----+------+
|  7 | Tom  |
| 10 | Jack |
+----+------+
2 rows in set (0.02 sec)

mysql> insert into t11 values(8,'Black');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+----+-------+
| id | name  |
+----+-------+
|  7 | Tom   |
|  8 | Black |
| 10 | Jack  |
+----+-------+
3 rows in set (0.00 sec)

-- 主键不允许取重复值
mysql> insert into t11 values(8,'John');
ERROR 1062 (23000): Duplicate entry '8' for key 'PRIMARY'

-- 主键不允许为空
mysql> insert into t11 values(null,'Rose');
ERROR 1048 (23000): Column 'id' cannot be null

唯一索引：唯一索引包含的数据列不允许取重复值，但允许为 NULL 值。一张表可以创建多个唯一索引，索引列的值必须唯一，如果是组合索引，则唯一索引包含的所有列的组合必须取值唯一。

/*
create table t12(
    id int primary key,
    name char(20),
    phone char(20),
    unique index idx_phone(phone)
);
*/

mysql> create table t12(
    ->     id int primary key,
    ->     name char(20),
    ->     phone char(20),
    ->     unique index idx_phone(phone)
    -> );
Query OK, 0 rows affected (0.05 sec)

-- 可以插入空值
mysql> insert into t12 values(1001, '张涛', null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t12;
+------+--------+-------+
| id   | name   | phone |
+------+--------+-------+
| 1001 | 张涛   | NULL  |
+------+--------+-------+
1 row in set (0.00 sec)


mysql> insert into t12 values(1002, '刘刚', '13637321555');
Query OK, 1 row affected (0.01 sec)

-- 不允许插入重复值
mysql> insert into t12 values(1003, '张静', '13637321555');
ERROR 1062 (23000): Duplicate entry '13637321555' for key 'idx_phone'

mysql> select * from t12;
+------+--------+-------------+
| id   | name   | phone       |
+------+--------+-------------+
| 1001 | 张涛   | NULL        |
| 1002 | 刘刚   | 13637321555 |
+------+--------+-------------+
2 rows in set (0.00 sec)

普通索引：一张表可以创建多个普通索引，普通索引允许数据重复，索引所包含的列允许取 NULL 值。

/*
create table t14(
    id int primary key,
    name char(20),
    gender char(1),
    phone char(20),
    salary int,
    index idx_name(name),
    unique idx_phone(phone),
    index idx_salary(salary)
);
*/
mysql> create table t14(
    ->     id int primary key,
    ->     name char(20),
    ->     gender char(1),
    ->     phone char(20),
    ->     salary int,
    ->     index idx_name(name),
    ->     unique idx_phone(phone),
    ->     index idx_salary(salary)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> insert into t14 values(1001, '张涛', '男', '13637321555', 4500);
Query OK, 1 row affected (0.03 sec)

mysql> insert into t14 values(1002, '刘涛', '女', '13637321566', 4500);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t14 values(1003, '张静', '女', '13637321577', 4500);
Query OK, 1 row affected (0.02 sec)

-- 该表创建了两个普通索引，并且索引的取值可以重复
mysql> select * from t14;
+------+--------+--------+-------------+--------+
| id   | name   | gender | phone       | salary |
+------+--------+--------+-------------+--------+
| 1001 | 张涛   | 男     | 13637321555 |   4500 |
| 1002 | 刘涛   | 女     | 13637321566 |   4500 |
| 1003 | 张静   | 女     | 13637321577 |   4500 |
+------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)

全文索引：查找文本中的关键词，主要用于全文检索。

（2）按索引包含的列数可以分为两类：单例索引和组合索引。

单例索引：一个索引只包含一个列，一个表可以有多个单例索引。

/*
create table t15(
    id int primary key,
    name char(20),
    salary int,
    phone char(20),
    email varchar(50),
    index idx_name(name),
    index idx_salary(salary)
);
*/
-- 索引 idx_name 和 idx_salary 都是单列索引
mysql> create table t15(
    ->     id int primary key,
    ->     name char(20),
    ->     salary int,
    ->     phone char(20),
    ->     email varchar(50),
    ->     index idx_name(name),
    ->     index idx_salary(salary)
    -> );
Query OK, 0 rows affected (0.07 sec)

组合索引：一个组合索引包含两个或两个以上的列。查询时候遵循组合索引的【最左前缀】原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

mysql> alter table t15 add index idx_name_salary(name, salary);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t15\G
*************************** 1. row ***************************
       Table: t15
Create Table: CREATE TABLE `t15` (
  `id` int(11) NOT NULL,
  `name` char(20) DEFAULT NULL,
  `salary` int(11) DEFAULT NULL,
  `phone` char(20) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`),
  KEY `idx_salary` (`salary`),
  KEY `idx_name_salary` (`name`,`salary`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

2、物理分类

按索引的存储结构划分，可以把索引分为聚簇索引和非聚簇索引（有时也称辅助索引或二级索引）。

（1）聚簇索引

聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式，每张表最多只能拥有一个聚簇索引。这种存储方式是依靠 B+ 树来实现的，根据表的主键构造一棵 B+ 树，并且 B+ 树的叶子节点存放表的行记录。聚簇索引可以理解为将数据与索引存放到一起，找到索引也就找到了数据。

聚簇索引的优点：使用聚簇索引数据访问的速度更快。因为聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快。聚簇索引对于主键的排序查找和范围查找速度非常快。

聚簇索引的缺点：数据插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键。更新主键的代价很高，因为将会导致被更新的行移动。对于 InnoDB 表，我们一般定义主键为不可更新。

/*
create table t16(
    id int primary key auto_increment,
    name char(20),
    salary int
);
*/
-- id 列为主键，主键默认为聚簇索引
mysql> create table t16(
    ->     id int primary key auto_increment,
    ->     name char(20),
    ->     salary int
    -> );
Query OK, 0 rows affected (0.05 sec)

（2）非聚簇索引

聚簇索引之外的索引称之为非聚簇索引，又称为辅助索引。非聚簇索引数据和索引是分开存放的，索引叶子节点存储的不是行数据记录，而是主键值。查找数据时首先通过非聚簇索引（辅助索引）找到主键值，然后到主键索引树中通过主键值找到数据行。

非聚簇索引访问数据需要两次索引查找，第一次找到主键值（非聚簇索引），第二次根据主键值找到行数据（聚簇索引）。

/*
create table t17(
    id int primary key,
    name char(20),
    salary int,
    email varchar(50),
    index idx_name(name),
    index idx_salary(salary)
);
*/

-- 索引 idx_name 和 idx_salary 是非聚簇索引
mysql> create table t17(
    ->     id int primary key,
    ->     name char(20),
    ->     salary int,
    ->     email varchar(50),
    ->     index idx_name(name),
    ->     index idx_salary(salary)
    -> );
Query OK, 0 rows affected (0.06 sec)

（3）聚簇索引和非聚簇索引的区别

聚簇索引的叶子节点存放的是数据行（主键值也是行内数据），支持覆盖索引；而非聚簇索引的叶子节点存放的是主键值或指向数据行的指针。

由于叶子节点（数据页）只能按照一棵 B+ 树排序，故一张表只能有一个聚簇索引。非聚簇索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个非聚簇索引。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。