mysql索引优化是什么意思,有哪些优化mysql索引的方式请举例

  mysql索引优化是什么意思,有哪些优化mysql索引的方式请举例

  MySQL索引分析和优化(to)-关注LAMP

  MySQL索引分析与优化(转自kcloze)

  发布时间:2012年5月15日星期二3点56分

  1.什么是指数?

  索引用于快速查找具有特定值的记录。所有的MySQL索引都是以B树的形式存储的。如果没有索引,MySQL在执行查询时必须从第一条记录开始扫描整个表的所有记录,直到找到符合要求的记录。表中的记录越多,这个操作的代价就越高。如果已经在列上创建了索引作为搜索条件,MySQL可以快速获得目标记录的位置,而无需扫描任何记录。如果表有1000条记录,通过索引搜索记录至少比顺序扫描快100倍。

  假设我们创建了一个名为people:

  1创建表people ( peopleid SMALLINT NOT NULL,name CHAR(50)NOT NULL);然后,我们在people表中随机插入1000个不同的名称值。下图显示了people表所在的数据文件的一小部分:

  如您所见,数据文件中的名称列没有明确的顺序。如果我们创建name列的索引,MySQL将在索引中对name列进行排序:

  对于索引中的每一项,MySQL都会在内部保存一个“指针”,指向它在数据文件中的实际记录位置。因此,如果我们想要找到名称等于“Mike”的记录的PeopleID(SQL命令是“Select PeopleID from people where Name= Mike ;)),MySQL可以在name的索引中查找“Mike”的值,然后直接转到数据文件中对应的行并准确返回该行的peopleid(999)。在这个过程中,MySQL只需要处理一行就可以返回结果。如果没有“姓名”列的索引,MySQL将扫描数据文件中的所有记录,即1000条记录!显然,MySQL需要处理的记录越少,它完成任务的速度就越快。

  第二,指数的类型

  MySQL提供了多种索引类型可供选择:

  总索引

  这是最基本的索引类型,它没有唯一性等限制。普通索引可以通过以下方式创建:

  1.在tablename(列的列表)上创建一个索引,如CREATE INDEX的名称;

  2.修改表,如alter table表名添加索引(列的列表);

  3.创建表时指定索引,例如CREATE TABLE TABLENAME ([.],index[索引的名称](列的列表);

  唯一性指数

  这个索引与前面的“普通索引”基本相同,但有一点不同:索引列的所有值只能出现一次,即必须唯一。可以通过以下方式创建唯一索引:

  1.创建一个索引,如在tablename(列列表)上创建唯一索引的名称;

  2.修改表,如ALTER TABLE tablename ADD UNIQUE[name of index](列的列表);

  3.创建表时指定索引,例如CREATE TABLE TABLENAME ([.],UNIQUE[索引名称](列列表);

  主键

  主键是唯一索引,但必须指定为“主键”。如果您曾经使用过AUTO_INCREMENT类型的列,您可能已经熟悉了主键这样的概念。通常,主键是在创建表时指定的,例如,“创建表表名([.],主键(列列表));"。不过我们也可以通过修改表来添加主键,比如“alter table表名添加主键”;" .每个表只能有一个主键。

  全文索引

  MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型是FULLTEXT。可以对VARCHAR或TEXT类型的列创建全文索引。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模数据集,使用ALTER TABLE(或CREATE INDEX)命令创建全文索引比在带有全文索引的空表中插入记录要快。下面的讨论不再涉及全文索引。更多信息,请参考MySQL文档。

  第三,单列索引和多列索引

  索引可以是单列索引,也可以是多列索引。下面通过具体的例子来说明两个指标的区别。假设有这样一个人表:

  12创建表people(peopleid SMALLINT NOT NULL AUTO _ INCREMENT,firstname CHAR(50) NOT NULL,lastname CHAR(50) NOT NULL,age SMALLINT NOT NULL,townid SMALLINT NOT NULL,PRIMARY KEY(peopleid));下面是我们插入到这个人员表中的数据:

  在这个数据剪辑中,有四个人的名字叫“迈克”(两个叫苏利文,两个叫麦康奈尔),两个17岁的人,还有一个不同名字的乔史密斯。

  该表的主要目的是根据指定用户的姓、名和年龄返回相应的peopleid。例如,我们可能需要找到一个17岁的用户的PeopleID,他的名字是Mike Sullivan(SQL命令是Select PeopleID from people,其中名字= Mike ,姓氏= Sullivan ,年龄=17岁;)。因为我们不希望MySQL每次执行查询时都扫描整个表,所以我们需要考虑在这里使用索引。

  首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或age列。如果我们创建firstname列的索引(alter table people add index first name);),MySQL会通过这个索引快速将搜索范围限制在那些名字=Mike 的记录,然后在这个“中间结果集”上搜索其他条件:它会先排除那些姓氏不等于“Sullivan”的记录,再排除那些年龄不等于17的记录。当记录满足所有搜索条件时,MySQL将返回最终的搜索结果。

  因为firstname列的索引是建立的,所以MySQL的效率远高于全扫描表的效率,但是我们要求MySQL扫描的记录数量仍然远远超过实际需要。虽然我们可以删除firstname列上的索引,并在lastname或age列上创建索引,但总的来说,无论在哪个列上创建索引,搜索效率都是相似的。

  为了提高搜索效率,我们需要考虑使用多列索引。如果为firstname、lastname和age列创建多列索引,MySQL只需一次搜索就能找到正确的结果!以下是创建该多列索引的SQL命令:

  1ALTER TABLE people添加索引fname_lname_age(名字,姓氏,年龄);因为索引文件是以B树格式保存的,所以MySQL可以立即转到相应的名字,然后转到相应的姓氏,最后转到相应的年龄。不用扫描数据文件的任何记录,MySQL就正确地找到了目标记录!

  那么,如果分别在firstname、lastname和age列上创建一个单列索引,是否与创建firstname、lastname和age的多列索引相同?答案是否定的,两者完全不同。当我们执行查询时,MySQL只能使用一个索引。如果您有三个单列索引,MySQL将尝试选择限制性最强的索引。但是,即使是限制最严格的单列索引,对名字、姓氏和年龄的限制也远不如多列索引。

  第四,最左边的前缀

  多列索引还有一个优点,就是通过称为最左前缀的概念来体现。继续前面的例子,现在我们在firstname、lastname和age列上有了一个多列索引。我们称这个索引为fname_lname_age。当搜索条件是以下列的组合时,MySQL将使用fname_lname_age索引:

  名字,姓氏,年龄

  名字,姓氏

  名字

  另一方面,它相当于在(名字,姓氏,年龄),(名字,姓氏)和(名字)的组合上创建一个索引。以下查询可以使用此fname_lname_age索引:

  12345678从名字=Mike 和姓氏=Sullivan 以及年龄=17 的人中选择peopleid选择people id FROM people WHERE first name= Mike AND last name= Sullivan ;选择people id FROM people WHERE first name= Mike ;以下查询根本无法使用该索引:SELECT people id FROM people WHERE last name= Sullivan ;从年龄=17 的人中选择peopleid从姓氏=Sullivan 和年龄=17 的人中选择peopleid五、选择索引列

  在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。有两种主要类型的列可以考虑用于索引:出现在WHERE子句中的列和出现在join子句中的列。请看下面的查询:

  23选择年龄# #不要使用名= Mike # #考虑使用索引,姓=Sullivan ##考虑使用索引。这个查询与前面的查询略有不同,但它仍然是一个简单的查询。因为在SELECT部分引用了年龄,所以MySQL不会用它来限制列的选择。因此,对于这个查询,没有必要创建age列的索引。这里有一个更复杂的例子:

  123456SELECT people.age,# #不使用索引town.name ##不使用people left join town on people . townid=town . townid # #考虑使用索引,其中firstname=Mike ##考虑使用索引,lastname=Sullivan ##考虑使用索引。与前面的示例一样,因为firstname和lastname出现在WHERE子句中,所以仍然有必要为这两列创建索引。此外,由于town表的townid列在join子句中,我们需要考虑创建该列的索引。

  那么,我们是否可以简单地认为,WHERE子句和join子句中出现的每一列都应该被索引?差不多,但不完全是。我们还必须考虑比较列的运算符的类型。MySQL只对以下操作符使用索引:=,=,=,=,BETWEEN,in,有时还有LIKE。可以在LIKE操作中使用索引的情况是指另一个操作数不以通配符(%或_)开头的情况。例如

  1从名字中包含“Mich%”的人中选择peopleid该查询将使用索引,但是

  1从名字类似“%ike”的人中选择peopleid该查询将不使用索引。

  不及物动词指数效率分析

  现在我们知道了一些如何选择索引列的知识,但无法判断哪一个最有效。MySQL提供了内置的SQL命令帮助。

  来自:http://www . kuqin . com/database/2008 09 22/19367 . html

郑重声明:本文由网友发布,不代表盛行IT的观点,版权归原作者所有,仅为传播更多信息之目的,如有侵权请联系,我们将第一时间修改或删除,多谢。

相关文章阅读

  • 使用php连接mysql数据库,php连接数据库的方法
  • 使用php连接mysql数据库,php连接数据库的方法,一文详解PHP连接MySQL数据库的三种方式
  • pymysql菜鸟教程,pymysql 使用
  • pymysql菜鸟教程,pymysql 使用,pymysql模块使用简介与示例
  • mysql锁实现,mysql锁算法
  • mysql锁实现,mysql锁算法,MySQL锁机制与用法分析
  • mysql连接报错10061,mysql连接错误10060
  • mysql连接报错10061,mysql连接错误10060,MYSQL无法连接 提示10055错误的解决方法
  • mysql连接报10060错误,mysql连接报错10055
  • mysql连接报10060错误,mysql连接报错10055,MySQL连接异常报10061错误问题解决
  • mysql辅助索引和主键索引,mysql 主键 外键 索引
  • mysql辅助索引和主键索引,mysql 主键 外键 索引,MySQL索引之主键索引
  • MySQL语句大全,mysql常见语句总结
  • MySQL语句大全,mysql常见语句总结,MySQL语句整理及汇总介绍
  • mysql触发器的使用方法实验报告,mysql中触发器的使用
  • 留言与评论(共有 条评论)
       
    验证码: