Mysql Sql优化注意的几点问题

Mysql Sql优化注意的几点问题

最近因业务需求需要对查询几个数据量较大的表，以下为sql语句：

SELECT
  a.organSign organSign,
  c.binding_name organSignName,
  b.create_time createTime,
  b.bill_no pref,
  b.supplier_name supplierName,
  d.standard_library_id standardLibraryId,
  a.product_code productCode,
  a.product_name productName,
  a.specifications specifications,
  a.packing_unit packingUnit,
  a.manufacturer manufacturer,
  a.product_amount productAmount,
  a.product_tax_price productTaxPrice,
  a.product_tax_price_sum productTaxPriceSum,
  a.product_batch_no productBatchNo,
  a.product_date productDate,
  a.product_expiry_date productExpiryDate,
  a.approval_number approvalNumber
FROM
  saas_purchase_bill_detail a
inner JOIN saas_purchase_bill_info b ON a.purchase_bill_id = b.origin_id AND a.organSign = b.organSign
LEFT JOIN saas_account_binding c ON c.organSign = a.organSign
LEFT JOIN saas_product_baseinfo d ON d.pref = a.product_code AND d.organSign = a.organSign
WHERE
  b.bill_type = '04'

几个表的数据量分别为：

saas_purchase_bill_detail：23W
saas_purchase_bill_info ：4W
saas_product_baseinfo ：273W

为优化前，sql直接查死了，优化后不到1s，以下为总结的几点优化注意事项：

• 通过explain查看sql执行计划
  

• 可针对表连接时，能用inner join尽量用 inner join

• 对on后的连接字段和where字段添加索引，对于单列和组合索引的取舍，优化时尽量添加组合索引

• on 后的连接字段的类型和字段长度尽量保持一致，字段类型不一致时，有时会导致索引失效

• where语句中的 bill_type字段类型为: char（2） 同事在之前做时是这样写的 b.bill_type = 04，通过sql运行计划，发现我们的sql基本一致但是我的走索引，他的却不走。mysql 在这个过程中自动将04转成‘04’影响了查询效率，具体解释参照下文：隐式类型转换

• 用IN来替换OR

• LIKE双百分号无法使用到索引

SELECT * FROM t WHERE name LIKE ‘%de%’;

只有SELECT * FROM t WHERE name LIKE ‘de%’;支持索引

• 不使用子查询，子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。在MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为
  SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;
  但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，故生产环境应避免使用子查询

SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’333’);