sql优化

建表需要注意的：能用int的不要用varchar,如果非负的话用UNSIGNED int,Ip地址用int存(使用inet_aton函数转换)

内存消耗巨大，引发磁盘操作（最严重）：这是 VARCHAR 过长最致命的性能杀手。在执行 ORDER BY、GROUP BY 等需要创建内存临时表的操作时，MySQL 会按照字段定义的最大长度来分配内存，而非实际数据长度。

• 举例：在 utf8mb4 字符集下，一个定义为 VARCHAR(255) 的字段在内存中会固定占用 255 * 4 = 1020 字节。处理一万行数据就需要约 9.7 MB 内存；而如果定义为 VARCHAR(50)，仅需约 1.9 MB。当内存不足，MySQL 就会使用磁盘临时表，性能会呈指数级下降

• varchar列上建索引也是按照varchar定义的最大值存储空间计算一个索引页能放几个索引,varchar值定义的太大会导致b+数层高过高,磁盘io大.

根据索引B+树高不超过三推断,bigint类型索引数量不超过2200万行,int类型索引不超过4000万行,超过这个数量单表查询性能会下降.

建索引要考虑的：多表查询优先考虑建在on连接条件上（注意on的两边字段类型是否一样，编码格式是否一样），单表查询建在where条件，
建复合索引还是单列索引，复合索引虽然效率高但是如果查询条件不满足左前缀原则会不生效

使用索引覆盖可以避免回表查询,速度更快,如果条件满足尽量使用索引覆盖.
索引用没用，用的哪个索引，如果默认索引不合适,手动指定索引; use index(建议用哪个索引), ignore index(指定忽略的索引), forece index (强制指定索引)

in 和exist：in需要对子表全表扫描，exist是把外面数据一条条带进来，子表数据量大时往往exist快
子查询和join,子查询就是where条件里某个字段in(select xxx),in改成关联查询时要注意可能出现主表一条数据关联出子表多条数据的情况，这种问题在in中不会出现。
多字段用like模糊查询时用union all 组合每个查询条件，而不是用or连接多个like模糊查询,如果还是慢就用es代替模糊查询，因为模糊查询本来就不是mysql的强项。
多表关联可以拉宽表但是实时性不高，total慢可以换myisam引擎或者跑常量表。

深度分页查询慢时可以先用id>xxx后跳过前面部分数据再用limit分页，弊端是这样不能直接跳转到第n页，只能根据上一条数据的id往后查,并且id要自增,uuid不能用。

索引失效情况: 

1 索引列上有函数,比如substring截取

2 数字类型字段在查询时加单引号和不加都不影响索引的使用，字符串类型字段如果存了纯数字，查询时如果不加引号，不会使用索引。（代码中用Mybatis不会出现这种情况，窗口里手写才会）

pkid是bigint,加了引号依旧走索引

empid是varchar类型有索引，不加单引号没走索引 

 

3 like模糊查询前面加%

一般情况下一张表只会用一个索引,如果存在or,union这种可能会用到多个索引, 执行计划的type显示Index Merge（索引合并）.出现索引合并并不是好现象，如果where

条件时 a and b出现索引合并，最好建立a和b的联合索引，如果 a or b出现索引合并，最好用union代替or.

 开启慢查询日志:　　在mysql配置文件(/etc/my.cnf)中配置 : show_query_log=1(开启慢查询功能), long_query_log=2(设置超过2秒为慢查询,时间可以根据需要改)

慢查询日志文件在/var/lib/mysql/localhost-slow.log

 

多表查询时如果某张表需要的字段都不是查询条件，那就不要join这张表，只join跟查询条件相关的表，等查出某页数据后再给这一页数据补上其他表的数据。