MySQL优化技术


简介

由于页面静态化技术可以实现对动态数据的缓存，但是有的时候还是需要去请求数据库。所以对数据库的优化也是不可缺少的。

优化思路

设计：存储引擎，字段，范式

自身：索引，自身的缓存

架构：读写分离

 

存储引擎：

MyISAM和InnoDB之间的对比。当然需要知道MySQL除了这两种存储引擎还有其他的存储引擎（memory存储引擎）。

MySQL在5.5版本之后默认的存储引擎为InnoDB

在面试的过程中，只要说出MyISAM和InnoDB的区别即可

 

字段选择：

合适即好，能用tinyint就不要使用int

 

范式：三范式 主要是为了减少数据的一个冗余，基本上设计出来的表都是满足的。

 

注意：一般在开发的时候，设计之初，最先设计的就是表与表之间的关系，在后期的开发过程中是很少修改表结构的。（系统架构、经理）

 

自身考虑

在做MySQL自身优化的时候，既然要去优化，那就要先去发现问题？如何发现问题？

发现问题

  1. 需要去定位问题？

    答：可以使用MySQL提供的一种慢查询日志功能来发现有问题的sql语句，然后对其进行优化。

  2. 什么事慢查询日志？

    答：MySQL提供的日志，可以用来记录超过某一个规定的时间界限的sql语句。

    使用MySQL的慢查询功能

  3. 开启，MySQL的配置文件里面，默认是不存在的，需要自己去添加，需要重启MySQL

     

  4. 测试看是否能够记录超过时间界限的sql语句
    1. 需要构建一张大型数据表

    b) 在没有索引的字段上做一个查询

    慢查询日志分析：

    注意：在有索引的字段上做查询操作

     

    总结：通过对比发现使用索引之后，明显查询的速度会快很多，但是使用MySQL的慢查询日志功能记录的时间相对来说还是很粗糙（慢查询的记录时间级别只能是 1s 以上的）的。

    这个时候如果需要记录时间为 1s 以下的时候，慢查询就无法发挥其作用，可以使用MySQL提供另外一个功能来完成，这个功能叫做 profile 。

     

    MySQL的profile的使用

     

    MySQL提供的一个可以记录更加精确时间的功能，能在1s 以下的sql语句都记录下来。

     

  5. 使用：默认是关闭的，需要自己开启

    # show varibales like "%profil%"; 当有的时候无法记清的时候，可以使用这种模糊匹配查询。

  6. 开启

    # set profiling=1;

  7. 简单的测试

    # show profiles; 可以查看profile工具记录的时间

  8. 使用该工具还可以详细的分析这个sql语句在执行的时候，每一步（如：连接，权限检查，sql语句编译，打开表。。。。执行，数据返回）花费的时间。
    1. 例如现在详细的定位第四条sql语句执行时候每一步花费的时间

      # show profile for query 4;

    注意：为什么使用索引之后会那么快？

    答：由于MySQL启动的时候一般也会将索引文件载入内存里面，即使没有，索引的载入也比去磁盘上对一条一条的查找要快。 然后索引本身是非常利于查找的一种结构，这个时候可以通过索引快速的去定位需要查询的数据，当查询到之后，然后在去磁盘上将数据取出来。

     

    总结：通过使用上面的这些工具，可以粗略发现，在使用索引之后，效果有明显的提升，所以有必要对MySQL数据库进行索引方面的优化操作。

     

    MySQL索引简介

     

    MySQL索引的分类

  9. 主键索引：主要是其快速查找和约束的效果，并且一张表里面只能有一个主键索引。不允许字段里面出现null
  10. 唯一索引：主要是快速查找和约束，但是字段可以有null存在，在建立唯一索引的字段上面的信息不允许重复，但是可以为多个字段建立多个唯一索引。
  11. 普通索引：主要是为了快速查找，但是需要注意的是，索引建立也是有代价的。索引能快速的查找，但是在数据插入、更新的时候，索引会影响插入的效率。所以普通索引也不要建立的过多。
  12. 全文索引：一般都是对text字段生效，但是由于MySQL的存储引擎只有MyISAM支持，并且只支持英文，所以可以使用另外的一个软件叫做sphinx来代替全文索引。
    1. 还有一种情况，就是需要注意一般使用 like 做模糊查询的时候 select * from tableName where name like "%张%"; 是无法使用索引的。 like这种查询就要做全表扫描。

    索引的管理

     

    索引是一种排好序的，快速查找的数据结构。

    什么叫排好序？

    例如上面的这个结构，可以发现，在D字母之后是不可能在出现A字母相关的信息的。

     

  13. 索引的创建
    1. 建表时候创建

    1. 在修改表结构时候创建

     

  14. 索引显示

     

  15. 索引的删除

    问题：为什么没有提供更新操作？

    答：没必要专门设计一个更新，只需要先删，在建。

     

    索引的结构

     

    由于索引本身也是一种数据，也需要占据磁盘空间，所以索引也是有数据结构的，常见的数据结构分为两类：B-TREE结构 和 聚簇结构。

     

    B-TREE结构

     

    对于B-TREE结构的索引，索引节点里面保存的是物理行地址，当查询的时候需要做回行的操作。对于MyISAM存储引擎的所有索引时候的都是B-TREE结构。

    结构图：

    聚簇结构

     

    对于聚簇结构的索引，索引节点里面保存的是该行的记录，当查询的时候不需要做回行的操作。对于InnoDB存储引擎的所有索引时候的都是聚簇结构。

    注意1：对于InnoDB的存储引擎来说，如果不存在主键索引，这个时候MySQL会自动的维持一个主键索引。

    注意2：对于InnoDB的存储引擎来说，次级索引（唯一和普通索引）的索引节点里面保存的信息是对主键索引的一个引用。

    注意3：对于InnoDB的存储引擎来说，如果是大批量的添加数据，这个时候会有很大的性能开销， 主要是在大批量插入数据的时候，主键索引也会重建，这个时候由于主键索引的索引节点里面保存的是该行的一个记录，所以要大批量的在内存中移动，开销非常大。

    结构图：

     

    扩展

     

    阅读书籍

    <<高性能MySQL>>

    地址：

     

    问题1：

    解决思路：

    只要使得上面的两个条件中的一个或者两个都不成立即可。

    那可以做一个刷新缓存的按钮，再要在缓存有效期内更改了，就清除生成的静态页。

本文永久更新链接地址：

相关内容