mysql 索引 及 优化

mysql中的索引就是用来加速表的查询 更新速度，mysql的每个表都对应三个文件 一个用来放数据 一个用来放表结构，还有一个就是用来放索引的，看来索引的作用很大，索引是建在列上的，我们平时可能不会注意到，因为数据量的关系不接触到百万级的数据，数据库的操作我们是感觉不到他的速度的，还有平时用的主键 ，where查询看似都像一种索引，但真正的索引是什么，他建在那里，在那里起作用那？像select * from where name="ceshi1" 就where后面跟的列名我做了测试4万条记录，没有建索引的时候用来0.035s 建立索引create index indexname on php (name)后用来0.001s,效果明显啊，索引是一个条件，索引的分类有b-tree索引  hash索引 fulltext全文索引  r-tree索引

b-tree索引是最常用的索引：所有存储引擎都支持的，他的物理文件大多是以平衡二叉树的形式存储的，数据都存放在left node上面，到任何一个节点的路径长度都是相同的，在innodb存储引擎中存在着两种索引，一种是主键形式的索引，另一种就是b-tree索引了，不同的是主键索引在left nodes中存放了实际数据包括主键字段和其他字段整个数据以主键进行排列，而b-tree索引与其他的一样，多的就是还存放了innodb的主键值，所以在innodb中用主键访问的效率是最高的，在innodb中有主键就按主键索引，有一般的就按一般的索引没有就有个默认的索引，默认的索引速度也是相当快的，比一般的索引都要快，

HASh 索引

只有memory支持此种索引，他的实现是将键值进行hash算法存入到hash表中，在每次检索的时候，都会将检索条件进行相同的算法，在和hash表中的值进行对比得到相应的信息，hash索引的效率非常高可以一次检索到位，但是有很多弊端，他不能使用范围条件进行查询，像 > < between != like 'pattern' (pattern不可以用通配符开始 可以结束),只能使用 = in <=>来进行查询，无法被利用来避免数据的排序操作，在组合索引（）中不能使用前面的几个索引进行查询，

FULL-TEXT全文索引

目前仅在myisam引擎中支持，只有char varchar text 这三种数据类型支持，主要用来替代效率低的like'%...%'操作的，在存储内容中索引被分为两部分，一部分是分割前的索引字符串数据集合，另一部分是分割后的词的索引信息，

R-TREE 索引

主要是用来解决空间数据检索的问题，只有一种数据类型geometry支持这种索引，

索引的制定：如何选择索引?

知道了都有什么索引，大概用在那些方面后，我们可以发现我们主要用的就是b-tree索引，我们都知道索引可以提高数据的查询效率，还有就是降低数据的排序成本，因为索引字段在使用索引的时候就把键值排序存放，当时有该字段排序的时候就不同操作而是直接使用排序好的结果，快吧，但不合理的索引设计也起不到任何作用反而为降低速度，应为索引本身也要占用一定的资源。那该如何选择索引那，具体情况很复杂，没有统一的标准，但可以遵循下面几条：

较频繁的作为查询条件的字段应该创建索引，

唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件，像性别 角色什么的，或是返回全表的15%以下的数据的

跟新非常频繁的字段不适合创建索引，因为更新操作还要更新索引的键值，

不会出现在where子句中的字段不适合建索引，

组合索引还是单键索引:

通常在where后面的字段并不是单一字段，经常会有多个字段在一起作为查询条件的，这就要考虑是只为过滤性较好的字段建立索引还是建立组合索引，还有就是我们可能会想到创建多个单键索引来替代组合索引但这种想法往往事与愿违的，为什么那，因为在使用的时候他只好使用其中一个索引，索引应该建立组合索引尤其是在并发量较大的应用中，但不是把所有的查询字段都建到一个组合索引中，应该让一个组合索引尽量为多个query语句使用，尽量减少同一个表上的索引数量，

不合理的索引是设计

在有很多重复的列上建立索引，数据在物理上随机存放在数据页上，在范围查找时，必须执行一次表扫描才能找到这一范围，这样会频繁的返回已经查找过的表，

组合索引前导的问题，例如 a, b, c, d 有四个字段组成的组合索引，但在query的时候，排在首位的子句不是a 则索引将其不到作用，后面在应用c的时候还有索引覆盖他的速度就比较快了，如果把前导选择好可以提高索引效率，后面的覆盖索引也可以提高索引的效率，

一般来说有大量重复值且经常有范围查询的(between > < >=  order by, group by可以考虑建立组合索引，组合索引尽量是关键查询形成的索引覆盖，其前导列一定是使用最频繁的列，

不可优化的where子句

如果在where子句中出现 substirng(name ,1,4)/nnu/20<1000 /="***"

的时候速度为变的很慢，因为where子句中对列的任何操作结果都在sql运行时逐列的计算得到，因此他不的不进行全表搜索，而没有使用该列上面的索引，可以使用下面代替 like '**%' <1000*30 /= 

还有就是在where条件中使用in  in ('0','1')，语法分析器会将in转换为 0  or 1 来执行，我们期望他会根据每个or子句分别查找，在将结果相加，这样可以利用前面建立的索引，但实际上他却采用了or的策略，及先取出满足每个or子句的行，存入数据库的工作表中，在建立唯一索引以去掉重复的行，最后从这个临时表中计算结果，因此实际过程中他没有用到建立在列上的索引，可以利用存储过程，或是拆开来处理这中事情，

create proc count as 
declare a int b int c int d char(10)
begin 
select a = count(*) from tablename id = '0'
select b = count(*) from talbename id = '1'
end
select c = a + b
select d = convert (char(10),c)
prind d