MySQL基础简介与操作

MySQL基础简介与操作

安装MySQL：
1、yum 安装根据官网https://dev.mysql.com/doc/mysql-yum-repo-quick-guide/en/提示安装mysql
2、https://dev.mysql.com/downloads/mysql/获取mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

#useradd –r mysql创建系统用户，用于运行mysql进程
#mkdir –pv /data/mysql 用于存放数据文件的目录， 
#chown mysql.mysql /data/mysql 变更属主属组为mysql用户
#tar xf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -C /usr/local/ 解压
# ln -sv mysql-5.7.18-linux-glibc2.5-x86_64 mysql 创建软连接
#mkdir etc 配置文件目录，
#mkidr logs 日志存放目录

Logs目录需要mysql为属主属组，其他文件设置为roor属主，mysql属组
新建/etc/profile.d/mysql.sh:
编辑export PATH=/usr/local/mysql/bin:$PATH导入环境变量，. /etc/profile.d/mysql.sh载入变量

# mysqld  --initilize-insecure --basedir=/usr/local/mysql --datadir=/data/mysql --user=mysql

修改配置文件：/usr/local/mysql/etc/my.cnf
			datadir
			socket
			includedir
			pid
准备错误日志文件（属主属组为mysql）：
可使用my_print_defaults命令查看配置文件加载顺序，同一参数，最后读取为最终生效值

#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld 复制启动脚本
#chkconfig --add mysqld 添加systemd管理
#systemctl start mysqld 启动服务

安装完成后的安全初始化：
	mysql_secure_installation
运行前常修改的参数()：在配置文件中/etc/my.cnf中[mysqld]添加如下：
	innodb_file_per_table=ON# 使用单独表空间文件
	skip_name_resolve=ON# 跳过解析，登mysql时默认解析主机名=

基础命令：
客户端命令：于客户端执行；
服务端命令：SQL语句，需要一次性完整地发往服务端；语句必须有结束符
MySQL [options]：
	常用选项：
		--host=host_name, -h host_name：服务端地址；
		--user=user_name, -u user_name：用户名；
		--password[=password], -p[password]：用户密码； 
		--port=port_num, -P port_num：服务端端口； 
		--protocol={TCP|SOCKET|PIPE|MEMORY}：
		本地通信：基于本地回环地址进行请求，将基于本地通信协议；
			Linux：SOCKET
			Windows：PIPE，MEMORY
		非本地通信：使用非本地回环地址进行的请求； TCP协议；
		--socket=path, -S path
		--database=db_name, -D db_name：
		--compress, -C：数据压缩传输
		--execute=statement, -e statement：非交互模式执行SQL语句；
		--vertical, -E：查询结果纵向显示；
	mysql命令的使用帮助：
		# man mysql
		# mysql  --help  --verbose
sql脚本运行：
mysql [options] [DATABASE] < /PATH/FROM/SOME_SQL_SCRIPT
mysqld服务器程序：工作特性的定义
命令行选项：
		mysql> SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
	服务器参数/变量：设定MySQL的运行特性；
		mysql> SHOW GLOBAL|[SESSION] VARIABLES [LIKE clause];
	状态（统计）参数/变量：保存MySQL运行中的统计数据或状态数据；
		mysql> SHOW GLOBA|[SESSION] STATUS [LIKE clause];
	显示单个变量设定值的方法：
		mysql> SELECT @@[global.|session.]system_var_name
			%：匹配任意长度的任意字符；
			_：匹配任意单个字符；
	变量/参数级别：
			全局：为所有会话设定默认； 
			会话：跟单个会话相关；会话建立会从全局继承；
	服务器变量的调整方式：
			运行时修改：
				global：仅对修改后新建立的会话有效；
				session：仅对当前会话有效，且立即生效；
			启动前通过配置文件修改：
				重启后生效；
			运行时修改变量值操作方法：
				mysql> HELP SET
				SET [GLOBAL | SESSION] system_var_name = expr
				SET [@@global. | @@session. | @@]system_var_name = expr
	注：GLOBAL值的修改要求用户拥有管理权限；
