MySQL（二）——存储引擎和SQL优化

目录

【3】存储引擎

【4】SQL优化

4.1 查看SQL执行频率

4.2 定位低效率执行SQL

4.3 explain分析执行计划

4.4 show profile分析SQL

4.5 trace分析优化器执行计划

4.6 SQL优化

---

【3】存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。MySQL提供了插件式的存储引擎架构，可以根据需要使用相应引擎。

• 查看MySQL提供的所有存储引擎：show engines;
• 查看默认存储引擎：show variables like ‘%storage_engine’;
• 查看表的存储引擎：show table status like "table_name";

InnoDB：supports transactions, row-level locking, and foreign key（支持事务、行级锁和外键）

InnoDB和MyISAM的区别：

• InnoDB支持事物，而MyISAM不支持事物
• InnoDB支持行级锁，而MyISAM支持表级锁（table-level locking）
• InnoDB支持MVCC, 而MyISAM不支持
• InnoDB支持外键，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持
• InnoDB支持数据库崩溃后的安全恢复，而MyISAM不支持。

补充：MVCC（multiversion concurrency control）：可看作是行级锁的一个升级，可以有效减少加锁操作，提高性能。使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃前的状态。这个恢复的过程依赖于redo log。

存储引擎的选择：

• InnoDB：是MySQL的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。
• MyISAM：如果是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是很合适的。

【4】SQL优化

4.1 查看SQL执行频率

即查看增、删、查、改等操作的执行次数。

• show global status like ‘Com_______’;（占位7个字符）
• 查看InnoDB相关：show global status like ‘Innodb_row s_%’;（global：全局信息）

4.2 定位低效率执行SQL

慢查询日志：它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中，慢查询日志在查询结束以后才记录；

show processlist：实时查询SQL的执行情况。

4.3 explain分析执行计划

通过EXPLAIN或者DESC命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。

查询SQL语句的执行计划：explain …（如：explain select * from students where score='95';）

• explain之id

1. id相同表示加载表的顺序是从上到下
2. id不同，id值越大，优先级越高，越先被执行
3. id有相同，也有不同，上述两种结合

• explain之select_type

• explain之type

    一般来说，我们需要保证查询至少达到range级别，最好达到ref级别。

• explain之extra

  前两种需要优化。文件排序：需要扫描文件中的内容。

4.4 show profile分析SQL

该工具可以帮助分析SQL执行效率

• 查看是否支持profile：select @@have_profiling;
• 查看是否开启profile：select @@profiling;
• 开启profile：set profiling=1;
• show profiles;
• 查看命令的执行过程：show profile for query X;

sending data耗时较多，表示MySQL线程开始访问数据并把结果返回给客户端的时间。

4.5 trace分析优化器执行计划

优化器：优化SQL语句。trace：查看优化器的优化效果。

• 打开trace，设置格式为JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能完全展示。

     SET optimizer_trace="enabled=on", end_marker_in_json=on;

     set optimizer_trace_max_mem_size=1000000;

• 然后，执行SQL语句；
• 最后，检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的：

    select * from information_schema.optimizer_trace\G;

4.6 SQL优化

详细内容请参考链接

• 优化大批量插入数据；
• 优化insert语句；
• 优化order by语句；
• 优化group by语句；
• 优化嵌套语句；
• 优化OR条件；
• 优化分页查询；
• 使用SQL提示。