mysql使用心得,如何在增删改查的基础上使sql运行的更快

• 前提:使用explain分析sql语句
  例:分析左右连接查询的sql
  使用join 默认是左连接,
  左边使用的是all右使用的是eq_ref

使用left join 左边使用的是all右使用的是eq_ref,换一种更容易理解的说法:left 左表为驱动表(适合左表较小的情况)

使用right join 右边使用的是all左边使用的是all,为什么左边一定是全表扫呢? 存疑

左连接,左边表里的数据全查出,右边未找到对应的显示为null

---

• 可以达到优化效果的写法和原因

mysql中:
插入数据时,对于有默认值的字段只在插入值和默认值不同时才显式插入,避免插入时的参数校验

避免使用sql本身的自增,因为会涉及主键冲突,需要额外的保证不会冲突,影响插入效率

大量数据入库的优化: 如果唯一约束确定唯一,或者可以容忍插入后再去除重复项,唯一约束在load file前的关闭和load后的开启

insert优化: 批量插入减少连接消耗,多客户端插入设置即时执行?(在内存中执行,而无需等待其他客户端读写完成)

order by 优化: 使用index(表现为explain中extra为index而不是filesort),where条件和order by条件保持一致,多个条件order by的写法和desc或者asc保持一致(达到重用的目的)

group by 优化: 如果不需要排序操作,可以取消默认的排序操作(order by null)

filesort 优化: 修改sort_buffer_size参数使排序尽量在内存中完成

嵌套查询优化: 使用join代替(不需要创建临时表?逻辑更简洁)

or 优化: or条件都使用index(本质是按or条件查询后做union),如何处理联合索引(当or一边的条件只使用了符合索引的一部分则=没用索引;如果or一边使用的是全部的符合索引呢?)

分页优化: 使用index分页后 再回表查询

like 查询时使用%,尽量保证左边为确定的字符如 ‘nam%’,减少正则匹配,正则用的不好反而是一大隐患
like优化,牺牲空间换取时间 使用全文索引使模糊查询更快

慎用 not in 效率和易读性,not in <not exists <join…

between 和in:当范围为连续的值between优于in

小表驱动大表:
① in和exists:exist外表为驱动表(执行顺序为外表先解析,适合外表小嵌套查询的内表大的情况),in为in()中的查询语句先执行,适合嵌套查询的表小的情况
例:select x from A in(select x from B) 当B小于A时优于exists
② inner join 优于left/right join inner默认取小表驱动

where中慎用 null判断,计算 引擎会放弃使用索引(ps:is null 和== null 不是一回事)