感觉自己总是忘,还是做个笔记吧,(~ ̄▽ ̄)~
常见的优化
1,尽量不要使用*,会导致全表扫描,不用索引。
例如select * ;
在统计记录总数的时候,count(主键) 比count(*) 消耗的时间更长,测试了108条数据,count(主键) 0.031是cout(*)时间 0.016的两倍。
但是看了这篇:https://www.cnblogs.com/alexguoyihao/p/9100979.html
一百万条 count(id) 时间要短 0.028ms 感觉两者差不多,都可以吧
2,在经常需要进行查询的字段上添加索引,例如where后面的字段
比如在登录的时候
select * from t_user where username='flower' and password='flower';
可以创建索引create index name_pass on t_user(username,password);
速度真的会快很多,数据越多越明显。
3,分页limit优化
select * from t_book limit 10,4;
使用覆盖索引进行优化using(index_coulumn)
select * from t_book inner join (select id from t_book limit 10,4) as t2 using(id);
可能我数据太少了不够明显吧
4,索引是最左匹配的,尽量按从左到右的顺序指定搜索的字段
5,多用join少用子查询
-- 分页查询的limit begin,end;
(begin,end]是左开右闭的。
select id,name from t_book limit 0,5;
-- 查询出来的数据是从第一条到第五条
-- 添加列
alter table t_type add column parent_id INT(11) DEFAULT NULL COMMENT '参考主键id';
-- 添加外键关系
alter table t_type add constraint id_parent foreign key(parent_id) REFERENCES t_type(id) on delete cascade;
-- 修改字段名
alter table t_blog change blogSchema blogschema varchar(500);
-- 重命名表
ALTER TABLE tbl1 RENAME tbl2;
-- 删去字段
ALTER TABLE tbl DROP COLUMN filed1, DROP COLUMN field2;
-- 删除列c:
ALTER TABLE t2 DROP COLUMN c;
-- 重命名列名
ALTER TABLE tbl MODIFY field1 BIGINT NOT NULL;
-- 在列d和列a中添加索引
ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);
-- 使用explain查看sql语句的执行情况
explain select * from t_book inner join (select id from t_book limit 10,4) as t2 using(id);
-- 左连接
select sname,age,name tname from t_student left join t_teacher on t_student.tid=t_teacher.id;
-- 内连接
select t.name from t_type as t inner join t_booktype as b on t.id =b.typeid where b.bookid=2;
集函数
having是分组(group by)后的筛选条件,分组后的数据组内再筛选
where则是在分组前筛选
SELECT region, SUM(population), SUM(area) FROM bbc
GROUP BY region HAVING SUM(area)>1000000
round (avg(列名),2)返回组中的平均值,可以选择保留一位或者两位。
过滤掉最低分小于60的学生,并平均分保留两位数值
select snum,sum(score) ,round (avg(score),2),max(score) from Tbl_Grade
Group by snum having min(score)>=60;
按条件分组并统计组内的数量
这里有个高级查询的例子
需求:查询购物车中商品的种数
mysql> select bid,num,status,update_time,create_time from t_shopcar where uid=1;
+------+------+--------+---------------------+---------------------+
| bid | num | status | update_time | create_time |
+------+------+--------+---------------------+---------------------+
| 1 | 1 | 1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
| 2 | 1 | 1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
| 1 | 1 | 1 | 0000-00-00 00:00:00 | 2019-02-12 10:03:27 |
+------+------+--------+---------------------+---------------------+
3 rows in set (0.01 sec)
嵌套查询
mysql> select T.bid,count(T.num) as bid_count,T.status,T.update_time,T.create_time from (select bid,num,status,update_time,create_time from t_shopcar where uid=1) as T group by bid order by update_time;
+------+-----------+--------+---------------------+---------------------+
| bid | bid_count | status | update_time | create_time |
+------+-----------+--------+---------------------+---------------------+
| 1 | 2 | 1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
| 2 | 1 | 1 | 0000-00-00 00:00:00 | 2019-02-11 16:35:02 |
+------+-----------+--------+---------------------+---------------------+
2 rows in set (0.01 sec)