徐州市新沂市建设局网站,优酷 做视频网站还能成功吗,网络推广网站电话,网站开发付款目录
1.B树的特点
索引结构
复盘
其他数据结构的对比
B树与B树总结
聚簇索引与非聚簇索引
辅助索引
2. 索引操作
主键索引
1. 创建主键索引
第一种方式
第二种方式
第三种方式
2. 查询索引
第一种方法
第二种方法
第三种方法
3. 删除索引
删除主键索引
删除…目录
1.B树的特点
索引结构
复盘
其他数据结构的对比
B树与B树总结
聚簇索引与非聚簇索引
辅助索引
2. 索引操作
主键索引
1. 创建主键索引
第一种方式
第二种方式
第三种方式
2. 查询索引
第一种方法
第二种方法
第三种方法
3. 删除索引
删除主键索引
删除其他索引
4. 特点
唯一索引
1. 创建
第一种方式
第二种方式
第三种方式
2. 删除唯一索引
3. 特点
普通索引
第一种方式
第二种方式
第三种方式
9. 普通索引的特点
复合索引
多列索引的创建
复合索引的工作原理
复合索引的应用场景
索引创建原则
索引总结
3.全文索引
创建
查询与全文索引
总结 1.B树的特点
叶子节点保存有数据非叶子节点不要数据
非叶子节点不存数据只存储目录项可以存储更多的目录项。目录页一个目录页可以管理更多的叶子Page使树更“矮胖”减少I/O次数提高效率。
叶子节点全部用链表级联起来
链表级联叶子节点用链表级联便于进行范围查找提高查询效率。
索引结构
InnoDB的索引结构MySQL InnoDB存储引擎使用B树作为索引结构。主键索引默认情况下如果没有指定主键MySQL会自动生成一个隐藏列作为主键。普通索引用户可以建立其他列的索引这些索引也是B树结构。
复盘
Page分类Page分为目录页和数据页。目录页只放各个下级Page的最小键值。查找过程自顶向下查找只需加载部分目录页到内存大大减少I/O次数。索引构建构建索引就是在MySQL内存中构建B树以指定列作为key值。
其他数据结构的对比
链表线性遍历效率低。二叉搜索树可能退化成线性结构效率不稳定。AVL 红黑树虽然平衡但树高较高I/O次数多。Hash适合点查询但在范围查找方面表现不佳。 B vs B B树 B树 B树非叶子节点不存数据数据都在叶子节点并且所有叶子节点用链式结构连接起来。而B树每一个节点内既包含目录项又包含数据所有B树除了叶子节点有数据路上节点也会包含数据。还有B树的叶子节点是没有被链式结构连接起来的。 那为什么mysql没有使用B树而用的B呢 第一mysql认为如果给非叶子节点增加数据也就意味着单个page里能够保存的目录项变少了意味着一个页目录所能管理的子目录子page就变少了一旦变少了在逻辑上这棵B树会比B树更高一些更瘦一些也就意味从根道叶子节点搜索的时候要经过更多的节点要经历更多次IO算法和IO带来的成本永远都是IO带来的成本更高的。 第二B树的叶子节点没有相连也就意味着想进行范围查找依旧要重新遍历这颗B树。而一旦重新遍历B树也就注定在遍历的时候需要每次查B树可能有些page并不在内存里又需要在进行IO同时每次查效率也很慢不像B树找到起始位置线性遍历一定拿到的是有效范围内所有数据。 B树与B树总结 B树每个节点内既包含目录项又包含数据树高较高I/O次数多。B树非叶子节点不存数据树更矮I/O次数少叶子节点用链表级联便于范围查找。
例演示一个Max.Degree3 的B树 数据结构演示链接
聚簇索引与非聚簇索引
非聚簇索引
MyISAM存储引擎索引和数据分离适合某些场景。MyISAM 引擎同样使用B树作为索引结果叶节点的data域存放的是数据记录的地址。下图为 MyISAM表的主索引 Col1 为主键。 聚簇索引
InnoDB存储引擎数据和索引放在一起提高查询效率。 下面我们见一下现象文件结构
InnoDB test1.frm表结构数据。test1.ibd主键索引和用户数据。
MyISAM分离
CREATE TABLE myisam_test (id INT PRIMARY KEY
) ENGINEMyISAM;
test2.frm表结构数据。test2.MYD数据记录。test2.MYI索引 数据指针。 辅助索引
InnoDB辅助索引的叶子节点只包含对应记录的主键值需要进行回表查询。MyISAM辅助索引和主键索引类似叶子节点包含数据记录的地址。
注意
所以一张表没有指明任何主键mysql默认会给表添加默认主键也会以B树结构呈现只不过我没有设立主键就只能线性遍历。如果我们指明主键默认我们的表会配上主键索引会以我们自己设置的主键为key值设立主键索引。如果我们指明主键索引未来还想给其他列设置索引我们可以手动添加。添加之后会在mysql内部重新构建B树以MyISAM为例会指向记录如果是InnoDB保存的是主键值方便我们快速索引。换句话说一个表可能会建立主键索引或者其他普通索引不管建立任何索引最终在mysql中一张表可能会有多颗B树。索引语法上分三类主键索引、唯一键索引、普通索引但其实宏观上就两类一个是主键索引指明就用主键的没有指明就用默认的。一个是普通索引包括唯一索引。 2. 索引操作
主键索引
1. 创建主键索引
第一种方式
在创建表时直接指定 primary key
create table test1(id int primary key, name varchar(30));
说明在字段名后指定 primary keyMySQL会根据该列构建主键索引。
第二种方式
在创建表的最后指定某列或某几列为主键索引
create table test2(id int, name varchar(30), primary key(id));
说明在表定义的最后指定某列或某几列为主键。
第三种方式
创建表以后再添加主键
create table test3(id int, name varchar(30));
alter table test3 add primary key(id);
说明先创建表再通过 alter table 添加主键。
2. 查询索引
第一种方法
使用 show keys from 表名
show keys from test1;
第二种方法
使用 show index from 表名
show index from test1;
第三种方法
使用 desc 表名
desc test1;
说明信息较为简略主要用于查看表结构。 3. 删除索引
删除主键索引
第一种方法
alter table 表名 drop primary key;
删除其他索引
第二种方法
alter table 表名 drop index 索引名;
第三种方法
drop index 素引名 on 表名;
4. 特点 一个表中最多有一个主键索引主键索引的效率高主键不可重复创建主键索引的列值不能为null且不能重复主键索引的列基本上是int 唯一索引
1. 创建
第一种方式
在表定义时指定 unique 唯一属性
create table test4(id int primary key, name varchar(30) unique);
第二种方式
在表定义的最后指定某列或某几列为 unique
create table test5(id int primary key, name varchar(30), unique(name));
第三种方式
创建表以后再添加唯一键
create table test6(id int primary key, name varchar(30));
alter table test6 add unique(name);
说明添加唯一键后表中会有两个B树一个是主键索引另一个是以指定列构建的唯一索引。
2. 删除唯一索引
使用 alter table 删除索引
alter table 表名 drop index 索引名;
说明唯一索引的删除方式与普通索引相同不能使用 drop unique。 为什么说这个呢我们发现主键很特殊构建是 add primary key删除是 drop primary key 这没问题。
但是删除唯一键不能用drop unique用的是drop index。未来你会发现我们删除普通索引用的也是drop index。说明unique索引本身也是一个普通索引。只不过指明它是uniqe是为了照顾表中的约束关系。其实在索引层面普通索引和唯一键索引都是一般索引。最特殊的就是主键索引。
3. 特点
一个表中可以有多个唯一索引唯一是指 无重复数据查询效率高如果在某一列建立唯一索引必须保证这列不能有重复数据如果一个唯一索引上指定 not null等价于主键索引 普通索引
创建
第一种方式
在表定义的最后指定某列为索引
create table test8(id int primary key,name varchar(20),email varchar(30),index(name)
);
第二种方式
创建完表以后指定某列为普通索引
create table test9(id int primary key,name varchar(20),email varchar(30)
);
alter table test9 add index(name);
说明普通索引和唯一索引在结构上没有区别都是B树。
第三种方式
创建一个索引名为 idx_name 的索引
create table test10(id int primary key,name varchar(20),email varchar(30)
);
create index idx_name on test10(name);
9. 普通索引的特点 一个表中可以有多个普通索引普通索引在实际开发中用得较多如果某列需要创建索引但该列有重复值应使用普通索引 复合索引
多列索引的创建
问题创建索引时只能在某一列创建吗如果表中有多列信息是否可以创建以多列为key值的索引结构
答案可以
示例
create table test11(id int primary key,name varchar(20),email varchar(30)
);
create index idx_name_email on test11(name, email);
解释
创建复合索引我们以 name 和 email 两列建立索引。索引数量创建复合索引后表中显示有三个索引但这并不意味着新增了两个B树。
复合索引的工作原理
单一B树复合索引实际上只构建了一颗B树而不是两颗。键值组合这颗B树的键值是由 name 和 email 两列值组合而成。搜索条件在搜索时这两列必须同时满足条件才能找到目标记录。
示例
索引名称复合索引的名称默认为 idx_name_email以多列中的第一列 name 作为索引名称。删除索引删除复合索引时只需一次操作即可删除整个复合索引。
alter table test11 drop index idx_name_email;
复合索引的应用场景
何时使用复合索引 避免回表InnoDB普通索引的叶子节点放的是表的主键的key值这意味着需要回表查询。但如果以 name 和 email 构建复合索引未来高频查询时可以直接通过 name 和 email 查找数据本身就在这颗复合索引的B树里无需回表。索引覆盖如果查询条件和返回值都在复合索引的列中可以直接从索引中获取数据无需回表这种情况称为索引覆盖。最左匹配原则MySQL在索引匹配时是从左侧开始向右匹配。例如可以按 name 或 name 和 email 查找但不能直接按 email 查找。
示例
查询姓名和QQ号
create table test12(id int primary key,name varchar(20),qq varchar(20)
);
create index idx_name_qq on test12(name, qq); 查询通过 name 查找 qq 号可以直接从复合索引中获取数据无需回表。
select qq from test12 where name 张三;
索引创建原则
1. 频繁作为查询条件的字段应该创建索引。
2. 唯一性太差的字段不适合单独创建索引即使频繁作为查询条件。
示例给性别打上索引但性别只有男和女构建出的B树并不优秀。
3. 更新非常频繁的字段不适合作创建索引。
示例考试信息更改太频繁索引创建出来是为了方便查询频繁修改不仅影响数据还会调整索引结构。
4. 不会出现在 where 子句中的字段不应创建索引。
示例某些字段从未在 where 子句中出现创建索引没有意义。
适合创建索引
高频读取低频修改唯一性高避免冗余避免在唯一性差或更新频繁的字段上创建索引。
索引总结
主键索引一个表中最多一个效率高值不能为null且不能重复。唯一索引一个表中可以有多个查询效率高值不能重复。普通索引一个表中可以有多个适用于有重复值的列。复合索引将多列值放在一起充当键值构建B树查找时必须满足多列值一致。 3.全文索引
B树索引
键值字段通常较短如 id、qq 等。用途用于快速找到一条记录或记录中的某一列或几列。
全文索引
键值字段通常较长如文章内容每行内容可能包含数千个字符。用途用于在长文本中查找特定的内容而不仅仅是找到一条记录。
创建
当对文章字段或有大量文字的字段进行检索时会使用到全文索引。MySQL提供全文索引机制但有以下要求
存储引擎必须是MyISAM。语言支持默认支持英文不支持中文。如果需要对中文进行全文检索可以使用sphinx的中文版(coreseek)。
示例
CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title, body)
) engineMyISAM;
插入数据
INSERT INTO articles (title, body) VALUES(MySQL Tutorial, DBMS stands for DataBase ...),(How To Use MySQL Well, After you went through a ...),(Optimizing MySQL, In this tutorial we will show ...),(1001 MySQL Tricks, 1. Never run mysqld as root. 2. ...),(MySQL vs. YourSQL, In the following database comparison ...),(MySQL Security, When configured properly, MySQL ...);
查询与全文索引
普通查询
select * from articles where body like %database%;
问题虽然查询出数据但没有使用到全文索引。检查可以使用 explain 工具查看查询是否使用了索引。 使用全文索引查询
select * from articles where match(title, body) against (database);
设置 match匹配条件。against匹配的关键字这里是 database。
检查
explain select * from articles where match(title, body) against (database);
解释
typefulltext 表示使用了全文索引。key表示使用了哪个索引。
总结
全文索引用于在长文本中查找特定内容特别适用于文章或大量文字的字段。创建需要使用 FULLTEXT 关键字并且表的存储引擎必须是MyISAM。查询使用 match 和 against 关键字进行全文索引查询可以显著提高查询效率。 文章转载自： http://www.morning.dwfzm.cn.gov.cn.dwfzm.cn http://www.morning.xnzmc.cn.gov.cn.xnzmc.cn http://www.morning.gslz.com.cn.gov.cn.gslz.com.cn http://www.morning.rxzcl.cn.gov.cn.rxzcl.cn http://www.morning.jnkng.cn.gov.cn.jnkng.cn http://www.morning.ylqpp.cn.gov.cn.ylqpp.cn http://www.morning.smspc.cn.gov.cn.smspc.cn http://www.morning.bzbq.cn.gov.cn.bzbq.cn http://www.morning.gfjgq.cn.gov.cn.gfjgq.cn http://www.morning.gbybx.cn.gov.cn.gbybx.cn http://www.morning.nrqnj.cn.gov.cn.nrqnj.cn http://www.morning.kbgzj.cn.gov.cn.kbgzj.cn http://www.morning.dnzyx.cn.gov.cn.dnzyx.cn http://www.morning.kczkq.cn.gov.cn.kczkq.cn http://www.morning.21r000.cn.gov.cn.21r000.cn http://www.morning.kfcz.cn.gov.cn.kfcz.cn http://www.morning.tkcct.cn.gov.cn.tkcct.cn http://www.morning.cdygl.com.gov.cn.cdygl.com http://www.morning.jwwfk.cn.gov.cn.jwwfk.cn http://www.morning.mqgqf.cn.gov.cn.mqgqf.cn http://www.morning.rbsmm.cn.gov.cn.rbsmm.cn http://www.morning.tjkth.cn.gov.cn.tjkth.cn http://www.morning.rkmsm.cn.gov.cn.rkmsm.cn http://www.morning.wnhsw.cn.gov.cn.wnhsw.cn http://www.morning.mrbmc.cn.gov.cn.mrbmc.cn http://www.morning.bwgrd.cn.gov.cn.bwgrd.cn http://www.morning.tymwx.cn.gov.cn.tymwx.cn http://www.morning.lekbiao.com.gov.cn.lekbiao.com http://www.morning.brscd.cn.gov.cn.brscd.cn http://www.morning.dysgr.cn.gov.cn.dysgr.cn http://www.morning.kwblwbl.cn.gov.cn.kwblwbl.cn http://www.morning.wnpps.cn.gov.cn.wnpps.cn http://www.morning.nqmwk.cn.gov.cn.nqmwk.cn http://www.morning.bszmy.cn.gov.cn.bszmy.cn http://www.morning.hmbtb.cn.gov.cn.hmbtb.cn http://www.morning.hcqd.cn.gov.cn.hcqd.cn http://www.morning.nbmyg.cn.gov.cn.nbmyg.cn http://www.morning.mxgpp.cn.gov.cn.mxgpp.cn http://www.morning.bhbxd.cn.gov.cn.bhbxd.cn http://www.morning.drqrl.cn.gov.cn.drqrl.cn http://www.morning.jxpwr.cn.gov.cn.jxpwr.cn http://www.morning.wypyl.cn.gov.cn.wypyl.cn http://www.morning.xywfz.cn.gov.cn.xywfz.cn http://www.morning.jbztm.cn.gov.cn.jbztm.cn http://www.morning.reababy.com.gov.cn.reababy.com http://www.morning.hrqfl.cn.gov.cn.hrqfl.cn http://www.morning.bqwnp.cn.gov.cn.bqwnp.cn http://www.morning.bnkcl.cn.gov.cn.bnkcl.cn http://www.morning.tstwx.cn.gov.cn.tstwx.cn http://www.morning.xwgbr.cn.gov.cn.xwgbr.cn http://www.morning.ffydh.cn.gov.cn.ffydh.cn http://www.morning.ptwzy.cn.gov.cn.ptwzy.cn http://www.morning.wzjhl.cn.gov.cn.wzjhl.cn http://www.morning.hqllx.cn.gov.cn.hqllx.cn http://www.morning.nrfqd.cn.gov.cn.nrfqd.cn http://www.morning.xkpjl.cn.gov.cn.xkpjl.cn http://www.morning.kchwr.cn.gov.cn.kchwr.cn http://www.morning.drcnn.cn.gov.cn.drcnn.cn http://www.morning.bgpch.cn.gov.cn.bgpch.cn http://www.morning.nlnmy.cn.gov.cn.nlnmy.cn http://www.morning.dwrbn.cn.gov.cn.dwrbn.cn http://www.morning.jrlgz.cn.gov.cn.jrlgz.cn http://www.morning.bzfwn.cn.gov.cn.bzfwn.cn http://www.morning.yhjlg.cn.gov.cn.yhjlg.cn http://www.morning.hgbzc.cn.gov.cn.hgbzc.cn http://www.morning.srgsb.cn.gov.cn.srgsb.cn http://www.morning.yrmgh.cn.gov.cn.yrmgh.cn http://www.morning.clyhq.cn.gov.cn.clyhq.cn http://www.morning.xqbbc.cn.gov.cn.xqbbc.cn http://www.morning.yqsq.cn.gov.cn.yqsq.cn http://www.morning.plgbh.cn.gov.cn.plgbh.cn http://www.morning.tmbfz.cn.gov.cn.tmbfz.cn http://www.morning.jmlgk.cn.gov.cn.jmlgk.cn http://www.morning.jwncx.cn.gov.cn.jwncx.cn http://www.morning.ypmqy.cn.gov.cn.ypmqy.cn http://www.morning.tymnr.cn.gov.cn.tymnr.cn http://www.morning.tpqrc.cn.gov.cn.tpqrc.cn http://www.morning.fjgwg.cn.gov.cn.fjgwg.cn http://www.morning.kngqd.cn.gov.cn.kngqd.cn http://www.morning.drnfc.cn.gov.cn.drnfc.cn
