优化

索引优化

索引建立优化

并不是所有的列都适合建立索引，索引越多也意味着维护索引的成本越高，对插入和更新造成影响，一个表的索引数最好不要超过6个

• 索引不会包含有NULL值的列

  只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL

• 使用短索引

  对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

• 在高区分度的列上建立索引

  当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，列值male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用，一般建议同值的数据不要超过表的百分之15

• 尽量使用数字型字段

  因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了

• 在不经常更新的列建立索引

  防止b+树频繁的变动，会影响更新和插入速度

查询优化

对查询进行优化，应尽量避免全表扫描，避免创建临时表

全表扫描的查询

• 使用null作为判断条件

  只要列中包含有null值都将不会被包含在索引中，组合索引中只要有一列含有null值，那么这一列对于此组合索引就是无效的。所以使用null作为条件会导致全表扫描

• 使用 != 或 <> ，not in

  会导致全表扫描，而且会导致无法查询出为null的列 ps: or查询在8.0版本不会全表扫描

• 强制类型转换

  如果一个列的数据类型是varchar，使用where查询时条件为数字类型，就会产生类型转换，不会使用到索引 ps: 在mysql8中发现使用到了索引

• 在对where字段进行函数，算术运算，表达式

  如： select id from t where num/2=100应改为:select id from t where num=100*2 select id from t where substring(name,1,3)=’abc’

业务优化

• select中只查询需要的列

  1. 如果索引中有要查询的列会使用索引的数据
  2. 使用* 会增加代码中的变化点，如返回给前端的字段发生变化

• 避免大事务操作

• 尽量避免临时表
• 控制临时表大小

  拆分sql

高并发优化

主从读写分离

从数据库负责读取，主数据库负责写入，通过mysql binlog日志的方式同步两个数据库

优缺点：

减小数据库压力

在网络不好时会造成数据不同步，但可以通过关键数据查主表的方式避免

水平拆分

垂直拆分

操作方式

相关程序

• proxy

  通过代理的方式进行数据操作，应用程序请求代理程序发出操作，由代理程序判断要操作的库和表

优缺点：

使用方便，不必关心分库分表规则，只要可以连接到代理程序即可

由于代理的存在需要进行更多次的网络请求，所以速度较慢

• jdbc

  通过在程序中写控制逻辑，来操作某个库和表

优缺点

由于直连数据库，所以速度较快

程序内需要写分库分表规则，所以对使用者要求高

大数据量

Explain分析

Type和Extra详解(精)