mysql 索引学习

学习参考地址：https://www.bilibili.com/video/BV1Kr4y1i7ru?p=87

1.表、数据准备

2.索引学习

 

 

 

 

1.表、数据准备

 SHOW CREATE TABLE student;

 

 

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `create_user` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tb_index_student_name` (`name`),
  KEY `tb_index_student_age` (`age`),
  KEY `tb_index_student_address` (`address`),
  KEY `tb_index_student_name_age_address` (`name`,`age`,`address`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb3

数据准备，插入10万条数据，测试用

DELIMITER $$

USE `school`$$

DROP PROCEDURE IF EXISTS `my_insert`$$

CREATE DEFINER=`root`@`%` PROCEDURE `my_insert`()
BEGIN
        DECLARE i INT DEFAULT 100000;
        WHILE i>0 DO
            IF i<=30000 THEN 
                  INSERT INTO student(NAME,age,address,create_time,create_user)
               VALUE(CONCAT('ligy',i),33,CONCAT('南昌',i),NOW(),UUID()) ;
            ELSEIF i<20 THEN
             INSERT INTO student(NAME,age,address,create_time,create_user)
               VALUE(CONCAT('ruirui',i),1,CONCAT('深圳',i),NOW(),UUID()) ;
            ELSE
               INSERT INTO student(NAME,age,address,create_time,create_user)
               VALUE(CONCAT('ruifen',i),25,CONCAT('梅州',i),NOW(),UUID()) ;
             
            END IF;
         SET i := i -1;
        END WHILE;
    END$$

DELIMITER ;

 

 

2.索引学习

 

-- ----------索引--------------------------------------------------------------------------------
B tree
B+ tree
HASH tree

-- ----------索引操作--------------------------------------------------------------------------------
-- 索引操作：查询、新增、修改
-- 创建索引
CREATE INDEX tb_idx_course_name ON course(NAME)
-- 查询索引
SHOW INDEX FROM course;
-- 删除索引
DROP INDEX tb_idx_course_name ON course;

CREATE INDEX tb_index_student_name ON student(NAME);
CREATE INDEX tb_index_student_age ON student(age);
CREATE INDEX tb_index_student_address ON student(address);
CREATE INDEX tb_index_student_name_age_address ON student(NAME,age,address);
--索引默认的是升序，排序，可自己去倒序
Create index tb_index_student_createtime on student(create_time desc)

-- -----------sql分析-------------------------------------------------------------------------------
-- sql执行频率，_______ 是7个下划线，代表7个字符
SHOW GLOBAL STATUS LIKE 'COM_______'
SHOW GLOBAL STATUS LIKE 'COM_SELECT'

-- 查询是否支持  YES支持
SELECT @@have_profiling;
-- 默认0 关闭
SELECT @@profiling;
SET @@profiling=1;
--  查询sql消耗时间
SHOW PROFILES
-- 查询某sql某语句具体耗时
SHOW PROFILE FOR QUERY 4
SHOW PROFILE CPU FOR QUERY 4

-- 查询执行计划，包括：是否走索引，索引长度，id； id不同，越大越先执行，id一样，排前面的先执行；
EXPLAIN SELECT * FROM student WHERE NAME='ruifen88888' AND age=25;

-- 查询 
SELECT * FROM student WHERE id=1  
-- -----------sql优化-------------------------------------------------------------------------------
-- 规则：最左前缀法制
-- 规则：范围查询<> 右边的列，不会走索引
EXPLAIN SELECT * FROM student WHERE NAME='ruifen100000' AND age>=25 AND address='地球人，都是'
EXPLAIN SELECT * FROM student WHERE  age>=25 AND address='地球人，都是'
EXPLAIN SELECT * FROM student WHERE NAME='ruifen100000'
EXPLAIN SELECT * FROM student WHERE age=25
EXPLAIN SELECT * FROM student WHERE address='地球人，都是'

-- 运算，索引会失效
-- where条件，不加引号，索引失效
-- 模糊查询，尾部，索引不失效,比如：aa%
-- %a 模糊查询，索引失效
EXPLAIN SELECT * FROM student WHERE address=12
EXPLAIN SELECT * FROM student WHERE SUBSTRING(address,1,2)='地球'
EXPLAIN SELECT * FROM student WHERE address LIKE '地球%'  -- 不失效
EXPLAIN SELECT * FROM student WHERE address LIKE '%地球'  -- 失效

-- 不用select * ，因为* 走索引了，也会有回表查询的动作

-- 因为这几个字段，都在索引之中，不用回表查询，效率很高
EXPLAIN SELECT id,NAME,age,address FROM student WHERE NAME='ruifen100000' AND age=25 AND address='地球人，都是'
-- 走索引查询了，但是会有回表查询，消耗了时间
EXPLAIN SELECT * FROM student WHERE NAME='ruifen100000' AND age=25 AND address='地球人，都是'
--  查询sql消耗时间，通过这可以查看，消耗的时间，多了
SHOW PROFILES