mysql索引

学习：

 

B-Tree：B即Balanced，平衡， 大量数据查找时，由树的高度决定

 

B+Tree：

增加了顺序访问指针，

所有关键字都在叶子节点中出现，非叶子节点作为叶子节点的索引，B+Tree总是到叶子节点才命中

 

B*Tree：非叶子节点也有链表

 

红黑树：有序的平衡二叉树

 

 

 

1、索引

索引是存储引擎用于快速找到记录的一种数据结构

当数据量大时， 索引对性能影响更大

 

2、索引像一本书的目录部分

select first_name from actor where actor_id=5;

a、先在目录中查找actor_id为5的物理地址，

b、从数据文件中， 找出物理地址对应的行

 

3、索引的类型

分为单列索引、多列的复合索引（最左前缀原则）

B-Tree

B+Tree:每个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历

 

InnoDB使用B+Tree

 

MyISAM使用前缀压缩技术使用得索引更小， InnoDB按照原数据格式进行存储

MyISAM索引通过数据的物理位置引用被索引的行，InnoDB根据主键被索引的行

 

B-Tree所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同

非叶子节点指定其子节点，叶子节点指针指向被索引的数据，

 

树的深度与表的大小有关

 

B-Tree对索引是顺序存储的， 所以适合查找范围数据， 排序操作(order by)

 

4、B-Tree索引查询类型

全值匹配：和索引中的所有列都匹配

匹配最左前缀：如查找索引的第一列

匹配列前缀：匹配某一列的前几个字符

匹配范围值：某一列的范围值

精确匹配某一列并范围匹配另一列：

只访问索引的查询：只需访问索引，不需要访问数据行

 

B-Tree索引的限制：

如果不是按索引最左列开始查找，则无法使用索引

不能跳过索引中的列, InnoDB没有限制(可以跳过某一列)

如果查询中有某个列的范围查询，则右边所有列都无法使用索引优化查找，InnoDB没有限制

 

5、索引策略

a、独立索引列：

索引列不能使用表达式， 也不能使用函数

 

b、前缀索引和索引选择性：

如果索引列字符很长，可以索引列的开始部分字符，节省索引空间，提高索引效率， 这样会降低索引的选择性

 

索引的选择性：不重复的索引值和数据表的记录总数的比值。索引的选择性越高，则查询效率越高，因为选择性高的索引在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的选择性，性能也是最好的。

 

c、多列索引

多个索引做相交操作(AND操作)时， 建立多个列的复合索引，而不是多个独立的单列索引

多个索引做联合操作(通常多个OR条件)，

 

d、选择合适的索引列顺序

如果不用考虑排序和分组时， 将选择性最高的列放在前面通常是最好的

 

e、聚簇索引

不是一种单独的索引类型，而是一种数据存储方式，具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上有同一个结构中保存了B-Tree索引和数据行

 

f、覆盖索引

使用索引直接获取列的数据，这样就不用读取数据行

explain的Exctra列可以看到：Using index

 

g、使用索引扫描来做排序

 

6、查询性能优化

查询性能低下的最基本原因：访问的数据太多

大部分性能低下的查询可以通过减少访问的的方式进行优化

 

7、分析步骤

a、确认应用程序是否在检索大量不需要的数据， 太多的行， 太多的列

   查询不需要的记录

   多表关联时， 返回全部列

   重复查询相同的数据：可以使用缓存

 

b、确认Mysql服务器层是否在分析大量超过需要的数据行

 

8、查询开销的3个指标， 这3个指标都会记录到慢日志中

   响应时间

   扫描的行数

   返回的行数

 

9、重构查询

   一个复杂查询还是多个简单查询

   切分查询：将大的数据行， 分成多次查询

   分解关联查询

10、查询执行的基础

    查询缓存

    解析器-解析树

    查询优化器

    查询执行计划

    舍近求远执行引擎

11、优化特定类型的查询

     count(1), count(某一列名)， count(*)是一样的， 都是统计所有结果行：使用统计表， 缓存