mysql优化

1. 索引失效

---

 

• 对索引列运算，运算包括(+、-、*、/、！、<>、%、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 都会走
• 组合索引遵循最左原则

2. explain

---

 

　　type　　　

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

　　extra

• using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ，其实不一定是文件排序，内部使用的是快排
• using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by
• using index： 表示相应的SELECT操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错。
• impossible where： WHERE子句的值总是false，不能用来获取任何元组
• distinct： 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的操作

using filesort,using temporary这两项出现时需要注意下，这两项是十分耗费性能的，在使用group by的时候，虽然没有使用order by，如果没有索引，是可能同时出现using filesort,using temporary的，因为group by就是先排序在分组，如果没有排序的需要，可以加上一个order by NULL来避免排序，这样using filesort就会去除，能提升一点性能。

3. 字段类型和编码

---

 

• mysql返回字符串长度：CHARACTER_LENGTH方法(CHAR_LENGTH一样的)返回的是字符数，LENGTH函数返回的是字节数，一个汉字三个字节
• varvhar等字段建立索引长度计算语句：select count(distinct left(test,5))/count(*) from table; 越趋近1越好
• 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。
• msyql排序规则(一般使用_bin和_genera_ci)：
• utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感，
• utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感，但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则，直接使用utf8_bin替代。
• utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

4. 好用的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 id from tb_test where id = 1 lock in share mode;
• 排它锁： select id from tb_test where id = 1 for update

• 强制使用某个索引： 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;

• 采用延迟关联(deferred join)技术优化超多分页场景，比如limit 10000,10,延迟关联可以避免回表
• distinct语句非常损耗性能，可以通过group by来优化