2021-09-02

mysql索引详解

1:索引简介

	简单来说索引就是数据库管理系统中的一种排序的数据结构，目的是为了加快查询的速度，可以类比成书本的目录。

2:存储引擎

	mysql中常用的存储引擎有innodb和mysaim，当我们创建了一张表的时候，两种存储引擎会生成不同的文件。在创建了一张表的时候，innodb会在datadir目录下生成一个table_innodb.frm的文件，而mysaim会在datadir目录下生成三个文件粉笔额是table_mysaim.frm,table_mysqim.MYD,table_mysqim.MYI三个文件，mysim会将相关的三个数据(frm:文件存储定义表，MYD:数据文件,MYI:索引文件扩展名)分别存储在这三个文件中，innodb会将三种数据聚和在一起存储在一个文件中，由此我们也可以简单的理解两中存储引擎最大的区别(是否支持事务)，因为mysaim将数据存储在三个不同的文件中，所以无法支持事务，而innodb将所有的数据文件都聚合的存储在一个数据文件中，所以innodb是支持事务的。因为mysaim是表级锁，而且支持全文索引，所以在插入和查询方面mysaim的效率是要高于innodb的。

3:创建索引的方式

Mysql默认的是B+Btree，项目中一般使用Btree，HASH的话，一般不会使用，因为他是基于哈希表实现的，他是基于索引的列来计算hashCode值，然后在hashCode对应的位置存储该值所在的位置，因为使用的是散列算法，所以查询的速度特别快，但是因为相同的数据的hashCode值是一样的，所以一般情况下是不会使用的。

4:索引的适用情况

一般情况下适合用于读较多的表

5:举例

5.1:单独索引举例

创建一张表
DROP TABLE IF EXISTS staff;
CREATE TABLE IF NOT EXISTS staff (
id INT PRIMARY KEY auto_increment,
name VARCHAR(50),
age INT,
pos VARCHAR(50) COMMENT '职位',
salary DECIMAL(10,2)
);

往表中添加数据
INSERT INTO staff(name, age, pos, salary) VALUES('Alice', 22, 'HR', 5000);
INSERT INTO staff(name, age, pos, salary) VALUES('Bob', 22, 'RD', 10000);
INSERT INTO staff(name, age, pos, salary) VALUES('David', 22, 'Sale', 120000);

创建三个单独的字段索引
ALTER TABLE staff ADD INDEX idx_name(name);
ALTER TABLE staff ADD INDEX idx_age(age);
ALTER TABLE staff ADD INDEX idx_pos(pos);

我们执行一条查询语句来观察索引的使用情况
explain select * from staff where name = "Alice";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staff | NULL       | ref  | idx_name      | idx_name | 153     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
我们可以看出来在这条sql执行的时候使用到了idx_name这条索引。
接下来我们在增加一个限制条件，再来看看索引的使用情况
explain select * from staff where name = "Alice" and age = 22;
+----+-------------+-------+------------+------+------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | staff | NULL       | ref  | idx_name,idx_age | idx_name | 153     | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------+----------+---------+-------+------+----------+-------------+
从执行的结果可以看出此处只使用的一个索引，一开始我是以为sql语句只能使用一个索引，其实在mysql 5.0版本以后就支持多索引字段查询了(合并索引)，此处未生效的原因是因为mysql在分析这条sql语句的阶段会认为使用一条索引的效率会高于使用两个索引，所以才会选择使用一个索引

6：联合索引(最左前缀原则)

联合索引的主要目的就是为了避免索引过多造成过多的写和磁盘空间的开销，比如你创建了一个idx_a_b_c(a,b,c)的一个联合索引，就相当于你创建了三个索引((a),(a,b),(a,b,c))。而且符合索引可以筛选出更少的数据查询效率更高。