核心思路 合理创建索引 使用索引避免全表扫描 使用explain sql语句来获取mysql执行计划,分析性能瓶颈 只有当where条件子句中用到索引字段才会触发使用索引 建立索引的标准: 1.表记录不足5000条,不建立索引[经验之谈] 2.频繁作为where子句的字段[实际需求] 3.唯一性比例要比较高 distinct(字段)/字段 >=80% 一.查询优化 1.避免全表扫描,首先应考虑在where和orderby字段上建立索引 2.避免对where子句中的字段使用null值判断,这样会导致引擎放弃索引而进行全表扫描,因此建立字段时,默认填充最好设置not null 约束并按需求判断是否指定默认值[一般字符类型默认空字符串,数值类型默认0,时间默认当前时间]. not null 和 default是两种不同情况的约束, not null 约束了在insert和update时,sql语句必须指定字段的值,而如果加了default,则会取消 not null 对insert的字段必填约束,转而使用默认值填充. 3.尽量避免where子句中字段判断使用 != 或 <> 操作符,这样会导致引擎放弃索引而进行全表扫描 4,应避免在where子句中使用 or 来链接条件,因为其中只要有一个字段没有使用索引,将导致引擎放弃索引而进行全表扫描. 如 select id from t where num=10 or Name = 'admin' 可以改为 select id from t where num = 10 union all select id from t where Name = 'admin' "union"操作符是合并两个多多个select的查询结果集.注意连接的两个或多个查询语句 查询字段的名称和顺序要一致,数据类型要类似. 其中 union all 是列出所有查询结果且不排重 select1 = 1 2 3 union all select2 = 3 4 5 结果为 1 2 3 3 4 5 其中 union 是列出所有查询结果且排重 select1 = 1 2 3 union all select2 = 3 4 5 结果为 1 2 3 4 5 只会列出所有符合的结果且首次出现的结果,后续不再出现.同表数据可以用此特性合并去重,但异表数据此特性会导致首次出现的数据外的其他表相同字段数据丢失 5.in 和 not in 要慎用,否则导致全表扫描 对于连续的范围,能用between就不要用in 多表查询取字段交集时,用exists替代in 例子: select num from a where num in(select num from b) 策略: select num from a where exists(select num from b where num=a.num) 6.模糊查询匹配的值起始部分使用了模糊%导致全表扫描 例子: select id from t where name like ‘%abc%’ 策略: 若要提高效率，可以考虑全文检索 7.避免对where子句的字段进行表达式操作,这样会导致引擎放弃索引而进行全表扫描 例子: select id from t where num/2 = 1000 策略: select id from t where num = 100*2 例子: select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id 策略: select id from t where name like 'abc%' 8.使用到符合索引,必须保证该复合索引的第一个字段作为条件才能保证索引被正常使用,否则不会使用,并应尽可能的让字段条件顺序与组合索引顺序一致.任意N个字段的组合索引,可以看似为N个索引组合的集合,每个组合分别为完全顺序的索引字段,依次从尾部去掉一个索引字段,因为所有的组合都保留了第一顺位的字段,也就是最左边的字段,也叫最左原则,使用到索引组合中,索引字段数越多越有效率. 例子: 符合索引 name age city ,相当于建立了 name age city name age name 这三个索引集合,只要条件字段的顺序与其一致,就可以用到,明显无论那种条件顺序都是第一个字段在最前面. 9.索引可以增加select效率,但是会降低insert和update的效率,建议一个表索引不要超过6个,索引的建立最好是查询频繁的字段上.索引除了可以增加查询效率还可以减少排序成本,因为索引本身是有序值相当于字典的页码,插入数据时本身就会根据索引字段的排序特效对数据排序,当排序字段刚好是查询的索引字段,就不用再排了. 10.连表查询中,mysql优化程序会用小表驱动大表,此时小表是驱动表,大表是被驱动表.如果有排序和条件字段,优先使用驱动表的字段作为条件,可以减少耗时.