mysql解密字符串

文章目录

• 深度理解Mysql数据库索引

• Mysql索引的基本概念
• 索引分类

• Mysql中索引的语法

• 创建索引
• 删除索引
• 查看表中的索引
• 查看查询语句使用索引的情况

• 索引的优缺点

• 优点
• 缺点

• 索引的实现原理

• 哈希索引：
• 全文索引：
• BTree索引和B+Tree索引

• BTree索引
• B+Tree索引

• 为什么常用的关系型数据库采用B+Tree做索引而不是其它Tree（如B-Tree）

• 计算机数据存储基本原理回顾
• InnoDB引擎的数据存储原理
• 为什么一棵B+树可以存2千万行数据？
• B-Tree 和 B+Tree优劣对比
• 如何得到InnoDB主键索引B+树的高度？
• 总结

• B+Tree数据查找示例
• InnoDB 引擎数据存储示例
• InnoDB 与 MyISAM 引擎对比
• 索引的使用策略及优化

• 使用策略

• 什么时候要使用索引？
• 什么时候不要使用索引？
• 索引失效的情况：
• 索引的优化：

• 索引在磁盘上的存储示例

深度理解Mysql数据库索引

Mysql索引的基本概念

MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

• MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
• InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

索引分类

常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引

• 主键索引：即主索引，根据主键pk_clolum（length）建立索引，不允许重复，不允许空值；
    ALTER TABLE ‘table_name’ ADD PRIMARY KEY pk_index(‘col’)；
• 唯一索引：用来建立索引的列的值必须是唯一的，允许空值
    ALTER TABLE ‘table_name’ ADD UNIQUE index_name(‘col’)；
• 普通索引：用表中的普通列构建的索引，没有任何限制
    ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)；
• 全文索引：用大文本对象的列构建的索引（下一部分会讲解）
    ALTER TABLE ‘table_name’ ADD FULLTEXT INDEX ft_index(‘col’)；
• 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值
    ALTER TABLE ‘table_name’ ADD INDEX index_name (‘col1’, ‘col2’, ‘col3’)；

注意：

• 主键索引（又称为聚集索引），在数据库中，所有的行数都会按照主键进行排序。
• 非聚集索引，就是给普通字段加上索引。
• 联合索引，就是好几个字段组成的索引。

Mysql中索引的语法

创建索引

• 在创建表的时候添加索引
  CREATE TABLE mytable(
  ID INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  INDEX [indexName] (username(length))
  );
• 在创建表以后添加索引
    ALTER TABLE my_table ADD [UNIQUE] INDEX index_name(column_name);

或者
  CREATE INDEX index_name ON my_table(column_name);

注意：
  1、索引需要占用磁盘空间，因此在创建索引时要考虑到磁盘空间是否足够

  2、创建索引时需要对表加锁，因此实际操作中需要在业务空闲期间进行

删除索引

  DROP INDEX my_index ON tablename；
或者
  ALTER TABLE table_name DROP INDEX index_name;

查看表中的索引

  SHOW INDEX FROM tablename

查看查询语句使用索引的情况

  //explain 加查询语句
  explain SELECT * FROM table_name WHERE column_1=‘123’;

索引的优缺点

优点

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据检索的速度（大大减少检索的数据量），这也是创建索引的主要原因。
• 帮助服务器避免排序和临时表
• 将随机IO变为顺序IO（较少IO次数）
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
• 根据索引分组和排序，可以加快分组和排序

缺点

• 索引本身也是一张表（索引表），索引会额外的占用一部分物理空间，通常索引表所占空间为数据表的1.5倍。
• 索引表的创建和维护需要时间成本，这个成本将随着数据量的增大而增大。
• 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因此在修改数据表时也需要维护索引表。

索引的实现原理

  MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，B+Tree索引，哈希索引，全文索引等等

哈希索引：

  只有memory（内存）存储引擎支持哈希索引，哈希索引用索引列的值计算该值的hashCode，然后在hashCode相应的位置存执该值所在行数据的物理位置，因为使用散列算法，因此访问速度非常快，但是一个值只能对应一个hashCode，而且是散列的分布方式，因此哈希索引不支持范围查找和排序的功能。

全文索引：

  FULLTEXT（全文）索引，仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

  FULLTEXT可以在创建表的时候创建，也可以在需要的时候用ALTER或者CREATE INDEX来添加：

