数据库SQL语句优化----必看推荐

60 阅读 0 评论 40 点赞

我是靠谱客的博主追寻云朵，最近开发中收集的这篇文章主要介绍数据库SQL语句优化----必看推荐，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一：基础数据准备

DROP TABLE IF EXISTS `tbl_user`;
CREATE TABLE `tbl_user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`email` varchar(20) DEFAULT NULL,
`age` tinyint(4) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_user` VALUES
('1', 'admin', 'admin@126.com', '18', '1', '2018-07-09 11:08:57'),
('2', 'mengday', 'mengday@163.com', '31', '2', '2018-07-09 11:09:00'),
('3', 'mengdee', 'mengdee@163.com', '20', '2', '2018-07-09 11:09:04'),
('4', 'root', 'root@163.com', '31', '1', '2018-07-09 14:36:19'),
('5', 'zhangsan', 'zhangsan@126.com', '20', '1', '2018-07-09 14:37:28'),
('6', 'lisi', 'lisi@gmail.com', '20', '1', '2018-07-09 14:37:31'),
('7', 'wangwu', 'wangwu@163.com', '18', '1', '2018-07-09 14:37:34'),
('8', 'zhaoliu', 'zhaoliu@163.com', '22', '1', '2018-07-11 18:29:24'),
('9', 'fengqi', 'fengqi@163.com', '19', '1', '2018-07-11 18:29:32');
DROP TABLE IF EXISTS `tbl_userinfo`;
CREATE TABLE `tbl_userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`address` varchar(255) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_userId` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;
INSERT INTO `tbl_userinfo` VALUES
('1', '上海市', '1'),
('2', '北京市', '2'),
('3', '杭州', '3'),
('4', '深圳', '4'),
('5', '广州', '5'),
('6', '海南', '6');

二：五百万数据插入

上面插入几条测试数据，在使用索引时还需要插入更多的数据作为测试数据，下面就通过存储过程插入500W条数据作为测试数据

