学习预览：
（一）mysql 运维基础篇（Linux云计算从入门到精通）
（三）mysql 触发器、存储过程和函数（数据库运维基础补充）
（五）mysql数据备份—物理备份(完备+lvm快照+xtrabackup)+逻辑备份(mysqldump+导入导出)
（六）mysql复制技术—M-S主从配置（传统+GTID）+M-M-S-S主从配置（GTID）
（七）mysql中间件mycat配置和部署（基于M-M-S-S）
（八） 数据库集群技术—Galera Cluster安装与配置
（九）数据库集群技术Galera+mycat（数据库运维学习终章）
CONTEND
一、MySQL索引
先介绍下mysql中索引的定义、功能和分类，然后介绍如何创建索引，修改索引，查看和测试索引，删除索引。
1.1 索引简介
索引在MySQL中也叫做“键” ，也可理解为key，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
可以这样理解，索引相当于字典的音序表,如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。索引能够轻易将查询性能提高好几个数量级，简单来说就是为提高我们的查询数据的速度。
但是也有不好的地方，建立索引也会占用一定的资源，例如建立索引之后在插入数据就会慢许多，所以我们一般在数据全部插入之后在建立索引。或者你已经建立索引了后又要批量插入数据呢，建议先删除索引然后插入数据，最后再建立索引（我说的这种情况一般在海量数据插入的情况下哦，如果数据不是很多，那就不讲究啦）。
1.2 索引的分类
普通索引，唯一索引、全文索引、单列索引、多列索引、空间索引。
我们一般建立普通索引就可以了，具体要求具体对待。
1.3 创建索引
（1）创建表时建立索引
语法:CREATE TABLE 表名( 字段名1 数据类型 [完整性约束条件..], 字段名2 数据类型 [完整性约束条件..], [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [索引名] (字段名[(长度)] [ASC | DESC]) );示例：
--创建普通索引示例: CREATE TABLE department10 ( dept_id INT, dept_name VARCHAR(30)， comment VARCHAR(50), INDEX index_dept_name (dept_name) ); --创建唯一索引示例: CREATE TABLE department11 ( dept_id INT, dept_name VARCHAR(30)， comment VARCHAR(50), UNIQUE INDEX index_dept_name (dept_name) ); --创建全文索引示例: CREATE TABLE department12 ( dept_id INT, dept_name VARCHAR(30)， comment VARCHAR(50), log text, FULLTEXT INDEX index_log (log) );
（2）创建表之后建立索引
语法:
CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名
ON 表名 | (字段名[(长度)] [ ASC | DESC ]);
示例：
--1、创建普通索引示例:
CREATE INDEX index_dept_name ON department (dept_name);
--2、创建唯一索引示例:
CREATE UNIQUE INDEX index_dept_name ON department (dept_name);
--3、创建全文索引示例:
CREATE FULLTEXT INDEX index_dept_name ON department (dept_name);
--4、创建多列索引示例:
CREATE INDEX index_dept_name_comment ON department (dept_name,comment);Alter也可以用来修改index，但create就够了，我就不就少alter咯。
1.4 管理索引
查看索引：
show create table 表名\G测试示例：
explain select * from department where dept_name='HR';删除索引：
show create table employee; --首先查看创建表的结构，知道了索引名才可以删除哦
drop index 索引名 on 表名;下面我们做个小测试来理解创建索引前后查询的区别：
use company #进入company数据库
create table t1(id int name ,varchar(50)); #创建一个t1表
delimiter $$ --定义分隔符方便后面创建存储过程
#下面这就是创建存储过程，实现批量插入数据，这个后面我会写博客介绍的，在这里就是实现批量插入数据的功能
create procedure autoinsert()
begin
declare i int default 1;
while(i<200000)do
insert into company.t1 values(i,'ggg');
set i=i+1;
end while;
end$$
delimiter ; #把分隔符修改回来
call autoinsert(); #执行这个函数，开始插入数据desc t1; --先查看下表的结构，现在是没有索引的
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
select * from t1 where id =100000; --注意观察查询时间，0.11s
+--------+------+
| id | name |
+--------+------+
| 100000 | ggg |
+--------+------+
1 row in set (0.11 sec)
--用explain查看测试下，竟然查了200242行！没有使用index
explain select * from t1 where id =190000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200242
filtered: 10.00
Extra: Using where
--开始建立索引进行比对
create index index_id on t1(id);
desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int | YES | MUL | NULL | |
| name | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
--查询时间为0！
select * from t1 where id =100000;
+--------+------+
| id | name |
+--------+------+
| 100000 | ggg |
+--------+------+
1 row in set (0.00 sec)
--再测试下，它使用了我们建立的索引查询，仅用了一行就查到了
explain select * from t1 where id =190000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: index_id
key: index_id
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
二、mysql视图view
2.1 视图简介
MySQL视图是一个虚拟表，其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。对其中所引用的基础表来说, MySQL视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的SQL查询语句,它主要出于两种原因：安全原因,视图可以隐藏一些数据,如：一些敏感的信息,另一原因是可以使复杂的查询易于理解和使用。
2.2 创建视图
语法一:
CREATE [ALGORITHM = {UNDEFINED |MERGE | TEMPTABLE} ]
VIEW 视图名[(字段1,字段2..)]
AS SELECT语句
[WITH [ CASCADED | LOCAL ] CHECK OPTION ];
语法二:
CREATE VIEW 视图名 AS SELECT语句;
（1）一般我们都是选择第二种语法，比较简单。下面看个单表的创建视图示例：
create view mysql_user
-> as select user,host,authentication_string from mysql.user;
select * from mysql_user; --就可以直接查看视图，不用敲那么长的语句了
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | $A$005$M 1#_\Jr5v(ji?qEIdRVMpdu8z0CQEQ2m9OAyJh5Z.uSnn2sJW4xnzTiis0 |
+------------------+-----------+-----------------------------------------------------------（2）多表创建视图案例
我们先创建两张表并插入数据，然后创建视图。
create table product( id int unsigned auto_increment primary key, name varchar(30) ) not null, price double not null);
create table sale( id int unsigned auto_increment primary key, name varchar(30) not null, quantity int not null default 0, get_time datetime not null);
insert into product(name,price) values ('apple',10), ('banana',2.5), ('peach',4);
insert into sale(name,quantity,get_time) values
-> ('apple',100,now()),
-> ('banana',150,now()),
-> ('peach',200,now());
先用select语句查询总价格，但是每次查询很麻烦，所以创建一个视图方便查询。
select product.id,product.name,price*quantity as 'total_price' from sale,product where product.name=sale.name;
+----+--------+-------------+
| id | name | total_price |
+----+--------+-------------+
| 1 | apple | 1000 |
| 2 | banana | 375 |
| 3 | peach | 800 |
+----+--------+-------------+
--创建视图
create view total_price as select product.id,product.name,price*quantity as 'total_price' from sale,product where product.name=sale.name;
select * from total_price;
+----+--------+-------------+
| id | name | total_price |
+----+--------+-------------+
| 1 | apple | 1000 |
| 2 | banana | 375 |
| 3 | peach | 800 |
+----+--------+-------------+
如果两张表插入数据时，视图也将发生变化：
insert into product(name,price) values ('orange',5);
insert into sale(name,quantity,get_time) values ('orange',120,now());
select * from total_price;
+----+--------+-------------+
| id | name | total_price |
+----+--------+-------------+
| 1 | apple | 1000 |
| 2 | banana | 375 |
| 3 | peach | 800 |
| 4 | orange | 600 |
+----+--------+-------------+
2.3 视图管理（查看，修改，删除）
（1）查看视图
1.SHOW TABLES; --查看视图名
2.SHOW TABLE STATUS;
--示例:查看数据库mysq|中视图及所有表详细信息
SHOW TABLE STATUS FROM mysql\G
--示例:查看数据库shop中视图名total_price的详细信息
SHOW TABLE STATUS FROM shop LIKE 'total_price'\G
3.SHOW CREATE VIEW
--示例:查看视图定义信息
SHOW CREATE VIEW total_price\G
4.DESCRIBE
--示例:查看视图结构
DESC total_price;（2）修改视图
--方法一:删除后新创建
DROP VIEW view_user;
CREATE VIEW view_user AS SELECT user,host FROM mysql.user;
SELECT * FROM view_user;
--方法二: ALTER修改视图
ALTER VIEW 视图名 AS SELECT语句;
ALTER VIEW view_user AS SELECT user,password FROM mysql.user;
（3）删除视图
--语法
DROP VIEW view_name [,view_name]..;
--示例
USE mysql;
DROP VIEW view_user;
2291
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
