索引
建立索引
索引的基数相对于数据表行数较高, 工作效果最好. 说明列中不同值较多, 区分度的公式是 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
来源: http://www.tuicool.com/articles/nIbMJvr