Nginx配置优化 Nginx配置优化1、隐藏nginx版本信息#不显示nginx版本 server_tokens off2、Buffers缓存另一个很重要的参数为buffer，如果buffer太小，Nginx会不停的写一些临时文件，这样会导致磁盘不停的去读写。client_body_buffer_size 10K; client_header_buffer_size 1k; client_max_body_size 8m; large_client_header_buffers 2 1k;client_body_buffer_size:允许客户端请求的最大单个文件字节数client_header_buffer_size:用于设置客户端请求的Header头缓冲区大小，大部分情况1KB大小足够client_max_body_size:设置客户端能够上传的文件大小，默认为1mlarge_client_header_buffers:该指令用于设置客户端请求的Header头缓冲区大小3、开启Gzip压缩 #开启Gzip压缩 gzip on; #压缩等级 1-9 等级越高，压缩效果越好，节约宽带，但CPU消耗大 gzip_comp_level 2; #最小压缩文件大小 gzip_min_length 1000; #根据某些头部决定是否压缩， gzip_proxied expired no-cache no-store private auth; #压缩类型，默认就已经包含text/html，所以下面就不用再写了，写上去也不会有问题，但是会有一个warn。 gzip_types text/plain application/x-javascript text/xml text/css application/xml; #是否传输gzip压缩标志 gzip_vary on;4、开启高效传输模式 #开启高效传输模式。 sendfile on; #该指令必须在sendfile打开的状态下才会生效，主要是用来提升网络包的传输'效率' tcp_nopush on; #该指令必须在keep-alive连接开启的情况下才生效，来提高网络包传输的'实时性' tcp_nodelay on;5、FastCGI配置相关参数是为了改善网站的性能：减少资源占用，提高访问速度。 #为FastCGI缓存指定一个文件路径、目录结构等级、关键字区域存储时间和非活动删除时间。 fastcgi_cache_path /usr/local/nginx/fastcgi_cache levels=1:2 keys_zone=TEST:10m inactive=5m; #指定连接到后端FastCGI的超时时间。 fastcgi_connect_timeout 300; #指定向FastCGI传送请求的超时时间，这个值是已经完成两次握手后向FastCGI传送请求的超时时间。 fastcgi_send_timeout 300; #指定接收FastCGI应答的超时时间，这个值是已经完成两次握手后接收FastCGI应答的超时时间。 fastcgi_read_timeout 300; #用于指定读取FastCGI应答第一部分需要用多大的缓冲区，这个值表示将使用1个64KB的缓冲区读取应答的第一部分（应答头），可以设置为fastcgi_buffers选项指定的缓冲区大小。 fastcgi_buffer_size 64k; #指定本地需要用多少和多大的缓冲区来缓冲FastCGI的应答请求。如果一个PHP脚本所产生的页面大小为256KB，那么会为其分配4个64KB的缓冲区来缓存；如果页面大小大于256KB，那么大于256KB的部分会缓存到fastcgi_temp指定的路径中，但是这并不是好方法，因为内存中的数据处理速度要快于硬盘。一般这个值应该为站点中PHP脚本所产生的页面大小的中间值，如果站点大部分脚本所产生的页面大小为256KB，那么可以把这个值设置为“16 16k”、“4 64k”等。 fastcgi_buffers 4 64k; #默认值是fastcgi_buffers的两倍。 fastcgi_busy_buffers_size 128k; #表示在写入缓存文件时使用多大的数据块，默认值是fastcgi_buffers的两倍。 fastcgi_temp_file_write_size 128k; #表示开启FastCGI缓存并为其指定一个名称。开启缓存非常有用，可以有效降低CPU的负载，并且防止502错误的发生，但是开启缓存也会引起很多问题，要视具体情况而定。 fastcgi_cache TEST; #用来指定应答代码的缓存时间，实例中的值表示将200和302应答缓存一个小时，将301应答缓存1天，其他应答均缓存1分 fastcgi_cache_valid 200 302 1h; fastcgi_cache_valid 301 1d; fastcgi_cache_valid any 1m;6、超时配置 #客户端连接超时时间，单位是秒 keepalive_timeout 60; #客户端请求头读取超时时间 client_header_timeout 10; #设置客户端请求主体读取超时时间 client_body_timeout 10; #响应客户端超时时间 send_timeout 10;7、expires缓存配置 #对于图片，通常过期时间可以设置为一个月 location ~ \.(gif|jpg|jpeg|png|bmp|ico)$ { expires 30d; } #对js/css,通常过期时间设置为1周 location ~* \.(js|css)$ { expires 7d; }

