MySQL知识点

					MySQL	分析引擎结构有索引特征优化数据库

一.MySQL配置(端口,路径,数据日志)

1.MySQL基本的配置参数

	基本配置:

		1)Datadir:指定MySQL的数据目录文件

		2)default-character-set:MySQL服务器默认字符集设置

		3)skip-grant-tables:当忘记MySQL用户密码时,可以在MySQL配置文件中配置该参数,跳过权限表的验证,不需要密码,直接登录MySQL

	日志相关:

		1)log-error:指定日志错误文件的名称

		2)log-bin:指定二进日志文件的名称,用于记录对数据造成更改的所有的查询语句

		3)binlog-do-db:指定的是将更新到二进制日志文件的数据库

		4)sync-binlog:指定多少次写日志后同步磁盘

		5)genral-log:是否开启查询日志记录;值为"1"开启

		6)genral-log-file:指定查询日志文件名,用于记录所有的查询语句

		7)slow-query-log:是否开启慢查询日志记录

		8)slow-query-log-file:指定慢查询日志文件的名称,用于记录消耗时间较长的查询语句

		9)long-query-time:设置慢查询的时间,超过这个时间的查询语句才记录日志

	   10)log-slow-admin-statements:是否将慢查询语句写入慢查询日志;(optimeze table,	analyze table,	alter table)

	都属于慢查询语句

	存储引擎相关:

		1)default-table-type:设置MySQL的默认存储引擎

		2)innodb-data-home-dir:InnoDB引擎的共享表空间数据文件根目录

		3)innodb-data-file-path:单独指定共享表空间数据文件的路径与大小

		4)innodb-file-per-table:是否开启独立空间表

		5)low-priority-updates:在MYISAM引擎锁使用中,默认情况下写请求优先于读请求,可以通过将该参数设置为"1"来使MYISAM引擎给与读

	请求优先权限

		6)max-heap-table-size:设置Memory表最大的空间大小

		7)max-write-lock-count:当一个MYISAM表的写锁定达到这个值后,MYISAM就暂时将写请求优先级降低,给部分读请求获得锁的机会

		8)innodb-lock-wait-timeout:设置innodb锁等待超时参数;若事务在这个时间内没有获得需要的锁,就发生回滚

	查询相关:

		1)max-sort-length:配置对blob或text类型的列进行排序时使用的字节数

		2)max-length-for-short:MySQL有两种排序算法,分别是"两次传输排序"和"单词传输排序".当查询需要所有列的总长度不超过

	max-length-for-short时,MySQL使用"单词传输排序",否则使用"两次传输排序"

	3)optimizer-search-depth:在关联查询中使用;当需要关联的表数量超过optimizer-search-depth时,优化器会用"贪婪"搜索方式找"最优"

的关联顺序

2.MySQL端口配置

二.慢查询日志

1.什么是慢查询日志

	MySQL会记录下查询超过指定时间的语句,我们将超过指定时间的SQL语句称为慢查询.它记录在慢查询日志里;我们开启慢查询日志配置,可以查看

究竟哪些SQL语句在慢查询.

2.如何开启慢日志查询

	默认情况下slow-query-log的值是off,表示慢查询日志是禁用的;可以通过这是slow-query-log值来开启,设置为"1"表示开启,"0"表示关闭

3.设置慢查询日志相关的参数

	1)slow-qyery-log和slow-query-log-file

4.慢查询日志常规的数据分析

三.Explain执行计划

详见day13.txt

四.主从同步(binlog):应用场景

1.主从的形式:

	1)一主一从	2)主主复制	3)一主多从	4)多主一从(MySQL5.7开始支持)	5)级联复制

2.MySQL主从复制用途:

	1)实时灾备,用于故障切换	2)读写分离,提供查询服务	3)备份,避免影响业务

3.主从部署的必要条件:

	1)主库开启binlog日志(设置log-bin参数)		2)主从服务器的id不同		3)从库服务器能联通主库

