关闭

MySQL索引

59人阅读 评论(0) 收藏 举报
分类:

参考文章:

http://blog.csdn.net/wulex/article/details/69540136

http://blog.csdn.net/gol_phing/article/details/47100619

在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构

1,创建索引

对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。

2,复合索引

比如有一条语句是这样的:select * from users where area=’beijing’ and age=22; 
如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效 
率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。
如果我们创建了(area, age, 
salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀 
特性
。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。

3,索引不会包含有NULL值的列

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

4,使用短索引

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

5,排序的索引问题

mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。

6,like语句操作

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

7,不要在列上进行运算

select * from users where 
YEAR(adddate)

8,不使用NOT IN和操作

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

多列索引和多个单列索引


对于要经常查询的含量大量数据的数据库,建立索引是非常重要的,建立索引一般都是在where语句用得较多的列上。现在有个问题,如果一个表有多个列需要建立索引,是把所有列建成一个索引,还是对每一个列建一个索引,上篇文章做了一个介绍,这是作者得出的结论,Conclusion: For benchmarked queries we can see Multiple Column index beats Index Merge in all cases when such index can be used. It is also worth to watchout a MySQL may decide not to do Index merge (either intersection or union) but instead do full table scan or access table picking only one index on the pair.意思应该是说对多个列建索引比对每个列分别建索引更有优势,而且要知道索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。

另外一篇文章How to create a successful multi-column index 则介绍了如何去建立一个多列索引,最重要的一个问题是如何安排列的顺序是至关重要的,比如需要对一个表里面的两个字段foo,bar建一个索引,那么索引的顺序是(foo,bar)还是(bar,foo)呢。在搞清楚如何安排顺序之前先了解一个概念,cardinality:金山的翻译是"集的势",比如,tb表有1700条记录,foo字段有750个不同的记录,那么就可以说We have a cardinality of 750 for foo。总规则可以说是cardinality越大的字段应该排在索引的第一位就是说索引的位置是(foo,bar),因为cardinality越大那么第一次取出来的记录集就越小,再进行第二次查询的次数就越少了。不过这只是对于建两个索引的规则,如果是三个以上就没有那么简单了,具体地看原文,有比较详细的例子。还需要提出的是即使我们建了一个很有效的索引,但是查询优化器也许会选择不用它,如果它会考虑更多因素以决定这个索引是否有足够的效率。It was also pointed out to me, that even if an efficient multi-column index is created, the query optimizer may choose to never use it. This is because the optimizer looks at further statistics to determine if the index would be efficient enough or not.

 

 

 

Mysql数据库提供两种类型的索引,如果没正确设置,索引的利用效率会大打折扣却完全不知问题出在这。

CREATE TABLE test (

    id         INT NOT NULL,

    last_name  CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    PRIMARY KEY (id),

    INDEX name (last_name,first_name)

);

以上创建的其实是一个多列索引,创建列索引的代码如下:

[c-sharp] view plaincopy

CREATE TABLE test (  

    id         INT NOT NULL,  

    last_name  CHAR(30) NOT NULL,  

    first_name CHAR(30) NOT NULL,  

    PRIMARY KEY (id),  

    INDEX name (last_name),  

     INDEX_2 name (first_name)  

);  

一个多列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。 当查询语句的条件中包含last_name 和 first_name时,例如:

[c-sharp] view plaincopy

1. SELECT * FROM test WHERE last_name='Kun' AND first_name='Li';  

sql会先过滤出last_name符合条件的记录,在其基础上在过滤first_name符合条件的记录。那如果我们分别在last_namefirst_name上创建两个列索引,mysql的处理方式就不一样了,它会选择一个最严格的索引来进行检索,可以理解为检索能力最强的那个索引来检索,另外一个利用不上了,这样效果就不如多列索引了。

 

但是多列索引的利用也是需要条件的,以下形式的查询语句能够利用上多列索引:

[c-sharp] view plaincopy

1. SELECT * FROM test WHERE last_name='Widenius';  

2.   

3. SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';  

4.   

5. SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty');  

6.   

7. SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N';  

以下形式的查询语句利用不上多列索引:

[c-sharp] view plaincopy

1. SELECT * FROM test WHERE first_name='Michael';  

2.   

3. SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael';  

 

多列建索引比对每个列分别建索引更有优势,因为索引建立得越多就越占磁盘空间,在更新数据的时候速度会更慢。

另外建立多列索引时,顺序也是需要注意的,应该将严格的索引放在前面,这样筛选的力度会更大,效率更高。




0
0
查看评论

MYSQL索引详解(学习记录)

一:什么是索引 1、索引   索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。 2. 索引,是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录,查找字典内容时可以根据目录查找到数据的存放位置,然后直接获取即...
  • w670268787
  • w670268787
  • 2016-08-08 09:12
  • 661

mysql索引深入理解

详解b+树 如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁...
  • liyantianmin
  • liyantianmin
  • 2017-02-13 17:27
  • 415

MySQL索引使用方法及实例学

索引用于快速找到特定一些值的记录。如果没有索引,MySQL就必须从第一行记录开始读取整个表来检索记录。表越大,资源消耗越大。如果在字段上有索引的话,MySQL就能很快决定该从数据文件的哪个位置开始搜索记录,而无须查找所有的数据。如果表中有1000条记录的话,那么这至少比顺序地读取数据快100倍。注意...
  • hi_jess
  • hi_jess
  • 2009-10-08 11:30
  • 2675

[面试时]MySQL索引背后的数据结构及算法原理

文章转自:http://blog.codinglabs.org/articles/theory-of-mysql-index.html摘要本文以MySQL数据库为研究对象,讨论与数据库索引相关的一些话题。特别需要说明的是,MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQ...
  • shawjan
  • shawjan
  • 2015-10-05 20:58
  • 602

MySQL:索引工作原理

MySQL索引原理详解:为什么需要索引(Why),什么是索引(What),什么时候用索引(When),索引怎么工作的(How)
  • iefreer
  • iefreer
  • 2013-11-15 16:15
  • 68393

Oracle与Mysql主键、索引及分页的区别小结

1、主键,Oracle不可以实现自增,mysql可以实现自增。  oracle新建序列,SEQ_USER_Id.nextval  2、索引:  mysql索引从0开始,Oracle从1开始。  3、分页,  mysql:...
  • u013047660
  • u013047660
  • 2014-03-12 20:45
  • 1043

mysql索引的增删改查

创建索引的语法是: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON tbl_name (index_col_name,...) index_col_name: col_nam...
  • longxingzhiwen
  • longxingzhiwen
  • 2016-12-27 14:57
  • 138

Mysql 数据库优化4---索引缓存

mysql数据库配置优化 缓存索引相关参数:  query_cache 缓存结果集,极高效,与sql语句一一对应,也有弊端,  binlog_cache_size 缓存binlog数据,影响所有写入操作的性能,我们服务器上设置的是32768,4G内存  thread_c...
  • zhuxineli
  • zhuxineli
  • 2012-09-07 13:57
  • 1075

对于MYSQL索引的一些理解

同样是基于昨天的一道面试题,面试官先是问了下MYSQL的索引是什么结构,我随口就说是B+树的索引,然后他接着追问,B+树索引的特点是什么,然后,就没有然后了。。
  • bingham2015
  • bingham2015
  • 2015-05-06 12:20
  • 269

MySQL索引优缺点

一、为什么要创建索引呢(优点)? 创建索引可以大大提高系统的性能。 第一,   通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 第二,   可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 第三,  ...
  • qishouzhang
  • qishouzhang
  • 2015-08-30 11:21
  • 1117
    个人资料
    • 访问:65005次
    • 积分:1105
    • 等级:
    • 排名:千里之外
    • 原创:40篇
    • 转载:6篇
    • 译文:0篇
    • 评论:16条
    文章分类
    最新评论