• Mysql优化框架

Mysql优化框架

1.SQL语句优化
2.索引优化
3.数据库结构优化
4.InnoDB表优化
5.MyISAM表优化
6.Memory表优化
7.理解查询执行计划
8.缓冲和缓存
9.锁优化
10.MySQL服务器优化
11.性能评估
12.MySQL优化内幕

MyISAM:
	表结构
	数据
	索引

InnoDB:
	.ibd:表空间
		索引+数据
	.frm

表锁
页锁
行锁

MySQL优化需要在三个不同层次上协调进行；MySQL级别、OS级别和硬件级别，MySQL级别的优化包括表优化、查询优化和MySQL服务器配置优化等，而MySQL的各种数据结构又最终作用于OS直至硬件设备，因此还需要了解每种结构对OS级别的资源的需要并最终导致的CPU和I/O操作等，并在此基础上将CPU及I/O操作需要尽量降低以提升其效率。

数据库层面的优化着眼点：
1.是否正确设定了表结构的相关属性，尤其是每个字段的字段类型是否为最佳，同时，是否为特定类型的工作组织使用了合适的表及表字段也将影响系统性能，比如，数据频繁更新的场景应该使用较多的表而每张表有着较少的字段结构，而复杂数据查询或分析的场景应该使用较少的表而每张表较多字段的结构等。
2.是否为高效进行查询创建了合适的索引。
3.是否为每张表选用了合适的存储引擎，并有效利用了选用的存储引擎本身的优势和特性。
4.是否基于存储引擎为表选用了合适的行格式（row format），例如，压缩表在读写操作中会降低I/O操作的需求并占用较少的磁盘空间，InnoDB支持读写应用场景中使用压缩表，但MyISAM仅能在读环境中使用压缩表。
5.是否使用了合适的锁策略，如在并发操作场景中使用共享锁，而对较高优先级的需求使用独占锁等，同时，还应该考虑存储引擎所支持的锁类型，
6.是否为InnoDB的缓冲池、MyISAM的键缓存以及MySQL查询缓存设定了合适大小的内存空间，以便能够存储频繁访问的数据且又不会引起页面换出。

操作系统和硬件级别的优化着眼点：
1.是否为实际的工作负载选定了合适的CPU，如对于CPU密集型的应用场景要使用更快速度的CPU甚至更多数量的CPU，为有着更多查询的场景使用更多的CPU等，基于多核以及超线程（hyperthreading）技术，现代的CPU架构越来越复杂、性能页越来越强了，但MySQL对多CPU架构的并行计算能力的利用仍然是有着不太尽如人意之处，尤其是较老的版本,如MySQL5.1之前的版本甚至无法发挥多CPU的优势，不过，通常需要实现的CPU性能提升目标有两类；低延迟和高吞吐量，低延迟需要更快速度的CPU，因为单个查询只能使用一颗，而需要同时运行许多查询的场景，多CPU更能提供更好的吞吐能力，然而其能否奏效还依赖于实际工作场景，因为MySQL尚不能高效的运行于多CPU，并且其对CPU数量的支持也有着限制，一般来说，较新的版本可以支持16至24颗CPU甚至更多。
2.是否有着合适大小的物理内存，并通过合理的配置平衡内存和磁盘资源，降低甚至避免磁盘I/O，现代的程序设计为提供性能通常都会基于局部性原理使用到缓存技术，这对于频繁操作数据的数据库系统来说尤其如此，有着良好设计的数据库缓存通常比针对通用任务的操作系统的缓存效率更高，缓存可以有效地延迟写入、优化写入、但并能消除写入，并综合考虑存储空间的可扩展性等，为业务选择合理的外部存储设备也是非常重要的工作。
3.是否选择了合适的网络设备并正确地配置了网络对整体系统也有着重大影响。延迟和带宽是网络连接的限制性因素，而常见的网络问题如丢包等，即是很小的丢包率也会赞成性能的显著下降，而更重要的还有按需调整系统中关于网络方面的设置，以高效处理大量的连接和小查询。
4.是否基于操作系统选择了适用的文件系统，实际测试表明大部分文件系统的性能都非常接近，因此，为了性能而苦选文件系统并不划算，但考虑到文件系统的修复能力，应该使用日志文件系统如：ext3,ext4,ext5等。同时，关闭文件系统的某些特性如访问时间和预读行为，并选择合理的磁盘调度器通常都会给性能提升带来帮助。
5.MySQL为响应每个用户连接使用一个单独的线程，再加内部使用的线程、特殊目的线程以及其它任何由存储引擎的线程等，MySQL需要对这些大量线程进行有效管理，Linux系统上的NPTL线程库更为轻量级也更有效率，MySQL5.5引入了线程池插件，但其效用尚不明朗。


