数据库优化总结

								数据库优化总结

存储引擎
查看存储引擎：show engines;
默认存储引擎：show variables like ‘%storage_engine%’;
MyISAM InnoDB
主外键 不支持 支持

事务 不支持 支持

行表锁 表锁，即使操作一天记录也会锁住整个表,不适合高并发操作 行锁，操作时只锁住某一行
不对其他行有影响，适合高并发

缓存 只缓存索引，不缓存真实数据 不仅缓存索引还缓存数据，对内存的要求比较高，
而且内存大小对性能又决定性影响

表空间 小 大

关注点 性能 事务

默认安装 Y Y

分析sql查询速度慢原因
执行时间长
等待时间长
主要问题：查询语句写的烂
索引失效：
单值索引：(select * from where name=’’) name为索引 概念：只为该表的某一个字段创建索引 如:name
复合索引:(select * from where name=’’ and email = ‘’) 概念：为该表的多个字段创建复合索引 如：name 和 email
关联查询太多join(设计缺陷或不得已的需求)
服务器调又及各个参数设置（缓存，线程数等）
导致sql慢关联查询太多join分析解决
SQL执行的顺序：
手写顺序：
SELECT DISTINCT <select_list>
FROM
<left_table> <join_type>
JOIN <right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_by_list>
ORDER BY
<order_by_condition>
LIMIT
机读顺序：
FROM <left_table> <join_type>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_by_list>
SELECT DISTINCT <select_list>
ORDER BY
<order_by_condition>
LIMIT
join七种理论
第一种：查询俩张表的共有部分使用内连接：SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key=B.Kay
第二种：查询A B共有和A的独有使用左连接：SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Kay
第三种：查询A B共有和B的独有使用右连接：SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Kay
第四种：查询A 的独有使用左连接：SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key=B.Kay WHERE B.key is NULL
第五种：查询B 的独有使用右连接：SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key=B.Kay WHERE A.key is NULL
第六种：查询A B所有使用全连接：SELECT <select_list> FROM TableA A Full OUTER JOIN TableB B ON A.Key=B.Kay
第七种：查询A B独有使用全连接：SELECT <select_list> FROM TableA A Full OUTER JOIN TableB B ON A.Key=B.Kay WHERE WHERE B.key is NULL OR A.key is NULL
建表练习：
部门表
CREATE TABLE tbl_dept(
id INT(11) NOT NULL AUTO_INCREMENT,
deptName VARCHAR(30) DEFAULT NULL,
locAdd VARCHAR(40) DEFAULT NULL,
PRIMARY KEY(id)
)ENGINE = INNODB AUTO_INCREMENT=1 DEFAULT CHARSET = utf8;
员工表
CREATE TABLE tbl_emp(
idINT(11) NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
deptId INT(11) DEFAULT NULL,
PRIMARY KEY(id),
KEY fk_dept_id(deptId)
)ENGINE = INNODB AUTO_INCREMENT=1 DEFAULT CHARSET =utf8;
数据插入：
INSERT INTO tbl_dept(deptName,locAdd)VALUES(“RD”,11);
INSERT INTO tbl_dept(deptName,locAdd)VALUES(“HR”,12);
INSERT INTO tbl_dept(deptName,locAdd)VALUES(“MK”,13);
INSERT INTO tbl_dept(deptName,locAdd)VALUES(“MIS”,14);
INSERT INTO tbl_dept(deptName,locAdd)VALUES(“FD”,15);

INSERT INTO tbl_emp(NAME,deptId)VALUES("z3",1);
INSERT INTO tbl_emp(NAME,deptId)VALUES("z4",1);
INSERT INTO tbl_emp(NAME,deptId)VALUES("z5",1);
INSERT INTO tbl_emp(NAME,deptId)VALUES("w5",2);
INSERT INTO tbl_emp(NAME,deptId)VALUES("w6",2);
INSERT INTO tbl_emp(NAME,deptId)VALUES("w7",3);
INSERT INTO tbl_emp(NAME,deptId)VALUES("w8",4);
根据上述表结构显示join其中情况
第一种：SELECT * FROM tbl_dept d INNER JOIN tbl_emp e ON d.id=e.deptId
第二种：SELECT * FROM tbl_dept d LEFT JOIN tbl_emp e ON d.id=e.deptId 左表全部,右表不满足的补NULL
第三种：SELECT * FROM tbl_dept d RIGHT JOIN tbl_emp e ON d.id=e.deptId 右表全部,左表不满足补NULL
第四种：SELECT * FROM tbl_dept d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId is NULL
第五种：SELECT * FROM tbl_dept d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id is NULL
第六种：SELECT * FROM tbl_dept d FULL OUTER JOIN tbl_emp e ON d.id=e.deptId
		mysql不支持 FULL OUTER 
		SELECT * FROM tbl_dept d LEFT JOIN tbl_emp e ON d.id=e.deptId
		union
		SELECT * FROM tbl_dept d RIGHT JOIN tbl_emp e ON d.id=e.deptId
		union(合并去重)
