MySQL的优化之索引分析

今天说到利用索引优化MySQL，就不得不先说下索引了，索引是用来快速地寻找具有特定值的记录的，如果没有索引，执行查询时MySQL必须从第一条记录开始扫描整个表的记录，直到找到符合我们要求的记录，这样子一旦表中记录数据庞大，操作的代价是不可想象的，但是如果我们在搜索条件的列上创建了索引，那么MySQL就可以迅速找到目标记录所在的位置，有一个很好的比喻，说白了索引就是一本书的目录，这样子讲大家就容易理解很多了。

加入现在有一张存储了1000条记录的people表，我们创建一个name列的索引，MySQL就会在索引中排序name列，对于索引中的每一项，MySQL在内部都会为它保存一个数据文件中实际记录所在位置的“指针”，此时当我们查询name为"伊桑"记录的peopleid时，MySQL就能够在name的索引中寻找"伊桑"值，然后直接转到数据文件中响应的行并返回，在整个过程中，MySQL仅处理一个行的记录就可以返回结果，这就大大提高的MySQL的执行效率了。

创建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))

删除索引的语法：DROP INDEX index_name ON tableName

了解了索引和它最基础的作用，接下来就讲讲MySQL的索引类型:

    主键索引  PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

    唯一索引  UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE (`column`)

    普通索引  INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

    全文索引  FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column` )

    组合索引  ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

Mysql各种索引区别：

    普通索引(INDEX)：最基本的索引，没有任何限制
    唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
    主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
    全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。
    组合索引(多例索引)：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

建立索引是MySQL优化很基础的一步，为表建立多例索引可以更好的优化我们的数据库。

用电商项目举个例，众所周知电商项目的数据量是非常庞大的，在数据库中有一张用户表用于存储用户的信息，比如后台管理人员想查询23岁的"张三"的订单相关信息，我们肯定要先返回这个用户的id值，然后根据id值去查询与其关联的订单，这就涉及一个查询用户的效率问题。

通过SELECT userid FROM user WHERE firstname='张' AND lastname='三' AND age=23;查询到用户id，但此时是扫描整张表，效率很低，我们可以考虑创建firstname列的索引(ALTER TABLE user ADD INDEX firstname (firstname);),MySQL将通过这个索引迅速把范围限制到firstname=“张”的记录上，然后再在这个“中间结果集”上进行其他条件的搜索，但是考虑到一旦查询条件很多时，查询效率仍旧很低，我们可以想到的解决办法之一是在第一个条件搜索到的时候删除它的索引，再简历第二个搜索条件的索引，但其实这样做效率并没有提高。

所以，在这个项目中我使用了多列索引，为查询条件的列创建一个多列索引，MySQL只需要一次检索就能检索出我们想要的结果，（ALTER TABLE user ADD INDEX fname_lname_age (firstname,lastname,age);）,根据这种情况下，其实MySQL是没有扫描任何一个记录的，但仍帮我们正确找出了搜索结果(原理是索引文件以B-树格式保存，具体的本人也没深入研究过)。

那么建立多例索引跟分别在查询条件的列上分别建立单例索引的效果是否一样的呢？答案很显然是否定的。因为在执行查询时，加入你有多个索引，那么MySQL也只会试图选择一个限制最严格的索引，而这样的效率还是低于多例索引的。

注意注意敲黑板画重点了！！

多例索引是必须遵守最左前缀的，像之前我创建的那个多例索引fname_lname_age，当搜索条件满足

firstname，lastname，age

firstname，lastname

firstname

的时候都是可以使用的，前提是搜索条件中必须得有firstname这个列，这也就是之前提到的只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用！

一个索引能被拆分成这么多组合查询，这也是多例索引对于数据库优化更深层次的体现。

在性能优化过程中，选择在哪些列上创建索引是最重要的步骤之一

可以考虑使用索引的主要有两种类型的列：在WHERE子句中出现的列，在join子句中出现的列。

例如：

SELECT user.age, ##不使用索引，因为在select部分被使用，MySQL不会用这个列来限制列选择操作

town.name ##不使用索引

FROM user LEFT JOIN town ON

user.townid=town.townid ##考虑使用索引

WHERE firstname='张' ##考虑使用索引

AND lastname='三' ##考虑使用索引

由于town表的townid列出现在join子句中，因此我们需要考虑创建该列的索引。那么，我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此，但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如，“SELECT userid FROM user WHERE firstname LIKE 'Mich%';”这个查询将使用索引，但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引!!

拿我之前在联华超市做的项目来讲，其中有一个会员表

有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)

会员备注信息 TEXT   

那么这个表中会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，就创建普通的 INDEX
会员身份证号码 如果要建索引的话，选择 UNIQUE （唯一的，不允许重复且为了隐私，使用了md5加密，这也是为什么不直接为这个列创建索引来查询用户的原因）

会员备注信息 ， 如果需要建索引的话，可以选择 FULLTEXT，全文搜索。

选择性地为列创建适合索引类型，能更好地优化我们的数据库

索引的不足之处

看到这里这里大家肯定会有疑问，既然索引有这么多好处，为什么我们不一股脑全部使用索引来优化数据库呢？那是因为索引也有它自己的致命缺点，首先虽然索引提高了查询的效率，但同时也降低了数据库增删改的效率，因为我们在保存数据的同时，还要保存索引，其次建立索引也会占用内存，假设我们数据很庞大，一旦建立过多索引，索引文件是很膨胀的。

总结:索引只是优化数据库的一部分，我们仍需要优化我们的sql语句才能更好地优化数据库。