//创建表的时候添加FULLTEXT索引
CTREATE TABLE my_table(
id INT(10) PRIMARY KEY,
name VARCHAR(10) NOT NULL,
my_text TEXT,
FULLTEXT(my_text)
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

//创建表以后，在需要的时候添加FULLTEXT索引

ALTER TABLE my_table ADD FULLTEXT INDEX ft_index(column_name);

全文索引的查询也有自己特殊的语法，而不能使用LIKE %查询字符串%的模糊查询语法

SELECT * FROM table_name MATCH(ft_index) AGAINST('查询字符串');

注意：

• 5.6版本前的MySQL自带的全文索引只能用于MyISAM存储引擎，如果是其它数据引擎，那么全文索引不会生效。5.6版本之后InnoDB存储引擎开始支持全文索引
• 在MySQL中，全文索引支队英文有用，目前对中文还不支持。5.7版本之后通过使用ngram插件开始支持中文。
• 在MySQL中，如果检索的字符串太短则无法检索得到预期的结果，检索的字符串长度至少为4字节，此外，如果检索的字符包括停止词，那么停止词会被忽略。

BTree索引和B+Tree索引

BTree索引

基本概念：
  BTree是平衡多叉搜索树（多叉平衡搜索树），它是在二叉平衡搜索树的基础上的改进，我们已知二叉树在进行删除和添加操作时有可能退化为一个线性链表，因此引进了平衡二叉树；在平衡二叉树中，要求左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树。在平衡二叉搜索树中我们需要查找一个数的时候是沿着树根一直往下找，这样的查找效率和二分法查找是一样的。（二叉搜索树中，非叶子节点的左节点小于它本身，右节点大于它本身；因此在查询时类似于二分查找。）

  对于平衡二叉搜索树而言当数据量很大的时候，树的深度也将很深，因此在查找操作时所需要进行比较的此时也更多。（100w 数据树的高度大概在 20 左右，也就是说从有着 100w 条数据的平衡二叉树中找一个数据，最坏的情况下需要 20 次查找。）
  如果是内存操作，效率也是很高的!但是我们数据库中的数据基本都是放在磁盘中的，每读取一个二叉树的结点就是一次磁盘 IO，这样我们找一条数据如果要经过 20 次磁盘的 IO?那性能就成了一个很大的问题了!因此需要将树的高度进行压缩——平衡多叉搜索树（BTree）BTree具有以下特点：

• 每个结点最多 m 个子结点。
• 除了根结点和叶子结点外，每个结点最少有 m/2(向上取整)个子结点。
• 如果根结点不是叶子结点，那根结点至少包含两个子结点。
• 所有的叶子结点都位于同一层。
• 每个结点都包含 k 个元素(关键字)，这里 m/2≤k。
• 每个节点中的元素(关键字)从小到大排列。
• 每个元素(关键字)字左结点的值，都小于或等于该元素(关键字)。右结点的值都大于或等于该元素(关键字)。
  
  普通的 B-Tree 的结点中，元素就是一个个的数字。B-树的搜索，从根结点开始，对结点内的关键字（有序）序列进行二分查找，如果命中则结束，否则进入查询关键字所属范围的儿子结点；重复，直到所对应的儿子指针为空，或已经是叶子结点；

特性

1.关键字集合分布在整颗树中；
   2.任何一个关键字出现且只出现在一个结点中；
   3.搜索有可能在非叶子结点结束；
   4.其搜索性能等价于在关键字全集内做一次二分查找；
   5.自动层次控制；

搜索性能：

其中，M为设定的非叶子结点最多子树个数，N为关键字总数；

所以B-树的性能总是等价于二分查找（与M值无关），也就没有B树平衡的问题；

由于M/2的限制，在插入结点时，如果结点已满，需要将结点分裂为两个各占M/2的结点；

删除结点时，需将两个不足M/2的兄弟结点合并；

B+Tree索引

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构。B+Tree 与 B-Tree 的结构很像，但是也有几个自己的特性：

1.所有的非叶子节点只存储关键字信息。
   2.所有卫星数据(具体数据)都存在叶子结点中。
   3.所有的叶子结点中包含了全部元素的信息。
   4.所有叶子节点之间都有一个链指针。

为什么常用的关系型数据库采用B+Tree做索引而不是其它Tree（如B-Tree）

计算机数据存储基本原理回顾

InnoDB一棵B+树可以存放多少行数据？
  约2千万。

  计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。

  在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节;而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k;而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。

此外：
  我们还需要了解的一个知识点是操作系统从磁盘读取数据到内存是以磁盘块(Block)为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。
  这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。预读的长度一般为页(Page)的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页(在许多操作系统中，页的大小通常为 4K)。

InnoDB引擎的数据存储原理

innodb的所有数据文件（后缀为ibd的文件），他的大小始终都是16384（16k）的整数倍。默认是16k，当然也可以通过参数设置.

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。

为什么一棵B+树可以存2千万行数据？

  假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：
    根节点指针数*单个叶子节点记录行数。

  上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

  那么现在我们需要计算出非叶子节点能存放多少指针？

  其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

  根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。

  所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

B-Tree 和 B+Tree优劣对比

1. B-Tree 因为非叶子结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。而 B+Tree 所有的数据都在叶子结点，每次查找都得到叶子结点。所以在同样高度的 B-Tree 和 B+Tree 中，B-Tree 查找某个关键字的效率更高。
2. 由于 B+Tree 所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree 只需要找到该关键字然后沿着链表遍历就可以了，而 B-Tree 还需要遍历该关键字结点的根结点去搜索。
3. 由于 B-Tree 的每个结点(这里的结点可以理解为一个数据页)都存储主键+实际数据，而 B+Tree 非叶子结点只存储关键字信息，而每个页的大小是有限的，所以同一页能存储的 B-Tree 的数据会比 B+Tree 存储的更少。

这样同样总量的数据，B-Tree 的深度会更大，增大查询时的磁盘 I/O 次数，进而影响查询效率。
鉴于以上的比较，所以在常用的关系型数据库中，都是选择 B+Tree 的数据结构来存储数据!

如何得到InnoDB主键索引B+树的高度？

前面已经介绍了B+树的高度通常为1-3，那么如何从侧面证明这一结论呢？

  在InnoDB的表空间文件中，约定page number为3的代表主键索引的根页，而在根页偏移量为64的地方存放了该B+树的page level。
  如果page level为1，树高为2，page level为2，则树高为3。即B+树的高度=page level+1；下面我们将从实际环境中尝试找到这个page level。

  在实际操作之前，你可以通过InnoDB元数据表确认主键索引根页的page number为3，你也可以从《InnoDB存储引擎》这本书中得到确认。

可以看出数据库dbt3下的customer表、lineitem表主键索引根页的page number均为3，而其他的二级索引page number为4。

下面我们对数据库表空间文件做想相关的解析：

因为主键索引B+树的根页在整个表空间文件中的第3个页开始，所以可以算出它在文件中的偏移量：16384*3=49152（16384为页大小）。

另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节，保存了page level的值

因此我们想要的page level的值在整个文件中的偏移量为：16384*3+64=49152+64=49216，前2个字节中。

接下来我们用hexdump工具，查看表空间文件指定偏移量上的数据：

linetem表的page level为2，B+树高度为page level+1=3；

region表的page level为0，B+树高度为page level+1=1；

customer表的page level为2，B+树高度为page level+1=3；

这三张表的数据量如下：

lineitem表的数据行数为600多万，B+树高度为3，customer表数据行数只有15万，B+树高度也为3。可以看出尽管数据量差异较大，这两个表树的高度都是3

换句话说这两个表通过索引查询效率并没有太大差异，因为都只需要做3次IO。那么如果有一张表行数是一千万，那么他的B+树高度依旧是3，查询效率仍然不会相差太大。

region表只有5行数据，当然他的B+树高度为1。

总结

为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树？
  因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出）指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

B+Tree数据查找示例

先将数据记录按主键进行排序，分别存放在不同的页中（为了便于理解我们这里一个页中只存放3条记录，实际情况可以存放很多）

除了存放数据的页以外，还有存放键值+指针的页，如图中page number=3的页，该页存放键值和指向数据页的指针，这样的页由N个键值+指针组成。

当然它也是排好序的。这样的数据组织形式，我们称为索引组织表。

现在来看下，要查找一条数据，怎么查？

如：select * from user where id=5;

这里id是主键,我们通过这棵B+树来查找，首先找到根页，你怎么知道user表的根页在哪呢？

其实每张表的根页位置在表空间文件中是固定的，即page number=3的页（这点我们下文还会进一步证明）

找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中，那么进一步去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录：

5 zhao2 27

现在我们清楚了InnoDB中主键索引B+树是如何组织数据、查询数据的，我们总结一下：

1、InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

InnoDB 引擎数据存储示例

假设我们现在有一个用户表，我们往里面写数据：

在某个页内插入新行时，为了减少数据的移动，通常是插入到当前行的后面或者是已删除行留下来的空间，所以在某一个页内的数据并不是完全有序的。但是为了数据访问的顺序性，在每个记录中都有一个指向下一条记录的指针，以此构成了一条单向有序链表，不过在这里为了方便演示我是按顺序排列的!

由于数据还比较少，一个页就能容下，所以只有一个根结点，主键和数据也都是保存在根结点(左边的数字代表主键，右边名字、性别代表具体的数据)。

假设我们写入 10 条数据之后，Page1 满了，再写入新的数据会怎么存放呢?

有个叫“秦寿生”的朋友来了，但是 Page1 已经放不下数据了，这时候就需要进行页分裂，产生一个新的 Page。

在 InnoDB 中的流程是怎么样的呢?

产生新的 Page2，然后将 Page1 的内容复制到 Page2。
产生新的 Page3，“秦寿生”的数据放入 Page3。
原来的 Page1 依然作为根结点，但是变成了一个不存放数据只存放索引的页，并且有两个子结点 Page2、Page3。
这里有两个问题需要注意的是：

1. 为什么要复制 Page1 为 Page2 而不是创建一个新的页作为根结点，这样就少了一步复制的开销了?
     如果是重新创建根结点，那根结点存储的物理地址可能经常会变，不利于查找。
     并且在 InnoDB 中根结点是会预读到内存中的，所以结点的物理地址固定会比较好!
2. 原来 Page1 有 10 条数据，在插入第 11 条数据的时候进行裂变，根据前面对 B-Tree、B+Tree 特性的了解，那这至少是一棵 11 阶的树，裂变之后每个结点的元素至少为 11/2=5 个。那是不是应该页裂变之后主键 1-5 的数据还是在原来的页，主键 6-11 的数据会放到新的页，根结点存放主键 6?
     如果是这样的话，新的页空间利用率只有 50%，并且会导致更为频繁的页分裂。

所以 InnoDB 对这一点做了优化，新的数据放入新创建的页，不移动原有页面的任何记录。

随着数据的不断写入，这棵树也逐渐枝繁叶茂，如下图：

每次新增数据，都是将一个页写满，然后新创建一个页继续写，这里其实是有个隐含条件的，那就是主键自增!主键自增写入时新插入的数据不会影响到原有页，插入效率高!且页的利用率高!

但是如果主键是无序的或者随机的，那每次的插入可能会导致原有页频繁的分裂，影响插入效率!降低页的利用率!这也是为什么在 InnoDB 中建议设置主键自增的原因!

这棵树的非叶子结点上存的都是主键，那如果一个表没有主键会怎么样?在 InnoDB 中，如果一个表没有主键，那默认会找建了唯一索引的列，如果也没有，则会生成一个隐形的字段作为主键!

有数据插入那就有删除，如果这个用户表频繁的插入和删除，那会导致数据页产生碎片，页的空间利用率低，还会导致树变的“虚高”，降低查询效率!这可以通过索引重建来消除碎片提高查询效率!

InnoDB 与 MyISAM 引擎对比

MyISAM 主键索引的存储结构

• 主键索引树的叶子结点的数据区域没有存放实际的数据，存放的是数据记录的地址。
• 数据的存储不是按主键顺序存放的，是按写入的顺序存放。

也就是说 InnoDB 引擎数据在物理上是按主键顺序存放，而 MyISAM 引擎数据在物理上按插入的顺序存放。
并且 MyISAM 的叶子结点不存放数据，所以非聚集索引的存储结构与聚集索引类似，在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比 InnoDB 的搜索效率会更高呢!

索引的使用策略及优化

使用策略

什么时候要使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引；
• 表记录太少不要建立索引。
  只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引失效的情况：

• 在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。
• 在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。
• LIKE操作中，’%aaa%'不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。
• 在索引的列上使用表达式或者函数会使索引失效。
  例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。其它通配符同样，也就是说，在查询条件中使用正则表达式时，只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
• 在查询条件中使用不等于，包括<符号、>符号和！=会导致索引失效。
  特别的是如果对主键索引使用！=则不会使索引失效，如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。（经erwkjrfhjwkdb同学提醒，不等于，包括<符号、>符号和！，如果占总记录的比例很小的话，也不会失效）
• 在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
• 字符串不加单引号会导致索引失效。
  更准确的说是类型不一致会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=‘99999’。
• 在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。
• 如果排序的字段使用了索引，那么select的字段也要是索引字段，否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。

索引的优化：

1. 最左前缀
   索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。根据最左前缀原则，我们一般把排序分组频率最高的列放在最左边，以此类推。
2. 带索引的模糊查询优化
   在上面已经提到，使用LIKE进行模糊查询的时候，’%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。
3. 为检索的条件构建全文索引，然后使用
   SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);
