MySQL的深入学习

1.SQL的约束

SQL 有五个基本约束条件

约束
NOT NULL	数据默认不为NULL
UNIQUE	列中数据唯一
PRIMARY KEY	主键唯一
FOREIGN KEY	指向另一张表的 PRIMARY KEY
CHECK	数据范围

Example:

创建一个名为EXAM 的表，其中 AUTO_INCREMENT 必须为PRIMARY KEY.

CREATE TABLE EXAM(
	ID INT NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL,
	COMPUTER INT NOT NULL,
	ENGLISH INT NOT NULL,
	PRIMARY KEY (ID),
	CHECK (ENGLISH > 0)
);

2.列置空

UPDATE tablename SET column_name='' where ...  ;

3.AUTO_INCREMENT 字段

AUTO_INCREMENT 字段 （当向表中增加或删除行时，自动编号）

 CREATE TABLE tablename( segment type NOT NULL AUTO_INCREMENT, PRIMARY KEY (segment) );

当删除了中间行时，自动编号依然不会变化，但若使用 UPDATE 将新插入行的 segment 设置为被删除行的编号时，
系统会将新插入行插入到之前被删除行的位置。

4.获取表结构

DESCRIBLE 与 EXPLAIN 使用方法相似

SHOW CREATE TABLE table_name;

DESCRIBLE table_name;

EXPLAIN SELECT column_name1 ... FROM table_name WHERE condition;

将一个表的数据复制到另一个表（两表结构相同）

	INSERT INTO BACK_UP SELECT * FROM TEMPLATE;

5.获取服务器元数据

	(1)  SELECT VERSION();  获取MySQL 版本信息
	(2)  SELECT DATABASE();  获取此用户下所有数据库
	(3)  SELECT USER();  查看当前用户名
	(4)  SHOW STATUS;  查看VARIABLES 
	(5)  SHOW VARIABLES();  获取系统全局变量

6.系统数据库 mysql

每个数据库都有一个系统数据库——mysql，其中有多个表如下：

其中user 表中存放用户，密码和相关权限。

7.mysql 的权限

具体结构在user 表中

管理权限	FILE,GRANT,PROCESS,RELOAD,SHUTDOWN
授权表	user,db,host,tables_priv,columns_priv 等
数据库和表权限	user,db,host,tables_priv,columns_priv 等ALTER,CREATE,DELETE,DROP,INDEX,DEFERENCE,SELECT,UPDATE

8.MySQL 登录（两阶段）

1.服务器从 user 表中查找匹配项，若匹配进行第二阶段 ;
2.服务器匹配用户的权限 ;

登录查询验证

当一个查询请求提出时，服务器检查是否有足够权限执行相关操作，以user,db,tables_priv,columns_priv 的顺序检查

host 表不受 GRANT,REVOKE 影响，因此一般可省略 host 检查， 但 host 的检查在 user 检查之后 ;

9.重命名

ALTER TABLE OLD_NAME RENAME TO NEW_NAME;  // 表重命名

ALTER DATABASE OLD_NAME RENAME TO NEW_NAME;  // 数据库重命名

10.数据库备份

1. 数据库中备份，通过 BACKUP 命令
2. 通过终端命令进行备份

mysqldump -u root -p db_name > db_file_name.sql ;

3. 数据库恢复

mysqladmin -u root -p create db_name ;		//创建新数据库
mysql -u root -p db_name < db_file_name.sql	//将备份导入

11.添加新本地用户

可通过两种办法添加新mysql用户( root 权限 )

GRANT	GRANT ALL ON *.* TO user_name IDENTIFIED BY “pass_word” ;
REVOKE	REVOKE ALL ON *.* TO user_name;
INSERT	INSERT INTO user VALUES (“host_”,“user_name”,PASSWORD(“password”), …)
DELETE	DELETE FROM user WHERE USER = “user_name”

注意： INSERT 插入不会加密密码，而GRANT 会自动加密，因此使用 INSERT 创建用户时需要手动使用 PASSWORD() 加密。

12.myisamchk 检查和修复数据库

使用myissamchk 时，必须是唯一访问，否则容易导致服务器的崩溃。

其次试图修复被破坏的文件时提前对文件进行备份，确保备份之前MySQL 服务器是关闭，否则可能导致数据的不一致 ;

myisamchk --recover --quick path_to_tbname	快速修复
myisamchk --recover path_to_tbname	普通修复
myisamchk --safe-recover path_to_tbname	具体修复

执行速度依次递减，执行效率依次提升 ;

13.MySQL 文件类型

*.cnf	配置文件
*.MYI	表格式文件
*.frm	表结构文件
*.MYD	表数据文件

14.MySQL 性能优化（默认参数优化）

主要常用性能参数