-- 修改mysql默认的结束符号，默认是分号；但是在函数和存储过程中会使用到分号导致解析不正确
delimiter $$
-- 随机生成一个指定长度的字符串
create function rand_string(n int) returns varchar(255)
begin
# 定义三个变量
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(255) default '';
declare i int default 0;
while i < n do
set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1));
set i = i + 1;
end while;
return return_str;
end $$
-- 如果这里执行报错（This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its de...）
--其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。在MySQL中创建函数时出现这种错误的解决方法：set global log_bin_trust_function_creators=TRUE;
-- 创建插入的存储过程
create procedure insert_user(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;
repeat
set i = i + 1;
insert into tbl_user values ((start+i) ,rand_string(8), concat(rand_string(6), '@random.com'), 1+FLOOR(RAND()*100), 3, now());
until i = max_num
end repeat;
commit;
end $$
-- 将命令结束符修改回来
delimiter ;
-- 调用存储过程，插入500万数据，需要等待一会时间，等待执行完成
call insert_user(100001,5000000);
-- Query OK, 0 rows affected (7 min 49.89 sec) 我的Macbook Pro i5 8G内存用了8分钟才执行完
select count(*) from tbl_user;

三：使用索引和不使用索引的比较

没有添加索引之前，我们使用一个简单的查询，用了2.60秒

mysql> select * from tbl_user where username = 'YEdCEmyH';
+--------+----------+-------------------+------+------+---------------------+
| id
| username | email
| age
| type | create_time
|
+--------+----------+-------------------+------+------+---------------------+
| 572298 | YEdCEmyH | OZFcwb@random.com |
83 |
3 | 2020-04-07 22:50:48 |
+--------+----------+-------------------+------+------+---------------------+
1 row in set (2.60 sec)

然后我们开始创建索引，执行查询用了0.01秒

mysql> create index idx_username on tbl_user(username);
Query OK, 0 rows affected (12.55 sec)
Records: 0
Duplicates: 0
Warnings: 0
mysql> select * from tbl_user where username = 'YEdCEmyH';
+--------+----------+-------------------+------+------+---------------------+
| id
| username | email
| age
| type | create_time
|
+--------+----------+-------------------+------+------+---------------------+
| 572298 | YEdCEmyH | OZFcwb@random.com |
83 |
3 | 2020-04-07 22:50:48 |
+--------+----------+-------------------+------+------+---------------------+
1 row in set (0.01 sec)

四：explain命令

mysql> explain select * from tbl_user where username = 'YEdCEmyH';
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|
1 | SIMPLE
| tbl_user | NULL
| ref
| idx_username
| idx_username | 768
| const |
1 |
100.00 | NULL
|
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tbl_user where id = 1;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table
| partitions | type
| possible_keys | key
| key_len | ref
| rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|
1 | SIMPLE
| tbl_user | NULL
| const | PRIMARY
| PRIMARY | 4
| const |
1 |
100.00 | NULL
|
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tbl_user where id = (select user_id from tbl_userinfo where address = '上海市');
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table
| partitions | type
| possible_keys | key
| key_len | ref
| rows | filtered | Extra
|
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|
1 | PRIMARY
| tbl_user
| NULL
| const | PRIMARY
| PRIMARY | 4
| const |
1 |
100.00 | NULL
|
|
2 | SUBQUERY
| tbl_userinfo | NULL
| ALL
| NULL
| NULL
| NULL
| NULL
|
6 |
16.67 | Using where |
+----+-------------+--------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
2 rows in set, 1 warning (0.05 sec)
mysql> explain select * from tbl_user where username = 'admin'
-> union
-> select * from tbl_user where email = 'admin@163.com';
+----+--------------+------------+------------+------+---------------+--------------+---------+-------+---------+----------+-----------------+
| id | select_type
| table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+--------------+------------+------------+------+---------------+--------------+---------+-------+---------+----------+-----------------+
|
1 | PRIMARY
| tbl_user
| NULL
| ref
| idx_username
| idx_username | 768
| const |
1 |
100.00 | NULL
|
|
2 | UNION
| tbl_user
| NULL
| ALL
| NULL
| NULL
| NULL
| NULL
| 4981602 |
10.00 | Using where
|
| NULL | UNION RESULT | <union1,2> | NULL
| ALL
| NULL
| NULL
| NULL
| NULL
|
NULL |
NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+--------------+---------+-------+---------+----------+-----------------+
3 rows in set, 1 warning (0.04 sec)

explain命令用于查看sql执行时是否使用了索引，是优化SQL语句的一个非常常用而且非常重要的一个命令, 上面中的key字段表示查询使用到的索引即使用了idx_username索引

select_type：查询类型
① simple: 简单表即不适用表连接或者子查询
② primary: 主查询
③ subquery: 子查询内层第一个SELECT，结果不依赖于外部查询
④ dependent subquery: 子查询内层第一个select: 依赖于外部查询
⑤ union：合并结果
type：扫描的方式
① all：全表扫描
② index: 扫描所有索引
③ range: 索引范围扫描
④ const: 表示最多有一个匹配行, 常见于根据主键或唯一索引进行查询
possible_keys ：该查询可以利用的索引，可能同一个查询有多个索引可以使用，如果没有任何索引显示null
key: 实际使用到的索引，从Possible_key中所选择使用索引，当有多个索引时，mysql会挑出一个最优的索引来使用
key_len: 被选中使用索引的索引长度
rows: 估算出结果集行数，该sql语句扫描了多少行，可能得到的结果,MySQL认为它执行查询时必须检查的行数

mysql可以通过 EXPLAIN EXTENDED 和 SHOW WARNINGS 来查看mysql优化器改写后的sql语句

mysql>
EXPLAIN EXTENDED
-> select * from tbl_user where 1=1 and username = 'admin';
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
|
1 | SIMPLE
| tbl_user | NULL
| ref
| idx_username
| idx_username | 768
| const |
1 |
100.00 | NULL
|
+----+-------------+----------+------------+------+---------------+--------------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level
| Code | Message
|
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.
|
| Note
| 1003 | /* select#1 */ select `test`.`tbl_user`.`id` AS `id`,`test`.`tbl_user`.`username` AS `username`,`test`.`tbl_user`.`email` AS `email`,`test`.`tbl_user`.`age` AS `age`,`test`.`tbl_user`.`type` AS `type`,`test`.`tbl_user`.`create_time` AS `create_time` from `test`.`tbl_user` where (`test`.`tbl_user`.`username` = 'admin') |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

可以看到优化器把条件查询中的1=1去掉了。。。

五：走索引的情况和不走索引的情况（注意看key）

in走索引： in操作能避免则避免，若实在避免不了，需要仔细评估in后边的集合元素数量，控制在1000个之内。

mysql> explain select * from tbl_user where username in ('admin','root');
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table
| partitions | type
| possible_keys | key
| key_len | ref
| rows | filtered | Extra
|
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|
1 | SIMPLE
| tbl_user | NULL
| range | idx_username
| idx_username | 768
| NULL |
2 |
100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

范围查询走索引
模糊查询只有右前缀使用索引

mysql> explain select * from tbl_user where username like '%admin%';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> explain select * from tbl_user where username like 'admin%';
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table
| partitions | type
| possible_keys | key
| key_len | ref
| rows | filtered | Extra
|
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|
1 | SIMPLE
| tbl_user | NULL
| range | idx_username
| idx_username | 768
| NULL |
1 |
100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from tbl_user where username like '%admin';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

反向条件不走索引 != 、 <> 、 NOT IN、IS NOT NULL 我这里只做！=测试，其他可自行测试

mysql> explain select * from tbl_user where username != 'admin';
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| idx_username
| NULL | NULL
| NULL | 4981602 |
50.87 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

常见的对not in的优化，使用左连接加上is null的条件过滤

SELECT id, username, age FROM tbl_user WHERE id NOT IN (SELECT user_id FROM tbl_order);
SELECT u.id, u.username, u.age FROM tbl_user u
LEFT JOIN tbl_order o ON u.id = o.user_id
WHERE o.user_id IS NULL;

对条件计算(使用函数或者算数表达式)不走索引
使用函数计算不走索引，无论是对字段使用了函数还是值使用了函数都不走索引，解决办法通过应用程序计算好，将计算的结果传递给sql，而不是让数据库去计算

mysql> explain select * from tbl_user where length(username) <6;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.06 sec)

