1、SQL索引优化
使用explain查询SQL的执行计划
explain返回各列的含义
- table：显示这一行的数据是关于哪张表的
- type：这是重要的列，显示连接使用了何种类型。从最好到最差的连续类型为const,eq_reg,ref,range,index和ALL(没有where从句，表扫描)。
- possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引
- key：实际使用的索引。如果为NULL，则没有使用索引
- key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
- ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
- rows：mysql认为必须检查的用来返回请求数据的行数
- extra列需要注意的返回值
- Using filesort:看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序，根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
- Using temporary 看到这个的时候，也需要优化，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上。
查询最后支付时间----优化MAX()函数
mysql> explain select max(payment_date) from payment \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16086
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
可以查看到：type:ALL是表扫描操作;没有任何索引，rows非常大，IO效率非常低。
如何优化这个SQL：通常情况下建立索引：
create index idx_paydate on payment(payment_date);
这样在次执行
explain select max(payment_date) from payment;
可以看到Extra：select tables optimized away;可以通过索引进行操作，大大减少了IO操作
mysql> create index idx_paydata on payment(payment_date);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select max(payment_date) from payment \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> DROP INDEX idx_paydata on payment;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select max(payment_date) from payment \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16086
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql>
在一条SQL中同时查出2006年和2008年电影的数量----优化count()函数
错误方式：
SELECT COUNT(release_year='2006' OR release_year='2008') FROM film;
无法分开计算2006和2008年的电影数量，结果是之和。
select count(*) FROM film WHERE release_year='2006' AND release_year='2007';
release_year不可能同时为2006和2007，因此有逻辑错误
正确的方式：
mysql> select count(release_year='2006' or null) as '2006film', count(release_year='2008' or null) as '2008film' from film;
+----------+----------+
| 2006film | 2008film |
+----------+----------+
| 999 | 1 |
+----------+----------+
1 row in set (0.00 sec)
mysql>
count中‘or null’的作用是：当年份不是2006/2008时返回null，而null在count(id)中时不会计数的，count(*)会将null计算进去。
mysql> SELECT COUNT(release_year='2007') FROM film;
+----------------------------+
| COUNT(release_year='2007') |
+----------------------------+
| 1000 |
+----------------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(release_year='2007' or null) FROM film;
+------------------------------------+
| COUNT(release_year='2007' or null) |
+------------------------------------+
| 0 |
+------------------------------------+
1 row in set (0.00 sec)
mysql>
4.子查询的优化
通常情况下，需要把子查询优化为join查询，但在优化时要注意关联键是否有一对多的关系，要注意重复数据。
我们新建一张表t1
create table t1(tid int);
插入一条数据insert into t1 values(1);
进行子查询：select * from t where t.id in (select * from t1.tid from t1);
返回t表id在t1表中的数据
优化成join的形式：
select t.id from t join t1 on t.id=t1.tid;
这两种形式返回的结果是一样的
需要注意的是：
如果在t1表中，添加一条数据：insert into t1 values(1);
然后在分别执行这两种形式的查询：
发现使用select * from t where t.id in (select * from t1.tid from t1);查询出来的结果是一条数据。而select t.id from t join t1 on t.id=t1.tid;是两条数据，说明有重复数据，我们可以使用distinct去重。 JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: