索引
什么是索引
-
索引简单来说就是查找我们在数据表中存储数据的一种方法，我们所要考虑的就是要用何种索引的方法能更快更准地找到想要的数据
-
例如：图书馆的图书管理系统就是一个索引，当我们要查询一本图书时，图书管理员总能够很快地找到我们想要的书，可能运用的索引方法就是b+tree。
-
-
在该图中，我们所要找的书的名字是Col2中的值，最左边的物理地址类似于该书在图书馆的具体存放位置（第x楼第y排第z层），Col1表示书的编号。右边的b+tree是索引方法。
-
比如我们要找‘91’这本书，图书管理员输入图书信息，系统根据右边的索引规则，只查询两次就能够查询到‘91’这本书书的地址在0xF3
简单理解索引的知识
- 索引可以简单理解为“排好序的快速查找数据结构”
- 数据库除了数据表内的数据之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引
- 索引的类型有：哈希索引（hash index）、b+树缩影（BTREE）
- 聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引、默认都是使用B+树索引
索引的分类
-
-
索引的规则需要有搜索引擎的支持
索引的普通分类
- 普通索引（index）：索引的关键字是index。允许在定义搜索的列中插入空值和重复值
- 主键索引（primary key）：主键索引是一种特殊的唯一索引，不允许有空值。一个表只有一个主键索引
- 唯一索引（unique）：unique索引列的值必须唯一，允许有空值。如果是组和唯一索引，组和值必须唯一。一个表上可以创建多个唯一索引。
- 全文索引（fulltext）：必须在MyISAM引擎的支持下使用。只能对char、varchar和text类型的列编制索引
- 空间索引（spatial）：必须在MyISAN引擎的支持下使用。
按照创建索引键值的列数分类
- 单列索引：一个索引只包含单个列，一个表可以有多个单列索引
- 复合索引：一个索引包含多个列
按照存储方式分类
- B-TREE索引和Hash索引（Hash索引不能范围查询，因此并不主流）
索引的优劣势
- 优势：查找快捷方便
- 劣势：降低更新表的速度。若该表要大量地进行insert、update、delete等写入操作，则不建议创建索引。
- 类似于一个图书馆的书的位置总是换，导致索引系统找出的地址和书存放的地址不同。长久来看会导致索引不准确。
- 总结：索引就是牺牲空间换取时间
怎样创建索引
用 create index 语句创建索引
建立升序普通索引
在学生表的phone字段上建立一个名字为phone_index的升序普通索引
create index phone_index on student(phone asc);
建立唯一索引
在课程表的cname字段上建立一个名字为cname_index的唯一索引
create unique index cname_index on course(cname);
建立复合索引
在成绩表的studentno和courseno联合字段上建立一个名字为sc_index的复合索引
create index sc_index on score(studentno,courseno);
创建表时创建索引
create table if not exists teacher1(
teacherno char(6) not null comment'教师编号',
tname char(8) not null comment'教师姓名',
department char(16) not null comment'部门',
primary key(teacherno),
unique index tname_index(tname),
index dep_index(department(5))
);
通过alter table 语句创建索引
在teacher1表上创建一个teacherno字段的名为key的主键索引和在tname与prof的联合字段上的名为mark的复合索引
alter table teacher1
add primary key(teacherno),
add index mark(tname,prof);
删除索引
用drop的方法删除索引
例如：删除teacher1上的mark索引
drop index mark on teacher1;
用alter table语句删除索引
例如：删除teacher1表上的mark索引
alter table teacher
drop index mark;
查看索引
show index from table_name;
-
各个字段简述
字段名 作用 Table 表示创建索引的数据表的名字 Non_unqiue 表示该值是否是唯一索引，若是则为0，若不是则为1 Key_name 表示创建的索引名称 Seq_in_index 表示该列在索引中的位置（索引中的序列号）；如果该值是单列的，则值为1；若是组合索引，则该列的值为每列在索引定义中的顺序 Column_name 表示该索引应用在了哪个字段上 Collation 表示列以何种顺序存储在索引中。在Mysql中，升序显示值“A”，若显示为NULL，则表示无分类 Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。 Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。 Comment 多种评注
explain的使用
-
explain的使用
-
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | row | filtered | extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 33.33 | Using where |
-
使用EXPLAIN关键数字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的（简单来说就是可以通过explain语句查看mysql在执行自己输入的语句时的顺序）
-
各字段简述
id
- id相同
id相同表示mysql在执行语句时是自上而下执行的，根据table所指的表来查看表的执行顺序
- id不同
如果是子查询，id的序号会递增，id值越大，越先被执行；可以根据select_type中的值来判断该表在句子中的地位，primary（最外层）、subquery（子查询）；
-
id相同又不同
id如果相同，可以认为是一组，从上往下顺序执行
在所有组中，id值越大，优先级越高，越先执行
衍生：
该语句是先执行t3表，t3表执行完毕后衍生出来了一个t2表，在执行,最后执行t2表
select_type
- 可选值：SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION、UNION RESULT
- 这是表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
- SIMPLE：简单的select查询，查询中不包含子查询或者UNION
- PRIMARY：查询中若包含任何复杂的子部分，最外层查询别标记为PRIMARY
- SUBQUERY：在SELECT或WHERE列表中包含了子查询
- DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）；MySQL会递归执行这些子查询，把加过放在临时表里
- UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED；
- UNION RESULT：从UNION表获取结果的SELECT
table
- 显示这一行的数据是关于哪张表的
type
- 访问类型的排列显示查询使用了何种类型
- 从最好到最坏依次是：system>const>eq_ref>ref>range>index>ALL
- system:表只用一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
- const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配了一行数据，所以很快。入将主键置于where列表中，mysql就能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行
- index: 只遍历索引树
- all： 全盘扫描
possible_keys
- 显示可能在这张表上应用到的索引，一个或者多个
keys
- 在理论上可能用到的索引中真正用到的索引
key_len
- 表示索引中使用的字节数，显示值为索引字段的最大可能长度并不是实际使用长度
ref
- 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些类或者常量被用于索引列上的值
row
- 每张表有多少行被索引
Extra
- Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，MySQL中无法利用索引完成的排序操作称为“文件排序”
- Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序的时候用了临时表。
- USING index: 表示相应的select操作中出现了覆盖索引，避免访问了表的数据行
什么是存储引擎
数据库引擎的使用
- 数据库中的表在创建时均被指定的数据引擎所处理
- 存储引擎之在MySQL数据库中存在
- 用 show engines \G 来查看当前服务器中有哪些可用的存储引擎
- 用 show engines \g 来查看当前数据库支持哪些引擎
MyISAM存储引擎（快速）
- 这个存储引擎是mysql最常用的引擎
- 它管理的表有一下特征
- 使用三个文件表示每个表
- 格式文件 —— 存储表的定义（.frm）
- 数据文件 —— 存储表行的内容（MDY）
- 索引文件 —— 存储表上索引（.MYI）
innoDB存储引擎（安全）
- 是mysql默认的缺省引擎
- 它管理的表有一下特征
- 每个innoDB表在数据库目录中一.frm格式文件表示
- innoDB表空间tablespace被用于存储表的内容
- 支持事务处理
- 在MYSQL服务器崩溃后提供自动恢复
- 多版本行及锁定
- 支持外键及引用的完整性，包括级联删除和更新
MEMORY存储引擎（不支持事务，数据容易丢失）
- 数据存储在内存中
- 行的长度固定
- 不支持事务
- 数据存储在内存中
视图
作用
-
视图就是站在不同的角度去看待一张表
-
视图可以隐藏表的现实细节。有较高的保密性可以对视图进行CRUT操作
操作
-
创建视图
create view view_name as select * from table_name; -
在多表上穿件视图
create view view_name as select table_row1,table_row2,table_row3 from table1 join table2 on table1.row = table2.row where 条件; -
查看视图的定义
-
show table status like ''; //查看视图的基本信息 -
show create view view_name; //查看视图的详细信息 -
select * from view_name.view; //在view表中查看视图的详细信息
-
-
修改视图
-
//用alert语句修改视图的定义 alter view view_name(row1,row2) as select srow1,srow2 from ... where...
-
-
使用视图进行查询
-
select 。。。 from view_name where...;
-
-
使用视图进行统计计算
-
使用视图修改基本数据表
-
插入
insert into view_name(row1,row2) values('1','2'); -
修改
update view_name set row1 = '1'; -
删除
delete from view_name where row1 = '1'; -
查询
select * from view_name;
-
使用视图进行统计计算
-
使用视图修改基本数据表
-
插入
insert into view_name(row1,row2) values('1','2'); -
修改
update view_name set row1 = '1'; -
删除
delete from view_name where row1 = '1'; -
查询
select * from view_name;
-
扫一扫
1039
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
