MySQL使用总结

索引

建立索引

索引的基数相对于数据表行数较高，工作效果最好。说明列中不同值较多，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描，惯用的百分比界线是“30%”，

索引失效

• 对索引列运算，运算包括（+、-、*、/、！、<>、%、like’%_’（%放在前面）；
• 类型错误，如字段类型为varchar，where条件用number；
• 对索引应用内部函数，这种情况下应该要建立基于函数的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此时应该建ROUND (t.logicdb_id)为索引，MySQL8.0开始支持函数索引，5.7可以通过虚拟列的方式来支持，之前只能新建一个ROUND (t.logicdb_id)列然后去维护；
• 如果条件有or，即使其中有条件带索引也不会使用（建议少使用or），如果想使用or，又想索引有效，只能将or条件中的每个列加上索引；
• 如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引；
• 组合索引遵循最左原则。
• B-tree索引 is null不会走，is not null会走，位图索引 is null，is not null 都会走；？？？

性能分析

extended explain加上你的SQL，然后通过show warnings可以查看实际执行的语句。要关注下面这些：

• type列 ， 连接类型。一个好的 SQL 语句至少要达到range级别。杜绝出现all级别。
• key列， 使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
• key_len列， 索引长度。
• rows列， 扫描行数。该值是个预估值。
• extra列， 详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

extra字段要特别注意

• using index： 需要查询的数据在索引上都可以查到，说明索引很成功；
• using index condition： 5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL Server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤；
• index merge： 对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到。
• using filesort： 说明对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，要特别注意有性能问题；因为group by是先 排序 再分组，如果没有排序的需要，可以加上一个order by NULL来避免排序从而避免出现using filesort；
• using temporary： 使用了临时表保存中间结果，常见于排序order by和分组查询group by，要特别注意有性能问题；
• impossible where： WHERE子句的值总是false，不能用来获取任何元组；
• select tables optimized away： 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；
• distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作。

type字段

• system： 表只有一行记录（等于系统表），这是const类型的特例，平时不会出现；
• const： 如果通过索引依次就找到了，const用于比较主键索引或者unique索引。因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量；
• eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；
• ref： 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；
• range： 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般为where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好；
• index： Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘读取的；
• all： Full Table Scan，遍历全表获得匹配的行。

字符与编码

CHARACTER_LENGTH（同CHAR_LENGTH）函数返回的是字符数，LENGTH函数返回的是字节数，一个汉字三个字节。MySQL的utf8最大是3个字节不支持emoji表情符号，必须只用utf8mb4。需要在 MySQL 配置文件中配置客户端字符集为utf8mb4。不过JDBC的连接串不支持配置characterEncoding=utf8mb4，最好的办法是在连接池中指定初始化SQL，例如：hikari连接池，其他连接池类似spring . datasource . hikari . connection – init – sql =set names utf8mb4。否则需要每次执行SQL前都先执行set names utf8mb4。

字符排序

• tf8_genera_ci不区分大小写；
• utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

常用语句

非常常用的sql语句：

• 如果有主键或者唯一键冲突则不插入： insert ignore into
• 如果有主键或者唯一键冲突则更新，注意这个会影响自增的增量： INSERT INTO room_remarks(room_id,room_remarks)VALUE(1,”sdf”) ON DUPLICATE KEY UPDATE room_remarks = “234”
• 如果有就用新的替代， values 如果不包含自增列，自增列的值会变化： REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,”sdf”)
• 备份表： CREATE TABLE user_info SELECT * FROM user_info
• 复制表结构： CREATE TABLE user_v2 LIKE user
• 从查询语句中导入： INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user
• 连表更新： UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id
• 连表删除： DELETE user FROM user,black WHERE user.id=black.id
• 强制使用某个索引： select * from table force index(idx_user) limit 2;
• 禁止使用某个索引： select * from table ignore index(idx_user) limit 2;
• 禁用缓存(在测试时去除缓存的影响)： select SQL_NO_CACHE from table limit 2;
• 查看字符集： SHOW VARIABLES LIKE ‘character_set%’;
• 查看排序规则： SHOW VARIABLES LIKE ‘collation%’;

特别可以注意的优化方法：

• where语句的解析顺序是从右到左，条件尽量放where不要放having；
• 采用延迟关联(deferred join)技术优化超多分页场景，比如limit 10000,10,延迟关联可以避免回表；
• distinct语句非常损耗性能，可以通过group by来优化；
• 连表尽量不要超过三个表。
• 如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的ID需要十分重视；
• 聚合函数会自动滤空，比如a列的类型是int且全部是NULL，则SUM(a)返回的是NULL而不是0；
• MySQL判断null相等不能用“a=null”，这个结果永远为UnKnown，where和having中，UnKnown永远被视为false，check约束中，UnKnown就会视为true来处理。所以要用“is null”或“is not null”处理。避免在where子句中对字段进行null值判断。
• MySQL对于in做了相应的优化，即将in中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。
• SELECT * 增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；要求直接在select后面接上字段名。
• 当只需要一条数据的时候，使用limit 1
• or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用union all或者是union（必要的时候）的方式来代替“or”会得到更好的效果。
• 尽量用union all代替union，union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。
• 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是in，那么先执行子查询。所以in适合于外表大而内表小的情况；exists适合于外表小而内表大的情况。推荐使用not exists取代not in，例如使用select colname … from A表 Left join B表 on where a.id = b.id where b.id is null 取代 select colname … from A表 where a.id not in (select b.id from B表)

在线更新表结构，一般都采用pt工具( Percona Toolkit)。如果线上请求超时，应该去关注下慢查询日志：先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。主要用到的是参数如下：

• -t：限制输出的行数，一般取前十条就够了；
• -s：根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡；
• -v：输出详细信息。

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500