一千万数据，怎么快速查询

【一千万数据，怎么快速查询】
CREATE TABLE user_operation_log (
id int(11) NOT NULL AUTO_INCREMENT,
user_id varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
ip varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
op_data varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr1 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr2 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr3 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr4 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr5 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr6 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr7 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr8 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr9 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr10 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr11 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
attr12 varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (id) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

创建数据脚本
采用批量插入，效率会快很多，而且每1000条数就commit，数据量太大，也会导致批量插入效率慢

DELIMITER ;;
CREATE PROCEDURE batch_insert_log()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE userId INT DEFAULT 10000000;
set @execSql = ‘INSERT INTO test.user_operation_log(user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12) VALUES’;
set @execData = ‘’;
WHILE i<=10000000 DO
set @attr = “‘测试很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长很长的属性’”;
set @execData = concat(@execData, “(”, userId + i, “, ‘10.0.69.175’, ‘用户登录操作’”, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “,”, @attr, “)”);
if i % 1000 = 0
then
set @stmtSql = concat(@execSql, @execData,“;”);
prepare stmt from @stmtSql;
execute stmt;
DEALLOCATE prepare stmt;
commit;
set @execData = “”;
else
set @execData = concat(@execData, “,”);
end if;
SET i=i+1;
END WHILE;

END;;
DELIMITER ;

不同数据量，相同偏移量：数据量越大，耗时越大
相同数据量，不同偏移量：偏移量越大，耗时越大

【优化方式一：子查询】

先定位偏移位置的id，然后再查询对应页的数据
SELECT * FROM user_operation_log LIMIT 1000000, 10 //
SELECT id FROM user_operation_log LIMIT 1000000, 1 // 有索引的情况下，耗时很小
SELECT * FROM user_operation_log WHERE id >= (SELECT id FROM user_operation_log LIMIT 1000000, 1) LIMIT 10

查询结果如下：
第一条：4818ms
第二条：4312ms/199ms
第三条：4319/201ms

第一条花费的时间最大，第三条比第一条稍微好点，子查询使用索引速度更快
缺点：只适用于id递增的情况

id非递增的情况可使用如下写法：

SELECT * FROM user_operation_log WHERE id IN (SELECT t.id FROM (SELECT id FROM user_operation_log LIMIT 1000000, 10) AS t)
缺点：分页查询只能放在子查询里面
注意：某些 mysql 版本不支持在 in 中使用 limit，所以采用了多个嵌套select

【优化方式二：id范围限定】

id必须是连续递增，先计算id的范围，再使用 between：
SELECT * FROM user_operation_log WHERE id between 1000000 AND 1000100 LIMIT 100
SELECT * FROM user_operation_log WHERE id >= 1000000 LIMIT 100
注意：这里的 LIMIT 限制条数，没有采用偏移量。

【优化数据量大的问题】

返回结果的数据量也会直接影响速度
SELECT * FROM user_operation_log LIMIT 1, 1000000
SELECT id FROM user_operation_log LIMIT 1, 1000000
SELECT id, user_id, ip, op_data, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12 FROM user_operation_log LIMIT 1, 1000000

减少不需要的列，查询效率也可以得到明显提升。

【不用select 】
（1）用 "SELECT * " 数据库需要解析更多的对象、字段、权限、属性等相关内容。
在 SQL 语句复杂，硬解析较多的情况下，会对数据库造成沉重的负担。
（2）增大网络开销， 有时会误带上如log、IconMD5之类的无用且大文本字段，数据传输size会几何增涨。
特别是MySQL和应用程序不在同一台机器，这种开销非常明显。

【一千万的数据如何高效查？】

不能这么搞，直接拒绝回答

【数据库中如何优雅地存储ip地址】

IPv4地址是一个4字节的无符号整数。
IPv4通常被写作点分十进制的形式。

使用varchar要使用3*4+3+1=16B（varchar的长度<=255时，需要1B保存长度)
使用int是4B

《高性能MySql》建议：
在够用的前提下，字节能省则省。所以用无符号的int(10)存储IPv4是最好的选择。

CREATE TABLE login_log (
id int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键id’,
user_id bigint(20) NOT NULL COMMENT ‘用户id’,
ipv4 int(10) unsigned DEFAULT NULL COMMENT ‘ipv4地址 int类型’,
ipv4_string varchar(16) DEFAULT NULL COMMENT ‘ipv4地址 varchar类型’,
ip varbinary(16) DEFAULT NULL COMMENT ‘ip地址，兼容ipv4和ipv6’,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO login_log(user_id, ipv4, ipv4_string) VALUES(1, INET_ATON(“192.168.1.5”), “192.168.1.5”);
SELECT *, INET_NTOA(ipv4) FROM login_log

INET_ATON() 将IPv4的字符串地址转换成数值，
INET_NTOA() 将IPv4的数值转换成字符串地址。

IPv6是一个16字节的整数。
MySQL不支持16字节的整数（最大为8字节的bigint类型），所以无法使用整数存储IPv6。
先看看IPv6的三种常见的表示方式：

（1）冒分十六进制表示法
格式为X:X:X:X:X:X:X:X，其中每个X表示地址中的16b（2B）。
以十六进制表示，ABCD:EF01:2345:6789:ABCD:EF01:2345:6789。
每个X的前导0可省略，例如：
2001:0DB8:0000:0023:0008:0800:200C:417A→2001:DB8:0:23:8:800:200C:417A

（2）0位压缩表示法
IPv6地址中间可能包含很长的一段0，可以把连续的一段0压缩为“::”。
但为保证地址解析的唯一性，地址中”::”只能出现一次。
例如：
FF01:0:0:0:0:0:0:1101 → FF01::1101
0:0:0:0:0:0:0:1 → ::1
0:0:0:0:0:0:0:0 → ::

（3）内嵌IPv4地址表示法
为了实现IPv4-IPv6互通，IPv4地址会嵌入IPv6地址中。
地址常表示为：X:X:X:X:X:X:d.d.d.d。
前96b采用冒分十六进制表示，后32b地址使用IPv4点分十进制表示。
例如
::192.168.0.1
::FFFF:192.168.0.1
（在前96b中，压缩0位的方法依旧适用）
如果使用varchar类型保存IPv6，那么存储以冒分十六进制表示的IPv6时，需要39 + 1 = 40 B。
IPv6本质上一个16字节的数字。

在《高性能MySQL 第三版》中有句话：
与CHAR和VARCHAR类似的类型还有BINARY和VARBINARY，它们存储的是二进制字符串。
二进制字符串和常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。
同时，要注意到VARBINARY(m)，其中m表示字节数。
这样可以使用VARBINARY(16)存储IPv6地址，并且兼容存储IPv4地址。

对三种不同方式表示IPv6地址的记录进行插入：
INSERT INTO login_log(user_id, times, ip) VALUES(1, 2, inet6_aton(“2001:db8:85a3:8d3:1319:8a2e:370:7348”));
INSERT INTO login_log(user_id, times, ip) VALUES(1, 3, inet6_aton(“fe80::3579:5807:93af:a2ce”));
INSERT INTO login_log(user_id, times, ip) VALUES(1, 4, inet6_aton(“::192.168.0.1”));
SELECT *, INET6_NTOA(ip) FROM login_log

INET6_ATON() 将IPv6的字符串地址转换成数值
INET6_NTOA() 将IPv6的数值转换成字符串地址