MySQL：性能优化-索引、语句、配置（基于5.7）

   小生博客：http://xsboke.blog.51cto.com

                             -------谢谢您的参考，如有疑问，欢迎交流

一、 数据库的类型

  1） 第一代数据库：

基于层次模型与网状模型的数据库

• 层次型数据库比较经典的是IBM公司的IMS（InformationManagement System）数据库，层次型数据库提供了良好的完整性支持，模型简单，对具有一对多层关系的部们描述非常自然、直观，容易理解，比较适用于那些实体间联系是固定的且预先定义好的环境，其性能优于关系模型；但是层次模型数据库对查询、插入和删除操作限制的比较多

• 网状型数据库更能够直接的描述现实世界，具有良好的性能，存取效率较高；但是随着应用环境的增大，数据库的架构也会越来越复杂，而且其DDL/DML语言也相对复杂，不利于用户的使用。

  2） 第二代数据库：

关系型数据库

• 常用的关系型数据库有db2、oracle、sqlserver、mysql

• 关系型数据库适用于存放结构化的数据，是基于行和列构成的二维表，对于非结构化的数据一般存放在系统存储中。

  3） 第三代数据库

面向对象型数据库

• 面向对象数据库系统是为了满足新的数据库应用需要而产生的新一代数据库系统，具有易维护、质量高、效率高、易扩展的特点，主要是为了支持非常复杂的数据模型，适用于工程设计领域；但是由于模型较为复杂，使得很多系统管理功能难以实现也不具备SQL处理集合数据的强大能力。

二、 影响mysql性能的因素

  1. 商业需求的影响

例：要求论坛帖子总量实时更新

1） Myisam存储引擎：

• Myisam有专用的计数器，当需要统计行数时，直接读取计数器即可，所以使用myisam存储引擎的数据库在这方面不需要很担心，但是myisam不支持事务，不支持行锁，所以现在一般都很少使用

2）Innodb存储引擎：

• Innodb存储引擎是没有专用的计数器的，所以在统计帖子总量时会相当耗费时间，虽然可以专门为统计帖子总数去创建一个，但是如果出现非常大的并发写入，那这种方法就不很难解决了

  2. 系统架构及实现的影响

1)二进制多媒体数据

• 如果将图片、音乐、视频等其他的一些相关二进制文件直接存放在数据库中，将会占用大量的数据库空间资源和消耗数据库主机的CPU资源，解决办法是将这些二进制文件存放在磁盘中

2）超大文本数据

• 对于一些比较大的文本数据也不适用于存储在数据库中，因为会使数据库的性能地下，而且浪费空间，所以可以存放在磁盘中

  3. 查询语句对性能的浪费

对于数据库管理软件，最大的性能瓶颈就在于磁盘的IO，当查询某个数据时，需要多次进行磁盘IO；将数据块加载到内存并且进行数据查找，当查询某个数据时，可能需要几百上千次进行这种磁盘IO的操作，所以在资源消耗方面是很大的，但是如果以不同的方式去寻找其中的某一点内容时，所需的IO次数可能相对就减少，这里说的也就是索引

  4. 数据库schema设计对性能的影响

   就是数据库的数据结构

  5. 硬件性能的影响

l  磁盘的I/O性能：内存、硬盘、网卡等

l  CPU的处理能力

l  网络设备：交换路由、网线网卡等

三、mysql性能优化-----索引

  1. 索引作用

索引通过减少磁盘IO而优化了查询速度，但是在一定程度上降低了增删改的性能，因为索引相当于一本书的目录，书的内容改变，索引也需要更新，而且索引是存放在物理文件中的，相对的也占用了磁盘空间

  2. 索引的类型

索引一般分为B-tree、R-tree和全文索引，但是innodb存储引擎是不支持全文索引的，而且常用的索引就是B-tree索引也叫平衡树索引

  3. B-tree索引的结构及原理

• 在查询数据时，首先将磁盘块1调入内存，然后根据数据项的范围查询到相对应的指针，然后根据指针再次调用一个磁盘块，就这样通过循环查询缩小查询范围，最后得到想要的数据

