MySQL高级【一】索引和视图

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL高级【一】索引和视图，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9942字，纯文字阅读大概需要15分钟。

内容图文

索引

概述

索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

没有建立索引时，要进行全表扫描，效率很慢，为了加快查找，可以维护一个二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找快速获取到相应数据。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。索引是数据库中用来提高性能的最常用的工具。

优劣势

优势

类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

结构

索引是在 MySQ L的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。MySQL 目前提供了以下4种索引：

BTREE 索引：最常见的索引类型，大部分索引都支持 B 树索引。
HASH 索引：只有 Memory 引擎支持，使用场景简单。
R-tree 索引（空间索引）：空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
Full-text （全文索引）：全文索引也是 MyISAM 的一个特殊索引类型，主要用于全文索引，InnoDB 从Mysql5.6 版本开始支持全文索引。

MyISAM、InnoDB、Memory 三种存储引擎对各种索引类型的支持：

索引	InnoDB引擎	MyISAM引擎	Memory引擎
BTREE索引	支持	支持	支持
HASH 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6 版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为索引。

BTREE 结构

BTree 又叫多路平衡搜索树，一颗 m 叉的 BTree 特性如下：
- 树中每个节点最多包含 m 个孩子。
- 除根节点与叶子节点外，每个节点至少有 [ceil(m/2)] 个孩子。
- 若根节点不是叶子节点，则至少有两个孩子。
- 所有的叶子节点都在同一层。
- 每个非叶子节点由n个key与n+1个指针组成，其中 [ceil(m/2)-1] <= n <= m-1
  
  以 5 叉 BTree 为例，key 的数量：公式推导 [ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当 n>4 时，中间节点分裂到父节点，两边节点分裂。
BTREE 树和二叉树相比，查询数据的效率更高，因为对于相同的数据量来说，BTREE 的层级结构比二叉树小，因此搜索速度快。
B+TREE 结构

B+Tree 为 BTree 的变种，B+Tree 与 BTree 的区别为：
- n 叉 B+Tree 最多含有n个key，而 BTree 最多含有 n-1 个 key。
- B+Tree 的叶子节点保存所有的key信息，依 key 大小顺序排列。
- 所有的非叶子节点都可以看作是 key 的索引部分。
- B+Tree 只有叶子节点保存 key 信息，查询任何 key 都要从 root 走到叶子。所以 B+Tree 的查询效率更加稳定。
MySQL中的 B+Tree

MySql索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。

语法

1、创建索引

索引在创建表时可以同时创建，也可以随时增加新的索引。

在 MySQL 中如果当前字段为主键，那么会自动创建一个主键索引。

创建表时创建索引（CREATE）

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)

index_col_name : column_name[(length)][ASC | DESC]

例：为 city 表中的 city_name 字段创建索引
create index idx_city_name on city(city_name);

创建复合索引:

创建复合索引:
	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

随时增加新的索引（ALTER 命令）

alter  table  tb_name  add  primary  key(column_list); 
	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
alter  table  tb_name  add  unique index_name(column_list);	
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
alter  table  tb_name  add  index index_name(column_list);
	添加普通索引， 索引值可以出现多次。
	
alter  table  tb_name  add  fulltext  index_name(column_list);	
	该语句指定了索引为FULLTEXT， 用于全文索引

2、查看索引

SHOW INDEX FROM tbl_name;

3、删除索引

DROP INDEX index_name ON tbl_name;

设计原则

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

对查询频次较高，且数据量比较大的表建立索引。
索引字段的选择，最佳候选列应当从 where 子句的条件中提取，如果 where 子句中的组合比较多，那么应当挑选最常用、过滤效果最好的列的组合。
使用唯一索引，区分度越高，使用索引的效率越高。
索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等 DML 操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML 操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL 也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的 I/O 效率，也可以提升总体的访问效率。假如构成索引的字段总长度比较短，那么在给定大小的存储块内可以存储更多的索引值，相应的可以有效的提升 MySQL 访问索引的 I/O 效率。
利用最左前缀，N 个列组合而成的组合索引，那么相当于是创建了 N 个索引，如果查询时 where 子句中使用了组成该索引的前几个字段，那么这条查询 SQL 可以利用组合索引来提升查询效率。

索引的使用

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的 MySQL 的性能优化问
题。

1、避免索引失效