MySQL优化 索引一、全表扫描没有使用索引的时候，数据的查询需要进行多次IO读写，这样的性能较差——全表扫描的过程。二、索引为数据库的某个字段创建索引，相当是为这个字段的内容创建了一个目录。通过这个目录可以快速的实现数据的定位，也就是通过索引能够快速的找到某条数据所在磁盘的位置。三、索引存放位置InnoDB存储引擎的表：将索引和数据存放在同一个文件里。（为什么？有什么优势？）*ibdMyISAM存储引擎的表：索引和数据分开两个文件来存储。 索引：*.MYI； 数据：MYD四、索引分类1、主键索引：主键自带索效果，也就意味着通过主键来查询表中的记录，性能是非常好的。2、普通索引：为普通列创建的索引。索引名一般格式: **idx_字段名** create index 索引名 on 表明(字段); 例子： create index idx_participant_ on cal_resource_permission(participant_);3、唯一索引：就像是唯一列，列中的数据是唯一的。比普通索引的性能要好。唯一索引名称：idx_unique_列明 create unique index 唯一索引名称 on 表明(列名); 例子： create unique index idx_unique_resource_id_ on cal_resource_permission(resource_id_);4、联合索引（组合索引）：一次性为表中的多个字段一起创建索引,最左前缀法则（如何命中联合索引中的索引列）。注意：一个联合索引建议不要超过5个列格式：联合索引名一般格式: idx_字段1_字段2_字段3 create index 联合索引名 on 表明(字段1,字段2,字段3); 例子： create index idx_resource_scope_type on cal_resource_permission(resource_id_,scope_,type_id_);5、全文索引：进行查询的时候，数据源可能来自于不同的字段或者不同的表。比如去百度中查询数据，yanxizhu，来自于网页的标题或者网页的内容。MyISAM存储引擎支持全文索引。在实际生产环境中，并不会使用MySQL提供的MyISAM存储引擎的全文索引功能来是实现全文查找。而是会使用第三方的搜索引擎中间件比如ElasticSearch（比较多）、Solr。索引使用的数据结构使用索引查找数据性能很快，避免了全表扫描的多次磁盘IO读写。但是我们发现，使用索引实际上也需要在索引中查找数据，而且数据量是一样的，那么凭什么索引就能快呢？这就跟索引使用了哪种数据结构支持快速查找。什么叫数据结构：存放数据的结构。比如：数组、链表、栈、堆、队列等等这些概念。一、线性表线性的维护数据的顺序。对于线性表来说，有两种数据结构来支撑：线性顺序表：相邻两个数据的逻辑关系和物理位置是相同的。线性链式表：相邻两个数据的逻辑关系和物理存放位置没有关系。数据是有先后的逻辑关系，但是数据的物理存储位置并不连续。线性链式表又分为单项链表和双向链表：单向链表：能够通过当前结点找到下一个节点的位置，以此来维护链表的逻辑关系结点结构：数据内容+下一个数据的指针双向链表：能够通过当前结点找到上一个或下一个节点的位置，双向都可找。结点结构：上一个数据的指针+数据内容+下一个数据的指针。时间复杂度（比较次数）/空间复杂度（算法需要使用多少个变量空间）顺序表和链式表的区别：顺序表--数组：进行数据的查询性能（可以通过数组的索引/下标），数组的查询性能非常好，时间复杂度是0（1）数组的增删性能是非常差。链式表--链表：查询的性能是非常差的：时间复杂度是O（n），增删性能是非常好。二、栈、队列、串、广义表栈分为：顺序栈、链式栈顺序栈链式栈栈特点:先进后出，FIFO。队列分为：顺序队列、链式队列顺序队列链式队列队列特点：先进先出。串：String定长串、StringBuffer/Stringbuilder动态串广义表：更加灵活的多维数组，可以在不同的元素中创建不同的维度的数组。树查找树的查找性能是明显比线性表的性能要好，有这么几种树：多叉树、二叉树、二又查找树、平衡二叉树、红黑树、B树、B+树一、多叉树：非二叉树二、二叉树：一个结点最多只能有2个子结点，可以是0、1、2子结点。三、二叉查找树：二又查找树的查找性能是ok的。查询性能跟树的高度有关，树的高度又根你插入数据的顺序有关系。特点：二又树的根结点的数值是比所有左子树的结点的数值大，比右子树的几点的数值小。这样的规律同样满足于他的所有子树。四、平衡二又树，又称为AVL树实际上就是遵循以下两个特点的二叉树：每棵子树中的左子树和右子树的深度差不能超过1；二叉树中每棵子树都要求是平衡二叉树；其实就是在二又树的基础上，若树中每棵子树都满足其左子树和右子树的深度差都不超过1，则这棵二又树就是平衡二又树。二又排序树转化为平衡二叉树为了排除动态查找表中不同的数据排列方式对算法性能的影响，需要考虑在不会破坏二又排序树本身结构的前提下，将二又排序树转化为平衡二叉树，左旋、右旋、双向（先左后右、先右后左）。五、红黑树红黑树是一种特化的AVL树（平衡二叉树）（平衡二叉树的一种体现），都是在进行插入和删除操作时通过特定操作保持二又查找树的平衡，从而获得较高的查找性能。在二又查找树强制一般要求以外，对于任何有效的红黑树我们增加了如下的额外要求：性质1.结点是红色或黑色。性质2.根结点是黑色。性质3.不可能有连在一起的红色节点。性质4.每个红色结点的两个子结点都是黑色。叶子结点都是黑色（nil-黑色的空节点）这些约束强制了红黑树的关键性质：从根到叶子的最长的可能路径不多于最短的可能路径的两倍长。结果是这个树大致上是平衡的。因为操作比如插入、删除和查找某个值的最坏情况时间都要求与树的高度成比例，这个在高度上的理论上限允许红黑树在最坏情况下都是高效的，而不同于普通的二叉查找树。平衡二又树为了维护树的平衡，在一旦不满足平衡的情况就要进行自旋，但是自旋会造成一定的系统开销。因此红黑树在自旋造成的系统开销和减少查询次数之间做了权衡。因此红黑树有时候并不是一颗平衡二叉树。红黑树已经是在查询性能上得到了优化，但索引依然没有使用红黑树作为数据结构来存储数据，因为红黑树在每一层上存放的数据内容是有限的，导致数据量一大，树的深度就变得非常大，于是查询性能非常差。因此索引没有使用红黑树。六、B树B树允许一个结点存放多个数据。这样可以使更小的树的深度来存放更多的数据。但是，B树的一个结点中到底能存放多少个数据，决定了树的深度。七、B+树特点：非叶子结点冗余了叶子结点中的键。叶子结点是从小到大、从左到右排列的。叶子结点之间提供了指针，提高了区间访问的性能。只有叶子结点存放数据。非叶子结点是不存放数据的，只存放键。八、哈希表（散列表）使用哈希表来存取数据的性能是最快的，O（1），但是不支持范围查找（区间访问）。InnoDB和MyISAM的区别InnoDB和MyISAM都是数据库表的存储引擎。那么在互联网公司，或者追求查询性能的场景下，都会使用lnnoDB作为表的存储引擎。为什么？1、InnoDB引擎把索引和数据存放在一个文件中，通过找到索引后就能直接在索引树上得叶子节点中获取完整得数据。------聚集索引可以实现行锁/表锁。2、MyISAM存储引擎把索引和数据存放在两个文件中，查找到索引后还要去另一个文件中找数据，性能会慢一些——非聚集索引。除此之外，MyISAM天然支持表锁，而且支持全文索引。|关键点：InnoDB聚集索引与MyISAM-非聚集索引面试题1.问题一：为什么非主键索引的叶子节点存放的数据是主键值如果普通索引中不存放主键，而存放完整数据，那么就会造成：数据冗余：虽然提升了查询性能，但是需要更多的空间来存放冗余的数据维护麻烦：一个地方修改数据，需要在多棵索引树上修改。2.问题二：为什么InnoDB表必须创建主键创建InnoDB表不使用主键能创建成功吗？如果能创建功能，能不能为这张表的普通列创建索引？如果没有主键，MySQL优化器会给一个虚拟的主键，于是普通索引会使用这个虚拟主键——也会造成性能开销。为了性能考虑，和设计初衷，那么创建表的时候就应该创建主键。3.问题三：为什么使用主键时推荐使用整型的自增主键1）为什么要使用整型：主键-主键索引树-树里的叶子结点和非叶子结点的键存放的是主键的值，而且这颗树是一个二又查找树。数据的存放是有大小顺序的。·整型：大小顺序是很好比较的·字符串：字符串的自然顺序的比较是要进行一次编码成为数值后再进行比较的。（字符串的自然顺序，AZ）uuid随机字符串2）为什么要自增：如果不用自增：（1016。200。18。29）使用不规律的整数来作为主键，那么主键索引树会使用更多的自旋次数来保证树索引树的叶子节点中的数据是从小到大-从左到右排列，因此性能必然比使用了自增主键的性能要差！联合索引和最左前缀法则1.联合索引的特点在使用一个索引来实现多个表中字段的索引效果。2.联合索引是如何存储的3.最左前缀法则最左前缀法则是表示一条sql语句在联合索引中有没有走索引（命中索引/不会全表扫描）#创建联合索引 create index idx_name_repaymentAmount_userName on pay(name,repaymentAmount,userName); #sql是否走联合索引 select * from pay where name="早餐"; //走索引 select * from pay where name="早餐" and repaymentAmount=5.50; //走索引 select * from pay where name="早餐" and repaymentAmount=5.50 and userName="邓凌"; //走索引 select * from pay where repaymentAmount=5.50; //不走索引 select * from pay where name="早餐" and userName="邓凌"; //走一个索引 select * from pay where userName="邓凌"; //不走索引 select * from pay where name="早餐" and userName="邓凌" and repaymentAmount=5.50 ; //走索引，因为mysql内部有优化器SQL优化SQL优化的目的是为了SQL语句能够具备优秀的查询性能，实现这样的目的有很多的途径：工程优化如何实现：数据库标准、表的结构标准、字段的标准、创建索引SQL语句的优化：当前SQL语句有没有命中索引。一、工程优化基础规范表存储引擎必须使用InnoDB表字符集默认使用utf8，必要时候使用utf8mb4通用，无乱码风险，汉字3字节，英文1字节utff8mb4是utf8的超集，有存储4字节例如表情符号时，使用它禁止使用存储过程，视图，触发器，Event对数据库性能影响较大，互联网业务，能让站点层和服务层干的事情，不要交到数据库层调试，排错，迁移都比较困难，扩展性较差禁止在数据库中存储大文件，例如照片，可以将大文件存储在对象存储系统，数据库中存储路径禁止在线上环境做数据库压力测试测试，开发，线上数据库环境必须隔离命名规范库名，表名，列名必须用小写，采用下划线分隔 tb_book t_bookabc，Abc，ABC都是给自己埋坑库名，表名，列名必须见名知义，长度不要超过32字符tmp，wushan谁TM知道这些库是干嘛的库备份必须以bak为前缀，以日期为后缀从库必须以-s为后缀备库必须以-ss为后缀表设计规范单实例表个数必须控制在2000个以内·单表分表个数必须控制在1024个以内表必须有主键，推荐使用UNSIGNED整数为主键删除无主键的表，如果是row模式的主从架构，从库会挂住禁止使用物理外键，如果要保证完整性，应由应用程式实现外键使得表之间相互耦合，影响 update/delete等SQL性能，有可能造成死锁，高并发情况下容易成为数据库瓶颈建议将大字段，访问频度低的字段拆分到单独的表中存储，分离冷热数据（具体参考：《如何实施数据库垂直拆分》）列设计规范根据业务区分使用tinyint/int/bigint，分别会占用1/4/8字节根据业务区分使用char/varchar字段长度固定，或者长度近似的业务场景，适合使用char，能够减少碎片，查询性能高字段长度相差较大，或者更新较少的业务场景，适合使用varchar，能够减少空间根据业务区分使用datetime/timestamp前者占用5个字节，后者占用4个字节，存储年使用YEAR，存储日期使用DATE，存储时间使用datetime必须把字段定义为NoT NULL并设默认值NULL的列使用索引，索引统计，值都更加复杂，MySQL 更难优化NULL需要更多的存储空间NULL只能采用Is NUL或者IS NoT NULL，而在=/！=/in/not in 时有大坑使用INT UNSIGNED存储IPv4，不要用char（15）使用varchar（20）存储手机号，不要使用整数牵扯到国家代号，可能出现+/-/（）等字符，例如+86手机号不会用来做数学运算varchar 可以模糊查询，例如like'138%'使用TINYINT来代替ENUM。ENUM增加新值要进行DDL操作索引规范唯一索引使用uniq_[字段名]来命名非唯一索引使用idx_[字段名]来命名单张表索引数量建议控制在5个以内互联网高并发业务，太多索引会影响写性能生成执行计划时，如果索引太多，会降低性能，并可能导致MySQL 选择不到最优索引异常复杂的查询需求，可以选择Es等更为适合的方式存储组合索引字段数不建议超过5个如果5个字段还不能极大缩小row范围，八成是设计有问题不建议在频繁更新的字段上建立索引非必要不要进行JoIN查询，如果要进行JoIN查询，被JorN的字段必须类型相同，并建立索引踩过因为JoIN字段类型不一致，而导致全表扫描的坑么？理解组合索引最左前缀原则，避免重复建设索引，如果建立了（a，b，c），相当于建立了（a），（a，b），（a，b，c）二、Explain执行计划--SQL优化神奇1、Explain介绍在完成工程结构的优化之后，数据库、表、表中的字段及表的索引，都能够支持海量数据的快速查找。但是查找依然需要通过SQL语句来实现，因此性能优秀的SQL语句是能够走索引，快速查找到数据的。性能不Ok的SQL语句，就不会走索引，导致全表扫描。阿里云rds提供了慢sql的查询功能。找到慢SQL后，如何做优化，以及如何查询一条SQL语句是否是慢SQL，那就可以通过Explain工具。通过在SQL语句前面加上Explain关键来执行，于是就能看出当前SQL语句的执行性能。得知道当前系统里有哪些SQL是慢SQL，查询性能超过1s的sql，然后再通过Explain工具可以对当前SQL语句的性能进行判断——为什么慢，怎么解决。要想知道哪些SQL是慢SQL，有两种方式，一种是开启本地MySQL的慢查询日志；另一种是阿里云提供的RDS（第三方部署的MySQL服务器），提供了查询慢SQL的功能。explain select * from pay where name like "早%";2、MySQL内部优化器在SQL查询开始之前，MySQL内部优化器会进行一次自我优化，让这一次的查询性能尽可能的好。查看内部优化器优化后结果：explain select * from pay where name like "早%"; show WARNINGS;内部优化器优化后结果：/* select#1 */ select `family`.`pay`.`id` AS `id`,`family`.`pay`.`name` AS `name`,`family`.`pay`.`repaymentAmount` AS `repaymentAmount`,`family`.`pay`.`repaymentTime` AS `repaymentTime`,`family`.`pay`.`repaymentUser` AS `repaymentUser`,`family`.`pay`.`userName` AS `userName`,`family`.`pay`.`updateTime` AS `updateTime`,`family`.`pay`.`description` AS `description` from `family`.`pay` where (`family`.`pay`.`name` like '早%')3、Explain细节关闭MySQL对衍生表的优化：set session optimizer_switch='derived_merge=off'一、select_type该列描述了查询的类型：simple：简单查询primary：外部的主查询derived：在from后面进行的子查询，会产生衍生表subquery：在from的前面进行的子查询union：进行的联合查询explain select (select 1 from type where id=1) from (select * from pay where id=1) des; show WARNINGS;结果：derived##第一条执行sql select * from pay where id=1第一条执行的sql是from后面的子查询，该子查询只要在from后面，就会生成一张衍生表，因此他的查询类型：derivedsubqueryselect 1 from type where id=1在select之后from之前的子查询primary最外部的查询simple不包含子查询的简单的查询unionexplain select * from pay where id=1 union select * from pay where id=2结果：使用union进行的联合查询二、table列这一列表示该sql正在访问哪一张表。也可以看出正在访问的衍生表三、tyge列通过Type列，可以直接的看出SQL语句的查询性能，性能从大到小的排列：null>system>const>eq_ref>ref>range>index>ALL一般情况下我们要保证我们的sql类型的性能是range级别。不同的级别的情况：null性能最好的，一般在查询时使用了聚合函数，于是直接从索引树里获取数据，而不用查询表中的记录。explain select min(id) from pay;const在进行查询时，查询的条件，使用了主键列或者唯一索引列的值与常量进行比较，这种性能是非常快的，所以是constsystem是const的特殊情况，一般在衍生表里，直接匹配一条记录，就是systemeq_ref如果顺序一直，按按顺序执行。在进行多表连接查询时。如果查询条件是使用了主键进行比较，那么当前查询类型是eq_refEXPLAIN select * from pay p left join type t on t.id=p.id;ref简单查询：使用普通索引列作为查询条件EXPLAIN select * from pay where name="早餐";复杂查询里：在进行连接查询时，连接查询的条件中使用了本表的联合索引列，因此这种类型的sql就是refrange在索引列上使用了范围查找，性能是ok的。EXPLAIN select * from pay where id<5;index在查询表中的所有的记录，但是所有的记录可以直接从索引树上获取，因此这种sql的查询类型就是index（pay表所有列都有索引）EXPLAIN select name from payall没有走索引，进行全表扫描。全表扫描。就是要从头到尾对表中的数据扫描一遍。这种查询性能是一定要做优化的。4、id列在多个select中，id越大越先执行，如果id相同。上面的先执行。5、possible_keys这一次查询可能使用到的索引（名称）。为什么要设计possiblekey这一列。因为在实际MySQL内部优化器做优化选择时，虽然我们写的sql语句是使用了索引列，但是MySQL内部优化器发现，使用索引列查找的性能并没有比全表扫描的性能要好（可以通过trace工具来查看），于是MySQL内部优化器就选择使用全表扫描。explain select * from pay where name like "早%"结果：可能使用idx_name_repaymentAmount_userName联合索引，实际上没用到。一共有142条记录，使用索引可能也需要扫描142条数据，使用索引效率可能还没有使用全表扫描快。6、key实际sql使用的索引7、rows列该sql语句可能要查询的数据条数8、key_len列键的长度，通过这一列可以让我们知道当前命中了联合索引中的哪几列。name长度是74，也就是当看到key-len是74，表示使用了联合索引中的name列。ken_len计算规则：-字符串1.char（n）：n字节长度2.varchar（n）：2字节存储字符串长度，如果是utf-8，则长度3n+2-数值类型1.tinyint：1字节2.smallint：2字节3.int：4字节4.bigint：8字节--时间类型1、date：3字节2、timestamp：4字节3、datetime：8字节如果字段允许位null，需要1字节记录是否位null。索引最大长度768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，讲前半部分的字符提取出来做索引。9、extraextra列提供了额外的信息，是能够帮助我们判断当前sql的是否使用了覆盖索引、文件排序、使用了索引进行查询条件等等的信息。using index覆盖索引：所谓的覆盖索引，指的是当前查询的所有数据字段都是索引列，这就意味着可以直接从索引列中获取数据，而不需要进行查表。使用了覆盖索引explain select repaymentAmount from pay where name ="早餐";using wherewhere的条件没有使用索引列。这种性能不ok的，我们如果条件允许可以给列设置索引，也同样尽可能的使用覆盖索引。explain select * from pay where name like "早%"using index condition查询的列没有完全被索引覆盖，并且where条件中使用普通索引。using temporary会创建临时表来执行，比如在没有索引的列上执行去重操作，就需要临时表来实现。explain select distinct(description) from pay;这种情况可以通过给列加索引进行优化。using filesortMySQL对数据进行排序，都会使用磁盘来完成，可能会借助内存，设计到两个概念：单路排序、双路排序explain select * from pay ORDER BY description;select tables optimized away当直接在索引列上使用聚合函数，意味着不需要操作表。explain select min(id) from pay;编写SQL注意点等值匹配：下面3个都命中：最左前缀法：1、2没命中，4name，age命中。不能在索引列上做计算、函数、类型转换：日志查找如何处理：尽量使用覆盖索引使用不等于(!=或者<>)会导致全表扫描使用is null、is not null会导致全表扫描使用like以通配符开头('%abc..')会导致全表扫描字符串不加单引号会到会导致全表扫描少用or或in，MySQL内部优化器可能不走索引in进行几千条数据查询时，解决方案：在后端通过多线程countDownlatch将几千条数据拆分，通过多个线程每个查询1000条进行结果汇总。范围查询优化Trace工具在执行计划中我们发现有的sql会走索引，有的sql即使明确使用了索引也不会走索引。这是因为mysql的内部优化器任务走索引的性能比不走索引全表扫描的性能要差，因此mysql内部优化器选择了使用全表扫描。依据来自于trace工具的结论。1、开启Traceset session optimizer_trace="enabled=on",end_markers_in_json=on; --开启Traceexplain select * from pay where name>1; --查询 SELECT * FROM information_schema.OPTIMIZER_ ; --获得TRACE信息Trace信息：{ "steps": [ { "join_preparation": { --- 阶段1：准备阶段 "select#": 1, "steps": [ { ----sql变成成下面这种 "expanded_query": "/* select#1 */ select `pay`.`id` AS `id`,`pay`.`name` AS `name`,`pay`.`repaymentAmount` AS `repaymentAmount`,`pay`.`repaymentTime` AS `repaymentTime`,`pay`.`repaymentUser` AS `repaymentUser`,`pay`.`userName` AS `userName`,`pay`.`updateTime` AS `updateTime`,`pay`.`description` AS `description` from `pay` where (`pay`.`name` > 1)" } ] /* steps */ } /* join_preparation */ }, { "join_optimization": { ----阶段2：进入优化阶段 "select#": 1, "steps": [ { "condition_processing": { ---条件处理 "condition": "WHERE", "original_condition": "(`pay`.`name` > 1)", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "(`pay`.`name` > 1)" }, { "transformation": "constant_propagation", "resulting_condition": "(`pay`.`name` > 1)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "(`pay`.`name` > 1)" } ] /* steps */ } /* condition_processing */ }, { "substitute_generated_columns": { } /* substitute_generated_columns */ }, { "table_dependencies": [ -----表依赖详情 { "table": "`pay`", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [ ] /* depends_on_map_bits */ } ] /* table_dependencies */ }, { "ref_optimizer_key_uses": [ ] /* ref_optimizer_key_uses */ }, { "rows_estimation": [ { "table": "`pay`", "range_analysis": { "table_scan": {--表的扫描 "rows": 142, --扫描行数 "cost": 31.5 --花费时间 } /* table_scan */, "potential_range_indexes": [ ----（关键）可能使用的索引 { "index": "PRIMARY", ---主键索引 "usable": false, "cause": "not_applicable" }, { "index": "idx_name_repaymentAmount_userName", ---联合索引 "usable": true, --是否可用 "key_parts": [ "name", "repaymentAmount", "userName", "id" ] /* key_parts */ }, { "index": "idx_id", "usable": false, "cause": "not_applicable" } ] /* potential_range_indexes */, "setup_range_conditions": [ ] /* setup_range_conditions */, "group_index_range": { "chosen": false, "cause": "not_group_by_or_distinct" } /* group_index_range */ } /* range_analysis */ } ] /* rows_estimation */ }, { "considered_execution_plans": [---分析执行计划 { "plan_prefix": [ ] /* plan_prefix */, "table": "`pay`", "best_access_path": { ---最优访问路径 "considered_access_paths": [ --最后选择访问的路径 { "rows_to_scan": 142, --全表扫描行数 "access_type": "scan", --全表扫描 "resulting_rows": 47.329, --结果的行数 "cost": 29.4, ---花费时间 "chosen": true --选择这种方式 } ] /* considered_access_paths */ } /* best_access_path */, "condition_filtering_pct": 100, "rows_for_plan": 47.329, "cost_for_plan": 29.4, "chosen": true } ] /* considered_execution_plans */ }, { "attaching_conditions_to_tables": { "original_condition": "(`pay`.`name` > 1)", "attached_conditions_computation": [ ] /* attached_conditions_computation */, "attached_conditions_summary": [ { "table": "`pay`", "attached": "(`pay`.`name` > 1)" } ] /* attached_conditions_summary */ } /* attaching_conditions_to_tables */ }, { "refine_plan": [ { "table": "`pay`" } ] /* refine_plan */ } ] /* steps */ } /* join_optimization */ }, { "join_explain": { "select#": 1, "steps": [ ] /* steps */ } /* join_explain */ } ] /* steps */ }常见优化方案、1、Order by在Order by中，如果排序会造成文件排序（在磁盘中完成排序，这样的性能会比较差），那么就说明sql没有命中索引，怎么解决？可以使用最左前缀法则，让排序遵循最左前缀法则，避免文件排序。优化手段：如果排序的字段创建了联合索引，那么尽量在业务不冲突的情况下，遵循最左前缀法则来写排序语句。如果文件排序没办法避免，那么尽量想办法使用覆盖索引。all-->index2.Group by优化group by 本质上是先排序后分组，所以排序优化参考Order by优化。3.文件排序的执行原理MySQL在进行文件排序时，会设置一个缓冲区，比较要排序的所有列的数据大小之和，与max_length_for_sort_data（默认是1024个字节）进行比较。如果比系统变量小，那么执行单路排序，反正执行双路排序。单路排序：把所有的数据扔到sort_buffer内存缓冲区中，进行排序，然后结束双路排序：取数据的排序字段和主键字段，在内存缓冲区中排序完成后，将主键字段做一次回表查询，获取完整数据。4、分页查询优化优化方式一：比较少见，因为有curd注意：主键是连续的，如果删除了，获取到的数据条数不对。优化方式二：先覆盖查询，在到小范围里面全表扫描5、join连表查询优化方式一：在join查询中，如果关联字段建立了索引，mysql就会使用nlj算法，去找小表（数据量比较小的表）作为驱动表，先从驱动表中读一行数据，然后拿这一行数据去被驱动表（数据量比较大的表）中做查询。这样的大表和小表是由mysql内部优化器来决定的，跟sql语句中表的书写顺序无关。—NLJ算法（nested loop join：嵌套循环join）方式二：如果没有索引，会创建一个join buffer内存缓冲区，把小表数据存进来（为什么不存大表，因为缓冲区大小限制，及存数据消耗性能的考虑），用内存缓冲区中100行记录去和大表中的1万行记录进行比较，比较的过程依然是在内存中进行的。索引join buffer起到了提高join效率的效果。-BNLJ算法（block nested loop join：块嵌套循环join）结论：如果使用join查询，那么join的两个表的关联字段一定要创建索引，而且字段的长度类型一定是要一致的（在建表时就要做好），否则索引会失效，会使用BNLJ算法，全表扫描的效果。6、in和exits优化在sql中如果A表是大表，B表是小表，那么使用in会更加合适。反之应该使用exists。in：前提B数据量<A数据量select * from A where id in (select id from B);exists: B的数据量>A的数据量select * from A where exists (select id from B where B.id=A.id);select id from B where B.id=A.id ---会返回true/false,true才查询B7、count优化对于count的优化应该是架构层面的优化，因为count的统计是在一个产品会经常出现，而且每个用户访问，所以对于访问频率过高的数据建议维护在缓存中。锁的定义和分类1、锁的定义锁是用来解决多个任务（线程、进程）在并发访问同一共享资源时带来的数据安全问题。虽然使用锁解决了数据安全问题，但是会带来性能的影响，频繁使用锁的程序的性能是必然很差的。对于数据管理软件MySQL来说，必然会到任务的并发访问。那么MySQL是怎么样在数据安全和性能上做权衡的呢？——MVCC设计思2、锁的分类1、）从性能上划分：乐观锁和悲观锁悲观锁：悲观的认为当前的并发是非常严重的，所以在任何时候操作都是互斥。保证了线程的安全，但牺牲了并发性。乐观锁：乐观的认为当前的并发并不严重，因此对于读的情况，大家都可以进行，但是对于写的情况，再进行上锁。以CAS自旋锁在某种性能是ok的，但是频繁自旋会消耗很大的资源。2、）从数据的操作细粒度上划分：表锁和行锁表锁：对整张表上锁行锁：对表中的某一行上锁。3、）从数据库的操作类型上划分：读锁和写锁，这2中锁都是悲观锁。读锁（共享锁）：对于同一行数据进行”读“来说，是可以同时进行但是写不行。写锁（拍他锁）：在上了写锁之后，及释放写锁之前，在整个过程中是不能进行任何的其他并发操作（其他任务的读和写是都不能进行的）。表锁：对整张表进行上锁。MyISAM存储引擎是天然支持表锁的，也就是说在MyISAM的存储引擎的表中如果出现并发的情况，将会出现表锁的效果。MyISAM不支持事务。读锁：其他任务可以进行读，但是不能进行写写锁：其他任务不能进行读和写。行锁：MyISAM只支持表锁，但不支持行锁，InnoDB可以支持行锁。在并发事务里，每个事务的增删改的操作相当于是上了行锁。MVCC设计思想MySQL为了权衡数据安全和性能，使用了MVCC多版本并发控制的设计。MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。MVCC是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。1、事务的特性原子性：一个事务是一个最小的操作单位（原子），多条sql语句在一个事务中要么同时成功，要么同时失败。一致性：事务提交之前和回滚之后的数据是一致的。持久性：事务一旦提交，对数据的影响是持久的。隔离性：多个事务在并发访问下，提供了一套隔离机制，不同的隔离级别会有不同的并发效果。2、事务的隔离级别幻读：开启2个事务（隔离级别位可重复读），2个事务进行查询，第一个事务插入了一条记录，然后提交。第二个事务，再次插入相同id记录时，提示id重复，查询却看不到第一个事务插入的记录。这种现象就是幻读(虚度).解决办法，将隔离级别设置成Serializable串行化，但是却西能了性能。另一种解决方案：通过上行锁来解决虚读问题。MVCC思想解读：MySQL在读和写的操作中，对读的性能做了并发性的保障，让所有的读都是快照读，对于写的时候，进行版本控制，如果真实数据的版本比快照版本要新，那么写之前就要进行版本（快照）更新，这样就可以既能够提高读的并发性，又能够保证写的数据安全。死锁和间歇锁1、死锁所谓的死锁，就是开启的锁没有办法关闭，导致资源的访问因为无法获得锁而处于阻塞状态。演示：事务A和事物B相互持有对方需要的锁而不释放，造成死锁的情况。2、间隙锁行锁只能对某一行上锁，如果相对某一个范围上锁，就可以使用间隙锁。间隙锁给的条件where id>13 and id<19，会对13和19所处的间隙进行上锁。