分页优化

limit 100000,10 :
去0- 1000010，条，前面的会丢掉，越往后效率越低

优化：where中过滤
比较有限，必须按id自增的，且连续

非主键字段排序的分页查询

一定会走 useing filesort
image.png

优化：
让排序返回的字段尽可能少，先排序去取主键，再根据主键查出记录
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

Join关联查询优化

关联常见有两种算法

1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法

关联的表示有索引的

  1. 先从小表（驱动表） 找出一条数据
  2. 根据找出的数据，关联大表找出数据，走索引
  3. 重复1,2,， 合同所有2的结果，返回客户端

扫描次数是 小表数 * 2

2、 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

关联的字段无法走索引

  1. 还是把小表的数据都加载进join buffer(默认256k), 分段放，第一取出一部分，比较，清空，再取另一部分比较
  2. 然后从大表逐一取数据，跟join buffer的数据比较
  3. 符合的返回

扫描的次数是 大表行数 + 小表行数

inner join 时，排在前面的表并不一定就是驱动表
left join时，左表是驱动表，右表是被驱动表
right join时，右表时驱动表，左表是被驱动表
实际上join，mysql 会选小表作为驱动表，大表作为被驱动表
如果Extra未出现 using join buffer，表明Join算是NLJ

对于关联sql的优化

  • 关联字段加索引，尽量走NLJ算法，走索引的尽量都加索引
  • 小表驱动大表：如果知道那个是小表可以强制驱动表 straigh_join

straight_join

强制左侧表作为驱动表
例子：
select * from t2 straight_join t1 on t2.a = t1.a;
ｔ２：就是驱动表
只是用inner join，不适用left join ，right join
慎用，机器的优化比人靠谱

in和exsits优化

原则：小表驱动大表，

  1. select * from A where id in (select id from B)  
  2. #等价于：
  3. 　　for(select id from B){
  4.       select * from A where A.id = B.id
  5.     }

B 结果 小于 A 时in 优于 exists
exists
当A表数据小于B表的数据时，exists 优于in
根据A结果，

  1. select * from A where exists (select 1 from B where B.id = A.id)
  2. #等价于:
  3.     for(select * from A){
  4.       select * from B where B.id = A.id
  5.     }
  7. #A表与B表的ID字段应建立索引

count(*)查询优化

  1. -- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
  2. mysql> set global query_cache_size=0;
  3. mysql> set global query_cache_type=0;
  5. mysql> EXPLAIN select count(1) from employees;
  6. mysql> EXPLAIN select count(id) from employees;
  7. mysql> EXPLAIN select count(name) from employees;
  8. mysql> EXPLAIN select count(*) from employees;

这四个sql执行效率应该差不多
字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)
//字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，
所以count(字段)>count(主键 id)
字段无索引：count(*)≈count(1)>count(主键 id)>count(字段)
//字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，
所以count(主键 id)>count(字段)
count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计，就用常量1做统计，
count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。
count(*) 是例外，mysql并不会把全部字段取出来，不取值，按行累加，效率很高，

为什么对于count(id)，mysql最终选择辅助索引而不是主键聚集索引？因为二级索引相对主键索引存储数据更少，检索性能应该更高，mysql内部做了点优化(应该是在5.7版本才优化)
count(字段） 统计不到null值

常见优化方法

1、查询mysql自己维护的总行数
myisam：会把总行数存储在磁盘上，查询时不会计算
innodb:不会存储总行数 因为MVCC机制，查询count需要实时计算

2：show table status

show table status like “employees” ; 可以获取行数效率高

image.png

3：总数据缓存到redisz总

4： 增加一个统计行数的表

Alibaba Mysql 规范

类型选择的规范：
1：确定合适的大类型：数字，字符串，时间，二进制
２：确定具体的类型：有无符号，取值范围，变长，定长
尽可能选用更小的数据类型，
尽量字段定义为ｎｏｔ　ｎｕｌｌ

一： 数值类型

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128, 127) (0, 255) 小整数值
SMALLINT 2 字节 (-32 768, 32 767) (0, 65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608, 8 388 607) (0, 16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648, 2 147 483 647) (0, 4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808, 9 223 372 036 854 775 807) (0, 18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38, 1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0, (1.175 494 351 E-38, 3.402 823 466 E+38) 单精度
浮点数值
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308, 2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) 0, (2.225 073 858 507 201 4 E-308, 1.797 693 134 862 315 7 E+308) 双精度
浮点数值
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

优化建议：
1：整数 如果无负数情况，建议定为 unsigned 无符号类型，容量可扩大一倍
2：建议使用tinyint 代替 enum ，biteenum set
3：避免使用整数的显示宽度，
int(10) : 并不是数值最大是10位数字，而是显示的数字宽度，会补0 的
当需要在查询结果前填充0时，命令中加上ZEROFILL就可以实现
`id TINYINT(2) UNSIGNED ZEROFILL `
字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，
只会在使用ZEROFILL时有用，让查询结果前填充0
4：建议用整型运算，和存储实数
5：decimal： 有限考虑，decimal(20，6 )，最长未20位，其中小数部分6位
6：整数通常是最佳的数据累

2：时间和日期

类型 大小
(字节)		 范围 格式 用途
DATE 3 1000-01-01 到 9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’ 到 ‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901 到 2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00 到 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 4 1970-01-01 00:00:00 到 2038-01-19 03:14:07 YYYYMMDDhhmmss 混合日期和时间值，时间戳

优化建议：
1：最小时间粒度为秒
2：建议使用Date类型 默认格式yyyy-MM-DD
3：不建议用字符串存
4：timestamp，和 datetime 时： 可以用current_timestamp作为默认（5.6版本+ ）
5：timestamp 是UTC时间戳，与时区有关
6：datetime存的是YYYYMMDD HH:MM:SS的整数，与时区无关
7：建议使用timestamp
8：

3： 字符串

类型 大小 用途
CHAR 0-255字节 定长字符串，char(n)当插入的字符数不足n时(n代表字符数)，插入空格进行补充保存。在进行检索时，尾部的空格会被去掉。
VARCHAR 0-65535 字节 变长字符串，varchar(n)中的n代表最大字符数，插入的字符数不足n时不会补充空格
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

优化建议：
1：字符串长度相差较大用varchar, 较短且固定用 char
2:：固定长度，不超过255，用char,
3：少用blob，text 单独存储id关联
4：blob 存储二进制字符串，与字符集无关，text存储非二进制文件，与字符集有关
5：blob和text 都不能有默认值