MySQL索引实战附带存储过程

姜同学

发布于 2022-10-27 16:58:41

6440

发布于 2022-10-27 16:58:41

文章被收录于专栏：姜同学姜同学

索引的分类

索引类型	索引简介	SQL语法
单值索引	即一个索引只包含单个列，一个表可以有多个单列索引	随表一起建索引：CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name));单独建单值索引：CREATE INDEX idx_customer_name ON customer(customer_name);删除索引：DROP INDEX idx_customer_name on customer;
唯一索引	单值索引的进阶版，索引列的值必须唯一，但允许有空值，当然有空值也只能有一个，不然还能叫唯一吗。	随表一起建索引：CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_no));单独建唯一索引：CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no);删除索引：DROP INDEX idx_customer_no on customer ;
主键索引	设为主键的列会自动创建的索引，是唯一索引的进阶版，进阶的原因就是主键索引不允许有空值。	随表一起建索引：CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id));单独建主键索引：ALTER TABLE customeradd PRIMARY KEY customer(customer_no);删除建主键索引：ALTER TABLE customerdrop PRIMARY KEY ;
复合索引	即一个索引包含多个列	随表一起建索引：CREATE TABLE customer (id INT(10) UNSIGNED AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),PRIMARY KEY(id),KEY (customer_name),UNIQUE (customer_name),KEY (customer_no,customer_name));单独建索引：CREATE INDEX idx_no_name ON customer(customer_no,customer_name);删除索引：DROP INDEX idx_no_name on customer ;

索引该如何添加

索引虽然是个可以提高查询效率的好东西，但是吗世间万物自然有好有坏，索引有索引的好处，自然就会有其不完美的地方，建立索引之后，MySQL除了维护数据文件之外自然又多了一份维护索引文件的任务，如果数据频繁的变动，维护两份索引文件的MySQL自然是有些招架不住。反馈的效率自然就会慢于没有索引的时候，索引文件自然是要有它落盘的地方，所以就要占用空间喽，虽然硬盘不贵但是这些都是要加进项目的预算哦。所以我们就要了解一下索引的特性才能有效地趋利避坏。

那些情况下不要创建索引

表记录太少
经常增删改的表或者字段
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引（不是男就是女你拿它创建索引干啥。。）

哪些情况需要创建索引

先说一下基本原则，找大佬评估下表的数据量。不然不要贸然的给自己找麻烦。

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单值/复合索引的选择问题，复合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

战前准备

初始化数据

表记录太少时不建议添加索引，所以咱们得让表数据量大起来呀，这里就涉及到如何高效的向数据库中插入数据的问题喽。首相我们的第一反应应该是数据量很大的时候开启事务批量插入自然是由于循环一条一条的插入的，次之自然就是MySQL自带的特性–存储过程喽，你写程序进行批量的插入自然是没有人家自身的SQL遍程更快捷喽。

建表

# 部门表
CREATE TABLE `dept` (
 `id` INT() NOT NULL AUTO_INCREMENT,
 `deptName` VARCHAR() DEFAULT NULL,
 `address` VARCHAR() DEFAULT NULL,
 ceo INT NULL ,
 PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT= DEFAULT CHARSET=utf8;
 
# 员工表
CREATE TABLE `emp` (
 `id` INT() NOT NULL AUTO_INCREMENT,
 `empno` INT NOT NULL ,
 `name` VARCHAR() DEFAULT NULL,
 `age` INT() DEFAULT NULL,
 `deptId` INT() DEFAULT NULL,
 PRIMARY KEY (`id`)
 #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT= DEFAULT CHARSET=utf8;

创建函数为存储过程做准备

# 创建函数
# 随即生成指定字符的字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR()
BEGIN    
 DECLARE chars_str VARCHAR() DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR() DEFAULT '';
 DECLARE i INT DEFAULT ;
 WHILE i < n DO  
 SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(+RAND()*),));  
 SET i = i + ;
 END WHILE;
 RETURN return_str;
END //
DELIMITER ;

# 随即生成指定范围的数字用于生成关联ID
DELIMITER //
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT()
BEGIN   
 DECLARE i INT DEFAULT ;  
 SET i = FLOOR(from_num +RAND()*(to_num -from_num+))   ;
RETURN i;  
 END//
DELIMITER ;

存储过程

其实嘛，只要了解编程，写一个存储过程不过是分分钟的事情，存储过程在压测阶段初始化数据的时候经常会用到，当然如果你的leader经常会让你统计某个日期范围的报表数据的时候，存储过程也是一个不错的选择。

MYSQL 存储过程中的关键语法

声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)

存储过程开始和结束符号:

BEGIN .... END

变量赋值:

SET @p_in=;
SET autocommit = ;

变量定义:

DECLARE l_int int unsigned default ;

创建mysql存储过程、存储函数:

create procedure 存储过程名(in|out|inout 参数 参数类型)

创建函数:

create function 函数名(参数 参数类型) returen 返回值类型

调用存储过程:

CALL 存储过程名称

使用存储过程初始化部门表和员工表数据