全值匹配，对索引中所有列都指定具体值。该情况下，索引生效，执行效率高。
最左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左列开始，并且不跳过索引中的列。

注意：如果条件中每一列都有索引，那么不管最左列在哪，都会走索引；如果符合最左法则，但是出现跳跃某一列，那么只有最左列索引生效。
范围查询右边的列，不能使用索引。
不要在索引列上进行运算操作，索引将失效。
字符串不加单引号，造成索引失效。因为 MySQL 的查询优化器会自动的进行类型转换，造成索引失效。

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。

使用 explain 进行分析，在参数 Extra 会有以下信息：

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表
查询数据

用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
以 % 开头的 Like 模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效。

解决方案：通过覆盖索引来解决。
如果 MySQL 评估使用索引比全表更慢，则不使用索引。MySQL 会根据数据情况进行评估。
is NULL ， is NOT NULL 有时索引失效。

根据数据的情况来判断：如果 null 多，那么 is null 就不走索引，is not null 走索引；如果 null 少，那么 is null 走索引，is not null 不走索引。
in 走索引， not in 索引失效。
单列索引和复合索引：尽量使用复合索引，而少使用单列索引。

对于单列索引，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

2、查看索引使用情况

show status like ‘Handler_read%‘;	

show global status like ‘Handler_read%‘;

使用情况的各个参数：

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低越好）。

Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的性能改善不高，因为索引不经常使用（这个值越高越好）。

Handler_read_next ：按照键顺序读下一行的请求数。如果用范围约束或如果执行索引扫描来查询索引列，该值增加。

Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC。

Handler_read_rnd ：根据固定位置读一行的请求数。如果正执行大量查询并需要对结果进行排序该值较高。可能使用了大量需要 MySQL 扫描整个表的查询或连接没有正确使用键。这个值较高，意味着运行效率低，应该建立索引来补救。

Handler_read_rnd_next：在数据文件中读下一行的请求数。如果正进行大量的表扫描，该值较高。通常说明表索引不正确或写入的查询没有利用索引。

视图

概述

视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

视图相对于普通的表的优势主要包括以下几项。

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤
好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但
是通过视图就可以简单的实现。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表
修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

创建或者修改视图

创建视图的语法为：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图的语法为：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

选项 : 
	WITH [CASCADED | LOCAL] CHECK OPTION 决定了是否允许更新数据使记录不再满足视图的条件。
	
	LOCAL ： 只要满足本视图的条件就可以更新。
	CASCADED ： 必须满足所有针对该视图的所有视图的条件才可以更新。 默认值.

例：创建 city_country_view 视图 , 执行如下 SQL :

create or replace view city_country_view 
as 
select t.*,c.country_name from country c , city t where c.country_id = t.country_id;

查看视图

从 MySQL 5.1 版本开始，使用 SHOW TABLES 命令的时候不仅显示表的名字，同时也会显示视图的名字，而不存在单独显示视图的 SHOW VIEWS 命令。

同样地，在使用 SHOW TABLE STATUS 命令的时候，不但可以显示表的信息，同时也可以显示视图的信息。

删除视图

DROP VIEW [IF EXISTS] view_name [, view_name] ...[RESTRICT | CASCADE]

例：删除视图 city_country_view ：

DROP VIEW city_country_view ;

MySQL高级【一】索引和视图

标签：高性能 exist root 引号 show view 链表出现数据并且

本文系统来源：https://www.cnblogs.com/Songzw/p/13226954.html

内容总结

以上是互联网集市为您收集整理的MySQL高级【一】索引和视图全部内容，希望文章能够帮你解决MySQL高级【一】索引和视图所遇到的程序开发问题。如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/525198.html

来源：【匿名】

【上一篇】mysql定义事件并查看事件是否执行【下一篇】用IE远程创建Mysql数据库的简易程序

更多 ►

【MySQL高级【一】索引和视图】教程文章相关的互联网学习教程文章

1.createorreplaceview emp_view asselect*from t4 ;给t4表创建一个名为emp_view的视图2.dropview emp_view 删除视图=======================================1.创建一个存储过程(查询所有数据)createprocedure p1 ()READS SQL DATABEGINselect*from t4;END2.创建一个存储过程(查询传参数据)createprocedure p2(IN tid INT)READS SQL DATABEGINselect*from t4 where id = tid;END3.创建一个存储过程(查询传参数据并返回name)create...

