MySQL进阶——索引 index

目录

索引 index

        1. 为什么要用索引

        2. 索引到底是什么

        3. 索引存放在哪

        4. 索引分类

        5. 索引使用的数据结构

        6. 联合索引中的最左前缀法则

        7. 索引常见问题

---

索引 index

        1. 为什么要用索引

        在没有索引的时候，数据的查询需要从前到后进行多次的IO读写，每次读取一段，性能较差在大量数据中进行查询时，查询性能会大大降低，此时需要添加索引来增加查询性能

        2. 索引到底是什么

        仿照查字典的方式，通过部首、偏旁的目录来查询字段，避免了全表查看，增加了查字典的效率。

        索引就相当于一个目录，记录了一个字段值对应的数据条在数据库海量数据中存放的位置，查询完这个目录后直接到相应位置取出数据即可，避免了大量的IO读写

        3. 索引存放在哪

        windows下存放在C:\programdata\mysql文件夹中，但是MySQL支持不同的存储引擎

        - InnoDB：索引和数据存放在同一个文件里 *.idb

        - MyISAM：索引和数据分开存储在 *.MYI *.MYD

        互联网公司在追求查询效率的情况下，一般使用InnoDB，因为它把索引和数据放在一个文件中，通过找到索引后就能直接在索引树上的叶子节点中获得完整的数据——聚集索引

        4. 索引分类

        - 主键索引：主键自带索引，通过主键来查询效率高

        - 普通索引：为非关键字段创建的索引，数据量大时创建时间较长

create index idx_name on table_name(name);

        - 唯一索引：列中的数据是唯一的，没有相同的

create unique index idx_unique_name on table_name(name);

        - 组合索引：一次性为表中的多个字段一起创建索引

create index idx_name_united on table_name(name1,name2);

        - 全文索引：进行查询时数据源可能来自于不同的字段或者不同的表，一般使用第三方搜索引擎中间件

        5. 索引使用的数据结构

        虽然索引查找避免了全表扫描的多次磁盘IO读写，但是其实索引也是在索引表中查找数据，数据量也是相同的，为什么索引就要快些呢——索引使用了特殊的数据结构B+树

        MySQL默认情况下一个节点的大小是16K，如果使用B树，海量数据下也要很深的树深度

        B+树叶子节点是从小到大、从左到右排列的，叶子节点间提供了指针，提高了区间访问性能，只有叶子节点存放数据，非叶子节点只存放键（类比操作系统中的多级分页）

        只用很浅的深度就能存放大量的索引键，叶子节点存放的索引值能够被快速找到

        而叶子节点间的指针提升了区间查找的性能，比如查询过了id=1的数据，id=2的数据就在它的下一个节点，通过指针连接，则可以飞速查询到id=2的数据

        下面的情况就能很好地应用叶子节点间的指针

        select * from a where id > 1

        可能有人会说哈希表的查询性能是最好的，但是考虑到数据库会有很多区间查找，而哈希表不支持区间查找的便利性，故使用B+树

        6. 联合索引中的最左前缀法则

        联合索引后在索引表中非叶子节点存储的数据是联合的字段值，在查询时

        where 语句从前往后应该按照联合索引的顺序进行，如果最左前缀没有在where中出现，则不会走索引，因为最左前缀是存放在最前端的，它不匹配则无法走索引！

        7. 索引常见问题

        - 为什么非主键索引的叶子节点存放的数据是主键值 

        由于主键自带索引，已经在索引文件中有了索引表，并且在叶子节点存放了数据，非主键索引的叶子节点存放主键值就可以通过查询到的主键值去主键自带的索引查询到要查询的数据，如果存放的不只是主键值，会有大量的数据冗余，浪费存储空间；另外，如果要通过非主键索引来修改一些数据条的值，则只需要在主键索引中修改一处即可，否则要在多处索引的叶子节点进行修改

        总结就是：1. 数据冗余，2. 维护麻烦

        - 为什么InnoDB表必须创建主键

        如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键，也会造成性能开销，为了性能考虑和设计初衷，就应该创建主键！

        - 为什么使用主键时推荐使用整型的自增主键

        主键会对应一棵主键索引树，树中数据的存放有大小顺序，整型数据的大小顺序好比较，直接比较数值即可，但是其他数据类型并不好比较（字符串类型需要重新编码等）；如果不用自增的方式，添加数据时使用不规律的数据，则构建主键索引树时会造成更多的自旋调整，浪费性能