SQL优化

1.优化

实现一个查询，可以写出很多种查询语句，不同的语句，根据你选择的引擎、表中数据的分布情况、索引情况、数据库优化策略、查询中的锁策略等因素，最终查询的效率相差很大；
优化要从整体去考虑，有时你优化一条语句后，其它查询反而效率被降低了，所以要取一个平衡点；
即使精通mysql的话，除了纯技术面优化，还要根据业务面去优化sql语句，这样才能达到最优效果；

1.1 优化sql和索引；

1.2 加缓存，memcached,redis；

1.3 主从复制或主主复制，读写分离

可以在应用层做，效率高，也可以用三方工具，第三方工具推荐360的atlas,其它的要么效率不高，要么没人维护；

1.4 mysql自带分区表

对应用是透明的，无需更改代码,但是sql语句是需要针对分区表做优化的，sql条件中要带上分区条件的列，从而使查
询定位到少量的分区上，否则就会扫描全部分区

1.5 垂直拆分

其实就是根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

1.6 水平切分

针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key,为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，
sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

1.7 引擎优化

myisam读的效果好，写的效率差，这和它数据存储格式，索引的指针和锁的策略有关的，它的数据是顺序存储的（innodb数据存储方式是聚簇索引），他的索引btree上的节点是一个指向数据物理位置的指针，所以查找起来很快，（innodb索引节点存的则是数据的主键，所以需要根据主键二次查找）
myisam锁是表锁，只有读读之间是并发的，写写之间和读写之间（读和插入之间是可以并发的，去设置concurrent_insert参数，定期执行表优化操作，更新操作就没有办法了）是
串行的，所以写起来慢，并且默认的写优先级比读优先级高，高到写操作来了后，可以马上插入到读操作前面去，如果批量写，会导致读请求饿死，所以要设置读写优先级或设置多少写操作后执行读操作的策略;myisam不要使用查询时间太长的sql，如果策略使用不当，也会导致写饿死，所以尽量去拆分查询效率低的sql

innodb一般都是行锁，这个一般指的是sql用到索引的时候，行锁是加在索引上的，不是加在数据记录上的，如果sql没有用到索引，仍然会锁定表,mysql的读写之间是可以并发的，普通的select是不需要锁的，当查询的记录遇到锁时，用的是一致性的非锁定快照读，也就是根据数据库隔离级别策略，会去读被锁定行的快照，其它更新或加锁读语句用的是当前读，读取原始行；因为普通读与写不冲突，所以innodb不会出现读写饿死的情况，又因为在使用索引的时候用的是行锁，锁的粒度小，竞争相同锁的情况就少，就增加了并发处理，所以并发读写的效率还是很优秀的，问题在于索引查询后的根据主键的二次查找导致效率低；

ps:为什innodb的索引叶子节点存的是主键而不是像mysism一样存数据的物理地址指针吗？如果存的是物理地址指针不就不需要二次查找了吗，这也是我开始的疑惑，根据mysism和innodb数据存储方式的差异去想
所以innodb为了避免二次查找可以使用索引覆盖技术，无法使用索引覆盖的，再延伸一下就是基于索引覆盖实现延迟关联；
不知道什么是索引覆盖的，建议你无论如何都要弄清楚它是怎么回事！

2.explain命令

2.1 id

id有一组数字组成。表示一个查询中各个子查询的执行顺序;

• id相同执行顺序由上至下。
• id不同，id值越大优先级越高，越先被执行。
• id为null时表示一个结果集，不需要使用它查询，常出现在包含union等查询语句中。

2.2 select_type

每个子查询的查询类型，一些常见的查询类型。

• SIMPLE 不包含任何子查询 或union等查询
• PRIMARY 包含子查询 外层查询就显示为 PRIMARY
• SUBQUER Y 在select或 where字句中包含的查询
• DERIVED from字句中包含的查询
• UNION 出现在 union后的查询语句中
• UNION RESULT 从UNION 中获取结果集，例如上文的

2.3 type

访问类型

• ALL 扫描全表数据
• index 遍历索引
• range 索引范围查找
• ref 使用非唯一索引查找数据
• eq_ref 在join查询中使用PRIMARY KEY or UNIQUE NOT NULL索引关联。

2.4 possible_keys

可能使用的索引，注意不一定会使用。查询涉及到的字段上若存在索引，则该索引将被
列出来。当该列为 NULL时就要考虑当前的SQL是否需要优化了。

2.5 key

显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。
查询中若使用了覆盖索引，则该索引仅出现在key列表中

2.6 extra

1. Using index 使用覆盖索引
2. Using where 使用了用where子句来过滤结果集
3. Using filesort 使用文件排序，使用非索引列进行排序时出现，非常消耗性能，尽量优化。
4. Using temporary 使用了临时表 sql优化的目标可以参考阿里开发手册
   【推荐】SQL性能优化的目标：至少要达到 range 级别，要求是ref级别，如果可以是consts 好。
   说明： 1） consts 单表中 多只有一个匹配行（主键或者唯一索引），在优化阶段即可读取到数据。
   2） ref 指的是使用普通的索引（normal index）。
   3） range 对索引进行范围检索。

3.超大分页怎么处理？

超大的分页一般从两个方向上来解决.
数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age

20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然
后基本上全部丢弃,只取10条当然比较慢. 当时我们可以修改为select * from table where id in
(select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由
于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select

• from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种, 但是核心思想
  都一样,就是减少load的数据.
  从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允
  许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.
  解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.
  在阿里巴巴《Java开发手册》中,对超大分页的解决办法是类似于上面提到的第一种.
  【推荐】利用延迟关联或者子查询优化超多分页场景。 说明：MySQL并不是跳过offset
  行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的
  低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。 正例：先快速定位需要获取
  的id段，然后再关联：
  SELECT a.* FROM 表1 a,(select id from 表1 where 条件 LIMIT 100000,20) b w here
  a.id=b.id

4.慢日志

用于记录执行时间超过某个临界值的SQL日志，用于快速定位慢查询，为我们的优化做参考。

• 开启慢查询日志
  配置项：slow_query_log 可以使用show variables like ‘slov_query_log’查看是否开启，如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，它会在datadir下产生一个xxx-slow.log的文件。
• 设置临界时间
  配置项：long_query_time 查看：show VARIABLES like 'long_query_time'，单位秒设置：set
  long_query_time=0.5
  实操时应该从长时间设置到短的时间，即将 慢的SQL优化掉
  查看日志，一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中

4.1 优化

是查询
条件没有命中索引？是 load了不需要的数据列？还是数据量太大？
首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

5.数据库优化

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

• 将字段很多的表分解成多个表对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。
  因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

• 增加中间表对于需要经常联合查询的表，可以建立中间表以提高查询效率。
  通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

• 增加冗余字段设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
  表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。
  冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题

6.具体方法

• 开启查询缓存：类似curdate函数不会开启，需要使用变量记录
• join时：俩个字段类型相同，字符集相同
• 不要order by rand()
• 使用enum而不是varchar: 如性别
• 拆分大的delete和insert
• 用PreparedStatement， 一般来说比Statement性能高
• 外键约束会影响插入和删除性能，如果程序能够保证数据的完整性，那在设计数据库时就去掉外键。
• 大字段text,blob:拆成一个表，这样查询其他字段效率高