本文主要总结了一些工作中常用的操作，以及不合理的操作，以及在优化慢查询时收集到的一些有用的资料和资料。MySQL基金会的开发者。1、索引相关的索引基数：基数是数据列中包含的不同值的个数。例如，一个数据列包含值1、3、7、4、7、3，那么它的基数是4。当它的基数相对于表中的行数（即，该列包含许多不同的值，几乎没有重复值）。如果一个数据列包含许多不同的年龄，索引将快速解析数据行。如果一个数据列用来记录性别（只有“M”和“F”值），那么索引就不是很有用了。如果值几乎均等出现，那么无论你搜索哪个值，你都可能得到一半的数据行。在这些情况下，最好根本不使用索引，因为当查询优化器发现某个值出现在表的数据行的比例很高时，它一般会忽略索引并进行全表扫描。通常的百分比截止值为“30%”。索引失败原因：对索引列的操作，操作包括(+,-,*,/,!,<>,%,like'%_'(%在前面)类型错误，比如字段类型是varchar，wherecondition是usednumber，对索引应用一个内部函数，此时应该创建一个基于函数的索引，如select*fromtemplatetwhereROUND(t.logicdb_id)=1，此时ROUND(t.logicdb_id)应该建索引，从mysql8.0开始支持函数索引，5.7可以支持虚拟列，之前只能创建一个ROUND(t.logicdb_id)列然后维护if条件有or，即使有是条件索引，不会用到（这就是为什么很少有人建议用or的原因），如果要用or，又想让索引生效，只能给其中的每一列加一个索引orcondition如果列类型是string，那么condition数据中一定要用引号，否则不使用索引；B-tree索引为null不会走，isnotnull会走，位图索引为null，isnotnull会走复合索引遵循最左原则建立索引最重要的是根据业务查询频率高的语句来选择和区分使用度高的列作为指标，区分度的计算公式为COUNT(DISTINCTcol)/COUNT(*)。表示字段唯一性的比例，比例越大，我们扫描的记录越少。如果业务有独特性，最好建立唯一键。一方面可以保证数据的正确性，另一方面可以大大提高索引的效率。二、EXPLIAN中有用信息的基本用法是desc或者explain加sqlextendedexplain加sql，然后可以通过showwarnings查看实际执行的语句，也很好用。很多情况下，不同写法的SQL解析后实际执行的代码是一样的。提高性能的特性是索引覆盖（覆盖索引）：要查询的数据在索引上就可以找到，不需要回表到EXTRA列显示使用索引ICP特性（IndexConditionPushdown）：本来，索引只是数据访问的一种访问方式，存储引擎通过索引回表得到的数据会传递给MySQL服务器，在该层进行条件过滤。从5.6版本开始，开启ICP后，如果有些where条件可以使用索引字段，MySQL服务器会将这部分下推到引擎层，索引过滤的where条件可以在存储引擎层过滤数据..EXTRA显示使用索引条件。需要了解的是，mysql的架构图分为服务器层和存储引擎层。索引合并（indexmerge）：对多个索引进行条件扫描，然后合并它们各自的结果（intersect/union）。通常，会使用OR。如果是AND条件，考虑建复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的merge算法和使用的索引extra字段1.usingfilesort：表示MySQL会使用外部索引对数据进行排序，而不是按照索引顺序读取在表中选择。MySQL中不能使用索引完成的排序操作称为“文件排序”。其实不一定是文件排序。在内部，使用快速排序。临时表。常见于排序orderby和分组查询groupby3。usingindex：表示对应的SELECT操作使用覆盖索引（CoveringIndex），避免访问表的数据行，效率好。6.Impossiblewhere：WHERE子句的值始终为false，不能用于获取任何元组7.selecttablesoptimizedaway：在没有GROUPBY子句的情况下，基于索引或MyISAM存储优化MIN/MAX操作引擎优化COUNT(*)操作不需要等到执行阶段才进行计算。优化在查询执行计划生成阶段完成。8.distinct：优化distinct操作，找到第一个匹配的祖先后停止查找相同值的操作注意：使用filesort时使用temporary需要注意这两项。这两项是非常耗性能的。在使用groupby的时候，虽然没有使用orderby，但是如果没有index，可能会同时出现usingfilesort和usingtemporary，因为groupby是先排序再分组。如果不需要排序，可以通过NULL加一个order，避免排序。这样就去掉了使用filesort，可以稍微提高性能。类型字段系统：该表只有一行记录（相当于系统表）。这是const类型的特例，一般不会出现。const：如果通过索引顺序查找，const用于比较主键索引或唯一索引。因为只能匹配一行数据，所以速度很快。如果主键放在where列表中，MySQL可以将查询转化为常量eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与其匹配。常见于主键或唯一索引扫描ref：非唯一索引扫描，返回匹配单个值的所有行。它本质上是一个索引访问，返回与单个值匹配的所有行，但是它可能会找到满足条件的多行，因此它应该属于搜索和扫描范围的混合体：只检索给定范围内的行，使用用于选择行的索引。键列显示使用了哪个索引。一般在你的where语句中会有between,<,>,in等查询。这种范围扫描索引优于全表扫描，因为它只需要从缩略的某一点开始，到另一点结束，不需要扫描整个索引索引：FullIndexScan，索引的区别而ALL是索引类型只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是读全表，index是从索引中读取，ALL是从硬盘中读取）all：FullTableScan，遍历全表，获取匹配的行3.字段类型返回带编码的字符串长度mysql：CHARACTER_LENGTH方法（同CHAR_LENGTH）返回字符数，LENGTH函数返回字节数，一个汉字用三个字节varvhar等字段建立索引长度计算语句：从表中选择count(distinctleft(test,5))/count(*)；越接近1越好。mysql的utf8最大3个字节，不支持emoji表情。您只能使用utf8mb4。需要在mysql配置文件中配置客户端字符集为utf8mb4。jdbc连接串不支持characterEncoding=utf8mb4的配置。最好的方式是在连接池中指定初始化sql，例如：hikari连接池，其他连接池类似spring.datasource.hikari.connection-init-sql=setnamesutf8mb4。否则每次执行sql前都需要执行setnamesutf8mb4。Msyql排序规则（一般使用_bin和_genera_ci）：utf8_genera_ci是case-insensitive，ci是caseinsensitive的缩写，即不区分大小写，utf8_general_cs是case-sensitive，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下：?<>asqlyog指定了初始连接的代码类型，使用连接配置的初始化命令。4.SQL语句总结常用但容易忘记的：如果有主键或唯一键冲突，不插入：insertignoreinto如果有主键或唯一键冲突，则更新，注意这样会影响增量增量：INSERTINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")ONDUPLICATEKEYUPDATEroom_remarks="234"如果有，将其替换为新的。如果值不包含自增列，自增列的值会发生变化：REPLACEINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")Backuptable:CREATETABLEuser_infoSELECT*FROMuser_info复制表结构：CREATETABLEuser_v2LIKEuser从查询语句中导入：INSERTINTOuser_v2SELECT*FROMuser或INSERTINTOuser_v2(id,num)SELECTid,numFROMuserLinktableUPDATE:UPDATEusera,roombSETa.num=a.num+1WHEREa.room_id=b.id删除偶表：DELETEuserFROMuser,blackWHEREuser.id=black.id锁相关（为了理解，很少用到）共享锁：selectidfromtb_testwhereid=1锁定共享模式；独占锁：从tb_test中选择id，其中id=1用于更新优化：强制使用一个索引：select*fromtableforceindex(idx_user)limit2;禁止使用索引：select*fromtableignoreindex(idx_user)limit2;禁用缓存（测试期间去除缓存的影响）：selectSQL_NO_CACHEfromtablelimit2;查看状态和查看字符集SHOWVARIABLESLIKE'character_set%';查看排序规则SHOWVARIABLESLIKE'collat??ion%';写SQL注意where语句的解析顺序是从右到左，尽量放where和nothave。使用deferredjoin技术优化超多分页场景，比如限制10000，10。deferredjoin可以避免distinct语句在回表时的性能损失。可以使用groupby优化join表，尽量不要超过三表5。如果有自增列，truncate语句会将自增列的基数重置为0。在某些场景下，self-业务中使用自增列作为id，聚合函数会自动过滤掉，比如a如果列类型是int，全部为NULL，那么SUM(a)返回NULL，而不是0。mysql判断nulls是否相等，不能使用“a=null”。这个结果会一直是UnKnown，whereandhaving,UnKnown会一直被认为是False，在check约束中，UnKnown会被当作true，所以用"a为null"来处理60,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,00.00业务请求被阻止。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七、慢查询日志有时如果在线请求超时，你应该注意慢查询日志，慢查询的分析很简单，先找到慢查询日志文件所在的位置，然后使用mysqldumpslow进行分析。查询慢查询日志信息，可以直接执行sql命令查看相关变量。常用的sql如下：--查看慢查询配置--slow_query_log是否开启慢查询日志--slow_query_log_file的值为文件中记录的慢查询日志--long_query_time指定慢查询的阈值--log_queries_not_using_indexes是否记录所有不使用索引的查询SHOWVARIABLESLIKE'%quer%';--检查慢查询是日志形式还是表形式SHOWVARIABLESLIKE'log_output'--查看慢查询数量SHOWGLOBALSTATUSLIKE'slow_queries';mysqldumpslow这个工具很简单，我主要用到以下几个参数：-t：限制输出行数，我一般取前十个就够了-s：按什么排序默认是平均查询时间在，我经常用c查询次数，因为查询次数很频繁但是时间不高，需要优化，和t查询次数，看哪个语句特别卡。-v：输出详细信息示例：mysqldumpslow-v-st-t10mysql_slow.log.2018-11-20-05008.查看sql进程并杀掉进程如果执行了sql操作，但还没有返回很长一段时间，可以通过查询进程列表来查看其实际执行情况。如果sql非常耗时，可以使用kill命令杀掉进程，以免影响线路。通过查看进程列表也可以直观的看到当前sql的执行状态，如果当前数据库负载很高，可能会出现在进程列表中，大量进程被夯实，执行时间很长。命令如下：--查看进程列表SHOWPROCESSLIST;--killaprocesskill183665如果你用sqlyog，还有一个图形页面，在菜单栏-工具-显示-进程列表。在进程列表页面，可以右键杀掉进程。如下图：查看进程列表并杀死进程九、数据库性能的一些考虑公司在优化慢查询日志的时候，很多时候可能会忘记建立索引。这样的问题很容易解决，加个索引就行了。.但是有两种情况不是简单的加索引就能解决的：业务代码循环读取数据库：考虑这样一个场景，获取用户粉丝列表信息，添加分页为十。其实像这样的SQL很简单，通过表查询性能也很高，但是有的时候，很多开发用的是取出一串id，然后循环读取每个id的信息，这样如果有id很多，对数据库的压力会很大，性能也很低Statisticssql:很多时候，业务中会有排行榜。我发现公司很多地方都是直接用数据库进行计算的。在对一些大表进行聚合操作时，往往需要五秒以上的时间。这些SQL一般都很长，很难优化，像这样的场景，如果业务允许（比如对一致性要求不高或者只在一定时间后统计），可以专门统计奴隶图书馆内。另外，我建议使用redis缓存来处理这种业务超大分页：在慢查询日志中，发现一些limit40000、1000等超大分页的慢查询，因为mysql分页是在服务端做的层，并且可以使用延迟相关性来减少背表。但是看了相关的业务代码，正常的业务逻辑是不会提出这样的请求的，所以很有可能是恶意用户在刷界面，所以最好在开发的时候给界面加一个check，拦截这些恶意请求.
