如何正确合理的建立MYSQL数据库索引

`id` int(11) NOT NULL AUTO_INCREMENT ,

在数据库表中,对字段建立索引可以大大提高查询速度。如我们创建了一个 mytable表:

mysqlnotin走索引吗 mysql如何走索引mysqlnotin走索引吗 mysql如何走索引


mysqlnotin走索引吗 mysql如何走索引


mysqlnotin走索引吗 mysql如何走索引


CREATE TABLE mytable( IDINTNOT NULL, username VARCHAR(16) NOT NULL

); 我们随机向里面插入了10000条记录,其中有一条:5555, admin。

在查找username="admin"的记录 SELECT FROMmytable WHERE

username=‘admin‘;时,如果在username上已经建立了索引,MySQL无须任何扫描,即准确可找到该记录。相反,MySQL会扫描所有记录,即要查询10000条记录。

索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索包含多个列。

MySQL索引类型包括:

(1)普通索引

这是基本的索引,它没有任何限制。它有以下几种创建方式:

◆创建索引

CREATE INDEXindexName ON mytable(username(length));

如果是CHAR,VARCHAR类型,length可以小于字段实际长度;如果是BLOB和TEXT类型,必须指定 length,下同。

◆修改表结构

ALTERmytable ADDINDEX[indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( IDINTNOT NULL, username VARCHAR(16) NOT NULL,

INDEX [indexName] (username(length)) ); 删除索引的语法:

DROP INDEX [indexName] ON mytable;

(2)索引

它与前面的普通索引类似,不同的就是:索引列的值必须,但允许有空值。如果是组合索引,则列值的组合必须。它有以下几种创建方式:

◆创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length))

◆修改表结构

ALTERmytable ADDUNIQUE [indexName] ON (username(length))

◆创建表的时候直接指定

CREATE TABLE mytable( IDINTNOT NULL, username VARCHAR(16) NOT NULL,

UNIQUE [indexName] (username(length)) );

(3)主键索引

它是一种特殊的索引,不允许有空值。一般是在建表的时候同时创建主键索引:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,

PRIMARY KEY(ID) ); 当然也可以用 ALTER命令。记住:一个表只能有一个主键。

(4)组合索引

为了形象地对比单列索引和组合索引,为表添加多个字段:

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL,

city VARCHAR(50) NOT NULL, age INT NOT NULL );

为了进一步榨取MySQL的效率,就要考虑建立组合索引。就是将 name, city, age建到一个索引里:

ALTER TABLE mytable ADDINDEX name_city_age (name(10),city,age);

建表时,usernname长度为 16,这里用

10。这是因为一般情况下名字的长度不会超过10,这样会加速索引查询速度,还会减少索引文件的大小,提高INSERT的更新速度。

如果分别在

usernname,city,age上建立单列索引,让该表有3个单列索引,查询时和上述的组合索引效率也会大不一样,远远低于我们的组合索引。虽然此时有了三个索引,但MySQL只能用到其中的那个它认为似乎是的单列索引。

建立这样的组合索引,其实是相当于分别建立了下面三组组合索引:

usernname,city,age usernname,city usernname 为什么没有

city,age这样的组合索引呢?这是因为MySQL组合索引“左前缀”的结果。简单的理解就是只从左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引,下面的几个SQL就会用到这个组合索引:

mytable WHREE username="admin" 而下面几个则不会用到:

SELECT FROM mytable WHREE age=20 AND city="郑州" SELECT FROM mytable WHREE

city="郑州"

(5)建立索引的时机

到这里我们已经学会了建立索引,那么我们需要在什么情况下建立索引呢?一般来说,在WHERE和JOIN中出现的列需要建立索引,但也不完全如此,因为MySQL只对<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE才会使用索引。例如:

SELECT t.Name FROM mytable t LEFT JOINmytable m ON t.Name=m.username

WHERE m.age=20 AND m.city=‘郑州‘

此时就需要对city和age建立索引,由于mytable表的userame也出现在了JOIN子句中,也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时,MySQL不会使用索引。例如下句会使用索引:

SELECT FROM mytable WHERE username like‘admin%‘ 而下句就不会使用:

SELECT FROM mytable WHEREt Name like‘%admin‘ 因此,在使用LIKE时应注意以上的区别。

(6)索引的不足之处

上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:

◆虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。

索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立秀的索引,或优化查询语句。

(7)使用索引的注意事项

使用索引时,有以下一些技巧和注意事项:

