索引笔记（自用，无需浏览，写的比较乱）

雅诗莫拉

已于 2024-07-15 18:30:48 修改

阅读量259

点赞数 4

文章标签： mysql

于 2024-07-12 16:09:15 首次发布

本文链接：https://blog.csdn.net/weixin_45564990/article/details/140380485

版权

1.1、MySQL索引的创建方式

测试表语句

DROP TABLE IF EXISTS label;
CREATE TABLE label(
    `id` bigint(20) NOT NULL AUTO_INCREMENT  COMMENT '编号' ,
    `creator` varchar(64)   DEFAULT '' COMMENT '创建者' ,
    `create_time` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ,
    `updater` varchar(64)   DEFAULT '' COMMENT '更新者' ,
    `update_time` datetime NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' ,
    `deleted` bit(1) NOT NULL  DEFAULT b'0' COMMENT '是否删除' ,
    `tenant_id` bigint(20) NOT NULL  DEFAULT 0 COMMENT '租户编号' ,
    `label_name` varchar(250)    COMMENT '标签名称' ,
    PRIMARY KEY (id)
)  COMMENT = '标签表（可与多个模块匹配）';

创建索引

①使用CREATE语句创建

CREATE INDEX indexName ON tableName (columnName(length) );

测试语句

   CREATE INDEX idx_label_label_name ON label (label_name(250));

这种创建方式可以给一张已存在的表结构添加索引，其中需要指定几个值：
indexName：当前创建的索引，创建成功后叫啥名字。
tableName：要在哪张表上创建一个索引，这里指定表名。
columnName：要为表中的哪个字段创建索引，这里指定字段名。
length：如果字段存储的值过长，选用值的前多少个字符创建索引。
ASC|DESC：指定索引的排序方式，ASC是升序，DESC是降序，默认ASC。
③建表时DDL语句中创建

CREATE TABLE tableName(  
  columnName1 INT(8) NOT NULL,   
  columnName2 ....,
  .....,
  INDEX [indexName] (columnName(length))  
);

这种方式就比较适合在库表设计时，已经确定了索引项的情况下建立

但不管通过哪种方式建立索引，本质上创建的索引都是相同的，当索引创建完成后，可通过

SHOW INDEX FROM tableName;

简单的概述一下查询后，每个字段的含义：

①Table：当前索引属于那张表。
②Non_unique：目前索引是否属于唯一索引，0代表是的，1代表不是。
③Key_name：当前索引的名字。
④Seq_in_index：如果当前是联合索引，目前字段在联合索引中排第几个。
⑤Column_name：当前索引是位于哪个字段上建立的。
⑥Collation：字段值以什么方式存储在索引中，A表示有序存储，NULL表无序。
⑦Cardinality：当前索引的散列程度，也就是索引中存储了多少个不同的值。
⑧Sub_part：当前索引使用了字段值的多少个字符建立，NULL表示全部。
⑨Packed：表示索引在存储字段值时，以什么方式压缩，NULL表示未压缩，
⑩Null：当前作为索引字段的值中，是否存在NULL值，YES表示存在。
⑪Index_type：当前索引的结构（BTREE, FULLTEXT, HASH, RTREE）。
⑫Comment：创建索引时，是否对索引有备注信息。

删除索引

DROP INDEX indexName ON tableName;

在MySQL中创建索引时，其默认的数据结构就为B+Tree，如何更换索引的数据结构呢？如下：

CREATE INDEX indexName ON tableName (columnName(length)) USING HASH;

新增编辑（组合索引的使用）

CREATE TABLE `employees1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees1(name,age,position,hire_time) VALUES('张三',22,'manager',NOW());
INSERT INTO employees1(name,age,position,hire_time) VALUES('李四', 23,'dev',NOW());
INSERT INTO employees1(name,age,position,hire_time) VALUES('王五',23,'dev',NOW());

-- 使用
EXPLAIN SELECT name,age FROM employees1 WHERE name= '李四' AND age = 23 AND position ='manager';
-- 查询到的结果 key=idx_name_age_position，为使用到索引

-- 扫不到索引（尽量避免使用这种形式）
EXPLAIN SELECT * FROM employees1 WHERE name LIKE '%四';
-- 能扫到索引
EXPLAIN SELECT * FROM employees1 WHERE name LIKE '李%';
-- 能扫到索引
EXPLAIN SELECT name,age,position FROM employees1 WHERE name LIKE '%王%';

附一下EXPLAIN的结果通常包含以下列（字段）：

1. id：这是一个查询块的标识符，数字越大优先级越高。同一id值表示在同一个select中，id小的先执行。

2. select_type：表示查询的类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。

3. table：显示此行数据是关于哪个表的信息。

4. type：连接类型，从最好到最差依次为：system > const > eq_ref > ref > fulltext > index_merge > unique_subquery > index_subquery > range > index > ALL。其中，const或eq_ref类型通常表示使用了主键或唯一索引。

5. possible_keys：显示可能用于优化查询的索引列表。

6. key：显示MySQL实际决定使用的键（索引）。

7. key_len：显示使用键的长度，在不损失精确性的情况下，键越短越好。

8. ref：显示索引列与哪些列或常量相匹配。

9. rows：估计的被查询的行数，数值越小通常越好。

10. filtered：显示MySQL根据WHERE子句过滤掉的行的百分比。

11. Extra：包含MySQL解决查询的额外信息，如Using where（表示使用where子句过滤行）、Using index（表示使用覆盖索引）等。

数据库轮询插入数据方法

DROP PROCEDURE IF EXISTS insert_emp;
DELIMITER ;;
CREATE PROCEDURE insert_emp()
BEGIN
  DECLARE i INT;
  SET i = 1;
  WHILE(i <= 100000) DO
    INSERT INTO employees1(name, age, position) VALUES (CONCAT('Ber', i), i, 'dev');
    SET i = i + 1;
  END WHILE;
END;;
DELIMITER ;
CALL insert_emp();

定义了一个名为 insert_emp 的存储过程，用于循环插入员工信息到 employees1表中。存储过程中使用了一个变量 i 来计数，初始化为 1，然后通过循环插入 100000 条员工信息，每次插入时根据 i 的值生成 name 字段的值，并设置 age 为 i，position 为'dev'。调用了存储过程 insert_emp()，执行插入操作。

EXPLAIN SELECT * FROM employees12 force index(idx_name_age_position) WHERE name < "LiLei" AND age = 23 AND position = "dev";


EXPLAIN SELECT * FROM employees12 WHERE name < "LiLei" AND age = 23 AND position = "dev";

关于EXPLAIN 工具类的详细介绍可以点击查看

后续的索引优化及提升可以点击查看