4. 使用短索引
   对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

最左前缀原则示例：

联合索引遵从最左前缀原则，什么意思呢，就比如说一张学生表里面的联合索引如

key 'idx_age_name_sex' ('age','name','sex')

那么下面A,B,C,D,E,F哪个会走索引呢？

A:select * from student where age = 16 and name = '小张'  
B:select * from student where name = '小张' and sex = '男'  
C:select * from student where name = '小张' and sex = '男' and age = 18  
D:select * from student where age > 20 and name = '小张'  
E:select * from student where age != 15 and name = '小张'  
F:select * from student where age = 15 and name != '小张'

A遵从最左匹配原则，age是在最左边，所以A走索引；

B直接从name开始，没有遵从最左匹配原则，所以不走索引；

C虽然从name开始，但是有索引最左边的age，mysql内部会自动转成where age = ‘18’ and name = ‘小张’ and sex = ‘男’ 这种，所以还是遵从最左匹配原则；

D这个是因为age>20是范围，范围字段会结束索引对范围后面索引字段的使用，所以只有走了age这个索引；

E这个虽然遵循最左匹配原则，但是不走索引，因为!= 不走索引；

F这个只走age索引，不走name索引，原因如上；

索引在磁盘上的存储示例

聚集索引和非聚集索引存储的不相同，那么来说下都是怎么存储的？如下示例说明：
有一张学生表

