數據庫系統工程師考試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