MySQL_11索引

1.什么是索引

索引是在数据库表的字段上添加的，是为了提高查询效率而存在的一种机制。

可以在一张表的一个字段上添加一个索引，也可以多个字段联合起来添加索引。

索引就好比是新华词典的目录，能够达到缩小扫描范围的目的。

对于词典来说，如果要查某一个汉字，有两种方式：

第一种：一页一页找，知道找到为止。这种方式属于全扫描，效率较低。
第二种：先通过目录(索引)定位一个大概的位置，然后直接定位到这个具体的位置，做局域性扫描。这种方式属于部分扫描，效率较高。

相对应的，对于MySQL来说，也有两种查询方式：

全表扫描
根据索引扫描

现在有一个表t_vip，表中数据如下：

+----+----------+
| id | name     |
+----+----------+
|  1 | ZhangSan |
|  2 | LiSi     |
|  3 | WangWu   |
|  4 | ZhanLiu  |
+----+----------+

执行一条SQL语句：

select * from t_vip where name = 'LiSi';

以上的SQL语句会去name字段上扫描，原因是查询条件是name = 'LiSi'

如果name字段上没有添加索引，或者说没有给name字段创建索引，MySQL会进行全扫描，将name字段上的每一个值都比对一遍。效率较低。

注意：

实际上，一本词典的目录是按照a b c d...的顺序排序的。为什么要排序呢？因为只有排序了才会有区间查找这一说。所谓的缩小扫描范围其实就是扫描某个区间。
在MySQL数据库中的索引也是要排序的。并且这个索引的排序和TreeSet数据结果相同。TreeSet(TreeMap)底层是一个自平衡二叉树，在MySQL中索引是一个B-Tree数据结构，遵循左小右大的原则存放，采用中序遍历方式遍历取数据。

2.索引的实现原理

新建一个t_user表，主键是id：

+-----+----------+
| id  | name     |(物理存储编号)
+-----+----------+-----------
|  90 | ZhaoLiu  |  0x1111
| 100 | ZhangSan |  0x2222
| 120 | WangWu   |  0x8888
| 110 | Tom      |  0x4444
|  50 | Lorry    |  0x9999
|  78 | LiSi     |  0x7777
| 130 | Jerry    |  0x5555
+-----+----------+

1.在任何数据库中，主键上都会自动添加索引对象，id字段是主键字段，所以自动有索引。此外，在MySQL中，一个字段如果有unique约束的话，也会自动创建索引对象。

2.在任何数据库中，任何一张表中的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。

3.在MySQL中，索引是一个单独的对象，不同的存储引擎以不同的形式存在：

在MyISAM中：索引存储在.MYI文件中
在InnoDB中：索引存储在一个逻辑名称叫tablespace的地方
在MEMORY中：索引被存储在内存中

不管索引存储在哪里，索引在MySQL中都是以一个树的形式存在。(自平衡二叉树B-Tree)

上面表中id字段的索引对象：

表中字段不会动，索引对象会进行排序。

实际上mysql中索引对象的结构更复杂，这里只是进行了一个简单的示范。

现在查询id = 50的那条记录：

select * from t_vip where id = 50;

mysql发现id字段上面有索引对象，所以会通过索引对象id_Index进行查找：

通过索引对象id_Index的二叉树，遍历定位到：50(缩小扫描范围，快速定位)
通过50得到物理编号：0x9999，此时马上进行SQL语句转换：
- ```
select * from t_user where 物理编号 = 0x9999;
```

索引的实现原理：缩小扫描的范围，避免全表扫描。

3.在什么情况下需要给字段添加索引

情况一：数据量庞大(是相对的比较庞大，需要根据具体硬件环境测试)
情况二：该字段经常跟在where后面，以条件的形式存在，也就是说这个字段总是被扫描。
情况三：该字段很少进行DML(insert update delete)操作。因为每次进行DML操作后，索引都需要重新排序。

建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。

建议通过主键、被unique约束的字段进行查询，查询的效率是较高的。

4.索引的创建和删除

4.1创建索引

给emp表的ename字段添加索引，起名为emp_ename_index

create index emp_ename_index on emp(ename);

4.2删除索引

删除上面创建的索引

drop index emp_ename_index on emp;

5.查看是否使用了索引

查看查询一条记录是否使用了索引：

mysql> explain select * from emp where ename = 'king';
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
|id|select_type|table|type|possible_keys| key|key_len|ref |rows|Extra      |
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
| 1|SIMPLE     |emp  |ALL |NULL         |NULL|NULL   |NULL|  14|Using where|
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+