• 使用索引进行查询可能只需要几次的磁盘IO就能找到想要的数据，而没有索引时需要遍历数据库进行查找数据可能要经过几十上百甚至更多的磁盘IO才能得到想要的数据，这就体现了索引的有点

• 底层存放真实数据的叫叶子，而其他层都是非叶子节点

  4. 索引的优点

• 提高查询速度，快速定位数据，大大提高mysql的查询（包括排序，分组）效率

  5. 索引的缺点

• 索引是以文件形式存放在硬盘的，从一定程度上占用了硬盘空间

• 写数据时需要更新索引，这对数据库是个很大的开销

  6. 不建议使用索引的情况

• 表记录较少时（一般为1000行）

• 索引的选择性较低时，比如有一列是性别，只有男女两种数据，这时选择性就较低

  7. 索引的类型

索引包括单列索引和组合索引

1）普通索引

• 这是最基本的索引，它没有任何限制

• CREATE INDEX indexName ON tablename(column1[,column2,……])

2）唯一索引

• 它与前面的普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值是指null。如果是组合索引，组合列的值必须唯一

• CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])

3）主键索引：一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

• CREATE TABLE tablename(ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  PRIMARY KEY(ID) );

4）组合索引

• 为了进一步提升MySQL的效率，就要考虑建立组合索引，组合索引就是由多个列组成的索引

• 组合索引的有效列

• 组合索引有最左前缀的特点，比如创建一个索引，列值取A、B、C，在使用命令创建索引时，三个值的顺序将决定什么情况下使用索引，如果从左至右一次为A、B、C，那么在查询数据时，只有条件为ABC、AB、A时才使用索引，而且条件的顺序不能颠倒，如果条件为BC、B、C时，索引将不被使用

5）全文索引（几乎不使用，一般用于对大数据进行查询时）

• 只用于MyISAM 表 对文本域进行索引。字段类型包括char、varchar、text

• 不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

• CREATE FULLTEXT INDEX indexname ON tablename(column)

  8. 查询索引

• mysql> show index from 表名；

• mysql> show keys from 表名；

  9. 在什么情况下需要建立索引，应该选择什么列建立索引

l  当表的记录较多，查询速度较慢时我们就需要通过建立索引的方式优化数据库的查询性能

l  一般经常出现在where和join子句中的列需要建立索引

l  使用索引的注意事项

• 当使用like做模糊查询时，以通配符“%”或“_”为开头做查询将不会使用索引

• 当在列上进行运算时，索引也会失效

• MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,

• 不要过渡建立索引，适可而止，因为每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，因为在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。

四、mysql性能优化的思路

  1. 基本思路

• 通过show查询系统的状态等信息，去优化数据库

• 通过慢查询日志去优化查询速度

• Explain工具可以模拟语句执行，然后通过输出结果得到语句的执行信息

• Profiling工具可以使语句真实执行，并且通过输出结果得到语句的执行信息

  2. 索引优化

1)比如有用户投诉公司网站查询数据特别慢，这时需要我们优化数据库的查询性能，用户是不知道使用了什么sql语句的，所以需要我们去找到sql语句，并对其进行优化

• 首先开启慢查询日志，记录查询超过N秒的语句

• 然后根据条件创建索引等

   2)        慢查询日志

  3） 通过explain工具模拟语句执行，进行分析查询

  4） 通过profiling工具对实际进行的查询语句进行分析，相比explain更准确

    执行要测试的语句并查看反馈的信息

    通过语句的query_id号可以查看语句执行的详细信息

