Sql调优汇总

Sql优化步骤

1. 优化sql语句
2. 适当添加索引
3. 通过explain查看索引是否生效，以及各条语句的执行性能数据
4. 及时调整索引不生效的情况
5. 数据量太多就要分表分库（单表超过500万就要分表）

Sql语句优化

1. 减少查询字段，不使用*
   1. 尽可能触发覆盖索引，减少回表查询的性能消耗
   2. 在进行大范围数据查询的情况下，每减少一个字段对性能都有提升。
2. 减少子查询
3. 减少表的关联，尽量去做单表的操作
   1.  例如：订单表和订单详情表，先查出10条订单，再根据10个订单id查询一次订单详情表
   2. 利用mybatis动态sql优化join关联的表，无搜索字段时可以不做关联
   3. 阿里开发手册上归档join不能超过3张表
   4. 关联字段加索引，让mysql做join操作时尽量选择NLJ算法
   5. 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间
4. count(*)查询优化
   1. 尽量使用count(*)，性能较好
   2. 将总数维护到Redis里。插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难 保证表操作和redis操作的事务一致性
   3. 增加数据库计数表。插入或删除表数据行的时候同时维护计数表，让他们在同一个事务里操作（只能针对全表，带条件的查询就不行了）
5. 合理的添加索引
   1. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，索引不能超过6个
   2. 注意索引失效的场景，使用explain查看索引是否被使用。
   3. 多使用组合索引，将常用查询条件字段放在组合索引内。
   4. 关联查询的ON条件字段尽量使用索引，where后面的字段上也可增加索引。
6. 建表字段不能太多，不能超过30个，超过了就要新建一张表
7. 其他：增加中间表、冗余字段、业务层面优化等

pagehelper分页插件的问题

1、这个分页插件每次查询总数都是拼接count在前面，数据量大的时候查询效率低

2、可以覆盖pagehelper的count方法，自己写查询总条数的sql

3、查询条数有时是不需要关联那么多表的，因为有些关联有些表只是为了获取它的某些字段，那count的时候又不需要字段，所以可以去掉一些关联

Order by与Group by优化

1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

        1) order by语句使用索引最左前列。

        2) 使用where子句与order by子句条件列组合满足索引最左前列。

3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。

索引失效的情况

1、使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，索引才会生效（like '%文'--索引不起作用）

2、使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效

3、使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则索引不生效

4、尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描

5、尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描

6、并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会去利用索引

7、字符串不加单引号索引失效

MySQL数据类型选择

数值类型

1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
2. 建议使用TINYINT代替ENUM、BITENUM、SET。
3. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用 INT。
4.  DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意 长度设置。
5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

日期和时间

        1. MySQL能存储的最小时间粒度为秒。

        2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。

        3. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。

        4. 当数据格式为TIMESTAMP和DATETIME时，可以CURRENT_TIMESTAMP作为默认（MySQL5.6以后）， MySQL会自动返回记录插入的确切时间。

        5. TIMESTAMP是UTC时间戳，与时区相关。

        6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。

        7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般 会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。

        8. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐 它

字符串

        1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。

        2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些 要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计 算的准确性和完整性。

        3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。

        4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。

        5. BLOB和TEXT都不能有默认值。