MySQL深入浅出(二):索引的设计原则、SQL优化、MySQL日志、备份与恢复

一、 索引的设计原则

  1. 查看字段散列度/离散度:select count(distinct col_name),... from table_name,如性别的离散度比较低不适合做索引
  2. InnoDB表的普通索引都会保存主键的值,所以主键要尽可能选择比较短的数据类型,可以有效地减少索引的磁盘占用,提高索引的缓存效果。

插件式存储引擎MySQL最重要的特性之一 优化表碎片:OPTIMIZE TABLE table_name 存储过程函数是事先经过编译并存储在数据库中的一段SQL语句的集合,可以减少开发人员很多工作;

事务控制和锁定语句

lock tables 锁定用于当前线程的表。如果表被其他线程锁定,则当前线程会等待,直到可以获取所有锁定为止

事务控制:

start transaction/begin
...
commit/rollback

当前分布式事务只支持InnoDB存储引擎。

Tips:如果想更清楚地了解SQL的执行过程:show profile for query

二、SQL优化

2.1 通过show status 命令了解各种SQL执行频率

show [session|global] status

session:当前连接 global:自数据库上次启动至今

show status like 'Com_%';

通过以上几个参数,可以了解到当前数据库的应用是插入更新为主还是以查询操作为主。

2.2 通过explain分析低效SQL的执行计划

各属性含义: id: 查询的序列号 select_type: 查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询

  • SIMPLE:查询中不包含子查询或者UNION
  • 查询中若包含任何复杂的子部分,最外层查询则被标记为:PRIMARY
  • 在SELECT或WHERE列表中包含了子查询,该子查询被标记为:SUBQUERY
  • 在FROM列表中包含的子查询被标记为:DERIVED(衍生)
  • 若第二个SELECT出现在UNION之后,则被标记为UNION;若UNION包含在 FROM子句的子查中,外层SELECT将被标记为:DERIVED
  • 从UNION表获取结果的SELECT被标记为:UNION RESULT

table: 输出的行所引用的表 type: 访问类型

从左至右,性能由差到好

  1. ALL: 扫描全表
  2. index: 扫描全部索引树
  3. range: 扫描部分索引,索引范围扫描,对索引的扫描开始于某一点,返回匹配值域的行,常见于between、<、>等的查询
  4. ref: 使用非唯一索引或非唯一索引前缀进行的查找
  5. eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  6. const, system: 单表中最多有一个匹配行,查询起来非常迅速,例如根据主键或唯一索引查询。system是const类型的特例,当查询的表只有一行的情况下, 使用system。
  7. NULL: 不用访问表或者索引,直接就能得到结果,如select 1 from test where 1

possible_keys: 表示查询时可能使用的索引。如果是空的,没有相关的索引。这时要提高性能,可通过检验WHERE子句,看是否引用某些字段,或者检查字段不是适合索引

key: 显示MySQL实际决定使用的索引。如果没有索引被选择,是NULL

key_len: 使用到索引字段的长度

注:key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。

ref: 显示哪个字段或常数与key一起被使用

rows: 这个数表示mysql要遍历多少数据才能找到,表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,在innodb上可能是不准确的

Extra: 执行情况的说明和描述。包含不适合在其他列中显示但十分重要的额外信息。

  1. Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。如果同时出现Using where,代表使用索引来查找读取记录, 也是可以用到索引的,但是需要查询到数据表。
  2. Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。如果type列是ALL或index,而没有出现该信息,则你有可能在执行错误的查询:返回所有数据。
  3. Using filesort:不是“使用文件索引”的含义!filesort是MySQL所实现的一种排序策略,通常在使用到排序语句ORDER BY的时候,会出现该信息。
  4. Using temporary:表示为了得到结果,使用了临时表,这通常是出现在多表联合查询,结果排序的场合。

2.3 索引问题

索引是在MySQL的存储引擎层实现的,而不是在服务器层实现的,所以每种存储引擎的索引都不一定完全相同。

最左匹配原则可以算是MySQL中B-Tree索引使用的首要原则

%开头的like查询不能够利用B-Tree索引,执行计划中key的值为NULL表示没有使用索引

数据类型出现隐式转换的时候也不会使用索引,特别是当列类型是字符串,那么一定记得在where条件中把字符常量用引号引起来。如select * from test where last_name='1';

or分割的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及的索引都不会被用到。因为or后面的条件列没有索引,那么后面的查询肯定走全表扫描,在存在全表扫描的情况下,就没有必要多一次索引扫描增加I/O访问,一次全表扫描过滤条件就足够了。