◆索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

◆使用短索引

对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O作。

◆索引列排序

MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order

by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序作;尽量不要包含多个列的排序,如果需要给这些列创建复合索引。

◆like语句作

一般情况下不鼓励使用like作,如果非使用不可,如何使用也是一个问题。like “%%” 不会使用索引而like

“%”可以使用索引。

◆不要在列上进行运算

select from users where YEAR(adddate)<2007;

将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成

select from users where adddate<‘2007-01-01’;

◆不使用NOT IN和<>作

以上,就对其中MySQL索引类型进行了介绍。 提高mysql千万级大数据SQL查询优化30条经验(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

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=1002

9.应尽量避免在where子句中对字段进行函数作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3)=‘abc‘,name以abc开头的id

应改为:

select id from t where name like ‘abc%‘ 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查询可能不会去利用索引,如一表中有字段,male、female几乎各一半,那么即使在上建了索引也对查询效率起不了作用。

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.如果使用到了临时表,在| 0 |存储过程的务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较,如果游标作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

27. 与临时表一样,游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。

28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

29.尽量避免大事务作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

如何正确合理的建立MYSQL数据库索引

标签:

mysql百万数据查询 用什么代替in,该如何处理

【课程:MySQL教程】

mysql百万数据查询用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)

1、应尽量避免在 where 子句中使用!=或<>作符,否则将引擎放弃使用索引而进行全表扫描。

2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

可以这样查询:

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%’

若要提高效率,可以考虑全文mysql主从需要三个线程:(binlog dump thread)、sle(I/O thread 、SQL thread)检索。

不适合建立索引的情况

2: select from A where A.id in (select id from B);

1. 在查询中很少使用的列。

2. 很少数据值的列,例如:性别

3. 对于text,image,bit这样大数据列的字段,因为这些字段数据量要么非常大要么很少。

4. 当修改性能大于查询性能时,不应该去建立索引。

1. 避免全表扫描,首先应在where及order by后面设计的列上建立索引。

2. 因尽量避免range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。在where子句中进行null值判断,否则将导致引擎放弃索引,进行全盘扫描。

3. 因尽量避免在where子句中使用!= ,<>。

4. 因尽量避免在where子句中使用or,因为or是左右两种要同时进行查询。

5. in 和 not in 也要慎用。

6. 模糊查询,like前有%的不会走索引。

7. where字句进行表达式运算,或者使用函数都不会走索引。

补:例如三个字段num,name, age建立联合索引(num_name_age),实际可用索引为三种,num,num_name,num_age

9. 使用exists代替(exists执行完的返回true或false,true才执行外侧语句,否则不执行。)

MYSQL NOT IN优化

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。

将两个表的已建立的索引删除,重新建立索引,然后执行以下语句

select from record a where not exists(select 1 from offline_record where rec_id=a.id)

selectselect id from t where num is null a.

from record a

where a.id not in(select distinct b.rec_id from offline_record);

mysql 有几种索引

如大家所知道的,Mysql目前主要有以下几种索引类型:FULLTEXT,HASH,BTREE,RTREE。

那么,这几种索引有什么功能和性能上的不同呢?

FULLTEXT

即为全文索引,目前只有MyISAM引擎支持。其可以在CREATE TABLE ,ALTER TABLE ,CREATE INDEX 使用,不过目前只有 CHAR、VARCHAR ,TEXT 列上可以创建全文索引。值得一提的是,在数据量较大时候,现将数据放入一个没有全局索引的表中,然后再用CREATE INDEX创建FULLTEXT索引,要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。

全文索引并不是和MyISAM一起诞生的,它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前,这样一个查询语句是要进行遍历数据表作的,可见,在数据量较大时是极其的耗时的,如果没有异步IO处理,进程将被挟持,很浪费时间,当然这里不对异步IO作进一步讲解,想了解的童鞋,自行谷哥。

创建ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);

使用SELECT FROM table WHERE MATCH(cname1[,cname2…]) AGAINST ('word' MODE );

其中, MODE为搜寻方式(IN BOOLEAN MODE ,IN NATURAL LANGUAGE MODE ,IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION)。

关于这三种搜寻方式,愚安在这里也不多做交代,简单地说,就是,布尔模式,允许word里含一些特殊字符用于标记一些具体的要求,如+表示一定要有,-表示一定没有,表示通用匹配符,是不是想起了正则,类似吧;自然语言模式,就是简单的单词匹配;含表达式的自然语言模式,就是先用自然语言模式处理,对返回的结果,再进行表达式匹配。

