索引优化介绍
mysql索引的结构
-
BTree结构
-
-
BTREE特性，BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：（上图是一个三叉的多路平衡搜索树）
- 树中每个节点最多包括m个孩子
- 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子
- 若根节点不是叶子节点，则至少有两个孩子
- 所有的叶子节点都在同一层
- 每个非叶子结点由n个key与n+1个指针组成，其中ceil(m/2)-1<= n <= m-1。说明一个三叉的BTree，每个非叶子节点至少有1个key，2个指针。每个非叶子节点至多有2个key，3个指针。一个四叉的BTree，每个非叶子节点至少有1个key，2个指正；每个非叶子节点至多有3个key，4个指针。
- BTREE的新key插入，是向叶子节点做插入的，因为如果插入始终往根节点插入的话，会造成叶子节点的元素始终不变，而根节点如果需要分裂又会分裂成新的叶子节点
-
以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1,所以2<= n <= 4。当n>4时，中间节点分裂到父节点，两边节点分裂，插入 C N G A H E K Q M F W L T Z D P R X Y S数据为例
-
演变过程
-
插入前4个字母C N G A
-
插入H，根节点的key的数量n>4，此时中间元素G字母向上分裂到新的节点，作为根节点，G左右两侧数据分裂成新的叶子节点
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xHdOEeOY-1624600598569)(pic\MySQL\btree插入过程\Btree-插入过程2.png)]
-
插入E，K，Q不需要分裂
- [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-H9MAaHYM-1624600598571)(pic\MySQL\btree插入过程\Btree-插入过程3.png)]
-
插入M需要分裂，此时M恰好是中间元素，中间元素M向上分裂添加到根节点中G元素的后面，将右侧节点H K N Q分裂成左右两个节点
-
插入F W L T，不需要分裂
-
插入Z时，需要分裂，最右侧节点满了，此时的中间关键字T上移至父节点，注意通过上移中间关键字，树最终还是保持平衡，分裂结果的节点存在2个关键字
-
插入D时，导致最左边的叶子节点被分裂，D恰好是中间关键字，上移到父节点。
-
随后插入P R X Y不需要分裂
-
最后插入S时候，导致右侧第二个节点满了需要分裂，此时把中间关键字Q上移时，发现根节点也满了，需要分裂。根节点中D G Q M T，此时M需要向上分裂。
-
-
-
说明
- 一颗b树，浅蓝色的块称之为一个个磁盘块，每个磁盘块包含几个数据项（白色表示）和指针（黄色表示），例如磁盘1包含数据项有17和35，包含指针p1,p2,p3。
- p1指向小于17的磁盘块，p2指向大于17，小于35的磁盘块，p3指向大于35的磁盘块。
-
-
B+Tree
-
B+Tree为BTree的变种，B+Tree和BTree的区别：
- n叉B+Tree最多含有n个Key，而Btree最多还有n-1个key；
- B+Tree的叶子节点保存所有key的信息，按照key 的大小顺序排列。包含关键字（数据），指向数据的指针，没有指向下一个磁盘块的指针。
- 所有的非叶子节点都可以看做是key的索引部分。非叶子节点包含的是，关键字（数据），向下的指针（指向下一个磁盘块的指针），没有指向数据的指针
- B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，B+树的记录只放在叶子节点。
- 在B-Tree中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+Tree每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-Tree性能好像要比B+Tree好，而在实际应用中B+Tree性能要好些。因为B+Tree的非叶子节点不存放实际数据，这样每个节点可容纳的元素个数比B-Tree多，树的高度比B-Tree小，这样带来的好处是减少磁盘访问次数。尽管B+Tree找到一个记录“所需的比较次数”要比B-Tree多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际B+Tree的性能可能还会好些。而且B+Tree的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等）这也是很多数据库和系统使用B+Tree的原因。
-
为什么说B+Tree比B-Tree更适合实际应用中操作系统的文件索引和数据库索引？
- B+Tree的磁盘读写代价更低
- B+Tree的内部节点并没有指向关键字具体信息的指针。因此其内部节点相对BTree数更小，如果把所有同一内部节点的关键字存放在同一个盘块中，那么盘块所能容纳的关键字数量也越多。一次性读取到内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了
- B+Tree的查询效率更加稳定
- 由于非叶子节点不是最终指向文件内容的节点，而是叶子节点中关键字的索引，所以任何关键字的查找都必须走一条从根节点到叶子节点的路。所有关键字的路径长度相同，导致每个数据的查询效率相当。
- B+Tree的磁盘读写代价更低
-
-
Mysql B+Tree索引
- Mysql索引数据结构对经典B+Tree进行了优化，在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提供了区间访问的性能。（双向链表）
-
聚簇索引与非聚簇索引
-
聚簇索引并非一种单独的索引类型，而是一种数据存储方式。聚簇索引本身也是B+tree索引
-
非聚簇索引：将数据存储于索引分开的结构，索引结构的叶子节点指向数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key_buffer命中时，速度慢的原因
-
在innodb中，在聚簇索引之上创建的索引称之为辅助索引（二级索引），辅助索引访问数据总是需要二次查找（需要回表，需要根据查询到的主键id（聚簇索引的id）再查询聚簇索引获得数据），非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
-
何时使用聚簇索引和非聚簇索引
-
对于经常分组排序的列，可以使用聚簇索引，也可以使用非聚簇索引（两者都是有序的，都是b+tree结构）
-
对于查询返回某范围内的数据，建议使用聚簇索引，不建议使用非聚簇索引（非聚簇索引，叶子节点存储的是主键值，需要回表）
-
列中只有一个值或极少的不同值，不建议使用索引
-
小数目的不同值使用聚簇索引，大数目的不同值使用非聚簇索引（这里有疑问？需要思考）
-
频繁更新的列不建议使用聚簇索引，建议使用非聚簇索引（因为，频繁更新会造成聚簇索引的分裂，
-
动作 使用聚簇索引 使用非聚簇索引 经常被分组排序 √ √ 返回某范围内的数据 √ × 一个或极少不同值 × × 小数目的不同值 √ × 大数目的不同值 × √ 频繁更新的列 × √ 外键列 √ √ 主键列 √ √ 频繁修改的列 × √
-
-
术语’聚簇‘表示数据行和相邻的键值聚簇存储在一起。
-
-
聚簇索引的好处
- 按照聚簇索引排序顺序，查询显示一定范围数据的时候，由于数据都是紧密相连的。数据库不用从多个数据块中提取数据，所以节省了大量io操作。
-
聚簇索引的限制
- 对于mysql数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引（因为Myisam只存储索引值，不存储数据信息）。
- 由于数据物理存储排序方式只能有一种，所以每个mysql表只能有一个聚簇索引（也就是主键索引）。一般情况下就是该表的主键
- 为了充分利用聚簇索引的聚簇特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议使用无序的id，比如uuid这种。（选用uuid会导致聚簇索引失去意义）
-
一个误区：把主键自动设为聚簇索引
- 聚簇索引默认是主键：如果表中没有定义主键，innodb会选择一个唯一的非空索引代替。如果表中没有主键也没有唯一的非空索引，InnoDB会隐式地定义一个主键来作为聚簇索引。InnoDB只聚集在同一个页面中的记录，包含相邻键值的页面可能相距甚远。如果要设置非主键的聚簇索引，必须先删除主键，然后添加想要聚簇的聚簇索引，最后恢复主键设置即可。
- 此时其他索引只能被定义为非聚簇索引。这是个最大的误区。有的主键还是无意义的自动增量字段，这样的话Clustered index对效率的帮助就被完全浪费了。
- 聚簇索引的性能最好而且具有唯一性，有序性，所以非常珍贵，需要慎重设置。一般根据表最常用的SQL查询方式进行选择，某个字段作为聚簇索引，或组合聚簇索引，这个要看实际情况。
- 最终的目的就是在相同结果集的情况下，尽量减少磁盘IO
-
mysql索引分类
-
单值索引
-
即一个索引只包含单个列，一个表可以有多个单列索引
-
语法
-
-- 随表一起创建单值索引 create table customer( id int(10) unsigned AUTO_INCREMENT, customer_no varchar(200), customer_name varchar(200), PRIMARY KEY(id), KEY(customer_name) ); -- 单独创建单值索引 create index idx_customer_name on customer(customer_name); -- 删除单值索引 drop index idx_customer_name on customer;
-
-
-
唯一索引
-
索引列的值必须唯一，但允许为空值
-
语法
-
-- 随表一起创建索引 CREATE TABLE customer( id int(32) UNSIGNED AUTO_INCREMENT, customer_no varchar(200), customer_name varchar(200), Primary key(id), key(customer_name), unique(customer_no) ) -- 单独创建唯一索引 create unique index idx_customer_no on customer(customer_no); -- 删除唯一索引 drop index idx_customer_no on customer;
-
-
-
主键索引
-
设定为主键后数据库会自动创建索引，innodb为聚簇索引
-
语法
-
create table customer( id int(32) unsigned auto_increment, customer_no varchar(200), customer_name varchar(200), primary key(id), ) -- 单独创建主键索引 alter table customer add primary key customer(customer_no); -- 删除主键索引 alter table customer drop primary key; -- 修改主键索引：必须先删除掉原主键索引，再新建新的主键索引
-
-
-
复合索引
-
一个索引包括多个列
-
语法
-
-- 随表一起创建复合索引 create table customer( id int(32) unsigned auto_increment, customer_no varchar(200), customer_name varchar(200), primary key(id), key(customer_name), unique(customer_no), key(customer_name,customer_no) ) -- 单独创建复合索引 create index idx_no_name on customer(customer_no,customer_name); -- 删除索引 drop index idx_no_name on customer;
-
-
-
基本语法
- 创建
- CREATE [UNIQUE] INDEX indexName ON myTable(columnname(length));
- ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
- 删除
- DROP INDEX [indexname] ON mytable;
- 查看
- SHOW INDEX FROM table_name\G
- 使用ALTER命令
- ALTER TABLE tbl_name ADD PRIMARY_KEY(column_list);该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL；
- ALTER TABLE tbl_name ADD UNIQUE index_name(column_list);这条语句创建的索引值必须是唯一的（除了NULL外，NULL可能会出现多次）
- ALTER TABLE tbl_name ADD INDEX index_name(column_list);添加普通索引，索引值可出现多次。
- ALTER TABLE tbl_name ADD FULL TEXT index_name(column_list);该语句指定了索引为FULLTEXT,用于全文索引。
- 创建
-
索引的结构
- BTree索引
- Hash索引
- full-text索引
- R-Tree索引
需要建索引的情况
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 频繁更新的字段不适合创建索引
- 因为每次更新不单单是更新了记录还会更新索引
- where条件用到的字段可以创建索引
- 单值索引和复合索引的选择，尽量选择复合索引
- 查询中排序的字段可以建索引，排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段需要创建索引
不需要建索引的情况
- 表记录太少的情况
- 经常增删改的表
- 因为索引提高了查询的速度，同时会降低表更新的速度，如果对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存索引文件。
- 数据重复且分布平均的表字段不适合间索引，因为如果某个数据列包含许多重复的内容，对这个列建索引没有太大的意义，因为查询到的任然是大量数据，因此应该只为经常查询和经常排序的数据列建立索引
- 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值分布概率大约为50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度。
- 索引的选择性是指索引列中不同值的数目和表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，呢么这个索引的选择性就是1980/2000 = 0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
- where条件里用不到的字段不创建索引
- Mysql数据100万到300万左右，性能开始下降。
625
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
