mysql数据库优化

Mysql数据库架构

1. 连接管理和安全验证是什么？
     每个客户端都会建立一个与服务器连接的线程，服务器会有一个线程池来管理这些链接；如果客户端需要连接到Mysql数据库还需要进行验证，包括用户名、密码、主机信息等。

2. 解析器
     解析器的作用主要是分析查询语句，最终生成解析树；首先解析器会对查询语句的语法进行分析，分析语法是否有问题。还有解析器会查询缓存，如果在缓存中有对应的语句，就返回查询结果不进行接下来的优化执行操作。前提是缓存中的数据有没被修改，当然如果被修改了也会被清除缓存。

3. 优化器
     优化器的作用主要是对查询语句进行优化操作，包括选择合适的索引，数据的读取方式，包括获取查询的开销信息，统计信息等，这也是为什么图中会有优化器指向存储引擎的箭头。之前在别的文章没有看到优化器跟存储引擎之间的关系，在这里我个人的理解是因为优化器需要通过存储引擎获取查询的大致数据和统计信息。

4. 执行器
     执行器包括执行查询语句，返回查询结果，生成执行计划包括与存储引擎的一些处理操作

 查询过程

　　在进行MySQL的优化之前，必须了解的就是Mysql的查询过程，很多查询优化工作实际上就是遵循一些原则，让MySQL的优化器按照预想的合理的方式执行

 数据库层面问题解决思路

　　检查问题常用

    1）MySQL
    2）msyqladmin：MySQL客户端，可进行管理操作
    3）mysqlshow：功能强大的查看shell命令
    4）show [SESSION | GLOBAL] variables：查看数据库参数信息
    5）SHOW [SESSION | GLOBAL] STATUS：查看数据库的状态信息
    6）information_schema：获取元数据的方法
    7）SHOW ENGINE INNODB STATUS：Innodb引擎的所有状态
    8）SHOW PROCESSLIST：查看当前所有连接session状态
    9）explain：获取查询语句的执行计划
    10）show index：查看表的索引信息
    11）slow-log：记录慢查询语句
    12）mysqldumpslow：分析slowlog文件的

 

 　　

一般应急的调优思路：针对突然的业务卡顿，无法进行正常的业务处理看，需要立马解决的场景

1）show processlist；//查看当所有的session状态
2）explain  select id ,name from stu where name='clsn'; # ALL  id name age  sex；
select id,name from stu  where id=2-1 函数 结果集>30；show index from table；
3）通过执行计划判断，索引问题（有没有、合不合理）或者语句本身问题；
4）show status  like '%lock%';    # 查询锁状态
kill SESSION_ID;   # 杀掉有问题的session

 

 常规调优思路：针对业务周期性的卡顿，例如在每天10-11点业务特别慢，但是还能够使用，过了这段时间就好了。

1）查看slowlog，分析slowlog，分析出查询慢的语句；
2）按照一定优先级，一个一个排查所有慢语句；
3）分析top SQL，进行explain调试，查看语句执行时间；
4）调整索引或语句本身。

 

 

数据库的优化(重点)

• Sql优化方向:执行计划、索引、SQL语句改写
• 架构优化方向：高可用架构、高性能架构、分库分表、读写分离

 SQL优化方向考虑

　数据库参数的优化

 　　    调整实例整体（高级优化，扩展）：

thread_concurrency：# 并发线程数量个数
sort_buffer_size：# 排序缓存
read_buffer_size：# 顺序读取缓存
read_rnd_buffer_size：# 随机读取缓存
key_buffer_size：# 索引缓存
thread_cache_size：# (1G—>8, 2G—>16, 3G—>32, >3G—>64)

 

 　　　连接层（基础优化）　　

    设置合理的连接客户和连接方式：
    max_connections           # 最大连接数，看交易笔数设置    
    max_connect_errors        # 最大错误连接数，能大则大
    connect_timeout           # 连接超时
    max_user_connections      # 最大用户连接数
    skip-name-resolve         # 跳过域名解析
    wait_timeout              # 等待超时
    back_log                  # 可以在堆栈中的连接数量

 

 

 

 　　  SQL层（基础优化）

query_cache_size：查询缓存>>>OLAP类型数据库,需要重点加大此内存缓存，但是一般不会超过GB。
对于经常被修改的数据，缓存会立马失效。
我们可以实用内存数据库（redis、memecache），替代他的功能。

 

 

 

 　　 存储引擎层（innodb基础优化参数）

 

default-storage-engine
innodb_buffer_pool_size       # 没有固定大小，50%测试值，看看情况再微调。但是尽量设置不要超过物理内存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2) # 1是最安全的，0是性能最高，2折中
binlog_sync
Innodb_flush_method=(O_DIRECT, fdatasync)
innodb_log_buffer_size           # 100M以下
innodb_log_file_size               # 100M 以下
innodb_log_files_in_group       # 5个成员以下,一般2-3个够用（iblogfile0-N）
innodb_max_dirty_pages_pct   # 达到百分之75的时候刷写 内存脏页到磁盘。
log_bin
max_binlog_cache_size                     # 可以不设置
max_binlog_size                               # 可以不设置
innodb_additional_mem_pool_size     #小于2G内存的机器，推荐值是20M。32G内存以上100M

　合理的添加索引

 　　注意：添加索引的时候，不必要的索引一定不要设置，比如在建表的时候，如果不设置默认值，那么mysql数据库都会默认设置为NULL，如果使用NULL会使得索引维护更加复杂，所以说设置NOT NULL

　　深入理解索引

　数据库的表结构