第七种：SELECT * FROM tbl_dept d LEFT JOIN tbl_emp e ON d.id=e.deptId WHERE e.deptId is NULL
	   union
	   SELECT * FROM tbl_dept d RIGHT JOIN tbl_emp e ON d.id=e.deptId WHERE d.id is NULL

索引：
索引是什么：
1.Mysql官网对索引得定义为：索引(Index) 是帮助MYSQL高效获取数据得数据结构（索引是数据结构）索引得目的在于提高查询效率
2.一般来说索引本身也很大，不可能全部存储到内存中，因此往往以索引文件得形式存储到磁盘中
3.可以理解为排好序得快速查找数据结构
详解：
在数据之外,数据库系统还维护着满足特查算法得数据结构，这些数据结构以某种方式引用(指向)数据
这样就可以再这写数据结构上实现高级查找算法。这种数据结构就是索引
图(1)就是可能得索引方式示例
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个直送对应数据记录物理地址的指针，这样可以运用二叉查找再一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录
4.我们平常所说的索引，如果没有特别的说明，都是指B树(多路搜索树，并不一定是二叉的)结构组织索引。其中聚集索引，次要索引，复合索引，前缀索引，
唯一索引默认都是使用的B+树索引，统称为索引，除了B+树这种索引类型外，还有哈希索引等
索引的优势：
提高了数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
索引的劣势:
实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也要占用空间
虽然索引大大提高了查询你的速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE
因为更新表时，mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的mysql有大数据量的表，就需要花时间建立最优秀的的索引，或优化查询
索引的分类：
单值索引：即一个索引只包含单个列，一个表可有多个单列索引
唯一索引：索引列的值必须唯一，但允许为空
复合索引：即一个索引包含多个列
基本语法：
创建：CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
AlTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
删除：DROP INDEX [indexName] ON mytable;
查看：SHOW INDEX FROM table_name\G
使用ALTER命令(有四种方式来添加数据表的索引)：
1.ALTER TABLE tbl_name ADD PRIMARY KEY(column_list) 该语句添加一个主键，意味着索引值必须唯一，且不能为null
2.ALTER TABLE tbl_name ADD UNIQUE index_name (column_list) 这条语句创建索引的值必须时唯一的(除NULL外，NULL可能出来多次)
3.ALTER TABLE tbl_name ADD INDEX index_name (column_list) 添加普通所用，索引值可出现多次
4.ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list) 该语句指定了索引为FULLTEXT,用于全文索引
mysql索引结构
BTree索引
检索原理：如图b+树
初始化介绍：
一颗b+树，浅蓝色的块我i们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示)
如磁盘块1包含数据项17，35包含指针P1,P2,P3
P1表示小于17的磁盘块，P2表示在17-35之间的磁盘块，P3表示大于35的磁盘块
真实数据存储到叶子节点上即3，5，9，10，13，15，28，29，36，60，75，79，90，99
非叶子节点不存储真实数据，只存储指引搜索方向的数据项，如17和35并不是真实在数据中
查找过程：
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存中，此时发生一次IO,在内存中用二分查找确定29在17和35之间，锁定磁盘块的P2指针，内存执行时间因为非常短(相比磁盘IO)，可以忽略不记，通过磁盘块1的P2指针的磁盘地址把磁盘3由磁盘加载到内存，发生第二次IO 29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO,同时内存中做二分查找找到29，结束查询，总计三次IO

			真实情况时。3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO,性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO,那么总共需要上百次的IO,显然成本是非常非常高
	Hash索引(了解)
	full-text全文检索(了解)
	R-Tree索引(了解)
那些情况i适合创建索引：
	1.主键自动建立唯一索引
	2.频繁作为查询添加的字段应该创建索引
	3.查询中与其他表关联的字段，外键建立索引
	4.频繁更新的字段不适合创建索引 因为每次更新不单单是更新了记录还更新索引
	5.where条件里用不到的字段不创建索引
	6.单键/组合索引选择（高并发下倾向创建复合索引）
	7.查询中排序的字段，排序字段通过索引去访问将大大提高排序速度
	8.查询中统计或分组的字段
那些情况不适合创建索引
	1.表记录太少
	2.经常增删改的表
	3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引 注意：如果某个数据列包含许多重复的内容，为他建立索引就没有太大的效果
		举例：假如一个表有10万行记录，有一个字段A只有T和F俩种值，且每个值的分布概率大概是50%，那么对这种表A字段建立索引一般不会提高数据库的查询速度
			索引的选择性是指索引列种不同值得数目与表记录数得比。如果一个表中有2000条记录，表索引列有1980个不同得值，那么这个索引得选择性就是1980/2000=0.99 一个索引得选择性越接近于1，这个索引得效率就越高