# 创建存储过程
# 部门表插入1w数据
DELIMITER //
CREATE PROCEDURE PROC_DEPT_INSERT(IN num INT)
BEGIN
DECLARE i INT DEFAULT ;
SET autocommit = ;
WHILE i < num DO
 	INSERT INTO dept(deptName,address,ceo) VALUES(rand_string(),rand_string(),rand_num(,));
	SET i = i+;
END WHILE;
COMMIT;
END//
DELIMITER ;

CALL PROC_DEPT_INSERT();
# 员工表插入50w数据
DELIMITER //
CREATE PROCEDURE PROC_EMP_INSERT(IN num INT)
BEGIN
DECLARE i INT DEFAULT ;
SET autocommit = ;
WHILE i<num DO
	INSERT INTO emp(empno,name,age,deptId) VALUES(rand_num(,),rand_string(),rand_num(,),rand_num(,));
	SET i = i+;
END WHILE;
COMMIT;
END//
DELIMITER ;

CALL PROC_EMP_INSERT();

SHOW PROCEDURE STATUS;
# 有报错删除了重新创建了下。。
DROP PROCEDURE PROC_DEPT_INSERT;
SELECT count(*) FROM dept;
SELECT count(*) FROM emp;

因为我们在进行索引测试的时候是要删除掉一些对测试有影响的索引的，所以也准备了批量删除指定表索引的存储过程，工具吗会用就行了，就不用过多研究啦。

DELIMITER $$
CREATE  PROCEDURE PROC_DROP_INDEX(dbname VARCHAR(),tablename VARCHAR())
BEGIN
       DECLARE done INT DEFAULT ;
       DECLARE ct INT DEFAULT ;
       DECLARE _index VARCHAR() DEFAULT '';
       DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index= AND    index_name <>'PRIMARY'  ;
       DECLARE  CONTINUE HANDLER FOR NOT FOUND set done= ;      
        OPEN _cur;
        FETCH   _cur INTO _index;
        WHILE  _index<>'' DO 
               SET @str = CONCAT("drop index ",_index," on ",tablename ); 
               PREPARE sql_str FROM @str ;
               EXECUTE  sql_str;
               DEALLOCATE PREPARE sql_str;
               SET _index=''; 
               FETCH   _cur INTO _index; 
        END WHILE;
   CLOSE _cur;
   END$$

DELIMITER ;

不多BB实战优化

单表使用索引及常见索引失效

全值匹配我最爱

系统中经常出现的sql语句如下：（SQL_NO_CACHE 不使用缓存） a.SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30; b.SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4; c.SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = ‘abcd’;

看到如上的简单SQL查询我们自然就想到单值索引和复合索引啦，上面提到过啦对于单值索引而言复合索引的性价比更高哦，我也不再解释什么，加个索引看看优化的效果如何喽。

### **直接查询**

### **添加复合索引**
CREATE INDEX IDX_AGE_DEPTID_NAME ON emp(age,deptid,name);

效果显著有木有。。

最佳左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

如果系统经常出现的sql如下： SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = ‘abcd’ 或者

SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = ‘abcd’ 那原来的IDX_AGE_DEPTID_NAME 还能否正常使用？

有没有生校咱们用Explain分析一下就好喽。

通过key_len=5可以看出来只有age列索引生效啦。

type=ALL 全表扫描喽可真是VeryGood。

所以过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

这两条sql哪种写法更好 SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE ‘abc%’ ;

SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = ‘abc’;

### **添加索引进行测试**
CREATE INDEX IDX_NAME ON emp(name);

命中索引。

全表扫描。

所以不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

存储引擎不能使用索引中范围条件右边的列

如果系统经常出现的sql如下：

SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = ‘abc’ ;

那么索引 idx_age_deptid_name这个索引还能正常使用么？

通过key_len=10可以计算出只有两个int类型并且可以为null的列生效了也就是name列失效啦。

所以如果where中包含范围查询请把该条件放到最后在按照最佳做前缀原则添加索引。

mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

  CREATE INDEX idx_name ON emp(NAME)
  EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE   emp.name <>  'abc'

不多BB~~这个不等于他是不是让索引失效啦。

is not null 也无法使用索引,但是is null是可以使用索引的

like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

这个也很好理解喽，在B+树中是按照列的值来进行排序的并且遵守字典序，如果首字母都无法确定那个的话，B+树表示很难办呀。

字符串不加单引号索引失效

如果不加单引号的话会涉及到一个类型转换的过程也算是对索引列进行函数操作了吧，所以自然就失效喽。

小总结

假设index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4	N
where a = 3 and c = 5	使用到a，但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用索引
where a = 3 and b like ‘kk%’ and c = 4	Y,使用到a,b,c
where a = 3 and b like ‘%kk’ and c = 4	Y,只用到a
where a = 3 and b like ‘%kk%’ and c = 4	Y,只用到a
where a = 3 and b like ‘k%kk%’ and c = 4	Y,使用到a,b,c

对于单键索引，尽量选择针对当前query过滤性更好的索引，在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。书写sql语句时，尽量避免造成索引失效的情况。