五、 配置优化

   就是my.cnf中的参数配置

  1. Max_connections：最大连接数，只对myisam存储引擎有效

   查看当前已经建立的连接

   查看试图连接或已连接数

   当连接大于最大连接数时，可能报错1040：too  many  connections

   判断当前最大连接数的方法：

  2. Back_log：mysql请求队列数，只对myisam存储引擎有效

   当mysql并发连接数达到最大时，允许被放入队列的连接数

   查看当前正在等待的队列数

  3. Wait_timeout 和interactive_timeout，只对myisam存储引擎有效

   非交互式和交互式的闲时超时时间

  4. Key_buffer_size：索引缓冲区大小，只对myisam存储引擎有效

   作用：

      决定索引处理速度

   优化方法：

  5. Query_cache_size:查询缓存，简称QC，只对myisam存储引擎有效

   1)        配置参数

   2）作用：

• 使用查询缓冲区，mysql将查询过的结果存放在缓冲区中，今后对于同样的select语句（select语句的大小写和空格），将直接从缓冲区中读取结果

   3） 通过检查状态值‘Qcache%’可以判断QC设置是否合理

• Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明Query Cache 中的内存碎片较多了，查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% ，如果查询缓存碎片率超过20%，使用FLUSHQUERY CACHE对缓存中的碎片进行整理

• Qcache_free_memory：Query Cache 中目前剩余的内存大小。

• Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想。

• Qcache_inserts：表示多少次未命中然后插入。

• Qcache_lowmem_prunes：多少条Query 因为内存不足而被清除出Query Cache。通过“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系统中Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query 被换出。

• Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT 语句或者用了now()之类的函数。

• Qcache_queries_in_cache：当前Query Cache 中cache 的Query 数量；

• Qcache_total_blocks：当前Query Cache 中的block 数量；。

   4） 关于query_cache的配置

  6. Max_connect_errors：与mysql安全有关的计数器值，最大错误连接数，

    只对myisam存储引擎有效

• 防止暴力破解，当用户连续输错多少次密码后锁定用户

• 清除计数器：重启mysql服务或者使用命令flush  hosts

  7. Sort_buffer_site：排序缓冲区大小，只对myisam存储引擎有效

• 影响带有order by或group  by的查询语句查询速度

• 此配置是一个连接级（connection级）参数，每个连接的用户都被单独分配一个缓冲区

  8. Max_allowed_packet：限制server接受的select、update语句包的大小，

    只对myisam存储引擎有效

• 最大值为1GB

  9. Join_buffer_size：影响带有多表查询类型语句的查询速度，

    connection级参数，只对myisam存储引擎有效

  10. Tread_cache_size：设置服务器线程缓存，只对myisam存储引擎有效

• 设置规则：1GB内存8个、2GB内存16个、3GB内存32个

• 这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限)

  11. Innodb_buffer_pool_size：相当于key_buffer_size对于MyISAM表的作用一样

• InnoDB使用该参数指定大小的内存来缓冲数据和索引。对于单独的MySQL数据库服务器，最大可以把该值设置成物理内存的80%

• 根据MySQL手册，对于2G内存的机器，推荐值是1G（50%）。 如果你的数据量不大，并且不会暴增，那么无需把 innodb_buffer_pool_size 设置的太大了。

  12. innodb_flush_log_at_trx_commit：控制事务日志刷写的时间点

  13. innodb_thread_concurrency ：设置innodb线程的并发数量

• 默认值为0表示不限制，若要设置则与服务器的CPU核数相同或是cpu的核数的2倍，建议用默认设置，一般为8

  14. innodb_log_buffer_size

• 确定事务日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。

  15. innodb_log_file_size

• 确定数据日志文件的大小，以M为单位，更大的设置可以提高性能.

  16. innodb_log_files_in_group

• 设置日志文件的组数

  17. read_buffer_size ：

• MySql 读入缓冲区大小。

• 对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

• 和sort_buffer_size一样，该参数对应的分配内存也是每个连接独享。

  18. read_rnd_buffer_size：

• MySql 的随机读（查询操作）缓冲区大小。

• 当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

• 顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因此访问方式是随机的。

  19. bulk_insert_buffer_size ：

• 批量插入数据缓存大小，可以有效提高插入效率，默认为8M。

  20. binary log日志文件的配置参数

六、优化后通过mysqlslap工具可以进行测试

  优化前

  将所有优化语句写入，进行测试

七、 优化语句总结