MySQL基本知识点梳理和查询优化总结！

一、索引相关

1、索引基数：

基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有"M"和"F"两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。

2、索引失效原因：

3、索引的建立

二、EXPLIAN中有用的信息

基本用法

提高性能的特性

1、索引覆盖(covering index)： 需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index。

2、ICP特性(Index Condition Pushdown)： 本来index仅仅是data access的一种访问模式，存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时，如果部分where条件能使用索引的字段，MySQL server会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层。

3、索引合并(index merge)： 对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到，如果是AND条件，考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge，EXTRA会显示具体的合并算法和用到的索引。

extra字段

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

type字段

三、字段类型和编码

• utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感，
• utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感，但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则，直接使用utf8_bin替代。
• utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。

那么，同样是区分大小写，utf8_general_cs和utf8_bin有什么区别？

cs为case sensitive的缩写，即大小写敏感；bin的意思是二进制，也就是二进制编码比较。

utf8_general_cs排序规则下，即便是区分了大小写，但是某些西欧的字符和拉丁字符是不区分的，比如ä=a，但是有时并不需要ä=a，所以才有utf8_bin。

utf8_bin的特点在于使用字符的二进制的编码进行运算，任何不同的二进制编码都是不同的，因此在utf8_bin排序规则下：ä<>a

5、sql yog中初始连接指定编码类型使用连接配置的初始化命令。

四、SQL语句总结

常用的但容易忘的：

锁相关(作为了解，很少用)

1、共享锁： select id from tb_test where id = 1 lock in share mode;

优化时用到：

1、强制使用某个索引： select * from table force index(idx_user) limit 2;

查看状态

1、查看字符集 SHOW VARIABLES LIKE 'character_set%';

SQL编写注意

1、where语句的解析顺序是从右到左，条件尽量放where不要放having。

五、踩坑

1、如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的id需要十分重视。

六、千万大表在线修改

mysql在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。mysql在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用pt工具( Percona Toolkit)。

如对表添加索引：

如下：

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_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 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE '%quer%';

-- 查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE 'log_output'

-- 查看慢查询的数量

mysqldumpslow的工具十分简单，我主要用到的是参数如下：

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

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

查看SQL进程和杀死进程

如果你执行了一个sql的操作，但是迟迟没有返回，你可以通过查询进程列表看看他的实际执行状况，如果该sql十分耗时，为了避免影响线上可以用kill命令杀死进程，通过查看进程列表也能直观的看下当前sql的执行状态，如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。命令如下：

--查看进程列表
SHOW PROCESSLIST;
--杀死某个进程
kill 183665

如果你使用的sqlyog，那么也有图形化的页面，在菜单栏-工具-显示-进程列表。在进程列表页面可以右键杀死进程。如下所示：

九、一些数据库性能的思考

在对公司慢查询日志做优化的时候，很多时候可能是忘了建索引，像这种问题很容易解决，加个索引就行了。但是有两种情况就不是简单能加索引能解决了：

1、业务代码循环读数据库： 考虑这样一个场景，获取用户粉丝列表信息 加入分页是十个 其实像这样的sql是十分简单的，通过连表查询性能也很高，但是有时候，很多开发采用了取出一串id，然后循环读每个id的信息，这样如果id很多对数据库的压力是很大的，而且性能也很低。

2、统计sql： 很多时候，业务上都会有排行榜这种，发现公司有很多地方直接采用数据库做计算，在对一些大表的做聚合运算的时候，经常超过五秒，这些sql一般很长而且很难优化， 像这种场景，如果业务允许（比如一致性要求不高或者是隔一段时间才统计的），可以专门在从库里面做统计。另外我建议还是采用redis缓存来处理这种业务。

3、超大分页： 在慢查询日志中发现了一些超大分页的慢查询如limit 40000,1000，因为mysql的分页是在server层做的，可以采用延迟关联在减少回表。但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的，所以很有可能是有恶意用户在刷接口，所以最好在开发的时候也对接口加上校验拦截这些恶意请求。

这篇文章就总结到这里，希望能够对你有所帮助！

- EOF -

加主页君微信，不仅数据分析和开发技能+1

主页君日常还会在个人微信分享数据分析和开发相关工具、资源和精选技术文章，不定期分享一些有意思的活动、岗位内推以及如何用技术做业余项目

加个微信，打开一扇窗

看完本文有收获？请转发分享给更多人

点赞和在看就是最大的支持❤️