MySQL相关书籍的读书笔记（五）

最新推荐文章于 2024-12-26 09:21:57 发布

原创最新推荐文章于 2024-12-26 09:21:57 发布 · 251 阅读

0 ·

CC 4.0 BY-SA版权

jathams（人勤事事易，人懒事事难）

文章标签：

#索引

数据库同时被 2 个专栏收录

16 篇文章

订阅专栏

MySQL

11 篇文章

订阅专栏

本文详细介绍了MySQL数据库中各种索引类型，包括B+tree、哈希索引、聚集索引、普通索引、主键索引、唯一索引、覆盖索引、前缀索引和联合索引的特点及创建方法。同时，探讨了ICP、MRR和BKA等优化技术，以及索引选择和使用原则。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

索引：MySQL数据库使用了B+tree索引和哈希索引，

二叉树结构：B+tree是由二叉树–>平衡二叉树—>B-tree演化而来，所以先看二叉树；二叉树的每个节点至多有两个子节点，并且子树有左右之分，并且左子树的值永远小于右子树的值且小于根键值；
平衡二叉树：
B-tree：又被称作Btree；它的结构是一个节点可以拥有多于两个子节点的多叉查找树；所有叶子节点都出现在同一层；叶子节点不包含任何关键字信息；
B+tree:它是B-tree的变体，也是一个多路搜索树，关键信息出现在叶子节点中，并包含这些关键字记录的指针，叶子节点可以按照关键字的大小顺序链接；它的所有数据都保存在叶子节点中；B+tree索引是双向链表结构，而且B+tree结构做检索比B-tree要块；访问关键字的顺序是连续的，不用访再访问上一个节点；
1. B+tree之聚集索引和普通索引
  聚集索引：创建表的时候，显式的为表创建一个主键，则主键会自动被识别位聚集索引；聚集索引的叶子节点存放表中所有行数据记录的信息；
  普通索引：它的叶子节点中并不包含所有行的数据记录，只是会在叶子节点存有自己本身的键值和主键的值；在检索数据时，通过普通索引叶子节点上的主键来获得想要查找的行数据记录；
  普通索引创建方式：
```
alter table table_name add index index_name(索引字段);
或者：
create index index_name on table_name(索引字段);
```
  所有索引的查看都可以使用：show index from table_name;查看表中有那些索引；
```
mysql> show index from sys_app;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| sys_app |          0 | PRIMARY  |            1 | id          | A         |           2 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.14 sec)
```
2. ICP\MRR和BKA
  ICP:index condition pushdown 是MySQL使用索引从表重检索行数据的一种优化方式，从MySQL5.6版本开始支持，如果where条件使用了索引列，MySQL会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足条件的行从表重读取出来，ICP可以减少引擎层访问基表的次数和server层访问存储引擎的次数；
  
  配置：通过index_condition_pushdown选项控制，默认开启；
  show variables like ‘%optimizer_switch%’;
  
  可以通过命令行修改是否开启：
  set optimizer_switch=‘index_condition_pushdown=on|off’
  使用ICP优化时，执行计划的extra列会显示使用Using index_conditon；
  
  MRR:multi-range read optimization .这个特性也是5.6版本之后增加的；可以通过optimizer_switch的两个参数控制：一个是mmr；另一个是mrr_cost_based。默认开启；
  set global optimizer_switch=‘mrr=on|off,mrr_cost_based=on|off’;
  使用MMR优化时，执行计划的extra会显示Using的关键字提示；
  
  BKA：batched key access:是提高表join性能的算法，起作用是在读取被join表的记录时使用顺序I/O；默认关闭；
  
  使用这个优化方式，需要保证是在强制使用MRR的基础上才可以。
  set global optimizer_switch=‘mrr=on,mrr_cost_based=off’;
  
  set global optimizer_switch=‘batched_key_access=on’;
  使用这种优化方式时，执行计划的extra列将显示：Using join buffer;