create table `student` (
`id` int(11) not null auto_increment comment '主键id',
`name` varchar(50) not null default '' comment '学生姓名',
`age` int(11) not null default 0 comment '学生年龄',
primary key (`id`),
key `idx_age` (`age`),
key `idx_name` (`name`)
) ENGINE=InnoDB default charset=utf8 comment ='学生信息';

表中内容如下：

其中id 为主键索引，name和age为非聚集索引

• 聚集索引在磁盘中的存储

  聚集索引叶子结点存储是表里面的所有行数据；
  每个数据页在不同的磁盘上面；
  如果要查找id=5的数据，那么先把磁盘0读入内存，然后用二分法查找id=5的数在3和6之间，然后通过指针p1查找到磁盘2的地址，然后将磁盘2读入内存中，用二分查找方式查找到id=5的数据。

• 非聚集索引在磁盘中的存储

叶子结点存储的是聚集索引键，而不存储表里面所有的行数据，所以在查找的时候，只能查找到聚集索引键，再通过聚集索引去表里面查找到数据。

如果要查找到name = 小徐，首先将磁盘0加载到内存中，然后用二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过二分查找法得知小徐id=4；

然后在根据id=4将磁盘0加载到内存中，然后通过二分查找的方法查到在指针p1所指的地址上，然后通过指针p1所指的地址可知道在磁盘2上面，然后通过id=4查找出郑正行数据，就查找出name=小徐的数据了。