查询出的结果中，type为ALL，表示全扫描，rows的值为14，表示扫描了14条记录。没有使用索引。

在ename字段上创建索引：

mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

再次查看：

mysql> explain select * from emp where ename = 'king';
+--+-----------+-----+----+---------------+---------------+-------+-----+----+----------+
|id|select_type|table|type|possible_keys  |key            |key_len|ref  |rows|Extra     |
+--+-----------+-----+----+---------------+---------------+-------+-----+----+----------+
| 1|SIMPLE     |emp  |ref |emp_ename_index|emp_ename_index|33     |const|   1|Using where
+--+-----------+-----+----+---------------+---------------+-------+-----+----+----------+

type变成了ref，表示使用了索引，rows值为1，表示只扫描了一次。

6.索引失效的情况

第一种情况：模糊查询

查询ename字段值中以“A“结尾的名字：

explain select * from emp where ename like '%A';

查看该语句是否使用了索引：

mysql> explain select * from emp where ename like '%A';
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra      |
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
| 1|SIMPLE     |emp  |ALL |NULL         |NULL|NULL   |NULL|  14|Using where|
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+

从结果来看，并没有使用索引。(type = ALL)

ename上即使添加了索引，也不会走索引。

原因是因为模糊匹配当中以”%“开头了。

所以应该尽量避免模糊查询时以”%“开始，这也是一种优化的策略。

第二种情况：使用or时，有一边的字段上没有索引

如果使用or，那么要求or两边的条件字段都要有索引，才会走索引。

如果其中一边的字段没有索引，那么另一个字段上的索引也会失效。

mysql> explain select * from emp where ename = 'king' or job = 'manager';
+----+------------+-------+-----+-----------------+------+--------+----+-----+-----
| id| select_type | table | type| possible_keys   | key  | key_len|ref |rows |   Extra
+----+------------+-------+-----+-----------------+------+--------+----+-----+-----
|  1 | SIMPLE     | emp   | ALL | emp_ename_index | NULL | NULL   |NULL|  14 |Using where 
+----+------------+-------+-----+-----------------+------+----- --+----+-----+-----

第三种情况：使用复合索引时，没有使用左侧的列查找

两个字段或更多的字段联合起来添加一个索引，叫复合索引。

创建一个job和sal的复合索引：

create index emp_job_sal_index on emp(job,sal);

现使用复合索引中左侧的job进行查询：

mysql> explain select * from emp where job = 'manager';
+--+-----------+-----+----+-----------------+-----------------+-------+-----+------+-----
|id|select_type|table|type|possible_keys    | key             |key_len |ref  |rows|Extra |
+--+-----------+-----+----+-----------------+-----------------+-------+-----+----+-------
| 1| SIMPLE    | emp | ref|emp_job_sal_index|emp_job_sal_index|30     |const|  3 |Using where |
+--+-----------+-----+----+-----------------+-----------------+-------+-----+------+-----

通过结果可见使用了索引。(type = ref)

再使用右侧的sal进行查询：

mysql> explain select * from emp where sal = '1000';
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
|id|select_type|table|type|possible_keys|key |key_len|ref |rows|Extra      |
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+
| 1|SIMPLE     |emp  |ALL |NULL         |NULL|NULL   |NULL|  14|Using where|
+--+-----------+-----+----+-------------+----+-------+----+----+-----------+

通过结果可见没有使用索引。(type = ALL)

第四种情况：在where中索引列参加了运算

在sal字段上添加索引：

create index emp_sal_index on emp(sal);

原生态地查询sal：

mysql> explain select * from emp where sal = 1000;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

在查询时有数学运算：

mysql> explain select * from emp where sal + 1 = 1000;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

第五种情况：在where中索引列使用了函数

mysql> explain select * from emp where lower(ename) = 'smith';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

7.索引的分类

单一索引：一个字段上添加索引。
复合索引：两个字段或者更多的字段上添加索引。
主键索引：主键上添加索引。
唯一性索引：具有unique约束的字段上添加索引。

...

注意：唯一性比较弱的字段上添加索引用处不大。

posted @ 2021-07-24 21:59 TSCCG 阅读(70) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

TSCCG

MySQL_11索引

MySQL_11索引

1.什么是索引

2.索引的实现原理

3.在什么情况下需要给字段添加索引

4.索引的创建和删除

5.查看是否使用了索引

6.索引失效的情况

7.索引的分类

公告