mysql优化——索引优化

高性能索引策略对查询速度是至关重要的、

索引类型

B-tree索引

  btree索引,都用的平衡树,但具体的实现上,各引擎稍有不同,B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。

  Myisam,innodb中,默认用B-tree索引，myisam的索引方式也称为非聚集,innodb的索引方式成为聚集索引

hash索引

所谓Hash索引，是通过哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，但是i它存在着好多缺点：

1、因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询

1、每次都要全表扫描

2、由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

3、不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

4、当哈希值大量重复且数据量非常大时，其检索效率并没有Btree索引高的

索引覆盖

索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.

理想的索引

1:查询频繁 2:区分度高  3:长度小  4:尽量能覆盖常用查询字段.

如何创建出理想的索引？

1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).

针对列中的值,从左往右截取部分,来建索引

① 截的越短, 重复度越高,区分度越小,索引效果越不好

② 截的越长, 重复度越低,区分度越高,索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.

 所以,我们要在区‘分度+长度’两者上,取得一个平衡.常常需要截取不同长度,并测试其区分度，最终取得最佳值

 测试sql案列user表中的address取索引长度为6是的区分度：select count(distinct left(address,6))/count(*) from user; 

2:多列索引

  多列索引的考虑因素，列的查询频率比较高的 , 列的区分度,例如：如果经常通过firstname、lastname和age这三个条件检索数据，如果为firstname、lastname和age这三个列创建一个多列索引，MySQL只需一次检索就能够找出正确的结果！下面是创建这个多列索引的SQL命令：
ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age)； 
由于索引文件以B-树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录！
那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列MySQL数据库索引一样呢？答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的MySQL数据库索引，MySQL会试图选择一个限制最严格的索引。
但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

3：索引与排序，出来的数据本身就是有序的! 利用索引来排序.

4：重复索引: 是指 在同1个列(如age),或者 顺序相同的几个列(age,school), 建立了多个索引,称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 应该去掉.

5：对于左前缀不易区分的列 ,建立索引的技巧

如 url列

http://www.baidu.com

http://www.zixue.it

列的前11个字符都是一样的,不易区分,可以用如下2个办法来解决

①: 把列内容倒过来存储,并建立索引

Moc.udiab.www//:ptth

Ti.euxiz.www//://ptth

这样左前缀区分度大,

②: 伪hash索引效果

同时存 url_hash列，通过url_hash去索引