深入浅出的学习Mysql(深入浅出了解啥意思)

推荐整理分享深入浅出的学习Mysql(深入浅出了解啥意思)，希望有所帮助，仅作参考，欢迎阅读内容。

文章相关热门搜索词:深入浅出的探讨,深入浅出了解啥意思,深入浅出的重要性,深入浅出的解读,深入浅出的阐述与讲解,深入浅出的了解,深入浅出的重要性,深入浅出的讲解让大家明白,内容如对您有帮助，希望把文章链接给更多的朋友！

前言

数据库一直是笔者比较薄弱的地方，结合自己的使用经验(python+sqlalchemy)等做个记录，笔者比较喜欢使用ORM，一直感觉拼sql是一件比较痛苦的事情(主要是不擅长sql)，以前维护项的目中也遇到过一些数据库的坑，比如编码问题，浮点数精度损失等，防止以后重复踩坑。

1章：使用帮助

使用mysql内置的帮助命令

2章：表类型（存储引擎）的选择

最常用的两种引擎:

1、Myisam是Mysql的默认存储引擎,当create创建新表时,未指定新表的存储引擎时,默认使用Myisam。 每个MyISAM 在磁盘上存储成三个文件。文件名都和表名相同,扩展名分别是 .frm (存储表定义) 、.MYD (MYData,存储数据)、.MYI (MYIndex,存储索引)。数据文件和 索引文件可以放置在不同的目录,平均分布io,获得更快的速度。

2、InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比 Myisam 的存储引擎,InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

常用环境:

1、MyISAM: 默认的 MySQL 插件式存储引擎, 它是在 Web、 数据仓储和其他应用环境下最常使用的存储引擎之一

2、InnoDB:用于事务处理应用程序,具有众多特性,包括 ACID 事务支持。

3章：选择合适的数据类型

首先选择合适的存储引擎，根据指定的存储引擎确定合适的数据类型。

需要注意的一些数据类型：

1、char与varchar: 保存和检索方式不同，最大长度和是否尾部空格被保留也不同。char固定长度，长度不够用空格填充，获取时如果没有设置 PAD_CHAR_TO_FULL_LENGTH默认去除尾部空格。varchar变长字符串，检索时尾部空格会被保留。注意查询时候不区分大小写，如果用sqlalchemy区分大小写不要用func.binary函数。

2、text和blob: text和blob执行大量的更新或者删除的时候会留下很大『空洞』，建议定期用OPTIMIZE TABLE功能对这类表碎片整理。避免检索大型的blob或text值 。把text和blob列分离到单独的表中。

3、浮点数float与定点数decimal:

注意几个点：

1.浮点数虽然能表示更大的数据范围，但是有误差问题。

2.对货币等精度敏感的问题，应使用定点数存储。之前项目踩过坑，结果不得不用放大和缩小倍数的方法解决，比较ugly。

3.编程如果遇到浮点数，注意误差问题，尽量避免浮点数比较（比较浮点数需要作差小于一个特定精度），python3.5中可以这么比较：float_eq = partial(math.isclose, rel_tol=1e-, abs_tol=0.0)

4.注意浮点数中一些特殊值的处理。

4章：字符集

一开始要选择合适的字符集，否则后期更换代价很高。python2中字符集就是个老大难问题，困然很多新手。之前维护过的项目使用了msyql默认的latin1字符集，导致每次写入的时候都要对字符串手动encode成utf8。最近用python3.5+flask做项目直接使用utf8，再也没碰到过编码问题：

5章：索引的设计和使用

所有mysql列类型都可以被索引，对相关列使用索引是提高select操作性能的最佳途径。索引设计的原则:

1.搜索的索引列，不一定是所要选择的列。最适合的索引的列是出现在where子句中的列，或连接子句中指定的列，而不是出现在select关键字之后的选择列表中的列。

2.使用唯一索引。对于唯一值的列，索引效果较好，而有多个重复值的列，索引效果差。

