MySQL笔记

MySQL笔记

MySQL逻辑分层
	select
	连接层： 提供与客户端连接的服务
	服务层：	1.提供各种用户使用的接口（select..增删改查）
			2.提供SQL查询优化器，优化复杂的SQL语句，写的语句A，执行的B
	引擎层：	提供各种存储数据的方式（InnoDB、MyISAM[看下区别])
		InnoDB：事务优先，适合高并发操作，事务可以防止高并发造成的混乱，行锁
		MyISAM：性能优先，表锁（一次锁一张表）
	存储层：存储数据

	查询数据库引擎：支持哪些引擎？ show engines;
					查看当前使用的引擎	show variables like '%storage_engine%';

	指定数据库引擎：
	create table tb(
		id int(4) auto_increment,
		name varchar(5),
		Primary Key(id)
	)ENGINE=MyISAM, AUTO_INCREMENT=1;


SQL优化	
	原因：性能低、执行时间长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲区、线程数等等）

	SQL语句
	编写过程：select (distinct.. from .. join .. on .. where .. group by .. having .. order by .. limit ..

	解析过程：from .. on .. join .. where .. group by .. having .. select .. order by .. limit ..

	文章：www.cnblogs.com/annsshadow/p/5037667.html


	SQL优化：主要就是优化索引
		索引：index是数据帮助MYSQL高效获取数据的数据结构，B+树

	索引不适用情况：数据量少、频繁更新的字段、使用少的字段
	优点：提高查询效率（降低IO次数）、B树索引本身就是有序的


索引
	分类：
		单值索引：单列，age，一个表可以有多个单值索引
		唯一索引：不能重复，id，【主键索引】不为NULL
		复合索引：多个列构成的索引（name,age）

	创建索引：
		create 索引类型 索引名 on 表(字段)
		单值索引：create index dept_index on tb(dept);
		唯一索引：create unique index name_index on tb(name);
		复合索引：create index dept_name_index on tb(dept, name);

		alter table 表 add 索引类型 索引名(字段)
		单值索引：alter table tb add index dept_index(dept);
		唯一索引：alter table tb add unique index name_index(name);
		复合索引：alter table tb add index dept_name_index (dept, name);

	删除索引：
		drop index 索引名 on 表；
		drop index name_index on tb;

	查询索引：
		show index from 表;

SQL性能问题
	a.分析SQL的执行计划  explain
	b.MySQL优化器会干扰我们的优化

	优化方法：官网dev.mysql.com/doc/refman/5.5/en/optimization.html

	id：编号
	select_type：查询类型 
	table：表 
	type：类型
	possible_keys：可能用到的索引 
	key：实际用到的索引
	key_len：实际使用索引的长度
	ref：表之间的引用关系
	rows：在查询中估计要读取的行数，不是结果集的行数
	Extra：额外信息

	准备数据：
		create table course(
			cid int(3),
			cname varchar(20),
			tid int(3)
		);

		create table teacher(
			tid int(3),
			tname varchar(20),
			tcid int(3)
		);

		create table teacherCard(
			tcid int(3),
			tcdesc varchar(200)
		);

		insert into course values(1, 'java', 1);
		insert into course values(2, 'html', 1);
		insert into course values(3, 'sql', 2);
		insert into course values(4, 'web', 3);

		insert into teacher values(1, 'tz', 1);
		insert into teacher values(2, 'tw', 2);
		insert into teacher values(3, 'tl', 3);

		insert into teacherCard values(1, 'tzdesc');
		insert into teacherCard values(2, 'twdesc');
		insert into teacherCard values(3, 'tldesc');

		查询课程编号为2或教师证编号为3的老师信息
 		select t.* from teacher t, course c, teacherCard tc where t.tid=c.tid and t.tcid=tc.tcid and (c.cid=2 or tc.tcid=3);


 	id：编号
		(1)id值相同：从上往下顺序执行		t3-tc3-c4
 						teacher 插入三条后：tc3-c4-t6
 			表执行顺序因数量的个数改变而改变的原因：笛卡尔积
 							2 * 3 = 6 * 4 = 24
 							4 * 3 = 12 * 2 = 24	结果一样，中间不一样
 							数据小的表优先查询
 		(2)id值不同，越大越优先查询(本质：嵌套子查询时，先查内层)

 		查询教授sql课程老师的描述（desc）
 		explain select tc.tcdesc from teacherCard tc,teacher t, course c where tc.tcid = t.tcid and c.tid = t.tid and c.cname='sql';

 		将以上多表查询变为子查询形式：
 		explain select tc.tcdesc from teacherCard tc where tc.tcid = 
 		(select t.tcid from teacher t where t.tid = (select tid from course where cname = 'sql'));

 		explain select t.tname, tc.tcdesc from teacher t, teacherCard tc where t.tcid = tc.tcid and t.tid = (select c.tid from course c where cname = 'sql');

 		(3)id有相同又有不同：id值越大越优先，id值相同上到下顺序执行


 	select_type：查询类型 
 		PRIMARY：包含子查询SQL中的主查询（最外层）
 		SUBQUERY：包含子查询SQL中的子查询（非最外层）
 		SIMPLE：简单查询（不包含子查询，union）
 		DERIVED：衍生查询（使用到了临时表）
 				在from子查询中只有一张表
 					select cr.cname from (select * from course where tid in (1, 2)) cr;
 				在from子查询中，有两张表union关系，tb1 union tb2, tb1是DERIVED，tb2是union
 					select cr.cname from (select * from course where tid = 1 union select * from course where tid = 2) cr;
 		UNION：上例
 		UNION RESULT：告知表之间存在union查询

 	table：表 
		
	type：索引类型
		system > const > eq_ref > ref > range > index > all
		system/const理想情况，实际达到ref级别

		system：只有一条数据的系统表，或衍生表只有一条数据的主查询
			create table test01(
				tid int(3),
				tname varchar(20)
			);
			insert into test01 values(1, 'a');
			alter table test01 add constraint tid_pk primary key(tid);
			explain select * from (select * from test01) t where tid=1;

		const：仅仅能查到一条数据的SQL，用于primary key或unique索引(类型与索引类型有关)
			explain select tid from test01 where tid=1;

			alter table test01 drop primary key;

		eq_ref：唯一性索引，对于索引键的查询，返回匹配唯一行数据（有且只有一个）
			常见与唯一索引和主键索引

			alter table teacherCard add constraint pk_tcid primary key(tcid);

			alter table teacher add constraint uk_tcid unique index(tcid);

			select t.tcid from teacher t, teacherCard tc where t.tcid=tc.tcid;

		ref：非唯一性索引，返回匹配的所有行
			insert into teacher values(4, 'tz', 4);
			insert into teacherCard values(4, 'tz222');

			alter table teacher add index idx_name(tname);
			select * from teacher t where tname='tz';
		range：检索指定范围的行，where后是一个范围查询（between..and， >,<,>=, in可能失效)
			alter table teacher add index tid_index(tid);
			explain select t.* from teacher t where t.tid > 3;

		index: 查询全部索引中的数据

		all:查询表中所有的数据

		总结：	system/const：结果只有一条数据
 				eq_ref：结果多条，但每条数据是唯一的
 				ref：结果多条，但每条数据是0条或者多条

	possible_keys：可能用到的索引 
	key：实际用到的索引
	key_len：实际使用索引的长度
	ref：表之间的引用关系
		ref作用：指明当前表所参照的字段
 			select .. where a.c = b.x（引用b的x）; （其中b的x可以是常量const）
 			select * from course c, teacher t where c.tid=t.tid and t.tname='tw';
	rows：在查询中估计要读取的行数，不是结果集的行数
	Extra：额外信息

