索引优化

      如果针对sql语句已经没啥可以优化的，那我们就要考虑加索引了。

      一、索引的概要:

        索引对查询的速度有着至关重要的影响，理解索引也是进行数据库性能调优的起点。考虑如下情况，假设数据库中一个表有

10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有

数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为

10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3

次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是

否可以建索引。

     二、索引的分类:

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。

MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换。　　

MEMORY/HEAP存储引擎：支持HASH和BTREE索引。

 

分类：

索引我们分为四类来讲单列索引(普通索引，唯一索引，主键索引)、组合索引、全文索引、空间索引、

　　2.1 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。这里不要搞混淆了。

　　2.2 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快

                                一点。

        2.3  唯一索引：索引列中的值必须是唯一的，但是允许为空值

        2.4  主键索引：是一种特殊的唯一索引，不允许有空值。（主键约束，就是一个主键索引）

        2.5  组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，

                                 使用组合索引时遵循最左前缀集合。例如，这里由id、name和age3个字段构成的索引，索引行中就按

                                 id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询

                                 的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用

                                 引查询

　　2.6 全文索引：全文索引，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引，介绍了要求，说说什么是全文索引，

                               就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。这里说的是可能，因为全文

                                索引的使用涉及了很多细节，我们只需要知道这个大概意思。

　　 2.7 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，

                                GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引

                                 擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

      

三、为什么说B+-tree比B 树更适合实际应用中操作系统的文件索引和数据库索引？

 

1.B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对于B树更小。如果把所有同一内部结点的关键字存放在

同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也

就降低了。

 

2. B+-tree的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到

叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
 

3.MySQL索引实现
　　MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照

B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。而在

InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶结点data域保存了完整的数据记录。

 

4.有可能造成索引失效的几种情况

   索引以最左前缀原则使用的~

    4.1  使用like关键字模糊查询时，% 放在前面索引不起作用，只有“%”不在第一个位置，

           索引才会生效（like '%文'--索引不起作用）

    4.2  使用联合索引时，只有查询条件中使用了这些字段中的第一个字段，索引才会生效

　 4.3  使用OR关键字的查询，查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生

            效，否则索引不生效。

     4.4  尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

　 4.5  对查询进行优化，应尽量避免全表扫描，首先应考虑在where以及order by涉及的列上建立索引。

 　4.6  应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

            select id from t where num/2=100  应改为:  select id from t where num=100*2

 　4.7   尽量避免在where子句中对字段进行函数操作,将导致引擎放弃使用索引而进行全表扫描。

    4.8   不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

　 4.9   并不是所有的索引对查询都有效，sql是根据表中的数据来进行查询优化的，当索引列有大量数据重复时，sql查询不会

             去利用索引，如一表中有字段sex，male,female几乎各占一半，那么即使在sex上建立了索引也对查询效率起不了作用。

　 4.10   索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert

               或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，

 　　　　若太多则应考虑一些不常使用到的列上建的索引是否有必要。

     4.11    尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，

                并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比

                较一次就够了。

     4.12    mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。

　　　　 因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列

                建复合索引。

     4.13    order by 索引 ，不起作用的问题（除了主键索引之外）：

                1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；

                2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！