文章预览
MYSQL语句优化总结
在实际项目中,数据量存储与读取是不可或缺的工作。读取数据不单单是把数据取出来就完事了,在数据量特别大的情况下,如何提高读取效率是一件很重要的事情.要想提高读取效率就要了解sql优化方案,小编在此对sql语句优化做了一个简单的总结,供大家学习交流。如有不对的地方,恳请各位看官指正
一在不使用索引的情况下sql语句优化
1.select语句中尽量避免使用*
*是获取表中全部字段的意思,比如select * from student,获取的是student表中全部字段的所有数据
优化思路:
比如我只需要这个表中姓名和成绩两个字段的数据,sql语句可以改成select name,achievement from student
补充:如果select * 用户连表查询,会占用更大的数据库服务器资源
2.select 语句中尽量避免使用in 和 not in
in和not in会导致数据库进行全表搜索,提高运行时间成本,可以使用between and代替
如:我想查询学号为1,和10的学生姓名和成绩
select name, achievement from student where stuno in(1,10);
优化:
Select name,achievement fron student where stuno between 1 and 10;
3.尽量避免使用or
使用or与使用in ,not in一样,会导致数据库进行全表检索。在实际项目工作中,如果想要从几十万上百万的数据中取出来你想要的数据,使用or是非常不划算的,可以使用union代替
如:从成绩表中取出来成绩为60和90的学生
select name,achievement from student where stuno=60 or stuno=90;
优化:
select name,achievement from student where stuno=60 union select name,achievement from student where stuno=90
sql语句长,但是处理数据量较大的情况下,是可以省下时间成本的
4.使用limit字句限制返回的数据行数
如果前端和APP开发人员只需要显示15行数据,而你的查询结果集返回了1万行,那么这适合最好使用limt子句来限制查询返回的数据行数。
5.Where 字句比较符号左侧避免使用函数或者表达式
尽量避免在where条件子句中,
比较符号的左侧出现表达式、函数等操作。因为这会导致数据库引擎进行全表扫描,从而增加运行时间。如果要使用表达式和函数,可以将其放置比较符号右侧
二.在建立索引的情况如何进行sql语句优化
1.
避免在比较符号左侧使用表达式和函数
SELECT `sname` FROM `stu` WHERE `age`+10=30;-- 不会使用索引,因为所有索引列参与了计算
SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990;
-- 不会使用索引,因为使用了函数运算,原理与上面相同
2.
索引不会包含带有null值的列,尽量不要让字段为null
3.
不使用
NOT IN
、
<>
、!
=
操作,但
<,<=
,
=
,
>,>=,BETWEEN,IN
是可以用到索引的
4.
尽量避免用or来作为连接条件,可使用union al
5.l
ike
语句操作
,
左模糊和全模糊将不走索引,右模糊走索引
一般情况下不鼓励使用
like
操作,如果非使用不可,注意正确的使用方式。
like ‘%aaa%’
不会使用索引,而
like ‘aaa%’
可以使用索引。
6.隐式转换不走索引
当查询条件左右两侧类型不匹配的时候回发生隐式转换,隐式转换带来的影响就是可能导致索引失效进而全表扫描。下面的案列中,date_str是字符串,然后匹配的是整数类型,从而发生隐式转换
select name,achievement from student where date_str = 201701
优化:
select name,achievement from student where date_str =
“201701“
7.多个单列索引并不是最佳选择
Mysql
只能使用一个索引,会从多个索引中选择一个限制最为严格的索引,因此,为多个列创建单列索引,并不能提高mysql的查询性能
假设,有两个单列索引,分别为
news_year_idx(news_year)
和
news_month_idx(news_month)
。现在,有一个场景需要针对资讯的年份和月份进行查询,那么,
SQL
语句可以写成:
-
select
*
from
news
where
news_year
=
2017
and
news_month
=
1
事实上,
MySQL
只能使用一个单列索引。为了提高性能,可以使用复合索引
news_year_month_idx(news_year, news_month)
保证
news_year
和
news_month
两个列都被索引覆盖。
8.复合索引的最左前缀原则
复合索引遵守
“
最左前缀
”
原则,即在查询条件中使用了复合索引的第一个字段,索引才会被使用。因此,在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找,则无法使用索引。
假设,有一个场景只需要针对资讯的月份进行查询,那么,
SQL
语句可以写成:
-
select
*
from
news
where
news_month
=
1
此时,无法使用
news_year_month_idx(news_year, news_month)
索引,因为遵守
“
最左前缀
”
原则,在查询条件中没有使用复合索引的第一个字段,索引是不会被使用的。
………………………………