MySQL中针对SQL语句优化

109 阅读 0 评论 72 点赞

我是靠谱客的博主隐形中心，最近开发中收集的这篇文章主要介绍MySQL中针对SQL语句优化，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

sql方面的优化最简单最直观的就是：
1.在频繁出现的查询条件处加索引；
2.使用explain看下执行计划

准备测试表：

CREATE TABLE `t_user_test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(16) NOT NULL,
`age` TINYINT(4) NOT NULL,
`city` VARCHAR(16) NOT NULL,
`create_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(`id`) 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4;

准备测试数据：

desc t_user_test;
insert into t_user_test values(null,'star',25,'bj',null);
#数据不够先多执行几次
insert into t_user_test(name,age,city) select name,age,city from t_user_test;
update t_user_test set name = concat(name,id); 
update t_user_test set age = age + floor(rand() * 50);

普通查询：

select * from t_user_test  where name = 'star9130';
#type:all  全表扫描
explain select * from t_user_test  where name = 'star9130';

创建普通索引：

create index idx_nac on t_user_test(name,age,city);
select * from t_user_test  where name = 'star9130';
#type:ref  简单理解普通索引
explain select * from t_user_test  where name = 'star9130';

索引最左匹配：

select * from t_user_test  where name = 'star9130' and age = 72;
#type:ref  
explain select * from t_user_test  where name = 'star9130' and age = 72;    

#无索引列最左字段，无法进入索引
explain select * from t_user_test  where age = 72;                       #type:all
explain select * from t_user_test  where city = 'bj';                        #type:all
explain select * from t_user_test  where age=72 and city = 'bj';    #type:all

#包含索引列最左字段，可以进入索引，条件顺序不影响，mysql优化执行
explain select * from t_user_test  where name = 'star9130' and city = 'bj';     #type:ref
explain select * from t_user_test  where city = 'bj' and name = 'star9130';     #type:ref
explain select * from t_user_test  where age = 72 and name = 'star9130';     #type:ref

索引失效：

select * from t_user_test  where substr(name,1,4) = 'star';
#type:all   避免函数作用于条件等号左边
explain select * from t_user_test  where substr(name,1,4) = 'star';   
#type:all   避免使用模糊查询 
explain select * from t_user_test  where name like 'star%';          
#type:range 尽量使用索引覆盖
explain select name,age,city from t_user_test  where name like 'star%';      
#type:all 字符类型加引号，避免隐式转换
explain select * from t_user_test where name = 2222

其他：

使用union取代or，合理使用in和exists，日期字段查询等号右侧使用常量，使用>=和<=取代<>等等，可以结合表数据量自行测试。