Mysql高级(三) 索引概述

一、索引定义

索引（Index）是帮助Mysql高效获取数据等数据结构。

索引是排好序的快速查找数据结构，故影响sql执行中的查找和排序。

二、索引的优势和劣势

2.1 索引优势

索引大幅度提高了查询效率，降低了数据库的IO成本。降低了数据排序成本，降低了CPU的消耗。

2.2 索引劣势

因为索引是一个独立的表，里面存了主键与索引字段，并且指向实体表的记录，所以也是占空间的。并且虽然有了所以之后查询速度快，但是对相应数据更新（insert、update、delete）的速度变慢了，所以对于那些经常需要更新的数据表尽量不要加索引。

三、索引分类

3.1 单值索引

一个索引只包含单个列，一个表可以有多个单值索引

3.2 唯一索引

索引列的值必须唯一，单允许空值

3.3 复合索引

一个索引包含多个列

主键是一种特殊的唯一索引

3.4 全文索引

这是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值，全文索引更类似于搜索引擎做的事情，实际生产中我们一般不会使用MySQL来做类似搜索引擎的工作

3.5 聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

聚集索引（InnerDB）：
1、建表的时候，如果指定了主键，则主键就是聚簇索引。
2、建表的时候，如果没有指定主键，且含有唯一索引，则会选择一个唯一的非空索引作为聚簇索引。
3、如果即不含有主键，也不含有唯一索引，则隐式使用6个字节的rowId作为聚簇索引。

四、索引的基本操作

查看索引

show index from tblname;

直接创建索引

CREATE INDEX index_name ON table(column(length))

修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

创建表的时候同时创建索引


    CREATE TABLE `table` (

    `id` int(11) NOT NULL AUTO_INCREMENT ,

    `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,

    `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,

    `time` int(10) NULL DEFAULT NULL ,

    PRIMARY KEY (`id`),

    INDEX index_name (title(length))

    )

删除索引

DROP INDEX index_name ON table

五、创建索引建议

5.1 适合建索引

a、主键自动建立唯一索引
b、频繁作为查询条件的字段时候建立索引
c、查询中与其它表关联的字段，外键关系建立索引
d、where 里用不到的不建立索引
e、查询中排序的字段，建立索引将大大提高排序速度
f、查询中统计或分组的字段

5.2 不适合建索引

a、表记录太少
b、经常增删改的表，建立索引将使得更新变慢
c、数据重复，且分布平均的字段

六、Mysql索引结构

MySql索引使用的数据结构是B+树

不使用Hash存储的原因是

a、使用hash存储必须使用好的hash算法。
b、hash存储由于数据分布的不均衡，比较占用内存。
c、hash存储不能进行范围查询，范围查询多于等值查询。（关键点）

不使用二叉树、BST、AVL、红黑树的原因：

当插入的节点越来越多，会导致树的深度越来越深，导致查询变慢。

6.1 BTree索引

6.2 Hash索引

6.3 Full-text全文索引

6.4 R-Tree索引

七、关于索引的常见问题

B+树有多少层

3～4层足够

索引用int 还是varchar

索引字段存储空间越小越好

索引为什么要自增

会引起底层的数据分裂或合并，影响性能

最后编辑于：2020.09.30 09:28:57