数据库设计_索引和引擎和序列和存储过程和视图入门(小白篇)

一、基本认知

1-----什么是索引？

如果将数据库中的一张表比喻为一本新华字典，那么索引就是这本又厚又重的字典的目录! 使用索引是为了提高搜索引用的效率，节约时间!同样以一本字典为例，一般情况我们查找一个目标的方法是两种：一种是穷举，逐页地查找自己想要的，这种情况下字典有1千万条记录，那么你的最大搜索就是一千万，搜索的效率会随着字典的厚度而降低; 另外一种是根据目录(或拼音或部首)查找，先找到目标所在页数，然后我们去翻页!

2-----什么是引擎?
如果将数据库中的一张表比喻为一本新华字典，那么引擎就是这边又厚又重的管理员，或者说是你指定要帮你执行查询字典的人，帮你新增目标进入字典的人!那么人根据类型不同可以分为小孩，年轻人，青年人，中年人等，不同年龄阶段的人能力的强弱不同，不于不同职业的人，能力偏重也各有不同！对于引擎来说，同样也如是，有的引擎侧重搜索，有的引擎侧重对数据库修改，有的引擎侧重事务，有的引擎丝毫不在意事务；不同类型的引擎能力各有不同，效率也各不相同；

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

二、入门

1-----索引类型？

常用的索引类型有：主键索引，普通索引，聚合索引(也即是多字段组合所以)，唯一索引，全文索引。 在日常开发中，唯一索引和全文索引使用得比较少，我们主要接触的是前三种索引；

---

主键索引：顾名思义即是数据库把表的主键字段默认设置为索引，一般在建表就已经存在了！

普通索引：针对普通非主键字段建立索引，一般该字段被作为高频查询条件在很多场景中使用!

聚合索引：多个字段一起作为索引

唯一索引：唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复，一般通过约束UNIQUE添加

全文索引：索引(查找)的是目标内容的拆分集合，其流程是读取数据到内存，调用分词算法对目标进行分词最后进行索引，从而实现提高关键字搜索功能的效率!可以简单理解为高效的模糊查询(Like)。全文索引一般是在大量数据库中才会有，对于一般数据库而言，数据量越小，关联性越低，拆分的词可能匹配度越低，冒用全文索引可能找不到目标!  大白话理解就是将所在列的词打散与排列组合，放在内存中，然后再从这些被打散的词里面找到符合自己要求的，再索引到原本的记录,因此数据的相关性会与数据量相关!

普通词频查询:
SELECT * FROM tableNname WHERE MATCH(index_name) AGAINST(‘xxx,xxx’); 
高频词的查询：
SELECT * FROM tableNname WHERE MATCH(index_name) AGAINST (‘高频词’ IN BOOLEAN MODE)

2-----操作索引？

查看某张表的所有索引：show  index  from  表名； 

增加某张表的普通索引：alter   表名  add index  索引名(加索引的列)  

删除某张表的某个索引：drop index  索引名  on  表名

添加某张表的全文索引：alter talbe tablename add fulltext( column); 

3-----操作引擎？

MyISAM：不支持事务，读写互相阻塞，并发性低，以读为主，同时只会缓存索引key，不会缓存数据。尽量顺序操作以保证不阻塞(表锁定，读写单一且不可重复)，其全表扫描操作效率比较高

InnoDB：支持事务四大特性，行级别锁定，可以缓存索引也可以缓存数据，表数据和主键关联后以平衡树形式存放，因此尽可能将主键设置小以拥有更多空间量!对高并发和数据一致性场景有较好适应，适用于频繁改动数据的场景！

NDBCluster：分布式存储引擎，可与mysql不在同一台主机上， 搜索方式单一，内存需求量巨大，每次查询数据量小(少用)

3-----测试案例？

步骤1：创建一张测试用的表，在该表里面维护10万到20万的数据以进行测试

