郑州正规网站制作公司,品牌营销增长好牌子推荐,邯郸做网站的博客,免费建站软件MySQL 一、视图1、什么是视图2、为什么需要视图3、视图的作用和优点4、创建视图5、视图使用规则6、修改视图7、删除视图 二、索引1、什么是索引2、索引优缺点3、索引分类4、索引的设计原则5、创建索引5.1 创建表是创建索引5.2 create index5.3 ALTER TABLE 6、删除索引7、MySQL… MySQL 一、视图1、什么是视图2、为什么需要视图3、视图的作用和优点4、创建视图5、视图使用规则6、修改视图7、删除视图 二、索引1、什么是索引2、索引优缺点3、索引分类4、索引的设计原则5、创建索引5.1 创建表是创建索引5.2 create index5.3 ALTER TABLE 6、删除索引7、MySQL使用索引的场景MySQL索引的优化 8、SQL如何使用索引9、聚簇索引和非聚簇索引9.1 非聚簇索引9.2 聚簇索引 一、视图
1、什么是视图
视图通过以定制的方式显示来自一个或多个表的数据 视图是一种数据库对象用户可以像查询普通表一样查询视图 视图内其实没有存储任何数据它只是对表的一个查询 视图的定义保存在数据字典内创建视图所基于对表称为“基表”
2、为什么需要视图
例如经常要对emp和dept表进行连接查询每次都要做表的连接写同样的一串语句同时由于工资列队数据比较敏感对外要求不可见。对这样的问题就可以通过视图来解决。
3、视图的作用和优点
作用
控制安全保存查询数据
优点
提供了灵活一致级别安全性。隐藏了数据的复杂性简化了用户的SQL指令通过重命名列从另一个角度提供数据
4、创建视图
CREATE [OR REPLACE] VIEW 视图名
[(alias[, alias]...)]--为视图字段指定别名
AS subquery
[WITH READ ONLY];创建视图, EMP_V_10, 包括10号部门的所有雇员信息。
CREATE VIEW emp_v_10
AS SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE manager_id;查看视图
SELECT * FROM emp_v_10;5、视图使用规则
视图必须有唯一命名在mysql中视图的数量没有限制创建视图必须从管理员那里获得必要的权限视图支持嵌套也就是说可以利用其他视图检索出来的数据创建新的视图在视图中可以使用OREDR BY但是如果视图内已经使用该排序子句则视图的ORDER BY将覆盖前面的ORDER BY。视图不能索引也不能关联触发器或默认值视图可以和表同时使用
6、修改视图
使用CREATE OR REPLACE VIEW 语句修改EMP_V_10 视图. 为每个列指定列名。
CREATE OR REPLACE VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id 10;在CREATE VIEW 语句中字段与子查询中的字段必须一一对应否则就别指定别名或在子查询中指定别名使用ALTER VIEW 语句修改EMP_V_10 视图. 为每个列指定列名。
ALTER VIEW emp_v_10
(id, name, sal, dept_id)
AS SELECT id,name,
salary, dept_id
FROM employees
WHERE dept_id 10;在CREATE VIEW 语句中字段与子查询中的字段必须一一对应否则就别指定别名或在子查询中指定别名创建复杂视图创建一个从两个表中查询数据并进行分组计算的复杂视图。
CREATE VIEW dept_sum_vu_10
(name, minsal, maxsal, avgsal)
AS SELECT d.name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.dept_id d. id
AND e.dept_id 10;7、删除视图
查询库中哪些是视图表
show table status where comment view;删掉视图不会导致数据的丢失因为视图是基于数据库的表之上的一个查询定义。
DROP VIEW view_name;案例演示
1、在数据库example下创建college表。College表内容如下所示
字段名字段描述数据类型主键外键非空唯一自增number学号INT(10)是否是是否name姓名VARCHAR(20)否否是否否major专业VARCHAR(20)否否是否否age年龄NT(5)否否否否否
CREATE TABLE college(number INT(10) NOT NULL UNIQUE PRIMARY KEY COMMENT 学号,name VARCHAR(20) NOT NULL COMMENT 姓名,major VARCHAR(20) NOT NULL COMMENT 专业,age INT(5) COMMENT 年龄
);2、在student表上创建视图college_view。视图的字段包括student_num、student_name、student_age和department。ALGORITHM设置为MERGE类型并且为视图加上WITH LOCAL CHECK OPTION条件
CREATE ALGORITHMMERGE VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major FROM college
WITH LOCAL CHECK OPTION;3、查看视图college_view的详细结构
SHOW CREATE VIEW college_view \G4、 更新视图。向视图中插入3条记录。记录内容如下表所示
umernamemajorage0901张三外语200902李四计算机220903王五计算机19
INSERT INTO college_view VALUES(0901,张三,20,外语);
INSERT INTO college_view VALUES(0902,李四,22,计算机);
INSERT INTO college_view VALUES(0903,王五,19,计算机);5 、修改视图使其显示专业为计算机的信息其他条件不变
方法一
CREATE OR REPLACE ALGORITHMUNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major’计算机’
WITH LOCAL CHECK OPTION;方法二
ALTER ALGORITHMUNDEFINED VIEW
college_view(student_num,student_name,student_age,department)
AS SELECT number,name,age,major
FROM college WHERE major’计算机’
WITH LOCAL CHECK OPTION;6 、删除视图college_view
DROP VIEW college_view;二、索引
索引是一种特殊的数据库结构可以用来快速查询数据库表中的特定记录。索引是提高数据库性能的重要方式。 MySQL中所有的数据类型都可以被索引。MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)它们包含着对数据表里所有记录的引用指针。 更通俗的说数据库索引好比是一本书前面的目录能加快数据库的查询速度。注意一般数据库默认都会为主键生成索引。
1、什么是索引
模式(schema)中的一个数据库对象
在数据库中用来加速对表的查询
通过使用快速路径访问方法快速定位数据,减少了磁盘的I/O
与表独立存放但不能独立存在必须属于某个表
由数据库自动维护表被删除时该表上的索引自动被删除。
索引的作用类似于书的目录几乎没有一本书没有目录
因此几乎没有一张表没有索引。索引的原理 就是把无序的数据变成有序的查询
把创建的索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候先拿到倒排表内容再取出数据地址链从而拿到具体数据
2、索引优缺点 索引的优点是 可以提高检索数据的速度这是创建索引的最主要的原因对于有依赖关系的子表和父表之间的 联合查询时可以提高查询速度使用分组和排序子句进行数据查询时同样可以显著节省查询中分组和排序的时间。 索引的缺点是 创建和维护索引需要耗费时间耗费时间的数量随着数据量的增加而增加索引需要占用物理空 间每一个索引要占一定的物理空间增加、删除和修改数据时要动态的维护索引造成数据的维护速度降低了。
3、索引分类
索引分为聚簇索引和非聚簇索引两种聚簇索引是按照数据存放的物理位置为顺序的而非聚簇索引就不一样了聚簇索引能提高多行检索的速度而非聚簇索引对于单行的检索很快。
MySQL的索引包括普通索引、惟一性索引、全文索引、单列索引、多列索引和空间索引等。
按数据结构分类 从数据结构来看MySQL常见索引有BTree索引HASH索引Full-Text索引。 每一种存储引擎支持的索引类型不一定相同 InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎BTree 索引类型也是 MySQL 存储引擎采用最多的索引类型。 在创建表时InnoDB 存储引擎会根据不同的场景选择不同的列作为索引 如果有主键默认会使用主键作为聚簇索引的索引键key 如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键key 在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键key
其它索引都属于辅助索引Secondary Index也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 BTree 索引。
4、索引的设计原则
为了使索引的使用效率更高在创建索引的时候必须考虑在哪些字段上创建索引和创建什么类型的索引。本小 节将向读者介绍一些索引的设计原则。
选择惟一性索引为经常需要排序、分组和联合操作的字段建立索引为常作为查询条件的字段建立索引限制索引的数目尽量使用数据量少的索引尽量使用前缀来索引删除不再使用或者很少使用的索引
5、创建索引
创建索引是指在某个表的一列或多列上建立一个索引以便提高对表的访问速度。创建索引有三种方式这三种方式分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。
5.1 创建表是创建索引
创建表的时候可以直接创建索引这种方式最简单、方便。其基本形式如下
CREATE TABLE 表名 ( 属性名 数据类型 [完整性约束条件],
属性名 数据类型 [完整性约束条件],
…
属性名 数据类型
[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY
[别名](属性名1 [(长度)] [ASC | DESC])
);1、普通索引
# 直接创建索引
CREATE INDEX index_name ON table(column(length))
# 创建表的时候同时创建索引
Create table index1(Id int,Name varchar(20),Sex boolean,index(id),
);
# 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
# 查询索引
Show create table index1 \G
# 查询某张表中索引情况
show index from table_name;
# 使用计划查询SQL使用索引情况
Explain select * from index1 where id1 \G
# 删除索引
DROP INDEX index_name ON table2、创建唯一性索引 当然也有多种创建方式
Create table index2(Id int unique,Name varchar(20),Unique index index2_id(id asc)
);3、创建全文索引FULLTEXT
MySQL从3.23.23版开始支持全文索引和全文检索FULLTEXT索引仅可用于 MyISAM 表他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建或是随后使用ALTER TABLE 或CREATE INDEX被添加。 对于较大的数据集将你的资料输入一个没有FULLTEXT索引的表中然后创建索引其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。只能创建在char,varchar或text类型的字段上。
create table index3(Id int,Info varchar(20),Fulltext index index3_info(info)
);
explain select * from table where id1;EXPLAIN分析结果的含义: table这是表的名字。 type连接操作的类型ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好 possible_keys可能可以利用的索引的名字 Key它显示了MySQL实际使用的索引的名字。如果它为空或NULL则MySQL不使用索引。 key_len索引中被使用部分的长度以字节计。 ref它显示的是列的名字或单词“const”MySQL将根据这些列来选择行 rowsMySQL所认为的它在找到正确的结果之前必须扫描的记录数。显然这里最理想的数字就是1 Extra这里可能出现许多不同的选项其中大多数将对查询产生负面影响 4、创建单列索引
Create table index4(Id int,Subject varchar(30),Index index4_st(subject(10))
); 5、创建多列索引 使用多列索引时一定要特别注意只有使用了索引中的第一个字段时才会触发索引。如果没有使用索引中的第一个字段那么这个多列索引就不会起作用。也就是说多个单列索引与单个多列索引的查询效果不同因为执行查询时MySQL只能使用一个索引会从多个索引中选择一个限制最为严格的索引。
Create table index5(Id int,Name varchar(20),Sex char(4),Index index5_ns(name,sex)
);6、创建空间索引
Create table index6(Id int,Space geometry not null,Spatial index index6_sp(space)
)enginemyisam;建空间索引时表的存储引擎必须是myisam类型而且索引字段必须有非空约束。空间数据类型包括geometry,point,linestring和polygon类型等。平时很少用到。
5.2 create index
首先保证已经存在表才能使用这个命令创建索引。 在已经存在的表上可以直接为表上的一个或几个字段创建索引。基本形式如下help create index
CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 (属性名 [ (长度) ] [ ASC | DESC] );1创建普通索引
CREATE INDEX index_name ON table(column(length))2创建惟一性索引
CREATE UNIQUE INDEX indexName ON table(column(length))3创建全文索引
CREATE FULLTEXT INDEX index_content ON article(content)4创建单列索引
CREATE INDEX index3_name on index3 (name(10));5创建多列索引6创建空间索引
5.3 ALTER TABLE
用ALTER TABLE语句来创建索引也是存在表的情况下。 在已经存在的表上可以通过ALTER TABLE语句直接为表上的一个或几个字段创建索引。基本形式如下
ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX
索引名属性名 [ (长度) ] [ ASC | DESC];1创建普通索引
ALTER TABLE table_name ADD INDEX index_name (column(length))2创建惟一性索引
ALTER TABLE table_name ADD UNIQUE indexName (column(length))3创建全文索引
ALTER TABLE index3 add fulltext index index3_name(name);4创建单列索引
ALTER TABLE index3 add index index3_name(name(10));5创建多列索引6创建空间索引
6、删除索引
删除索引是指将表中已经存在的索引删除掉。一些不再使用的索引会降低表的更新速度影响数据库的性能。 对于这样的索引应该将其删除。本节将详细讲解删除索引的方法。 对应已经存在的索引可以通过DROP语句来删除索引。基本形式如下
DROP INDEX 索引名 ON 表名 ;索引示例
1、 在数据库job下创建workInfo表。创建表的同时在id字段上创建名为index_id的唯一性索引而且以降序的格式排列。workInfo表内容如下所示
字段描述数据类型主键外键非空唯一自增id编号INT(10)是否是是name职位名称VARCHAR(20)否否是否type职位类别VARCHAR(10)否否否否address工作地址VARCHAR(50)否否否否wage工资INT否否否否contents工作内容TINYTEXT否否否否extra附加信息TEXT否否否否
CREATE TABLE workInfo(id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,name VARCHAR(20) NOT NULL,type VARCHAR(10),address VARCHAR(50),tel VARCHAR(20),wage INT,content TINYTEXT,extra TEXT,UNIQUE INDEX index_id(id DESC));2 、使用create index语句为name字段创建长度为10的索引index_name
CREATE INDEX index_name ON workInfo(name(10));3 、使用alter table语句在type和address上创建名为index_t的索引
ALTER TABLE workInfo ADD INDEX index_t(type,address);4 、将workInfo表的存储引擎更改为MyISAM类型
ALTER TABLE workInfo ENGINEMyISAM;5 、使用alter table语句在extra字段上创建名为index_ext的全文索引
ALTER TABLE workInfo ADD FULLTEXT INDEX index_ext(extra);6 、删除workInfo表的唯一性索引index_id
DROP INDEX index_id ON workInfo;实例测试MySQL使用索引带来的效率提升
1、创建测试表
create table test1(id int,num int,pass varchar(50)
);
create table test2(id int,num int,pass varchar(50),index idIdx (id)
);
create table test3(id int,num int,pass varchar(50)
);2、向表test1里插入1000000条数据
for ((i1;i1000000;i));do mysql -p123456 -uroot -e insert into it.test1
values($i,floor($irand()*$i),md5($i));; done /tmp/mysql.txt 21
# 注意测试时可以插入300000条记录
mysql select count(*) from test1;
----------
| count(*) |
----------
| 300000 |
----------
1 row in set (0.12 sec)3、在有索引和没有索引的情况下执行查询 1没有创建索引时查询
mysql reset query cache;
mysql explain select num,pass from test3 where id5000 and id5050;2创建索引后再次查询
mysql reset query cache;
mysql explain select num,pass from test2 where id5000 and id5050;4、在有索引和没有索引的情况下新增数据 1没有创建索引时插入数据
mysql insert into test3 select * from test1;
Query OK, 300000 rows affected (1.00 sec)
Records: 300000 Duplicates: 0 Warnings: 02创建索引后再次插入数据
mysql insert into test2 select * from test1;
Query OK, 300000 rows affected (1.17 sec)
Records: 300000 Duplicates: 0 Warnings: 07、MySQL使用索引的场景
1.快速查找符合where条件的记录2.快速确定候选集。若where条件使用了多个索引字段则MySQL会优先使用能使候选记录集规模最小的那个索引以便尽快淘汰不符合条件的记录。3.如果表中存在几个字段构成的联合索引则查找记录时这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。 例如若为某表创建了3个字段(c1, c2, c3)构成的联合索引则(c1), (c1, c2), (c1, c2, c3)均会作为索引(c2, c3)就不会被作为索引而(c1, c3)其实只利用到c1索引。4.多表做join操作时会使用索引如果参与join的字段在这些表中均建立了索引的话。5.若某字段已建立索引求该字段的min()或max()时MySQL会使用索引6.对建立了索引的字段做sort或group操作时MySQL会使用索引
MySQL索引的优化
上面都在说使用索引的好处但过多的使用索引将会造成滥用。因此索引也会有它的缺点虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT、UPDATE和DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重但如果你在一个大表上创建了多种组合索引索引文件的会膨胀很快。索引只是提高效率的一个因素如果你的MySQL有大数据量的表就需要花时间研究建立最优秀的索引或优化查询语句。下面是一些总结以及收藏的MySQL索引的注意事项和优化方法。
何时使用聚集索引或非聚集索引
动作描述使用聚集索引使用非聚集索引列经常被分组排序使用使用返回某范围内的数据使用不使用一个或极少不同值不使用不使用小数目的不同值使用不使用大数目的不同值不使用使用频繁更新的列不使用使用外键列使用使用主键列使用使用频繁修改索引列不使用使用
8、SQL如何使用索引
1.B-Tree可被用于sql中对列做比较的表达式如, , , , 及between操作2.若like语句的条件是不以通配符开头的常量串MySQL也会使用索引。比如SELECT * FROM tbl_name WHERE key_col LIKE Patrick%或SELECT * FROM tbl_name WHERE key_col LIKE Pat%_ck%可以利用索引而SELECT * FROM tbl_name WHERE key_col LIKE %Patrick%以通配符开头和SELECT * FROM tbl_name WHERE key_col LIKE other_collike条件不是常量串无法利用索引。 对于形如LIKE %string%的sql语句若通配符后面的string长度大于3则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找.3.若已对名为col_name的列建了索引则形如col_name is null的SQL会用到索引。4.对于联合索引sql条件中的最左前缀匹配字段会用到索引。5.若sql语句中的where条件不只1个条件则MySQL会进行Index Merge优化来缩小候选集范围
MySQL只对一下操作符才使用索引,,,,,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引版本不同可能会有变化。
9、聚簇索引和非聚簇索引
9.1 非聚簇索引
索引节点的叶子页面就好比一片叶子。叶子头便是索引键值。 先创建一张表
CREATE TABLE user (
id INT NOT NULL ,
name VARCHAR NOT NULL ,
class VARCHAR NOT NULL);对于MYISAM引擎如果创建 id 和 name 为索引。对于下面查询
select * from user where id 1会利用索引先在索引树中快速检索到 id但是要想取到id对应行数据必须找到改行数据在硬盘中的存储位置因此MYISAM引擎的索引叶子页面上不仅存储了主键id 还存储着 数据存储的地址信息。如图
1234磁盘磁盘磁盘磁盘地址地址地址地址
像这样的索引就称为非聚簇索引。
非聚簇索引的二级索引与主键索引类似。假设我们对name添加索引那么name的索引树叶子将是如下结构
zhangsanlisiwangwulaoliu磁盘磁盘磁盘磁盘地址地址地址地址
9.2 聚簇索引
对于非聚簇索引来说每次通过索引检索到所需行号后还需要通过叶子上的磁盘地址去磁盘内取数据回行消耗时间。为了优化这部分回行取数据时间InnoDB 引擎采用了聚簇索引。
聚簇索引即将数据存入索引叶子页面上。对于 InnoDB 引擎来说叶子页面不再存该行对应的地址而是直接存储数据
3456name1name2name3name4class1class2class3class4
这样便避免了回行操作所带来的时间消耗。 使得 InnoDB 在某些查询上比 MyISAM 还要快
关于查询时间一般认为 MyISAM 牺牲了功能换取了性能查询更快。但事实并不一定如此。多数情况下MyISAM 确实比 InnoDB 查的快 。但是查询时间受多方面因素影响。InnoDB 查询变慢得原因是因为支持事务、回滚等等使得 InnoDB的叶子页面实际上还包含有事务id换句话说就是版本号 以及回滚指针。
在二级索引方面 InnoDB 与 MyISAM 有很大区别。
InnoDB默认对主键建立聚簇索引。如果你不指定主键InnoDB会用一个具有唯一且非空值的索引来代替。如果不存在这样的索引InnoDB会定义一个隐藏的主键然后对其建立聚簇索引。一般来说InnoDB 会以聚簇索引的形式来存储实际的数据它是其它二级索引的基础。
假设对 InnoDB 引擎上表name字段加索引那么name索引叶子页面则只会存储主键id
name1name2name3name43456
检索时先通过name索引树找到主索引id再通过id在主索引树的聚簇索引叶子页面取出数据。
