Mysql知识点梳理

Mysql:
@[TOC]一、基础知识：
DDL语句：
创建用户：

 CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

		删除用户：

 DROP USER '用户名'@'主机名';

		创建数据库:

create database database_name;

		删除数据库：

drop database database_name;

创建表：

		create table table_name(
				colume_name type constraints,
			)engine=InnoDB default charset=gbk;

删除表：

drop table table_name;

			修改表：
				1、修改表类型：
					alter table table_name modify colume_name column_definition;
				2、增加表字段：
					alter table table_name add column colume_name column_definition;
				3、删除表字段：
					alter table table_name drop column colume_name;
				4、字段改名：
					alter table table_name change old_col_name new_col_name column_definition
				5、修改字段排序顺序：
					alter table table_name add colume_name [first|after] colume_name;
				6、表改名:
					alter table table_name rename table_name;
				
		DML语句：
			插入：
				insert into table_name (列名...) values(值...);
			更新：
				update table_name set 字段=值，... [where 子句][order by 子句][limit 子句]
			删除：
				delete from table_name [where 子句][order by 子句][limit 子句]	

		DQL语句：
			查询：
				select * from table_name [where 子句][group by 分组子句][having 子句][order by 子句][limit 子句] ;
			show ...
		DCL语句：
			清空表：
				truncate table_name;
			授权：
				GRANT 权限 1, 权限 2... ON 数据库名.表名 TO '用户名'@'主机名';
			撤销权限：
				REVOKE 权限 1, 权限 2... ON 数据库.表名 revoke all on test.* from 'user1'@'localhost'; '用户名'@'主机名';
			修改管理员密码:
				mysqladmin -uroot -p password 新密码
			修改普通用户密码:
				set password for '用户名'@'主机名' = password('新密码');
		
		总结：当不需要该表时，用 DROP；当仍要保留该表，但要删除所有记录时，用 TRUNCATE；当要删除部分记录时，用 DELETE。
		
		事务：
			begin;或 start transition
				...
			commit；
			rollback 回滚
		存储过程语法：
			1.基本语法：
				create procedure sp_name()
				begin
				.........
				end
				
				1.1.条件语句
					if 条件 then
					 statement
					 else
					 statement
					 end if;
				 1.2.循环语句
					(1).while循环
					[label:] WHILE expression DO
					 statements
					 END WHILE [label] ;

					(2).loop循环
					

				[label:] LOOP
				 statements
				 END LOOP [label];

					 
					
(3).repeat until循环

[label:] REPEAT
statements
UNTIL expression
END REPEAT [label] ;

			2.调用存储过程：call sp_name()
				注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递
			3.删除存储过程:drop procedure sp_name
				注意事项:不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
							
		游标：游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，
			游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
			示例：
				

delimiter //
create procedure p1()
begin
declare id int;
declare name varchar(100) character set utf8;
declare done int default 0;
– 声明游标
declare mc cursor for select stuId,stuName from student where stuAge >19;
declare continue handler for not found set done = 1;
– 打开游标
open mc;
– 获取结果
fetch mc into id,name;
– 这里是为了显示获取结果
select id,name;
– 关闭游标
close mc;
end //
delimiter ;