使用InnoDB存储引擎最佳实践：
	1.基于MySQL查询语句中最常用的字段或字段组合创建主键，如果没有合适的主键也最好使用AUTO_INCREMENT类型的某字段为主键。
	2.根据需要考虑使用多表查询，将这些表通过外键建立约束关系。
	3.关闭autocommit
	4.使用事务(START TRANSACTION和COMMIT语句)组合相关的修改操作或一个整体的工作单元，当然也不应该创建过大的执行单元。
	5.停止使用LOCK TABLES语句，InnoDB可以高效地处理来自多个会话的并发读写请求，如果需要在一系列的行上获取独占访问权限，可以使用SELECT...FOR UPDATE锁定仅需要更新的行。
	6.启用innodb file per table选项，将各表的数据和索引分别进行存放。
	7.评估数据和访问模式是否能从InnoDB的表压缩功能中受益（在创建表时使用ROW_FORMAT=COMPRESSED）选项
，如果可以，则应该启用压缩功能。

聚集索引
非聚集索引

主索引
辅助索引

稠密索引
稀疏索引

多级索引

B+树
	从根到每一个叶子节点的路径是等长的；
		平衡树索引
			Balance Tree
		
		索引：加速查找
		索引：降低写入速度
	
	对插入、删除、更新等性能的影响
	
	B树索引的使用场景：
		适用全键值、键值范围或键左前缀查找；
		键左前缀查找：
			select * from where name like 'chen%';
	局限性：
		eg:
			name age salary 组合索引
		
    	如果不是从最左前缀开始，索引没用；
    		where name like '%chen%'
    	不能跳过索引中的列：
    		where name like 'chen%' and salary>3000; # 索引失效
		存储引擎不能优化访问任何在第一个范围条件右边的列；
	

哈希索引
	key--value
    hash码
	
	age:hash索引
	key(hash)-->value
	
	select * from students where age=30;
		对30做hash码计算，计算结果除以桶的个数、取余落到哪个桶就去这个桶查找age=30的value
		
	查询时使用等值条件比较，如果要实现范围查找，就要使用B+树索引
	
	只有Memory引擎支持显示的hash索引，Innodb引擎支持自适应hash索引，即如果Innodb引擎发现我们在某个字段上经常使用等值比较，就会自动创建一个hash索引
	
		hash索引只支持使用 =，in (),<=>进行的条件比较；

	缺陷：
		无法使用索引排序；
		不支持部分键匹配；

全文索引


MyISAM引擎常用参数调整：
    key_buffer_size: 调整使用索引的缓冲区大小
    concurrent_insert:提高并发写入的性能
    delay_key_write:
    max_write_lock_count:
    preload_buffer_size:

InnoDB引擎常用参数调整：
	Innodb_data_file_path
	Innodb_data_home_dir
	Innodb_file_per_table

	innodb_buffer_pool_size:安装完MySQL之后，第一个调整的参数,用来调整InnoDB引擎缓存数据+索引的空间大小，可以使用大内存页，将需要的尽可能多的数据和索引加载到内存，减少磁盘IO
	innodb_flush_log_at_trx_commit：日志刷到磁盘上的频率
	innodb_log_file_size

尽量使用风格统一的SQL查询语句：
	Select name from students where age=30;
	select name from students where age=30;
	SELECT name FROM students WHERE age=30;
	
	以上3条语句表达的意思都一样，但是大小写有区别，这在查询中创建的hash索引不同，第一条创建的hash索引，第2、3条查询语句用不到。
	
Query Cache System Varuables:
	query_alloc_block_size:
		Specifies the size of memory blocks alloc ated by the query cache during query processing.The default is 8 Kb.
	query_cache_limit:
		Result sets larger than this are not cached.The default is 1 Mb.
	query_cache_min_res_unit:
		Specifies the minimums size for blocks in the query cache. The default value is 4 Kb.
	query_cache_size:
		The total memory allocated by mysqld for the query cache.The default is 0,which disables the query cache. This can be changed dynamically --changes do not require a mysqld restart.
	query_cache_type:
		Determines the mode of operation of the query cache.Options are OFF,ON,and DEMAND.The default is ON.
	query_cache_wlock_incalidate:
		If set to IRUE,queries referencing MyISAM tables are incalidated when a write lock for that table is obtained,even if none of the data is changed when the write lock is related. The default is FALSE.
	query_cache_prealloc_size:
		Specifies the size of the buffer used for query parsing by the cache.Defaults to 8 Kb.