HA`time` int(10) NULL DEFAULT NULL ,SH

Hash这个词,可以说,自打我们开始码的那一天起,就开始不停地见到和使用到了。其实,hash就是一种(key=>value)形式的键值对,如数学中的函数映射,允许多个key对应相同的value,但不允许一个key对应多个value。正是由于这个特性,hash很适合做索引,为某一列或几列建立hash索引,就会利用这一列或几列的值通过一定的算法计算出一个hash值,对应一行或几行数据(这里在概念上和函数映射有区别,不要混淆)。在ja语言中,每个类都有自己的hashcode()方法,没有显示定义的都继承自object类,该方法使得每一个对象都是的,在进行对象间equal比较,和序列化传输中起到了很重要的作用。hash的生成方法有很多种,足可以保证hash码的性,例如在MongoDB中,每一个document都有系统为其生成的的objectID(包含时间戳,主机散列值,进程PID,和自增ID)也是一种hash的表现。额,我好像扯远了-_-!

由于hash索引可以一次定位,不需要像树形索引那样逐层查找,因此具有极高的效率。那为什么还需要其他的树形索引呢?

在这里愚安就不自己总结了。引用下园子里其他大神的文章:来自 14的路 的MySQL的btree索引和hash索引的区别

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值,所以它只能用于等值的过滤,不能用于基于范围的过滤,因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系,并不能保证和Hash运算前完全一样。

(2)Hash 索引无法被用来避免数据的排序作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值,而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样,所以数据库无法利用索引的数据来避免任何排序运算;

(3)Hash 索引不能利用部分索引键查询。

对于组合索引,Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值,而不是单独计算 Hash 值,所以通过组合索引的前面一个或几个索引键进行查询的时候,Hash 索引也无法被利用。

(4)Hash 索引在任何时候都不能避免表扫描。

前面已经知道,Hash 索引是将索引键通过 Hash 运算之后,将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中,由于不同索引键存在相同 Hash 值,所以即使取满足某个 Hash 键值的数据的记录条数,也无法从 Hash 索引中直接完成查询,还是要通过访问表中的实际数据进行相应的比较,并得到相应的结果。

(5)Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键,如果创建 Hash 索引,那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦,会浪费多次表数据的访问,而造成整体性能低下。

愚安我稍作补充,讲一下HASH索引的过程,顺便解释下上面的第4,5条:

当我们为某一列或某几列建立hash索引时(目前就只有MEMORY引擎显式地支持这种索引),会在硬盘上生成类似如下的文件:

hash值 存储地址

1db54bc745a1 77#45b5

4bca452157d4 76#4556,77#45cc…

…hash值即为通过特定算法由指定列数据计算出来,磁盘地址即为所在数据行存储在硬盘上的地址(也有可能是其他存储地址,其实MEMORY会将hash表导入内存)。

所以,每次查询时都要遍历hash表,直到找到对应的hash值,如(4),数据量大了之后,hash表也会变得庞大起来,性能下降,遍历耗时增加,如(5)。

BTREE

BTREE索引就是一种将索引值按一定的算法,存入一个树形的数据结构中,相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新,反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番,不过那次考试好像没怎么考这个。如二叉树一样,每次查询都是从树的入口root开始,依次遍历node,获取leaf。

BTREE在MyISAM里的形式和Innodb稍有不同

在 Innodb里,有两种形态:一是primary key形态,其leaf node里存放的是数据,而且不仅存放了索引键的数据,还存放了其他字段的数据。二是secondary index,其leaf node和普通的BTREE不多,只是还存放了指向主键的信息.

而在MyISAM里,主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里,leaf node里存放的不是主键的信息,而是指向数据文件里的对应数据行的信息.

RTREE自动建立索引

RTREE在mysql很少使用,仅支持geometry数据类型,支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。

相对于BTREE,RTREE的优势在于范围查找.

各种索引的使用情况

(1)对于BTREE这种Mysql默认的索引类型,具有普遍的适用性

(2)由于FULLTEXT对中文支持不是很好,在没有插件的情况下,不要使用。其实,一些小的博客应用,只需要在数据采集时,为其建立关键字列表,通过关键字索引,也是一个不错的方法,至少愚安我是经常这么做的。

(3)对于一些搜索引擎级别的应用来说,FULLTEXT同样不是一个好的处理方法,Mysql的全文索引建立的文件还是比较大的,而且效率不是很高,即便是使用了中文分词插件,对中文分词支持也只是一般。真要碰到这种问题,Apache的Lucene或许是你的选择。

