目录
表联查避免笛卡尔积使用inner join/left join/right join
SQL优化
避免使用select *
select * from student
推荐写法:
select sname from student
原因:
- 字段多时,大表能达到100多个字段甚至达200多个字段
- 只取需要的字段,节省资源、减少网络开销
- select * 进行查询时,很可能不会用到索引,就会造成全表扫描
表联查避免笛卡尔积使用inner join/left join/right join
select * from student,class,sc
where student.classid = class.classid AND
student.sid = sc.sid
推荐写法:
select * from student
inner join class
on student.classid = class.classid
inner join sc
on student.sid = sc.sid
表联查的时候先查小表再查大表
假如有student和class两张表,其中student表有10000条数据,而class表有100条数据。
select * from class left join student
on student.classid = class.classid
使用varchar代替char
原因:
- varchar变长字段按数据内容实际长度存储,存储空间小,可以节省存储空间
- char按声明大小存储,不足补空格
- 其次对于查询来说,在一个相对较小的字段内搜索,效率更高
使用分页查询
有时候,列表页在查询数据时,如果查询返回数据量很大,就会造成查询时间过长,网络传输时间过长。同时,大量数据返回也可能没有实际意义。如返回上千条甚至更多,用户也看不过来。
在mysql中通常采用分页,用limit关键字:
select sid,sname,ssex from student limit 10,20;
用连接查询代替子查询
mysql中如果需要从两张以上的表中查询出数据的话,一般有两种实现方式:子查询 和 连接查询
子查询示例:
-- 查询大于等于5人的班级名称和人数
select classname,人数 from class inner join
(select classid,count(*) 人数
from student group by classid) t1
on class.classid = t1.classid
where 人数 >= 5
连接查询:
-- 查询大于等于5人的班级名称和人数
select classname,count(sid)
from class left join student
on class.classid = student.classid
group by class.classid
having count(sid) >= 5;
子查询程序先运行在嵌套在最内层的语句,再运行外层的语句。
子查询语句的优点是简单,结构化,如果涉及的表数量不多的话。
但缺点是mysql执行子查询时,需要创建临时表,查询完毕后,需要再删除这些临时表,有一些额外的性能消耗。
添加索引
sql优化当中,有一个非常重要的内容就是:索引优化。
很多时候sql语句,走了索引,和没有走索引,执行效率差别很大。所以索引优化被作为sql优化的首选。
如何判断是否要加索引
索引的使用原则
- 控制索引的数量
众所周知,索引能够显著的提升查询sql的性能,但索引数量并非越多越好。
因为表中新增数据时,需要同时为它创建索引,而索引是需要额外的存储空间的,而且还会有一定的性能消耗。
阿里巴巴的开发者手册中规定,单表的索引数量应该尽量控制在5个以内,并且单个索引中的字段数不超过5个。
mysql使用的B+树的结构来保存索引的,在insert、update和delete操作时,需要更新B+树索引。如果索引过多,会消耗很多额外的性能。
- 尽量选择区分度高的列作为索引
区分度的公式是count(distinct col)/count(*) 字段不重复的比例
越接近于1,越适合做索引
- 复合索引最左前缀匹配原则
对于多列复合索引,总是从索引的最前面字段开始,接着往后,中间不能跳过。
- 复合索引也称为联合索引
- 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则
- 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的
- =和in可以乱序
比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可 以识别的形式。
- 索引列不能参与计算,保持列“干净”
假如索引列参与计算的话,那每次检索时,都会先将索引计算一次,再做比较,显然成本太大。
- 尽量的扩展索引,不要新建索引
比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可
- 有NULL值的列不适合建立索引
- 使用短索引
短索引不仅可以提高查询速 度而且可以节省磁盘空间和I/O操作。
- 排序字段创建索引
where和order by中常出现的字段就创建索引。
但MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是 不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个 列的排序,如果需要最好给这些列创建复合索引。
- like语句操作
索引列一般情况下不鼓励使用like操作