004 Mysql 索引

1、索引作用

提供了类似于书目录的作用，目的是为了优化查询

2、索引的种类（算法）

B树索引 （保证查找公平和快捷）

Hash索引

R树

Full text

GIS（地理位置）

3、B树 基于不同的查找算法分类介绍

 

从下向上分为叶子节点----指节点-----根节点

每个

B-tree

B+Tree 在范围查询方面提供了更好的性能（><>=<= like）

B*tree

4、在功能上的分类

4.1 辅助索引（S）怎么构建B树结构的？

（1）、索引是基于表中、列（索引键）的值生产的B树结构

（2）、首先提取此列所有的值进行自动排序

（3）、将排好序的值均匀的分布到索引树的叶子节点中（16K）

（4）、然后生产此索引键值所对应的后端数据叶的指针

（5）、生成枝节点和根节点，根据数据量级和索引键长度生成合适的索引树高度

id  name  age   gender

select * from t1 where id=10;

问题：基于索引键做where 查询，对于id列是顺序IO，但是其他的列是随机IO

补充：

辅助索引如何使用B树

1、取出ID列的所有值自动排序；

2、申请索引叶子节点数据页（16k），将有序的值存储到叶子节点的数据页中

3、申请枝节点和根节点数据页，将下层节点的最少值存储进去。

聚集（聚合）索引如何使用B

1、构建的提前是根据主键或唯一键，一般键表的时候我们指定pk，并且建议是数据列。

2、

聚集索引和辅助索引构成区别：

聚集索引只能有一个，非空唯一，一般是主键

辅助索引，可以有多个，是配合聚集索引使用的

聚集索引叶子节点就是磁盘的数据行存储的数据页

MySQL 是根据聚集索引，组织存储数据，数据存储时就是按照聚集索引的顺序进行存储数据；

辅助索引只会提取索引键值进行自动排序生成B树结构。

 

聚集索引和辅助索引怎样使用的？！！！！！

select id , name from std where name=xxx

查询过程：

1、先查找辅助索引（name）列，找到辅助索引的叶子节点中存储着（name=xxx的id号）；

2、拿到name=xxx的 ID号，再到聚集索引中进行查询

3、按照name=xxx的id到聚集索引的叶子节点中读取数据；

 

存储时按照聚集索引进行数据排序；

辅助索引创建时会创建（辅助索引列与聚集索引列的对应关系）

辅助索引细分

1、普通的单列辅助索引

2、联合索引

      多个列为索引条件生成索引树，理论上设计的好可以减少大量的回表

     不同版本优化器还有差异。

3、唯一索引

六、索引的高度

一般情况下索引的高度控制在四层，一个表行数越多索引层数就会增大。一个表的行数超过千万级别就考虑分表。数据内容越大索引也会增大。

1、数据量级，解决方法：分表、分开、分布式

2、索引列值过长，解决方法：前缀索引

3、数据类型：

变长长度的字符串，使用了char。解决方案：变长字符串使用varchar

enum类型的使用enum（‘山东','河北','黑龙江','辽宁','山西’）

七、索引的基本管理

desc city;

show index from table_name;

show index from city;

alter table city add index idx_name(name);

alter table city drop index idx_name;

alter table city add idx_d_p(district,population);

show index from city;

alter table city add index idx_addr(addr(5));

在设计表时把需要的索引在见表时进行创建；

9、模拟数据库数据

创建100w测试数据

drop database if exists db1;

create database db1 charset utf8mb4 collate utf8mb4_bin;

use oldboy;

create table t_100w (id int,num int,k1 char(2),k2 char(4),dt timestamp);

delimiter//

create procedure rand_data(in num int)

begin

declare str char(62) default

'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890';

declare str2 char(2);

declare str4 char(4);

declare i int default 0;

while i < num do

set

str2=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),1));

set

str4=concat(substring(str,1+floor(rand()*61),1),substring(str,1+floor(rand()*61),2));

set i=i+1;

insert into t_100w values (i,floor(rand()*num),str2,str4,now());

end while;

end;

//

delimiter;

#插入100w 条数据

call rand_data(1000000);

commit;

 

数据清洗：

1、统计

select population，count（id） from city group by population having count（id） order by count(id);