back_log	back_log值指出在MySQL暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。
interactive_timeout	服务器在关闭它前在一个交互连接上等待行动的秒数
key_buffer_size	key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引
max_connections	允许的同时客户的数量
record_buffer	每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区，修改这个缓冲区大小
sort_buffer	每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速ORDER BY或GROUP BY操作。
table_cache	为所有线程打开表的数量。增加该值能增加mysqld要求的文件描述符的数量
thread_cache_size	可以复用的保存在中的线程的数量，如果有很多新的线程，为了提高性能可以这个变量值。
wait_timeout	服务器在关闭它之前在一个连接上等待行动的秒数。

性能优化能超过操作系统的规定最大值，否则将会失效，参数可以通过 MySQL 的配置文件来修改 ;

15.中文排序错误解决办法

MySQL 默认字符集一般是ISO-8859 将中文字符定义为二进制形式，	例如：name varchar(20)binary
编译MySQL 时使用 --with-charset =gbk	–with-charset =gbk

16.索引

提高表的查找效率，可通过设置相关索引来优化

索引缺点及实现

MySQL 中索引的实现为B-树

索引占用内存空间，大量索引对空间消耗大
索引会降低 DELETE ,UPDATE,INSERT 操作的速度，执行操作时不只要将数据写入数据文件，还要写入索引文件

17.mysqld 守护进程

/var/run/mysqld 	查看mysql 的守护进程 ，需root权限

18.MySQL 优化

普通优化

调整服务器设置 （ 修改配置文件 my.cnf）
编译时优化MySQL 1 使用特定编译器 2 将MySQL 编译成静态文件执行 3 配置样本
改变硬件配置，安装更多内存，使用快速I/O ，等
SELECT 操作频繁时使用查询缓存形式
使用MyISAM 键缓存

管理员级优化

尽可能将数据保存在内存中
保留索引中的信息
增加服务器缓存大小

查询优化

使用相同类型查询，相同数据类型比不同数据类型查询效率要高
最小匹配项原则（在WHERE … AND … 会按在表中匹配项最少的优先，再从之中取满足另一限制的匹配项）	SELECT * FROM table_name WHERE condition_1 AND condition_2 系统会根据condition 在表中匹配项的最少来选择最优查找方案
正则表达式不会被优化
MySQL 自动类型转换	SELECT * FROM table_name WHERE column_int = ‘1’ ，此时会将’1’ 转换为 INT 类型若column_int 是索引的，换来的是查询效率的降低，和索引的无法使用
有些子查询可以使用联结达到相同目的，且效率更高	将子查询改写为 join 模式，提高查询效率

优化MySQL 默认调度策略 （实现，锁机制）

写入操作优先级高于读操作
同一时刻只能有一个写操作被响应，写入执行顺序由请求次序决定
支持多个读操作的执行
调度策略可以根据语句调节符来设置 LOW_PRIORITY 等系统调度调节符

改变语句调度的优先级

LOW_PRIORITY和HIGH_PRIORITY调节符影响那些使用数据表锁的存储引擎（例如MyISAM和MEMORY）。DELAYED调节符作用于MyISAM和MEMORY数据表。

LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE
HIGH_PRIORITY关键字应用于SELECT和INSERT语句
DELAYED关键字应用于INSERT和REPLACE语句

使用延迟插入操作

DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候，服务器把数据行放入一个队列中，并立即给客户端返回一个状态信息，这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据，队列中的数据就会被保持着，直到没有读取者为止。接着服务器开始插入延迟数据行（delayed-row）队列中的数据行。在插入操作的同时，服务器还要检查是否有新的读取请求到达和等待。如果有，延迟数据行队列就被挂起，允许读取者继续操作。当没有读取者的时候，服务器再次开始插入延迟的数据行。这个过程一直进行，直到队列空了为止。

优化数据类型与效率

尽可能使用短数据类型
对于 MyISAM 固定数据类型处理速度比可变类型快 , CHAR 快于 VARCHAERMEMORY 将VARCHAR 和 CHAR 都当做 CHAR 类型处理 BDB 差别不大InnoDB 没有区分 CHAR 和 VARCHAR ，但主要性能影响是存储总量，因此 VARCHAR 性能更好一些
使用 SELECT * FROM table_name PROCEDURE ANALYSE(), 查看优化建议
加密函数 MD5(),SHA1(),CRC32()
对于BLOB 和 TEXT 类型，执行操作时会花费大量时间和资源，为此可将内容加密，添加索引，根据散列值来快速进行操作
批量操作比单行载入的效率高，INSERT INTO table_name VALUES (),(),… 比 单行 INSERT 效率高
对于MyISAM ，若将数据载入新表，为提高效率应将表中存在的索引取消，建立新表后重新建立相关索引

19.锁机制

锁层次越细微，并发性越优

锁的层次有 MyISAM 和 MEMORY 表层次 ， InnoDB 行层次， BDB 页面层次
表层次的锁不会发生死锁，InnoDB 和 BDB 易发生死锁，InnoDB 和 BDB 是在事务开始之后，根据请求分配相应的锁

总结

有些地方可能有错误，不完善，会后续持续改进。