• 联合索引在磁盘中的存储
  例如创建一个two_key 表，并且id_fid是联合索引，联合的列是（id, fid）那么联合索引的内部是什么样的呢?
  从本质上来说，联合索引也是一颗B+树，不同的是联合索引的键值的数量和不是1，而是大于等于2。接着来讨论两个整数型列组成的联合索引，假设两个键值的名称为id， fid 如图 [多个键值的B+树]：
  
  从图中可以看到多个键值的B+树情况，键值都是排序的。通过叶子节点可以逻辑上顺序读取所有数据，就上面图中所示，即为(1，1)、(1、2)、(2、1)、(2、4)、(3、1)、(3、2)，数据是按照(id, fid)的顺序进行存放。

所以，对于查询 select * from table two_key where id = XXX and fid = XXX, 像这样的操作显然是可以使用（id, fid）这联合索引的。对于单个列id的查询 select * from two_key where id = XXX 也是可以使用这个联合索引，但是对于fid列的查询 select * from two_key where fid = XXX 则是不能使用这个B+树的联合索引，因为 叶子节点中的fid的值为：1、2、1、4、1、2 显然不是排序的，因此对于fid的查询是使用不到(id， fid )这个联合索引的。

使用联合索引的第二个好处是对已经对第二个键值进行了排序处理。 这里的情况是确定了第一个键，这种情况，对于第一个键相同的记录来说，查询的结果是第二个键是已经进行了排序。我们举一个栗子吧！ 例如：有些应用程序都需要查询某个用户的购物情况，并按照时间进行排序，最后取出最近三次的购买记录，这时使用联合索引就可以避免多一次排序操作，因为索引本身在叶子节点已经排序了。