4.MySQL主从复制的原理

	1.从库生成两个线程,一个I/O线程,一个SQL线程;

		I/O线程去请求主库的binloh日志,并将得到的binlog日志写到中继日志文件中;主库会生成一个log dump线程,用来给从库I/O线程传

	binlog

		SQL线程会读取中继日志文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致

							sql语句分析,实现sql优化

I.MySQL的组成结构和sql的执行顺序

1.MySQL的组成结构:MySQL是由SQL接口,解析器,优化器,缓存,存储引擎组成的.

	SQL Interface:SQL接口,接受用户的SQL命令,并且返回用户需要查询的结果.

	Parser:解析器,SQL命令传递到解析器的时候会被解析器验证和解析

	Optimizer:查询优化器,SQL语句在执行之前会用查询优化器对查询进行优化.他使用的是"选取-投影-连接"策略进行查询.

	例:select uid,name from user where uid = 1;先选取uid=1的,

	cache和buffer:查询缓存,如果查询缓存有命中的查询结果,查询语句就可以直接去查询缓存中取数据,这个缓存机制是由一系列的

小缓存组成的,比如表缓存,记录卡缓存,key缓存,权限缓存等.

	Engine:存储引擎  存储引擎是MySQL中具体的与文件打交道的子系统.也是MySQL最具有特殊的一个地方.

	MySQL的存储引擎是插件式的.他根据MySQL ab公司提供的文件访问层的一个抽象接口定制一种文件访问机制.这种访问机制就叫

存储引擎.

	现在有很多种存储引擎,各个存储引擎的优势各不一样.最常用的MyISAM,InnoDB,BDB。默认下MySQL使用的是MyISAM引擎.

区别:

	1)MyISAM查询速度快,有较好的索引优化和压缩技术,但是它不支持事务.

	2)InnoDB支持事务,并且提供行级锁定,应用也是很广泛的.

2.MySQL的执行顺序

	MySQL的执行顺序是写SQL的核心.

	*如何进行多表查询,优化.

	SQL语句的函数,SQL提供的函数方便了很多操作

1)MySQL的语法顺序:即SQL中存在下面的关键字时,他们要保持这样的顺序:

	select [distinct] 

	from 

	join(inner join,left join,right join)

	on 

	where

	group by

	having

	union

	order by

	limit

2)MySQL的执行顺序:

	即在执行SQL语句时按照下面的顺序进行执行:

		where

		from

		join on

		group by

		having

		select

		distinct

		union

		order by

II:MySQL的日志文件和数据文件

1.MySQL日志文件和数据文件的作用

	MySQL有6中日志文件:*重做日志,*回滚日志,*二进制日志,错误日志,查询日志(慢查询日志,一般查询日志),中继日志

日志文件作用:

	1)重做日志:确保事务的持久性,防止在发生故障的时间点尚有未写入磁盘的,在重启MySQL服务的时候,根据重做日志进行重做,从而达到

事务的持久性这一特点.

	2)回滚日志:保存了事务发生之前的一个版本,同时可以提供多版本并发下的读,也就是非锁定读.

	3)二进制日志:用于复制,在主从复制中,从库利用主库上的二进制日志进行重播,实现主从同步.用于数据库的基于时间点的还原.

数据文件作用:保存数据.

	包含数据文件上的所有索引和索引树

2.MySQL日志文件和数据文件存放目录

	windows下: D:\study\phpstudy\PHPTutorial\MySQL\data\;
	配置文件在目录: D:\study\phpstudy\PHPTutorial\MySQL\my.ini

	Linux下: /var/lib/mysql

III:MySQL表连接(外连接[左外连接,右外连接],内连接(inner join),笛卡尔积,交叉连接)***

1.inner join: 子句是将一个表中的行与其他表中的行进行匹配,并且允许从两个表中查询包含列的行记录.他的子句是select语句的可选

部分,出现在from子句之后.使用inner join子句之前,必须指定以下条件:

首先在from子句中指定主表,其次表中要连接的主表应出现在innher join子句中.理论上说可以连接多个其他的表,但是为了获得更

好的性能,应该限制要连接的表的数量(不超过三个).

