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.like语句操作,左模糊和全模糊将不走索引，右模糊走索引

      一般情况下不鼓励使用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) 索引，因为遵守“最左前缀”原则，在查询条件中没有使用复合索引的第一个字段，索引是不会被使用的。