高性能的索引实现——《深究MySQL》

傷城~ 2022-06-02 04:45 147阅读 0赞

本篇主要总结的使索引更高效的方式

# 1.独立的列 #

独立的列，指的是建了索引的列不能在sql查询语句中进行计算。不然索引会失效。

**错误示例**：  
select \* from user where user\_id +1 =11;  
select \* from user DATE(create\_date) > ‘2017-10-10’;如果create\_date建了索引，该索引会失效。

# 2.前缀索引和索引选择性 #

**2.1 前缀索引**

**定义**：数据列太长时，我们可以选取字段开始的几个字符来作为索引，这样的索引就叫前缀索引。

**优点**：可以节约索引空间，从而提高索引效率。

**缺点**：会降低索引的选择性。Mysql无法使用前缀索引做group by 和order by，也无法做覆盖扫描。

**创建前缀索引**：Alter table 表名 Add key（city(7)）。city是需要建前缀索引列，7是前缀索引的长度。

**前缀索引长度的选取**：select count(distinct left(city, 4))/count(*) as sel4, count(distinct left(city, 5))/count(*) as sel5 from 表名 。通过这种方式可得出不同长度的前缀索引得到的选择性，选择性越大越好。单在增加前缀索引长度的情况下，选择性变化不大时，就没必要增加前缀索引的长度了。

**※** 像Blob、Text或很长的varchar类型的列，Mysql会强制使用前缀索引，而不允许将整个列的数据长度做为索引长度。

**※** 如果我们想给类似电子邮箱这种数据做索引，可以通过将邮箱**反转后存储**，然后再建立前缀索引。

**2.2 索引的选择性**：不重复的索引值（也称基数）与数据表记录总数的**比值**。比值越大越好。唯一索引的选择为1，此时性能最好。

**计算某列索引选择性的方式**：Select count(distinct 列名 )/ count(\*) from 表名

# 3. 聚集索引（聚簇索引） #

1.  聚集索引不是单独的索引类型，而是InnoDB引擎存储数据的方式。  
    更多参考：[http://blog.csdn.net/superhosts/article/details/25611119][http_blog.csdn.net_superhosts_article_details_25611119]
2.  聚集索引的**节点页**只存储了索引列，**叶子页**存储了全部数据（包括主键、事务ID、用于事务和MVCC的回滚指针），可以说是InnoDB\*\*通过主键聚集数据\*\*。
3.  InnoDB中**二级索引**的叶子页存储的是主键值。通过二级索引查找数据时，是先找到对应主键，然后通过主键索引才找到对应数据行。所以二级索引会查找两次，而自适应哈希可以减少这样的重复工作。
4.  基于聚集索引的表，在插入新行或者更新主键导致需要移动行的时候，可能面临“**页分裂**”的问题。当数据要插入到某个已满的页中时，存储引擎会将该行分裂成两个页面来存储。页分裂既会导致表占用更多的存储空间，也会影响执行性能。
5.  当达到页的最大填充因子（默认15/16）时，下一条记录就会写入新的页中。
6.  我们常见的主键索引都是顺序的，但顺序索引有时也会有问题。在搞并发时，如果按照顺序插入，可能会造成明显的争用，导致间隙锁竞争。也有可能导致Auto\_increment锁机制。

# 4. 覆盖索引 #

1.  **定义**：一个包含所有要查询字段值的索引。即**通过查询索引就能查到所有值**。  
    例：select 列1， 列2 from 表名。现在给这两列建立一个**复合索**引fuhe\_index，那么我们就可以说fuhe\_index索引覆盖了要查询的两列。
2.  如果只查询主键，二级索引就能覆盖查询，因为二级索引的页节点存储的就是主键。
3.  Mysql中如果对索引列使用了LIKE操作，则索引会失效。
4.  **优点**：**减少回表**操作。
5.  **延迟关联**就是覆盖索引的一种应用。  
    我们知道limit会先查出所有满足条件的数据，然后截取几条。如果我们查询的字段太多，就会浪费时间和性能。要是我们只查询主键，相当于用了覆盖索引，这样的查询就快很多。于是我们就可以通过覆盖索引先查出主键这一个字段，再根据主键查询需要的数据。如：  
    select \* from pro inner join (select id from pro where pro2.c2=’M’ limit 10000, 10) as pro2 ON pro2.id = pro.id

[http_blog.csdn.net_superhosts_article_details_25611119]: http://blog.csdn.net/superhosts/article/details/25611119