连接条件或谓词;连接条件出现在inner join子句的on关键字之后,连接条件是将主表中的行与其他表中的行进行匹配的规则.

使用语法:select uid,name from t1 inner join t2 on t1.uid = t2.id inner join t3 on ... where ...

原理:对于t1表的每一行,inner join子句将它与t2表的每一行进行比较,以检查他们是否都满足连接条件,当满足连接条件时,inner join

将返回由t1和t2表中的列组成的新行.

2.左连接(left join):left join子句允许从匹配的左右表中查询选择行记录,连接左表(t1)中的所有行,即使在右表(t2)

中找不到匹配的行,也显示出来,使用null代替.

工作原理:略

基本语法:select U.name,U.sex from user u left join name n

3.右连接(right join):与左连接相反

4.交叉连接(cross join):corss join子句从连接的表返回的笛卡尔乘积

	假设使用cross join连接两个表,结果集将包两个表中所有的行,其中结果集中的每一行都是第一个表与第二个表中的行的组合.

当连接的表之间没有关系时,会使用这种情况.

	select * from t1 cross join t2

	cross join子句不具有连接条件,没有on条件.如果添加where子句,如果t1和t2有关系,则cross join工作方式与inner join类似.

	select * from t1 cross join t2 where t1.id = t2.id

5.笛卡尔积

	A={0.1}  B={2,3}   A*B= {0,2} {0,3}  {1,2}  {1,3}

	1)两个集合相乘不满足交换律		A*B != B*A

	2)A集合与B集合相乘包含了集合A中的元素和集合B中的元素相结合的所有的可能性.就是两个集合相乘得到的新集合是

		A集合的元素个数  *  B集合的元素个数.

		select * from student join student_subject

		以这条SQL为例,from语句把student表和student_subject表从数据库文件加载到内存中,join语句相当于对两张表做乘法运算,

	把student表中的每一行与student_subject表中的记录依次匹配.

		针对以上理论,我们提出一个问题,难道表连接的时候都要先形成笛卡尔积表吗?如果两张表的数据量较大的话,那样就会占用很大的

	内存空间,这显然是不合理的.所以我们在进行表连接查询的时候,一般都会使用join xxx on xxx的语法,on语句的执行是在join语句

	之前的也就是说两张表数据行之间进行匹配的时候,会先判断数据行是否符合on语句后的条件,在决定是否join.

		因此有一个显而易见的SQL优化的方案:当两张表的数据量比较大又需要连接查询时,应该使用from t1 join t2 on xxx语法,避免

	使用from t1,t2 where xxx的语法.因为后者会在内存中先生成一张数据量比较大的笛卡尔积表,增加了内存的开销.

SQL语句执行流程

	from   -> on  ->  join  -> where ->  group by  ->  select  ->  select  -> having  ->  order by  ->  limit

IV:MySQL表连接的方法以及on,where的作用

where:查询条件		on:内外连接时用的条件		as:别名		in:查询某值是否在某条件里


						DAY	13	MYSQL使用索引技术实现数据库操作优化

一.表结构优化(存储引擎,字段类型,三范式)

1.三范式:数据库设计范式;在设计数据库的时候的一些规范,设计出没有数据冗余和数据维护异常的数据结构

数据库第一范式:数据库表中所有字段都只具有单一属性,单一属性的列是由基本的数据诶性所构成的,设计出来的表都是简单的二维表.

数据库第二范式:要求每个表中只有一个业务主键,也就是说符合第二范式的表中不能存在非主键列对部分主键的依赖关系.

数据库第三范式:指每一个非主属性既不部分依赖于也不传递依赖于业务主键,也就是在第二范式的基础上消除了非主属性对主键的传递

依赖.

理解三大范式:

第一范式:

	1.每一列属性都是不可再分的属性值,确保每一列的原子性

	2.两列的属性相近或相似或一样,尽量合并属性一样的列,确保不产生冗余数据

