根据非主键字段排序的分页查询
# 一个违反直觉的查询,先使用index排序,再查出需要的数据.比直接使用文件排序快.
select * from employees ORDER BY name limit 90000,5;
+-------+------------+-------+----------+---------------------+
| id | name | age | position | hire_time |
+-------+------------+-------+----------+---------------------+
| 90999 | zhuge90996 | 90996 | dev | 2023-09-10 11:24:22 |
| 91000 | zhuge90997 | 90997 | dev | 2023-09-10 11:24:22 |
| 91001 | zhuge90998 | 90998 | dev | 2023-09-10 11:24:22 |
| 91002 | zhuge90999 | 90999 | dev | 2023-09-10 11:24:22 |
| 94 | zhuge91 | 91 | dev | 2023-09-10 11:20:59 |
+-------+------------+-------+----------+---------------------+
5 rows in set (0.08 sec)
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
+-------+------------+-------+----------+---------------------+-------+
| id | name | age | position | hire_time | id |
+-------+------------+-------+----------+---------------------+-------+
| 90999 | zhuge90996 | 90996 | dev | 2023-09-10 11:24:22 | 90999 |
| 91000 | zhuge90997 | 90997 | dev | 2023-09-10 11:24:22 | 91000 |
| 91001 | zhuge90998 | 90998 | dev | 2023-09-10 11:24:22 | 91001 |
| 91002 | zhuge90999 | 90999 | dev | 2023-09-10 11:24:22 | 91002 |
| 94 | zhuge91 | 91 | dev | 2023-09-10 11:20:59 | 94 |
+-------+------------+-------+----------+---------------------+-------+
5 rows in set (0.01 sec)
explain select * from employees ORDER BY name limit 90000,5;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 100120 | 100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
explain select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.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 | 90005 | 100.00 | NULL |
| 1 | PRIMARY | e | NULL | eq_ref | PRIMARY | PRIMARY | 4 | ed.id | 1 | 100.00 | NULL |
| 2 | DERIVED | employees | NULL | index | NULL | idx_name_age_position | 185 | NULL | 90005 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+-----------------------+---------+-------+-------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
Join关联查询优化
小表驱动大表, 关联字段加索引, 强制指定驱动表
嵌套循环连接 Nested-Loop Join(NLJ) 算法
整个过程会读取 t2 表的所有数据(扫描100行)
然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表 中的对应行(扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100 行)。因此整个过程扫描了 200 行。
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
整个过程对表 t1 和 t2 都做了一次全表扫描，因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100。
并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table t2 like t1;
drop procedure if exists insert_t1;
delimiter ;;
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t1();
drop procedure if exists insert_t2;
delimiter ;;
create procedure insert_t2()
begin
declare i int;
set i=1;
while(i<=100)do
insert into t2(a,b) values(i,i);
set i=i+1;
end while;
end;;
delimiter ;
call insert_t2();
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | t2 | NULL | ALL | idx_a_b_c | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ref | idx_a_b_c | idx_a_b_c | 4 | explain_test.t2.a | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------+
EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t2 | NULL | ALL | NULL | NULL | NULL | NULL | 100 | 100.00 | NULL |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 9930 | 10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
count(*)查询优化
常见优化方法
1、查询mysql自己维护的总行数 (对于innodb存储引擎的表mysql不会存储表的总记录行数,因为有MVCC机制)
2、show table status
3、将总数维护到Redis里
4、增加数据库计数表
阿里巴巴Mysql规范解读
选择正确的数据类型，对于性能至关重要
-
数值类型
- 如果整形数据没有负数，建议指定为UNSIGNED无符号类型。
- 建议使用TINYINT代替ENUM、BITENUM、SET。
- DECIMAL最适合保存准确度要求高，用于计算的数据。
- 建议使用整形类型来运算和存储实数
-
日期和时间
- 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
- TIMESTAMP是UTC时间戳，与时区相关。
- DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关
-
字符串
-
字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
-
尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
-
BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
-
