MySQL优化（超完整版）（一）

  前言：
    MySQL数据库的优化模块：
      – 数据库的设计—三大范式
      – 数据库的索引：唯一索引、主键索引、聚合索引、复合索引、默认索引
      – SQL优化
      – 分库分表
      – 读写分离：提升IO性能
      – 存储过程优化
      – 对MySQL配置进行优化（my.ini）
      – 定时清理碎片    为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。三大范式包括：
    – 1NF：属性的原子性，要求属性具有原子性，不可分解。
    – 2NF：对记录的唯一性，表中记录是唯一的（通常通过主键来实开发云主机域名现）。
    – 3NF：是对字段冗余性的约束，要求字段没有冗余。– 案例    慢查询的定义：MySQL规定，只要10s内，没有按照规则的时间返回结果，就是慢查询类型，然后MySQL会将这些语句存储到慢查询日志中。
可以通过命令查看注意：这里默认的是session，表示的时当前会话，如果想查询全局的需要：①慢查询时间设置②建表、造数据③批量生成100W条数据④设置MySQL记录慢查询日志
首先将MySQL服务关闭：

进入mysql/bin下执行一下命令：
[mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）然后在my.ini配置文件中有这么一行：

在这个目录下，会生成相应的慢查询记录。
#5.7版本自动开启：

然后我们设置慢查询时间为：1s用刚刚造出来的数据执行一个慢查询:查看慢查询日志：

通过日志，我们就能定位到具体的是哪一条语句查询慢。   索引用来快速的查询那些具有特定值的记录。所有的MySQL索引都是以B+树的形式保存的。如果没有索引，执行查询时MySQL必须从第一个记录开始，进行全表扫描，直至找到合适的记录。表里的记录越多，这个操作越耗时。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录，通过索引查找记录至少要比顺序扫描记录快100倍。   主键索引是一种唯一性索引，但是它必须指定“PRIMARY KEY”。主键一般在创建表的时候指定，并且一张表只能有一个主键。    全文索引一般用于查询文本或者长内容而建立。使用全文索引的注意事项：
    – MySql自带的全文索引只能用于数据库引擎为MYISAM的数据表，如果是其他数据引擎，则全文索引不会生效.
    – MySQL不能对中文进行全文索引，只适用于英文.
    – 使用全文索引，只能用固定的语法：match(字段名)… against(关键字).
    – MySQL全文索引所能找到的默认最小长度为4个字符，并且如果查询的字符串包含停止词（常见字符），那么该停止词将会被忽略。   这种索引的所有值都只能出现一次，即必须唯一。
默认的，在创建表时指定字段为唯一时，自动为其创建唯一索引。    普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（ORDERBYcolumn）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。   MySQL数据库的索引，是数据库管理中的一个排序的数据结构，以便于协助快速查询，更数据库表中数据。MySQL中的索引实现方式就是：B+树索引。

   上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在 O(log2n)的复杂度内获取到相应数据。
不同的存储引擎使用的索引：

    b-/+树索引的性能分析：先从 B-Tree 分析，根据 B-Tree 的定义，可知检索一次最多需要访问 h 个节点。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的，在实际实现 B-Tree 还需要使用如下技巧：
   每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个 node 只需一次 I/O。B-Tree 中一次检索最多需要 h-1 次 I/O（根节点常驻内存），渐进复杂度为 O(h)=O(logdN)。一般实际应用中，出度 d 是非常大的数字，通常超过 100，因此 h 非常小（通常不超过 3）。
   而红黑树这种结构，h 明显要深的多。由于逻辑上很近的节点（父子）物理上可能很远，无法利用局部性，所以红黑树的 I/O 渐进复杂度也为 O(h)，效率明显比 B-Tree 差很多。
   综上所述，用 B-Tree 作为索引结构效率是非常高的。
    B+ 树非叶节点中存放的关键码并不指示数据对象的地址指针，非也节点只是索引部分。所有的叶节点在同一层上，包含了全部关键码和相应数据对象的存放地址指针，且叶节点按关键码从小到大顺序链接。如果实际数据对象按加入的顺序存储而不是按关键码次数存储的话，叶节点的索引必须是稠密索引，若实际数据存储按关键码次序存放的话，叶节点索引时稀疏索引。
   B+ 树有 2 个头指针，一个是树的根节点，一个是最小关键码的叶节点。
所以 B+ 树有两种搜索方法：
   一种是按叶节点自己拉起的链表顺序搜索。
   一种是从根节点开始搜索，和 B 树类似，不过如果非叶节点的关键码等于给定值，搜索并不停止，而是继续沿右指针，一直查到叶节点上的关键码。所以无论搜索是否成功，都将走完树的所有层。
B+ 树中，数据对象的插入和删除仅在叶节点上进行。综上所述，两种排序的不同之处在于：
    – B 树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而 B+ 树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持 B+ 树的平衡。
    – 因为 B 树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+ 树相比来说是一种较好的折中。
    – B 树的查询效率与键在树中的位置有关，最大时间复杂度与 B+ 树相同(在叶结点的时候)，最小时间复杂度为 1(在根结点的时候)。而 B+ 树的时候复杂度对某建成的树是固定的。可以扫描2的次方。  优势：
    – 创建索引可以大大提高系统性能
    – 大大加快对数据的检索速度
    – 加速表和表之间的连接
    – 对使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
  劣势：
    – 建立索引耗时：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
    – 占用一定的物理内存：索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
    – DML操作效率变低： 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。  应该建立索引的字段：
    – 查询作为查询条件字段应该创建索引
    – 经常用在连接的字段可以建立索引
    – 经常需要根据范围进行搜索的列上创建索引
    – 经常需要排序的列上创建索引
  不应建立索引的字段：
    – 那些在查询中很少使用或者参考的列不应该创建索引
    – 对于那些只有很少数据值的列也不应该增加索引，例如：性别、是否已婚等等
    – 对于那些定义为 text, image 和 bit 数据类型的列不应该增加索引
    – 修改性能远远大于检索性能时，不应该创建索引① 联合索引的失效场景综上案例得出：创建的多列索引，如果不是使用第一部分，则不会创建索引。② 模糊查询时like,索引不会失效，但是如果like中有‘%xxx%’，则索引失效，但是%放在后面索引不会失效，例“xxx%”，但是%放在前面会失效，例：“%xxx”。
③ 如果条件中有or，及时其中有带索引字段，也不会使用索引
④ 如果类型为字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引
⑤ 如果MySQL的全表扫描比使用索引快，则不使用索引。handler_read_key：这个值越高越好，越高表示使用索引查询到的次数。
handler_read_rnd_next：这个值越高，说明查询低效。    使用group by 分组查询是，默认分组后，还会排序，可能会降低速度，在group by 后面增加 order by null 就可以防止排序。原因：使用join，MySQL不需要在内存中创建临时表。    MySQL使用的存储引擎有三种：myisam / innodb/ memory。
    Myisam存储：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎. ,比如 bbs 中的 发帖表，回复表。
    innodb存储：对事务要求高，保存的数据都是重要数据，我们建议使用INNODB,比如订单表，账号表。
    Memory：基于内存存储，类似于Redis。   - 事物安全（MyISAM不支持事务，INNODB支持事务）
   - 查询和添加速度（MyISAM批量插入速度快）
   - 支持全文索引（MyISAM支持全文索引，INNODB不支持全文索引）
   - 锁机制（MyISAM时表锁，innodb是行锁）
   - 外键机制（MyISAM 不支持外键， INNODB支持外键）
Ps：Memory 存储，比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快。
   如果我们在建表的时候指定的是myisam引擎，那么在我们删除表数据时，默认只是逻辑删除，而真正的物理存储的数据文件是不会删除的。
   接下来由下图，为大家讲解一下MySQL数据存放的方式：
   在my.ini文件中有：datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data 这个配置就是本地系统存放MySQL数据文件的目录。

进入具体的数据库：（test）

这其中：
   .frm结尾的表示表的结构文件
   .MYD结尾的表示数据文件
   .MYI结尾的表示索引文件
#然后我们演示如果使用的是myisam引擎，数据如何删除：此时我们查看test100.MYD 发现这个文件的大小根本没有变化。之后test100.MYD大小就会改变了，数据真正意义上的删除了。编写备份脚本，然后通过crontab 定时执行。小编在下篇文章给大家MySQL如何分库分表，MySQL负载均衡版的读写分离。绝对没有水货！！！！！！！！！

相关推荐: 电路图原理图怎么画？它的常用符号有哪些?

现在很多同学喜欢用word写作，有时会涉及到电路图原理图，而电路图是利用电路的各种符号来表示电路连接，一般是用于研究、工程规划的需要。在设计电路时，工程师可以根据纸上的原理以及符号，进行调试电路，修复错误和提高工程师的工作效率。电路图原理图是电路图的分类之一。…