SQL优化从我自己的经验来说的，我自己总结了几个方面吧，主要从以下几个方面来着手：
- explain
- profiling
- sql使用技巧
explain
一般比较关注type,key,rows,extra几列
-
type: 描述了表之间是如何进行连接的
-
key: 真正使用的索引
-
rows: 预估扫描的行数，不是预估返回的行数
-
extra: 额外的信息
-
explain输出行的id值越大，越先执行。id如果相同，从上往下顺序执行。
扩展阅读： https://blog.csdn.net/horses/article/details/106905110
extra列实战
- using index: 使用了覆盖索引
- using where: 表示mysql服务器将在存储引擎检索行后再进行过滤
- using filesort: order的列没有索引或者没有用到order列的索引 order by 没有用到索引的情况有：
- order by的列就没有建立相关索引
- order by的列存在相关索引，但和where条件中使用的索引不同
- using index condition: 使用的是辅助索引，但进行了回表操作（也就是说还走了一次主键索引）
- null: 使用的是主键索引，但筛选的数据不全在索引中
type列实战
-
ALL 全表扫描
-
index 索引全扫描
-
range 索引范围扫描，常用语<,<=,>=,between等操作
-
ref 使用了非唯一索引或唯一索引的左侧部分，返回可能多余一条的数据
-
eq_ref 类似ref，使用到了聚集索引或唯一非空索引的全部组成部分，使用了 = 或者 join 来关联其他表的某列
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1; -
const/system 只有使用到了聚集索引或唯一索引的全部组成部分才会是const，因此结果集最多只会有一行数据
SELECT * FROM tbl_name WHERE primary_key=1; SELECT * FROM tbl_name WHERE unique_key=1; SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2; -
null MySQL不访问任何表或索引，直接返回结果
profiling
这种方式和使用performance_schema数据库是等效的。
使用performance_schema数据库的具体步骤参考：https://dev.mysql.com/doc/refman/5.7/en/performance-schema-query-profiling.html
怎么玩
-
执行set profiling = 1 开启该功能
可用通过 show variables like ‘profiling’查看是否已经开启。
这个变量的作用范围既可以是整个系统，也可以是当前会话，具体参考：https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html
-
执行sql语句
-
执行set profiling = 0 关闭该功能
-
执行show profiles找到刚才执行的sql语句的query_id.
一般只会展示15条记录，可通过参数 profiling_history_size 修改
-
执行show profile all for query query_id 来查看耗时清单
具体显示哪些列：
ALL：显示所有的开销信息 BLOCK IO：显示块IO相关开销 CONTEXT SWITCHES：上下文切换相关开销 CPU：显示CPU相关开销 IPC：显示发送和接受相关开销 MEMORY：显示内存相关开销 PAGE FAULTS：显示页面错误相关开销 SOURCE：显示和Source_function, Source_file,Source_line相关的相关开销 SWAPS：显示交换次数相关开销
注意
如果在其生命周期阶段出现如下的情况的就要重视了：
-
converting HEAP to MyISAM 表示正在将基于内存的临时表转储到基于磁盘的临时表上
MySQL会使用Memory存储引擎的表来作为临时表来存放查询产生的中间结果，如果中间结果集大于了memory存储引擎表的容量（tmp_table_size参数控制），或者中间结果包括了memory存储引擎表不支持的数据类型，mysql又将此中间结果转换为MyISAM存储引擎表而存放在磁盘上。而MyISAM表是不缓存数据文件的，因此产生这样的临时表对于查询的性能会有所降低，所以在查询时尽量避免产生临时表。
-
Creating tmp table 创建了临时表，先拷贝到临时表，用完后再删除
-
Copying to tmp table on disk 把内存中的临时表复制到磁盘中，这个很耗性能
-
Locked 这个就是指在等待锁的意思
查看全局创建的内存临时表数量
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';
查看全局创建的磁盘临时表数量
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';
SQL使用技巧
- 是否有索引
- 覆盖索引
- 可以避免回表操作
- 避免隐式的类型转换
- 连表的两个字段必须保持相同的数据类型和字符集
- 有可能不能使用索引，造成全表扫描
- not exist与not in(优先选择not exist)
- not in使用不当还有可能返回错误结果。 如果not in子查询包含了null，将返回空数据。 如果not in子查询返回空数据，将匹配所有行数据
- not in (1,null) : 返回空数据
- not in (select 1 where 1=0) : 返回所有数据
- 索引字段进行表达式或函数计算
- 会造成全表扫描
- 不满足左匹配原则，比如模糊查询
- 会造成全表扫描
- !=（不等于）
- 会造成全表扫描
- 索引字段尽量建立在区分度高的列上
- 比如在is_del这样的字段上加索引意义就不是很大
- 长字段单独一张表
- 从b+数的结构上解释，一页的空间是16kb，存放的数据占用空间越小那么一页存放的数据越多，整棵树就更加矮胖，IO次数就越少，效率越高
- in与exist（各有适用场景：in和exist的选择
- 小表驱动大表
- 时间排序转换为id排序
- 分页技巧（断点分页）
- 表设计时，满足要求的情况下尽量选择占用空间小的数据类型
- 合理适用子查询，比如in与exist，有时比连表效率更高
- sql语句尽量简单（这个我的使用习惯），一些操作可以让应用层完成
- 聚合操作避免使用ICP（也就是聚合操作的extra列不要出现using index condition）
- or
- null
- 避免使用not in,而用not exists 替代
ICP(Index Condition PushDown)
https://www.jb51.net/article/222444.htm
MySQL执行计划
https://blog.csdn.net/horses/article/details/106905110
小表驱动大表
何为小表驱动大表
用小的数据集驱动大的数据集
如何区分驱动表与被驱动表
很简单：explain 出来的结果中，第一行显示的表即为驱动表
join的时候谁是驱动表
- A left join B，则左表是驱动表，即A是驱动表
- A right join B，则右表是驱动表，即B是驱动表
- A inner join B，MySQL会选择比较小的表为驱动表
Exsit与In
现在有两张表：
dept表：部门表，大概20条记录
emp表：员工表，大概1W条记录
需求：找出部门名称包含"技术"两个字的所有员工信息
方案一： select * from emp A where A.depNo in (select B.deptNo from dept B where B.name like ‘%技术%’);
方案二： select * from emp A where exists ( select 1 from dept B where B.deptNo = A.deptNo and B.name like ‘%技术%’ )
先说结论：
- 当A表的数据集大于B表时，IN比exists更优
- 当A表的数据集小于B表时，exists比IN更优
根据上面的结论，可知方案一比方案二时间更短，不信可自行实验
https://mp.weixin.qq.com/s/KpTG1WUlcP6cyk8Y9SMcyQ
https://blog.csdn.net/qq_38011415/article/details/103304189
哪些情况会出现全表扫描
- 数据量太小了，走索引可能成本还要高一些
- 筛选列没有索引
- 筛选列有索引，但是匹配的数据量接近于整张表数据
- 筛选列有索引，但是使用方式不对，比如对列进行计算、不满足最左匹配原则
- 有索引，但字符集不同
IN 和 EXIST语句
use index 与 force index的区别？
use index是告诉优化器可以使用这个索引，但最终优化器可以不用这个索引。
force index是强制让优化器使用这个索引
show index from 库名.表名
这条命令输出了一个关键值：cardinality，表示该索引中唯一值的数目。这个值和表总行数应该尽量接近1，否则可以考虑是否可以删除这个索引。但这个值不是实时更新的，只是一个大概值。如果要手动更新这个值，可以使用 analyze table
table_name来完成。
mysql是通过采样的方法来进行统计这个值的
优化器会根据这个值来判断是否使用这个索引，但这个值可能有时候不准确，因此可能影响sql执行性能，所以可以考虑在系统非高峰时间对核心表执行analyze table操作。
实际例子
- MySQL的大部分索引（PRIMARY KEY,UNIQUE,INDEX）都采用的是B-Tree数据结构
- 对于一些特定的数据类型，会采用R-Tree数据结构
- 内存表（MEMORY table）支持 哈希表 数据结构
- 组合索引，复合索引，联合索引，是一个东西
- 对多个列建立一个索引，如下的组合索引。
- 要根据业务场景来决定列的顺序。
- 是否能用到联合索引，要看where条件是否符合 最左前缀 原则。
- 组合索引的第一个字段必须出现在查询组句中，这个索引才会被用到。（和上一条一个意思）
create table test( a int, b int, c int, KEY a(a,b,c) ); 优: select * from test where a=10 and b>50 差: select * from test where a50 优: select * from test order by a 差: select * from test order by b 差: select * from test order by c 优: select * from test where a=10 order by a 优: select * from test where a=10 order by b 差: select * from test where a=10 order by c 优: select * from test where a>10 order by a 差: select * from test where a>10 order by b 差: select * from test where a>10 order by c 优: select * from test where a=10 and b=10 order by a 优: select * from test where a=10 and b=10 order by b 优: select * from test where a=10 and b=10 order by c 优: select * from test where a=10 and b=10 order by a 优: select * from test where a=10 and b>10 order by b 差: select * from test where a=10 and b>10 order by c create table pqs_temp ( a BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , b VARCHAR(50) DEFAULT '' not NULL , c VARCHAR(50) DEFAULT '' not NULL , d VARCHAR(50) DEFAULT '' not NULL , e VARCHAR(50) DEFAULT '' NOT NULL ); alter TABLE pqs_temp add index b_c_d (b(20),c,d); //索引包含的索引列的 部分信息 -- d 在索引中（没有用到覆盖索引） EXPLAIN select d from pqs_temp where b = '1'; mysql> EXPLAIN select d from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ alter TABLE pqs_temp drop INDEX b_c_d; ALTER TABLE pqs_temp add INDEX b_c_d (b,c,d); //索引包含的索引列的 全部信息 -- d 在索引中（用到覆盖索引）什么叫 覆盖索引，下面有讲到。 EXPLAIN select d from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+--------------------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 202 | const | 35 | Using where; Using index | +----+-------------+----------+------+---------------+-------+---------+-------+------+--------------------------+ alter TABLE pqs_temp drop INDEX b_c_d; ALTER TABLE pqs_temp add INDEX b_c_d (b(20),c,d); -- e 不在索引中 EXPLAIN select e from pqs_temp where b = '1'; mysql> EXPLAIN select e from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ alter TABLE pqs_temp drop INDEX b_c_d; ALTER TABLE pqs_temp add INDEX b_c_d (b,c,d); -- e 不在索引中 EXPLAIN select e from pqs_temp where b = '1'; mysql> EXPLAIN select e from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 202 | const | 1 | Using index condition | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ alter TABLE pqs_temp drop INDEX b_c_d; ALTER TABLE pqs_temp add INDEX b_c_d (b(20),c,d); -- * 中有不在索引中的列 EXPLAIN select * from pqs_temp where b = '1'; mysql> EXPLAIN select * from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ alter TABLE pqs_temp drop INDEX b_c_d; ALTER TABLE pqs_temp add INDEX b_c_d (b,c,d); -- * 中有不在索引中的列 EXPLAIN select * from pqs_temp where b = '1'; mysql> EXPLAIN select * from pqs_temp where b = '1'; +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 202 | const | 1 | Using index condition | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------+ -- order by 的列 不在索引中 EXPLAIN select * from pqs_temp where b = '1' ORDER BY e; mysql> EXPLAIN select * from pqs_temp where b = '1' ORDER BY e; +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where; Using filesort | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ -- order by 的列 在索引中,但order by 的时候不能用到索引,因为不满足 最左优先 原则 EXPLAIN select * from pqs_temp where b = '1' ORDER BY d; mysql> EXPLAIN select * from pqs_temp where b = '1' ORDER BY d; +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where; Using filesort | +----+-------------+----------+------+---------------+-------+---------+-------+------+-----------------------------+ -- order by 的列 在索引中,同时order by 也能用到索引.因为满足 最左优先 原则 EXPLAIN select * from pqs_temp where b = '1' ORDER BY c; mysql> EXPLAIN select * from pqs_temp where b = '1' ORDER BY c; +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ | 1 | SIMPLE | pqs_temp | ref | b_c_d | b_c_d | 82 | const | 35 | Using where | +----+-------------+----------+------+---------------+-------+---------+-------+------+-------------+ - 覆盖索引
- 如果一个select语句，select的字段都在这个sql使用的索引中，那么这种查询就走了覆盖索引。
- 可以说覆盖索引不是一种索引，而是索引的一种使用方法。
- 覆盖索引是针对具体的sql语句而言。同样的索引结构，不同的sql语句不一定就都走了 覆盖索引。
- 在EXPLAIN的Extra列出现Using Index提示时，就说明该select查询使用了覆盖索引。
- 要使用覆盖索引，至少满足下列条件
- 所用到的索引必须能覆盖相关索引列的全部数据
- select的字段必须是索引中的字段
- 索引注意事项
- 应尽量避免对索引列进行频繁更新，因为每次更新索引列，对应的索引也需要更新，这样就降低的更新性能
- 每张表建立的所以也不能太多，因为每次插入删除数据的时候，都相应的增加，删除索引，所以一定程度上就会降低性能。
-
添加索引
ALTER TABLE pqs_temp MODIFY COLUMN a BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT; ALTER TABLE pqs_temp ADD PRIMARY KEY (a); //唯一索引 ALTER TABLE pqs_temp ADD UNIQUE (b); //联合唯一索引 alter TABLE pqs_temp add UNIQUE f_g (f,g); //单列索引 ALTER TABLE pqs_temp ADD INDEX (c); //组合索引 alter TABLE pqs_temp add INDEX b_c_d (b,c,d); alter TABLE pqs_temp add INDEX b_c_d (b(20),c,d);//只将b列的前20个字符加入到索引的计算中 //全文索引 ALTER TABLE pqs_temp ADD FULLTEXT (d); //貌似不能创建 hash 结构的索引 ALTER TABLE pqs_temp add INDEX i USING HASH (i); //可以执行成功，但通过show index from pqs_temp 查看索引，仍然显示index_type为btree. +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | pqs_temp | 0 | PRIMARY | 1 | a | A | 2 | NULL | NULL | | BTREE | | | | pqs_temp | 1 | i | 1 | i | A | 1 | NULL | NULL | | BTREE | | | +----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -
查看表索引
show index from table_name; -
limit子句影响到了MySQL对索引的选择（这个真奇葩）
explain SELECT *, usermessage.createTime AS mes FROM usermessage WHERE ( ( usermessage.uid = 101838 ) AND ( usermessage.messageType = 'notice' OR usermessage.messageType = 'im' ) ) ORDER BY msgId limit 50 ; +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ | 1 | SIMPLE | usermessage | range | uid,messageType | uid | 8 | NULL | 12106562 | Using index condition; Using where | +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ explain SELECT *, usermessage.createTime AS mes FROM usermessage WHERE ( ( usermessage.uid = 101838 ) AND ( usermessage.messageType = 'notice' OR usermessage.messageType = 'im' ) ) ORDER BY msgId DESC limit 50; +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ | 1 | SIMPLE | usermessage | range | uid,messageType | uid | 8 | NULL | 11726940 | Using index condition; Using where | +----+-------------+-------------+-------+-----------------+------+---------+------+----------+------------------------------------+ explain SELECT *, usermessage.createTime AS mes FROM usermessage WHERE ( ( usermessage.uid = 101838 ) AND ( usermessage.messageType = 'notice' OR usermessage.messageType = 'im' ) ) ORDER BY msgId DESC; +----+-------------+-------------+-------+-----------------+-------------+---------+------+------+----------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+-------+-----------------+-------------+---------+------+------+----------------------------------------------------+ | 1 | SIMPLE | usermessage | range | uid,messageType | messageType | 50 | NULL | 402 | Using index condition; Using where; Using filesort | +----+-------------+-------------+-------+-----------------+-------------+---------+------+------+----------------------------------------------------+ -
union操作会去重
- MySQL在UNION去重时，只是比对两条数据的值是否相同，并不去判断值的数据类型是否一致，只要两条数据的每一列数值相等，就认为是重复的数据，UNION出来的结果只会一条.
- 指定sql结果集顺序：order by field(字段名,字段的值[逗号隔开])
SELECT
post.postId,
post.createTime,
post.commentNumber,
post.likeNumber,
post.postText,
post.cityName,
post.locationAddress,
postimg.bigUrl,
postimg.smallUrl,
postimg.width,
postimg.height
FROM post
LEFT JOIN postimg ON postimg.imgId = post.postImgId
WHERE post.postId in (1,2) ORDER BY field(post.postId, 1,2)
LIMIT 10;
- Join
- join:两个集合的交集， 等价于 inner join
- cross join:两个集合的笛卡尔积，返回的记录数为两个表的记录数乘积
- A left join B:返回的记录数为A的记录数
- B right join A == A left join B
- SQL优化实战
- case 1 : 分页获取粉丝列表
# 索引结构:following root@localhost:fotoplacedb216:39:21>show index from following; +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | following | 0 | PRIMARY | 1 | followingId | A | 59980091 | NULL | NULL | | BTREE | | | | following | 1 | uid1 | 1 | uid1 | A | 3748755 | NULL | NULL | | BTREE | | | | following | 1 | uid2 | 1 | uid2 | A | 19993363 | NULL | NULL | | BTREE | | | +-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ # 索引结构:user root@localhost:fotoplacedb216:40:21>show index from user; +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | uid | A | 24321834 | NULL | NULL | | BTREE | | | | user | 0 | email | 1 | email | A | 24321834 | NULL | NULL | | BTREE | | | | user | 1 | isRecommended | 1 | isRecommended | A | 1044 | NULL | NULL | | BTREE | | | | user | 1 | isDefaultFollow | 1 | isDefaultFollow | A | 2 | NULL | NULL | | BTREE | | | | user | 1 | telephoneStr | 1 | telephoneStr | A | 8107278 | NULL | NULL | | BTREE | | | | user | 1 | likeNumber | 1 | likeNumber | A | 10611 | NULL | NULL | | BTREE | | | | user | 1 | latlngidx | 1 | lat | A | 24321834 | NULL | NULL | | BTREE | | | | user | 1 | latlngidx | 2 | lng | A | 24321834 | NULL | NULL | | BTREE | | | | user | 1 | idx_ctime | 1 | createTime | A | 24321834 | NULL | NULL | YES | BTREE | | | | user | 1 | pass_idx | 1 | password | A | 12160917 | NULL | NULL | | BTREE | | | | user | 1 | ostype_idx | 1 | OSType | A | 149213 | NULL | NULL | | BTREE | | | | user | 1 | ltime | 1 | lastUpdateTime | A | 24321834 | NULL | NULL | YES | BTREE | | | +-------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 12 rows in set (0.00 sec) # 原始Sql:7.03s root@localhost:fotoplacedb216:21:46>SELECT -> following.followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar, -> user.lat, -> user.lng -> FROM following -> INNER JOIN user ON following.uid2 = user.uid -> WHERE following.uid1 = 101838 -> ORDER BY following.followingId DESC -> LIMIT 2000000, 15; +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | followingId | uid | userName | signature | avatar | lat | lng | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | 63041171 | 23301217 | 浅沫记忆 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041168 | 23301216 | 港岛妹妹 | | http://dn.fotoplace.cc/9031e9b5433e4aa7a0640c39579684f7.jpg | 0.00000000 | 0.00000000 | | 63041166 | 23301212 | 陈透明 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041161 | 23301215 | 趺苏 | | http://dn.fotoplace.cc/1a6b8ac82b6c41a49f8325b9cf6735c4.jpg | 0.00000000 | 0.00000000 | | 63041158 | 23301214 | Gareth_Bale11 | | /53a081e3fe6549248a096dd6b81f77ee.jpg | 0.00000000 | 0.00000000 | | 63041156 | 23301213 | 老宋 | | http://dn.fotoplace.cc/bd0419c95b4a4cf284c4301755d44722.jpg | 0.00000000 | 0.00000000 | | 63041151 | 23301211 | 唯爱敏 | | http://dn.fotoplace.cc/f6d8e31029b945338cff2fa059609612.jpg | 0.00000000 | 0.00000000 | | 63041146 | 23301209 | LVenus | | http://dn.fotoplace.cc/a9d2eec4719d4af19d3c9f702a9f2149.jpg | 0.00000000 | 0.00000000 | | 63041138 | 23301207 | 呐个谁伱不配 | | http://dn.fotoplace.cc/0dce2ae1cfb048e5b4420d9795e21e57.jpg | 0.00000000 | 0.00000000 | | 63041134 | 23301206 | 懒先森 | | http://dn.fotoplace.cc/8873bda0cbbb4a7b9772b4e962ad7c4d.jpg | 0.00000000 | 0.00000000 | | 63041130 | 23301205 | 刘艺 | | http://dn.fotoplace.cc/7db76a45455f4d92b03659b643b3759c.jpg | 0.00000000 | 0.00000000 | | 63041128 | 23301203 | 搅拌雨水的尘埃 | | http://dn.fotoplace.cc/a6fd183dee2843b88704e8459d28820c.jpg | 0.00000000 | 0.00000000 | | 63041124 | 23301202 | NotFoundError | | /6aa98a013dcd47f2a49449b78c54722a.jpg | 0.00000000 | 0.00000000 | | 63041122 | 23301201 | Ruin彡 | | http://dn.fotoplace.cc/13d94a3080644af5bb1bfde03cd0f3ad.jpg | 0.00000000 | 0.00000000 | | 63041120 | 23301200 | 马兰花 | | http://dn.fotoplace.cc/07b6ee1ee1c9468193a01cb30480739c.jpg | 0.00000000 | 0.00000000 | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ 15 rows in set (7.03 sec) # 优化第一次:2.55s root@localhost:fotoplacedb216:24:58>SELECT -> following.followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar, -> user.lat, -> user.lng -> FROM following -> INNER JOIN user ON following.uid2 = user.uid -> WHERE following.uid1 = 101838 AND following.followingId < 63041172 -> ORDER BY following.followingId DESC -> LIMIT 15; +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | followingId | uid | userName | signature | avatar | lat | lng | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | 63041171 | 23301217 | 浅沫记忆 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041168 | 23301216 | 港岛妹妹 | | http://dn.fotoplace.cc/9031e9b5433e4aa7a0640c39579684f7.jpg | 0.00000000 | 0.00000000 | | 63041166 | 23301212 | 陈透明 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041161 | 23301215 | 趺苏 | | http://dn.fotoplace.cc/1a6b8ac82b6c41a49f8325b9cf6735c4.jpg | 0.00000000 | 0.00000000 | | 63041158 | 23301214 | Gareth_Bale11 | | /53a081e3fe6549248a096dd6b81f77ee.jpg | 0.00000000 | 0.00000000 | | 63041156 | 23301213 | 老宋 | | http://dn.fotoplace.cc/bd0419c95b4a4cf284c4301755d44722.jpg | 0.00000000 | 0.00000000 | | 63041151 | 23301211 | 唯爱敏 | | http://dn.fotoplace.cc/f6d8e31029b945338cff2fa059609612.jpg | 0.00000000 | 0.00000000 | | 63041146 | 23301209 | LVenus | | http://dn.fotoplace.cc/a9d2eec4719d4af19d3c9f702a9f2149.jpg | 0.00000000 | 0.00000000 | | 63041138 | 23301207 | 呐个谁伱不配 | | http://dn.fotoplace.cc/0dce2ae1cfb048e5b4420d9795e21e57.jpg | 0.00000000 | 0.00000000 | | 63041134 | 23301206 | 懒先森 | | http://dn.fotoplace.cc/8873bda0cbbb4a7b9772b4e962ad7c4d.jpg | 0.00000000 | 0.00000000 | | 63041130 | 23301205 | 刘艺 | | http://dn.fotoplace.cc/7db76a45455f4d92b03659b643b3759c.jpg | 0.00000000 | 0.00000000 | | 63041128 | 23301203 | 搅拌雨水的尘埃 | | http://dn.fotoplace.cc/a6fd183dee2843b88704e8459d28820c.jpg | 0.00000000 | 0.00000000 | | 63041124 | 23301202 | NotFoundError | | /6aa98a013dcd47f2a49449b78c54722a.jpg | 0.00000000 | 0.00000000 | | 63041122 | 23301201 | Ruin彡 | | http://dn.fotoplace.cc/13d94a3080644af5bb1bfde03cd0f3ad.jpg | 0.00000000 | 0.00000000 | | 63041120 | 23301200 | 马兰花 | | http://dn.fotoplace.cc/07b6ee1ee1c9468193a01cb30480739c.jpg | 0.00000000 | 0.00000000 | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ 15 rows in set (2.55 sec) # 优化第二次:0.01s root@localhost:fotoplacedb216:30:58>SELECT -> followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar, -> user.lat, -> user.lng -> FROM user, -> ( -> SELECT -> uid2, -> followingId -> FROM following -> USE INDEX (`PRIMARY`) -> WHERE uid1 = 101838 AND followingId < 63041172 -> ORDER BY followingId DESC -> LIMIT 15 -> ) AS a -> WHERE a.uid2 = user.uid ORDER BY followingId desc; +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | followingId | uid | userName | signature | avatar | lat | lng | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ | 63041171 | 23301217 | 浅沫记忆 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041168 | 23301216 | 港岛妹妹 | | http://dn.fotoplace.cc/9031e9b5433e4aa7a0640c39579684f7.jpg | 0.00000000 | 0.00000000 | | 63041166 | 23301212 | 陈透明 | | /static/img/common/avatar.png | 0.00000000 | 0.00000000 | | 63041161 | 23301215 | 趺苏 | | http://dn.fotoplace.cc/1a6b8ac82b6c41a49f8325b9cf6735c4.jpg | 0.00000000 | 0.00000000 | | 63041158 | 23301214 | Gareth_Bale11 | | /53a081e3fe6549248a096dd6b81f77ee.jpg | 0.00000000 | 0.00000000 | | 63041156 | 23301213 | 老宋 | | http://dn.fotoplace.cc/bd0419c95b4a4cf284c4301755d44722.jpg | 0.00000000 | 0.00000000 | | 63041151 | 23301211 | 唯爱敏 | | http://dn.fotoplace.cc/f6d8e31029b945338cff2fa059609612.jpg | 0.00000000 | 0.00000000 | | 63041146 | 23301209 | LVenus | | http://dn.fotoplace.cc/a9d2eec4719d4af19d3c9f702a9f2149.jpg | 0.00000000 | 0.00000000 | | 63041138 | 23301207 | 呐个谁伱不配 | | http://dn.fotoplace.cc/0dce2ae1cfb048e5b4420d9795e21e57.jpg | 0.00000000 | 0.00000000 | | 63041134 | 23301206 | 懒先森 | | http://dn.fotoplace.cc/8873bda0cbbb4a7b9772b4e962ad7c4d.jpg | 0.00000000 | 0.00000000 | | 63041130 | 23301205 | 刘艺 | | http://dn.fotoplace.cc/7db76a45455f4d92b03659b643b3759c.jpg | 0.00000000 | 0.00000000 | | 63041128 | 23301203 | 搅拌雨水的尘埃 | | http://dn.fotoplace.cc/a6fd183dee2843b88704e8459d28820c.jpg | 0.00000000 | 0.00000000 | | 63041124 | 23301202 | NotFoundError | | /6aa98a013dcd47f2a49449b78c54722a.jpg | 0.00000000 | 0.00000000 | | 63041122 | 23301201 | Ruin彡 | | http://dn.fotoplace.cc/13d94a3080644af5bb1bfde03cd0f3ad.jpg | 0.00000000 | 0.00000000 | +-------------+----------+-----------------------+-----------+-------------------------------------------------------------+------------+------------+ 14 rows in set (0.01 sec) 这里少返回一条数据，是因为删除user表数据时没有删除following表的关联数据，导致following中存在无效数据。 如果不删除following表中的关联数据，极端情况下上条sql可能返回 empty set。 # expalin 对比 三个sql语句的explain结果一次如下： +----+-------------+-----------+--------+---------------+---------+---------+-----------------------------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+---------------+---------+---------+-----------------------------+----------+-------------+ | 1 | SIMPLE | following | ref | uid1,uid2 | uid1 | 8 | const | 15359220 | Using where | | 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 8 | fotoplacedb2.following.uid2 | 1 | NULL | +----+-------------+-----------+--------+---------------+---------+---------+-----------------------------+----------+-------------+ +----+-------------+-----------+--------+-------------------+---------+---------+-----------------------------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------+--------+-------------------+---------+---------+-----------------------------+---------+-------------+ | 1 | SIMPLE | following | ref | PRIMARY,uid1,uid2 | uid1 | 8 | const | 6850308 | Using where | | 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 8 | fotoplacedb2.following.uid2 | 1 | NULL | +----+-------------+-----------+--------+-------------------+---------+---------+-----------------------------+---------+-------------+ +----+-------------+------------+--------+---------------+---------+---------+--------+----------+----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+---------+---------+--------+----------+----------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 15 | Using filesort | | 1 | PRIMARY | user | eq_ref | PRIMARY | PRIMARY | 8 | a.uid2 | 1 | NULL | | 2 | DERIVED | following | range | PRIMARY | PRIMARY | 8 | NULL | 29990072 | Using where | +----+-------------+------------+--------+---------------+---------+---------+--------+----------+----------------+ # SQL分析 第一条sql: 1. 用offset来分页，大数据量下不可取 2. 获取某一页A数据后，如果产生了新的数据（新的粉丝），在获取A的下一页数据时，会返回重复数据。 优化方案：采用断点方式分页。（这里的断点就是followingId字段） # 断点分页注意事项： 1. 断点字段在表中必须唯一，否则可能漏掉数据或返回重复数据（比如时间类型的字段） 2. 断点字段必须有建立索引（为了快速定位目标数据），排序字段如果不方便可以不用建立索引，但前提是返回结果集的数量小的情况下才可以不用建立索引。 3. 根据实际场景优化索引结构，比如上面的最后一条SQL，由于where中用到了uid1和followingId两个字段，可以对这两个字段建立联合索引(uid1,followingId)，在优化的话可以考虑到select字段，让select的字段也在索引中。 # 奇怪的事来了，接着往下看 root@localhost:fotoplacedb217:43:19>SELECT -> followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar -> FROM user, -> ( -> SELECT -> uid2, -> followingId -> FROM following -> use index (`PRIMARY`) -> WHERE uid1 = 101838 AND followingId < 63041172 -> ORDER BY followingId DESC -> LIMIT 20 -> ) AS a -> WHERE a.uid2 = user.uid -> ORDER BY followingId DESC -> LIMIT 15; 数据省了 15 rows in set (0.00 sec) 这个秒回，任意改变followingId的值，也是秒回（其实也不是，当followingId为20000000的时候，需要2.65s，而30000000，20000000的时候都小于2.65s，可以近似的说20000000是个峰值，followingId离20000000越近，时间越长，越远，时间越短。这里先不讨论这个问题，就当做是秒回） 换为另外一个用户，并获取第一页数据 root@localhost:fotoplacedb217:58:45>SELECT -> followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar -> FROM user, -> ( -> SELECT -> uid2, -> followingId -> FROM following -> use index (`PRIMARY`) -> WHERE uid1 = 23640407 AND followingId < 999999999999 -> ORDER BY followingId DESC -> LIMIT 20 -> ) AS a -> WHERE a.uid2 = user.uid -> ORDER BY followingId DESC -> LIMIT 15; 数据省了 15 rows in set (0.58 sec) 这里竟然用了0.58s。换做101838用户获取任意页数据都是秒回。 101838 用户是大V用户，粉丝大约有600w。 23640407 用户是普通用户（就是本宝宝），粉丝就40个。 如果不强制使用主键索引，那么普通用户的数据就是秒回。 总结如下： 如果强制使用主键索引，大V用户拉取任意页数据可以认为是秒回。普通用户拉取数据却会变慢. 如果不强制使用索引，大V用户越往后拉数据，速度会变慢（也不是百分百递增，下面说这个问题）。普通用户就是秒回。 本宝宝搞不懂了。😢 由于系统大部分用户还是像我一样的普通用户，然后即便是大V用户，也很少拉后面的数据。所以本宝宝决定不强制使用主键索引了。 现在试验不强制使用索引的情况： 普通用户不用试验，有雨数据量小，都是秒回（真正的秒回） 试验大V用户发现： root@localhost:fotoplacedb218:18:13>SELECT -> followingId, -> user.uid, -> user.userName, -> user.signature, -> user.avatar -> FROM user, -> ( -> SELECT -> uid2, -> followingId -> FROM following -> WHERE uid1 = 101838 AND followingId < 60000000 -> ORDER BY followingId DESC -> LIMIT 20 -> ) AS a -> WHERE a.uid2 = user.uid -> ORDER BY followingId DESC -> LIMIT 15; 数据省了 15 rows in set (3.90 sec) 可以近似认为 followingId 为 6000w 时，速度最慢。followingId离6000w越近，越慢，越远，越快。 其实这就引出两个问题： 1. 为什么存在这样一个峰值？ 2. 为什么使用索引和不使用索引，两个峰值不一样?
redis 3.0.5
mysql 5.6.26-log
主：16个cpu(每个cpu一个核) 32G内存
从：16个cpu(每个cpu一个核) 32G内存
备份：4个cpu(每个cpu一个核) 4G 内存
zookeeper 3.4.6
dubbo-admin 2.5.3
es 1.6.0-2.1.0
- 随机毒鸡汤：所谓复习就是，把不会的东西再确认一遍，我—确实不会。