单表优化例子：
	create table info(
		id int(4) primary key,
		name varchar(10),
		age int(4),
		deptId int(4),

	);


两表优化：
	create table teacher(
		tid int(4) primary key,
		cid int(4) not null
	);

	insert into teacher values(1,2);
	insert into teacher values(2,1);
	insert into teacher values(3,3);

	create table course(
		cid int(4),
		cname varchar(10)
	);

	insert into course values(1, 'c++');
	insert into course values(2, 'python');
	insert into course values(3, 'java');

	左连接：
		select * from teacher t left outer join course c on t.cid=c.cid where c.cname='java';

	索引怎么加？  --小表驱动大表，索引建立在经常使用的字段
	where 小表.x=大表.y

	a left on b,建立在a表
	a right on b,建立在b表
	alter table teacher add index idx_cid(cid);
	alter table course add index idx_cname(cname);

	drop index idx_cid on teacher;

	using join buffer：MySQL引擎使用了链接缓冲


多表优化：
	a.小表驱动大表
	b.建立在经常使用的字段上






避免索引失效的原则：
	a.复合索引，不要跨列或无序使用（最佳左前缀）
		where a and b order by c
	b.复合索引，尽量使用全索引匹配	
		找张三，学校-年级-班级
	c.不要在索引上进行操作（计算、函数、类型转换）
		where a.x = ..;
		where a.x*3 = ..;
	d.复合索引不能使用不等于(!=, <>)或is [not] null
	e.like尽量以“常量”开头，不以%或*开头
		explain select * from course where cname like "%x";
		使用索引覆盖可以挽救一部分 select cname ...
	f.不要使用类型转换
		explain select * from course where cname=123;
	g.不使用or
		explain select * from course where cname='1' and cid=2;