3.使用短索引。如果对字符串列进行索引，应指定一个前缀长度，只要有可能就应该这样做。

4.利用最左前缀。在创建一个n列索引时，实际上创建了mysql可利用的n个索引。多列索引可以起到几个索引的作用，因为可利用索引中的最左边的列集来匹配行，这样的列集成为最左前缀。

5.不要过度索引。索引会浪费磁盘空间，降低写入性能。

6.考虑在列上进行的比较类型。

6章：锁机制和事务控制

InnoDB引擎提提供行级锁，支持共享锁和排他锁两种锁定模式，以及四种不同的隔离级别。mysql通过AUTOCOMIT, START TRANSACTIONS, COMMIT和ROLLBACK等语句支持本地事务。

7章：SQL中的安全问题

SQL注入：利用某些数据库的外部接口把用户数据插入到实际的数据库操作语音（sql）中，从而达到入侵数据库甚至操作系统的目的。产生原因主要是因为程序堆用户输入的数据没有进行严格的过滤，导致非法数据库查询语句的执行，防范措施：

8章：SQL Mode及相关问题

更改默认的mysql执行模式，比如严格模式下列的插入或者更新不正确时mysql会给出错误，并放弃操作。set session sql_mode='STRICT_TRANS_TABLES'。设置sql_mode需要应用人员权衡各种得失，做一个合适的选择。

9章：常用SQL技巧

章：其他需要注意的问题

数据库名、表名大小写问题：不同平台和系统，是否区分大小写是不同的。建议就是始终统一使用小写名。使用外键需要注意的地方：mysql中InnoDB支持对外部关键字约束条件的检查。

章：SQL优化

优化SQL的一般步骤：

1.使用show status和应用特点了解各种SQL的执行频率，了解各种SQL大致的执行比例。比如InnoDB的的参数Innode_rows_read查询返回的行数，Innodb_rows_inserted执行insert插入的行数，Innodb_rows_updated更新的行数。还有一下几个参数：Connections试图连接mysql服务器嗯出书，Uptime服务器的工作时间，Slow_queries慢查询的次数。

2.定位执行效率低的SQL语句。两种方式：一种是通过慢查询日志定位执行效率低的语句,使用—log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。另一种是show processlist查看当前mysql在进行的线程，包括线程的状态，所否锁表等，可以实时查看SQL执行情况，同时对一些锁表操作进行优化。

3.通过EXPLAIN分析低效SQL的执行计划：explain可以知道什么时候必须为表假如索引以得到一个使用索引来寻找记录的更快的SELECT，以下是EXPLAIN执行后得到的结果说明：

4.确定问题，并采取相应优化措施。

索引问题

两个简单实用的优化方法

从客户端（代码端）角度优化

章: 优化数据库对象

优化表的数据类型：PROCEDURE ANALYZE()对当前表类型的判断提出优化建议。实际可以通过统计信息结合应用实际优化。

通过拆分，提高表的访问效率：这里拆分主要是针对Myisam类型的表。

逆规范化：规范化设计强调独立性，数据尽可能少冗余，更多冗余意味着占用更多物理空间，同事也对数据维护和一致性检查带来问题。适当冗余可以减少多表访问，查询效率明显提高，这种情况可以考虑适当通过冗余提高效率。

使用冗余统计表：使用create temporary table做统计分析

选择更合适的表类型：1.如果应用出现比较严重的锁冲突，请考虑是否刻意更改存储引擎到InnoDB,行锁机制可以有效减少锁冲突出现。2.如果应用查询操作很多，且对事务完整性要求不严格，可以考虑使用Myisam。

章：锁问题

获取锁的等待情况：table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺。检查Innode_row_lock分析行锁的争夺情况。

章：优化Mysql Server

查看Mysql Server当前参数

影响Mysql性能的重要参数

章：I/O问题

磁盘搜索是巨大的性能瓶颈。

章：应用优化

总结