数据库系统工程师考试2025数据库系统SQL优化与执行试题集

数据库系统工程师考试2025数据库系统SQL优化与执行试题集考试时间：______分钟总分：______分姓名：______一、SQL语句优化要求：请对以下SQL语句进行优化，分析其性能瓶颈，并给出优化后的SQL语句。1.原SQL语句：SELECT*FROMordersWHEREorder_dateBETWEEN'2021-01-01'AND'2021-12-31';2.原SQL语句：SELECTcustomer_id,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_id;3.原SQL语句：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalary>5000;4.原SQL语句：SELECT*FROMproductsWHEREpriceBETWEEN100AND500;5.原SQL语句：SELECT*FROMcustomersWHEREcity='NewYork'ORcity='LosAngeles';6.原SQL语句：SELECT*FROMordersWHEREcustomer_idIN(SELECTcustomer_idFROMcustomersWHEREcountry='USA');7.原SQL语句：SELECT*FROMordersWHEREorder_date='2021-01-01';8.原SQL语句：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalaryBETWEEN5000AND10000;9.原SQL语句：SELECT*FROMproductsWHEREprice>100ANDprice<500;10.原SQL语句：SELECT*FROMcustomersWHEREcityIN('NewYork','LosAngeles','Chicago');二、索引优化要求：请对以下表进行索引优化，分析其性能瓶颈，并给出优化后的索引创建语句。1.表结构：CREATETABLEemployees(employee_idINTPRIMARYKEY,nameVARCHAR(50),department_idINT,salaryDECIMAL(10,2));2.表结构：CREATETABLEorders(order_idINTPRIMARYKEY,customer_idINT,order_dateDATE,amountDECIMAL(10,2));3.表结构：CREATETABLEproducts(product_idINTPRIMARYKEY,nameVARCHAR(50),priceDECIMAL(10,2));4.表结构：CREATETABLEcustomers(customer_idINTPRIMARYKEY,nameVARCHAR(50),cityVARCHAR(50),countryVARCHAR(50));5.表结构：CREATETABLEdepartments(department_idINTPRIMARYKEY,nameVARCHAR(50));6.表结构：CREATETABLEsales(sale_idINTPRIMARYKEY,employee_idINT,product_idINT,quantityINT,sale_dateDATE);7.表结构：CREATETABLEsuppliers(supplier_idINTPRIMARYKEY,nameVARCHAR(50),countryVARCHAR(50));8.表结构：CREATETABLEshipments(shipment_idINTPRIMARYKEY,supplier_idINT,product_idINT,quantityINT,shipment_dateDATE);9.表结构：CREATETABLEinvoices(invoice_idINTPRIMARYKEY,customer_idINT,order_idINT,invoice_dateDATE,total_amountDECIMAL(10,2));10.表结构：CREATETABLEpayments(payment_idINTPRIMARYKEY,invoice_idINT,payment_dateDATE,amountDECIMAL(10,2));三、查询优化要求：请对以下查询进行优化，分析其性能瓶颈，并给出优化后的查询语句。1.原查询语句：SELECT,ASdepartment_nameFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREe.salary>5000;2.原查询语句：SELECT,SUM(o.amount)AStotal_amountFROMproductspJOINordersoONduct_id=duct_idGROUPBY;3.原查询语句：SELECT,COUNT(o.order_id)AStotal_ordersFROMcustomerscJOINordersoONc.customer_id=o.customer_idGROUPBY;4.原查询语句：SELECT,COUNT(sh.shipment_id)AStotal_shipmentsFROMsupplierssJOINshipmentsshONs.supplier_id=sh.supplier_idGROUPBY;5.原查询语句：SELECTi.invoice_id,i.invoice_date,AScustomer_nameFROMinvoicesiJOINcustomerscONi.customer_id=c.customer_idWHEREi.total_amount>1000;6.原查询语句：SELECT,SUM(p.quantity)AStotal_quantityFROMproductspJOINshipmentsshONduct_id=duct_idGROUPBY;7.原查询语句：SELECT,ASdepartment_nameFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREe.salaryBETWEEN5000AND10000;8.原查询语句：SELECT,AVG(p.price)ASaverage_priceFROMproductspJOINordersoONduct_id=duct_idGROUPBY;9.原查询语句：SELECT,COUNT(p.payment_id)AStotal_paymentsFROMcustomerscJOINpaymentspONc.customer_id=p.customer_idGROUPBY;10.原查询语句：SELECT,SUM(sh.quantity)AStotal_shipmentsFROMsupplierssJOINshipmentsshONs.supplier_id=sh.supplier_idGROUPBY;四、存储过程优化要求：请对以下存储过程进行优化，分析其性能瓶颈，并给出优化后的存储过程代码。```sqlDELIMITER//CREATEPROCEDUREGetCustomerOrders(INcustomer_idINT)BEGINSELECTo.order_id,o.order_date,o.amountFROMordersoJOINcustomerscONo.customer_id=c.customer_idWHEREc.customer_id=customer_id;END//DELIMITER;```五、事务处理要求：请对以下事务处理进行优化，分析其性能瓶颈，并给出优化后的事务处理代码。```sqlSTARTTRANSACTION;INSERTINTOemployees(employee_id,name,department_id,salary)VALUES(1,'JohnDoe',10,5000);INSERTINTOdepartments(department_id,name)VALUES(10,'Marketing');UPDATEdepartmentsSETname='Sales'WHEREdepartment_id=10;COMMIT;```六、视图优化要求：请对以下视图进行优化，分析其性能瓶颈，并给出优化后的视图创建语句。```sqlCREATEVIEWCustomerOrdersViewASSELECTAScustomer_name,o.order_id,o.order_date,o.amountFROMcustomerscJOINordersoONc.customer_id=o.customer_id;```本次试卷答案如下：一、SQL语句优化1.原SQL语句：SELECT*FROMordersWHEREorder_dateBETWEEN'2021-01-01'AND'2021-12-31';解析：原SQL语句没有性能瓶颈，但可以优化查询条件，使用范围索引来提高查询效率。优化后：SELECT*FROMordersWHEREorder_date>='2021-01-01'ANDorder_date<='2021-12-31';2.原SQL语句：SELECTcustomer_id,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_id;解析：原SQL语句使用了聚合函数和分组，但未指定索引，可以添加索引以提高查询效率。优化后：SELECTcustomer_id,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_id;添加索引：CREATEINDEXidx_customer_idONorders(customer_id);3.原SQL语句：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalary>5000;解析：原SQL语句中，salary字段未建立索引，可以通过添加索引来优化查询。优化后：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalary>5000;添加索引：CREATEINDEXidx_department_salaryONemployees(department_id,salary);4.原SQL语句：SELECT*FROMproductsWHEREpriceBETWEEN100AND500;解析：原SQL语句没有性能瓶颈，但可以优化查询条件，使用范围索引来提高查询效率。优化后：SELECT*FROMproductsWHEREprice>=100ANDprice<=500;5.原SQL语句：SELECT*FROMcustomersWHEREcity='NewYork'ORcity='LosAngeles';解析：原SQL语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT*FROMcustomersWHEREcity='NewYork'ORcity='LosAngeles';添加索引：CREATEINDEXidx_cityONcustomers(city);6.原SQL语句：SELECT*FROMordersWHEREcustomer_idIN(SELECTcustomer_idFROMcustomersWHEREcountry='USA');解析：原SQL语句中，子查询会导致性能问题，可以通过添加索引或使用连接查询来优化。优化后：SELECT*FROMordersoJOINcustomerscONo.customer_id=c.customer_idWHEREc.country='USA';7.原SQL语句：SELECT*FROMordersWHEREorder_date='2021-01-01';解析：原SQL语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT*FROMordersWHEREorder_date='2021-01-01';添加索引：CREATEINDEXidx_order_dateONorders(order_date);8.原SQL语句：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalaryBETWEEN5000AND10000;解析：原SQL语句中，salary字段未建立索引，可以通过添加索引来优化查询。优化后：SELECT*FROMemployeesWHEREdepartment_id=10ANDsalaryBETWEEN5000AND10000;添加索引：CREATEINDEXidx_department_salaryONemployees(department_id,salary);9.原SQL语句：SELECT*FROMproductsWHEREprice>100ANDprice<500;解析：原SQL语句没有性能瓶颈，但可以优化查询条件，使用范围索引来提高查询效率。优化后：SELECT*FROMproductsWHEREprice>100ANDprice<500;10.原SQL语句：SELECT*FROMcustomersWHEREcityIN('NewYork','LosAngeles','Chicago');解析：原SQL语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT*FROMcustomersWHEREcityIN('NewYork','LosAngeles','Chicago');添加索引：CREATEINDEXidx_cityONcustomers(city);二、索引优化1.表结构：CREATETABLEemployees(employee_idINTPRIMARYKEY,nameVARCHAR(50),department_idINT,salaryDECIMAL(10,2));解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_department_salaryONemployees(department_id,salary);2.表结构：CREATETABLEorders(order_idINTPRIMARYKEY,customer_idINT,order_dateDATE,amountDECIMAL(10,2));解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_customer_order_dateONorders(customer_id,order_date);3.表结构：CREATETABLEproducts(product_idINTPRIMARYKEY,nameVARCHAR(50),priceDECIMAL(10,2));解析：该表结构已包含主键索引，但可以添加索引以提高查询效率。优化后：CREATEINDEXidx_priceONproducts(price);4.表结构：CREATETABLEcustomers(customer_idINTPRIMARYKEY,nameVARCHAR(50),cityVARCHAR(50),countryVARCHAR(50));解析：该表结构已包含主键索引，但可以添加索引以提高查询效率。优化后：CREATEINDEXidx_city_countryONcustomers(city,country);5.表结构：CREATETABLEdepartments(department_idINTPRIMARYKEY,nameVARCHAR(50));解析：该表结构已包含主键索引，无需添加其他索引。6.表结构：CREATETABLEsales(sale_idINTPRIMARYKEY,employee_idINT,product_idINT,quantityINT,sale_dateDATE);解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_employee_product_sale_dateONsales(employee_id,product_id,sale_date);7.表结构：CREATETABLEsuppliers(supplier_idINTPRIMARYKEY,nameVARCHAR(50),countryVARCHAR(50));解析：该表结构已包含主键索引，但可以添加索引以提高查询效率。优化后：CREATEINDEXidx_countryONsuppliers(country);8.表结构：CREATETABLEshipments(shipment_idINTPRIMARYKEY,supplier_idINT,product_idINT,quantityINT,shipment_dateDATE);解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_supplier_product_shipment_dateONshipments(supplier_id,product_id,shipment_date);9.表结构：CREATETABLEinvoices(invoice_idINTPRIMARYKEY,customer_idINT,order_idINT,invoice_dateDATE,total_amountDECIMAL(10,2));解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_customer_order_invoice_dateONinvoices(customer_id,order_id,invoice_date);10.表结构：CREATETABLEpayments(payment_idINTPRIMARYKEY,invoice_idINT,payment_dateDATE,amountDECIMAL(10,2));解析：该表结构已包含主键索引，但可以添加复合索引以提高查询效率。优化后：CREATEINDEXidx_invoice_payment_dateONpayments(invoice_id,payment_date);三、查询优化1.原查询语句：SELECT,ASdepartment_nameFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREe.salary>5000;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT,ASdepartment_nameFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREe.salary>5000;添加索引：CREATEINDEXidx_salary_department_idONemployees(salary,department_id);2.原查询语句：SELECT,SUM(o.amount)AStotal_amountFROMproductspJOINordersoONduct_id=duct_idGROUPBY;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT,SUM(o.amount)AStotal_amountFROMproductspJOINordersoONduct_id=duct_idGROUPBY;添加索引：CREATEINDEXidx_product_idONorders(product_id);3.原查询语句：SELECT,COUNT(o.order_id)AStotal_ordersFROMcustomerscJOINordersoONc.customer_id=o.customer_idGROUPBY;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT,COUNT(o.order_id)AStotal_ordersFROMcustomerscJOINordersoONc.customer_id=o.customer_idGROUPBY;添加索引：CREATEINDEXidx_customer_idONorders(customer_id);4.原查询语句：SELECT,COUNT(sh.shipment_id)AStotal_shipmentsFROMsupplierssJOINshipmentsshONs.supplier_id=sh.supplier_idGROUPBY;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT,COUNT(sh.shipment_id)AStotal_shipmentsFROMsupplierssJOINshipmentsshONs.supplier_id=sh.supplier_idGROUPBY;添加索引：CREATEINDEXidx_supplier_idONshipments(supplier_id);5.原查询语句：SELECTi.invoice_id,i.invoice_date,AScustomer_nameFROMinvoicesiJOINcustomerscONi.customer_id=c.customer_idWHEREi.total_amount>1000;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECTi.invoice_id,i.invoice_date,AScustomer_nameFROMinvoicesiJOINcustomerscONi.customer_id=c.customer_idWHEREi.total_amount>1000;添加索引：CREATEINDEXidx_customer_id_total_amountONinvoices(customer_id,total_amount);6.原查询语句：SELECT,SUM(p.quantity)AStotal_quantityFROMproductspJOINshipmentsshONduct_id=duct_idGROUPBY;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SELECT,SUM(p.quantity)AStotal_quantityFROMproductspJOINshipmentsshONduct_id=duct_idGROUPBY;添加索引：CREATEINDEXidx_product_idONshipments(product_id);7.原查询语句：SELECT,ASdepartment_nameFROMemployeeseJOINdepartmentsdONe.department_id=d.department_idWHEREe.salaryBETWEEN5000AND10000;解析：原查询语句没有性能瓶颈，但可以使用索引来提高查询效率。优化后：SE