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

最新推荐文章于 2021-02-05 11:20:49 发布
最新推荐文章于 2021-02-05 11:20:49 发布
阅读量174 收藏 1
点赞数 1
分类专栏： mysql 文章标签： mysql
原文链接：https://www.toutiao.com/i6799099348975092231
版权

一：基础数据准备

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）

  1. 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)

  1. 范围查询走索引

  2. 模糊查询只有右前缀使用索引

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)
  1. 反向条件不走索引 != 、 <> 、 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;
  1. 对条件计算(使用函数或者算数表达式)不走索引
    使用函数计算不走索引，无论是对字段使用了函数还是值使用了函数都不走索引，解决办法通过应用程序计算好，将计算的结果传递给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)
  1. 查询时必须使用正确的数据类型
    如果索引字段是字符串类型，那么查询条件的值必须使用引号，否则不走索引
select * from tbl_user where username  = 'admin' 
--不要这样使用
select * from tbl_user where username = admin
  1. or 使用索引和不使用索引的情况
    or 只有两边都有索引才走索引，如果都没有或者只有一个是不走索引的
  2. 用union少用or
    尽量避免使用or，因为大部分or连接的两个条件同时都进行索引的情况几率比较小，应使用uninon代替，这样能走索引的走索引，不能走索引的就全表扫描。
  3. 能用union all就不用union
    union all 不去重复，union去重复，union使用了临时表，应尽量避免使用临时表
  4. 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)
  1. 分页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
那个少年~
关注
专栏目录
在MySQL中note是什么意思_MySQL 必知必会 note
weixin_36444022的博客
01-27 1004
concat.png顺序问题：from --> where --> group by --> having --> order by --> limit显示所有的数据库show databases;使用指定的数据库use |数据库名|;进入到指定的数据库，可以显示该数据库中所有的表show tables;显示某个表中的所有字段信息show columns from ...
MySQL：一文搞懂EXPLAIN命令
简单随风的博客
08-18 637
TIPS 本文基于MySQL 8.0编写，理论支持MySQL 5.0及更高版本。 EXPLAIN使用 explain可用来分析SQL的执行计划。格式如下： {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} {EXPLAIN | DES.
如何查看mysql警告信息_MySQL查询执行计划的警告信息辨析一例
weixin_36312271的博客
02-05 951
实例1 ：引入实例Step 1: 创建表CREATE TABLE `t1` (`id` SMALLINT(6) NOT NULL DEFAULT '99',`txt` TEXT,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8;INSERT INTO t1 (id,txt) VALUES (1,'a'), (2,'b'), (3,'c')...
关于MYSQL DML(UPDATE DELETE)中的子查询问题和ERROR 1093 (HY000)错误
cri5768的博客
12-29 353
从5.6开始MYSQL的子查询进行了大量的优化，5.5中只有EXISTS strategy，在5.7中包含如下: IN(=ANY) --Semi-join --table pullout(最快的,子查询条件为唯一键) ...
SQL语句里会导致性能问题的3个场景
weixin_33916256的博客
06-04 463
　　条件字段做函数操作：　　破坏了索引值的有序性，所以优化器决定放弃走树搜索功能，但不是放弃走索引，只是不能使用索引的快速定位功能，可以使用使用全索引扫描，当然也可能会直接遍历主键索引。　　mysql> desc select * from t where substr(age,1,1)='1';　　| id | select_type | table | partit...
关系型数据库及SQL语句-SQLSERVER数据库设计与实现210页.doc
最新发布
04-24
"关系型数据库及SQL语句-SQLSERVER数据库设计与实现" 关系型数据库是一种常用的数据库管理系统，它使用SQL语句来管理和操作数据。在本文中，我们将介绍关系型数据库的基本概念、SQL语句的使用以及SQLSERVER数据库...
SQL Server 数据库管理常用的SQL和T-SQL语句
09-11
在SQL Server数据库管理...以上就是SQL Server数据库管理中常见的SQL和T-SQL语句，它们对于数据库的日常维护、性能优化以及问题排查都有着重要作用。理解并熟练运用这些语句，能够有效地提升数据库管理的效率和质量。
ORACLE数据库SQL语句编写优化总结.rar
01-17
这份"ORACLE数据库SQL语句编写优化总结"文档很可能是对如何提高SQL查询性能、减少资源消耗以及提升系统整体效率的详细阐述。以下是根据标题和描述推测的一些关键知识点： 1. **查询优化基础**： - **索引的使用**...
易语言-access数据库SQL语句切换工具
06-25
《易语言-access数据库SQL语句切换工具》是针对Access数据库设计的一款实用工具，它主要功能是帮助用户在处理Access数据库时便捷地切换和执行SQL语句。Access数据库是Microsoft Office套件中的一个组件，主要用于...
SQL语句优化提高数据库性能
09-10
在数据库管理中，SQL语句优化是提升系统性能的关键环节。复杂的SQL语句往往会导致数据库执行效率低下，因此，保持SQL语句简洁是提高数据库性能的基础。以下是一些关于SQL语句优化的重要策略： 1. **减少表连接...
MySQL的explain
weixin_34261739的博客
08-29 77
explain命令的语法： {EXPLAIN} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED　　　　　　　　　　　　# 在5.7.18中已经过期，后期版本会被移除(本测试是基于5.7.18) | PARTITIONS　　　　　　　　　...
超级全面的MySQL优化面试解析
吒哇
04-15 660
概述 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问速度上 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢 数据是存放在磁盘上的，读写速度无法和内存相比 如何优化 设计数据库时：数据库表、字段的设计，存储引擎 利用好MySQL自身提供的功能，如索引等 横向扩展：MySQL集群、负载均衡、读写分离 SQL语...
Mysql下的SQL优化——隐式类型转换
程序猿的日常笔记
05-15 3433
前几天同事问了一个问题：在一个只有两个值的字段上加索引，在以这个字段为查询条件的时候是否会走索引。同事说不会，我说会走索引。同事不服，于是我们当场做了一个实验。结果居然没有走索引，满脸尴尬，难道真的是我错了？接下来我们分析下 实验表的情况如下 表结构 ： Field Type Null Key id int(11) NO PRI s...
关于比较字段使用不同字符集导致的索引失效问题
weixin_34128839的博客
09-23 195
最近遇到这么一个问题，两个表通过user_id关联 where a.user_id = b.user_id 且两个表中user_id均加了索引 table a key (user_id) table b unique key (user_id) 但实际查询很慢， 约3605ms。为什么这么慢呢？ 通过explain extended命令...
NOT NULL列用IS NULL也能查到数据？
weixin_34040079的博客
05-10 72
[测试表DDL CREATE TABLE t1 (id int(11) DEFAULT NULL,dt datetime NOT NULL DEFAULT '0000-00-00 00:00:00') ENGINE=InnoDB;插入测试数据： yejr@imysql.com> insert into t1(id) select 1; --- 不指定dt列的值 yejr@imysql.com...
子查询优化
weixin_34128237的博客
07-31 197
2019独角兽企业重金招聘Python工程师标准>>> ...
SQL Server数据库管理必备SQL与T-SQL命令总结
本文档主要介绍了在SQL Server...这些SQL和T-SQL语句覆盖了数据库管理和维护的多个关键领域，无论是日常运维还是故障排查，都是不可或缺的工具。熟练掌握这些命令能有效提升数据库管理员的效率和数据库系统的稳定性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值