@[TOC]二、高级进阶
	@[TOC]存储引擎：
		myisam：
			高速查询及插入。擅长插入和查询。
		innodb：
			innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。
	@[TOC]索引：
		主键索引：
			要求关键字不能重复，也不能为NULL。同时增加主键约束
		唯一索引：
			要求关键字不能重复。同时增加唯一约束。
		普通索引：
			对关键字没有要求。
		组合索引：
			多列组合形成的索引，遵循最左前缀原则、最左匹配原则
		全文索引：
			关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
	注:
		聚簇索引：
			是按照存储方式构造的索引，每张表只能拥有一个聚簇索引。
		非聚簇索引：
			辅助索引（二级索引），即查找数据时需要先找到数据对应的聚簇索引，再通过聚簇索引完成数据查找，该过程也称为回表。
		
		索引覆盖：
			索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。
			覆盖就是直接在索引区获取内容，而不需要在数据区获取。
			
		@[TOC]索引存储结构：
			B+树索引：
				B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且同层级的节点间有指针相互链接，是有序的。
			哈希索引：
				哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，
				只需一次哈希算法即可,是无序的。
				优势：等值查询，哈希索引具有绝对优势（前提是：没有大量重复键值，如果大量重复键值时，
					哈希索引的效率很低，因为存在所谓的哈希碰撞问题。）
				劣势：
					1、不支持范围查询
					2、不支持索引完成排序
					3、不支持联合索引的最左前缀匹配规则。
			B树和B+树的区别:
				1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。
				2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字
					的大小自小而大的顺序链接所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）
					关键字。 (而B 树的非终节点也包含需要查找的有效信息)。
			B+比B树更适合实际应用中操作系统的文件索引和数据库索引:	
				1、B+的磁盘读写代价更低。
					B+的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小。
					如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。
					一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

				2、B+-tree的查询效率更加稳定。
					由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须
					走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。
		@[TOC]索引策略：
			1、覆盖索引
			2、最左前缀原则
			3、索引下推
				可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
	@[TOC]事务隔离级别及未隔离下的问题：
		1、读未提交（存在脏读问题）
		2、读提交（存在不可重复读问题）
		3、重复读（存在幻读问题）（Mysql数据库的默认隔离级别）
		4、串行化（最高隔离级别，但并发低）
		
	@[TOC]Mysql中的锁：
		一、全局锁
			即对整个数据库实例加读锁，命令：Flush tables with read lock
			缺点：
				1、如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆
				2、如果在从库上备份，那么在备份期间从库不能执行主库同步过来的binlog，会导致主从延迟
			
			使用set global readonly=true方式使全库只读存在的问题：
				1、在有些系统中，readonly被使用来做主库与从库的判别，因此修改global影响会更大。
				2、如果执行Flush tables with read lock命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，
					整个库回到可以正常更新的状态。使用global方式则不会，可能会导致整个库长时间处于只读状态，风险性较高。
		二、表级锁
			1、表锁
				加锁语法：lock tables … read/write。
				释放锁：unlock tables主动释放锁，或者在客户端断开后自动释放。
				注：读锁之间不互斥，因此可以有多个线程同时对一张表增删改查。
					读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。
			2、MDL（元数据锁）
				MDL锁不需要显示使用，在访问表时会被自动加上，访问完成后自动释放。
				注：事务中的MDL锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
				2.1、安全的给小表加字段
					在给小表安全加字段或修改时，就需要考虑常事务存在的问题，在MySQL的information_schema库
					的innodb_trx表中可以查到当前执行的事务。如果要做DDL变更的表刚好有长事务在执行，要考虑先暂停DDL，
					或者kill掉这个长事务。
				2.2、对热点表加字段
					在alter table语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到MDL写锁最好，
					拿不到也不要阻塞后面的业务语句，先放弃。之后再通过重试命令重复这个过程。
		三、行锁
			查询加读锁：select * from table lock in share mode
			查询加写锁：select * from table for update
			增删改自动加写锁
		间隙锁：
			在锁定自身数据的同时，也锁住邻近的数据。和行锁合称next-key lock
MVVC（多版本并发控制）:
	MVVC下的CRUD
	SELECT：
	　　当隔离级别是REPEATABLE READ时select操作，InnoDB必须每行数据来保证它符合两个条件：

		InnoDB必须找到一个行的版本，它至少要和事务的版本一样老(也即它的版本号不大于事务的版本号)。
	这保证了不管是事务开始之前，或者事务创建时，或者修改了这行数据的时候，这行数据是存在的。
		这行数据的删除版本必须是未定义的或者比事务版本要大。这可以保证在事务开始之前这行数据没有被删除。
		符合这两个条件的行可能会被当作查询结果而返回。

	INSERT：InnoDB为这个新行记录当前的系统版本号。
	DELETE：InnoDB将当前的系统版本号设置为这一行的删除ID。
	UPDATE：InnoDB会写一个这行数据的新拷贝，这个拷贝的版本为当前的系统版本号。它同时也会将这个版本号写到旧行的删除版本里。
		这种额外的记录所带来的结果就是对于大多数查询来说根本就不需要获得一个锁。只是简单地以最快的速度来读取数据，
		确保只选择符合条件的行。这个方案的缺点在于存储引擎必须为每一行存储更多的数据，做更多的检查工作，处理更多的善后操作。

		MVCC只工作在REPEATABLE READ和READ COMMITED隔离级别下。READ UNCOMMITED不是MVCC兼容的，因为查询不能找到适合
	他们事务版本的行版本；它们每次都只能读到最新的版本。SERIABLABLE也不与MVCC兼容，因为读操作会锁定他们返回的每一行数据。

	当前读：
		读取的是最新版本；
		UPDATE、DELETE、INSERT、SELECT …  LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。
	快照读：
		读取的是快照版本，也就是历史版本
		简单的select操作(当然不包括 select … lock in share mode, select … for update)
		
			查询执行速度慢的原因分析：
				1、查询长时间不返回
					1.1 等MDL锁
					1.2、等flush
					1.3 等行锁
				2、第二类：查询慢：事务一致性读引起的根据undolog日志执行回滚操作，导致查询慢

