手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表Tag:索引

全文索引的苦逼记事二

 继昨天的处理之后,又来新的笔记 ,这次的笔记纯粹是个人的测试,与实际条件有关,比如,我要查询的字段不超过varchar的255的长度,所以我才这么做。

昨天做普通索引后,1100万条记录,索引 为220M,改成全文索引后,索引文件为1.1G,存储空间上,涨了5倍左右。

以下是笔记 ,请不要笑话,场景不同而已

 

  • 经过测试
    • title 字段改为全文索引后,在1100万条的时候
      • 优点:
        • 速度也为0.0x秒级。速度非常快
        • 即使有or条件,只要带了limit参数,速度也非常快
      • 缺点:
        • 如果查询不带limit ,直接卡死,因为他要计算total count
        • select count() 卡死
        • 如果查询不存在的关键字,卡死
    • 使用方法
      • 尽量不做select count 查询 (数量低于100万时可以考虑,超过100万时,其实已经没有必要)
      • 查询一定要带上limit条件
      • 每次查询到不存在的关键字时,记录到关键词库,每次有新增记录时,select 关键词库一下,如果新增房间中有关键字,则将关键词去除,避免卡死
  • 暂时不使用coreseek(sphinx)/xunsearch等第三方工具
    • xunsearch只支持分词查询,不支持完全匹配
    • 第三方工具,耗内存,而且增量的时候,不够及时

Tags: 索引

全文索引的苦逼记事一

 这是一篇未完成的博客,在这里面做了一点记录

场景:需要做一个关于标题的模糊查询,只是记录有点多,而且需要相对精确,比如搜索:ac, 不能出现abc,可以接受acb,bac,之类。
测试:
1、100万数据,mysql / mongo ,在这种情况下。无论是查询什么数据,基本上都在0.00x秒级,
mysql的查询是like '%xxxx%' , mongo 是 {title:/xxxx/i} 
一般情况下,两者速度真心差不多,但如果查询一下不数据库中不存在的关键字,一般都在0.2秒至2秒左右,mongo会相对好一点,在0.5秒
 
2、500万~1000万数据
查询条件如上
mysql 查询的时候 cpu 占40%左右,20多秒 (mysql 1100万数据)
mongo 查询的时候 CPU占50%左右,10秒/8秒左右 (mongo 550万)
这种性能没法用啊
 
---下一步
1、xunsearch / coreseek(sphinx)
2、mysql 全文索引
 
需要再次测试一下。关键mysql虽然100万只有0.00x或者0.0x秒左右。但是如果多个并发的时候就会卡死了。
所以需要再次考虑 场景的复杂性

Tags: 索引

Mysql数据库索引查询优化的分享

这是昨天SAE分享的一篇文章,开始的时候,我看了一遍,发现好象没有什么特别的内容,但再仔细看的时候,发现居然可以这样做。。。

     问题描述:

      我们要访问的表是一个非常大的表,四千万条记录,id是主键,program_id上建了索引。

      执行一条SQL:

select * from program_access_log where program_id between 1 and 4000

      这条SQL非常慢。我们原以为处理记录太多的原因,所以加了id限制,一次只读五十万条记录

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000

      但是这条SQL仍然很慢,速度比上面一条几乎没有提升。Mysql处理50万条记录的表,条件字段还建了索引,这条语句应该是瞬间完成的。

      问题分析:

      这张表大约容量30G,数据库服务器内存16G,无法一次载入。就是这个造成了问题。

      这条SQL有两个条件,ID一到五十万和Program_id一到四千,因为program_id范围小得多,mysql选择它做为主要索引。

      先通过索引文件找出了所有program_id在1到4000范围里所有的id,这个过程非常快。

      接下来要通过这些id找出表里的记录,由于这些id是离散的,所以mysql对这个表的访问不是顺序读取。

      而这个表又非常大,无法一次装入内存,所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存,大量的IO操作导致了速度的下降。

      问题解决方案:

1. 以program_id为条件对表进行分区

2. 分表处理,每张表的大小不超过内存的大小

      然而,服务器用的是mysql5.0,不支持分区,而且这个表是公共表,无法在不影响其它项目的条件下修改表的结构。所以我们采取了第三种办法:

select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000

      现在program_id的范围远大于id的范围,id被当做主要索引进行查找,由于id是主键,所以查找的是连续50万条记录,速度和访问一个50万条记录的表基本一样

      总结:

      这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题,有一定的典型性和大家交流下!

-----------

看我标红的那一段,原来还能够这样做,以前真的没有注意过,也从来没有想过,先利用主键做一次过滤。这样效率会好很多啊

