数据库概述08索引

图形化客户端
SQLyog是一个图形化的管理MySQL数据库的工具，能够在任何地点管理你的数据库，可以通过网络来维护远端的MySQL数据库，适合DBA

Navicat for MySQL是一个功能强大的MySQL数据库管理和开发工具，适用于开发者

开启远程服务
默认MySQL没有开启远程登陆支持，只能在localhost上使用
首先在localhost上登录数据库
还可以使用授权的方式进行处理 grant
alter user ‘root’@‘localhost’ identified with mysql_native_password by ‘123456’
如果连接过程中报出加密插件验证失败，则可以修改口令所采用的加密方式。在MySQL8中引入了新特性caching_sha2_password。客户端一般支持的都有mysql_native_password这种加密方法

索引

索引是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作
mysql -uroot -p123456
use 要使用的数据库名; – 切换当前系统库
update user set host=‘%’ where user=‘root’;
按照物理存储方式：聚簇和非聚簇
MyISAM：frm元数据文件、myd数据、sdi索引数据，MyISAM都是非聚簇索引，数据和索引分别存放
InnoDB：ibd元数据文件、数据和索引，innodb存储引擎支持聚簇索引
按照数据结构：B 树、hash等
按照逻辑：主键索引、普通索引、唯一索引、空间索引（针对空间类型的数据）和全文索引

B-tree

B树是一种自平衡的多叉查找树，在一个B树种最多可以开m个叉（m>=2）称为m阶B树

• 定义任意非叶子节点最多有m个儿子
• 根节点可以有m-1个键值【节点中存储了具体的数据key-value】，根节点的儿子数为[2,m]
• 除去根节点以外的非叶子节点儿子数为[m/2,m]，非叶子节点的关键字个数为儿子数-1
• 所有的叶子节点位于同一层
• 利用k个关键字将节点拆分为k 1段，分别指向k 1个儿子，同时需要满足查找树的大小关系

B-Tree的特点：

• 关键字集合分布在整个树中
• 任何一个关键字出现且只能出现在一个节点中
• 搜索可能在非叶子节点就直接结束，其搜索性能等价于在关键字全集内进行一次二分查找O(logN)
• B树在插入删除数据时会破坏B树的性质，所以插入删除时需要对数据进行分裂、合并、转移等操作以保持B树性质

B 树

就是在B树的基础上添加了顺序访问指针

• 有n个子树的节点包含有n个关键字
• 所有的关键字全部存储在叶子节点上，且叶子节点根据关键字从小到大的顺序连接
• 非叶子节点可以看作索引部分，节点中仅仅包含有其子树中最大或者最小的关键字

B 树的查找过程类似B树，只是查找时，如果非叶子节点上的关键字等于给定值时，并不终止，而是继续沿着指针指到叶子节点位置。因此不管在B 树中是否查找成功，每次查找都时走了一条从根到叶子的全路径

B 树的特点：

所有关键字都存储在叶子节点上，且链表中的关键字恰好是有序的
不可能是非叶子节点命中，所有查询的执行时间稳定
非叶子节点相当于叶子节点的索引，叶子节点相当于存储数据的存储层
非常适合文件索引系统

引入B树的原因

红黑树也可以实现索引，但是文件系统和数据库系统普遍采用的是B树或者B 树

• 首先需要建立索引的数据规模比较大，所以造成索引的数据量也不会太小，不可能全部存储在内存中，索引经常需要以文件的形式存储在磁盘上，所以索引查找过程中就需要有磁盘IO的消耗。索引的组织结构需要尽量减少查找过程中磁盘的IO存取次数。
• 磁盘的读取并不是严格的按需读取，每次都会预读，这是依据局部性原理：当一个数据被用到时，其附近的数据也通常会被用到。磁盘的顺序读取效率很高，按照局部性原理，采用预读机制能够提高IO效率。预读的长度一般是页page的整数倍，页是计算机存储管理的逻辑块，页大小一般都是4k或者8k。
• 数据库中利用磁盘预读的原理，将一个节点的大小设置为一个页大小，每个节点只需要一次IO就可以完全载入，所以使用B树作为索引结构是非常高效的。使用红黑树则树的高度比B树要高的多，而B树一般只有3-4层高度

MySQL索引

索引实际上就是关键字和数据的映射关系，关键字是从数据中提取的用于标识、检索数据的特定内容

• 关键字相对于数据来说，关键字的数据量要小的多
• 关键字应该是有序的，二分查找可以快速确定位置
  MySQL中的索引类型可以分为普通索引、唯一索引、主键索引、全文索引和空间索引（针对空间类型的数据）
• 普通索引：对关键字没有限制
• 唯一索引：用于实现关键字不允许重复的约束
• 主键索引：要求主键唯一且不允许为空

基础命令

show create table 表名称;
desc 表名称;

创建索引