MySQL 视图使用初探【代码】【图】

<一> 视图1.优点视点集中简化操作定制数据合并分割数据安全性2.为什么需要视图？视图是存储在数据库中的查询的sql 语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，可使复杂的查询易于理解和使用。3.创建视图的语法CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition[注]...

mysql 批量导出建表语句 (视图,函数同理)

private static String driverName = "com.mysql.jdbc.Driver"; public static void main(String[] args) throws SQLException { try { Class.forName(driverName); } catch (ClassNotFoundException e) { e.printStackTrace(); System.exit(1); } Connection con = DriverManager.getConnection("jdbc:mysql://192.168.5.148/ifms", "root", "1...

数据库之mysql 视图【代码】

视图，把基本表的某些数据组合起来构成一个虚拟表的一种形式，之所以叫虚拟，是因为只有当视图用于一条语句中的时候，它才能存在。同时如果对视图中的数据进行修改，会同时修改到基本表中的数据。创建视图： create [or replace] view view_name [column1,column2..] as 表的表达式 as: 创建一个视图towns，１.包含了 players 表中的所有城市名，2.接下来并查询这个虚拟表的内容 1.　create view towns asselect townfrom players...

MySQL视图（3）【图】

原文：https://www.cnblogs.com/topass123/p/12963237.html

11.Mysql视图

11.视图11.1 什么是视图视图view是一张虚拟表，它不存储数据，数据仍在表里，视图由一条查询表的select语句创建，视图只存储select语句。可以将复杂的查询语句封装成视图，用户可以从视图中查询，以简化SQL编写；视图中的数据只能查看，不能增删改，可用于权限管理；屏蔽底层数据结构，对表的部分修改(新增列，修改列类型等)不影响视图的结构，不会对用户和应用程序产生影响。11.2 视图操作 11.2.1 创建或者修改视图创建视...

MySQL视图和自定义函数

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 视图是指从一个或者多个表中导出来的数据，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样用户就可以不用看到整个数据而只关心对自己有用的数据。数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。使用视图查询数据时，数据库会从原来的表中取出对应的数据。视图中...

MySQL学习随笔--视图

视图概念数据库中的视图指的是一个虚拟表，其内容由查询定义。同真实的表一样，视图也是由行与列构成的。视图的数据来源由SQL语句查询得到,不存储数据视图创建方法格式 : create view 视图名称 as SQL查询说明: create view //创建视图视图名称 //也就是虚拟表的名称è表名as 后面跟查询语句,不可省略查看视图创建语句格式: show create view 视图名称 show create view 视图名称 \G 注意: \G表示列名垂直显示不加\G列以水平显...

MySQL 数据库视图：视图定义、创建视图、修改视图【图】

简介 1·视图的概念2·创建视图3·使用视图1·视图的概念什么是视图，一下咱们举个例子：一个老板想要了解几个仓库的具体信息，想知道几个仓库里面还有多少大米、面粉、有多少种类，于是派出一个得力干将小王去清点货物，小王跑了很远之后终于清点完毕回报给老板。由于老板的生意很好，仓库的里面的东西经常发生变化，这时老板又需要知道几个仓库里的库存明细，又派小王去清点。这样来来回回，老板觉得小王台辛苦，也不容易，老板就多顾...

MySQL-视图View【代码】

视图:一个非真实存在的,囊括复杂查询在内的表,也可以理解成,视图就是一个查询之后的结果.补充一个概念:临时表查询(子查询):就是在查询语句中再次嵌套一个查询,并将嵌套中的查询设定别名SELECT*FROM(SELECT nid, NAME FROM tb1WHERE nid >2) AS A WHERE A. NAME >‘alex‘;1、创建视图:1--格式：CREATE VIEW 视图名称 AS SQL语句2CREATEVIEW v1 AS3 SELET nid, name FROM A 4WHERE nid >42.删除视图:1--格式：DROP...

mysql基础（九）索引和视图【代码】

索引：是一种按照特定存储格式存储的特殊数据，用于SQL查询索引的类型：聚集索引和非聚集索引：数据是否和索引存储在一起主键索引和辅助索引：索引是否在主键之上左前缀索引和覆盖索引：是否只截取左边的前几个字节最为索引，mysql默认使用左前缀索引左前缀索引的存储：在指定索引的字段中截取左边的前几个字节生成索引，并对索引进行排序处理，将排完序的索引进行分组管理并生成源数据，当用户以索引字段作为查...

mysql视图【代码】【图】

本文转载自https://www.cnblogs.com/chenpi/p/5133648.html#_label0阅读目录什么是视图视图的特性视图的作用使用场合视图实例1-创建视图及查询数据操作视图实例2-增删改数据操作其它什么是视图通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。视图的特性视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改...

MySQL中视图的使用及多表INNER JOIN的技巧分享【代码】

创建视图 Sql代码 CREATE VIEW view_name AS SELECT t1.xxx, t2.xxx, t3.xxx FROM (table1 t1 INNER JOIN table2 t2 ON t1.fid = t2.fid) INNER JOIN table3 t3 ON t1.mid = t3.mid; 这里使用了3表关联，对于多表关联的 INNER JOIN 写法有一个技巧1. 先写最简单的2表关联 INNER JOIN 2. 然后使用 () 从 FROM 之后到语句结尾全部扩起来 3. 在语句结尾开始连接与下一个表的 INNER JOIN记住这个原则，未来进行4表关联，5表关联...

mysql视图学习总结

转自http://www.cnblogs.com/wangtao_20/archive/2011/02/24/1964276.html一、使用视图的理由是什么？ 1.安全性。一般是这样做的:创建一个视图，定义好该视图所操作的数据。之后将用户权限与视图绑定。这样的方式是使用到了一个特性：grant语句可以针对视图进行授予权限。 2.查询性能提高。3.有灵活性的功能需求后，需要改动表的结构而导致工作量比较大。那么可以使用虚拟表的形式达到少修改的效果。这是在实际开发中比较有用的例子...

MYSQL 第十三课视图【代码】

#视图/*含义：虚拟表，和普通表一样使用mysql5.1版本出现的新特性，是通过表动态生成的数据比如：舞蹈班和普通班级的对比创建语法的关键字是否实际占用物理空间使用视图 create view 只是保存了sql逻辑，没有结果增删改查，只是一般不能增删改表 create table 保存了数据增删改查*/#案例：查询姓张的学生名和专业名SELECT stuname,majornameFROM stuinfo sINNER JOIN major m ON s.`majorid`=...

MYSQL - 技术教程分类

MySQL 教程 MySQL 安装 MySQL 管理 MySQL PHP 语法 MySQL 连接 MySQL 创建数据库 MySQL 删除数据库 MySQL 选择数据库 MySQL 数据类型 MySQL 创建数据表 MySQL 删除数据表 MySQL 插入数据 MySQL 查询数据 MySQL WHERE 子句 MySQL UPDATE 更新 MySQL DELETE 语句 MySQL LIKE 子句 MySQL UNION MySQL 排序 MySQL 分组 MySQL 连接的使用 MySQL NULL 值处理 MySQL 事务 MySQL ALTER命令 MySQL 索引 MySQL 临时表 MySQL 复制表 MySQL 元数据 MySQL 序列使用 MySQL 处理重复数据 MySQL 及 SQL 注入 MySQL 导出数据 MySQL 导入数据 MySQL 函数 MySQL 运算符 mysql 全部

MYSQL - 最热教程

sql分组取最大记录方法 mysql如何设置默认值 mysql创建数据表时指定默认值教程 MySQL 5.7 的初始化操作（root初始密码...Oracle中合并数据集(多行变一行)mysql 相同内容的字段合并为一条的方法解决mysql设置时区时的错误Unknown or ...解决ubuntu下mysql的'Access denied fo...ubuntu系统中MysqlERROR1045(28000)报错...mysql数据库设置不区分大小写

首页 / MYSQL / MySQL高级【一】索引和视图

MySQL高级【一】索引和视图

内容导读

内容图文

索引

概述

优劣势

结构

分类

语法

1、创建索引

2、查看索引

3、删除索引

设计原则

索引的使用

1、避免索引失效

2、查看索引使用情况

视图

概述

创建或者修改视图

查看视图

删除视图

内容总结

内容备注

内容手机端

【MySQL高级【一】索引和视图】教程文章相关的互联网学习教程文章

MYSQL - 相关标签

MYSQL - 技术教程分类

MYSQL - 最新教程

MYSQL - 最热教程