MySQL-索引优化

最新推荐文章于 2024-06-22 15:26:37 发布

月弦YueXian

最新推荐文章于 2024-06-22 15:26:37 发布

阅读量72

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/zZZ251682553/article/details/125171438

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前言

本文通过创建含有大量数据的数据表，测试并总结MySQL在单表、关联表、子查询、排序分组时对应的索引创建规则。

准备工作

创建表

-- 创建数据表，大量数据的表
CREATE TABLE `dept` (
                        `id` INT(11) NOT NULL AUTO_INCREMENT,
                        `deptName` VARCHAR(30) DEFAULT NULL,
                        `address` VARCHAR(40) DEFAULT NULL,
                        ceo INT NULL ,
                        PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `emp` (
                       `id` INT(11) NOT NULL AUTO_INCREMENT,
                       `empno` INT NOT NULL ,
                       `name` VARCHAR(20) DEFAULT NULL,
                       `age` INT(3) DEFAULT NULL,
                       `deptId` INT(11) DEFAULT NULL,
                       PRIMARY KEY (`id`)
    #CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

开启自定义函数功能

-- 开启mysql二进制日志，可以做主从复制。如不开启不能自定义mysql函数
show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

创建MySQL函数

-- 随机产生字符串，DELIMITER $$:以$为开始结束符,代替;
DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
    DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str VARCHAR(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < n DO
            SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
            SET i = i + 1;
        END WHILE;
    RETURN return_str;
END $$

-- 随机产生部门编号
DELIMITER $$
CREATE FUNCTION  rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
    DECLARE i INT DEFAULT 0;
    SET i = FLOOR(from_num +RAND()*(to_num -from_num+1))   ;
    RETURN i;
END$$

-- 假如要删除
-- drop function rand_num;

-- 创建往emp表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE  insert_emp(  START INT ,  max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO emp (empno, NAME ,age ,deptid ) VALUES ((START+i) ,rand_string(6)   , rand_num(30,50),rand_num(1,10000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END$$
-- 创建往dept表中插入数据的存储过程
DELIMITER $$
CREATE PROCEDURE `insert_dept`(  max_num INT )
BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    REPEAT
        SET i = i + 1;
        INSERT INTO dept ( deptname,address,ceo ) VALUES (rand_string(8),rand_string(10),rand_num(1,500000));
    UNTIL i = max_num
        END REPEAT;
    COMMIT;
END$$

-- 批量删除某个表上的所有索引
DELIMITER $$
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE ct INT DEFAULT 0;
    DECLARE _index VARCHAR(200) DEFAULT '';
    DECLARE _cur CURSOR FOR  SELECT   index_name   FROM information_schema.STATISTICS   WHERE table_schema=dbname AND table_name=tablename AND seq_in_index=1 AND    index_name <>'PRIMARY'  ;
    DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;
    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$$

插入数据

-- 执行存储过程，往dept表添加1万条数据
DELIMITER ;
CALL insert_dept(10000);
-- 执行存储过程，往emp表添加50万条数据
CALL insert_emp(100000,500000);

select count(*) from emp;
select count(*) from dept;

-- 删除数据库中表的索引
-- CALL proc_drop_index("dbname","tablename");

单表索引优化

索引失效规则

-- 1.全值匹配我最爱
explain select sql_no_cache * from emp where emp.age=30 and emp.deptId=4 and emp.name='abcd';
create index idx_age_depid_name on emp(age,deptId,name);

-- 2.最佳左前缀法则，如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。(带头大哥不能死，中间兄弟不能少)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30   AND emp.name = 'abcd';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1   AND emp.name = 'abcd';

-- 3.在索引列上做任何操作（计算、函数、(自动or手动)类型转换），都会导致索引失效而转向全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = 'abc';
create index idx_name on emp(name);

-- 4.存储引擎不能使用索引中范围条件右边的列。范围索引创建时应放在最后面
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc';
create index idx_age_deptid_name on emp(age,deptId,name); -- 部分索引有用
create index idx_age_name_deptid on emp(age,name,deptId); -- 全部索引有用

-- 5.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc';
CREATE INDEX idx_name ON emp(NAME);

-- 6.is not null 也无法使用索引,但是is null是可以使用索引的
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NULL;
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age IS NOT NULL;
create index idx_age  on emp(age);

-- 7.like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE '%abc%';
create index idx_name on emp(name);

-- 8.字符串不加单引号索引失效，因为myslq会自动类型转化，即3的问题
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name = 123;
create index idx_name on emp(name);

创建索引的建议

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

关联查询优化

保证被驱动表的join字段已经被索引。
left join 时，选择小表作为驱动表，大表作为被驱动表。
inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
子查询尽量不要放在被驱动表，有可能使用不到索引。
能够直接多表关联的尽量直接关联，不用子查询。

子查询优化

尽量不要使用not in 或者 not exists，用left outer join on xxx is null 替代。

 EXPLAIN SELECT SQL_NO_CACHE age,count(*) FROM  emp a LEFT OUTER JOIN dept b ON a.id =b.ceo WHERE b.ceo IS NULL

排序分组优化

无过滤不索引
如果SQL语句没有过滤条件，索引失效。

explain select SQL_NO_CACHE * from emp order by age,deptid;
explain select SQL_NO_CACHE * from emp order by age,deptid limit 10;

顺序错，必排序
如果order by后的字段的顺序和索引创建顺序不同，Extra会出现useing filesort。

explain  select * from emp where age=45 order by   deptid,name; 
explain  select * from emp where age=45 order by  deptid,empno;
explain  select * from emp where age=45 order by  name,deptid;
explain select * from emp where deptid=45 order by age;

方向反必排序
如果order by后的字段的排序方式不同，Extra会出现useing filesort。要保证排序方式全部相同。

explain select * from emp where age=45 order by  deptid desc, name desc ;
explain select * from emp where age=45 order by  deptid asc, name desc ;

索引选择
当范围条件和group by 或者 order by 的字段出现二选一时，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
```
SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno <101000 ORDER BY NAME ;
CREATE INDEX idx_age_name ON emp(age,NAME);
create index idx_age_eno on emp(age,empno); 
```
分组通排序
group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。