mysql索引原理简单说明

本次使用的是mysql5.7.17

首先说下索引中的组合索引，即多个字段组合的索引就叫做组合索引，但是什么时候会生效，什么时候会失效，我不是很清楚

首先建个表造点数据看下情况，

CREATE TABLE `bank` (
  `id` INT(11)  PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
  `bank_end` VARCHAR(255) DEFAULT NULL COMMENT '银行卡后四位',
  `bank_name` VARCHAR(255) COMMENT '银行名称',
  `bank_funds` VARCHAR(255) DEFAULT NULL COMMENT '银行卡余额',
  `bank_use` VARCHAR(255) COMMENT '该笔金额用途',
  `update_resume_times` VARCHAR(255) DEFAULT NULL COMMENT '余额变动最新时间',
  `resume_user` VARCHAR(255) DEFAULT NULL COMMENT '消费人',
  `bank_resume_status` CHAR(1) DEFAULT NULL
)

　　使用存储过程整点数据

 存储过程

DELIMITER $$

CREATE PROCEDURE generate_bank_data(IN num INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;  -- 每次插入的批次大小
    DECLARE bank_names VARCHAR(255);
    DECLARE bank_uses VARCHAR(255);
    DECLARE resume_users VARCHAR(255);
    DECLARE bank_end VARCHAR(4);
    DECLARE bank_funds VARCHAR(255);
    DECLARE update_time DATETIME;
    DECLARE resume_user VARCHAR(255);
    DECLARE bank_resume_status CHAR(1);

    -- 准备一些模拟数据的词汇
    SET bank_names = '工商银行,农业银行,建设银行,招商银行,平安银行,交通银行,中信银行';
    SET bank_uses = '消费,提现,转账,存款,支付,还款';
    SET resume_users = '张三,李四,王五,赵六,钱七,孙八,周九';

    -- 开启事务
    START TRANSACTION;

    -- 循环生成数据并逐条插入
    WHILE i < num DO
        -- 生成随机数据
        SET bank_end = LPAD(FLOOR(1000 + (RAND() * 9000)), 4, '0');
        SET bank_funds = CONCAT('¥', ROUND(1000 + (RAND() * 10000), 2));
        SET update_time = DATE_FORMAT(NOW() - INTERVAL (FLOOR(RAND() * 30)) DAY, '%Y-%m-%d %H:%i:%s');
        SET resume_user = SUBSTRING_INDEX(SUBSTRING_INDEX(resume_users, ',', FLOOR(1 + (RAND() * 7))), ',', -1);
        SET bank_resume_status = IF(RAND() > 0.5, '1', '0');

        -- 插入一条数据
        INSERT INTO bank (bank_name, bank_use, bank_end, bank_funds, update_resume_times, resume_user, bank_resume_status)
        VALUES (
            SUBSTRING_INDEX(SUBSTRING_INDEX(bank_names, ',', FLOOR(1 + (RAND() * 7))), ',', -1),  -- bank_name
            SUBSTRING_INDEX(SUBSTRING_INDEX(bank_uses, ',', FLOOR(1 + (RAND() * 6))), ',', -1),  -- bank_use
            bank_end,  -- bank_end
            bank_funds,  -- bank_funds
            update_time,  -- update_resume_times
            resume_user,  -- resume_user
            bank_resume_status  -- bank_resume_status
        );

        -- 每次插入 `batch_size` 条数据
        IF (i + 1) % batch_size = 0 OR i + 1 = num THEN
            COMMIT;
            START TRANSACTION;
        END IF;

        SET i = i + 1;
    END WHILE;

    -- 提交事务
    COMMIT;

END $$

DELIMITER ;

　　调用 

CALL generate_bank_data(50000);

　

 先来不同顺序的解释计划说明

 

 这里可以看到都是走了索引，和字段的先后顺序无关

 

 

 

 索引列顺序不正确

组合索引的字段顺序非常重要，MySQL 会尝试从索引的最左边开始匹配。如果查询条件没有按照组合索引的顺序来匹配字段，索引就不能有效地工作

　or操作符会导致索引失效

当查询包含多个条件并使用 OR 操作符时，MySQL 会试图分别使用每个条件的索引，这可能导致它无法有效地结合这些条件，从而导致索引的失效。MySQL 通常会放弃使用组合索引，而选择进行全表扫描

　对索引列使用函数表达式

索引的目的是加速检索数据，但如果对索引列使用了函数（如 UPPER(), LOWER()）或者其他表达式，索引的值会在查询时被修改或计算，因此 MySQL 无法直接通过索引查找数据

　　

 这里做了数字转为字符串的操作，原先定义好的索引没法使用，这里在进行进一步的解释

在这里mysql会识别到使用这个字段并不是高效的查找，所以会进行舍弃