索引原理和优化

索引原理和优化

索引原理

MySQL 的基本存储结构是页(记录都存在页里边)：

各个数据页可以组成一个双向链表

每个数据页中的记录又可以组成一个单向链表

• 每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位对应的位置，然后遍历该位置对应分组中的记录即可快速找到指定的记录

• 以其他列（非主键）作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

  select * from user where indexname = 'xxx'这样没有进行任何优化的 sql 语句，默认会这样做：

  1. 定位到记录所在的页：需要遍历双向链表，找到所在的页

  2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

使用索引后：

没有用索引我们是需要遍历双向链表来定位对应的页，现在通过“ 目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为 O(logn)）其实底层结构就是 B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

没有索引时：

MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高

索引优化

1. 选择唯一性索引

2. 为经常需要排序，分组和联合操作的字段建立索引

3. 为常作为查询条件的字段建立索引

4. 限制索引的数目：越多的索引，会使更新表变得很浪费时间

5. 最左前缀匹配原则，非常重要的原则。

   如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列可以被引用到。（由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY 子句也遵循此规则）

6. 尽量的扩展索引，不要新建索引