Archive for the category: 3.数据库相关

索引相关

索引基数

基数是数据列所包含的不同值的数量，例如，某个数据列包含值 1、3、7、4、7、3，那么它的基数就是 4。

索引的基数相对于数据表行数较高(也就是说，列中包含很多不同的值，重复的值很少)的时候，它的工作效果最好。

如果某数据列含有很多不同的年龄，索引会很快地分辨数据行;如果某个数据列用于记录性别(只有“M”和“F”两种值)，那么索引的用处就不大;如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。

在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是“30%”。

索引失效原因

索引失效的原因有如下几点：

• 对索引列运算，运算包括(+、-、*、/、!、<>、%、like’%_'(% 放在前面)。
• 类型错误，如字段类型为 varchar，where 条件用 number。
• 对索引应用内部函数，这种情况下应该要建立基于函数的索引。例如 select * from template t where ROUND (t.logicdb_id) = 1，此时应该建 ROUND (t.logicdb_id) 为索引。

MySQL 8.0 开始支持函数索引，5.7 可以通过虚拟列的方式来支持，之前只能新建一个 ROUND (t.logicdb_id) 列然后去维护。

• 如果条件有 or，即使其中有条件带索引也不会使用(这也是为什么建议少使用 or 的原因)，如果想使用 or，又想索引有效，只能将 or 条件中的每个列加上索引。
• 如果列类型是字符串，那一定要在条件中数据使用引号，否则不使用索引。
• B-tree 索引 is null 不会走，is not null 会走，位图索引 is null，is not null 都会走。
• 组合索引遵循最左原则。

索引的建立

索引的建立需要注意以下几点：

• 最重要的肯定是根据业务经常查询的语句。
• 尽量选择区分度高的列作为索引，区分度的公式是 COUNT(DISTINCT col) / COUNT(*)，表示字段不重复的比率，比率越大我们扫描的记录数就越少。
• 如果业务中唯一特性最好建立唯一键，一方面可以保证数据的正确性，另一方面索引的效率能大大提高。

EXPLIAN 中有用的信息

基本用法

EXPLIAN 基本用法如下：

• desc 或者 explain 加上你的 SQL。
• extended explain 加上你的 SQL，然后通过 show warnings 可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经 SQL 分析后，实际执行的代码是一样的。

提高性能的特性

EXPLIAN 提高性能的特性如下：

• 索引覆盖(covering index)：需要查询的数据在索引上都可以查到不需要回表 EXTRA 列显示 using index。
• ICP特性(Index Condition Pushdown)：本来 index 仅仅是 data access 的一种访问模式，存数引擎通过索引回表获取的数据会传递到 MySQL Server 层进行 where 条件过滤。

5.6 版本开始当 ICP 打开时，如果部分 where 条件能使用索引的字段，MySQL Server 会把这部分下推到引擎层，可以利用 index 过滤的 where 条件在存储引擎层进行数据过滤。

EXTRA 显示 using index condition。需要了解 MySQL 的架构图分为 Server 和存储引擎层。

• 索引合并(index merge)：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

一般用 or 会用到，如果是 AND 条件，考虑建立复合索引。EXPLAIN 显示的索引类型会显示 index_merge，EXTRA 会显示具体的合并算法和用到的索引。

Extra 字段

Extra 字段使用：

• using filesort：说明 MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

MySQL 中无法利用索引完成的排序操作称为“文件排序”，其实不一定是文件排序，内部使用的是快排。

• using temporary：使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
• using index：表示相应的 SELECT 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错。
• impossible where：where 子句的值总是 false，不能用来获取任何元组。
• select tables optimized away：在没有 group by 子句的情况下基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct：优化 distinct 操作，在找到第一匹配的元组后即停止找同样值的操作。

using filesort、using temporary 这两项出现时需要注意下，这两项是十分耗费性能的。

在使用 group by 的时候，虽然没有使用 order by，如果没有索引，是可能同时出现 using filesort，using temporary 的。

因为 group by 就是先排序在分组，如果没有排序的需要，可以加上一个 order by NULL 来避免排序，这样 using filesort 就会去除，能提升一点性能。

type 字段

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，遍历全表获得匹配的行。

字段类型和编码

MySQL 返回字符串长度

CHARACTER_LENGTH(同CHAR_LENGTH)方法返回的是字符数，LENGTH 函数返回的是字节数，一个汉字三个字节。

varchar 等字段建立索引长度计算语句

select count(distinct left(test,5))/count(*) from table;越趋近 1 越好。

MySQL 的 utf8

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。

MySQL 排序规则

一般使用 _bin 和 _genera_ci：

• 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。

初始化命令

SQLyog 中初始连接指定编码类型使用连接配置的初始化命令，如下图：

SQL 语句总结

常用但容易忘的

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。

查看状态

查看状态：

• 查看字符集：SHOW VARIABLES LIKE ‘character_set%’。
• 查看排序规则：SHOW VARIABLES LIKE ‘collation%’。

SQL 编写注意

SQL 编写请注意：

• 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 来处理。所以要用“a is null”处理。

千万大表在线修改

MySQL 在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。

MySQL 在 5.6 之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用 PT 工具( Percona Toolkit)。

如对表添加索引：

1. pt-online-schema-change –user=’root’ –host=’localhost’ –ask-pass –alter “add index idx_user_id(room_id,create_time)” 
2. D=fission_show_room_v2,t=room_favorite_info –execute

慢查询日志

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用 mysqldumpslow 去分析。

查询慢查询日志信息可以直接通过执行 SQL 命令查看相关变量，常用的 SQL 如下：

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 的执行状态;如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。

命令如下：

1. –查看进程列表
2. SHOW PROCESSLIST;
3. –杀死某个进程
4. kill 183665

如果你使用的 SQLyog，那么也有图形化的页面，在菜单栏→工具→显示→进程列表。

在进程列表页面可以右键杀死进程，如下所示：

一些数据库性能的思考

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

业务代码循环读数据库

考虑这样一个场景，获取用户粉丝列表信息，加入分页是十个，其实像这样的 SQL 是十分简单的，通过连表查询性能也很高。

但是有时候，很多开发采用了取出一串 ID，然后循环读每个 ID 的信息，这样如果 ID 很多对数据库的压力是很大的，而且性能也很低。

统计 SQL

很多时候，业务上都会有排行榜这种，发现公司有很多地方直接采用数据库做计算，在对一些大表做聚合运算的时候，经常超过五秒，这些 SQL 一般很长而且很难优化。

像这种场景，如果业务允许(比如一致性要求不高或者是隔一段时间才统计的)，可以专门在从库里面做统计。另外我建议还是采用 Redis 缓存来处理这种业务。

超大分页

在慢查询日志中发现了一些超大分页的慢查询如 Limit 40000，1000，因为 MySQL 的分页是在 Server 层做的，可以采用延迟关联在减少回表。

但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的，所以很有可能是有恶意用户在刷接口，最好在开发的时候也对接口加上校验拦截这些恶意请求。

ELK 介绍

ELK 最早是 Elasticsearch（以下简称ES）、Logstash、Kibana 三款开源软件的简称，三款软件后来被同一公司收购，并加入了Xpark、Beats等组件，改名为Elastic Stack，成为现在最流行的开源日志解决方案，虽然有了新名字但大家依然喜欢叫她ELK，现在所说的ELK就指的是基于这些开源软件构建的日志系统。

我们收集mysql慢日志的方案如下：

• mysql 服务器安装 Filebeat 作为 agent 收集 slowLog
• Filebeat 读取 mysql 慢日志文件做简单过滤传给 Kafka 集群
• Logstash 读取 Kafka 集群数据并按字段拆分后转成 JSON 格式存入 ES 集群
• Kibana读取ES集群数据展示到web页面上

慢日志分类

目前主要使用的mysql版本有5.5、5.6 和 5.7，经过仔细对比发现每个版本的慢查询日志都稍有不同，如下：

5.5 版本慢查询日志

 

5.6 版本慢查询日志

 

5.7 版本慢查询日志

慢查询日志异同点：

1. 每个版本的Time字段格式都不一样
2. 相较于5.6、5.7版本，5.5版本少了Id字段
3. use db语句不是每条慢日志都有的
4. 可能会出现像下边这样的情况，慢查询块# Time：下可能跟了多个慢查询语句

处理思路

上边我们已经分析了各个版本慢查询语句的构成，接下来我们就要开始收集这些数据了，究竟应该怎么收集呢？

1. 拼装日志行：mysql 的慢查询日志多行构成了一条完整的日志，日志收集时要把这些行拼装成一条日志传输与存储。
2. Time行处理：# Time: 开头的行可能不存在，且我们可以通过SET timestamp这个值来确定SQL执行时间，所以选择过滤丢弃Time行
3. 一条完整的日志：最终将以# User@Host: 开始的行，和以SQL语句结尾的行合并为一条完整的慢日志语句
4. 确定SQL对应的DB：use db这一行不是所有慢日志SQL都存在的，所以不能通过这个来确定SQL对应的DB，慢日志中也没有字段记录DB，所以这里建议为DB创建账号时添加db name标识，例如我们的账号命名方式为：projectName_dbName，这样看到账号名就知道是哪个DB了
5. 确定SQL对应的主机：我想通过日志知道这条SQL对应的是哪台数据库服务器怎么办？

   慢日志中同样没有字段记录主机，可以通过filebeat注入字段来解决，例如我们给filebeat的name字段设置为服务器IP，这样最终通过beat.name这个字段就可以确定SQL对应的主机了。

Filebeat配置

filebeat 完整的配置文件如下：

 

重要参数解释：

• input_type：指定输入的类型是log或者是stdin
• paths：慢日志路径，支持正则，比如/data/*.log
• exclude_lines：过滤掉# Time开头的行
• multiline.pattern：匹配多行时指定正则表达式，这里匹配以# Time或者# User开头的行，Time行要先匹配再过滤
• multiline.negate：定义上边pattern匹配到的行是否用于多行合并，也就是定义是不是作为日志的一部分
• multiline.match：定义如何将皮排行组合成时间，在之前或者之后
• tail_files：定义是从文件开头读取日志还是结尾，这里定义为true，从现在开始收集，之前已存在的不管
• name：设置filebeat的名字，如果为空则为服务器的主机名，这里我们定义为服务器IP
• output.kafka：配置要接收日志的kafka集群地址可topic名称

Kafka 接收到的日志格式：

 

{“@timestamp”:”2018-08-07T09:36:00.140Z”,”beat”:{“hostname”:”db-7eb166d3″,”name”:”10.63.144.71″,”version”:”5.4.0″},”input_type”:”log”,”message”:”# User@Host: select[select] @  [10.63.144.16]  Id: 23460596\n# Query_time: 0.155956  Lock_time: 0.000079 Rows_sent: 112  Rows_examined: 366458\nSET timestamp=1533634557;\nSELECT DISTINCT(uid) FROM common_member WHERE hideforum=-1 AND uid != 0;”,”offset”:1753219021,”source”:”/data/slow/mysql_slow.log”,”type”:”log”}

Logstash配置

logstash完整的配置文件如下：

 

重要参数解释：

• input：配置 kafka 的集群地址和 topic 名字
• filter：过滤日志文件，主要是对 message 信息（看前文 kafka 接收到的日志格式）进行拆分，拆分成一个一个易读的字段，例如User、Host、Query_time、Lock_time、timestamp等。

  grok段根据我们前文对mysql慢日志的分类分别写不通的正则表达式去匹配，当有多条正则表达式存在时，logstash会从上到下依次匹配，匹配到一条后边的则不再匹配。

  date字段定义了让SQL中的timestamp_mysql字段作为这条日志的时间字段，kibana上看到的实践排序的数据依赖的就是这个时间

• output：配置ES服务器集群的地址和index，index自动按天分割

kibana查询展示

打开Kibana添加 mysql-slowlog-* 的Index，并选择timestamp，创建Index Pattern

 

进入Discover页面，可以很直观的看到各个时间点慢日志的数量变化，可以根据左侧Field实现简单过滤，搜索框也方便搜索慢日志，例如我要找查询时间大于2s的慢日志，直接在搜索框输入 query_time: > 2 回车即可。

 

点击每一条日志起边的很色箭头能查看具体某一条日志的详情。

如果你想做个大盘统计慢日志的整体情况，例如top 10 SQL等，也可以很方便的通过web界面配置。