简介
数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询、更新数据库表中数据。
平衡二叉树、B树、B+树、B*树
一般都在索引用的数据类型就是B+树，这里顺便记一下其他的数据结构。
平衡二叉树
平衡二叉树是基于二分法的策略提高数据的查找速度的二叉树的数据结构；平衡二叉树是采用二分法思维把数据按规则组装成一个树形结构的数据，用这个树形结构的数据减少无关数据的检索，大大的提升了数据检索的速度；
B树(B-tree)
B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），数据库索引技术里大量使用者B树和B+树的数据结构
B树的查询流程：
我要从上图中找到E字母，查找流程如下
- 获取根节点的关键字进行比较，当前根节点关键字为M，E<M（26个字母顺序），所以往找到指向左边的子节点（二分法规则，左小右大，左边放小于当前节点值的子节点、右边放大于当前节点值的子节点）；
- 拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；拿到关键字D和G，D<E<G 所以直接找到D和G中间的节点；
- 拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；拿到E和F，因为E=E 所以直接返回关键字和指针信息（如果树结构里面没有包含所要查找的节点则返回null）；
B+树
B+树是B树的一个升级版，在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点 中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；
B*树
在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；
索引创建,删除,查看
创建索引
CREATE INDEX index_name ON table_name (column_list)
或
ALTER TABLE table_name ADD INDEX index_name (column_list)
删除索引
ALTER TABLE table_name DROP INDEX index_name
查看索引
SHOW INDEX FROM table_name ;
主键索引创建
- 全表只能有一个主键
- 唯一
- 非空
- 表创建的时候至少要有一个主键索引，最好和业务无关。
- 建立表时创建
CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
- 建立表后增加
CREATE TABLE `test1` (
`id` int(4) NOT NULL,
`name` char(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;
--增加自增主键
alter table test1 change id id int(11) primary key not null auto_increment;
普通索引创建
加快查询速度，工作中优化数据库的关键。在合适的列上建立索引，让数据查询更高效。在where条件关键字后面的列建立索引才会加快查询速度.
-- 第一种方式
create index index_name on test(name);
-- 第二种方式
alter table test add index index_name(name);
唯一索引创建
内容唯一，但不是主键。
alter table test add unique index idx_name(name);
怎么判断某个列的值都是唯一的？
1、总行数查询
select count(*) from test;
2、基于某个列去重复之后还剩多少行
select count(distinct population) from test;
EXPLAIN命令的应用
EXPLAIN用于获取查询执行计划
mysql> EXPLAIN SELECT * FROM test \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
EXPLAIN输出列
| 列名 | 含义 |
|---|---|
| id | 该SELECT标识符 |
| select_type | 该SELECT类型 |
| table | 表名 |
| partitions | 匹配的分区 |
| type | 连接类型 |
| possible_keys | 可供选择的索引 |
| key | 实际选择的指数 |
| key_len | 所选键的长度 |
| ref | 列与索引进行比较 |
| rows | 估计要检查的行 |
| filtered | 按表条件过滤的行的百分比 |
| Extra | 附加信息 |
-
id
SELECT标识符。这是SELECT查询中的序号 -
select_type
| select_type 常见值 | 含义 |
|---|---|
| SIMPLE | 简单SELECT（不使用 UNION或子查询） |
| UNION | 使用了UNION查询 |
| SUBQUERY | 第一个SELECT是子查询 |
-
table
表名 -
partitions
查询将匹配记录的分区。该值适用NULL于非分区表 -
type（非常重要）
该type列 EXPLAIN输出介绍如何联接表。从最差类型到最佳类型的连接类型：ALL,index,range,ref,eq_ref,const,system,NULL，一般来说最少也要到达range级别ALL:全表扫描，性能最差index：Full Index Scan，index与ALL区别为index类型只遍历索引树range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。当MySQL使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。ref:使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行（等值查询）eq_ref:类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件const:该表最多只有一个匹配行，在查询开头读取。因为只有一行，所以优化器的其余部分可以将此行中列的值视为常量。 const表非常快，因为它们只读一次。system:该表只有一行（=系统表）。这是const连接类型的特例 。NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，比如利用主键查找一个不存在的值，直接返回NULL，这样就没有产生磁盘io性能非常好。侧面说明。。。数据库不用的时候，性能最好。
-
possible_keys
该possible_keys列指示MySQL可以从中选择查找此表中的行的索引 -
key
该key列指示MySQL实际决定使用的索引 -
key_len
该key_len列指示MySQL决定使用的索引的长度 -
ref
该ref列显示将哪些列或常量与列中指定的索引进行比较 -
rows
该rows列指示MySQL认为必须检查以执行查询的行数 -
filtered
该filtered列指示将按表条件过滤的表行的估计百分比 -
Extra
Using temporary
Using filesort --文件排序，索引已经生效还要做排序操作`
Using join buffer
如果出现以上附加信息：
请检查order by ,group by ,distinct,join 条件列上是否有索引
如果排序的列有索引但是在where子句中没有用到这个索引，那么实际上会产生Using filesort，也就是使用了文件排序
key_len 越小越好 可以用前缀
rows 越小越好 返回行越少越好
参考文档
扫一扫
1838
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
