如果有一大堆数据让你去查某一条，比如学生成绩单，看一个人的成绩，一般是从头查到尾，但是如果数据量很大，当然查起来很慢。
                   但是如果这些数据排好顺序，那就好办多了,这样查起来最快，先查中间，然后看学号是大还是小，然后递次往前或往后二分查找，理解这一点，数据索引对查询效率的影响就豁然开朗了。

                   hash索引不属于有序索引，直接定位，效率更高，常用于key-value数据库，但不能提供排序，大小比较等查询。
 
          索引缺点
                  额外的创建时间
                  额外的存储空间（单列索引占原表5%至15%空间，想象一下如果为一个表创建三四个索引）
                  额外的维护时间
 

索引的优化效果非常明显，能够立马提高百倍千倍的查询速度(这里有例子)，但是查询速度的提高是以更新、删除、插入的速度为代价的，因为给这些写数据库操作增加量大量的I/O。

索引的增删改查
常用的索引有主键索引 、 唯一索引 、 普通索引 、 全文索引 、 空间索引

1，查看索引
desc tb_name
​show keys from tb_name
show index from​ tb_name
show indexes from​ tb_name

2，创建索引
创建主键索引 primary key

一种是创建表的时候直接创建主键索引

一种是先建表再建索引 alter table tb_name add primary key (colum1, colum2...)

主键索引的特点：

一个表只能有一个主键索引，但是一个主键可以指向多个列
主键索引是唯一的且不能为null
主键索引效率最高，我们应该给id做主键索引，并让id自增


创建唯一索引 unique

一种是建表的时候直接创建 create table tb1(id int primary key, name varchar(20) unique);
一种是建完表再创建唯一索引 create unique index index_name on tb_name (col1, col2...)
一张表唯一索引可以有多个
唯一索引可以为null
唯一索引效率很高


创建普通索引 index

create index index_name on tb_name(col1, col2...);
alter table tb1_name add index (col1, col2...);
一张表可以有多个普通索引，一个普通索引可以指向多个列
普通索引的数据可以重复
效率比唯一索引要低


创建全文索引

create fulltext index index_name on tb_name(col1, col2);

说明： 全文索引使用 match(col1, col2....) against ('str') 来查询
停止词，mysql全文索引不对特别普通的词建索引，如a
默认不支持中文，可以使用mysqlcft插件来支持中文搜索，强大的sphinx中文版Coreseek 也可以支持中文搜索


3，删除索引

drop index index_name on tb_name
​ALTER TABLE table_name DROP INDEX index_name;


4，索引的原理
索引使用的是二叉树算法，所以速度快

5，哪些字段适合建索引

出现在where条件里面的字段适合建索引
频繁作为查询条件的字段适合加索引
经常更新的字段不适合加索引
不会出现在where条件里面的不要加索引
性别字段不适合加索引


注意：

like的字符串前面如果有%、_（�c），使用不到索引，%_放在条件后面(abc%)可以使用索引,like的效率非常低，建议使用sphinx来做全文检索
or条件的前后都加了索引，才会使用索引
列类型是字符串类型，条件里面也要用字符串，省略了引号，不会使用索引
group by col1 order by null 在分组后面加上order by null提高效率
用join代替子查询，减少临时表的使用

 

 

索引的目的在于提高查询效率，本文给大家介绍Mysql使用索引实现查询优化技巧

 

1.索引的优点

 

假设你拥有三个未索引的表t1、t2和t3，每个表都分别包含数据列i1、i2和i3，并且每个表都包含了1000条数据行，其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示：

?
1
2
3
SELECT t1.i1, t2.i2, t3.i3
FROM t1, t2, t3
WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

　　这个查询的结果应该是1000行，每个数据行包含三个相等的值。

     如果在没有索引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办法知道哪些数据行含有哪些值的。

     因此你必须尝试所有的组合来查找符合WHERE条件的记录。

     可能的组合的数量是1000 x 1000 x 1000（10亿！），它是匹配记录的数量的一百万倍。这就浪费了大量的工作。

     这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。

 

     我们可以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来处理：

　　1．选择表t1中的第一行并查看该数据行的值。

　　2．使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t3上的索引，直接定位到与表t2的值匹配的数据行。

　　3．处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。

 

　　在这种情况下，我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上执行索引查找，从这些表中直接地获取数据行。

      理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。

      然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能提高。
-

 

 

 

2.索引的代价

          首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。

          也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。

          数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。

          在本文的”高效率载入数据”部分中，我们将更细致地了解这些现象并找出处理方法。

 

　　其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：

　　· 对于MyISAM表，频繁地索引 可能引起 索引文件比数据文件更快地达到最大限制。

　　· 对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种表更快地达到最大文件限制。

　　· 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。

           但是，与MyISAM和BDB表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。

            只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空间。

 

　　使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的数据和索引值都存储在单个文件中。

 

　　这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就不要建立索引。

 

3.选择索引

　　假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。

      这要求我们考虑数据表的使用方式。

      这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：

　　用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列：

?
1
2
3
4
5
6
7
SELECT
col_a <- 不是备选列
FROM
tbl1 LEFT JOIN tbl2
ON tbl1.col_b = tbl2.col_c <- 备选列
WHERE
col_d = expr; <- 备选列

　　当然，显示的数据列与WHERE子句中使用的数据列也可能相同。

      我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。

　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。

      前面显示的查询中的col_b和col_c就是这样的例子。

      如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。

　　但是对于查询一个值的范围效果就非常差了：

?
1
2
id < 30
weight BETWEEN 100 AND 150

　　

?
1
2
3
4
5
6
CREATE TABLE lookup
(
id INT NOT NULL,
name CHAR(20),
PRIMARY KEY USING BTREE (id)
) ENGINE = MEMORY;

　　

 

 

*4.建索引的几大原则*

 

4.1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

 

4.2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

 

4.3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

 

4.4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = '2014-05-29'就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp('2014-05-29');

 

4.5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

 转自::http://www.jb51.net/article/89450.htm