创建索引有2种方式：
创建表的同时建立对应索引，例如主键、唯一性约束
创建表之后建立索引

create table tb_users(
id bigint auto_increment, -- 实际会有报错
first_name varchar(16),
last_name varchar(16),
id_card varchar(18),
memo text,
primary key(id), -- 主键索引
key name1(first_name,last_name),
unique key(id_card),
fulltext key(memo)
);
create table tb_users(
id int auto_increment primary key, -- 建立了主键索引
username varchar(20) unique, -- 建立唯一性索引
memo text
);
alter table tb_users drop column username;
alter table tb_users add first_name varchar(16);
alter table tb_users add last_name varchar(16);

//一个列可以构成索引，索引也可以是由多个列构成
– 建立一个复合索引，并且命名为name1
alter table tb_users add key name1(first_name,last_name);
– 创建全文索引,具体存储采用倒排索引的方式，不支持中文分词。如果业务需要使用搜索引擎，建议使用ES
实现全文搜索

删除索引

可以根据索引名称执行删除操作，对应的是普通索引、唯一索引、全文索引
语法：alter table 表名称 drop key 索引名称
删除主键索引：alter table 表名 drop primary key，这里没有索引名称，因为一个表只能有一个主键，主键可以是多个列构成。需要注意的是：如果主键自增长，那么不能直接删除。因为auto_increment需要依赖于主键索引
如果需要删除则必须先取消自增长，然后删除主键索引
实际上在具体开发中通常不会删除主键，因为主键有自然主键和代理主键两种，建议的使用的是代理主键，代理主键和业务规则无关。

执行计划

当执行SQL语句时，首先会分析、优化，形成执行计划，最后按照执行计划执行操作，然会操作结果集。可以通过explain来分析sql执行前的执行计划。
查看查询操作的执行计划
alter table tb_users add fulltext(memo);
alter table tb_users drop key name1; – 删除名称为name1的复合索引
alter table tb_users drop key id_card; – 删除名称为id_card的唯一性索引
alter table tb_users drop key memo;
alter table tb_users modify id bigint;
alter table tb_users drop primary key;
create table tb_users(
id bigint primary key auto_increment,
username varchar(20) unique,
city varchar(10),
province varchar(10),
key name1 (province,city)
)
insert into tb_users values(null,‘zhangsan’,‘西安’,‘陕西’);
explain select * from tb_users where id<10;
id查询操作的唯一标识
select_type显示查询的类型
table显示本次查询相关的表
partitions查询匹配的分区
type显示访问表使用哪种类型，ALL全表查询、index使用索引树、range给定范围查询
possible_keys查询时可能会使用的索引
key使用的索引
key_len索引字段的长度
ref使用索引列等值查询时，和索引列进行等值匹配的对象信息
rows扫描出的行数
filtered按照表条件过滤出的行百分比
Extra执行形况的描述和说明
可以使用查看执行计划判断定义索引是否对特定查询起到优化查询效率的作用
这里表示没有任何索引生效，整表扫描查询数据

索引的使用场景

1、针对经常需要搜索的列和主键列
explain select * from tb_users where id>1

根据id查询记录，因为id列上创建了主键索引，因此sql语句执行的可选索引possible_keys只有主键索引，如果有多个不同索引，最终会选择一个较好的作为检索的依据
2、order by排序或者范围查询条件的列
执行order by排序操作时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排序。操作很影响性能。可以使用 alter table 表名称 add index(列名称) 建立索引，由于索引本身就是有序的，所以直接按照索引顺序逐条读取数据即可。如果还涉及分页操作时，只用获取索引表中某个
范围内的索引对应的数据，而不是获取所有数据
具体开发中做查询必做分页

• 逻辑分页就是将数据先全部加载到内存中，然后获取部分数据进行显示
• 物理分页就是查询时只获取所需要显式的数据，不显示的数据不加载。 select * from tb_users limit 起始行号，每页行数
  3、需要进行连接查询join
  对join语句匹配关系on所涉及的字段建立索引可以提供连接查询的执行效率

索引覆盖

如果要查询的字段都建立了索引，那么存储引擎会直接在索引表中进行查询，而不会访问原始数据，这个叫做索引覆盖。因此需要尽可能在select后写上必要查询的字段，以增加索引覆盖的几率

• 注意：不要在每个字段上建立索引，因为优先使用索引的优势在于索引的体积小。
• 针对一个表一般建议建立的索引数目不要超过6个，因为索引除了需要额外的存储空间外，还会对数据的增删改性能造成影响

索引失效

在满足所用使用的情况下，例如where、order by、join…on或者索引覆盖，索引也不一定被使用
1、字段要独立出现

select * from tb_users where id=20-1; -- 会使用主键索引
select * from tb_users where id 1=20; -- 不会使用主键索引,针对列使用了表达式计算或者函数