查看索引使用情况:

show [global] status like 'Handler_read%';
 
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2225  |
| Handler_read_key      | 2486  |
| Handler_read_last     | 0     |
| Handler_read_next     | 78    |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 2     |
| Handler_read_rnd_next | 47857 |
+-----------------------+-------+

Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救

InnoDB类型的表是按照主键的顺序保存的。 优化insert语句,如果同时从同一个客户端插入多行,应尽量使用多个值表的insert语句,这种方式大大缩减客户端与数据库之间的连接、关闭等消耗

优化嵌套查询:有些情况下,子查询可以被更有效的连接(join)代替。连接(join)之所以更有效率一些,是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作

优化分页查询:消息私信MySQL的limit用法和分页查询的性能分析及优化

InnoDB采用redo log机制来保证事务更新的一致性和持久性

三、MySQL日志

在MySQL中有4种不同的日志:错误日志二进制日志(BINLOG)查询日志慢查询日志

  • mysqlbinlog(日志管理工具)
  • mysqldump(数据导出工具)

3.1 二进制日志

二进制日志记录了所有的DDL和DML语句,但是不包括数据查询语句。语句以“事件”的形式保存,它描述了数据的更改过程。此日志对于灾难时的数据恢复起着极其重要的作用。 由于二进制日志以二进制存储,不能直接读取,需要用mysqlbinlog工具来查看:mysqlbinlog log-file

四、备份与恢复

备份要在系统负载较小的时间进行

4.1 逻辑备份和恢复

备份数据库test:

mysqldump -uroot -p test > test.sql

备份数据库test下的表emp:

mysqldump -uroot -p test emp > emp.sql

恢复:

mysqldump -uroot -p daname < bakfile

物理备份:冷备份(停掉数据库)和热备份(本质是将要备份的表加锁)

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏MongoDB中文社区

常见问题:索引

本文档解决了有关MongoDB 索引的一些常见问题 。有关索引的更多信息,请参阅 索引。

11520
来自专栏MongoDB中文社区

海量数据的分页怎么破?

分页应该是极为常见的数据展现方式了,一般在数据集较大而无法在单个页面中呈现时会采用分页的方法。

28620
来自专栏Linyb极客之路

一句话说清分布式锁,进程锁,线程锁

线程锁:大家都不陌生,主要用来给方法、代码块加锁。当某个方法或者代码块使用锁时,那么在同一时刻至多仅有有一个线程在执行该段代码。当有多个线程访问同一对象的加锁方...

12020
来自专栏AI科技大本营的专栏

Python分析9万条数据告诉你复仇者联盟谁才是绝对C位

漫威宇宙,其实就讲了一件事情。整个宇宙就好比一个项目组。其中有一群叫作美国队长、钢铁侠、惊奇队长、浩克、索尔等人在维护这个项目,兢兢业业的维护整个项目。

10010
来自专栏SpringCloud专栏

Springboot分别使用乐观锁和分布式锁(基于redisson)完成高并发防超卖

在电商中经常会有防超卖的需求,本质上是对一条数据的多线程并发情况下的数据安全性进行控制。

1.1K30
来自专栏MongoDB中文社区

使用模式构建:近似值模式

假设现在有一个相当规模的城市,大约有3.9万人。人口的确切数字是相当不稳定的,人们会搬入搬出、有婴儿会出生、有人会死亡。我们也许要花上整天的时间来得到每天确切的...

8730
来自专栏MongoDB中文社区

使用MongoDB进行分片

本文档回答了有关分片的常见问题。或者可以参考手册的分片章节,其提供了 分片的概述,包括如下细节:

33240
来自专栏MongoDB中文社区

使用模式构建:树形模式

到目前为止,我们讨论的许多设计模式都强调省去JOIN操作的时间是有好处的。那些会被一起访问的数据也应该存储在一起,即便导致了一些数据重复也是可以的。像扩展引用(...

12330
来自专栏MongoDB中文社区

常见问题:复制和副本集

• MongoDB支持哪种复制? • 复制是否可以通过Internet和WAN连接进行? • MongoDB可以通过“noisy”连接进行复制吗? • 如果复制...

14360
来自专栏趣谈编程

如何实现分布式锁?

Tomcat是这个系统的核心组成部分, 每当有用户请求过来,Tomcat就会从线程池里找个线程来处理,有的执行登录,有的查看购物车,有的下订单,看着属下们尽心尽...

18850

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励