　　Mysql可以支持大量数据的存取，但是一般来说数据库表越小，他执行查询操作就会越快，为了获得更好的性能，我们可以在设置字段宽度的时候尽可能的小，足够支持查询即可，也可以节省内存，
    ♥ 比如在设置name这个字段的时候，如果将其设置为char(255),这样的话显然数据库在变相的增大。

    那么什么时候用char和varchar呢？
    ♥ 比如手机号码，固定11位的，就使用char，
    ♥ 如果是其他的，就使用varchar,设置定长就好啦

　　大表拆分：

　　大表拆分的前提条件是在执行查询语句之前，已经知道需要查询的数据会在哪一个位置上，然后再进行拆分

 

　EXPLAIN（执行计划）

　　  做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。
        下面来个简单的示例，标注（1、2、3、4、5）我们要重点关注的数据：

        ○ type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。
        ○ key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。
        ○ key_len列，索引长度。
        ○ rows列，扫描行数。该值是个预估值。
　　 ○ extra列，详细说明。注意，常见的不太友好的值，如下：Using filesort，Using temporary。

　SQL语句的优化

 　SQL语句中IN包含的值不应过多
        MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用between就不要用in了；再或者使用连接来替换。

　SELECT语句务必指明字段名称
　　SELECT*增加很多不必要的消耗（CPU、IO、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

　当只需要一条数据的时候，使用limit 1
　　这是为了使EXPLAIN中type列达到const类型，也就是查询到这条结果之后就返回，不用查询全表。

　如果排序字段没有用到索引，就尽量少排序

　区分in和exists、not in和not exists
        select * from 表A where id in (select id from 表B)
        上面SQL语句相当于
        select * from 表A where exists(select * from 表B where 表B.id=表A.id)
        区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
        
        关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的SQL语句？
        原SQL语句：
        select colname … from A表 where a.id not in (select b.id from B表)
        高效的SQL语句：
        select colname … from A表 Left join B表 on where a.id = b.id where b.id is null
取出的结果集如下图表示，A表不在B表中的数据：

　　

　使用合理的分页方式以提高分页的效率
        select id,name from product limit 866613, 20
        使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。
        优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法：
        select id,name from product where id> 866612 limit 20
        
　limit的基数比较大是使用between

　避免在where子句中对字段进行null值判断
　　对于null的判断会导致引擎放弃使用索引而进行全表扫描。

　不建议使用%前缀模糊查询

　避免在where子句中对字段进行表达式操作

        select user_id,user_project from user_base where age*2=36;
        中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成：
　　　　 select user_id,user_project from user_base where age=36/2;

 

　关于JOIN优化

　　

        LEFT JOIN A表为驱动表，INNER JOIN MySQL会自动找出那个数据少的表作用驱动表，RIGHT JOIN B表为驱动表。   
        注意：  
        1）MySQL中没有full join，可以用以下方式来解决：   
        select * from A left join B on B.name = A.namewhere B.name is nullunion allselect * from B;
        
        2）尽量使用inner join，避免left join：    
        参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。
        
        3）合理利用索引：      
        被驱动表的索引字段作为on的限制字段。
        
        4）利用小表去驱动大表：

　　从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

 

 　 5）巧用STRAIGHT_JOIN：
        inner join是由MySQL选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。
这个方式有时能减少3倍的时间。

架构优化方向

　数据库集群策略

　　   当有海量数据进行读写操作的时候,服务器的缓存性能是不足以支撑的,海量请求的数据涌入数据库,导致数据库承受不住压力导致宕机,请求并不会消失,导致长时间宕机造成缓存雪崩
        数据的过多,查找缓慢  单机数据库无论如何设计,都会被物理瓶颈限制,需要引入分布式高可用数据库集群
        分布式高可用集群策略是可以实现
        海量数据的存储
        读写分离
        高可用替换
        以双机热备为最小单位
        主从结构可以实现数据备份 保证数据的安全 单机热备
　　  以双机热备为集群结构最小单位  实现数据的高可用

　　详细参考https://www.cnblogs.com/nanlinghan/p/9951596.html

　添加本地缓存和redis缓存

　　  ☀ 本地缓存：默认存储位置在C盘
        ☀ Redis作为mysql数据库的缓存服务器：实现读写分离
        blingbling
        读: 读redis->没有，读mysql->把mysql数据写回redis
        写: 写mysql->成功，写redis（捕捉所有mysql的修改，写入和删除事件，对redis进行操作）
        非关系性数据库 读的速度是110000次/s,写的速度是81000次/s 。性能非常高
        redis具有持久化数据到磁盘的能力,将内存数据在写入之后按照一定规则存储在磁盘文件中,宕机之后,重启redis读取磁盘文件恢复缓存数据;
        单个服务器内存 磁盘空间有限 无法处理海量的缓存数据,必须支持分布式的结构
分担了大量的对数据库的读操作,减轻数据库的压力,解决了缓存雪崩的问题

　引入Mycat插件

 　　 面向企业的开源的大的数据库集群,性能极高
        支持控制数据集群结构的代理软件
        由于数据库的集群,客户端不能有效的管理使用和操作数据库集群,所以出现了mycat中间件,对多种操作数据库的输入输出资源进行整合,大表水平分片,集群并行计算,高性能读写分离.大大的提高了访问数据库的性能.
        注意:是基于数据库集群基础之上的,和redis区分开
        遵守Mysql原生协议，跨语言，跨平台，跨数据库的通用中间件代理
        支持通过全局表，ER关系的分片策略，实现了高效的多表join查询
　　 支持分布式事务