MySQL优化

MySQL优化
索引
简介
作用
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
优点
索引大大减小了服务器需要扫描的数据量
索引可以帮助服务器避免排序和临时表
索引可以将随机IO变成顺序IO
索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组。
关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效
类型
单主键索引
普通索引
唯一索引
主键索引
组合索引
全文索引
空间索引
Hash索引
创建索引准则
应该创建索引的列
在经常需要搜索的列上，可以加快搜索的速度
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间
在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度
不该创建索引的列
对于那些在查询中很少使用或者参考的列不应该创建索引。
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
对于那些定义为text, image和bit数据类型的列不应该增加索引
当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）
索引结构
B-TREE

		B+TREE 
		HASH
	存储引擎对比
		
EXPLAIN 
	字段解释
		id
			查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序
			id相同，执行顺序从上往下
			id不同，id值越大，优先级越高，越先执行
		select_type
			查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
			simple 
				简单的select查询，查询中不包含子查询或者UNION
			 primary
				查询中若包含任何复杂的子部分，最外层查询被标记
			derived
				在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
			union
				如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
			union result
				UNION 的结果
			MATERIALIZED
				在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION。
			SUBQUERY
				如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是不相关子查询，并且查询优化器决定采用将该子查询物化的方案来执行该子查询时，该子查询的第一个SELECT关键字代表的那个查询的select_type就是SUBQUERY
			DEPENDENT SUBQUERY
				如果包含子查询的查询语句不能够转为对应的semi-join的形式，并且该子查询是相关子查询，则该子查询的第一个SELECT关键字代表的那个查询的select_type就是DEPENDENT SUBQUERY
			DEPENDENT UNION
				在包含UNION或者UNION ALL的大查询中，如果各个小查询都依赖于外层查询的话，那除了最左边的那个小查询之外，其余的小查询的select_type的值就是DEPENDENT UNION
		table
			这一列表示 explain 的一行正在访问哪个表。
		type
			显示联结类型，显示查询使用了何种类型，按照从最佳到最坏类型排序
			system
				表中仅有一行（=系统表）这是const联结类型的一个特例。
			const
				表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
			eq_ref
				唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
			ref
				非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
			range
				只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
			index
				与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
			all
				遍历全表以找到匹配的行
			一般保证查询至少达到range级别，最好能达到ref。
		possible_keys
			指出MySQL能使用哪个索引在该表中找到行
		key
			显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠
		key_len
			表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
		ref
			显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
		rows
			根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
		filtered
			显示了通过条件过滤出的行数的百分比估计值。
		Extra
			包含不适合在其他列中显示，但是十分重要的额外信息
			Using filesort
				说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
			Using temporary
				使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
			Using index
				表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。
			Using where
				表明使用where过滤
			Using index condition
				与Using where类似，查询的列不完全被索引覆盖，where条件中是一个前导列的范围；
			Using where Using index
				查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，意味着无法直接通过索引查找来查询到符合条件的数据
			using join buffer
				使用了连接缓存
			impossible where
				where子句的值总是false，不能用来获取任何元组
			select tables optimized away
				在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
			distinct
				优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。
			NULL
				查询的列未被索引覆盖，并且where筛选条件是索引的前导列，意味着用到了索引，但是部分字段未被索引覆盖，必须通过“回表”来实现，不是纯粹地用到了索引，也不是完全没用到索引
SQL执行顺序
	完整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_condition>

order by

<order_by_condition>

limit
执行顺序
1、from <left_table><join_type>

2、on <join_condition>

3、<join_type> join <right_table>

4、where <where_condition>

5、group by <group_by_list>

6、having <having_condition>

7、select

8、distinct <select_list>

9、order by <order_by_condition>

10、limit <limit_number>
鱼骨图

