Mysql 高级

sql_mode 是一个容易忽视的变量，默认情况下为空，可以忍耐一些非法操作，在生产环境中，必须将其设置为严格模式，在开发测试环境中配该变量也是很有必要的，因为这样可以在生产之前发现问题。sql_mode 常用值如下：和其它数据库相比，MySQL 有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。连接层最上层是客户端和连接服务，包含本地 socket 通信和 tcp/ip 通信，主要完成连接处理、授权认证及相关的安全方案，该层引入了线程池，为授权用户提供线程，还实现了 ssl 安全链接。服务层引擎层存储引擎层，负责了数据的存储和提取，服务器通过 API 与存储引擎进行通信。存储层数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。开启诊断分析工具set profiling=1;显示最近的几条查询show profiles;查看 SQL 的执行步骤show profile cpu,block io for query 1; 查看支持的存储引擎show engines;查看当前默认的存储引擎show variables like ‘%storage_engine%’;InnoDBInnoDB 是 MySQL 默认的事务型引擎，用来处理大量的短期事务，除非有特别的原因需要用到其他存储引擎，否则优先考虑 InnoDB。MyISAMMyISAM 提供了大量的特性，包括全文检索、压缩、空间函数等，但 MyISAM 不支持事务和行级锁，缺点是崩溃后无法安全恢复。ArchiveArchive 档案存储引擎只支持 INSERT 和 SELECT 操作，在 MySQL5.1 之前不支持索引；Archive 表适合日志和数据采集类应用；根据英文的测试结论来看，Archive 表比 MyISAM 表要小大约 75%，比支持事务处理的 InnoDB 表小大约 83%。BlackholeBlackhole 引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。CSVCSV 引擎可以将普通的 CSV 文件作为 MySQL 表来处理，但不支持索引， CSV 可以作为一种数据交换的机制，CSV 引擎存储的数据可以被文本编辑器、execl 读取。Memory如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用，Memory 表至少比 MyISAM 表要快一个数量级。FederatedFederated 引擎是访问其他 MySQL 服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。分库分表SQL 优化建立索引调整 my.cnf 优化服务器及开发云主机域名配置参数数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引；一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上；虽然索引提高了查询的效率，但是也降低了更新的效率，因为更新表时，不仅要插入数据，同时还要保存一下索引文件每次更新添加了的索引列的字段，都会调整因为更新所带来的键值变化后的索引信息；实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。如图所示，磁盘块 1 包含数据项 17 和 35，包含指针 P1、P2、P3P1 表示小于 17 的磁盘块，P2 表示介于 17 和 35 之间的磁盘块，35 表示大于 35 的磁盘块查找过程如果要查找数据项 29，首先将磁盘块 1 加载到内存，此时发生一次 IO，利用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 加载到内存，此时发生一次 IO，利用二分查找确定 29 在26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过磁盘块 3 的 P2 指针的磁盘地址把磁盘块 8 加载到内存，此时发生一次 IO，同时利用二分查找到 29，查询结束。B+ 树的非叶子节点只是存储 key，占用空间非常小，因此每一层的节点能索引到的数据范围更加的广，换句话说，每次 IO 操作可以观看更多的数据；叶子节点两两相连，符合磁盘的预读特性。如图存储 5、8 、9 的叶子节点，它有个指针指向了 10、15、18 这个叶子节点，那么当我们从磁盘读取5、8、9 对应的数据的时候，由于磁盘的预读特性，会顺便把 10、15、18 对应的数据读取出来，这个时候属于顺序读取，而不是磁盘寻道了，加快了速度；支持范围查询，而且部分范围查询非常高效，原因是数据都是存储在叶子节点这一层，并且有指针指向其他叶子节点，这样范围查询只需要遍历叶子节点这一层，无需整棵树遍历。聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，聚簇表示数据行和相邻的键值聚簇的存储在一起；按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不不用从多个数据块中提取数据，所以节省了大量的 IO 操作；对于 MySQL 数据库目前只有 InnoDB 数据引擎支持聚簇索引，而 MyISAM 并不支持聚簇索引；由于数据物理存储排序方式只能有一种，所以每个 MySQL 的表只能有一个聚簇索引，一般情况下就是该表的主键；为了充分利用聚簇索引的聚簇的特性，所以 InnoDB 表的主键列尽量选用有序的顺序 ID，而不建议用无序的 ID，比如 UUID这种。即一个索引只包含单个列，一个表可以有多个单列索引索引列的值必须唯一，但可以为空设为主键后自动创建主键索引一个索引包含单个列哪些情况需要创建索引？哪些情况不需要创建索引？使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理 SQL 语句的，分析查询语句或是表结构的性能瓶颈。EXPLAN 的作用：查看表的读取顺序查看哪些索引可以被使用数据读取操作的操作类型哪些索引被实际使用表之间的引用使用方式：Explain + SQLid：SELECT 查询的序列号,包含一组数字，表示查询中执行 SELECT 子句或操作表的顺序id 相同：执行顺序由上至下id 不同：如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行每个 id 表示一趟独立的查询，一个 SQL 的查询趟数越少越好select_type查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询SIMPLE：最简单的查询，不包含 UNION 和子查询PRIMARY：查询中若包含复杂的子部分，最外层查询被标记为 PRIMARYDERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED，MySQL 会递归执行这些子查询, 把结果放在临时表里SUBQUERY：在 SELECT 或 WHERE 列表中包含子查询DEPENDENT SUB：在 SELECT 或 WHERE 列表中包含子查询，子查询基于外层UNCACHEABLE SUBQUREY：结果集不能被缓存的子查询，必须重新为外层查询的每一行进行评估UNION：若第二个 SELECT 出现在 UNION 之后，则被标记为 UNIONtable显示这一行的数据是关于哪张表的type显示连接使用的类型，按最优到最差的类型排序system：表只有一行记录const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引，因为只匹配一行数据，所以很快
如将主键置于 where 列表中，MySQL 就能将该查询转换为一个常量eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体range：只检索给定范围的行，使用一个索引来选择行，key 列显示使用了哪个索引，一般就是在 where 语句中出现了 between、、in 等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引index：出现 index 是 SQL 使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组all：Full Table Scan，将遍历全表以找到匹配的行index_merge：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的 SQL 中ref_or_null：对于某个字段既需要关联条件，也需要 null 值的情况下，查询优化器会选择用 ref_or_null 连接查询index_subquery：利用索引来关联子查询，不再全表扫描一般来说，得保证查询至少达到 range 级别，最好能达到 refpossible_keys显示可能应用在这张表中的索引，一个或多个，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用key实际使用的索引，如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的 select 字段重叠key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度， key_len 字段能够检查是否充分的利用上了索引ref显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值rowsrows 列显示 MySQL 认为它执行查询时必须检查的行数Extra包含不适合在其他列中显示但十分重要的额外信息index(a,b,c)创建索引的建议：对于单值索引，尽量选择针对当前查询过滤性更高的字段选择组合索引，当前查询过滤性最高的字段在索引的位置越靠前越好选择组合索引，尽量选择可以能够包含当前查询中的 where 字句中更多字段的索引在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面保证被驱动表的 join 字段已经被索引left join 时，选择小表作为驱动表，大表作为被驱动表inner join 时，MySQL 会自己把小结果集的表选为驱动表子查询尽量不要放在被驱动表，有可能使用不到索引能够直接多表关联的尽量直接关联，不用子查询尽量不要使用not in 或者 not exists，用 left join on xxx is null 替代ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序如果不在索引列上，filesort 有两种算法：双路排序单路排序：group by 使用索引的原则几乎跟 order by 一致 ，唯一区别是 group by 即使没有过滤条件用到索引，也可以直接使用索引什么是慢查询日志？慢查询日志是 MySQL 提供的一种日志记录，它用来记录在 MySQL 中响应时间超过阈值的语句，具体指运行时间超过long_query_time 值的 SQL，则会被记录到慢查询日志中；long_query_time 的默认值为10，意思是运行10秒以上的语句。默认慢查询日志是关闭的，需要手动开启查看并配置 long_query_time日志分析工具 mysqldumpslow常用参考：复制的基本原理master 将改变记录到二进制日志（binary log），这些记录过程叫做二进制日志事件，binary log events；slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）；slave 重做中继日志中的事件，将改变应用到自己的数据库中，MySQL 复制是异步的且串行化的。复制的基本原则每个 slave 只有一个 master每个 slave 只能有一个唯一的服务器 ID每个 master 可以有多个salve1、配置主数据库vim /etc/my.cnf为从服务分配账号查看主服务器 BIN 日志的信息重启主数据库2、配置从数据库连接主数据库启动从数据库

相关推荐: SaltStack实战之配置管理-LAMP自动化部署

学习SaltStackSaltStack实战之配置管理-LAMP自动化部署1. 部署思路2. 编写lamp.sls将lamp分成3部分：软件包、配置文件、服务；确认安装的软件包名、配置文件路径、服务名等；按以上信息编写yaml文件；执行salt ‘*’ sta…