1、开门见山,问题所在
sql语句性能达不到你的要求,执行效率让你忍无可忍,一般会是下面几种情况。
- 网速不给力,不稳定。
- 服务器内存不够,或者SQL 被分配的内存不够。
- sql语句设计不合理
- 没有相应的索引,索引不合理
- 表数据过大没有有效的分区设计
- 数据库设计太2,存在大量的数据冗余
- …
那么我们如何给找出来导致性能慢的的原因呢?
- 首先你要知道是否跟sql语句有关,确保不是机器开不开机,服务器硬件配置太差
- 接着采用sql性能检测工具,分析出sql慢的相关语句,就是执行时间过长,占用系统资源,cpu过多的
- 然后对慢的sql进行优化,避免一些不合理的sql语句,取暂优sql
- 数据太多的表,要分区,缩小查找范围
2、SQL语句优化的原因
-
SQL语句是对数据库(数据) 进行操作的惟一途径,应用程序的执行最终要归结为SQL语句的执行,SQL语句的效率对数据库系统的性能起到了决定性的作用。
-
SQL语句消耗了70%~90%的数据库资源。
-
SQL语句独立于程序设计逻辑,对SQL语句进行优化不会影响程序逻辑,相对于对程序源代码的优化,对SQL语句的优化在时间成本和风险上的代价都很低。
-
SQL语句可以有不同的写法,不同的写法在性能上的差异可能很大。
-
SQL语句易学,难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关,不存在普遍适用的规律来提升性能。
3、优化数据库的思想
-
关键字段建立索引。
-
使用存储过程,它使SQL变得更加灵活和高效。
-
备份数据库和清除垃圾数据。
-
SQL语句语法的优化。
-
清理删除日志。
4、SQL语句优化的原则
不要以为只有SELECT语句是查询。实际上,带有任何WHERE条件的 DML(INSERT、UPDATE、DELETE)语句中都包含查询要求。
SQL语句同其它语言的语句不一样,它是非过程化(non-procedural)的语句,即当你要取数据时,不需要告诉数据库通过何种途径去取数据,如到底是通过索引取数据,还是应该将表中的每行数据都取出来,然后再通过一一比较的方式取数据(即全表扫描)。
为了实现一个查询,内核必须为每个查询定制一个查询策略,或为取出符合条件的数据生成一个执行计划(execution plan)。典型的,对于同一个查询,可能有几个执行计划都符合要求,都能得到符合条件的数据。例如,参与连接的表可以有多种不同的连接方法,这取决于连接条件和优化器采用的连接方法。为了在多个执行计划中选择最优的执行计划,优化器必须使用一些实际的指标来衡量每个执行计划使用的资源(I/0次数、 CPU等),这些资源也就是我们所说的代价(cost)。如果一个执行计划使用的资源多,我们就说使用执行计划的代价大。以执行计划的代价大小作为衡量标 准,优化器选择代价最小的执行计划作为真正执行该查询的执行计划,并抛弃其它的执行计划。
5、具体优化策略
以下测试以mysql数据库为例,版本如下:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22 |
+-----------+
1 row in set (0.00 sec)
所采用的表名以及数据量如下:
mysql> SELECT TABLE_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='ZHANG' ORDER BY TABLE_ROWS DESC;
+--------------+------------+
| TABLE_NAME | TABLE_ROWS |
+--------------+------------+
| salaries | 2663301 |
| titles | 415579 |
| dept_emp | 310558 |
| employees | 279401 |
| dept_manager | 24 |
| departments | 9 |
+--------------+------------+
6 rows in set (0.00 sec)
各表结构如下:
salaries
mysql> desc salaries;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| salary | int(11) | NO | MUL | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
titles
mysql> desc titles;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| title | varchar(50) | NO | PRI | NULL | |
| from_date | date | NO | PRI | NULL | |
| to_date | date | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
dept_emp
mysql> desc dept_emp;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| dept_no | char(4) | NO | PRI | NULL | |
| from_date | date | NO | | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
employees
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | MUL | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | MUL | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql>
dept_manager
mysql> desc dept_manager;
+-----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| dept_no | char(4) | NO | PRI | NULL | |
| from_date | date | NO | | NULL | |
| to_date | date | NO | | NULL | |
+-----------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
departments
mysql> desc departments;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dept_no | char(4) | NO | PRI | NULL | |
| dept_name | varchar(40) | NO | UNI | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
4.1、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
4.1.1、单字段索引
创建索引之前:
mysql> select count(*) from salaries where from_date >= '2000-01-01';
+----------+
| count(*) |
+----------+
| 644349 |
+----------+
1 row in set (0.14 sec)
创建索引:
ALTER TABLE salaries ADD INDEX (from_date);
创建索引之后:
mysql> select count(*) from salaries where from_date >= '2000-01-01';
+----------+
| count(*) |
+----------+
| 644349 |
+----------+
1 row in set (0.12 sec)
4.1.2、双字段复合索引
创建索引之前:
mysql> select count(*) from salaries where from_date >= '1990-01-01' and to_date <= '2020-01-01';
+----------+
| count(*) |
+----------+
| 2317336 |
+----------+
1 row in set (1.10 sec)
mysql>
创建索引:
ALTER TABLE salaries ADD INDEX (from_date, to_date);
创建索引之后:
mysql> select count(*) from salaries where from_date >= '1990-01-01' and to_date <= '2020-01-01';
+----------+
| count(*) |
+----------+
| 2317336 |
+----------+
1 row in set (0.62 sec)
mysql>
4.2、尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描
where子句没有对字段进行null值判断
mysql> select count(*) from salaries;
+----------+
| count(*) |
+----------+
| 2843993 |
+----------+
1 row in set (0.34 sec)
```sql
where子句对字段进行null值判断
```sql
mysql> select count(*) from salaries where salary is not null;
+----------+
| count(*) |
+----------+
| 2843993 |
+----------+
1 row in set (0.41 sec)
最好不要给数据库留NULL,尽可能的使用 NOT NULL填充数据库,可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from dual where num = 0
4.3、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描
没有使用 != 或 <> 操作符
mysql> select count(*) from employees where gender = 'M';
+----------+
| count(*) |
+----------+
| 179973 |
+----------+
1 row in set (0.03 sec)
使用 != 或 <> 操作符
mysql> select count(*) from employees where gender <> 'F';
+----------+
| count(*) |
+----------+
| 179973 |
+----------+
1 row in set (0.04 sec)
4.4、字段加运算符不会使用索引。所以尽量把运算放在数值上
字段加运算,不使用索引
mysql> select count(*) from salaries where salary + 10000 >= '80000';
+----------+
| count(*) |
+----------+
| 907731 |
+----------+
1 row in set (0.42 sec)
mysql>
运算放在值上,使用索引
mysql> select count(*) from salaries where salary >= '70000';
+----------+
| count(*) |
+----------+
| 907731 |
+----------+
1 row in set (0.14 sec)
mysql>
字段加运算,不使用索引
mysql> select count(*) from salaries where salary * 2 = '80000';
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.40 sec)
mysql>
运算放在值上,使用索引
mysql> select count(*) from salaries where salary = '80000'/2;
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.02 sec)
mysql>
4.5、应尽量避免在 where 子句中使用 or 来连接条件, 否则将导致引擎放弃使用索引而进行全表扫描, 可以使用UNION合并查询
注:这个是在有索引的前提下,没索引的话,貌似前者更优秀,因为都是全表扫描。前者扫描一次,后者扫描两次。
4.5.1、有索引
where中使用or来连接条件:
mysql> select * from salaries where from_date >= '2002-08-01' or from_date <= '1985-01-01';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10017 | 99651 | 2002-08-01 | 9999-01-01 |
| 12494 | 72810 | 2002-08-01 | 9999-01-01 |
*********************************************
| 499774 | 87619 | 2002-08-01 | 9999-01-01 |
| 499902 | 100339 | 2002-08-01 | 9999-01-01 |
+--------+--------+------------+------------+
682 rows in set (0.73 sec)
使用union all
mysql> select * from salaries where from_date >= '2002-08-01'
-> union all
-> select * from salaries where from_date <= '1985-01-01';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10017 | 99651 | 2002-08-01 | 9999-01-01 |
| 12494 | 72810 | 2002-08-01 | 9999-01-01 |
*********************************************
| 499774 | 87619 | 2002-08-01 | 9999-01-01 |
| 499902 | 100339 | 2002-08-01 | 9999-01-01 |
+--------+--------+------------+------------+
682 rows in set (0.00 sec)
4.5.2、无索引
删掉索引进行测试:
drop index from_date on salaries ;
where中使用or来连接条件:
mysql> select * from salaries where from_date >= '2002-08-01' or from_date <= '1985-01-01';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10017 | 99651 | 2002-08-01 | 9999-01-01 |
| 12494 | 72810 | 2002-08-01 | 9999-01-01 |
*********************************************
| 499774 | 87619 | 2002-08-01 | 9999-01-01 |
| 499902 | 100339 | 2002-08-01 | 9999-01-01 |
+--------+--------+------------+------------+
682 rows in set (0.93 sec)
使用union all
mysql> select * from salaries where from_date >= '2002-08-01'
-> union all
-> select * from salaries where from_date <= '1985-01-01';
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10017 | 99651 | 2002-08-01 | 9999-01-01 |
| 12494 | 72810 | 2002-08-01 | 9999-01-01 |
*********************************************
| 499774 | 87619 | 2002-08-01 | 9999-01-01 |
| 499902 | 100339 | 2002-08-01 | 9999-01-01 |
+--------+--------+------------+------------+
682 rows in set (1.68 sec)
4.6、in 和 not in 也要慎用,否则会导致全表扫描,对于连续的数值,能用 between 就不要用 in 了
使用 in
mysql> select count(*) from salaries where salary in ('47199','47200','47201','47202','47203','47204','47205','47206','47207','47208','47209','47210','47211','47212','47213','47214','47215','47216','47217','47218','47219','47220','47221','47222','47223','47224','47225','47226');
+----------+
| count(*) |
+----------+
| 1832 |
+----------+
1 row in set (0.43 sec)
使用 between
mysql> select count(*) from salaries where salary between 47199 and 47226;
+----------+
| count(*) |
+----------+
| 1832 |
+----------+
1 row in set (0.42 sec)
4.7、sql语句的where查询条件,对字段进行表达式操作,也会导致引擎放弃使用索引而进行全表扫描查询。
不对字段进行表达式操作
mysql> select count(*) from salaries where salary = '80000'/2;
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.01 sec)
对字段进行表达式操作
mysql> select count(*) from salaries where salary * 2 = '80000';
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.40 sec)
mysql>
4.8、sql语句的where查询条件,对字段进行函数操作,也会导致引擎放弃使用索引而进行全表扫描查询。
对字段进行函数操作
mysql> select count(*) from salaries where SUBSTR(salary,1,3)='471';
+----------+
| count(*) |
+----------+
| 6540 |
+----------+
1 row in set (0.50 sec)
mysql>
优化,使用 like
mysql> select count(*) from salaries where salary like '471%';
+----------+
| count(*) |
+----------+
| 6540 |
+----------+
1 row in set (0.43 sec)
mysql>
4.9、sql语句用大写,因为oracle 总是先解析sql语句,把小写的字母转换成大写的再执行。
小写
mysql> select count(*) from salaries where from_date >= '2002-08-01' or from_date <= '1985-01-01';
+----------+
| count(*) |
+----------+
| 682 |
+----------+
1 row in set (0.69 sec)
mysql>
大写
mysql> SELECT COUNT(*) FROM SALARIES WHERE FROM_DATE >= '2002-08-01' OR FROM_DATE <= '1985-01-01';
+----------+
| count(*) |
+----------+
| 682 |
+----------+
1 row in set (0.58 sec)
mysql>
4.10、多张数据表查询数据,使用inner join,left/right join来代替子查询,可以提高查询的效率。
子查询
mysql> select count(*) from salaries where emp_no in (select emp_no from employees);
+----------+
| count(*) |
+----------+
| 2843993 |
+----------+
1 row in set (2.02 sec)
join 查询
mysql> select count(*) from salaries a left join employees b on a.emp_no = b.emp_no;
+----------+
| count(*) |
+----------+
| 2843993 |
+----------+
1 row in set (3.33 sec)
注意:执行计划中显示子查询是被转换为join语句
mysql> explain select count(*) from salaries where emp_no in (select emp_no from employees);
+----+-------------+-----------+------------+-------+---------------+---------+---------+------------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------------------------+--------+----------+-------------+
| 1 | SIMPLE | employees | NULL | index | PRIMARY | PRIMARY | 4 | NULL | 279401 | 100.00 | Using index |
| 1 | SIMPLE | salaries | NULL | ref | PRIMARY | PRIMARY | 4 | zhang.employees.emp_no | 9 | 100.00 | Using index |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------------------------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select count(0) AS `count(*)` from `zhang`.`employees` join `zhang`.`salaries` where (`zhang`.`salaries`.`emp_no` = `zhang`.`employees`.`emp_no`) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
4.11、使用like时注意,like ‘%L%’ 不使用索引,like ‘L%’ 使用索引:
like ‘%888%’ 不使用索引
mysql> select count(*) from salaries where salary like '%888%';
+----------+
| count(*) |
+----------+
| 6870 |
+----------+
1 row in set (0.52 sec)
like ‘888%’ 使用索引
mysql> select count(*) from salaries where salary like '888%';
+----------+
| count(*) |
+----------+
| 2053 |
+----------+
1 row in set (0.45 sec)
mysql>
4.12、如果使用数字作为字符,则数字需要加引号,否则mysql会自动在列上加数据类型转换函数
使用索引:where phone=’15234940672’
mysql> select count(*) from salaries where salary >= '80000';
+----------+
| count(*) |
+----------+
| 491494 |
+----------+
1 row in set (0.08 sec)
mysql>
不使用索引:where phone=15234940672
mysql> select count(*) from salaries where salary >= 80000;
+----------+
| count(*) |
+----------+
| 491494 |
+----------+
1 row in set (0.08 sec)
mysql>
4.13、如果在 where 子句中使用参数,也会导致全表扫描
因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描
不使用索引:
mysql> select count(*) from salaries where salary = @num;
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.02 sec)
mysql>
可以改为强制查询使用索引:
mysql> select count(*) from salaries force index(salary) where salary = @num:=40000;
+----------+
| count(*) |
+----------+
| 95369 |
+----------+
1 row in set (0.01 sec)
mysql>
4.14、任何地方都不要使用 select * from t
select * from table
mysql> select * from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1987-01-01 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
*********************************************
| 10001 | 76884 | 1995-06-24 | 1996-06-23 |
| 10001 | 80013 | 1996-06-23 | 1997-06-23 |
| 10001 | 81025 | 1997-06-23 | 1998-06-23 |
| 10001 | 81097 | 1998-06-23 | 1999-06-23 |
| 10001 | 84917 | 1999-06-23 | 2000-06-22 |
+--------+--------+------------+------------+
2843993 rows in set (1.40 sec)
mysql>
select a,b,c from table
mysql> select emp_no,salary,from_date,to_date from salaries;
+--------+--------+------------+------------+
| emp_no | salary | from_date | to_date |
+--------+--------+------------+------------+
| 10001 | 60117 | 1987-01-01 | 1987-06-26 |
| 10001 | 62102 | 1987-06-26 | 1988-06-25 |
| 10001 | 66596 | 1989-06-25 | 1990-06-25 |
*********************************************
| 10001 | 76884 | 1995-06-24 | 1996-06-23 |
| 10001 | 80013 | 1996-06-23 | 1997-06-23 |
| 10001 | 81025 | 1997-06-23 | 1998-06-23 |
| 10001 | 81097 | 1998-06-23 | 1999-06-23 |
| 10001 | 84917 | 1999-06-23 | 2000-06-22 |
+--------+--------+------------+------------+
2843993 rows in set (1.16 sec)
mysql>
4.15、笛卡尔积(交叉连接)
如果A表有n条记录,B表有m条记录,笛卡尔积产生的结果就会产生n*m条记录。
在MySQL中可以为 CROSS JOIN 或者省略 CROSS 即 JOIN,或者直接用 from 多表用逗号分开。
select count(*) from employees cross join salaries;
select count(*) from employees join salaries;
select count(*) from employees,salaries;
不用 on table1.key1 = table2.key2 得出的结果是table1的记录数*table2的记录数,;
如果用on连接,得出的和inner join的结果一样(所以在有on的情况下,inner joijn、cross join、 join(推荐、会自动用小的表作为驱动表)结果一样)。
4.16、truncate 与 delete
truncate只作用于表,主要用于删除一张表中的所有数据,其作用与不带任何条件的delete一样,且都不会破坏表结构。但是,truncate不走事务,不会锁表;不会产生日志,不写入日志文件;truncate执行完之后,立马释放磁盘空间。
truncate会清空表中的所有行,但表结构及其约束、索引不会被改变,但是会使表和索引所占用的空间会恢复到初始大小。最后一点,它还会重置表的自增值。这样的效果,让我们联想起,它是不是像是把一张表drop之后,又把表重新create了一遍?答案确实如此。
事务性与非事务性
所有的DML语句操作都会将数据放到rollback中,事务提交后才生效,所以在事务提交之前的这段时间,会锁表,从而占用一定的磁盘空间。
而DDL语句操作后即刻生效,它是不会将数据放到rollback中的,执行完成则意味着结束,不能被回滚,所以操作完成后就会释放占用的磁盘空间。
delete属于DML语句,truncate属于DDL语句。
日志记录
delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
delete记录日志比truncate更耗时。
truncate的安全性
truncate表是高危操作,特别是在生产环境要更加小心。毕竟它无法通过binlog回滚,一旦执行就会清空所有数据且执行速度很快,所以执行truncate前一定要反复检查确认,最好备份下表数据。
4.17、函数索引
MySQL5.7版本中不支持函数索引,因此 遇到函数索引的时候需要进行修改,否则即使查询的字段上有索引,执行时也无法使用索引而进行全表扫描,数据量大的表查询时间会比较长;
MySQL8.0的索引特性增加了函数索引。其实MySQL5.7中推出了虚拟列的功能,而MySQL8.0的函数索引也是依据虚拟列来实现的。将上述的案例在MySQL8.0中实现情况如下文所述。
alter table tablename add key indexname((date(create_time)));
Oracle 支持函数索引。
create index indexname on tablename(substr(fileld,0,2)) online nologging;