数据库查询优化

程序员文章站 2022-06-02 12:27:05

...

1.如何构造大量数据用于数据库的操作？
在mysql性能测试中需要产生大量数据，数据生成由软件TestDataBuilder完成；
具体的使用细节参考：
http://testdatabuilder.sourceforge.net/
2.数据为什么需要查询优化？
问题：减少数据查询所消耗的时间，提高查询效率；
出发点：避免全表查询(尤其在使用笛卡尔积进行表连接时)
分析会导致全表扫描的情况：
①对某个字段进行null判断；
②在where中加入!=或<>判断；
③where条件使用or连接(或使用in/not in)；
④对某个字段的模糊查询；
⑤在where子句中使用参数；
⑥在where子句中出现表达式运算或函数运算；
3.如何进行查询优化？
建立索引
1）什么是索引？
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息；
在查询特定数据时，只需要查询出对应的记录，避免全表扫描；
2）索引分类(从索引设计的范围)
单列索引:(普通、唯一、主键)
语法：CREATE INDEX index_name ON table_name (column_name);
唯一索引:CREATE UNIQUE INDEX index_name on table_name (column_name);
组合索引:
CREATE INDEX index_name on table_name (column1, column2);
全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，用于关键字查询
FULLTEXT INDEX FullTxtIdx(info)
空间索引：空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。
SPATIAL INDEX spatIdx(g)
3）索引基本操作
①查询表上使用的索引：SHOW INDEX FROM dept_emp
②删除索引：DROP INDEX index_name;
4.数据库查询性能分析
1）explain
EXPLAIN SELECT* FROM dept_emp
2）profiling
SET profiling=1 #打开开关
SELECT* FROM dept_emp
SHOW profiles;#显示查询结果
5.性能分析
数据：https://download.csdn.net/download/loneleaf1/9271903
查询：查询每个部门(departments)的编号(dept_no),名称(dept_name),在该部门工作过的雇员(employees)人数,最低工资(salary),平均工资,最高工资及工资总额；

EXPLAIN
SELECT departments.dept_no,departments.dept_name,COUNT(dept_emp.emp_no),MIN(salaries.salary),
AVG(salaries.salary),MAX(salaries.salary),SUM(salaries.salary)
FROM departments,dept_emp,salaries
WHERE departments.dept_no = dept_emp.dept_no AND dept_emp.emp_no = salaries.emp_no
GROUP BY departments.dept_no

explain分析：
id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  dept_emp    index   PRIMARY,emp_no,dept_no  emp_no  4   \N  331883  Using index; Using temporary; Using filesort
1   SIMPLE  departments eq_ref  PRIMARY PRIMARY 12  employees.dept_emp.dept_no  1   
1   SIMPLE  salaries    ref PRIMARY,emp_no  PRIMARY 4   employees.dept_emp.emp_no   4

profiling分析：
数据库查询优化
6.使用索引
1）未使用索引

SET profiling=1
SELECT *FROM titles WHERE to_date BETWEEN '1999-01-01' AND '2005-01-01'
SHOW profiles;

数据库查询优化
2）使用索引

CREATE INDEX to_date_index ON titles(to_date)
SET profiling=1
SELECT *FROM titles WHERE to_date_index BETWEEN '1999-01-01' AND '2005-01-01'
SHOW profiles;

数据库查询优化

7.存储过程简单使用

#创建数据插入的存储过程
DELIMITER //
CREATE PROCEDURE add_empInfo(emp_num INT,dept_num CHAR(4),date1 DATE,date2 DATE)
BEGIN
INSERT INTO dept_emp
(emp_no,dept_no,from_date,to_date)
VALUES
(emp_num,dept_num,date1,date2);
END//
DELIMITER ;
#调用存储过程，完成数据插入
CALL add_empInfo(10010,'d001','2001-01-01','2001-02-02');