“视图优化、索引策略和数据导入导出技巧“

CREATE VIEW <视图名> AS <SELECT语句>
• 1

-- 1.视图
select * from t_mysql_student;
select * from t_mysql_score;
select * from t_mysql_course;
select * from t_mysql_teacher;
-- 直接创建视图报错，因为有重复的列段
CREATE view v_student_score as
select
  *
from
t_mysql_student s,
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t
where 
s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid
-- 去重重复列段
CREATE view v_student_score as
select
  s.*,sc.cid,sc.score,c.cname,t.*
from
t_mysql_student s,
t_mysql_score sc,
t_mysql_course c,
t_mysql_teacher t
where 
s.sid = sc.sid
and sc.cid = c.cid
and c.tid = t.tid;
-- 视图依然可以行转列
SELECT * from v_student_score;
SELECT
  sid,sname,
  sum(if(cid='01',score,0)) 语文,
  sum(if(cid='02',score,0)) 数学,
  sum(if(cid='03',score,0)) 英语
FROM
  v_student_score
GROUP BY
sid,sname
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44

create index 索引名称 on table(索引字段)；
• 1

ALTER TABLE table_name ADD INDEX index_name ON (column(length))
• 1

DROP INDEX index_name ON table
• 1

CREATE UNIQUE INDEX indexName ON table(column(length))
• 1

CREATE TABLE mytable( 
  ID INT NOT NULL, 
  username VARCHAR(16) NOT NULL, 
  PRIMARY KEY(ID) 
);
• 1
• 2
• 3
• 4
• 5

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);
• 1

CREATE TABLE t_articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT,
  FULLTEXT (title,body)
);
• 1
• 2
• 3
• 4
• 5
• 6

ALTER TABLE t_articles ADD FULLTEXT INDEX fulltext_article (title, body);
• 1