(4)正是因为hash表在处理较小数据量时具有无可比拟的素的优势,所以hash索引很适合做缓存(内存数据库)。如mysql数据库的内存版本Memsql,使用量很广泛的缓存工具Mencached,NoSql数据库redis等,都使用了hash索引这种形式。当然,不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

(5)至于RTREE,愚安我至今还没有使用过,它具体怎么样,我就不知道了。有RTREE使用经历的同学,到时可以交流下!

如何写出高质量,高性能的MySQL查询

◆建立索引会占用磁盘空间的索引文件。一般情况这个问题不太,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。

不能用null作索引,任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下,只要这些列中有一列含有null,该列就会从索引中排除。也就是说如果某列存在空值,即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列,即使的联接值为一个静态值,优化器是不会使用索引的。我们一起 来看一个例子,定有一个职工表(employee),对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME),现在要查询一个 叫比尔.(Bill Cliton)的职工。

下面是一个采用联接查询的SQL语句,

select from employss

where

first_name||''||last_name ='Beill Cliton'

上面这条语句完全可以查询出是否有Bill Cliton这个员工,但是这里需要注意,系统优化13、尽量避免大事务作,提高系统并发能力。器对基于last_name创建的索引没有使用。

当采用下面这种SQL语句的编写,Oracle系统就可以采用基于last_name创建的索引。

Select from employee

where

first_name ='Beill' and last_name ='Cliton'

遇到下面这种情况又如何处理呢?如果一个变量(name)中存放着Bill Cliton这个员工的姓名,对于这种情况我们又如何避免全程遍历,使用索引呢?可以使用一个函数,将变量name中的姓和名分开就可以了,但是有一点需 要注意,这个函数是不能作用在索引列上。下面是SQL查询脚本:

select from employee

where

first_name = SUBSTR('&&name',1,INSTR('&&name',' ')-1)

and

