锐客网

  1. 首页 > 睿知 > it技术 > >

mysql怎么代替in mysql 替代like

索引IT技术

如何让mysql速度更快的响应？如何提高读取和查询速度1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引 。
2.应尽量避免在 where 子句中对字段进行 null 值判断 ， 否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
3.应尽量避免在 where 子句中使用!=或操作符，否则将引擎放弃使用索引而进行全表扫描 。
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.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
6.下面的查询也将导致全表扫描：
select id from t where name like '玞%'
若要提高效率，可以考虑全文检索 。
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
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate='2005-11-30' and createdate'2005-12-1'
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引 。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致 。
12.不要写一些没有意义的查询 ， 如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的 ， 应改成这样：
create table #t(...)
13.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的 ， 当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半 ， 那么即使在sex上建了索引也对查询效率起不了作用 。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定 。一个表的索引数最好不要超过6个 ， 若太多则应考虑一些不常使用到的列上建的索引是否有必要 。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源 。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引 。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型 ， 这会降低查询和连接的性能，并会增加存储开销 。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符 ， 而对于数字型而言只需要比较一次就够了 。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar，因为首先变长字段存储空间?。?可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些 。
19.任何地方都不要使用 select * from t ， 用具体的字段列表代替“*”，不要返回用不到的任何字段 。
20.尽量使用表变量来代替临时表 。如果表变量包含大量数据，请注意索引非常有限（只有主键索引） 。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗 。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如 ， 当需要重复引用大型表或常用表中的某个数据集时 。但是，对于一次性事件，最好使用导出表 。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert 。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table，然后 drop table ， 这样可以避免系统表的较长时间锁定 。
25.尽量避免使用游标，因为游标的效率较差 ， 如果游标操作的数据超过1万行，那么就应该考虑改写 。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效 。
27.与临时表一样，游标并不是不可使用 。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法 ， 尤其是在必须引用几个表才能获得所需的数据时 。在结果集中包括“合计”的例程通常要比使用游标执行的速度快 。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好 。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息 。
29.尽量避免大事务操作，提高系统并发能力 。
30.尽量避免向客户端返回大数据量 ， 若数据量过大，应该考虑相应需求是否合理 。
mysql查询语句in和exists二者的区别和性能影响如果查询的两个表大小相当，那么用in和exists差别不大 。
如果两个表中一个较小 ， 一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B) 效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc) 效率高，用到了B表上cc列的索引 。
相反的
2：
select * from B where cc in (select cc from A) 效率高 ， 用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc) 效率低，用到了A表上cc列的索引 。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引 。所以无论那个表大 ， 用not exists都比not in要快 。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的 。
mysql存储过程 in 怎么用out 表示输出的参数，存储过程调用 代码 需要获得此参数值 。
in 表示输入参数，默认为in
例1、一个简单存储过程游标实例
复制代码代码如下:
DELIMITER $$
DROP PROCEDURE IF EXISTS getUserInfo $$
CREATE PROCEDURE getUserInfo(in date_day datetime)
--
-- 实例
-- 存储过程名为：getUserInfo
-- 参数为：date_day日期格式:2008-03-08
--
BEGIN
declare _userName varchar(12); -- 用户名
declare _chinese int ; -- 语文
declare _math int ;-- 数学
declare done int;
-- 定义游标
DECLARE rs_cursor CURSOR FOR SELECT username,chinese,math from userInfo where datediff(createDate, date_day)=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 获取昨天的日期
if date_day is null then
set date_day = date_add(now(),interval -1 day);
end if;
open rs_cursor;
cursor_loop:loop
FETCH rs_cursor into _userName, _chinese, _math; -- 取数据
if done=1 then
leave cursor_loop;
end if;
-- 更新表
update infoSum set total=_chinese _math where UserName=_userName;
end loop cursor_loop;
close rs_cursor;
END$$
DELIMITER ;
例2、存储过程游标循环跳出现
在MySQL的存储过程中,游标操作时,需要执行一个conitnue的操作.众所周知,MySQL中的游标循环操作常用的有三种,LOOP,REPEAT,WHILE.三种循环,方式大同小异.以前从没用过,所以记下来,方便以后查阅.
1.REPEAT
复制代码代码如下:
REPEAT
Statements;
UNTIL expression
END REPEAT
demo
DECLARE num INT;
DECLARE my_stringVARCHAR(255);
REPEAT
SETmy_string =CONCAT(my_string,num,',');
SETnum = num1;
UNTIL num 5
END REPEAT;
2.WHILE
复制代码代码如下:
WHILE expression DO
Statements;
END WHILE
demo
DECLARE num INT;
DECLARE my_stringVARCHAR(255);
SET num =1;
SET str ='';
WHILE numspan10DO
SETmy_string =CONCAT(my_string,num,',');
SETnum = num1;
END WHILE;
3.LOOP(这里面有非常重要的ITERATE,LEAVE)
代码如下 复制代码
DECLARE numINT;
DECLARE strVARCHAR(255);
SET num =1;
SET my_string ='';
loop_label:LOOP
IFnum 10THEN
LEAVEloop_label;
ENDIF;
SETnum = num1;
IF(num mod3)THEN
ITERATEloop_label;
ELSE
SETmy_string =CONCAT(my_string,num,',');
ENDIF;
END LOOP;
PS:可以这样理解ITERATE就是我们程序中常用的contiune,而ITERATE就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.
例3,mysql 存储过程中使用多游标
先创建一张表，插入一些测试数据：
复制代码代码如下:
DROP TABLE IF EXISTS netingcn_proc_test;
CREATE TABLE `netingcn_proc_test` (
`id` INTEGER(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20),
`password` VARCHAR(20),
PRIMARY KEY (`id`)
)ENGINE=InnoDB;
insert into netingcn_proc_test(name, password) values
('procedure1', 'pass1'),
('procedure2', 'pass2'),
('procedure3', 'pass3'),
('procedure4', 'pass4');下面就是一个简单存储过程的例子：
drop procedure IF EXISTS test_proc;
delimiter //
create procedure test_proc()
begin
-- 声明一个标志done，用来判断游标是否遍历完成
DECLARE done INT DEFAULT 0;
-- 声明一个变量，用来存放从游标中提取的数据
-- 特别注意这里的名字不能与由游标中使用的列明相同 ， 否则得到的数据都是NULL
DECLARE tname varchar(50) DEFAULT NULL;
DECLARE tpass varchar(50) DEFAULT NULL;
-- 声明游标对应的 SQL 语句
DECLARE cur CURSOR FOR
select name, password from netingcn_proc_test;
-- 在游标循环到最后会将 done 设置为 1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
-- 执行查询
open cur;
-- 遍历游标每一行
REPEAT
-- 把一行的信息存放在对应的变量中
FETCH cur INTO tname, tpass;
if not done then
-- 这里就可以使用 tname， tpass 对应的信息了
select tname, tpass;
end if;
UNTIL done END REPEAT;
CLOSE cur;
end
//
delimiter ;
-- 执行存储过程
call test_proc();
需要注意的是变量的声明、游标的声明和HANDLER声明的顺序不能搞错，必须是先声明变量，再申明游标 ， 最后声明HANDLER 。上述存储过程的例子中只使用了一个游标，那么如果要使用两个或者更多游标怎么办，其实很简单，可以这么说，一个怎么用两个就是怎么用的 。例子如下：
复制代码代码如下:
drop procedure IF EXISTS test_proc_1;
delimiter //
create procedure test_proc_1()
begin
DECLARE done INT DEFAULT 0;
DECLARE tid int(11) DEFAULT 0;
DECLARE tname varchar(50) DEFAULT NULL;
DECLARE tpass varchar(50) DEFAULT NULL;
DECLARE cur_1 CURSOR FOR
select name, password from netingcn_proc_test;
DECLARE cur_2 CURSOR FOR
select id, name from netingcn_proc_test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur_1;
REPEAT
FETCH cur_1 INTO tname, tpass;
if not done then
select tname, tpass;
end if;
UNTIL done END REPEAT;
CLOSE cur_1;
-- 注意这里 ， 一定要重置done的值为 0
set done = 0;
open cur_2;
REPEAT
FETCH cur_2 INTO tid, tname;
if not done then
select tid, tname;
end if;
UNTIL done END REPEAT;
CLOSE cur_2;
end
//
delimiter ;
call test_proc_1();
上述代码和第一个例子中基本一样，就是多了一个游标声明和遍历游标 。这里需要注意的是，在遍历第二个游标前使用了set done = 0，因为当第一个游标遍历玩后其值被handler设置为1了 ， 如果不用set把它设置为 0 ，那么第二个游标就不会遍历了 。当然好习惯是在每个打开游标的操作前都用该语句，确保游标能真正遍历 。当然还可以使用begin语句块嵌套的方式来处理多个游标,例如：
复制代码代码如下:
drop procedure IF EXISTS test_proc_2;
delimiter //
create procedure test_proc_2()
begin
DECLARE done INT DEFAULT 0;
DECLARE tname varchar(50) DEFAULT NULL;
DECLARE tpass varchar(50) DEFAULT NULL;
DECLARE cur_1 CURSOR FOR
select name, password from netingcn_proc_test;
DECLARE cur_2 CURSOR FOR
select id, name from netingcn_proc_test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur_1;
REPEAT
FETCH cur_1 INTO tname, tpass;
if not done then
select tname, tpass;
end if;
UNTIL done END REPEAT;
CLOSE cur_1;
begin
DECLARE done INT DEFAULT 0;
DECLARE tid int(11) DEFAULT 0;
DECLARE tname varchar(50) DEFAULT NULL;
DECLARE cur_2 CURSOR FOR
select id, name from netingcn_proc_test;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
open cur_2;
REPEAT
FETCH cur_2 INTO tid, tname;
if not done then
select tid, tname;
end if;
UNTIL done END REPEAT;
CLOSE cur_2;
end;
end
//
delimiter ;
call test_proc_2();
MySQL简单介绍——换个角度认识MySQL1、InnoDB存储引擎
Mysql版本=5.5 默认的存储引擎 ， MySQL推荐使用的存储引擎 。支持事务，行级锁定，外键约束 。事务安全型存储引擎 。更加注重数据的完整性和安全性 。
存储格式 : 数据，索引集中存储，存储于同一个表空间文件中 。
InnoDB的行锁模式及其加锁方法： InnoDB中有以下两种类型的行锁：共享锁（读锁： 允许事务对一条行数据进行读?。┖?互斥锁（写锁： 允许事务对一条行数据进行删除或更新），对于update，insert，delete语句，InnoDB会自动给设计的数据集加互斥锁，对于普通的select语句，InnoDB不会加任何锁 。
InnoDB行锁的实现方式： InnoDB行锁是通过给索引上的索引项加锁来实现的，如果没有索引，InnoDB将通过隐藏的聚簇索引来对记录加锁 。InnoDB这种行锁实现特点意味着：如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样 。
（1）在不通过索引条件查询时，InnoDB会锁定表中的所有记录 。
（2）Mysql的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果使用相同的索引键 ， 是会出现冲突的 。
（3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，但都是通过行锁来对数据加锁 。
优点：
1、支持事务处理、ACID事务特性；
2、实现了SQL标准的四种隔离级别（ 原子性（ Atomicity ）、一致性（ Consistency ）、隔离性（Isolation ）和持续性（Durability ））；
3、支持行级锁和外键约束；
4、可以利用事务日志进行数据恢复 。
5、锁级别为行锁，行锁优点是适用于高并发的频繁表修改，高并发是性能优于 MyISAM 。缺点是系统消耗较大 。
6、索引不仅缓存自身，也缓存数据，相比 MyISAM 需要更大的内存 。
缺点：
因为它没有保存表的行数 ， 当使用COUNT统计时会扫描全表 。
使用场景：
(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且表锁定的机会比较大的情况 。
2、 MyISAM存储引擎
MySQL= 5.5 MySQL默认的存储引擎 。ISAM：Indexed Sequential Access Method(索引顺序存取方法)的缩写 ， 是一种文件系统 。擅长与处理，高速读与写 。
功能：
（1）支持数据压缩存储,但压缩后的表变成了只读表，不可写；如果需要更新数据，则需要先解压后更新 。
（2）支持表级锁定 ， 不支持高并发；
（3）支持并发插入 。写操作中的插入操作，不会阻塞读操作（其他操作）；
优点：
1.高性能读?。?
2.因为它保存了表的行数 ， 当使用COUNT统计时不会扫描全表；
缺点：
1、锁级别为表锁，表锁优点是开销小 ， 加锁快；缺点是锁粒度大，发生锁冲动概率较高，容纳并发能力低，这个引擎适合查询为主的业务 。
2、此引擎不支持事务 ， 也不支持外键 。
3、INSERT和UPDATE操作需要锁定整个表；
使用场景：
(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务 。
InnoDB和MyISAM一些细节上的差别：
1、InnoDB不支持FULLTEXT类型的索引，MySQL5.6之后已经支持（实验性） 。
2、InnoDB中不保存表的 具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可 。注意的是 ， 当count()语句包含 where条件时 ， 两种表的操作是一样的 。
3、对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中 ， 可以和其他字段一起建立联合索引 。
4、DELETE FROM table时 ， InnoDB不会重新建立表 ， 而是一行一行的删除 。
5、LOAD TABLE FROM MASTER操作对InnoDB是不起作用的 ， 解决方法是首先把InnoDB表改成MyISAM表 ， 导入数据后再改成InnoDB表 ， 但是对于使用的额外的InnoDB特性（例如外键）的表不适用 。
6、另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表 。
1．索引概述
利用关键字，就是记录的部分数据（某个字段，某些字段，某个字段的一部分），建立与记录位置的对应关系 ， 就是索引 。索引的关键字一定是排序的 。索引本质上是表字段的有序子集 ， 它是提高查询速度最有效的方法 。一个没有建立任何索引的表 ， 就相当于一本没有目录的书 ， 在每次查询时就会进行全表扫描 ， 这样会导致查询效率极低、速度也极慢 。如果建立索引 ， 那么就好比一本添加的目录 ， 通过目录的指引 ， 迅速翻阅到指定的章节 ， 提升的查询性能 ， 节约了查询资源 。
2．索引种类
从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引 。
无论任何类型，都是通过建立关键字与位置的对应关系来实现的 。索引是通过关键字找对应的记录的地址 。
以上类型的差异：对索引关键字的要求不同 。
关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分） 。
普通索引,index：对关键字没有要求 。
唯一索引,unique index：要求关键字不能重复 。同时增加唯一约束 。
主键索引,primary key：要求关键字不能重复，也不能为NULL 。同时增加主键约束 。
全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词 。
PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值 ， 唯一索引允许空值；主键索引在一张表内只能创建一个 ， 唯一索引可以创建多个 。主键索引肯定是唯一索引 ， 但唯一索引不一定是主键索引 。
3.索引原则
如果索引不遵循使用原则，则可能导致索引无效 。
（1）列独立
如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧 。否则索引不会用到索引, 例如这条sql就不会用到索引:select * from A where id 1=10;
（2）左原则
Like：匹配模式必须要左边确定不能以通配符开头 。例如:select * from A where name like '%小明%'，不会用到索引，而select * from A where name like '小明%' 就可以用到索引（name字段有建立索引），如果业务上需要用到'%小明%'这种方式,有两种方法:1.可以考虑全文索引,但mysql的全文索引不支持中文；2.只查询索引列或主键列,例如:select name from A where name like '%小明%' 或 select id from A where name like '%小明%' 或 select id,name from A where name like '%小明%' 这三种情况都会用到name的索引;
复合索引：一个索引关联多个字段，仅仅针对左边字段有效果，添加复合索引时，第一个字段很重要 ， 只有包含第一个字段作为查询条件的情况才会使用复合索引(必须用到建索引时选择的第一个字段作为查询条件,其他字段的顺序无关),而且查询条件只能出现and拼接,不能用or,否则则无法使用索引.
（3）OR的使用
必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引 。
（4）MySQL智能选择
即使满足了上面说原则，MySQL也能弃用索引 ， 例如：select * from A where id1;这里弃用索引的主要原因：查询即使使用索引 ， 会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大 。
4.索引的使用场景
（1）索引检索：检索数据时使用索引 。
（2）索引排序: 如果order by 排序需要的字段上存在索引，则可能使用到索引 。
（3）索引覆盖: 索引拥有的关键字内容，覆盖了查询所需要的全部数据 ， 此时，就不需要在数据区获取数据，仅仅在索引区即可 。覆盖就是直接在索引区获取内容，而不需要在数据区获取 。例如: select name from A where name like '小明%';
建立索引索引时，不能仅仅考虑where检索，同时考虑其他的使用场景 。（在所有的where字段上增加索引，就是不合理的）
5.前缀索引
前缀索引是建立索引关键字一种方案 。通常会使用字段的整体作为索引关键字 。有时，即使使用字段前部分数据，也可以去识别某些记录 。就比如一个班级里，我要找王xx，假如姓王的只有1个人 ， 那么就可以建一个关键字为'王'的前缀索引 。语法：Index `index_name` (`index_field`(N))使用index_name前N个字符建立的索引 。
6.索引失效
（1） 应尽量避免在 where 子句中使用 != 或操作符，否则将引擎放弃使用索引而进行全表扫描；
（2） 应尽量避免在 where 子句中使用 or 来连接条件 ， 如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描；
（3） 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描；
（4）应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描；如select id from t where num/2 = 100；
（5） 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描；如：select id from t where substring(name,1,3) = ’abc’ ；
（6）应尽量避免在where子句中对字段进行类型转换，这将导致引擎放弃使用索引而进行全表扫描； 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,如select id from t where id = 1；如果id字段在表设计中是varchar类型 ， 那么即使id列上存的是数字，在查询时也一定要用varchar去匹配，sql应改为select id from t where id = '1'；
（7）应尽量避免在where子句中单独引用复合索引里非第一位置的索引；
join 的两种算法：BNL 和 NLJ
NLJ（Nested Loop Join）嵌套循环算法；以如下 SQL 为例：
select * from t1 join t2 on t1.a=t2.a
SQL 执行时内部流程是这样的：
1. 先从 t1（假设这里 t1 被选为驱动表）中取出一行数据 X；
2. 从 X 中取出关联字段 a 值，去 t2 中进行查找，满足条件的行取出；
3. 重复1、2步骤，直到表 t1 最后一行循环结束 。
这就是一个嵌套循环的过程，如果在被驱动表上查找数据时可以使用索引，总的对比计算次数等于驱动表满足 where 条件的行数 。假设这里 t1、t2都是1万行，则只需要 1万次计算，这里用到的是Index Nested-Loops Join（INLJ，基于索引的嵌套循环联接） 。
如果 t1、t2 的 a 字段都没有索引，还按照上述的嵌套循环流程查找数据呢？每次在被驱动表上查找数据时都是一次全表扫描，要做1万次全表扫描，扫描行数等于 1万 1万*1万 ， 这个效率很低，如果表行数更多，扫描行数动辄几百亿，所以优化器肯定不会使用这样的算法，而是选择 BNL 算法；
BNLJ（Block Nested Loop Join）块嵌套循环算法；
1. 把 t1 表（假设这里 t1 被选为驱动表）满足条件的数据全部取出放到线程的 join buffer 中；
2. 每次取 t2 表一行数据，去 joinbuffer 中进行查找，满足条件的行取出，直到表 t2 最后一行循环结束 。
这个算法下，执行计划的 Extra 中会出现 Using join buffer(Block Nested Loop) ， t1、t2 都做了一次全表扫描，总的扫描行数等于 1万 1万 。但是由于 joinbuffer 维护的是一个无序数组，每次在 joinbuffer 中查找都要遍历所有行 ， 总的内存计算次数等于1万*1万 。另外如果 joinbuffer 不够大放不下驱动表的数据，则要分多次执行上面的流程，会导致被驱动表也做多次全表扫描 。
BNLJ相对于NLJ的优点在于，驱动层可以先将部分数据加载进buffer，这种方法的直接影响就是将大大减少内层循环的次数，提高join的效率 。
例如：
如果内层循环有100条记录，外层循环也有100条记录，这样的话，每次外层循环先将10条记录放到buffer中，内层循环的100条记录每条与这个buffer中的10条记录进行匹配，只需要匹配内层循环总记录数次即可结束一次循环（在这里，即只需要匹配100次即可结束），然后将匹配成功的记录连接后放入结果集中 ， 接着 ， 外层循环继续向buffer中放入10条记录，同理进行匹配，并将成功的记录连接后放入结果集 。后续循环以此类推，直到循环结束 ， 将结果集发给client为止 。
可以发现，若用NLJ，则需要100 * 100次才可结束，BNLJ则需要100 / block_size * 100 = 10 * 100次就可结束,大大减少了循环次数 。
JOIN 按照功能大致分为如下三类：
JOIN、STRAIGHT_JOIN、INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录 。
LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录 。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反 ， 取得右表（table2）完全记录 ， 即是左表（table1）并无匹配对应记录 。
注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join 。
mysql 多表连接查询方式，因为mysql只支持NLJ算法,所以如果是小表驱动大表则效率更高；反之则效率下降；因此mysql对内连接或等值连接的方式做了一个优化,会去判断join表的数据行大小,然后取数据行小的表为驱动表 。
INNER JOIN、JOIN、WHERE等值连接和STRAIGHT_JOIN都能表示内连接，那平时如何选择呢？一般情况下用INNER JOIN、JOIN或者WHERE等值连接，因为MySQL 会按照"小表驱动大表的策略"进行优化 。当出现需要排序时，才考虑用STRAIGHT_JOIN指定某张表为驱动表 。
两表JOIN优化
a.当无order by条件时 ， 根据实际情况，使用left/right/inner join即可，根据explain优化 ；
b.当有order by条件时 ， 如select * from a inner join b where 1=1 and other condition order by a.col；使用explain解释语句；
1）如果第一行的驱动表为a，则效率会非常高，无需优化；
2）否则 ， 因为只能对驱动表字段直接排序的缘故，会出现using temporary，所以此时需要使用STRAIGHT_JOIN明确a为驱动表 ， 来达到使用a.col上index的优化目的；或者使用left join且Where条件中不含b的过滤条件，此时的结果集为a的全集，而STRAIGHT_JOIN为inner join且使用a作为驱动表 。注：使用STRAIGHT_JOIN虽然不会using temporary，但也不是一定就能提高效率，如果a表数据远远超过b表，那么有可能使用STRAIGHT_JOIN时比原来的sql效率更低，所以怎么使用STRAIGHT_JOIN，还是要视情况而定 。
在使用left join（或right join）时 ， 应该清楚的知道以下几点：
(1). on与 where的执行顺序
ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行 。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据,在匹配阶段 WHERE 子句的条件都不会被使用 。仅在匹配阶段完成以后，WHERE 子句条件才会被使用 。它将从匹配阶段产生的数据中检索过滤 。
所以我们要注意：在使用Left (right) join的时候，一定要在先给出尽可能多的匹配满足条件 ， 减少Where的执行 。
(2).注意ON 子句和 WHERE 子句的不同
即使右表的数据不满足ON后面的条件,也会在结果集拼接一条为NULL的数据行,但WHERE后面的条件不一样,右表不满足WHERE的条件,左表关联的数据也会被过滤掉 。
(3).尽量避免子查询，而用join
往往性能这玩意儿，更多时候体现在数据量比较大的时候，此时，我们应该避免复杂的子查询 。
（1）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很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)用下面的语句替换：select num from a where exists(select 1 from b where num=a.num)
（2）Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志 。
（3）join语句,MySQL里面的join是用小表去驱动大表,而由于MySQL join实现的原理就是做循环 ， 比如left join就是对左边的数据进行循环去驱动右边的表,左边有m条记录匹配,右边有n条记录那么就是做m次循环,每次扫描n行数据,总扫面行数是m*n行数据 。左边返回的结果集的大小就决定了循环的次数,故单纯的用小表去驱动大表不一定的正确的,小表的结果集可能也大于大表的结果集,所以写join的时候尽可能的先估计两张表的可能结果集,用小结果集去驱动大结果集.值得注意的是在使用left/right join的时候,从表的条件应写在on之后,主表应写在where之后.否则MySQL会当作普通的连表查询；
（4）select count(*) from table；这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的；
（5）select * from t 这种语句要尽量避免，使用具体的字段代替*，更有实际意义，需要什么字段就返回什么字段；
（6）数据量大的情况下，limit要慎用 ， 因为使用limit m，n方式分页时，mysql每次都是查询前m n条，然后舍弃前m条，所以m越大，偏移量越大，性能就越差 。比如：select * from A limit 1000000,20这钟，查询效率就会非常低 ， 当分页的页数大于一定的数量之后 ， 就可以换种方式来分页：select * from A a join （select id from A limit 1000000，20） b on a.id=b.id;
mysql查询 怎么使用 inin作为查询条件 ， 一般典型有两种用法：
一是IN常量，例如下面语句查询一、三年级的学生：
SELECT * FROM student WHERE grade IN ('一','三');
二是使用子查询 ， 也就是IN（SQL语句），例如下面的语句查询不及格的班级的所有学生：
SELECT * FROM student WHERE classno IN (
select classno from scores where score60
);
【mysql怎么代替in mysql 替代like】mysql怎么代替in的介绍就聊到这里吧 ， 感谢你花时间阅读本站内容 ， 更多关于mysql 替代like、mysql怎么代替in的信息别忘了在本站进行查找喔 。

    推荐阅读


    上一篇：舟山联想服务器使用效果如何？ 舟山联想服务器怎么样

    下一篇：mysql支持的变量类型有用户变量 mysql用户变量会存在数据库吗