查询
优化查询超多分页场景
-
SELECT * FROM customer LIMIT 10000000, 10;+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+ | 1 | SIMPLE | customer | NULL | ALL | NULL | NULL | NULL | NULL | 9383084 | 100.00 | NULL | +----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+MySql 的分页查询默认是通过取 offset + limit 条记录进行的。
从执行计划也可以看出直接查询的查询方式是全表查询(type=ALL)，由于是千万级数据，因此需要查询 10000010 行数据。
统计平均耗时：3.4667128412667s;
-
SELECT customer.* FROM (SELECT id FROM customer LIMIT 10000000, 10) a LEFT JOIN customer ON a.`id` = customer.`id`; -
SELECT a.* FROM customer a, (SELECT id FROM customer LIMIT 10000000, 10) b WHERE a.`id` = b.id;+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 9383084 | 100.00 | NULL | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.id | 1 | 100.00 | NULL | | 2 | DERIVED | customer | NULL | index | NULL | PRIMARY | 4 | NULL | 9383084 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+采用关联查询的方式，以上两种写法是差不多的，二者执行计划也是一样的，多表查询就是笛卡儿积。
同样先利用索引 id 查询到指定区域，再关联原表，通过 id 来取数据。
统计平均耗时：2.3666601628s;
高数量级数据查询要点
-
Where 子句
尽量避免全表扫描，在需要频繁进行 Where 筛选的字段添加索引：
SELECT * FROM customer WHERE mobile = '17089127158';-
未加索引：4.199990180933333s
-
普通索引：0.037s
-
主键索引：0.037s
-
统计同一列中各数据出现的次数和各数据的和
-
效果：
+----+------+--------+ | id | name | data | +----+------+--------+ +------+-------+----------+ | 1 | a | 1024 | | name | times | all_data | | 2 | b | 4324 | +------+-------+----------+ | 3 | c | 3424 | | b | 3 | 347880 | | 4 | d | 342 | ==> | c | 2 | 26756 | | 5 | b | 342322 | | e | 1 | 2134 | | 6 | a | 23 | | a | 2 | 1047 | | 7 | c | 23332 | | d | 1 | 342 | | 8 | e | 2134 | +------+-------+----------+ | 9 | b | 1234 | +----+------+--------+ -
SELECT `name`, COUNT(*) AS times, SUM(data) AS all_data FROM [table] GROUP BY [table].`name` ORDER BY times;
查找第 n 高的数据
-
效果：
+----+------+------+ | id | name | data | +----+------+------+ +------+ | 1 | a | 1024 | | max | | 2 | b | 4324 | n=2 +------+ | 3 | c | 3424 | ====> | 3424 | | 4 | d | 342 | +------+ | 8 | e | 2134 | +----+------+------+ -
巧用 max 函数（仅适用于前 3）：
SELECT max(data) max FROM [table] WHERE data < (SELECT max(data) FROM [table]); -
使用分页
使用视图解决 null 问题：
SELECT (SELECT data FROM [table] GROUP BY data DESC LIMIT 1, 1) AS max;使用
ifnull()函数解决 null 问题：SELECT ifnull ((SELECT data FROM [table] GROUP BY data DESC LIMIT 1, 1), null) AS max;
行列转换
将多行数据转换成多列数据
-
效果：
+----+-------+-----------+------------+ | id | class | course_id | teacher_id | +----+-------+-----------+------------+ +-------+--------+--------+--------+ | 1 | 101 | 2 | 18 | | class | 语文 | 数学 | 英语 | | 2 | 101 | 1 | 12 | +-------+--------+--------+--------+ | 3 | 101 | 3 | 1 | | 101 | 12 | 18 | 1 | | 4 | 102 | 2 | 4 | ====> | 102 | 54 | 4 | 0 | | 5 | 102 | 1 | 54 | | 103 | 23 | 0 | 0 | | 6 | 103 | 1 | 23 | | 104 | 0 | 0 | 13 | | 7 | 104 | 3 | 13 | +-------+--------+--------+--------+ +----+-------+-----------+------------+ -
使用
Group By分组如果需要作为分组的依据是多个字段关联，那么使用
Group By col1, col2, col3...即可。注意，
Group By之后select后面指定的字段必须与group by后面的一致，或者是使用聚合函数。比如，这个例子中，对
class进行Group By之后，不能select id，因为同一组的 id 是不同的，MySQL 默认不知道选哪个；如果 select 了不合法的字段，MySql 会报异常。 -
使用
IF函数IF(a, b, c)函数：若 a 为真，则 b， 否则 c。SELECT class, SUM(IF(course_id = 1, teacher_id, 0)) AS `语文`, SUM(IF(course_id = 2, teacher_id, 0)) AS `数学`, SUM(IF(course_id = 3, teacher_id, 0)) AS `英语` FROM test GROUP BY class;
将多行数据转换为单列数据
-
效果：
-
使用
GROUP_CONCAT()函数GROUP_CONCAT()函数可以将多行中同一列的数据拼接起来，可以指定分隔符和指定合并时排序的顺序。
最大值：单行多列数据取最大值
-
效果：
+----+---------+------+---------+ +----+---------+------+---------+--------+ | id | english | math | chinese | | id | english | math | chinese | higest | +----+---------+------+---------+ +----+---------+------+---------+--------+ | 1 | 99 | 78 | 53 | | 1 | 99 | 78 | 53 | 99 | | 2 | 88 | 34 | 89 | ====> | 2 | 88 | 34 | 89 | 89 | | 3 | 34 | 23 | 58 | | 3 | 34 | 23 | 58 | 58 | | 4 | 95 | 84 | 78 | | 4 | 95 | 84 | 78 | 95 | +----+---------+------+---------+ +----+---------+------+---------+--------+ -
使用
GREATEST()函数SELECT *, GREATEST(english, math, chinese) AS higest FROM student_grades;
将查询出来的 null 值转为自定义默认值
-
效果：
+-----+-----------+-------+ +-----+-----------+-------+ | id | name | level | | id | name | level | +-----+-----------+-------+ +-----+-----------+-------+ | 777 | 涂志强 | 1 | | 777 | 涂志强 | 1 | | 888 | 姚欢 | 2 | ====> | 888 | 姚欢 | 2 | | 790 | 冷欢 | 3 | | 790 | 冷欢 | 3 | | 733 | 熊桂荣 | NULL | | 733 | 熊桂荣 | 0 | | 734 | 谢莉 | NULL | | 734 | 谢莉 | 0 | +-----+-----------+-------+ +-----+-----------+-------+ -
方法：
ISNULL(exp1, exp2)SELECT c.`id`, c.`name`, IFNULL(w.`level`, 0) as level FROM customer c LEFT JOIN white_list w ON c.`id` = w.`customer_id` LIMIT 5;
基础查询
运行线程
show processlist;
版本
SELECT version();
查询数据库各表记录数
USE information_schema;
SELECT table_name,table_rows FROM TABLES WHERE TABLE_SCHEMA = '[databese]' ORDER BY table_rows DESC;
表操作
设置 create_time 与 update_time 字段
-
数据类型：TIMESTAMP（格式为Y-m-d H:i:s），范围：1970 ~ 2037；
-
create_time
ALTER TABLE [table] MODIFY created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL; ALTER TABLE [table] MODIFY updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL;
字符集
查看字符集
-
查看数据库编码：
SHOW CREATE DATABASE [database]; -
查看表编码：
SHOW CREATE TABLE [table]; -
查看字段编码：
SHOW FULL COLUMNS FROM [table];
修改表字符集
-
设置表的编码
ALTER TABLE [table] CHARACTER SET utf8mb4; -
修改表字段的字符集
ALTER TABLE [table] CONVERT TO CHARACTER SET utf8mb4;
写操作
插入千万级数据
-
使用 insert 的多条模式，一个 sql 语句插入多条数据：
INSERT INTO table (field1,field2,field3) VALUES ('a','b','c'), ('a','b','c'),('a','b','c'); -
MySql 会为单一条 sql 语句执行事务操作，因此逐条执行 sql 插入时事务操作会占用很多时间，因此我们可以对 sql 插入进行批处理，处理前开启事务，处理后提交事务。
begin transaction; insert ... ... insert ... commit transaction; -
示例：
$faker = Factory::create('zh_CN'); for ($i = 0; $i < 10; $i++) { DB::beginTransaction(); try { for ($j = 0; $j < 10; $j++) { $sql = "insert into customer (name, birthday, gender, mobile) values ('你好', '1993-03-17 08:00:00', 1 , 12345678901)"; for ($record = 0; $record < 100000; $record++) { $sql .= ", ('". $faker->name . "', '" . $faker->dateTime->format('Y-m-d H:i:s') . "', " . $faker->numberBetween(1, 3) . ', ' . $faker->phoneNumber . ')'; } DB::insert($sql); } DB::commit(); } catch(QueryException $ex) { DB::rollback(); } } -
bulk_insert_buffer_size这个参数仅作用于使用 MyISAM 存储引擎，用来缓存批量插入数据的时候临时缓存写入数据，默认值为 8M，如果需要更快的批量处理，我们可以把它调整到 32M 甚至更大。 -
max_allowed_packet参数会限制 MySql 服务器接受的数据包大小。此时太大的插入和更新会受 max_allowed_packet 参数限制。
插入数据唯一性约束
-
insert ignore into …: 若有重复数据，不进行插入
前提：建立唯一索引，如需要对下表 username 和 mobile 建立唯一索引
insert ignore into user(username, mobile, address) values("Lillian", 2147483647, "Beijing"); -
on duplicate key update：若有重复数据，不进行插入，但对数据指定字段进行更新
前提：建立唯一索引，如需要对下表 username 和 mobile 建立唯一索引
insert into user(username, mobile, address) values("Lillian", 2147483647, "Beijing") on duplicate key update address="Beijing"; -
replace into：若有重复数据，删除旧数据，插入新数据
前提：建立唯一索引，如需要对下表 username 和 mobile 建立唯一索引
replace into user(username, mobile, address) values("Lillian", 2147483647, "Beijing"); -
insert if not exists：若有重复数据，不进行插入
前提：无，适用于不建立唯一索引的表
使用方法：select 中每一个值对应 insert 的每一个字段，where exists 子查询中的搜索条件为唯一性约束条件
insert into user(username, mobile, address) select "Lillian", 2147483647, "Beijing" from user where not exists ( select username, mobile from user where username="Lillian" and mobile=2147483647 );