6.4 编写高效的数据查询

学习目标：

1.会分析SQL的执行计划

一、思考问题：如何优化数据访问？

• 影响查询性能的主要原因？

•向数据库请求不需要的数据

•（1）查询不需要的记录

•（2）多表关联时返回全部列

•（3）总是取出全部列

•查询的开销

•（1）响应时间

二、优化数据访问

实例：查询会员蔡静购买的所有商品信息。

SELECT *
FROM users join cart USING(uid)  
                       join goods USING(gid)
WHERE uname = '蔡静' ;

SELECT goods.*
FROM users join cart USING(uid) 
                      join goods USING(gid)
WHERE uname = '蔡静' ;

问：哪种方案执行效率更高？

方法二，按需取材

三、SQL的执行计划

•要编写高效的查询语句，需要了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询。

学习提示：执行该语句，可以分析EXPLAIN后SELECT语句的执行情况，并且能够分析出所查询表的一些特征。

实例：使用EXPLAIN语句分析查询users_bg表的执行计划。

EXPLAIN SELECT * FROM users_bg WHERE card = 'HS395964JA39';

•查询分析器的查询类型:

EXPLAIN SELECT g.gname,g.gprice,c.cnum # 查询必要列
                FROM users u
                NATURAL JOIN cart c
                NATURAL JOIN goods g
                WHERE uname = '段湘林';

四、优化查询

实例：为表users_bg中card列建立索引，并再次查看执行计划和执行时间。

-- 删除索引
DROP INDEX idx_name on user_bg;
-- 创建索引
CREATE INDEX idx_name on user_bg(uname);
-- 没有uname索引时，时间: 0.071s
SELECT *
FROM user_bg
WHERE uname = '段湘林';
-- 使用索引，再次执行查询 时间: 0.001s
SELECT *
FROM user_bg
WHERE uname = '段湘林';

结论：在大数据查询时，有索引时，查询效率大大高于无索引的查询。

•影响查询速度的原因除是否添加索引外，获取记录的页数也会影响查询性能。当要进行分页操作时，通常会使用LIMIT子句实现。但是当分页操作要求偏移量非常大的时候，代价就非常高。

— 使用limit子句分页

SELECT * FROM user_bg -- 时间: 0.031s
LIMIT 20000,10;

SELECT *  -- 时间: 0.002s
FROM user_bg
WHERE uid > 20000  
LIMIT 10;

•SELECT查询的数据列从索引中就能够取得，不必读取数据行。即，查询列要被所建立的索引覆盖，索引的字段不仅仅包含查询的列，还包含查询条件、排序等。

SELECT  id, card FROM  users_bg WHERE id > 500000 ORDER BY card LIMIT 10 ;

优化方案：

SELECT card, age FROM  users_bg ORDER BY card LIMIT 1000 ;

五、实践任务

•（1）创建索引、查看索引和维护索引。

•（2）创建视图、管理和维护视图以及使用可更新视图。

•（3）写出各种高效数据查询的方法。

•实践内容

•（1）使用Navicat图形工具在onlinedb.goodstype表的tName列上创建一个为IX_tName的普通索引名。

•（2）使用SQL语句在onlinedb.goods表的gdCode和gdName列上创建一个名为IX_gdCN的复合索引。

•（3）分别使用SHOW CREATE TABLE命令和SHOW INDEX FROM/SHOW KEYS FROM命令查看（2）中所创建的索引IX_gdCN的相关信息。

•（4）使用SQL语句删除（1）和（2）创建的索引。

课堂小结：

1. 优化查询的三种方法：

（1）建立条件索引优化查询；（2）充分使用主键索引作为筛选条件，优化分页查询；（3）覆盖索引优化查询，要注意第一索引没有使用时，其他列索引无效。

1. 在不常更新的列上建索引更加有效，索引不宜多建，会影响数据维护的效率。