MYSQL优化

全值匹配(最好)

建立复合索引(name,age,salary)

create  index idx_name_dep_age ON employee(name,dep_id,age )

使用到了1个

explain select * from employee where name ='鲁班'

 

使用到了2个

explain select * from employee where name ='鲁班' and dep_id=1

 

使用用到了3个

explain select * from employee where name ='鲁班' and dep_id=1 and age=10最佳左前缀法则

如果索引的多列,要遵守最左前缀法则,指的就是从索引的最左列开始 并且不跳过索引中的列

跳过第一个,索引失效

explain select * from employee where  dep_id=1 and age=10

 

跳过前两个, 索引失效

explain select * from employee where  age=10

跳过中间一个 ,只有第一个生效

explain select * from employee where  name ='鲁班'  and  age=10

 

顺序可以乱

explain select * from employee where   dep_id=1 and name ='鲁班'  and  age=10

 

不在索引列上做任何操作

计算,函数,类型转换会导致索引失效而转向全表扫描

explain select * from employee where trim(name) ='鲁班' 

 

范围条件右边的索引失效

explain select * from employee where name ='鲁班' and dep_id>1 and age=10

 

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

explain select * from employee where name <>'鲁班'

explain select * from employee where name !='鲁班'

 

is not null 无法使用索引

explain select * from employee where name is not null

 

用or连接时, 会导致索引失效

explain select * from employee where name ='鲁班' or age=10

 

like以通配符开头(%qw)索引失效变成全表扫描

explain select * from employee where name  like '%鲁班'

 

explain select * from employee where name  like '鲁%'

 

字符串不加引号索引失效

explain select * from employee where name=200

 

尽量使用覆盖索引

覆盖索引 查询的字段和建立的字段刚好吻合,这种我们称为覆盖索引、

explain select name,dep_id,age from employee

explain select name,dep_id,age from employee where trim(name) ='鲁班' 

explain select name,dep_id,age  from employee where name <>'鲁班'

explain select name,dep_id,age  from employee where name is not null

explain select name,dep_id,age  from employee where name ='鲁班' or age=10

explain select  name,dep_id,age  from employee where name  like '%鲁班'

explain select name,dep_id,age  from employee where name=200

 

排序与分组优化

在使用order by时，经常出现Using filesort

使用order by排序时, 如果没有按照索引顺序,会出现Using filesort

explain select * from employee where name ='鲁班'  order by  dep_id

explain select * from employee where name ='鲁班'  order by  dep_id, age

explain select * from employee where name ='鲁班'   order by age,dep_id

当使用*时 order by即使使用了 全部索引,也会也filesort

explain select * from employee    order by name,dep_id,  age  

explain select  name,dep_id,age   from employee    order by name,dep_id,  age  

explain select  name    from employee    order by name,dep_id,  age  

explain select  dep_id    from employee    order by name,dep_id,  age   

explain select  age       from employee    order by name,dep_id,  age  

explain select  dep_id,age       from employee    order by name,dep_id,  age 

explain select  dep_id,age,cus_id        from employee    order by name,dep_id,  age  

当索引字段为常量时 可以当作是存在索引的

explain select * from employee where name ='鲁班'   order by  dep_id,age

explain select * from employee where name ='鲁班'  and dep_id=1  order by age

使用排序一升一降会造成filesort

explain select name,dep_id,age from employee where name ='鲁班'   order   by  dep_id desc, age  asc

使用group by时,使用不当, 会出现Using temporary

如果没有按照索引顺序,会出现Using temporary

explain select  dep_id,age ,name    from employee where name ='鲁班'  group  by  dep_id ,age

explain select  dep_id,age ,name    from employee where name ='鲁班'  group  by  age ,dep_id 

以下语法使用索引的情况

假设建立复合索引(a,b,c),请说出以下条件是否使用到了索引及使用情况

where a = 4使用到了索引a

where a = 4 and b = 6; 使用到了索引a,b