MySQL的数据类型：
字符型：
	CHAR(#)， BINARY(#)：定长型；CHAR不区分字符大小写，而BINARY区分；
	VARCHAR(#)， VARBINARY(#)：变长型
	TEXT：TINYTEXT，TEXT，MEDIUMTEXT，LONGTEXT
	BLOB：TINYBLOB，BLOB，MEDIUMBLOB， LONGBLOB
数值型：
	浮点型：近似
		FLOAT
		DOUBLE
		REAL
		BIT
整型：精确
		INTEGER：TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT
		DECIMAL
日期时间型：
		日期：DATE
		时间：TIME
		日期时间：DATETIME
		时间戳：TIMESTAMP
		年份：YEAR(2), YEAR(4)
内建：
		ENUM：枚举
			ENUM('Sun','Mon','Tue','Wed')
		SET：集合
类型修饰符：
		字符型：NOT NULL，NULL，DEFALUT ‘STRING’，CHARACET SET ‘CHARSET’，COLLATION ‘collocation'
		整型：NOT NULL， NULL， DEFALUT value, AUTO_INCREMENT, UNSIGNED
		日期时间型：NOT NULL， NULL， DEFAULT
SQL MODE：定义mysqld对约束等违反时的响应行为等设定；
常用的MODE：
			TRADITIONAL
			STRICT_TRANS_TABLES
			STRICT_ALL_TABLES
修改方式：
			mysql> SET GLOBAL sql_mode='MODE';
			mysql> SET @@global.sql_mode='MODE';
查看当前模式：
			select @@global.sql_mode;

库，表操作：
数据库：
	CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name CHARACTER SET [=] charset_name  COLLATE [=] collation_name
	ALTER {DATABASE | SCHEMA} [db_name] CHARACTER SET [=] charset_name  COLLATE [=] collation_name
	DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

表：
CREATE 
 (1) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
					(create_definition,...)
					[table_options]
					[partition_options]
	CREATE TABLE [IF NOT EXISTS] tble_name (col_name  data_typ|INDEX|CONSTRAINT);
	table_options：
		ENGINE [=] engine_name
			查看支持的所有存储引擎：
			mysql> SHOW ENGINES;
			查看指定表的存储引擎：
			mysql> SHOW TABLE STATUS LIKE clause;
	ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
					
 (2) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
					[(create_definition,...)]
					[table_options]
					[partition_options]
					select_statement
直接创建表，并将查询语句的结果插入到新创建的表中；
 (3) CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
			{ LIKE old_tbl_name | (LIKE old_tbl_name) }
复制某存在的表的结构来创建新的空表；
DROP:
DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name]; 
ALTER：
ALTER  TABLE tbl_name
				 [alter_specification [, alter_specification] ...]
	可修改内容：
		 (1) table_options
		 (2) 添加定义：ADD
				字段、字段集合、索引、约束
		 (3) 修改字段：
				CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
				MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]
		 (4) 删除操作：DROP
				字段、索引、约束
	表重命名：
			RENAME [TO|AS] new_tbl_name
查看表结构定义：
	DESC tbl_name；
查看表定义：
	SHOW CREATE TABLE tbl_name
查看表属性信息：
	SHOW TABLE STATUS [{FROM | IN} db_name] [LIKE 'pattern' | WHERE expr]

