sqlite like优化 like sql 优化

前言

        尽量从我们平时开发中的环境找例子。

        有时候SQL语句运行的速度令我抓狂，我也迫切的想弄明白如何才能写出更高效的SQL。

1.模糊查询

只有"%"不在第一个位置，索引才会发挥左右。

# 走全表扫描，放弃索引
SELECT [COLUMN_List] FROM TABLE WHERE [COLUMN] LIKE '%abc%'  

#会使用COLUNMN 列的索引
SELECT [COLUMN_List] FROM TABLE WHERE [COLUMN] LIKE 'abc%'

2. IN或者NOT IN 要慎用

        就我经历过的项目而言，系统数据库中一般会有一些参数表。比如现在参数表中，订单有四种状态。

        

       我需要查询出  不是"已接收"状态  的其它所有订单。那么我通常写的SQL是

SELECT [COLUMN_LIST] FROM T_SYS_PARAM WHERE PARAM_VALUE IN (200，201，202)

        但是这样的写法可能会出现全表扫描。应该优化成 

SELECT [COLUMN_LIST] FROM T_SYS_PARAM WHERE PARAM_VALUE BETWEEN 200 AND 202

        所以，对于连续的数值，能用 between 就不要用 in 了。 

3. * 禁用

       使用 * 可能会查询出不需要的列，降低SQL的查询速度。

使用 * 会返回了不需要的列
 SELECT * FROM T_TABLE

4.使用LIMIT 1 或者 TOP 1 

         当知道只会查询出一条数据的时候，使用LIMIT 1 及时终止SQL的执行，可终止数据库索引继续扫描整个表或索引。

SELECT PARAM_NAME FROM t_sys_param WHERE PARAM_VALUE = '已支付' LIMIT 1

5.尽量不要在WHERE子句中对字段进行表达式操作

CREATE TABLE `t_sys_param` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `PARAM_NAME` varchar(10) DEFAULT NULL,
  `PARAM_VALUE` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `abc` (`PARAM_VALUE`)
) ENGINE=InnoDB AUTO_INCREMENT=2231 DEFAULT CHARSET=utf8;

          首先我写了一个存储过程，插入两千条数据到T_SYS_PARAM中。

DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
   DECLARE i INT DEFAULT 0 ; -- 定义变量
   WHILE i < myCount DO  -- 符合条件就循环
       INSERT INTO t_sys_param VALUES(NULL,i,i);
       SET i = i + 1 ; -- 计数器+1
   END WHILE;       -- 当不满足条件，结束循环  --分号一定要加！
   SET result = i;  -- 将变量赋值到输出
END //

        

        

        花了5秒中，生成了2000条数据，只显示了一部分。

        

        现在查询PARAM_VALUE 除以 2 等于 50 的数据。果然扫描了2000条数据，我的妈耶。

EXPLAIN SELECT PARAM_NAME FROM t_sys_param WHERE PARAM_VALUE / 2 = 50

        再测试另外一个。 只扫描了1条数据，可以，很强势。

EXPLAIN SELECT PARAM_NAME FROM t_sys_param WHERE PARAM_VALUE  = 50 * 2

6.尽量不在where子句中用 != 操作符

        比如说，查询某个班级的同学，条件是同学的名字不叫"大宇"，首先做的就是应该先查询到这个班的所有同学的名字呢。所以，全表扫描就出现了。

7.尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

        理由同5