##使用该表字段信息
CREATE TABLE `batchinfo`(
    `id` BIGINT(20)PRIMARY KEY NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(20)DEFAULT NULL,
    `password` VARCHAR(50)DEFAULT NULL,
    `mobile`   VARCHAR(11)DEFAULT NULL,
    `email`    VARCHAR(50)DEFAULT NULL
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
================================================================================================

解析：建表时候使用的存储引擎是MyISAM ，此引擎没有事物等数据库高级应用，在按顺序操作和可读性效率比较高，方便我们快速插入测试数据，待插入完数据把存储引擎改为InnoDB~

步骤2：往测试表里面插入测试用的数据(此处利用存储过程)

CREATE PROCEDURE batchinfo_addData()
BEGIN
    DECLARE num INT;
    SET num = 1;
    WHILE num <= 100000 DO
        INSERT INTO batchinfo(username, password,mobile,email) VALUES (num, PASSWORD(num),num,num);
        SET num = num + 1;
    END WHILE;
END;
call batchinfo_addData();

解析：存储过程适用于格式化的数据库操作

步骤3：修改存储引擎，为表添加事务机制

alter table batchinfo engine=InnoDB;

步骤4：此时表中存在至少10万条数据，那么可以尝试测试索引与不索引之间的区别

主键索引：
select id,username,password,mobile,email from batchinfo where id=9998
统计用时：0.0xx s

---

无索引情况下执行组合查询:
select id,username,password from andy_user where username='99999' and password = '1111'
统计用时:0.0xx s

添加组合索引：
ALTER TABLE `batchinfo` ADD INDEX index_union_name_password(username,password)
再次执行SQL,统计用时：0.0xx s

删除索引,以方便下一次测试：
drop index index_union_name_password on btahinfo;

无索引查询：
select id,username,password,mobile,email from batchinfo where username='9998'
统计用时：0.0xx s

添加普通索引:
ALTER TABLE `andy_user` ADD INDEX index_name(username);
执行SQL,统计用时:0.00xx s

 解析：根据每次有无执行索引的耗时来看，执行索引的效率要数倍高于普通无索引查询

4-----常用数据库结构查询？

查询数据库的表结构：desc  tablename;

查询执行计划的信息：desc  执行的SQL  ；

查询执行计划的信息：explain 执行的SQL ；

解析：执行计划可以查看本次查询检索的行数，使用的索引等信息

---

5-----什么是序列?

通俗地理解，我们把数据库当成一本大词典，在这本词典中，词典的维护者按照一定的规律给每条记录都做了一个序号，每增加一个便维护一个序号!这一串序号就被称为序列!

序列(SEQUENCE)是序列号生成器，可以为表中的行按照一定的规则自增或自减地生成一组有规律的序列号，以保证不重复。其在数据库的主要的用途是生成表的主键的数值，可以在插入语句中引用也可以通过查询检查当前值，或使序列增至下一个值。创建序列需要CREATE SEQUENCE系统权限! 其基础语法如下:

CREATE SEQUENCE  sequence_name                  //此处填入序列名
[INCREMENT BY n]                                //递增量,可以为正整数或负整数,每次步长
[START WITH n]                                  //生成的第一个N1值
[{MAXVALUE/ MINVALUE n|NOMAXVALUE}]             //（最大n）或（最小 n）或（nomaxvalue）
[{CYCLE|NOCYCLE}]                               //（ CYCLE ） 或 （NO CYCLE）
[{CACHE n|NOCACHE}];                            //（ CACHE 10 ）或者 （ NOCACHE ）
==========================================================================================
括号里面为执行方法举例子，可以参考使用!下面为网络上常用的方式,可参考
create seqence sequence_name 

[start with n1] 

[increment by n2] 

[maxvalue n3|no maxvalue] 

[minvalue n4|no minvalue] 

[cache n5|no cache] 

[cycle|no cycle] 

[order|no order]

----------------->n1到n5都是整数

----------------->start with 生成的第一个n1值

-----------------> increment by n2 递增量,可以为正整数或负整数，指明每一次增加多少

----------------->maxvalue最大值，no maxvalue用于指定序列没有上限

----------------->minvalue 最小值，no minvalue,没有指定最小下限

----------------->cache 用高速缓存中可以预分配的序列号个数,默认是20。 如果缓存中的序列号没有用完就关闭数据库等其它原因. 没有使用的序列号就丢失了,所以不能保证序列号是连续的。nocache高速缓冲中不预分配序列号，每次只生成一个序列号,虽然降低了获取序列号的速度,但是可以保证序列号的连续性。
----------------->cycle 序列达到最大值或最小值后是否循环。再从n1开始循环，默认不no cycle

----------------->order用于指定按顺序生成序列，只有在RAC时需要指定，指定ORDER 是为了保证序列号是因为有请求才生成的。在使用序列号做为一个时间戳时很有用，no order是不指定按顺序生成序列（默认）。

解析：初级阶段对序列号的使用主要在代理主键以维护唯一标识，以及用于跟踪最新的数据库变更记录，将之高效地查询出来!

---

序列创建后如何使用呢?   ==>

引用下一个序列值 ： 创建的序列名.nextval

引用当前的序列值： 创建的序列名.currval  或者 创建的序列名.nextval.currval

注意：第一次读取的时候，当前序列值是最后一个被引用的值，已经被使用，首次取值必须保证是经过一个 nextval 获取到的值!

在插入语句中应用 ： 

查询序列信息: 
insert tab_name into values(1, 'abc', nextval for sequence_name);   
或者 insert tab_name into values(1, 'xyz', sequence_name.nextval);
============================================================================================
修改除了初始值以后的字段信息:
alter sequence_name minvalue 100000 maxvalue 99999999 increment by 10 cache 10 cycle;
============================================================================================
借助查询序列：
select sequence_name.nextval from dual; ------>每执行一次sequence号会一直增加

select sequence_name.currval from dual; ------->执行多少次都不会变化

select *  from sequence_table  where xxxx=sequence_name.currval-------->作为条件

序列如何删除呢?  ==> DROP SEQUENCE SEQ_TEST;   //SEQ_TEST 为表名

注意：

1.序列号和序列的起始值不可以改变，否则会报错

2.在DELETE、SELECT、UPDATE的子查询，涉及视图的查询中不可以使用序列

3.如果SQL中使用了DISTINCT去重符号，使用了GROUP BY或者ORDER BY，不可使用序列号

---

6-------存储过程?

对于流程化的操作，逻辑简单的批量化操作，可以建立存储过程进行操作?

  1.如何创建存储过程

CREATE PROCEDURE batchinfo_addData()
BEGIN
    DECLARE num INT;
    SET num = 1;
    WHILE num <= 100000 DO
        INSERT INTO batchinfo(username, password,mobile,email) VALUES (num, PASSWORD(num),num,num);
        SET num = num + 1;
    END WHILE;
END;
call batchinfo_addData();

 2.查看数据库的存储过程？

#查询存储过程的表结构==>desc information_schema.routines

#查询某个数据库的存储过程==>select * from information_schema.routines where  ROUTINE_SCHEMA='mmall'

#查看数据库的详细存储信息==>SHOW PROCEDURE STATUS WHERE db='mmall';

#查看存储过程的详细定义 ==>SHOW CREATE PROCEDURE 数据库.某个存储过程名称;

 3.如何删除存储过程？

#删除某个存储过程 ===>DROP PROCEDURE  mmall.andy_user_addData_proc;

 4.如何调用其他用户的存储过程?

存储过程是用户的私有品，如果一个用户想要调用另外一个用户的存储过程，则 存储过程的拥有者需要赋予他使用的权限!

GRANT  EXECUTE  ON PROCEDURE  <过程名>   TO  <user>;
在数据库中,每一个<xxx>都是代表此处包括<>符号都是替代位置

---

7--------视图？

1.视图有什么好处？视图可以简化用户的数据，形成直观简洁的图表  , 具有视点集中，简化操作，可以根据用户不同水平定制化数据，合并分割数据，提供安全保护和权限操作等机制。

1.什么是视图?  还是老样子，用大白(●—●)话解释就是指一张可视化的表，但这张表依赖于我们的查询语句查询结果集生成的虚拟表，其组成可以是来自其他各类表的字段信息,之所以称之为可视化的表是因为它被创建后跟一般的表没区别，完全可以当做正常表使用！我们将数据库比作一本字典，那么视图就是一张被用户从字典中按照自己喜欢筛选出来的信息的集合! 

对于数据库而言，每一张表本质上某一种单一关系的体现，如商品表拥有商品信息，学生表维护学生信息。对于每一张表来说，其所包含的信息都比较单一。但总存在着一些场景我们需要携带混合关系的信息，如商品表与地址表与用户表之间混合使用，这类的混合关系如果为之建立数据库的表则会产生大量的冗余，对数据库是很不友好的！ 视图本身不存储数据，而是存储查询结果的定义，其本质上是一种混合关系的体现!  用户可以直接对视图中所存储的关系进行各种操作，就如同面对的是真实的数据表!

1.如何创建一张视图?

//创建
create view useridInfo as select username,email,phone,question ,answer from mmall_user;

//查询：正常流程下,当做正常的表使用

2.如何修改一张视图？

//修改
ALTER VIEW  userInfo   AS  select  xxx,xxx,xxx from mmall_user  where  xxxx;

 3.如何插入一条视图记录?

//前提条件是什么?
（1）使用INSERT语句向数据表中插入数据时，用户必须有插入数据的权利。
（2）由于视图只引用表中的部分字段，所以通过视图插入数据时只能明确指定视图中引用的字段的取值。而那些表中并未引用的字段，必须知道在没有指定取值的情况下如何填充数据，因此视图中未引用的字段必须具备下列条件之一。
         该字段允许空值。
         该字段设有默认值。
         该字段是标识字段，可根据标识种子和标识增量自动填充数据。
         该字段的数据类型为timestamp或uniqueidentifier。
（3）视图中不能包含多个字段值的组合，或者包含使用统计函数的结果。
（4）视图中不能包含DISTINCT或GROUP BY子句。
（5）如果视图中使用了WITH CHECK OPTION，那么该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件。如果插入的数据不符合该条件，SQL Server会拒绝插入数据。
（6）不能在一个语句中对多个基础表使用数据修改语句。因此，如果要向一个引用了多个数据表的视图添加数据时，必须使用多个INSERT语句进行添加。
==========================================================================================
//样式与正常的SQL一致,保持其基表没有维护的字段是默认为空或者可以自己根据规则填充~
insert into useridInfo(username,email,question,answer)values('ZDS','1922353@qq.com','你好?','还行');

 4.如何删除一张视图?

//删除
drop view  view_name;

---