常用SQL优化
	SQL优化
		尽量避免使用子查询
			select * from t1 where id ( select id from  t2 where flag = 1 )
			5.5 版本中ysql 先查外表再匹配内表,外表数据大时，查询非常慢
			5.6 版本mysql 自动转化为join，但是只针对SELECT ，DELETE/UPDATE 不会转化
			select t1.* from t1 join t2 on t1.id = t2.id where flag = 1
		避免函数索引
			mysql在普通索引上使用函数会导致索引失效
			select * from t where year(y) >2016
			5.7 版本中新增虚拟列 Generated Columns，可以在虚拟列上使用函数并创建建索引
				Virtual Generated Column（虚拟生成的列）
					不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。
				Stored Generated Column（存储生成的列）
					存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual Column相比并没有优势。
				CREATE TABLE t1 (
a INT,
b INT,
c INT GENERATED ALWAYS AS ( a / b ) STORED,

PRIMARY KEY ( c ));
alter table t1 add index c_idx©；
MySQL 8.0 支持函数索引（也是使用虚拟列实现）
用IN来替换OR
select * from t where a = 10 or a = 20 or a = 30;
select * from t where a in (10,20,30)
MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。
select id from table_name where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。
LIKE前缀%号、双百分号、_下划线查询非索引列或无法使用到索引，如果查询的是索引列则可以
字符过长可以使用前缀索引，可以有效减少索引长度，增加查询效率
全文索引
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引
MySQL 5.7.7 后全文索引支持中文等
alter table t
add fulltext index content_tag_fulltext(col1,col2);
语法：select * from t where match(col1,col2) against(‘xx xx’)
避免数据类型不一致
select * from t where char = 12
查询时类型不一致，MySQL会自动转换类型，增加额外的消耗
分组统计可以禁止排序sort，总和查询可以禁止排重用union all
select id,count() from t where t = 1 group by id;
union 和 union all
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议使用 union all 提高速度。
不要使用NOT等负向查询条件
对于一棵B+树，根节点是40，如果你的条件是等于20，就去左面查，你的条件等于50，就去右面查，但是你的条件是不等于66，索引应该咋办？还不是遍历一遍才知道
尽量不用select *
SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上字段名
区分in和exists
区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
优化Group By语句
如果对group by语句的结果没有排序要求，要在语句后面加 order by null（group 默认会去排序）；
尽量让group by过程用上表的索引，确认方法是explain结果里没有Using temporary 和 Using filesort；
如果group by需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大tmp_table_size参数，来避免用到磁盘临时表；
如果数据量实在太大，使用SQL_BIG_RESULT这个提示，来告诉优化器直接使用排序算法（直接用磁盘临时表）得到group by的结果。
索引优化
Join语句的优化
straight_join
永远用小结果集驱动大的结果集
用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽量减少join语句中的Nested Loop的循环总次数
优先优化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；
对被驱动表的join字段上建立索引
当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size
尽量用inner join(因为其会自动选择小表去驱动大表).避免 LEFT JOIN (一般我们使用Left Join的场景是大表驱动小表)和NULL
优化Left Join
条件中尽量能够过滤一些行将驱动表变得小一点，用小表去驱动大表
右表的条件列一定要加上索引（主键、唯一索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建在右边。当然如果索引是在左边的，我们可以考虑使用右连接，如下
适当地在表里面添加冗余信息来减少join的次数
避免索引失效
最佳左前缀法则
如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。Mysql查询优化器会对查询的字段进行改进，判断查询的字段以哪种形式组合能使得查询更快，所有比如创建的是(a,b)索引，查询的是(b,a)，查询优化器会修改成(a,b)后使用索引查询。
不在索引列上做任何操作
(计算、函数、(自动or手动)类型转换)，会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列
如这样的sql: select * from user where username=‘123’ and age>20 and phone=‘1390012345’,其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。
尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))
如select age from user减少select *
mysql在使用不等于(!= 或者 <>)的时候无法使用索引会导致全表扫描。
s null, is not null 也无法使用索引，在实际中尽量不要使用null。
like 以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作。
如果两边都要用，可以用select age from user where username like ‘%abc%’,其中age是必须是索引列，才可让索引生效
在组合索引中，将有区分度的索引放在前面
如果没有区分度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使得索引失去了意义