last_name = SUBSTR('&&name',INSTR('&&name’,' ')+1)

3. 带通配符(%)的like语句

同样以上面的例子来看这种情况。目前的需求是这样的,要求在职工表中查询名字中包含cliton的人。可以采用如下的查询SQL语句:

select from employee where last_name like '%cliton%'

这里由于通配符(%)在搜寻词首出现,所以Oracle系统不使用last_name的索 引。在很多情况下可能无法避免这种情况,但是一定要心中有底,通配符如此使用会降低查询速度。然而当通配符出现在字符串其他位置时,优化器就能利用索引。 在下面的查询中索引得到了使用:

select from employee where last_name like 'c%'

4. Order by语句

ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制,也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。

仔细检查order by语句以找出非索引项或者表达式,它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引,也可以为所使用的列建立另外一个索引,同时应避免在order by子句中使用表达式。

5. NOT

我们在查询时经常在where子句使用一些逻辑表达式,如大于、小于、等于以及不等于等等,也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子:

… where not (status =’VALID’)

如果要使用NOT,则应在取反的短语前面加上括号,并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中,这就是不等于(<>)运算符。换句话说,即使不在查询where子句中显式地加入NOT词,NOT仍在运算符中,见下例:

… where status <>’INVALID’

再看下面这个例子:

select from employee where salary<>3000;

对这个查询,可以改写为不使用NOT:

select from employee where salary<3000 or salary>3000;

虽然这两种查询的结果一样,但是第二种查询方案会比种查询方案更快些。第二种查询允许Oracle对salary列使用索引,而种查询则不能使用索引。

6. IN和EXISTS

有时候会将一列和一系列值相比较。简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。

种格式是使用IN作符:

… where column in(select from … where …);

第二种格式是使用EXIST作符:

… where exists (select ‘X’ from …where …);

我相信绝大多数人会使用种格式,因为它比较容易编写,而实际上第二种格式要远比种格式的效率高。在Oracle中可以几乎将所有的IN作符子查询改写为使用EXISTS的子查询。

第二种格式中,子查询以‘select ‘X’开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里定在where语句中使用的列存在索引)。相对于IN子句来说,EXISTS使用相连子查询,构造起来要比IN子查询困难一些。

通过使用EXIST,Oracle系统会首先检查主查询,然后运行子查询直到它找到个匹配项,这就节省了时间。Oracle系统在执行IN子查询时,首先执行子查询,并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前,系统先将主查询挂起,待子查询执行完毕,存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。

同时应尽可能使用NOT EXISTS来代替NOT IN,尽管二者都使用了NOT(不能使用索引而降低速度),NOT EXISTS要比NOT IN查询效率更高。

提高mysql查询效率的方法有哪些

例: select from A where a = 3 and b = 4 ;

1.尽量不要在where中包含子查询;

关于时间的查询,尽量不要写成:where

2.在过滤条件中,可以过滤掉数量记录的条件必须放在where子句的末尾;

FROM子句中写在的表(基础表,driving

table)将被处理,在FROM子句中包含多个表的情况下,你必须选择记录条数少的表作为基础表。如果有三个以上的连接查询,那就需要选择交叉表

(intersection

table)作为基础表,交叉表是指那个被其他表所引用的表;

3.采用绑定变量

4.在WHERE中尽量不要使用OR

5.用EXISTS替代IN、用NOT

EXISTS替代NOT

IN;

6.避免在索引列上使用计算:WHERE

SAL12>00;

7.用IN来替代OR:

WHERE

LOC_ID=10

OR

LOC_ID=15

OR

LOC_ID=20

8.避免在索引列上使用IS

NULL和IS

select from B where cc in (select cc from A) 效率高,用到了B表上cc列的索引;NOT

NULL;

9.总是使用索引的个列;

10.用UNION-ALL替代UNION;

11.避免改变索引列的类型:SELECT…FROM

EMP

WHERE

EMPNO=’123’,由于隐式数据类型转换,to_char(EMPNO)=’123’,因此,将不采用索引,一般在采用字符串拼凑动态SQL语句出现;

12.’!=’

将不使用索引;

13.优化GROUP

BY;

14.避免带有LIKE参数的通配符,LIKE

‘4YE%’使用索引,但LIKE

‘%YE’不使用索引

15.避免使用困难的正规表达式,例如select

from

customer

where

zipcode

like

“98___”,即便在zipcode上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改成select

from

customer

where

zipcode>”98000″,在执行查询时就会利用索引来查询,显然会大大提高速度;

16.尽量明确的完成SQL语句,尽量少让数据库工作。比如写SELECT语句时,需要把查询的字段明确指出表名。尽量不要使用SELECT

语句。组织SQL语句的时候,尽量按照数据库的习惯进行组织。

mysql 查询问题 关于 not in

MySQL中NOT IN语句对NULL值的处理2007-07-23 16:01mysql> SELECT COUNT(name) FROM CVE WHERE name NOT IN ('CVE-1999-0001', 'CVE-1999-0002');

+-------------+

| count(name) |

+-------------+

| 17629 |

+-------------+

1 row in set (0.02 sec)

mysql> SELECT COUNT(name) FROM CVE WHERE 这样,当我们进行WHERE age = 18 时,会将18通过相同的算法计算出一个hash值==>在hash表中找到对应的储存地址==>根据存储地址取得数据。name NOT IN ('CVE-1999-0001', 'CVE-1999-0002', NULL);

+-------------+

| count(name) |

+-------------+

+-------------+

1 row in set (0.01 sec)

当在子查询中出现NULL的时候,结果就一定是0了。查了一下手册,确实有这样的说法。所以实际采用了这样的查询:

SELECT COUNT(DISTINCT name)

FROM CVE

WHERE name NOT IN (SELECT cveID FROM cve_sig WHERE cveID IS NOT NULL)

顺便提一下MySfirstnameQL中正则表达式匹配的简单使用:

FROM Alarm

WHERE (CVE NOT RLIKE '^CVE-[0-9]{4}-[0-9]{4}$' OR CVE IS NULL)

当然,RLIKE也可以写作REGEXP,我个人倾向于使用RLIKE,因为拼写接近LIKE,可以见名知义。

mysql 查询 not in not like和in like啥区别啊

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

in是在某个范围内,加not,就是不在某个范围内了。like和not like是一样的道理。你可以自己写sql语句,看看查询结果,就知道什么意思了。

索引

2 中,加了not就表示当前记录的id不在(1,2,3)这个中出现时,该记录是符合要求的

4 中,表示当前记录的tt字段的内容中不符合 "%as%"这个字符串匹配条件时,该记录是符合要求的

fulltext

,其实是一个索引,like语句很难用索引,所以提升了效率。

fulltext

,支持多个字段检索。

搜索功能一般都用带有中文分词的开源产品,像xunsearch。

一般小项目用like就行了