索引：
	创建：
		CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [index_type] ON tbl_name (index_col_name,...)
	查看：
		SHOW {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
	删除：
		DROP  INDEX index_name ON tbl_name
索引类型：
		聚集索引、非聚集索引：索引是否与数据存在一起；
		主键索引、辅助索引
		稠密索引、稀疏索引：是否索引了每一个数据项；
		BTREE（B+）、HASH、R Tree、FULLTEXT
					BTREE：左前缀；
		EXPLAIN：分析查询语句的执行路径；
视图：VIEW
		虚表：存储下来的SELECT语句；
		创建：
			CREATE  VIEW view_name [(column_list)] AS select_statement
		修改：
			ALTER  VIEW view_name [(column_list)] AS select_statement
		删除：
			DROP VIEW [IF EXISTS] view_name [, view_name] ...
INSERT：
单行插入，	批量插入
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE][INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE
col_name=expr
 [, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
 [ ON DUPLICATE KEY UPDATE
col_name=expr
 [, col_name=expr] ... ]
Or:
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [(col_name,...)]
SELECT ...
 [ ON DUPLICATE KEY UPDATE
col_name=expr
 [, col_name=expr] ... ]
DELETE： 
DELETE  FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count]	
注意：一定要有限制条件，否则将清空整个表；
	限制条件：
		 [WHERE where_condition]
		 [ORDER BY ...] [LIMIT row_count]
					 
UPDATE：
	UPDATE table_reference SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
		 [WHERE where_condition]
		 [ORDER BY ...]
		 [LIMIT row_count]				
注意：一定要有限制条件，否则将修改整个表中指定字段的数据；
		限制条件：
			 [WHERE where_condition]
			 [ORDER BY ...] [LIMIT row_count]
注意：sql_safe_updates变量可阻止不带条件更新操作；
SELECT：
Query Cache：缓存查询的执行结果；
	key：查询语句的hash值； 
	value：查询语句的执行结果；
SQL语句的编写方式：
	SELECT name FROM tbl2;
	select name from tbl2;
查询执行路径：
	请求-->查询缓存
	请求-->查询缓存-->解析器-->预处理器-->优化器-->查询执行引擎-->存储引擎-->缓存-->响应
SELECT语句的执行流程：
FROM  --> WHERE --> Group By --> Having --> Order BY --> SELECT --> Limit 
单表查询：
SELECT
	 [ALL | DISTINCT | DISTINCTROW ]
	 [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
	 select_expr [, select_expr ...]
	 [FROM table_references
	 [WHERE where_condition]
	 [GROUP BY {col_name | expr | position}
	 [ASC | DESC], ... [WITH ROLLUP]]
	 [HAVING where_condition]
	 [ORDER BY {col_name | expr | position}
	 [ASC | DESC], ...]
	 [LIMIT {[offset,] row_count | row_count OFFSET offset}]	
用法：
	SELECT col1, col2, ... FROM tble_name；   极其危险，慎用；
	SELECT col1, col2, ... FROM tble_name WHERE clause；
	SELECT col1, col2, ... FROM tble_name  [WHERE clause] GROUP BY col_name [HAVING clause]； 
DISTINCT：数据去重；
SQL_CACHE：显式指定缓存查询语句的结果；
SQL_NO_CACHE：显式指定不缓存查询语句的结果；
query_cache_type服务器变量有三个值：
	ON：启用； 
		SQL_NO_CACHE：不缓存；默认符合缓存条件都缓存；
	OFF：关闭；
		DEMAND：按需缓存；
		SQL_CACHE：缓存；默认不缓存；
字段可以使用别名 ：
		col1 AS alias1, col2 AS alias2, ...
WHERE子句：指明过滤条件以实现“选择”功能；
		过滤条件：布尔型表达式；
	[WHERE where_condition]
		算术操作符：+, -, *, /, %
		比较操作符：=, <>, !=, <=>, >, >=, <, <=
			IS NULL， IS NOT NULL
			区间：BETWEEN min AND max 
			IN：列表；匹配列表；select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
			LIKE：模糊比较，%和_；
			RLIKE或REGEXP
		逻辑操作符：
			AND， OR， NOT
		GROUP BY：根据指定的字段把查询的结果进行“分组”以用于“聚合”运算；
			avg(), max(), min(), sum(), count()
			HAVING：对分组聚合后的结果进行条件过滤；
		ORDER BY：根据指定的字段把查询的结果进行排序；
			升序：ASC
			降序：DESC 
		LIMIT：对输出结果进行数量限制
			 [LIMIT {[offset,] row_count | row_count OFFSET offset}]
多表查询：
连接操作：
	交叉连接：笛卡尔乘积；
内连接：
	等值连接：让表之间的字段以等值的方式建立连接；
	不等值连接：
	自然连接
	自连接
	外连接：
		左外连接：
			FROM tb1 LEFT JOIN tb2 ON tb1.col = tb2.col 
		右外连接：
			FROM tb1 RIGHT JOIN tb2 ON tb1.col = tb2.col 
	子查询：在查询中嵌套查询；
	用于WHERE子句中的子查询；
			 (1) 用于比较表达式中的子查询：子查询仅能返回单个值； 
			 (2) 用于IN中的子查询：子查询可以返回一个列表值； 
			 (3) 用于EXISTS中的子查询：
	用于FROM子句中的子查询；
		SELECT tb_alias.col1, ... FROM (SELECT clause) AS tb_alias WHERE clause; 
	联合查询：将多个查询语句的执行结果相合并；
		UNION 
			SELECT clause UNION SELECT cluase；

练习：导入hellodb.sql生成数据库
 (1) 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄；

SELECT Name,Age FROM students WHERE Age > 25 AND Gender='M';

 (2) 以ClassID为分组依据，显示每组的平均年龄；

SELECT ClassID,avg(age) FROM students GROUP BY ClassID; 

 (3) 显示第2题中平均年龄大于30的分组及平均年龄；

SELECT ClassID,avg(age) AS Aging FROM students GROUP BY ClassID HAVING Aging>30;

 (4) 显示以L开头的名字的同学的信息；

SELECT * FROM students WHERE Name LIKE 'L%';

 (5) 显示TeacherID非空的同学的相关信息；

SELECT * FROM students WHERE TeacherID IS NOT NULL;

 (6) 以年龄排序后，显示年龄最大的前10位同学的信息；

SELECT * FROM students ORDER BY Age DESC LIMIT 10;

 (7) 查询年龄大于等于20岁，小于等于25岁的同学的信息；用三种方法；

select * from students where age > 20 and age < 25;	
SELECT * from students WHERE age IN (20,21,22,23,24,25);
SELECT * FROM students WHERE age BETWEEN 20 AND 25;

练习2：导入hellodb.sql，以下操作在students表上执行
1、以ClassID分组，显示每班的同学的人数；

SELECT ClassID,count(StuID) FROM students GROUP BY ClassID;

2、以Gender分组，显示其年龄之和；

SELECT Gender,SUM(Age) FROM students GROUP BY Gender; 

3、以ClassID分组，显示其平均年龄大于25的班级；

SELECT ClassID,avg(age) FROM students GROUP BY ClassID HAVING avg(Age) > 25; 

4、以Gender分组，显示各组中年龄大于25的学员的年龄之和；

select gender,sum(age) from students group by gender having sum(age) > 25;

练习3：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩；

select distinct name,courseid,score from students,scores where students.stuid = scores.stuid limit 5;

2、显示其成绩高于80的同学的名称及课程；

select distinct name,courseid,score from students,scores where students.stuid = scores.stuid and score >  80;

3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；

SELECT Name,avg(Score) FROM (SELECT * FROM students limit 8) as b1 LEFT JOIN scores as b2 ON b1.stuid=b2.stuid GROUP BY Name ORDER BY avg(Score) DESC;

4、显示每门课程课程名称及学习了这门课的同学的个数；

SELECT courses.course,count(b1.stuid) FROM scores AS b1 LEFT JOIN courses ON courses.courseid=b1.courseid GROUP BY b1.courseid;

思考：
1、如何显示其年龄大于平均年龄的同学的名字？

SELECT Name,Age FROM students WHERE Age > (SELECT avg(Age) FROM students);

2、如何显示其学习的课程为第1、2，4或第7门课的同学的名字？

SELECT b1.Name,scores.CourseID FROM students AS b1 LEFT JOIN scores ON scores.StuID = b1.StuID WHERE scores.CourseID IN (1,2,4,7);

3、如何显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学？

SELECT students.name,students.age,tp.classid,tp.vg FROM students,(SELECT classid,COUNT(stuid)  AS cs,AVG(age) AS vg FROM students GROUP BY classid HAVING cs >=3) AS tp WHERE students.age>tp.vg AND students.classid=tp.classid;

4、统计各班级中年龄大于全校同学平均年龄的同学。

SELECT rj.Name,rj.Age FROM students AS rj LEFT JOIN classes AS jr ON rj.ClassID=jr.ClassID WHERE rj.ClassID=jr.ClassID AND Age > (SELECT AVG(Age) FROM students);

存储引擎：
表类型：也称为“表类型”，表级别概念，不建议在同一个库中的表上使用不同的ENGINE；
CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...
SHOW TABLE STATUS 
常见的存储引擎：
MyISAM, Aria, InnoDB, MRG_MYISAM, CSV, BLACKHOLE, MEMORY, PERFORMANCE_SCHEMA, ARCHIVE, FEDERATED

InnoDB：InnoBase
事务型存储引擎，适合对事务要求较高的场景中；但较适用于处理大量短期事务；
基于MVCC（Mutli Version Concurrency Control）支持高并发；支持四个隔离级别，默认级别为REPEATABLE-READ；间隙锁以防止幻读；
使用聚集索引（主键索引）；
支持”自适应Hash索引“；
锁粒度：行级锁；间隙锁；
数据存储于“表空间(table space)"中：
 (1) 所有数据库中的所有类型为InnoDB的表的数据和索引存储于同一个表空间中；
	表空间文件：datadir定义的目录中
			文件：ibdata1, ibdata2, ...
 (2) innodb_file_per_table=ON，意味着每表使用单独的表空间文件；
	每表的数据文件（数据和索引，存储于数据库目录）存储于自己专用的表空间文件中，并存储于数据库目录下: tbl_name.ibd 
	表结构的定义：在数据库目录，tbl_name.frm 
			
总结：
	数据存储：表空间；
	并发：MVCC，间隙锁，行级锁；
	索引：聚集索引、辅助索引；
	性能：预读操作、内存数据缓冲、内存索引缓存、自适应Hash索引、插入操作缓存区；
	备份：支持热备；
		引擎状态：SHOW ENGINE INNODB STATUS;

MyISAM：
	支持全文索引（FULLTEXT index）、压缩、空间函数（GIS）；
	不支持事务
	锁粒度：表级锁
	崩溃无法保证表安全恢复
	适用场景：只读或读多写少的场景、较小的表（以保证崩溃后恢复的时间较短）；
	文件：每个表有三个文件，存储于数据库目录中
			tbl_name.frm：表格式定义；
			tbl_name.MYD：数据文件； 
			tbl_name.MYI：索引文件；
	特性：
			加锁和并发：表级锁；
			修复：手动或自动修复、但可能会丢失数据；
			索引：非聚集索引；
			延迟索引更新；
			表压缩；
其它的存储引擎：
	CSV：将CSV文件（以逗号分隔字段的文本文件）作为MySQL表文件； 
	MRG_MYISAM：将多个MyISAM表合并成的虚拟表；
	BLACKHOLE：类似于/dev/null，不真正存储数据；
	MEMORY：内存存储引擎，支持hash索引，表级锁，常用于临时表；
	FEDERATED: 用于访问其它远程MySQL服务器上表的存储引擎接口；

并发控制：
锁：Lock 
	锁类型 ：
	读锁：共享锁，可被多个读操作共享，只能读；
	写锁：排它锁，独占锁；
锁粒度：
	表锁：在表级别施加锁，并发性较低；
	行锁：在行级别施加锁，并发性较高；维持锁状态的成本较大；
锁策略：在锁粒度及数据安全性之间寻求一种平衡机制；
	存储引擎：级别以及何时施加或释放锁由存储引擎自行决定；
	MySQL Server：表级别，可自行决定，也允许显式请求； 
锁类别：
	显式锁：用户手动请求的锁；
	隐式锁：存储引擎自行根据需要施加的锁；
显式锁的使用：
	 (1) LOCK TABLES 
		LOCK TABLES  tbl_name  read|write, tbl_name read|write, ...
		UNLOCK TABLES
	 (2) FLUSH TABLES
		FLUSH TABLES tbl_name,... [WITH READ LOCK];
		UNLOCK TABLES;
	 (3) SELECT cluase
		[FOR UPDATE | LOCK IN SHARE MODE]

事务：
事务：一组原子性的SQL查询、或者是一个或多个SQL语句组成的独立工作单元；
事务日志：
	innodb_log_files_in_group
	innodb_log_group_home_dir
	innodb_log_file_size	
	innodb_mirrored_log_groups
ACID测试：
A：AUTOMICITY，原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚；
C：CONSISTENCY，一致性；数据库总是应该从一个一致性状态转为另一个一致性状态； 
I：ISOLATION，隔离性；一个事务所做出的操作在提交之前，是否能为其它事务可见；出于保证并发操作之目的，隔离有多种级别； 
D：DURABILITY，持久性；事务一旦提交，其所做出的修改会永久保存；
自动提交：单语句事务
	mysql> SELECT @@autocommit; 查看
	mysql> SET @@session.autocommit=0；设置
手动控制事务：
	启动：START TRANSACTION 
	提交：COMMIT
	回滚：ROLLBACK
事务支持savepoints：
	SAVEPOINT identifier
	ROLLBACK [WORK] TO [SAVEPOINT] identifier
	RELEASE SAVEPOINT identifier				
事务隔离级别：
	READ-UNCOMMITTED：读未提交 --> 脏读；
	READ-COMMITTED：读提交--> 不可重复读；
	REPEATABLE-READ：可重复读 --> 幻读；
	SERIALIZABLE：串行化；
	mysql> SELECT @@session.tx_isolation; 查看当前事务类型


用户与权限：
创建用户：可以一次创建多个用户，用逗号分隔CREATE USER  'user'@'host' [IDENTIFIED BY [PASSWORD] 'password']
	create user stu44@localhost identitied by 'stu44';
修改密码：
 1、set password for stu44@localhost = password('123456');
 2、update mysql.user set authentication_string=password('abcd') where user='stu44' and host='localhost';
MYSQL5.7密码存在authentication_string字段
重命名：RENAME USER old_user TO new_user
	rename user stu44@localhost to smartwy@localhost;
删除用户： DROP USER 'user'@'host'
	drop user smartwy@localhost;
重新加载授权表： FLUSH PRIVILEGES
用户账号：user@host
		user：账户名称；
		host：此账户可通过哪些客户端主机请求创建连接线程； 
			%：任意长度牟任意字符；
			_：任意单个字符；

授权：
	grant select on hellodb.* to stu44@localhost;
	stu44用户可对hellodb库下的所有表拥有查询权限
查看授权：show grants for stu44@localhost;
取消授权：revoke select on hellodb.* from stu44@localhost;

索引：
索引的作用：加速查询操作；表中数据子集：把表中某个或某些字段的数据提取出来另存为一个特定数据结构组织的数据；
某个字段或某些字段：WHERE子句中用到的字段；
索引类型： B+tree、hash
B+tree ：顺序存储，每个叶子节点到根节点的距离相等，左前缀索引，适合与范围类型的数据查询
	全值匹配：where cloumu = ‘value’
	匹配最左前缀：起头部分，where column like ‘wang%’
	匹配范围值：精确匹配某一列，范围匹配另一列
	只用访问索引的查询：覆盖索引；
		index(Name)
		SELECT Name FROM students WHERE Name LIKE 'L%';
	如查条件不是从最左侧列开始，索引无效；
		index(age,name), WHERE name='Jerry';    , WHERE age>30;
	不能跳过索引中的某列；
		index(name,age,gender)
	WHERE name='black' and age > 30;
		如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；

Hash：基于hesh表实现，适合用于精确匹配
	适用于等值比较查询，如：= 、in()、
索引优点：
		降低需要扫描的数据量，减少了IO次数；
		可以帮助避免排序操作，避免使用临时表； 
		帮助将随机IO转为顺序IO；
高性能索引策略：
		(1) 在WHERE中独立使用列，尽量避免其参与运算；
			WHERE age > 30 ; 
		(2) 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
			索引选择性：不重复的索引值和数据表的记录总数的比值；
		(3) 多列索引：
			AND连接的多个查询条件更适合使用多列索引，而非多个单键索引；
		(4) 选择合适的索引列次序：选择性最高的放左侧；
EXPLAIN分析索引有效性：
	EXPLAIN+含有索引的查询语句

Id：当前语句中第几个select语句的编号，联合查询会出现一个匿名零时表
Select_type：查询类型：SIMPLE、SUBQUERY、DERIVED、PRIMARY、UNION、UNION RESULT。
Table：被查询的表
Type：关联/访问类型
	ALL：全表扫描；
	index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
	range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
	ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
	eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；
	const，system：与某个常数比较，且只返回一行；
possiable_keys：查询中可能会用到的索引；
key：查询中使用的索引；
key_len：查询中用到的索引长度；
ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值； 
rows：MySQL估计出的为找到所有的目标项而需要读取的行数；
Extra：额外信息
	Using index：使用了覆盖索引进行的查询；
	Using where：拿到数据后还要再次进行过滤； 
	Using temporary：使用了临时表以完成查询；
	Using filesort：对结果使用了一个外部索引排序；
创建索引create index stuid on students(stuid);
查看索引show index from students;
删除索引drop index stuid on students;

日志：在配置文件中配置生效
查询日志；general_log
慢查询日志：log_slow_queries
错误日志：log_error,log_warnings
二进制日志：binlog
中继日志：relay_log
事务日志：innodb_log

1、查询日志：general_log
记录查询语句：一般不建议开启
	general_log={ON|OFF}
	general_log_file=HOSTNAME.log 
	log_output={FILE|TABLE|NONE}
2、慢查询日志：log_slow_queries
	运行时间超过10s的查询，默认10s
	查看：show global variables like 'long_query_time';
	设置：set @@global.long_query_time=20;
	开启慢日志：在/etc/my.cnf中的mysqld中添加如下：
	slow_query_log=ON #开启
	slow_query_log_file=/data/mysql/mysql_slow_query.log #指定日志文件路径
	long_query_time=10 #超时时间
	log_queries_not_using_indexes=ON 记录没有使用索引的慢查询
3、错误日志：log_error,log_warnings
记录信息：
		 (1) mysqld启动和关闭过程 输出的信息； 
		 (2) mysqld运行中产生的错误信息； 
		 (3) event scheduler运行时产生的信息；
		 (4) 主从复制架构中，从服务器复制线程启动时产生的日志；
	log_error=/path/to/file.log
	log_warnings={ON|OFF}
4、二进制日志：binlog
	用于记录引起数据改变或存在引起数据改变的潜在可能性的语句（STATEMENT）或改变后的结果（ROW），也可能是二者（MIXED）混合,一般用于恢复最近一次备份至当前时间段的数据
	Binlog_format= {STATEMENT|ROW|MIXED}
	查看二进制日志文件列表：
	SHOW MASTER|BINARY LOGS;
	查看当前正在使用的二进制日志文件：
	SHOW MASTER STATUS；
	查看二进制 日志文件中的事件：
	SHOW BINLOG EVENTS	 [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
服务器变量：
	log_bin=/PATH/TO/BIN_LOG_FILE
	sql_log_bin={ON|OFF}
	max_binlog_size=1073741824
	sync_binlog={1|0}
mysqlbinlog：
	--start-datetime=#查询的起始时间
	--stop-datetime=#查询的结束时间
	-j, --start-position=#查询的其实位置
	--stop-position=#查询的结束位置
	--user, --host, --password
Exp：mysqlbinlog mysql-bin.000003 -j 22920 --stop-position=23102#03的二进制日志的地址始22920至23102止
二进制日志事件格式：
	# at 553
	#160831  9:56:08 server id 1  end_log_pos 624   Query   thread_id=2     exec_time=0     error_code=0
	SET TIMESTAMP=1472608568/*!*/;
	BEGIN
	/*!*/;
	at :事件的起始位置
	事件发生的日期时间：#160831  9:56:08
	事件发生的服务器id：server id 1
	事件的结束位置：end_log_pos 624
	事件的类型：Query
	事件发生时所在服务器执行此事件的线程的ID： thread_id=2 
	语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
	错误代码：error_code=0
	事件内容：SET TIMESTAMP=1472608568/*!*/;
5、中继日志：relay_log
	从服务器上记录下来从主服务器的二进制日志文件同步过来的事件；
6、事务日志：innodb_log
	事务型存储引擎innodb用于保证事务特性的日志文件：redo log,nudu log

缓存：
缓存：K/V，key：查询语句的hash值，value：查询语句的执行结果
判断缓存是否命中：根据查询语句的哈希值判断，查询本身，查询数据库，协议版本
一般不会被缓存：查询语句中的UDF，存储函数，自定义变量，临时表...
缓存变量：show global variables like 'query_cache%';
query_cache_limit：能够缓存的最大查询结果；（单语句结果集大小上限）
有着较大结果集的语句，显式使用SQL_NO_CACHE，以避免先缓存再移出； 
query_cache_min_res_unit：内存块的最小分配单位；
缓存过小的查询结果集会浪费内存空间；较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；较大值的会带来空间浪费；
query_cache_size：查询缓存空间的总共可用的大小；
单位是字节，必须是1024的整数倍；
set @@global.query_cache_size=16*1024*1024;设置缓存大小为16兆
query_cache_type：缓存功能启用与否；
	ON：启用；
	OFF：禁用；
	DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；
query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为OFF，表示可以；ON则表示不可以；
状态变量：show global status like 'qcache_%';
	Qcache_free_blocks：剩余的块
	Qcache_free_memory：剩余的内存大小
	Qcache_hits： 缓存命中数
	Qcache_inserts：插入命令次数
	Qcache_lowmem_prunes：因为内存不足而被清除出缓存
	Qcache_not_cached： 没有缓存的项数
	Qcache_queries_in_cache：缓存中有多少缓存项
	Qcache_total_blocks：当前缓存的块数量
缓存命中率：Qcache_hits/Com_select
show global status like 'com_select';

备份与恢复：
	备份：存储数据的副本
	恢复：把副本应用到线上系统
	时间点恢复：使用二进制日志binary logs：
备份注意事项：
	可以容忍最多丢失多少数据
	恢复数据的时间
	恢复哪些数据
备份后需做测试备份的可用性，增强恢复的操作效率
备份类型：
	备份的数据集范围：
		完全备份：整个数据集；
		部分备份：数据集的一部分，比如部分表；
全量备份、增量备份、差异备份：
	完全备份
	增量备份：仅备份自上一次完全备份或 增量备份以来变量的那部数据；
	差异备份：仅备份自上一次完全备份以来变量的那部数据；
物理备份、逻辑备份：
	物理备份：复制数据文件进行备份；
	逻辑备份：从数据库导出数据另存在一个或多个文件中；
根据数据服务是否在线：
	热备：读写操作均可进行的状态下所做的备份；
	温备：可读但不可写状态下进行的备份；
	冷备：读写操作均不可进行的状态下所做的备份；
备份需要考虑因素：
	锁定资源多长时间？
	备份过程的时长？
	备份时的服务器负载？
	恢复过程的时长？
备份内容：数据、二进制日志、InnoDB的事务日志、代码（存储过程、存储函数、触发器、事件调度器）、服务器的配置文件
备份策略：
	完全+差异+binlog
	完全+增量+binlog

备份工具：
mysqldump：mysql服务自带的备份工具；逻辑备份工具；
	完全、部分备份；
	InnoDB：热备；
	MyISAM：温备；
cp/tar
	lvm2：快照（请求一个全局锁），之后立即释放锁，达到几乎热备的效果；物理备份；
	注意：不能仅备份数据文件；要同时备份事务日志；
	前提：要求数据文件和事务日志位于同一个逻辑卷；
xtrabackup：
	由Percona提供，开源工具，支持对InnoDB做热备，物理备份工具；
		完全备份、部分备份；
		完全备份、增量备份；
		完全备份、差异备份；
mysqlhotcopy
	select：
		备份：SELECT cluase INTO OUTFILE 'FILENAME';
		恢复：CREATE TABLE 
		导入：LOAD DATA
Mysqldump: 支持逻辑备份、完全备份、部分备份；
使用：
	mysqldump [OPTIONS] database [tables]#备份库中表
	mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]#备份库
	mysqldump [OPTIONS] --all-databases [OPTIONS]#备份全部库

MyISAM存储引擎：支持温备，备份时要锁定表；
	-x, --lock-all-tables：锁定所有库的所有表，读锁；
	-l, --lock-tables：锁定指定库所有表；

InnoDB存储引擎：支持温备和热备；
	-x、-l
	--single-transaction：创建一个事务，基于此快照执行备份；
其它选项：
	-R, --routines：存储过程和存储函数；
	--triggers 
	-E, --events      
	--master-data[=1/2]
		1：记录为CHANGE MASTER TO语句，此语句不被注释；
		2：记录为CHANGE MASTER TO语句，此语句被注释；
	--flush-logs：锁定表完成后，即进行日志刷新操作；此日志可作为时间点恢复数据，(此次备份至故障点时间的数据恢复)

备份数据：
备份hellodb库内所有表到hellodb当前日期.sql恢复时hellodb库必须要存在，因为在备份文件内没有建库指令

mysqldump -uroot -p hellodb > hellodb-`date +"%F"`.sql

锁定所有库的所有表,执行完全备份至allmysql-`date +"%F"`.sql文件内

mysqldump -uroot -p -x --all-databases > allmysql-`date +"%F"`.sql

建一个事务，基于此快照执行完全备份至allmysql-`date +"%F"`.sql文件内同时滚动binlog

mysqldump -uroot -p --single-transaction –flush-logs --all-databases > allmysql-`date +"%F"`.sql

恢复数据
完全备份恢复数据：

mysql -uroot –p  < /PATH/TO/allmysql-`date +"%F"`.sql

或进入mysql 输入：

\. /PATH/TO/ allmysql-`date +"%F"`.sql

时间点恢复数据：mysql-bin.000014是备份完的二进制日志文件

Mysqlbinlog mysql-bin.000014 | mysql –uroot –p 

或进入mysql 输入（mysqlbinlog mysql-bin.000014 > binlog.sql）：

\. /PATH/TO/ binlog.sql

如果备份时没有使用—flush-logs参数，恢复时间点时可使用以下参数
mysqlbinlog	--start-datetime=
			 --stop-datetime=
			 -j, --start-position=#
			 --stop-position=#
来指定恢复的时间段或指定事件的位置