id是主键，id/10使用了算数表达式不走索引

mysql> explain select * from tbl_user where id/10 = 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
100.00 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询时必须使用正确的数据类型
如果索引字段是字符串类型，那么查询条件的值必须使用引号，否则不走索引

select * from tbl_user where username
= 'admin'
--不要这样使用
select * from tbl_user where username = admin

or 使用索引和不使用索引的情况
or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
用union少用or
尽量避免使用or，因为大部分or连接的两个条件同时都进行索引的情况几率比较小，应使用uninon代替，这样能走索引的走索引，不能走索引的就全表扫描。
能用union all就不用union
union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表
group by
默认情况下，group by column；有两个作用，第一个就是根据指定的列进行分组，第二作用group by 不但分组，而且还为分组中的数据按照列来排序，如果分组的字段创建了索引，那么排序也没什么毕竟排序走索引也很快但是如果group by指定的列没有走索引，而我们通常情况下只对分组中的数据进行统计，例如对分组中的数据求和，通常顺序无关紧要，此时就要关闭group by 的排序功能，使用Order By NULL;来关闭排序功能，避免排序对性能的影响。

mysql> explain select age , count(*) from tbl_user group by age;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
100.00 | Using temporary; Using filesort |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+---------------------------------+
1 row in set, 1 warning (0.06 sec)
-- order by null 关闭 group by的排序功能
mysql> explain select age , count(*) from tbl_user group by age order by null;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
100.00 | Using temporary |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

分页limit 分页查询一般会全表扫描，优化的目的应尽可能减少扫描；

第一种思路：在索引上完成排序分页的操作，最后根据主键关联回原表查询原来所需要的其他列。这种思路是使用覆盖索引尽快定位出需要的记录的id，覆盖索引效率高些

mysql> select * from tbl_user limit 1000000,2;
+---------+----------+-------------------+------+------+---------------------+
| id
| username | email
| age
| type | create_time
|
+---------+----------+-------------------+------+------+---------------------+
| 1099993 | ZtYJyssL | AwFNZH@random.com |
29 |
3 | 2020-04-07 22:51:50 |
| 1099994 | yzdQWjhk | BcFXXU@random.com |
61 |
3 | 2020-04-07 22:51:50 |
+---------+----------+-------------------+------+------+---------------------+
2 rows in set (1.71 sec)
mysql> explain select * from tbl_user limit 1000000,2;
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table
| partitions | type | possible_keys | key
| key_len | ref
| rows
| filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|
1 | SIMPLE
| tbl_user | NULL
| ALL
| NULL
| NULL | NULL
| NULL | 4981602 |
100.00 | NULL
|
+----+-------------+----------+------------+------+---------------+------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> select * from tbl_user u
-> inner join (select id from tbl_user order by id asc limit 1000000,2) tmp on tmp.id = u.id;
+---------+----------+-------------------+------+------+---------------------+---------+
| id
| username | email
| age
| type | create_time
| id
|
+---------+----------+-------------------+------+------+---------------------+---------+
| 1099993 | ZtYJyssL | AwFNZH@random.com |
29 |
3 | 2020-04-07 22:51:50 | 1099993 |
| 1099994 | yzdQWjhk | BcFXXU@random.com |
61 |
3 | 2020-04-07 22:51:50 | 1099994 |
+---------+----------+-------------------+------+------+---------------------+---------+
2 rows in set (0.28 sec)
mysql> explain
->
select * from tbl_user u
-> inner join (select id from tbl_user order by id asc limit 1000000,2) tmp on tmp.id = u.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
| 1000002 |
100.00 | NULL
|
|
1 | PRIMARY
| u
| NULL
| eq_ref | PRIMARY
| PRIMARY | 4
| tmp.id |
1 |
100.00 | NULL
|
|
2 | DERIVED
| tbl_user
| NULL
| index
| NULL
| PRIMARY | 4
| NULL
| 1000002 |
100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+--------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