EXPLAIN语句解析：
	id:SELECT语句的标识符，一般为数字，表示对应的SELECT语句在原始语句中的位置，没有子查询或联合的整个查询只有一个SELECT语句，因此其id通常为1，在联合或子查询语句中，内层的SELECT语句通常按他们在原始语句中的次序进行编号，但UNION操作通常最后会有一个id为NULL的行，因为UNION的结果通常保存至临时表中，而MySQL需要到此临时表中取得结果。
	
	select_type:
	即SELECT类型，有如下值列表：
		SIMPLE：简单查询，即没有使用联合或子查询；
		PRIMARY：UNION得最外围得查询或者最先进行得查询；
		UNION：相对于PRIMARY，为联合查询得第二个及以后得查询；
		DEPENDENT UNION：于UNION相同，但其位于联合子查询中（即UNION查询本身是子查询）；
		UNION RESULT：UNION的执行结果；
		SUBQUERY：非从属子查询，优化器通常认为其只需要运行一次；
		DEPENDENT SUBQUERY：从属于子查询，优化器认为需要为外围的查询的每一行运行一次，如用于IN操作符中的子查询；
		DERUVED：用于FROM子句的子查询，即派生表查询；
		
		table：
		输出信息所关系到的表的表名，也有可能会显示为如下格式：
			<unionM,N>:id为M和N的查询执行联合查询后的结果；
			<derivedN>:id为N的查询执行的结果集；
		
		type：
			MySQL官方手册中解释type的作用为“type of join(联结的类型)”，但其更确切的意思应该是“记录（record）访问类型”，因为其主要目的在于展示MySQL在表中找到所需行的方式，通常有如下所示的记录访问类型；
			system：表中仅有一行，是const类型的一种特殊情况；
			const：表中至多有一个匹配的行，该行仅在查询开始时读取一次，因此，该行此字段中的值可以被优化器看作是各常量（constant）；当基于PRIMARY KEY或UNIQUE NOTNULL字段查询，且与某常量进行等值比较时其类型就为const，其执行速度非常快；
			eq_ref:类似于const，表中至多有一个匹配的行，但比较的数值不是某常量，而是来自于其它表：eq_ref出现在PRIMARY KEY或UNIQUE NOT　NULL类型的索引完全用于联结操作中进行等值(*)比较时，这是除了system和const之外最好的访问类型；
			ref：查询时的索引类型不是PRIMARY KEY或UNIQUE NOT NULL 导致匹配到的行可能不唯一，或者仅能用到索引的左前缀而非全部时的访问类型；ref可悲用于基于索引的字段进行=或<=>操作；
			fulltext:用于FULLTEXT索引中用纯文本匹配的方法来检索记录。
			ref_or_null:类似于ref，但可以额外搜索NULL值；
			index_merge:使用“索引合并优化”的记录访问类型，相应地，某key字段（EXPLAIN的输出结果）中会出现用到的多个索引，key_len字段中会出现被使用索引的最长长度列表；将多个“范围扫描（tange scan）”获取到的行进行合并成一个结果集的操作即索引合并（index merge）。
			unique_subquery：用于IN比较操作符中的子查询中进行的“键值唯一”的访问类型场景中，如value IN (SELECT primary_key FROM single_table WHERE some_expr);
			index_subquery:类似于unique_subquery，但子查询中键值不唯一；
			range:带有范围限制的索引扫描，而非全索引扫描，它开始于索引里的某一点，返回匹配那个值的范围的行；相应地，其key字段（EXPLAIN的输出结果）中会输出所用到的索引，key_len字段中会包含用到的索引的最长部分的长度；range通常用于将索引与常量进行=、<>、>、>=、<、<=、 IS NULL、<=>、BETWEEN 或 IN()类的比较操作中；
			index：同全表扫描（ALL），只不过是按照索引的次序进行而不行的次序；其优点是避免了排序，但是要承担按索引次序读取整个表的开销，这意味着若是按随机次序访问行，代价将非常大；
			ALL：“全表扫描”的方式查找所需要的行，如果第一张表的查询类型（EXPLAIN的输出结果）为const，其性能可能不算太坏，而第一张表的查询类型为其它结果时，其性能通常会非常差；
		
		Extra:
			Using where:MySQL服务器将在存储引擎收到数据后进行“后过滤（post-filter）”以限定发送给下张表或客户端的行；如果WHERE条件中使用了索引列，其读取索引时就由存储引擎检查，因此，并拿所有带有WHERE子句的查询都会显示“Using where”；
			Using index:表示所需要的数据从索引就能够全部获取到，从而不再需要从表中查询获取所需要数据，这意味着MySQL将使用覆盖索引；但如果同时还出现了Using where,则表示索引将被用于查找特定的键值；
			Using index for group-by:类似于Using index，它表示MySQL可仅通过索引中的数据完成GROUP BY或DISTINCT 类的查询；
			Using filesort：表示MySQL会对结果使用一个外部索引排序，而不是从表里按索引次序来读取行；