MySQL索引与视图

索引

1.什么是索引

索引是由数据库表中一列或多列组合而成，其作用是提高对表中数据的查询速度。
类似于图书的目录，方便快速定位，寻找指定的内容。

2.索引的优缺点

优点：提高查询数据的速度。
缺点：创建和维护索引的时间增加了，同时占用硬盘空间。

3.索引分类

1) 普通索引：是最基本的索引，它没有任何限制；

2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；

3)主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；

4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；

5) 全文索引：使用FULLTEXT参数可以设置，全文索引只能创建在CHAR,VARCHAR,TEXT类型的字段上。主要作用是提高查询较大字符串类型的速度；只有MyISAM引擎支持该索引，MySQL默认引擎不支持；mysql5.7+

4.创建索引

语法：CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC]）
普通索引

CREATE INDEX t_log_moduleid on t_log(moduleid)
SELECT * FROM t_log WHERE moduleid='100401'

5.修改索引

语法：ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT] INDEX 索引名(字段名[(长度)][ASC|DESC])

ALTER TABLE t_log ADD INDEX t_log_url(url)
SELECT * FROM t_log WHERE url='/dict/queryDictPager'

6.删除索引

语法：DROP INDEX 索引名 ON 表名

DROP INDEX t_log_moduleid on t_log

全文索引

ALTER TABLE `t_p1_blog` ADD FULLTEXT ft_blog_summary_content  (title,summary,content)
select  *  from  t_p1_blog  where  match  (title,summary,content)  against  ('ssm Linux');

索引对性能的提升

SELECT * FROM t_log where id = '410860a3b6f44f6a856d67409e5a5f51';
SELECT * FROM t_log where moduleid = '100310';#0.431s

create index idx_moduleid on t_log(moduleid);
select * from t_log where moduleid='100310';#0.032s

DROP INDEX idx_moduleid ON t_log;

组合索引问题
#组合索引，必须满足最左前缀原则
#满足
#情况1：id,moduleid,url
#情况2：id,moduleid
#情况3：id
#不满足：不以id开头的，均不满足组合索引最左前缀原则

select * from t_log where 
id='d7a46a9d60bf11e99fe528d24413c293' 
and url='/log/queryLogPager'
and moduleid='110401';
drop index idx_group_1 on t_log;

数据库备份

导出整个数据库结构和数据
语法：mysqldump -h localhost -uroot -p123456 database > dump.
代码：mysqldump -h localhost -uroot -p123456 db_xiaoli > e:/t_log_001.sql

导出单个数据表结构和数据
语法：mysqldump -h localhost -uroot -p123456 database table > dump.sql
代码：mysqldump -h localhost -uroot -p123456 db_xiaoli t_log > d:/t_log_002.sql

导出整个数据库结构（不包含数据）
语法：mysqldump -h localhost -uroot -p123456 -d database > dump.sql
代码：mysqldump -h localhost -uroot -p123456 -d db_xiaoli > e:/t_log_002.sql

导出单个数据表结构（不包含数据）
语法：mysqldump -h localhost -uroot -p123456 -d database table > dump.sql
代码：mysqldump -h localhost -uroot -p123456 -d db_xiaoli t_log > e:/t_log_003.sql

视图

含义：虚拟表，和普通表一样使用
mysql5.1版本出现的新特性，是通过表动态生成的数据

比如：舞蹈班和普通班级的对比
创建语法的关键字 是否实际占用物理空间 使用

视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改

表 create table 保存了数据 增删改查

1.查询姓名中包含a字符的员工名、部门名和工种信息

①创建

CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM t_mysql_employees e
JOIN t_mysql_departments d ON e.department_id  = d.department_id
JOIN t_mysql_jobs j ON j.job_id  = e.job_id;

②使用

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

二、视图的修改
方式一：create or replace view 视图名as
查询语句：

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

SELECT * FROM myv3 

方式二：
语法：alter view 视图名as
查询语句：

ALTER VIEW myv3
AS
SELECT * FROM t_mysql_employees
GROUP BY salary;

SELECT * FROM myv3 

三、删除视图
语法：drop view 视图名,视图名,…;
DROP VIEW myv1;

四、查看视图

DESC myv3;

SHOW CREATE VIEW myv3;

五、视图的更新

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
FROM t_mysql_employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM t_mysql_employees;

SELECT * FROM myv1;
SELECT * FROM t_mysql_employees;

1.插入

INSERT INTO myv1 VALUES('张飞','[e-mail]');

2.修改

UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';

3.删除

DELETE FROM myv1 WHERE last_name = '张无忌';

具备以下特点的视图不允许更新

①包含以下关键字的sql语句：分组函数、distinct、group by、having、union或者union all。