INSERT INTO t_articles VALUES
(NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
(NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
(NULL,'Optimising MySQL','In this tutorial we will show ...'),
(NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
(NULL,'MySQL Security', 'When configured properly, MySQL ...');
INSERT INTO t_articles VALUES
(NULL,'abc', 'zs red blue ...'),
(NULL,'xyz', 'ls white');
INSERT INTO t_articles VALUES
(NULL,'aaa', 'zs red blue ...'),
(NULL,'bbb', 'ls white red');
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17

SELECT * FROM t_articles where MATCH (title,body) AGAINST ('database')
• 1

#包含red或者blue
SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red blue' IN BOOLEAN MODE );
#包含red，但是必须包含blue
SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red +blue' IN BOOLEAN MODE );
#包含red，但是一定不能包含blue
SELECT * FROM t_articles where MATCH (title,body) AGAINST ('red -blue' IN BOOLEAN MODE );
• 1
• 2
• 3
• 4
• 5
• 6

select version();
• 1

show engines;
• 1

[mysqld]
ft_min_word_len = 2
• 1
• 2

ALTER TABLE t_book
ADD FULLTEXT INDEX fulltext_bookname_type (bookname, booktype) WITH PARSER ngram;
• 1
• 2

SELECT * FROM t_book where MATCH (bookname, booktype) AGAINST ('三国')
• 1

--  2.索引分类
CREATE TABLE `t_log` (
  `id` varchar(32) NOT NULL COMMENT '唯一标识',
  `ip` varchar(15) NOT NULL COMMENT 'IP地址',
  `userid` varchar(32) NOT NULL COMMENT '用户ID',
  `moduleid` varchar(32) NOT NULL COMMENT '模块ID',
  `content` varchar(500) NOT NULL COMMENT '日志内容',
  `createdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建日期',
  `url` varchar(100) DEFAULT NULL COMMENT '请求URL地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 --   1) 普通索引：是最基本的索引，它没有任何限制；
 -- 0.762s
select * from t_log;
-- 建索引前 0.12s
select * from t_log where moduleid = '10040199';
-- 创建索引所花费的时间：1.593s
Create index idx_moduleid on t_log(moduleid);
-- 建索引前 0.001s
select * from t_log where moduleid = '10040199';
-- 可以查看走过的索引
EXPLAIN select * from t_log where moduleid = '10040199';
--    2) 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一；
-- Duplicate entry '/quartz/queryJobLst' for key 'idx_url'  有重复列段
create UNIQUE index idx_url on t_log(url);
drop index idx_url on t_log;
 --   3) 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值；
 -- 主键索引所花费的时间：0s
select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';
EXPLAIN select * from t_log where id = '07489cdafd6d4a3489884cd3c00c7b27';
--    4) 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合；
-- 花费的时间：3.959s
create index idx_userid_moduleid_url on t_log(userid,moduleid,url);
-- 走组合索引
EXPLAIN select * from t_log where userid = '' and moduleid = '' and url = '';
EXPLAIN select * from t_log where userid = '' and moduleid = '';
EXPLAIN select * from t_log where userid = '' ;
EXPLAIN select * from t_log where userid = '' and url = '';
-- 不走组合索引
EXPLAIN select * from t_log where moduleid = '';
EXPLAIN select * from t_log where url = '';
EXPLAIN select * from t_log where moduleid = '' and url = '';
--    4.创建索引
--    CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名[(长度)][ASC|DESC])
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• 34
• 35
• 36
• 37
• 38
• 39
• 40
• 41
• 42
• 43
• 44

-- DELETE/TRUNCATE
-- 花费的时间：10.597s
DELETE FROM t_log;
-- 花费的时间：0.121s
TRUNCATE TABLE t_log;
-- 1.使用工具，类似Sqlyog、Navicat等导入导出数据。
-- 使用Navicat工具导入 t_log 共耗时 45s；
-- 使用mysqldump导入 包含t_log表的整个数据库 共耗时 20s；
-- 使用单表数据导入load data infile 的方式，共耗时 7.502s
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11

--   2.1 导出
--     2.1.1 导出表数据和表结构
--     mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql(这个名字随便叫)
     D:\SoftwareInstallPath\mysql-8.0.13-winx64\bin>mysqldump -uroot -p123456 mybatis_ssm > 1234567.sql
--     2.1.2 只导出表结构
    -- mysqldump -u用户名 -p密码 -d 数据库名 > 数据库名.sql
    # mysqldump -uroot -p -d abc > abc.sql 
    -- 注：导出的数据在mysql的bin目录下
--  2.2 导入
--    注意：首先建立空数据库
--    mysql>create database abc;
--    2.2.1 方法一
--    mysql>use abc;                   #选择数据库
--    mysql>set names utf8;            #设置数据库编码
--    mysql>source /D:/SoftwareInstallPath/mysql-8.0.13-winx64/bin/1234567.sql;  #导入数据 
--    2.2.2 方法二
--    mysql -u用户名 -p密码 数据库名 < 数据库名.sql
      #mysql -uabc_f -p abc < abc.sql
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22

--   可先通过SELECT INTO OUTFILE方式，将数据导出到Mysql的C:\ProgramData\MySQL\MySQL Server 5.5\data目录下，再通过LOAD DATA INFILE方式导入。 
--  1) select * from 表名 into outfile '/文件名.sql';
--  2) load data infile '/文件名.sql' into table 表名(列名1,...); 
--  这时候就可以在 mysql.ini 文件的 [mysqld] 代码下增加 secure_file_priv=E:/TEST 再重启 mysql 就可以了。然后在导出的地址下面写上刚才配置的这个地址 eg: select * from tb_test into outfile "E:/TEST/test.txt"；就可以了。
select * from t_log into outfile 'E:/12345678.sql';
load data infile 'E:/12345678.sql' into table t_log(id,ip,userid,moduleid,content,createdate,url); 
show variables like 'secure%'
desc t_log;
select * FROM t_log;
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8 
[mysqld]
secure_file_priv=D:
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:/mySQL/mysql-8.0.18-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:/mySQL/mysql-8.0.18-winx64/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18