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

上一篇：MySQL深入浅出（一）：SQL基础、数据类型、运算符

一、 索引的设计原则

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

    物理备份：冷备份（停掉数据库）和热备份（本质是将要备份的表加锁）