性能分析前提知识：
	Mysql Query Optimizer(查询优化器):
		1.Mysql中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中手机到的统计信息，为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式，但不见得是DBA认为最优的，这部分是最耗费时间)
		2.当客户端向Mysql请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给Mysql Query Optimizer 时，Mysql Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值，并对Query中的查询条件进行简化和转化，如去掉一些无用或显而易见的条件 结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完成确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划
mysql常见瓶颈：
	1.CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
	2.IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
	3.服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态
Explain：
	是什么(查看执行计划)：
		使用EXPLAIN关键字可以模拟优化器执行sql查询语句，从而知道MYSQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
	能干什么：
		1.表的读取顺序
		2.数据读取操作的操作类型
		3.那些索引可以使用
		4.那些索引被实际使用
		5.表之间的引用
		6.每张表有多少行被优化器查询
	怎么玩：
		1.Explain+sql语句
		2.执行计划包含的信息
			id select_type table type possible_keys key key_len ref rows Extra
			下面介绍字段名
			id:
				1.select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
				2.三种情况
					1.id相同，执行顺序由上到下(表在mysql加载的顺序)
						列： Explain select * from a,b where a.id=9有可能他加载的顺序就是b  a
					2.id不同，如果是子查询，id的序号就会递增，id值越大优先级越高，越先被执行
					3.id相同又不同同时存在，id如果相同，可以认为是一组，从上到下执行，在所有组中，id越大，优先级越高，越先执行
			select_type(查询类型)：
				1.SIMPLE 简单的select查询，查询中不包含子查询或者UNION
				2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
				3.SUBQUERY 在SELECT或WHERE列表中包含子查询
				4.DERIUDE 在FROM列表中包含的子查询被标记为DERIVED(衍生)MYSQL会递归执行这写子查询，把结果放在临时表里
				5.UNION 如第二个SELECT出现在UNION后，则被标记为UNION若UNION包含在FROM子句查询中，外层SELECT将被标记为：DERIVED
				6.UNION RESULT 从UNION表获取结果的SELECT
			table：显示这一行的数据是关于哪张表
			type： 
				显示查询使用了何种类型，有下面几种值：
					ALL index range ref eq_ref const system NULL
				常见的从最好到最差依次是：
					system>const>eq_ref>ref>range>index>ALL
				详细访问类型排列：
					type显示的是访问类型，是较为重要的指标，结果值从最好到最坏依次是：
						system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>range>index>ALL
					一般来说保证查询至少能达到range级别，最好是ref
				system：表只有一条记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略
				const：表示通过索引一次就找到了，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快
				eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一扫描
				ref：非唯一索引扫描，返回匹配某个单位值得所有行，本质上也是一种索引访问，它返回所有匹配某个单独值得行，然后它可能会找到多个复合条条件得行，所以它应该属于查找和扫描得混合体
				range：只检索给定范围得行，使用一个索引来选择行，key列显示使用了那个索引 一般就是你得where语句中出现了between < > in等查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引一点，，而结束于另一点，不用扫描全部索引
				index：Full Index Scan,Index于all区别为index类型只遍历索引树，通常比all快，因为索引文件通常比数据文件小
				ALL：Full Table Scan 将遍历全表以找到匹配的行
			possible_keys：显示可能应用在这张表得索引，一个或多个 查询涉及到得字段上若存在索引，则该索引将被列出(但不一定被查询实际使用)
			key：
				1.实际使用得索引。如果为NULL 则没有使用索引
				2.查询中若使用了覆盖索引，则该索引仅出现在key列表中
					覆盖索引：select 后要查询得字段和我创建的索引个数和顺序是一样的
			key_len：
				1.表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确度的情况下，长度越短越好
				2.key_len显示的值为索引字段的最大可能长度。并非实际使用长度。即key_len是根据表定义计算而得，不是通过表内检索出来得
			ref：显示索引得那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上得值
			rows：根据表统计信息及索引选用情况，大致估算出找到所需得记录所需要读取得行数
			Extra：包含不适合在其他列中显示但十分重要得额外信息
				#1.Using filesort (会出查询问题 九死一生)说明mysql会对数据使用一个外部得索引顺序，而不是按照表内得索引顺序进行读取。MYSQL中无法利用索引完成得排序操作称之为 文件排序
				#2.Using temporay （很大很大得问题 十死无生）使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序 order by和分组查询 group by
				#3.Using index(好事)
					1.表示相应得select操作中使用了覆盖索引，避免访问了表得数据行，效率不错
					2.如果同时出现using where 表明索引被用来执行索引值得查找
					3.如果没有同时出现using where 表明索引用来读取数据而非执行查找动作
				4.using where表明使用过了where 过滤
				5.using join buffer 使用了连接缓存
				6.select tables optimized away 在没有GROUP BY 子句得情况下 基于索引优化MIN/MAX操作或者对于没有MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算 查询执行计划生成得阶段即完成优化
				7.distinct 优化DISTINCT操作 再找到第一匹配得元组后即停止找同样值得动作
				8.impossible where where子句得值总时false 不能用来获取任何元组