2、like查询

select * from tb_users where name like '%方%'; -- 由于使用%统配符开头，所以在name上
创建的索引无效，只能进行全表扫描，效率极低，实际开发中不建议使用；如果使用较为频繁建议引入第三方
的全文索引来实现
select * from tb_users where name like '方%'; -- name上创建的索引是有效

3、复合索引要求对左边的字段有效，最左前缀匹配原则
具体的实现原理是先按照first_name中提取的关键字进行排序，如果无法确定先后时再按照从last_name中提取的关键排序，索引复合索引生效的前提是左端的列参与的查询

alter table tb_users add index(first_name,last_name);

具体的实现原理是先按照first_name中提取的关键字进行排序，如果无法确定先后时再按照从last_name中提取的关键排序，索引复合索引生效的前提是左端的列参与的查询

select * from tb_users where first_name=’zhangsan‘; -- 可以使用索引
select * from tb_users where first_name='zhang' and last_name='san'; -- 索引生效
select * from tb_users where last_name="lisi"; -- 无法利用索引

从查询条件 first_name=? and last_name=? 来说，针对first_name和last_name创建复合索引的效率
比分别创建first_name索引和last_name索引的查询效率高
4、or条件查询
or两边都有索引时索引生效，一边有一边没有会导致整个sql语句的全表扫描
5、状态值不用索引
取值的可能性较少，例如sex或者订单状态之类的字段，这种字段即使创建索引，也往往利用不上，因为一个状态值可能匹配大量的记录，这种情况下mysql会认为索引比全表扫描的效率低，从而弃用索引。

如何创建索引

建立索引是以浪费空间为代价，换取高查询效率。建立索引会对修改操作的性能造成影响
1、建立基础索引：在使用where、order by、join字段上建立索引
2、优化组合索引：基于业务规则和业务逻辑

• 如果条件经常性的出现在一起，可以考虑将多个字段的索引升级为复合索引
• 如果添加个别字段的索引就可能出现索引覆盖，可以考虑为该字段建立索引
  3、如果查询时不常出现的索引应该删除掉
  4、建立索引后注意sql语句的使用
• %开头的模糊查询导致索引失效
• 索引列最好非空，null值不会出现在索引中。 sex boolean default 1
• 少用not in或者!=查询，not in可以使用not exists替代
• 不要针对列进行计算，针对列建议的索引无效
  5、使用explain查看执行计划，判断索引是否生效

前缀索引

一般希望索引表应该比具体的数据表要小。当建立索引时默认使用字段的全部内容建立索引，可以指定使用字段的前10个字符建立索引，而不是整个字段内容。语法： index(列名称(长度))
使用前缀索引的前提是：字段的前缀标识度比较高，例如口令比较适合创建前缀索引，因为各个密码几乎都不相同
使用前缀索引的难度就是如何定义前缀截取的长度
可以使用 select count(*)/count(distinct left(password,len)) 进行比较，通过调整len值的大小查看不同前缀的平均匹配度，接近1时就可以了

索引的具体实现

Innodb存储引擎采用的是索引组织表，在Innodb中表数据文件本身就是按照B 树组织的一个索引结构。MyISAM中主索引和辅助索引在结构上没有任何区别，只是主索引要求key值唯一的，而辅助索引的key值允许重复

Innodb存储引擎

在innodb中的数据文件本身就是按照主键聚集存放【聚集索引】，所以要求innodb中的数据表必须有主键，MyISAM可以没有。如果没有显式指定主键，mysql会自动选择一个可以标识的列作为主键，如果不存在可以标识的列，mysql自动为innodb表生成一个隐含字段充当主键，这个字段为6B

辅助索引首先检索辅助索引表获取对应的主键，然后再用主键到主索引表中检索对应的数据

在Innodb中索引不建议使用过长的字段作为主键，因为所有的辅助索引都要使用主索引，过长的主索引会导致辅助索引变大，建议使用整型数据充当主键，占用空间少，比较速度快。另外使用非单调的字段充当主键不是个好主意，因为innodb数据文件本身就是一个B 树，非单调的主键会造成插入新记录时数据文件为了维护B 树的特性而出现频繁的分裂调整，十分低效。最佳实践；可以考虑使用bigint auto_increment的代理主键

MyISAM存储引擎

MyISAM主索引使用的是 B 树作为索引结构，叶子节点data域中存储的是数据记录的地址

MyISAM辅助索引也是B 树，data域中保存数据记录的地址。

MyISAM索引的检索算法是首先按照B 树的搜索算法查找对应的key，如果key存在，则获取存储在data域中的地址值，最后根据地址值获取对应行的数据，这种叶子节点不存储具体数据的方式叫做非聚集索引

hash索引

在索引被载入内存中，使用hash结构存储数据
类似HashMap实现

这篇好文章是转载于：学新通技术网