上述内容来自:http://ourmysql.com/archives/108?f=wb,该网站还有很多技巧值得一看。

Tags: sae, mysql, 索引

MySQL索引的知识点

数据库是否建索引以及如何更好的建索引,当然是有讲究的。虽然都知道建了索引后,在按条件搜索的时候速度会快很多,但其实,如果索引建的不合理,反而是使得数据库效率降低。何时需要建索引,其实是值得深究的,创建之后,怎样判断效率,当然还得好好学习。要多做尝试才会知道。

这一篇讲的比较详细,所以就转载了,来自冰山的http://xinsync.xju.edu.cn/index.php/archives/6845。

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

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL  
  4. );  

 

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

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

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

索引的类型包括:

(1)普通索引
这是最基本的索引,它没有任何限制。它有以下几种创建方式:
●     创建索引

SQL代码
  1. CREATE INDEX indexName ON mytable(username(length));  

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

SQL代码
  1. ALTER mytable ADD INDEX [indexName] ON (username(length))  

●     创建表的时候直接指定

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     INDEX [indexName] (username(length))  
  5. );  

 

删除索引的语法:

SQL代码
  1. DROP INDEX [indexName] ON mytable;  


(2)唯一索引

 

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

SQL代码
  1. CREATE UNIQUE INDEX indexName ON mytable(username(length))  

 

●     修改表结构

SQL代码
  1. ALTER mytable ADD UNIQUE [indexName] ON (username(length))  

 

●     创建表的时候直接指定

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     UNIQUE [indexName] (username(length))  
  5. );  

 

(3)主键索引

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

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     PRIMARY KEY(ID)  
  5. );  

 

当然也可以用 ALTER 命令。记住:一个表只能有一个主键。

(4)组合索引

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

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     city VARCHAR(50) NOT NULL,  
  5.     age INT NOT NULL  
  6. );  

 

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

SQL代码
  1. ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);  

 

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

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

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

SQL代码
  1. usernname,city,age  
  2. usernname,city  
  3. usernname  

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

SQL代码
  1. SELECT * FROM mytable WHREE username="admin" AND city="乌鲁木齐"  
  2. SELECT * FROM mytable WHREE username="admin"  

而下面几个则不会用到:

SQL代码
  1. SELECT * FROM mytable WHREE age=20 AND city="乌鲁木齐"  
  2. SELECT * FROM mytable WHREE city="乌鲁木齐"  

 

(5)建立索引的时机

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

SQL代码
  1. SELECT t.Name  
  2. FROM mytable t LEFT JOIN mytable m  
  3. ON t.Name=m.username WHERE m.age=20 AND m.city='乌鲁木齐'  

 

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

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

SQL代码
  1. SELECT * FROM mytable WHERE username LIKE'admin%'  

而下句就不会使用:

SQL代码
  1. 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 “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

●     不要在列上进行运算

SQL代码
  1. SELECT * FROM users WHERE YEAR(adddate)<2007;  

 

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

SQL代码
  1. SELECT * FROM users WHERE adddate< '2007-01-01';  

 

●     不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以使用NOT EXISTS代替,id<>3则可以使用id>3 or id<3来代替。

--EOF--

本博客内部关于MYSQL索引的相关文章有:

MYSQL索引之小小分析

 

 

 

 

 

 

 

 

 

 

 

 

 

 

[分享]mysql数据库索引查询优化的分享

联合索引的经典例子

当然,对于索引,肯定要看MYSQL中EXPLAIN的说明

Tags: mysql, index, 索引, 知识点, explain

联合索引的经典例子

1.SQL需求,统计当天的数据量。

SQL> SELECT count(*) FROM test_union WHERE win_type=1 AND gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1;

  COUNT(*)
----------
   20063

1 row selected.

2.查看其索引,以gmt_create开头。

sql>create index idx_union on test_union (gmt_create,win_type) tablespace tbs_index compute statistics;

3.查看awr报表的性能,逻辑读很高,达到9700个。

Buffer Gets   Executions  Gets per Exec %Total Time  Time (s) Hash Value
--------------- ---------- -------------- ------ -------- --------- ------
205,157,987    21,236      9,660.9      34.5  6733.21  7568.58 1532799124
Module: [email protected] (TNS V1-V3)
SELECT count(*) FROM test_union WHERE win_type=1 AND gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1

因为是只通过索引扫描,当看到返回结果集在2万左右,我们很容易估算出这个sql需要的逻辑读,(gmt_date字段7个字节+win_type字段1个字节+rowid+…)*2万,小于100个,现在很明显是偏高的。
4.调整前我们先去看数据分布。

