目录1.索引相关2.EXPLIAN中的有用信息3.字段类型与编码4.SQL语句总结5.踩坑6.在线修改千万级表Log8.查看sql进程并杀死进程9.对一些数据库性能的思考，本文适合有mysql基础的开发者。一、索引??相关1、索引基数：基数是数据列中包含的不同值的个数。例如，一个数据列包含值1、3、7、4、7、3，那么它的基数为4。当索引的基数相对于数据表的行数较高时效果最好（即该列包含很多不同的值和很少的重复值）。如果一个数据列包含许多不同的年龄，索引将快速解析数据行。如果一个数据列用来记录性别（只有“M”和“F”值），那么索引就不是很有用了。如果值几乎均等出现，那么无论你搜索哪个值，你都可能得到一半的数据行。在这些情况下，最好根本不使用索引，因为当查询优化器发现某个值出现在表的数据行中的比例很高时，它一般会忽略索引并进行全表扫描。通常的百分比截止值为“30%”。2、索引失败的原因：1、对索引列的操作，包括(+、-、??、/、!、<>、%，如'%_'(%在前面)2、类型错误，如字段类型是varchar，where条件用的是number。3.对索引应用内部函数。这种情况下应该建立一个基于函数的索引，比如select*fromtemplatetwhereROUND(t.logicdb_id)=1。此时应该建立ROUND(t.logicdb_id)作为索引，从mysql8开始.0支持函数索引，5.7可以支持虚拟列。在此之前，只能新建一个ROUND(t.logicdb_id)列，然后维护。4.如果条件有or，即使有条件索引，也不会使用（这就是为什么建议少用or），如果要使用or，又想让索引生效，可以只为or条件中的每一列添加索引5.如果列类型是字符串，则条件中的数据必须用引号引起来，否则不要使用索引；6.B-tree索引为null不会走，isnotnull会走，bitmap索引为null，isnotnull会走7.复合索引遵循最左原则索引建立1、最重要必须根据频繁出现查询的业务报表。2、尽量选择鉴别度高的列作为索引。区分度的公式是COUNT(DISTINCTcol)/COUNT(*)。表示非重复字段的比例，比例越大，我们扫描的记录越少二、EXPLIAN有用信息的基本用法1、将你的sql2添加到desc或explain中，将你的sql添加到extendedexplain中，然后就可以查看了实际执行的语句通过显示警告。这个也很好用，很多时候不同的写法用SQL分析出来，实际执行的代码是一样的。Featurestoproduceperformance1.Indexcoverage（覆盖索引）：需要查询的数据都可以在索引上找到，不需要返回表到EXTRA列显示使用索引ConditionPushdown：原来索引只是数据访问的一种访问方式。存储引擎通过索引回表得到的数据会传递给MySQL服务器层进行where条件过滤。从5.6版本开始，开启ICP后，如果某些where条件可以使用索引字段，MySQL服务器会将这部分下推到引擎层，可以使用索引过滤器的where条件在存储处过滤数据引擎层。EXTRA显示使用索引条件。需要了解的是，mysql的架构图分为server层和存储引擎层3.索引合并（indexmerge）：对多个索引进行条件扫描，然后合并各自的结果（intersect/union）。通常，会使用OR。如果是AND条件，考虑建复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的merge算法和使用的索引extra字段1、usingfilesort：表示MySQL会使用外部索引对数据进行排序，而不是按照索引顺序读取在表中选择。MySQL中不能用索引完成的排序操作称为“文件排序”。其实不一定是文件排序。内部使用的是quicksort2.usingtemporary：一张临时表用来保存中间结果，MySQL在对查询结果进行排序时使用临时表。常见于sortingorderby和分组查询groupby3，usingindex：表示对应的SELECT操作使用覆盖索引（CoveringIndex），避免访问表的数据行，效率好。4.Impossiblewhere：WHERE子句的值始终为false，不能用于获取任何元组5.selecttablesoptimizedaway：在没有GROUPBY子句的情况下，根据索引优化MIN/MAX操作或优化MyISAM存储引擎COUNT(*)操作不需要等到执行阶段进行计算，优化在查询执行计划生成阶段完成6.distinct：优化distinct操作，停止查找相同的操作valueafterfindthemostmatchingancestor当这两项出现时需要注意usingtemporary。这两项是非常耗性能的。在使用groupby的时候，虽然没有使用orderby，但是如果没有index，可能会同时出现usingfilesort和usingtemporary，因为groupby是先排序后分组。如果不需要排序，可以通过NULL加一个order来避免排序，这样使用filesort就会去掉，这样可以稍微提高性能。类型字段system：表只有一行记录（相当于系统表），是const类型的特例，const一般不会出现：如果通过索引顺序查找，const用于比较主键索引或唯一索引。因为只能匹配一行数据，所以速度很快。如果主键放在where列表中，MySQL可以将查询转化为常量eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描ref：非唯一索引扫描，返回匹配单个值的所有行。它本质上是一个索引访问，返回与单个值匹配的所有行，但是它可能会找到满足条件的多行，因此它应该属于搜索和扫描范围的混合体：只检索给定范围内的行，使用用于选择行的索引。键列显示使用了哪个索引。一般在你的where语句中会有between,<,>,in等查询。这种范围扫描索引优于全表扫描，因为它只需要从缩略的某一点开始，到另一点结束，不需要扫描整个索引索引：FullIndexScan，索引的区别而ALL是索引类型只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是读全表，index是从索引中读取，ALL是从硬盘中读取）all：FullTableScan，遍历全表，获取匹配的行3.字段类型及编码1、mysql返回字符串长度：CHARACTER_LENGTH方法（同CHAR_LENGTH）返回字符个数，LENGTH函数返回字节数，一个汉字是三个字节2、varvhar等字段建立索引计算长度语句：selectcount(distinctleft(test,5))/count(*)fromtable;越接近1越好3.mysql的utf8***是3个字节，不支持emoji表情，所以只能用utf8mb4。需要在mysql配置文件中配置客户端字符集为utf8mb4。jdbc连接串不支持characterEncoding=utf8mb4的配置。最好的方式是在连接池中指定初始化sql，例如：hikari连接池，其他连接池类似spring.datasource.hikari.connection-init-sql=setnamesutf8mb4。否则每次执行sql前都需要执行setnamesutf8mb4。4.msyql排序规则（一般使用_bin和_genera_ci）：utf8_genera_ci是case-insensitive，ci是caseinsensitive的缩写，即不区分大小写，utf8_general_cs区分大小写，cs是casesensitive的缩写，即大小写sensitive，但是目前的MySQL版本不支持类似于***_genera_cs的排序规则，直接使用utf8_bin代替。utf8_bin将字符串中的每个字符存储为二进制数据，区分大小写。那么，它也是区分大小写的，utf8_general_cs和utf8_bin有什么区别呢？cs是casesensitive的缩写，即区分大小写；bin的意思是binary，即二进制编码比较。在utf8_general_cs排序规则下，即使区分大小写，一些西欧字符和拉丁字符是无法区分的，比如?=a，但有时?=a是不需要的，所以utf8_binutf8_bin的特点在于使用characters二进制编码用于操作，任何不同的二进制编码都是不同的，所以在utf8_bincollat??ion下：?<>a5，sqlyog中初始连接指定编码类型使用连接配置的初始化命令4.SQL语句总结常用但容易忘记：1.主键或唯一键冲突不插入：insertignoreinto2.主键或唯一键冲突则更新。注意这会影响自增increment:INSERTINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")ONDUPLICATEKEYUPDATEroom_remarks="234"3.如果有，换成新的。如果values中不包含自增列，自增列的值会发生变化：REPLACEINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")4.备份表：CREATETABLEuser_infoSELECT*FROMuser_info5。复制表结构：CREATETABLEuser_v2LIKEuser6。从查询语句导入：INSERTINTOuser_v2SELECT*FROMuser或INSERTINTOuser_v2(id,num)SELECTid,numFROMuser7,linktableupdate:UPDATEusera,roombSETa.num=a.num+1WHEREa.room_id=b.id8,linktabledelete:DELETEuserFROMuser,blackWHEREuser.id=black.id锁相关（为了理解，很少用到）1.共享锁：selectidfromtb_testwhereid=1lockinshare模式;2.独占锁：selectidfromtb_testwhereid=1forupd在ate优化中使用：1.强制使用一个索引：select*fromtableforceindex(idx_user)limit2;2.禁止使用一个索引：select*fromtableignoreindex(idx_user)limit2;3、禁用缓存（测试时去除缓存的影响）：selectSQL_NO_CACHEfromtablelimit2；查看状态1、查看字符集SHOWVARIABLESLIKE'character_set%';2、查看排序规则SHOWVARIABLESLIKE'collat??ion%';SQL写法注意事项1、where语句的解析顺序是从右到左，条件尽量多放，而不是having2。使用deferredjoin技术优化超多分页场景，比如limit10000,10。Deferredjoin可以避免回表。3.distinct语句很Loss性能可以通过groupby优化4.尽量不要连接三个以上的表。该列作为业务的id，需要高度重视。2.聚合功能会自动过滤掉空白。比如a列的类型是int，全部为NULL，那么SUM(a)返回NULL，而不是03。mysql判断nulls是否相等，不能使用"a=null"，结果永远是UnKnown，在where和having中，UnKnown会一直被当作false，在check约束中，UnKnown会被当作true来处理，所以使用“a为null”来处理60,000,000,000张表在线修改MySQL表量大的时候数据，如果表结构被修改，表将被锁定，业务请求将被阻塞。mysql在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般使用pt工具（PerconaToolkit），比如给表加索引：如下：pt-online-schema-change--user='root'--host='localhost'--ask-pass--alter"addindexidx_user_id(room_id,create_time)"d=fission_show_room_v2,t=room_favorite_info--execute七、慢查询log有时如果在线请求超时，你应该去关注慢查询日志。慢查询的分析很简单。首先找到慢查询日志文件所在的位置，然后使用mysqldumpslow进行分析。查询慢查询日志信息，可以直接执行sql命令查看相关变量。常用的sql如下：--查看慢查询配置--slow_query_log慢查询日志是否开启--slow_query_log_file值为文件中记录的慢查询日志--long_query_time指定慢查询的阈值--log_queries_not_using_indexes是否记录所有不使用索引的查询SHOWVARIABLELIKE'%quer%';--检查慢查询是日志形式还是表形式SHOWVARIABLELIKE'log_output'--查看慢查询数量mysqldumpslow工具很简单，我主要使用如下参数：-t：限制输出行数，我一般取前十个就够了-s：按什么排序，默认是平均查询时间at，我经常用c查询的次数，因为查询次数很频繁但是耗时不高，所以需要优化，而且还有t查询时间，查看是哪条语句特别卡。-v：输出详细信息示例：mysqldumpslow-v-st-t10mysql_slow.log.2018-11-20-05008.查看sql进程并杀掉进程如果执行了sql操作，但还没有返回很长一段时间，可以通过查询进程列表来查看其实际执行情况。如果sql非常耗时，可以使用kill命令杀掉进程，以免影响线路。通过查看进程列表也可以直观的看到当前sql的执行状态，如果当前数据库负载很高，可能会出现在进程列表中，大量进程被夯实，执行时间很长。命令如下：--查看进程列表SHOWPROCESSLIST;--killaprocesskill183665如果你用sqlyog，还有一个图形页面，在菜单栏-工具-显示-进程列表。在进程列表页面，可以右键杀掉进程。如下图：九、一些数据库性能的考虑公司在优化慢查询日志的时候，很多时候可能会忘记建立索引。这种问题很好解决，加个索引就行了。但是有两种情况不是简单的加索引就可以解决的：1.业务代码循环读取数据库：考虑这样一个场景，获取用户粉丝列表信息，添加十页。其实像这样的SQL很简单。表查询性能也是很高的，但是有的时候，很多开发者会使用一串id，然后循环读取每个id的信息，这样如果id很多的话，对数据库的压力会很大，而且性能也很低。2.统计sql：业务中很多时候都是有排名的。发现公司在很多地方直接使用数据库进行计算。在对一些大表进行聚合操作时，往往需要五秒以上的时间。这些SQL一般都很长很困难。优化，在这种场景下，如果业务允许（比如一致性要求不高或者隔一段时间才统计），可以专门在从库进行统计。另外，我建议使用redis缓存来处理这种业务。3、超大分页：在慢查询日志中，发现了一些limit40000、1000等超大分页的慢查询，因为mysql分页是在server层做的，可以使用delay关联减少回桌子。但是看了相关的业务代码，正常的业务逻辑是不会提出这样的请求的，所以很有可能是恶意用户在刷界面，所以***在开发的时候也在界面中加入了检查，拦截这些恶意的询问.本篇文章到此结束，希望对您有所帮助！