第二范式:

	1.每一行的数据只能与其中的一列相关,就是一行数据只做一件事,只要数据列中出现数据重复,就把这个表拆开

		订单编号		房间号		联系人		联系人电话		身份证号

									||---[拆分]

	表1:订单编号		房间号		联系人编号

	表2:联系人编号	联系人 	 	联系人电话	身份证号

第三范式:

	1.数据不能存在传递关系,就是每个属性与主键有直接关系而不是间接关系;如:表a和表b有关系,表b和表c有关系,是符合第三范式

的

	student表(学号,姓名,年龄,性别,所再院校,院校地址,院校电话)

				||---[拆分]

	表1:		(学号,姓名,年龄,性别,所在院校)

	表2:		(所在院校,院校地址,院校电话)

作业:1.PHPmailer

 2.sms短信验证做登录

二.存储引擎

1.MYISAM:不支持事务,不支持外键,尤其是访问速度快,对事务完整性没有要求,或者以select insert为主的应用,基本都可以用这个

引擎来创建表.每个MYISAM在磁盘上存储成三个文件,其中文件名和表名都相同,但是扩展名分别为: '.fim’存储表定义 ‘.myd’ 存储数据

'.myi’存储索引;

2.Memory:速度快,把数据放在内存里;致命弱点:当mysql进程崩溃时,memory数据会全部丢失;要求存储在memory表里的数据采用长度

不变的格式[如char];[什么情况下使用:

				1)目标数据比较小,而且被非常频繁的访问

				2)如果数据时临时的,而且要求必须立即可用

				3)如果存在memory数据表中的数据全部丢失,不会对应用服务产生实质的影响

3.BDB(不常用):

4.InnoDB:是一个事务型存储引擎;这种引擎已经被很多互联网公司使用,为用户操作非常大的数据存储,提供了强大的解决方案.

  InnoDB还引入了行级锁定和外键;------需要事务支持,有较高的并发读取频率使用InnoDB

			[在什么情况下使用:

					1)更新密集的表

					2)事务

					3)自动灾难恢复

					4)外键约束

					5)支持自动增加列[auto_increment]
			]

5.merge:是一组MYISAM表的组合,这些MYISAM表的表结构必须完全相同;尽管他的使用不如其他搜索引擎突出,但是在某些情况下非常有

用.可以对merge类型的表进行查询,更新,删除;具体是对merge类型的表中MYISAM表进行查询,更新,删除

		[什么情况下使用:

			1)服务器日志信息(将数据分成多个表,每个数据与时间相关)

		]

6.archive:拥有很好的压缩机制,在记录被请求时,被实时压缩,所以经常被当做仓库来使用.--[归档的意思,归档之后很多高级功能就不支持了.

只支持简单的插入功能.]

三.如何选择合适的存储引擎

1.是否支持事务	2.是否需要使用热备	3.崩溃恢复	4.是否需要外键支持

总结:MYISAM支持并发插入的表级锁,表锁:主要应用于select和insert;忌用:读写操作频繁的

 merge主要用于分段归档,数据仓库;忌用:全局查找过多的场景

 INnoDB支持事务,行锁;几乎所有的场景都可以使用

 achive不支持事务,行锁;主要应用于日志记录,它只支持insert和select;忌用:需要随机读取,更新,删除

四.表结构如何优化

合理的优化数据表结构,可以提网站的执行速度,提高项目的执行效率

	1.可以使用反三范式,适当的添加冗余字段,减少多表查询

	2.使用索引[普通索引,主键索引,唯一索引,全文索引,复合索引]

注意:什么时候创建索引:

	1)比较频繁的作为查询条件的字段

	2)唯一索引太差的字段不适合创建索引

	3)数据更新非常频繁的字段不适合创建索引

存储引擎在什么时候使用:

	MYISAM:表对事务的要求不高,同时又是以查询和添加为主的表

	InnoDB:对事务的要求高,保存的数据都是重要的数据[比如账号表,订单表]

	Memory:数据变化频繁,不需要入库,同时又频繁的查询和修改,插入的数据不会持久化到磁盘空间里(不会入库)