一些其他优化方法：
	exist和in
		如果主查询的数据集大，使用in
		如果子查询的数据集大，使用exist
	select .. from table where exist/in (子查询);
	select .. from tablr where id in (1,2);

	order by:
		using filesort有两种算法，双路排序和单路排序（根据IO次数）
		双路排序，扫描两次磁盘，第一次扫描排序字段，第二次扫描其他字段
		单路排序，一次读取所有字段，在buffer中排序
		MySQL4.1之后默认单路（IO消耗性能）：一次读取全部字段，在buffer中进行排序，但单路排序会有一定隐患（有可能多次IO）数据量太大，缓存区容量不足，无法一次性读完，因此会”分片读取、多次读取“。

 		单路比双路占用更多buffer，单路排序如果数据大，可以考虑调大buffer的容量大小：set max_length_for_sort_data = 1024;

 		如果max_length_for_sort_data值太低，MySQL自动从单路->双路（太低：需要排序的列总大小超过了定义的字节数）

		提高order by查询：
			1.选择合适的单双路，调整buffer大小
			2.避免select * 
			3.复合索引不要跨列使用，避免using filesort
			4.保证排序字段的一致性，全升序或全降序

SQL排查：
 		慢查询日志：MySQL提供的一种日志记录，用于记录响应时间超过阈值的SQL语句（long_query_time，默认10秒）
 		慢查询日志默认关闭，建议：调优时打开，最终部署时关闭

 		检查是否开启了慢查询日志： show variables like '%slow_query_log%';
 		临时开启：set global slow_query_log = 1; --在内存中开启
 		重启mysql服务恢复默认 service mysql restart;
 		
 		临时设置阈值：set long_query_time=10；完毕后重登起效

 		show global status like '%slow_queries%';



分析海量数据：
	模拟数据： 存储过程/存储函数(return)
	create database testdb;

	create table dept(
		id int(4) primary key default 0,
		dname varchar(10) not null default '',
		loc varchar(10) default ''
	)engine=innodb default charset=utf8;

	create table emp(
		eid int(4) primary key,
		ename varchar(10) not null,
		job varchar(10) not null,
		deptid int(4) not null
	)engine=innodb default charset=utf8;

	通过存储函数插入海量数据
	创建存储函数：

		delimiter $
		create function randstring(n int) returns varcahr(255)
		begin
			declare all_str varchar(255) default 'abcdefghijklmnopqrstuvwxyz';
			declare ret_str varchar(255) default '';
			declare i int default 0;
			while i < n
			do
				set ret_str = concat(ret_str, substring(all_str, FLOOR(rand()*26 + 1), 1));
				set i = i + 1
			end while;
			return ret_str;
		end $