where a = 4 and c = 5 and b = 6 ; 使用到了a,b,c

where b = 4 or b=5; 没有使用到索引

where a = 4 and c = 6; 使用到了索引a

where a  = 4 and b > 5 and c=6; 使用到索引a,b

where a = 4 and b like 'test%' and c=4 使用到了a,b   kk%相当于范围

where a = 4 order by b,c 使用到了a,不会有filesort

where b = 5 order by a 没用到索引  会有filesort

where b = 5 order by c 没有索引,会有filesort

where a = 5 group by  c,b 使用到了索引a,  造成Using temporary;

大批量数据时分页操作优化

传统分析查询

使用limit 随着offset增大, 查询的速度会越来越慢,

会把前面的数据都取出,找到对应位置

优化后分页查询

使用子查询优化

select * from employ e inner join (SELECT id from employ limit 500000 ,10 ) et on e.id = et.id

select * from employee where id >=(SELECT id from employee limit 500000 , 1) limit 10

使用 id 限定优化

记录上一页最大的id号 使用范围查询

限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利

select * from employee where  id between 1000000 and 1000100 limit 100;

select * from orders_history where id >= 1000001 limit 100;

in与exists

exists可以代替in

select * from employee where dep_id in (select id from department )

 

select * from employee e where exists  (select 1 from department  d where e.dep_id =d.id )

in与exist选择

当A 表中数据多于 B 表中的数据时,这时我们使用IN 优于 EXISTS

当B表中数据多于 A 表中的数据时，这时我们使用EXISTS 优于 IN

因此是使用IN 还是使用EXISTS 就需要根据我们的需求决定了。但是如果两张表中的数据量差不多时那么是使用IN 还是使用 EXISTS 差别不大

EXISTS 子查询只返回TRUE 或 FALSE ，因此子查询中的SELECT * 可以是SELECT 1 或者其他

 

 

explain

 

        SELECT p.id                            id,

               p.`name`                        name,

               p.sub_title                     subTitle,

               p.price                         price,

               p.pic                           pic,

               p.product_attribute_category_id productAttributeCategoryId,

               p.stock                         stock,

 

               ps.id                           sku_id,

               ps.sku_code                     sku_sku_code,

               ifnull(ps.price, 0)             sku_price,

               ifnull(ps.promotion_price, 0)   sku_promotion_price,

               ps.sp1                          sku_sp1,

               ps.sp2                          sku_sp2,

               ps.sp3                          sku_sp3,

               ifnull(sku.stock, 0)            sku_stock,

               ps.pic                          sku_pic,

               c.code                          keywords 

        FROM pms_product p

                 inner join pms_product_site s on p.id = s.product_id

 

                 LEFT JOIN pms_sku_stock ps ON p.id = ps.product_id

                 LEFT JOIN pms_product_site_sku sku on s.id = sku.product_site_id and sku.sku_stock_id = ps.id

                 left join pms_product_category c on p.product_category_id = c.id

        WHERE p.id = 141

          and s.tenant_id = 'f652e66ac0714627aa66c58471455680'

          and  s.site_id = '30251497'

 

 

 

explain

select p.id,p.name,p.pic ,p.price,p.product_sn productSn ,packing_coefficient packingCoefficient ,

purchase_price purchasePrice, s.onekey_car onekeyCar,s.shop_delivery shopDelivery,

  sku.stock, st.skucode skucode, st.sp sp from pms_product p

left join (select product_id , min(sku_code ) skucode ,min(sp1) sp from pms_sku_stock

where product_id is not null group by product_id) st on p.id = st.product_id

inner join pms_product_site s on p.id = s.product_id

LEFT JOIN (select product_site_id,IFNULL( sum( stock) ,0) stock  from pms_product_site_sku

group by  product_site_id) sku on s.id =sku.product_site_id

WHERE 1=1 and s.tenant_id ='f652e66ac0714627aa66c58471455680'

and s.site_id = '30251497' and s.status=1