字段类型[详见课课本P117]:

	数值类型:int  tinyint  smallint  mediumint  Bigint  float  double

	字符串类型:Char  varchar  tinyblob  tinytext  blob  text  mediumblob  Mediumtext  longblob  longtext

	日期时间型:Data  time  year  datetime  timestamp

五.索引优化

五种索引应用及优化

	普通索引:加快查询速度,也是我们大多数情况下用到的索引,使用关键字 KEY 或 INDEX 来创建.

		–直接创建索引

			CREATE INDEX index_name ON table(column(length))

		–修改表结构的方式添加索引

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

		–创建表的时候同时创建索引

			CREATE TABLE `table` (
			`id` int(11) NOT NULL AUTO_INCREMENT ,
			`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT 	NULL ,
			`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
			`time` int(10) NULL DEFAULT NULL ,
			 PRIMARY KEY (`id`),
			INDEX index_name (title(length))
			)

		-删除索引

			alter table tableName drop INDEX index_name

	主键索引:加速查询,列值唯一,表中只能有一个;PRIMARY KEY

	唯一索引:加速查询,列值唯一;约束创建或者使用唯一索引的单一列必须是不重复的;使用关键字UNIQUE定义;创建与普通索引相同

	全文索引:对文本的内容进行搜索;使用FULLTEXT关键字定义;对文本进行搜索;把文本中出现的所有文本创建一份清单,根据这个清单去检索

	复合索引:多列的值组成一个索引.又叫最左前缀,必须要保证唯一;适用于排列在前的数据列组合.只从最左面开始组合.

		ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))

索引:是对数据库表中一列或者多列进行排序的结构.使用索引可快速访问数据库表中特定的信息.

六.Explain的基本使用

写在SQL语句的前面,explain对我们优化SQL语句是非常有帮助的,可以通过explain+SQL语句的方式分析当前SQL语句,使用方法:

	在select语句前加上explain		例:explain select * from tableName;

Explain列的解释:

	id:是select的识别符,这是select查询序号

		1)id相同,执行顺序由上到下		2)如果是子查询,id的序号会递增,id的值越大优先级越高,就越先被执行

	select_type:查询中每个select子句的类型

		1)SIMPLE(简单的select,不适用union或子查询等)	

		2)PRIMARY(查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)

		3)UNION:UINON中的第二个或后面的select子句

		4)DEPENDENT UNION(union中的第二个或后面的select子句,取决于外面的查询)

		5)UNION RESULT(union的结果)

		6)SubQuery		7)Dependent SubQuerY

	TABLE:表名,如果有别名则显示别名

	TYPE:访问类型,性能由高到低分别是:system -> const -> eq_ref -> range -> index -> all

	POSSIBLE_KEYS:显示可能应用在这张表中的索引,如果为空,没有可能的索引

	KEY:实际使用的索引,如果为null则没有索引

	KEY_LEN:使用的索引的长度,在不损失精确度的情况下,长度越短越好

	REF:显示索引的哪一列被使用了,如果可能的话,他是一个常数

	ROWS:MySQL认为必须检查的用来返回请求数据的行数

	FILTERED:

	EXTRA:关于MySQL如何解析查询的额外信息

七.事务的四大特性(ACID)

原子性(A):原子性是指事务包含的所有操作,要么全部成功,要么全部失败回滚.因此事务额度操作如果成功就必须完全应用到数据库,如果操作失败则不能对数据库造成任何影响.

一致性(C):事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态,这种特性称为事务的一致性.

隔离性(I):隔离性是当多个用户并发访问数据库时,比如操作同一张表,数据库为每一个用户开启的事务不能被其他事务的操作所干扰,多个并发事务之间要相互隔离.

既要达到这么一种效果:对于任意两个并发的事务 t1和t2,在事务t1看来,要么t1事务在开启的时候t2已结束,要么在t1结束之后再开始t2事务,这样每个事务都感觉不到有其他的事务在并发的执行.

持久性(D):事务的持久性指的是一个事务一旦提交了,那么对数据库中的数据的改变是永久性的;即便是在数据库系统遇到故障的情况下,也不会丢失提交事务的操作