本文主要总结了一些工作中常用的操作和不合理的操作，以及在优化慢查询时收集到的一些有用的资料和信息。本文适合有MySQL基础的开发人员。IndexDependentIndexCardinality基数是一个数据列包含的不同值的个数，比如一个数据列包含值1、3、7、4、7、3，那么它的基数就是4。它起作用了当索引的基数相对于数据表中的行数较高时（即该列包含许多不同的值和很少的重复值）时最好。如果一个数据列包含很多不同的年龄段，索引会很快区分数据行；如果一个数据列用来记录性别（只有“M”和“F”值），那么索引就没有多大用处；如果值几乎均等出现，那么无论你搜索哪个值，你都可能得到一半的数据行。在这些情况下，最好根本不使用索引，因为当查询优化器发现某个值出现在表的数据行中的比例很高时，它一般会忽略索引并进行全表扫描。通常的百分比截止值为“30%”。索引失败的原因索引失败的原因有：对索引列的操作，包括(+,-,*,/,!,<>,%,like'%_'(%infront)。类型错误，如果字段类型为varchar，where条件使用number，对索引应用内部函数，此时需要创建函数式索引，例如select*fromtemplatetwhereROUND(t.logicdb_id)=1,此时应该建ROUND(t.logicdb_id)是一个索引，MySQL8.0开始支持函数式索引，5.7可以通过虚拟列支持，之前只能建一个ROUND(t.logicdb_id)列然后维护，如果条件有or，即使有条件也不会和索引一起使用（这也是为什么建议少用or的原因），如果要用or又想让索引生效，只能给or条件中的每一列加索引，如果列类型是字符串，那么条件中的数据一定要用引号，o否则索引将不会被使用。B树索引为null不会走，isnotnull会走，位图索引为null，isnotnull会走。复合索引遵循最左原则。建立索引建立索引需要注意以下几点：最重要的一定是根据业务经常查询的语句。尽量选择鉴别度高的列作为索引。判别度的公式为COUNT(DISTINCTcol)/COUNT(*)，代表字段不重复的比例。比率越大，我们扫描的记录就越少。如果保证业务中的唯一性，建立唯一键，一方面可以保证数据的正确性，另一方面可以大大提高索引的效率。EXPLIAN中的有用信息EXPLIAN的基本用法如下：desc或explain加上你的SQL。extendedexplain加上你的SQL，然后你可以通过showwarnings查看实际执行的语句，也很好用。很多时候，不同写法的SQL分析后，实际执行的代码是一样的。EXPLIAN的性能提升特性性能提升特性如下：索引覆盖（coveringindex）：需要查询的数据可以在索引上找到，不需要将表的EXTRA列返回到显示使用索引。ICP特性（IndexConditionPushdown）：本来索引只是数据访问的一种访问方式，存储引擎通过索引回表得到的数据会传递给MySQLServer层进行where条件过滤。从5.6版本开始，开启ICP后，如果有些where条件可以使用索引字段，MySQLServer会把这部分下推到引擎层，可以使用索引过滤的where条件在存储引擎层过滤数据。EXTRA显示使用索引条件。需要了解的是，MySQL的架构图分为Server层和存储引擎层。索引合并：有条件地分别扫描多个索引，然后合并各自的结果（intersect/union）。一般还是会用。如果是AND条件，考虑建复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的merge算法和使用的索引。ExtrafieldExtrafielduses:usingfilesort:表示MySQL将使用外部索引对数据进行排序，而不是按照表中的索引顺序读取。MySQL中不能使用索引完成的排序操作称为“文件排序”。其实不一定是文件排序，内部用的是quicksort。usingtemporary：使用临时表保存中间结果，MySQL在对查询结果进行排序时使用临时表。常见于排序orderby和分组查询groupby。usingindex：表示在对应的SELECT操作中使用覆盖索引（CoveringIndex），避免访问表的数据行，效率较好。impossiblewhere：where子句的值始终为false，不能用来获取任何元组。selecttablesoptimizedaway：在没有groupby子句的情况下，根据索引优化MIN/MAX操作或者针对MyISAM存储引擎优化COUNT(*)操作。您不必等到执行阶段才执行计算。查询执行计划生成阶段完成。优化。distinct：优化distinct操作，找到最匹配的tuple后停止找同值操作。当出现usingfilesort和usingtemporary这两项时，需要注意。这两项是非常耗性能的。使用groupby时，虽然没有使用orderby，但是如果没有index，可能会同时出现usingfilesort和usingtemporary。因为groupby是先排序再分组，如果不需要排序，可以加一个orderbyNULL来避免排序，所以使用filesort会去掉，可以提高一点性能。typefield类型字段使用：system：表只有一行记录（相当于system表），是const类型的特例，通常不会出现。const：如果是依次通过索引找到的，const用于比较主键索引或者唯一索引。因为只能匹配一行数据，所以速度很快。如果将主键放在where列表中，MySQL可以将查询转换为常量。eq_ref：唯一索引扫描，对于每一个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。ref：非唯一索引扫描，返回匹配单个值的所有行。它本质上是一种索引访问，返回与单个值匹配的所有行，但是它可能会找到满足条件的多行，因此它应该是搜索和扫描的混合体。范围：仅检索给定范围的行，使用索引来选择行。key列显示使用了哪个索引，一般就是查询wherebetween,<,>,in等出现在你的where语句中。这种范围扫描索引优于全表扫描，因为它只需要从缩略图中的一个点开始，在另一个点结束，而不用扫描整个索引。index：FullIndexScan，index和ALL的区别是索引类型只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。也就是说，虽然ALL和index都是读全表，但是index是从索引中读取，而ALL是从硬盘中读取。all：FullTableScan，遍历整个表，获取匹配的行。字段类型和编码MySQL返回字符串长度CHARACTER_LENGTH（同CHAR_LENGTH）方法返回字符个数，LENGTH函数返回字节数，一个汉字为三个字节。varchar等字段创建索引长度计算语句selectcount(distinctleft(test,5))/count(*)fromtable;越接近1越好。MySQL的utf8MySQL的utf8***是3个字节，不支持emoji表情，只能用utf8mb4。需要在MySQL配置文件中配置客户端字符集为utf8mb4。JDBC连接字符串不支持characterEncoding=utf8mb4的配置。最好的办法是在连接池中指定初始化SQL。比如：hikari连接池，其他的连接池和spring类似。数据源。光。connection-init-sql=setnamesutf8mb4.否则每次执行SQL前都需要执行setnamesutf8mb4。MySQLcollat??ion一般使用_bin和_genera_ci：utf8_genera_ci是case-insensitive，ci是caseinsensitive的缩写，即不区分大小写。utf8_general_cs是区分大小写的，cs是casesensitive的缩写，即区分大小写。但是目前的MySQL版本不支持类似***_genera_cs的排序规则，所以改用utf8_bin。utf8_bin将字符串中的每个字符存储为二进制数据，区分大小写。那么，它也是区分大小写的，utf8_general_cs和utf8_bin有什么区别呢？cs是casesensitive的缩写，即区分大小写；bin的意思是binary，即二进制编码比较。在utf8_general_cscollat??ion下，即使区分大小写，一些西欧字符和拉丁字符是无法区分的，比如?=a，但有时?=a是不需要的，所以可以使用utf8_bin。utf8_bin的特点是使用字符的二进制编码进行运算，任何不同的二进制编码都是不同的，所以在utf8_bincollat??ion下：?<>a。初始化命令在SQLyog中，初始连接指定编码类型，使用连接配置的初始化命令，如下图：SQL语句总结常用但容易忘记进去。如果存在主键或唯一键冲突，则更新。请注意，这将影响增量增量：INSERTINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")ONDUPLICATEKEYUPDATEroom_remarks="234"。如果有，请更换新的。如果values不包含自增列，自增列的值会发生变化：REPLACEINTOroom_remarks(room_id,room_remarks)VALUE(1,"sdf")。备份表：CREATETABLEuser_infoSELECT*FROMuser_info。复制表结构：CREATETABLEuser_v2LIKEuser。从查询语句导入：INSERTINTOuser_v2SELECT*FROMuser或INSERTINTOuser_v2(id,num)SELECTid,numFROMuser。链接表更新：UPDATEusera,roombSETa.num=a.num+1WHEREa.room_id=b.id。删除偶表：DELETEuserFROMuser,blackWHEREuser.id=black.id。Lock-related锁相关（作为理解，很少用到）：共享锁：selectidfromtb_testwhereid=1共享模式的锁。独占锁：selectidfromtb_testwhereid=1forupdate。用于优化：用于优化：强制使用一个索引：select*fromtableforceindex(idx_user)limit2.禁止使用一个索引：select*fromtableignoreindex(idx_user)limit2.禁用缓存(测试时去除缓存的影响）：selectSQL_NO_CACHEfromtablelimit2.查看状态查看状态：查看字符集：SHOWVARIABLESLIKE'character_set%'。查看排序规则：显示像“排序规则%”这样的变量。SQL书写注意事项SQL书写注意事项：where语句的解析顺序是从右到左，条件尽量放在where而不是having。使用deferredjoin技术优化超多分页场景，比如限制10000，10，deferredjoin可以避免回表。distinct语句比较消耗性能，可以通过groupby优化。连表尽量不要超过三张表。踩坑踩坑总结如下：如果有自增列，truncate语句会将自增列的基数重置为0。在某些场景下，使用自增列作为业务ID需要非常关注。聚合函数会自动过滤掉空白。比如a列的类型是int，而且都是NULL，那么SUM(a)返回的是NULL，而不是0。MySQL不能用“a=null”来判断null是否相等。结果总是未知的。在where和having中，UnKnown总是被认为是假的。在检查约束中，UnKnown被视为true。所以用“aisnull”来处理。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--executeslowquerylog有时在线请求超时，需要注意慢查询日志。慢查询的分析很简单。首先找到慢查询日志文件所在的位置，然后使用mysqldumpslow进行分析。查询慢查询日志信息，可以通过执行SQL命令直接查看相关变量。常用的SQL如下：mysqldumpslow这个工具很简单，我主要使用的参数如下：-t：限制输出行数，我一般取前十个。向上。-s：按什么排序，默认是平均查询时间at，我经常用c查询次数，因为查询次数很频繁但是占用时间不高，需要优化，还有t查询时间，检查哪个语句特别卡住。-v：输出详细信息。示例：mysqldumpslow-v-st-t10mysql_slow.log.2018-11-20-0500。检查SQL进程并杀死进程。如果你执行了一个SQL操作，但是很长时间没有返回，你可以通过查询进程列表来查看它的实际执行情况。如果SQL非常耗时，可以使用kill命令杀掉进程，以免影响线路。通过查看进程列表也可以直观的看到当前SQL执行状态；如果当前数据库负载很高，它可能会出现在进程列表中。大量进程被夯实，执行时间很长。命令如下：--查看进程列表SHOWPROCESSLIST;--杀死某个进程kill183665如果你使用SQLyog，还有一个图形页面，在菜单栏→工具→显示→进程列表。在进程列表页面，可以右键杀进程，如下图：数据库性能的一些考虑公司在优化慢查询日志的时候，很多时候可能会忘记建立索引。这种问题好解决，加个索引就行了。但是有几种情况不是简单的加索引就能解决的：业务代码循环读取数据库。考虑这样一个场景，获取用户粉丝列表信息，添加十个页面。其实像这样的SQL很简单，而且可以通过连表查询，性能也很高。但是有的时候，很多开发取出一串ID，然后循环读取每个ID的信息，这样如果ID很多的话，对数据库的压力会很大，性能也会很低。统计SQL很多时候，业务中都会有排名。发现公司很多地方直接使用数据库进行计算。在对一些大表进行聚合操作时，往往需要五秒以上的时间。这些SQL一般都很长，很难优化。.在这样的场景下，如果业务允许（比如一致性要求不高或者隔一段时间才统计），可以专门在从库进行统计。另外，我建议使用Redis缓存来处理这种业务。超大分页在慢查询日志中发现了一些像Limit40000、1000这样超大分页的慢查询，因为MySQL的分页是在server层做的，可以通过延迟关联来减少回表。但是看了相关的业务代码，正常的业务逻辑是不会发出这样的请求的，所以很有可能是恶意用户在刷界面，***在开发的时候还在界面上加了一个check来拦截这些恶意请求.