@[TOC]解决幻读问题：
	：在快照读情况下，mysql通过mvcc来避免幻读。
	：在当前读情况下，mysql通过X锁或next-key来避免其他事务修改:
		1、使用串行化读的隔离级别
		2、(update、delete)当where条件为主键时，通过对主键索引加record locks(索引加锁/行锁)处理幻读。
		3、(update、delete)当where条件为非主键索引时，通过next-key锁处理。next-key是record locks(索引加锁/行锁) 
			和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)的结合。
			
@[TOC]数据库优化：
	1、设计上，可考虑查询需要进行逆范式设计，将高频查询的数据库通过冗余的方式放在同一个表中，减少查询时表的关联。
	2、设计时存储引擎的选择，依照存储引擎的特性，根据业务性质，选择合适的数据存储引擎。
	3、sql优化:
		3.1 对于多表联合查询的，尽可能拆分为多个少表或单表查询，提升查询速度。
		3.2 大量数据的插入,建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。
		3.3 对于慢查询，通过日志定位。
			

show variables like ‘slow_query%’; 
			show variables like ‘%long_query%’;

	

开启日志
		

		set global slow_query_log=1; 
		set long_query_time=0.5；

	4、索引优化，正确建立索引，并合理利用索引。
		索引创建原则：
			1、最左前缀匹配原则
			2、频繁作为查询条件的字段才去创建索引
			3、频繁更新的字段不适合创建索引
			4、索引列不能参与计算，不能有函数操作
			5、优先考虑扩展索引，而不是新建索引，避免不必要的索引
			6、在order by或者group by子句中，创建索引需要注意顺序
			7、区分度低的数据列不适合做索引列(如性别）
			8、定义有外键的数据列一定要建立索引。
			9、对于定义为text、image数据类型的列不要建立索引。
			10、删除不再使用或者很少使用的索引
	5、分库分表
		
	6、表分区
		1、分区算法
			（1）KEY; 取余 ，按照某个字段进行取余
				PARTITION by key(分区列) PARTITIONS
			（2）Hash; 取余，按照某个表达式的值进行取余
				PARTITION by hash(month(分区列)) PARTITIONS
			（3）List ; 条件; 列表，需要指定的每个分区数据的存储条件。
					PARTITION by list(month(分区列)) (
					PARTITION chun VALUES in< (3,4,5)
					PARTITION xia VALUES < (6,7,8)
					PARTITION qiu VALUES < (9,10,11)
					PARTITION dong VALUES < (12,1,2)
				)
			（4）Range - 条件 &ndash; 范围， 条件依赖的数据是一个条件表达式。
				PARTITION by range(year(分区列)) (
					PARTITION xx VALUES < (1980)
					PARTITION xx VALUES < (1990)
					PARTITION xx VALUES < MAXVALUE
				)
			注意：Key，hash都是取余算法，要求分区参数（括号里的），返回的数据必须为整数。
			
		2.分区的管理与选择
			（1）取余：key，hash
				增加分区数量： add partition partitions N
				减少分区数量： COALESCE partition N	
			（2）条件：list，range
				添加分区：add partition（
					PARTITION chun VALUES in< (3,4,5)
				）
				删除分区：Drop partition partition_name;
			3）选择分区算法
				平均分配：就按照主键进行key(primary key)即可(非常常见)
				按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型
					
	7、读写分离
		主从复制，Mysql具备该能力，只需配置好主从服务即可使用
	8、使用缓存机制（数据库一级缓存和二级缓存、nosql如redis等缓存服务器）