SQL> select win_type,count(*) from test_union group by win_type;  --按win_type分组
  win_type   count(*)
---------- ----------
         0    8583162
         1    2725424
         2    765237
         3    2080156
         4    2090871
         5    3568682
SQL> select count(*) from test_union where gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1; --按gmt_create统计,一天数据量在22万左右

  COUNT(*)
----------
     229857

1 row selected.

5.调整索引,改为以win_type开头,为什么要以win_type开头呢?

create index idx_union123 on test_union (win_type,gmt_create) tablespace tbs_index compute statistics;  --新索引

6.查看其执行计划,逻辑读变成了89。

sql>set auto traceonly
sql> select count(*) from test_union
where win_type=1 and gmt_create >= trunc(sysdate,'dd') and gmt_create <= trunc(sysdate,'dd')+1;

Elapsed: 00:00:07.17

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=9)
   1    0   SORT (AGGREGATE)
   2    1     FILTER
   3    2       INDEX (RANGE SCAN) OF 'idx_union123' (NO
          N-UNIQUE) (Cost=7 Card=1 Bytes=9)
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         89  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

都在说建索引一定要看数据分布,从数据分布来看,一天的的数据(gmt_create)量在22万左右,而win_type的数据量非常 大,win_type为1有300万左右,为什么还要把win_type放在索引的前面呢?抛块砖,希望大家能对联合索引有更深入的理解,希望一起讨论。

--EOF--

帖子下的几个评论也很有意思:

XML/HTML代码
  1.  1.     1棉花糖ONE  
  2.   
  3.     hehe,所以我在itpub上经常建议人家如果是建复合索引的话,把范围查询的谓词尽量放在后面,这样能增加木匠说的有效索引选择度,对oracle执行计划的判断有意义,当然更主要的是这样创建索引确实是减少了索引扫描的范围,不知道楼主的环境还在不在,根据数据来看,这2个字段是相关的,而且楼主恰好查询的是 count(*),可以不扫描表,走复合索引就比较正常,如果是select * from … ,这样很可能就不会选择走索引,就11g以前除了动态采样,加hint,统计信息上应该是搞不对,我测试了11g的扩展列的统计信息,感觉这玩意效果没有想象中的理想  
  4.     Comment on Jan 10th, 2009 at 9:58 am    
  5.  2. 2木匠  
  6.   
  7.     如果没有搞清楚SQL optimizer engine and Run time engine怎么工作,怎么使用composite index,  
  8.     你的观测结果只是表面现象,永远也不知道为什么.  
  9.   
  10.     查看数据分布是对的,  
  11.     但是这种情况是需要寻根问底的. my CA$0.02 + tax, 我的一点建议.  
  12.   
  13.     木匠不辞劳苦,再重复一遍SQL optimizer and runtime engine 是怎么工作的:  
  14.   
  15.     as soon as we have a range scan on a column used somewhere in the  
  16.     middle of an index definition or fail to supply a test on such a column, the predicates on later columns do not restrict the selection of index leaf blocks that we have to examine.  
  17.   
  18.     In this case, the effective index selectivity has to be calculated from the predicate on just the “gmt_create” column. Because the test on “gmt_create” is range-based, the predicates on “win_type” do not restrict the number of index leaf blocks we have to walk.  
  19.   
  20.     关键字: effective index selectivity.  
  21.     Comment on Jan 10th, 2009 at 1:49 am    
  22.  3. 3丁原  
  23.   
  24.     这个案例已经很久了。  
  25.     实际上是没有打算写的,这个例子一直有人在问为什么,问的多了,我干脆就邮件中的内容整理出来,大家 讨论加深印象。  
  26.     我尝试把线上的数据拉下来做测试,发觉太大了根本拉不下来,只能是拼拼凑凑,gmt_create的索引已经drop掉了,部分测试数据可能不那么准确,但是数据还是在范围之内,不会偏差很大。  
  27.   
  28.     逻辑读怎么算?  
  29.     select (7+1+rowid+..)*20000/8192,差不多就是我们要的逻辑读。  
  30.   
  31.     to木匠:cost不一定准确的,我更多的是查看数据分布,结果集,以逻辑读来判断。  
  32.     to棉花糖:回复有审核的,防止很多垃圾广告。  
  33.     Comment on Jan 9th, 2009 at 5:11 pm    
  34.  4. 4carcase  
  35.   
  36.     win_type,gmt_create 扫描的索引块少多了,确定了win_type=1的范围后,再确定了是当天的数据  
  37.     (索引是win_type,gmt_create 排序的,范围扫描马上能定位到当天的数据)也就扫描了2万多就够了 ,速度得到了提升。  
  38.     和  
  39.     gmt_create,win_type 扫描的索引块多了很多,相当于扫描了 gmt_create当天所有的数据了,22万多数据都要扫描一遍,过滤出win_type=1 的数据  
  40.     Comment on Jan 9th, 2009 at 11:05 am    
  41.  5. 5棉花糖ONE  
  42.   
  43.     Your comment is awaiting moderation.  
  44.     到底咋回事啊  
  45.     Comment on Jan 9th, 2009 at 10:45 am    
  46.  6. 6棉花糖ONE  
  47.   
  48.     逻辑读89是不是搞错了  
  49.     Comment on Jan 9th, 2009 at 10:45 am    
  50.  7. 7jlttt  
  51.   
  52.     我们很容易估算出这个sql需要的逻辑读,(gmt_date字段7个字节+win_type字段1个字节+rowid+…)*2万,小于100个  
  53.     (7+1+10+…)×2W 所读的块怎么算出小于100的?  
  54.     Comment on Jan 9th, 2009 at 9:46 am    
  55.  8. 8棉花糖ONE  
  56.   
  57.     to 木匠:  
  58.     cardinality=1可能和9i有关,sysdate包含函数计算的时候,没有直方图的情况下,范围查询的选择度是按5%算的,2个5%*5%  
  59.     Comment on Jan 9th, 2009 at 9:42 am    
  60.  9. 9棉花糖ONE  
  61.   
  62.     如果把范围的放前面,索引的第二个字段只是起到filter的作用,并没有减少索引扫描的块,从索引的成本计算也能看出这一点  
  63.     Comment on Jan 9th, 2009 at 9:36 am    
  64. 10. 10木匠  
  65.   
  66.     对不起,再唠叨一句.  
  67.   
  68.     除了比较cost,还要比较Cardinality, 第二个SQL,index scan这一步的Card=1.  
  69.     但是我觉得index scan这一步的Cardinality应该大于一, 不知道哪里出了问题.  
  70.   
  71.     每一个Oracle版本的Cost and Card会有不同的结果.9.2.0.6 and 10.1.0.4或更高版本会提供更有用的信息.  
  72.   
  73.     (木匠真够粗心大意的, 请包涵)  
  74.     Comment on Jan 9th, 2009 at 2:26 am    
  75. 11. 11木匠  
  76.   
  77.     In this case, the effective index selectivity has to be calculated from the predicate on just the “gmt_create” column. Because the test on “gmt_create” is range-based, the predicates on “win_type” do not restrict the number of index leaf blocks we have to walk.  
  78.   
  79.     照搬老刘(Lewis)的原话, 改一下列名就行了. :)  
  80.   
  81.     上一个评论中的cost 指的是整个SQL(with index scan)的cost.  
  82.   
  83.     另外, 你忘了记录走第一个索引的SQL执行计划,可以比较一下 INDEX (RANGE SCAN) 的 cost.  
  84.   
  85.     INDEX (RANGE SCAN) OF ‘idx_union123′ (NON-UNIQUE) (Cost=7 Card=1 Bytes=9), 我们看到走第二个索引的(index range scan) cost=7.  
  86.     Comment on Jan 9th, 2009 at 2:03 am    
  87. 12. 12木匠  
  88.   
  89.     Q: 为什么还要把win_type放在索引的前面呢?  
  90.     A: leaf_blocks * effective index selectivity  
  91.   
  92.     第一个索引, range scan on gmt_create, first column on index.  
  93.     Because as soon as we have a range scan on a column used somewhere in the  
  94.     middle of an index definition or fail to supply a test on such a column, the predicates on later columns do not restrict the selection of index leaf blocks that we have to examine.  
  95.   
  96.     参考:  
  97.     cost =  
  98.     blevel +  
  99.     ceiling(leaf_blocks * effective index selectivity) +  
  100.     ceiling(clustering_factor * effective table selectivity)  
  101.   
  102.     a well-known guideline for arranging the columns in a multicolumn  
  103.     index. Columns that usually appear with range-based tests should generally appear later in the index than columns that usually appear with equality tests. Unfortunately, changing the column ordering in an index can have other contrary effects, which we will examine in Chapter 5.  
  104.   
  105.     Now you got test case. very well ! ^_^  
  106.   
  107.     Reference: page 74,62,67 of book Cost-Based Oracle Fundamentals  
  108.     Comment on Jan 9th, 2009 at 1:52 am    

Tags: 索引, mysql, 联合索引, 数据库

Records:712