主键索引：
是聚集索引，每张表中有且仅有一个主键，可以由表中的一个字段或者多个字段组成；
必须满足的三个条件：
1. 主键值必须唯一；
2. 不能包含null值
3. 一定要保证该值是自增属性；使用自增列做主键，可以保证写入的数据的顺序也是自增的，提高存取效率；
创建方法：
```
alter table table_name add primary key (column);
```
唯一索引：
约束条件：不能含有重复的值，但是可以有null值。一个表中只能具有一个主键，但是唯一索引可以具有多个。
创建方法：
```
alter table table_name add unique(column);
```
覆盖索引：
MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查询到索引之后再回去查询数据，这样可以减少大量的I/O操作，提高查询速度；而且在执行计划中，extra列中会出现Using indes的关键字；
```
--前提条件：主键为id，普通索引列为name列；
explain select id form t where name ='zhangsan';
--这个查询中，如果要检索主键id，而且在查询条件中name字段是普通索引，
```
前缀索引：针对大字段

联合索引：是在表中两个或者两个以上的列上创建索引。需要满足最左前缀法则：将选择性高的列放在前面。查询语句可以使用该索引的一部分，但是必须要从最左侧开始。

--创建:create index index_name on table_name(column1,column2..)
create index idx_c1_c2 on t(c1,c2);--将c1和c2创建为联合索引；
--可以使用的索引为：c1和c1，c2索引
select from t where c1='zang';
select from t where c2='lll' and c1='dd';
select from t where c1='aaa' annd c2 in('a','b');
select from t where c1='add' order by c2;
select from t order by c1,c2;

--下边的几个将不会用到这个联合索引
select from t where c2='dd';
select from t where rc2 = 'dddd' order by c1;

哈希索引：
1. 采用hash算法，把键值换算成新的hash值，
2. 哈希索引只可以进行等值查询，不能进行排序、模糊查询、范围查询等。

创建索引的列的选择经验

经常被查询的列，经常放在where后面
经常用于表连接的列；
经常排序分组的列：order byb 或者group by后面的字段

试一试：

mysql> desc uap_workflow_task_his;
+----------------------+--------------+------+-----+---------+----------------+
| Field                | Type         | Null | Key | Default | Extra          |
+----------------------+--------------+------+-----+---------+----------------+
| id                   | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| task_id              | varchar(64)  | YES  |     | NULL    |                |
| task_key             | varchar(64)  | YES  |     | NULL    |                |
| task_name            | varchar(100) | YES  |     | NULL    |                |
| bussines_id          | bigint(20)   | YES  |     | NULL    |                |
| bussines_name        | varchar(100) | YES  |     | NULL    |                |
| initiator_id         | varchar(64)  | YES  |     | NULL    |                |
| initiator_name       | varchar(100) | YES  |     | NULL    |                |
| initiator_department | varchar(64)  | YES  |     | NULL    |                |
| assignee_id          | varchar(64)  | YES  |     | NULL    |                |
| assignee             | varchar(64)  | YES  |     | NULL    |                |
| start_time           | datetime     | YES  |     | NULL    |                |
| end_time             | datetime     | YES  |     | NULL    |                |
| process_key          | varchar(64)  | YES  |     | NULL    |                |
| process_name         | varchar(64)  | YES  |     | NULL    |                |
| process_instance_id  | varchar(64)  | YES  |     | NULL    |                |
| type                 | int(11)      | YES  |     | NULL    |                |
| remark               | varchar(255) | YES  |     | NULL    |                |
| create_date          | datetime     | YES  |     | NULL    |                |
| user_id              | bigint(20)   | YES  |     | NULL    |                |
| state                | int(11)      | YES  |     | NULL    |                |
| form_key             | varchar(255) | YES  |     | NULL    |                |
+----------------------+--------------+------+-----+---------+----------------+
22 rows in set (0.18 sec)

mysql> select count(distinct initiator)/count(*) from uap_workflow_task_his;
1054 - Unknown column 'initiator' in 'field list'
mysql> select count(distinct initiator_id)/count(*) from uap_workflow_task_his;
+---------------------------------------+
| count(distinct initiator_id)/count(*) |
+---------------------------------------+
| 0.1563                                |
+---------------------------------------+
1 row in set (0.10 sec)