第二种思路：limit m,n 转换为 n

之前分页查询是传pageNo页码, pageSize分页数量，当前页的最后一行对应的id即lastrowid，以及pageSize，这样先根据条件过滤掉lastrowid之前的数据，然后再去n挑记录,此种方式只能用于排序字段不重复唯一的列，如果用于重复的列，那么分页数据将不准确

当只一行数据使用limit 1

mysql> select * from tbl_user where id > 1000000 order by id asc limit 2;
+---------+----------+-------------------+------+------+---------------------+
| id
| username | email
| age
| type | create_time
|
+---------+----------+-------------------+------+------+---------------------+
| 1000001 | BXkfXwrr | LEMYIt@random.com |
87 |
3 | 2020-04-07 22:51:38 |
| 1000002 | lBXhVfRR | KXJACk@random.com |
37 |
3 | 2020-04-07 22:51:38 |
+---------+----------+-------------------+------+------+---------------------+
2 rows in set (0.00 sec)

多表连接查询连接条件(也就是外键必须创建索引，否则大数据查询直接卡死)

如果全表扫描比使用索引快，就不会使用索引，比如表的数量很少或者满足条件的数据量比较大也不走索引, 查询数据库记录时，查询到的条目数尽量小，当通过索引获取到的数据库记录> 数据库总记录的1/3时，SQL将有可能直接全表扫描，索引就失去了应有的作用。

where条件将能过滤掉多的条件写在前面，过滤掉少部分的数据写在后面，这样先排除一大部分不满足条件的数据，然后剩下一小部分数据，然后再从中找出满足条件的记录

六：其它优化

禁止使用select *，需要什么字段就去取哪些字段
超过三个表禁止join。需要join的字段数据类型必须绝对一致;多表关联查询时，保证被关联的字段需要有索引。说明:即使双表 join 也要注意表索引、SQL 性能。尽可能避免复杂的join和子查询。尽量使用左右连接，少使用内连接。永远小结果集驱动大结果集(这点mysql会自动优化)
不要使用count(列名)或 count(常量)来替代 count()，count()是SQL92定义的标准统计行数的语法，跟数据库无关，跟 NULL和非NULL无关。说明:count(*)会统计值为NULL 的行，而count(列名)不会统计此列为NULL值的行。
使用表连接来优化子查询。尽量避免使用子查询，建议将子查询转换成关联查询，子查询的效率并没有连接join查询快(并不绝对)，连接查询之所以更有效率一些，是因为mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。对于多表连接，如果连接条件创建索引效率更高
对于列类型是字符串，值必须使用单引号括住，如果没有引号引住就不走索引
拒绝大SQL，拆分成小SQL
减少 IO 次数 IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

找出项目中执行比较慢的sql语句

MySQL慢查询日志记录下所有执行超过longquerytime时间的SQL语句，帮你找到执行慢的SQL(包括查询语句、修改语句、已经回滚的sql)，方便我们对这些SQL进行优化。默认情况下，mysql没有启用慢查询日志，如果需要启动需要在my.cnf配置文件中配置开启

# 开启慢查询
slowquerylog=true
# 指定慢查询日志的存储路径和文件
slowquerylog_file = /usr/local/mysql/data/slow.log
# 指定记录慢查询的时间阀值
longquerytime=1

最后

以上就是追寻云朵为你收集整理的数据库SQL语句优化----必看推荐的全部内容，希望文章能够帮你解决数据库SQL语句优化----必看推荐所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。

本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。

本文分类：mysql
浏览次数：60 次浏览
发布日期：2023-10-24 16:16:19
本文链接：https://www.kaopuke.com/article/k-p-k_13_u_23_o_22_f1_13_z_6_5.html

数据库SQL语句优化----必看推荐

概述

一：基础数据准备

二：五百万数据插入

三：使用索引和不使用索引的比较

四：explain命令

五：走索引的情况和不走索引的情况（注意看key）

六：其它优化

找出项目中执行比较慢的sql语句

最后

评论列表共有 0 条评论

发表评论取消回复

数据库SQL语句优化----必看推荐

概述

一：基础数据准备

二：五百万数据插入

三：使用索引和不使用索引的比较

四：explain命令

五：走索引的情况和不走索引的情况 （ 注意看key）

六：其它优化

找出项目中执行比较慢的sql语句

最后

相关文章

评论列表共有 0 条评论

发表评论 取消回复

五：走索引的情况和不走索引的情况（注意看key）

发表评论取消回复