阅读量:11
在使用MySQL进行模糊查询的时候，很自然的会用到like语句。通常情况下，在数据量小的时候，不容易看出查询的效率。但在数据量达到百万级、千万级的时候，查询的效率就很容易显现出来。这个时候查询的效率就显得很重要！
一般情况下like模糊查询的写法为（field已建立索引）：
SELECT `column` FROM `table` WHERE `field` like '%keyword%';
上面的语句用explain解释来看，SQL语句并未用到索引，而且是全表搜索，如果在数据量超大的时候，可想而知最后的效率会是这样
对比下面的写法：
SELECT `column` FROM `table` WHERE `field` like 'keyword%';
这样的写法用explain解释看到，SQL语句使用了索引，搜索的效率大大提高了！
但是有的时候，我们在做模糊查询的时候，并非要想查询的关键词都在开头，所以如果不是特别的要求，"keywork%"并不合适所有的模糊查询。
这个时候阿里巴巴就问到了我，如何解决上面的这个问题？
我们可以使用下面的方法：
1. LOCATE(substr,str), LOCATE(substr,str,pos)
第一个语法返回substr在字符串str 的第一个出现的位置。第二个语法返回子符串 substr 在字符串str，从pos处开始的第一次出现的位置。如果substr 不在str 中，则返回值为0 。
SELECT LOCATE('xbar',`foobar`);
###返回0
SELECT LOCATE('bar',`foobarbar`);
###返回4
SELECT LOCATE('bar',`foobarbar`,5);
###返回7
SELECT `column` FROM `table` WHERE LOCATE('keyword', `field`)>0
备注：keyword是要搜索的内容，field为被匹配的字段，查询出所有存在keyword的数据
2.POSITION('substr' IN `field`)方法
position可以看作是locate的别名，功能跟locate一样
SELECT `column` FROM `table` WHERE POSITION('keyword' IN `filed`)
3.INSTR(`str`,'substr')方法
SELECT `column` FROM `table` WHERE INSTR(`field`, 'keyword' )>0
除了上述的方法外，还有一个函数FIND_IN_SET
FIND_IN_SET(str1,str2):
返回str2中str1所在的位置索引，其中str2必须以","分割开。
使用like，还是使用locate position高效的前提条件是查询的字段上面已经建立起了索引。
MySQL百万级以上数据查询提速的方法
最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法。
由于在参与的实际项目中发现当mysql表的数据量达到百万级时，普通SQL查询效率呈直线下降，而且如果where中的查询条件较多时，其查询速度简直无法容忍。
曾经测试对一个包含400多万条记录（有索引）的表执行一条条件查询，其查询时间竟然高达40几秒，相信这么高的查询延时，任何用户都会抓狂。因此如何提高sql语句查询效率，显得十分重要。
以下是30种SQL查询语句优化方法：
1、应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4、尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描：(不能前置百分号)
select id from t where name like ‘%c%’
若要提高效率，可以考虑全文检索。
6、in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
7、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为：
select id from t where num=100*2
9、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)=’abc’–name以abc开头的id
