精华内容
下载资源
问答
  • MYSQL优化 1 1. 我们可以且应该优化什么? 2 2. 优化硬件 2 3. 优化磁盘 2 4. 优化操作系统 3 5. 选择应用编程接口 3 6. 优化应用 3 7. 应该使用可移植的应用 4 8. 如果你需要更快的速度 4 9. 优化MYSQLD 4 10. 编译...
  • 随着互联网web2.0网站的兴起,传统的关系数据库在处理web2.0网站,特别是超大规模和高并发的SNS类型的web2.0纯动态网站已经显得力不从心,出现了很多难以克服的问题,而非关系型的数据库则由于其本身的特点得到了...

    关于NoSQL

    随着互联网web2.0网站的兴起,传统的关系数据库在处理web2.0网站,特别是超大规模和高并发
    的SNS类型的web2.0纯动态网站已经显得力不从心,出现了很多难以克服的问题,而非关系型的
    数据库则由于其本身的特点得到了非常迅速的发展。NoSQL数据库的产生就是为了解决大规模数
    据集合多重数据种类带来的挑战,尤其是大数据应用难题。
    
    NoSQL有如下优点:易扩展,NoSQL数据库种类繁多,但是一个共同的特点都是去掉关系数据库
    的关系型特性。数据之间无关系,这样就非常容易扩展。无形之间也在架构的层面上带来了可扩展
    的能力。大数据量,高性能,NoSQL数据库都具有非常高的读写性能,尤其在大数据量下,同样表
    现优秀。这得益于它的无关系性,数据库的结构简单
    

    一,关系型数据库和非关系型数据库

    1,关系型数据课

    • 一个结构化的数据库,创建在关系模型基础上
    • 一般面相与记录
    • 包括以下
      • oracle,mysql,sql server,microsoft access,db2等 sybase数据库才是老大

    2,非关系型数据课

    • 除了主流的关系型数据库外的数据库,都认为是非关系型数据库
    • 包括以下
      • redis, mongbd,hbase,couhdb等

    3,时间序列数据库

    • 是原本的非关系数据库中加入了时间轴的概念
    • 例如微信朋友圈

    4,非关系型数据库产生背景

    • High performance—对数据库高并发读写需求
    • huge storage–对海量数据高效存储与访问需求
    • high scalability && high availability----对数据库高可扩展性与高可用性需求

    二,redis安装部署

    1,什么是Redis ?

    • Redis(Remote Dictionary Server ),即远程字典服务,是一个开源的使用ANSI C语言编写、支持网络、可基于内存亦可持久化的日志型、Key-Value数据库,并提供多种语言的API。同时 redis也是一个key-value存储系统。和Memcached类似,它支持存储的value类型相对更多,包括string(字符串)、list(链表)、set(集合)、zset(sorted set --有序集合)和hash(哈希类型)。这些数据类型都支持push/pop、add/remove及取交集并集和差集及更丰富的操作,而且这些操作都是原子性的。在此基础上,redis支持各种不同方式的排序。与memcached一样,为了保证效率,数据都是缓存在内存中。区别的是redis会周期性的把更新的数据写入磁盘或者把修改操作写入追加的记录文件,并且在此基础上实现了master-slave(主从)同步。

    • Redis 是一个高性能的key-value数据库。 redis的出现,很大程度补偿了memcached这类key/value存储的不足,在部 分场合可以对关系数据库起到很好的补充作用。它提供了Java,C/C++,C#,PHP,JavaScript,Perl,Object-C,Python,Ruby,Erlang等客户端,使用很方便。 也是最受欢迎的数据库之一。

    • Redis基于内存运行并支持持久化

    • 采用key-value(链值对)的存储形式

    • 优点

      • 具有极高的数据读写速度
      • 支持丰富的数据类型
      • 支持数据的的持久化
      • 原子性
      • 支持数据备份
        在这里插入图片描述

    2,Redis安装部署

    (1)操作步骤

    解压软件包–make&&make install —设置redis相关配置文件—查看运行状态

    (2)编译安装Redis
    tat xzvf redis---
    cd redis---/
    make -j3      //加线程编译剫更快
    make PREFIX=/usr/local/redis install
    
    ln -s /usr/local/redis/bin/* /usr/local/bin/
    
    (3) 设置Redis相关配置文件
    [root@server3 redis-5.0.7]# cd utils/
    [root@server3 utils]# ./install_server.sh   #执行安装服务脚本
    Welcome to the redis service installer
    This script will help you easily set up a running redis server
    
    Please select the redis port for this instance: [6379]    #确定端口号
    Selecting default: 6379 
    Please select the redis config file name [/etc/redis/6379.conf]     #redis配置文件
    Selected default - /etc/redis/6379.conf
    Please select the redis log file name [/var/log/redis_6379.log]    #日志文件 
    Selected default - /var/log/redis_6379.log
    Please select the data directory for this instance [/var/lib/redis/6379]     #数据存放目录
    Selected default - /var/lib/redis/6379
    Please select the redis executable path [/usr/local/bin/redis-server] 
    Selected config:
    Port           : 6379
    Config file    : /etc/redis/6379.conf
    Log file       : /var/log/redis_6379.log
    Data dir       : /var/lib/redis/6379
    Executable     : /usr/local/bin/redis-server
    Cli Executable : /usr/local/bin/redis-cli
    Is this ok? Then press ENTER to go on or Ctrl-C to abort.
    Copied /tmp/6379.conf => /etc/init.d/redis_6379
    Installing service...
    Successfully added to chkconfig!
    Successfully added to runlevels 345!
    Starting Redis server...
    Installation successful!
    [root@server3 utils]#
    

    暗装完成之后登录

    [root@server3 utils]# redis-cli -h 127.0.0.1 -p 6379
    127.0.0.1:6379> 
    
    
    (4)redis进程控制
    [root@server3 utils]# /etc/init.d/redis_6379 status
    Redis is running (68589)
    [root@server3 utils]# /etc/init.d/redis_6379 stop
    Stopping ...
    Waiting for Redis to shutdown ...
    Redis stopped
    [root@server3 utils]# /etc/init.d/redis_6379 start
    Starting Redis server...
    [root@server3 utils]# /etc/init.d/redis_6379 restart
    Stopping ...
    Waiting for Redis to shutdown ...
    Redis stopped
    Starting Redis server...
    [root@server3 utils]# 
    
    

    3,Redis配置文件

    配置参数(/etc/redis/6379.conf)
    • bind:监听的主机地址
    • port:端口
    • daemonize yes:启用守护进程
    • pidfile:指定PID文件(建议使用kill -9,不要使用pkill -9 )
    • loglevel notice:日志级别
    • logfile:指定日志文件

    三,redis数据库常用命令

    1,Redis-cli命令行工具

    • 将本机地址添加到监听端口,重启服务后测试能否成功登录
    [root@server3 utils]# vi /etc/redis/6379.conf
    
    bind 127.0.0.1  20.0.0.30 ###把允许登录的地址加到配置文件的bind后面
    [root@server3 utils]# /etc/init.d/redis_6379 restart
    Stopping ...
    Waiting for Redis to shutdown ...
    Redis stopped
    Starting Redis server...
    [root@server3 utils]# netstat -lutap | grep 6379
    tcp        0      0 localhost:6379          0.0.0.0:*               LISTEN      29276/redis-server  
    tcp        0      0 localhost:6379          localhost:52850         TIME_WAIT   -                   
    [root@server3 utils]# 
    
    
    • 连接本地数据库
    [root@server3 utils]# redis-cli
    127.0.0.1:6379> 
    
    
    • 连接远程
    [root@server3 utils]# redis-cli -h 20.0.0.30 -p 6379
    
    
    • help @:获取中的命令列表
    • help :获取某个命令的帮助
    • help :获取可能帮助的主题列表
    [root@server3 utils]# redis-cli 
    127.0.0.1:6379> help @list
    
      BLPOP key [key ...] timeout
      summary: Remove and get the first element in a list, or block until one is available
      since: 2.0.0
    
    
    • Redis-cli命令行工具
      • set:存放数据
      • get:获取数据
    127.0.0.1:6379> help  set
    
      SET key value [expiration EX seconds|PX milliseconds] [NX|XX]
      summary: Set the string value of a key
      since: 1.0.0
      group: string
    
    127.0.0.1:6379>
    
    
    127.0.0.1:6379> set teacher zhangli
    OK
    127.0.0.1:6379> get teacher
    "zhangli"
    127.0.0.1:6379> 
    
    

    2,key相关命令

    • keys: 获取符合罪责的键值列表
    
    #keys *:查看所有键;通配符*和?都支持 
    20.0.0.30:6379> keys nam? 
    1) "name" 
    20.0.0.30:6379> keys na?? 
    1) "name"
    
    • exists:判断当前键值是否存在
    
    20.0.0.30:6379> exists name (integer) 1 #执行失败返回0,执行成功返回非0值
    
    • del:删除当前数据库的指定key
    
    20.0.0.30:6379> keys * 
    1) "name" 
    2) "age" 
    20.0.0.30:6379> del age
    (integer) 1
    20.0.0.30:6379> keys * 
    1) "name"
    
    • type:获取key对应的value值类型
    
    20.0.0.30:6379> type name string
    
    • rename(覆盖)/renamenx(不覆盖):对已有的key进行重命名
    
    20.0.0.30:6379> rename name zhangsan
    OK
    
    • dbsize:查看当前数据库中Key的数目
    
    20.0.0.30:6379> dbsize (integer) 
    1
    

    3,Redis-benchmark测试工具

    参数 功能
    -h 指定服务器主机名
    -p 指定服务器端口
    -c 指定并发连接数
    -n 指定请求数
    -d 以字节的形式指定set/get值的数据大小
    -q 强制退出Redis,仅显示query/sec值

    Redis数据库采用key-value(键值对)的数据存储形式,所使用的命令

    ●向IP地址为20.0.0.30、端口为6379的Redis服务器发送100个并发连接与10000个请求测试性能

    
    redis-benchmark -h 20.0.0.30 -p 6379 -c 100 -n 10000
    

    ●测试存取大小为100字节的数据包的性能

    
     redis-benchmark -h 20.0.0.30 -p 6379 -q -d 100
    

    四,Redis持久化

    1 RDB持久化

    • Redis的默认持久化方式
    • 默认文件名dump.rdb
    • 触发条件
    • 优缺点
      • 适合大规模的数据恢复
      • 如果业务对数据完整性和一致性要求不高,RDB是很好的选择
      • 数据的完整性和一致性不高
      • 备份时占用内存

    3, 通过RDB文件恢复数据

    • 将dump.rbd文件拷贝到Redis的安装目录的bin目录下,重启Redis服务即可
    • 配置文件选项

    五 ,Redis性能管理

    1,内存碎片率

    2,内存使用率

    • Redis实例的内存使用率超过可用最大内存,操作系统将开始进行内存与swap空间交换
    • 避免内存交换
      • 针对缓存数据大小选择
      • 尽可能的使用hash数据结构
      • 设置key的过期时间

    3,回收Key

    • 保证合理分配Redis优先的内存资源
    • 当达到设置的最大阀值时,需选择一种key的回收策略
      • 默认情况下回收策略是禁止删除
      • Redis.conf配置文件中修改maxmemory-policy属性值
        • volatile-lru:使用lru算法从已设置过期时间的数据集合中淘汰数据
        • volatile-ttl:从已设置过期时间的数据集合中淘汰数据
        • volatile-random:从已设置过期时间的数据集合中随机挑选数据淘汰
        • allkeys-lru:使用lru算法从所有数据集合中淘汰数据
        • allkeys-random:从数据集合中任意选择数据淘汰
        • no-enviction:禁止淘汰数据
    展开全文
  • 1 常见问题阻塞慢查询2 可以从以下几个方面入手服务器硬件优化系统配置优化数据库结构优化SQL和索引通常优化3和43 慢查询日志show variables like 'slow_query_log';# 开启慢查询日志set global slow_query_log=on;#...

    1 常见问题

    阻塞

    慢查询

    2 可以从以下几个方面入手

    服务器硬件优化

    系统配置优化

    数据库结构优化

    SQL和索引

    通常优化3和4

    3 慢查询日志

    show variables like 'slow_query_log';

    # 开启慢查询日志

    set global slow_query_log=on;

    # 查询慢查询日志文件存放的位置

    show variables like 'slow_query_log_file';

    # 设置慢查询日志文件存放的位置

    set global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log'

    # 设置是否将没有使用索引的SQL记录到慢查询日志中

    set global log_queries_not_using_indexes=on;

    # 设置是否将查询事件超过0秒的SQL记录到慢查询日志中,通产设置0.01秒

    set global long_query_time=0;

    4 实操

    4.1 默认不开启慢查询日志

    4.2 慢查询日志存储的位置

    查询日志,这里我是Docker容器部署MySQL,映射在服务器上/data/mysql目录下

    4.3 日志格式

    第一行:执行SQL的主机信息

    第二行:SQL的执行信息

    第三行:SQL的执行事件

    第四行:SQL的内容

    5 慢查询日志分析工具

    5.1 mysqldumpslow(官方工具)

    5.1.1 参数解释

    -s 是order的顺序

    al 平均锁定时间

    ar 平均返回记录时间

    at 平均查询时间(默认)

    c 计数

    l 锁定时间

    r 返回记录

    t 查询时间

    -t 是top n的意思,即为返回前面多少条的数据

    -g 后边可以写一个正则匹配模式,大小写不敏感的

    5.1.2 基本使用

    # 得到返回记录集最多的10个SQL。

    mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

    # 得到访问次数最多的10个SQL

    mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

    # 得到按照时间排序的前10条里面含有左连接的查询语句。

    mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log

    # 另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现刷屏的情况。

    mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

    5.2 pt-query-digest

    5.2.1 快速安装

    wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y percona-toolkit-2.2.16-1.noarch.rpm

    5.2.2 参数解释

    pt-query-digest [OPTIONS] [FILES] [DSN]

    --create-review-table 当使用--review参数把分析结果输出到表中时,如果没有表就自动创建。

    --create-history-table 当使用--history参数把分析结果输出到表中时,如果没有表就自动创建。

    --filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析

    --limit 限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是50%则按总响应时间占比从大到小排序,输出到总和达到50%位置截止。

    --host mysql服务器地址

    --user mysql用户名

    --password mysql用户密码

    --history 将分析结果保存到表中,分析结果比较详细,下次再使用--history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化。

    --review 将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用--review时,如果存在相同的语句分析,就不会记录到数据表中。

    --output 分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于阅读。

    --since 从什么时间开始分析,值为字符串,可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点,也可以是简单的一个时间值:s(秒)、h(小时)、m(分钟)、d(天),如12h就表示从12小时前开始统计。

    --until 截止时间,配合—since可以分析一段时间内的慢查询。

    5.2.3 基本使用

    # 输出到文件

    pt-query-digest slow-log > slow_log.report

    # 输出到数据库 --create-reviewtable 意思是慢查询日志输出到某一张表中

    pt-query-digest slow.log -review h=127.0.0.1,D=test,p=root,P=3306,u=root.t=query_review --create-reviewtable --review-history t= hostname_slow

    6 SQL优化

    6.1 需要优化的SQL特征

    查询次数多且每次查询占用时间长的SQL

    IO大的SQL(SQL中扫描行数越多,IO越大)

    未命中索引的SQL

    6.2 使用explain查询SQL的执行计划

    6.2.1 解释说明

    table 显示这一行数据时关于哪张表的

    type 这是重要的列,显示连接使用了那种类型。从最好到最差的连接类型为const,eq_reg,ref,range,index,ALL。const常见于主键/唯一索引查找,eq_reg常见于主键的范围查找,ref常见于连接查询,range常见于索引的范围查找,index常见于索引的扫描,ALL常见于表扫描

    possible_keys 显示可能应用在这张表中的索引。如果为空,没有可能的索引。

    key 实际使用的索引。如果为NULL,则没有使用索引。

    key_len 使用的索引长度。在不损失精确性的情况下,长度越短越好

    ref 显示索引的那一列被使用了,如果可的话,是一个常数

    rows MySQL认为必须检查的用来返回请求数据的行数

    6.3 SQL优化

    在经常查询的字段上适当加索引

    避免子查询,可优化为连接查询,注意是否存在一对多关系,可能会出现数据重复

    6.4 如何选择合理的列建立索引

    在where从句,group by 从句,order by 从句,on 从句中出现的列

    索引字段越小越好

    离散度大的列放在联合索引的前面

    例如:

    select * from payment where staff_id = 2 and customer_id = 584;

    由于customer_id的离散度更大,所以应该使用index(customer_id,staff_id)

    6.4.1 如何判断列的离散程度

    select count(distinct customer_id),count(distinct staff_id) from payment

    列的唯一值越高,离散程度越大,可选择性越高。

    6.4.2 索引的维护和优化

    重复及冗余索引,重复索引是指相同的列以相同的顺序建立的同类型的索引,如下面parmary key 和ID列上的索引就是重复索引

    create table test(id int not null primart key),name vachar(10) not null,title varchar(50) not null,unique(id) ) engine=innodb

    使用pt-duplicate-key-checker工具检查重复及冗余索引

    pt-duplicate-key-checker -uroot -p'' -h 127.0.0.1

    7 数据库及表结构优化

    7.1 选择合适的数据类型

    使用可以存下你的数据的最小的数据类型

    使用简单数据类型,int要比varchar类型在MySQL处理上简单

    尽可的使用not null 定义字段

    尽量少用text类型,非用不可时最好考虑分表

    例如

    使用bigint来存储IP地址,利用INET_ATON(),INET__NTOA()两个函数进行转换

    insert into sessions(ipaddress) values (INET_ATON('127.0.0.1'));

    select INET__NTOA('127.0.0.1') from sessions;

    7.2 范式化和反范式化

    7.2.1 范式化

    第一范式,强调原子性,要求属性具有原子性,不可再分解

    第二范式,强调主键,要求记录有唯一标识,即实体的唯一性,级不存在部分依赖

    第三范式,强调外键,要求任何字段不能由其他字段派生出来,要求字段没有冗余,即不存在依赖传递

    7.2.2 反范式化

    为了查询效率的考虑,把原本符合第三范式的表适当的增加冗余,以达到优化查询效率的目的,反范式化是一种以控件来换时间的操作。

    7.3 数据库结构的优化

    7.3.1 表的垂直拆分

    把原来一个有很多列的表拆分成多个表,这解决了表的宽度问题,通常垂直拆分可以按以下原则进行:

    把不常用的字段单独存放在一个表中

    把大字段独立存在在一个表中

    把经常一起使用的字段放在一起

    7.3.2 表的水平拆分

    为了解决单表的数据量大的问题,水平拆分的表每一个表的结构都是完全一致的

    8 系统配置优化

    数据库是基于操作系统的,目前大多数MySQL都是安装在Linux上,所以对于操作系统的一些参数配置也会影响MySQL的性能,下面列出常用的系统配置

    8.1 网络配置方面

    修改/etc/sysctl.coonf文件

    # 增加tcp支持的队列数

    net.ip4.tcp_max_syn_backlog=65535

    # 减少断开连接时,资源回收

    net.ipv4.tcp_max_tw_buckets=8000

    net.ipv4.tcp_tw_reuse=1

    net.ipv4.tcp_tw_recycle=1

    net.ipv4.tcp_fin_timeout=10

    打开文件数的限制,可以使用ulimit -a查看目录的各位限制,可以修改/etc/security/limits.conf文件,增加以下内容以修改打开文件数量的限制

    soft nofile 65335

    hard nofile 65535

    除此之外最好在MySQL服务器上关闭iptables,selinux等防火墙软件

    8.2 MySQL配置文件

    8.2.1 配置文件路径

    Linux:/etc/my.cnf

    8.2.2 参数

    innodb_buffer_pool_size:非常重要的参数,用于配置innodb的缓冲池如果数据库中只有innodb表,则推荐配置量为总内存的75%

    innodb_buffer_pool_instances:MySQL5.5中新增的参数,可以控制缓存池的个数,默认情况下只有一个缓存池

    innodb_log_buffer_size:innodb log缓存的大小,由于日志最长每秒钟就会刷新所以一般不用太大

    innodb_flush_log_at_trx_commit:关键参数,对innodb的IO效率影响最大,默认值为1,可以取0,1,2三个值,一般建议设置为2,但如果数据安全性要求比较高则使用默认值1

    innodb_read_io_threads/innodb_write_io_threads:决定innodb读写IO的进程数,默认为4

    innodb_file_per_table:关键参数,控制innodb没一个表使用独立的表空间,默认为OFF,也就是所有表都会建立在共享表空间中

    innodb_stats_on_metadata:决定MySQL在什么情况下会刷新innodb表的统计信息

    8.2.3 第三方配置工具

    Percon Configuration Wizard

    9 服务器硬件的优化

    9.1 CPU

    选择合适的CPU,单个频率更快的CPU

    MySQL一些工作只能使用到单核

    MySQL对CPU的核数支持并不是越快越好,MySQL5.5使用的服务器不要超过32核

    9.2 disk IO优化

    常用RAID级别简介

    RAID0:也成为条带,就是把多个磁盘连接成一个硬盘使用,这个级别IO最好

    RAID1:也称为镜像,要求至少有两个磁盘,每组磁盘存储的数据相同

    RAID5:也是把多个(最少3个)硬盘合并成一个逻辑盘使用,数据读写时会建立奇偶校验信息,并且奇偶校验信息和相对应的数据分别存储于不同的硬盘上。当RAID5的一个磁盘数据发生损坏后,就剩下的数据和相应的奇偶校验信息去恢复被损坏的数据。

    RAID1+0:就是RAID1和RAID0的结合,同时具备两个级别的优缺点。一般建议数据库使用这个级别。

    SNA和NAT是否适合数据库

    展开全文
  • 数据库优化

    2021-02-27 16:34:46
    MySQL数据库优化的八种方式(经典必看) 超详细:数据库性能优化(一) 有哪些常见的数据库优化方法?
    展开全文
  • MYSQL数据库优化方向sql及索引优化,存储优化(程序)数据库表结构优化系统配置硬件一、SQL语句优化1、Mysql慢查日志的开启和日志存储格式查看慢查询日志是否开启:show variables like 'slow_query_log'查看没索引...

    MYSQL数据库优化方向

    sql及索引优化,存储优化(程序)

    数据库表结构优化

    系统配置

    硬件

    一、SQL语句优化

    1、Mysql慢查日志的开启和日志存储格式

    查看慢查询日志是否开启:show variables like 'slow_query_log'

    查看没索引日志是否记录:show variables like '%log%'

    列表项‘log_queries_not_using_indexes’,如果是OFF

    开启非索引日志记录:set global log_queries_not_using_indexes=on

    查询时间设置(超过多长时间的sql记录慢查询日志):show variables like 'long_query_time';

    这里测试设置为空0:set global long_query_time=0.0  (需要重新链接下mysql才回看到设置的值)

    测试:随便查询几个表

    找到慢查询日志存放目录:show varibels like '%log%';

    | slow_query_log_file                     | /var/lib/mysql/ubuntu-slow.log

    退出mysql,

    sudo cat /var/lib/mysql/ubuntu-slow.log ,查看慢查日记记录内容

    519ee2848d02245ea620b257a118f494.png

    2、慢查询日志管理工具

    mysqldumpslow

    退出mysql,系统中:mysqldumpslow -h查看工具使用方法,比如:

    sudo mysqldumpslow -t 5 /var/lib/mysql/ubuntu-slow.log

    pt-query-digest功能比mysqldumpslow强的多

    查看帮助命令:pt-query-digest --help

    使用实例:sudo pt-query-digest /var/lib/mysql/ubuntu-slow.log

    3、慢查询日志如何发现有问题的sql

    744a55437e9f6e469e8f594c7490aa2e.png

    rows examine扫描行数。

    4、explain查询和分析sql执行计划,先计划在执行

    5b5e62aeababd65cde334d936fa0ad17.png

    a0127225eb8c850df2c153796fe42442.png

    using filesort 和using temporary一般是order by 和group by导致的。

    5、max()优化

    max()直接使用会扫描所有行,创建索引后不需要扫描:

    stelin-2144804

    创建索引:explain select max(payment_date) from payment \G;

    命::explain select max(payment_date) from payment \G;

    6、子查询优化

    优化成 join..on(链接方式,若果一对多,注意重复数据处理ditinct)

    7、group by优化

    sql:EXPLAIN SELECT actor.first_name,actor.last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

    操作了大量的io(filesort)

    优化成子查询:

    EXPLAIN SELECT actor.first_name,actor.last_name,c.cnt FROM actor INNER JOIN (SELECT film_actor.actor_id,COUNT(*) AS cnt FROM film_actor GROUP BY actor_id) AS c USING(actor_id);

    group 多表链接尽量使用子查询。

    8、limit优化

    普通sql:EXPLAIN SELECT f.film_id,f.description,f.title FROM film f LIMIT 0,5;扫描全表,type是all

    第一步优化:EXPLAIN SELECT f.film_id,f.description,f.title FROM film f ORDER BY f.film_id LIMIT 0,5;

    通过一个ID排序,也是扫描全表但是,type是index(索引类型)

    第二步优化:EXPLAIN SELECT f.film_id,f.description,f.title FROM film f WHERE film_id<=56 AND       film_id>50 ORDER BY f.film_id LIMIT 0,5;

    通过记录上次id位置,避免扫描全表,注意适用于ID递增,没有空缺的表。

    二、索引优化

    1、选择合适的列建立索引

    e28be9d0673c60e994694c966dd4c9b4.png

    列的离散度,是指列的唯一性。离散度越大,唯一性越大。比如:SELECT COUNT(DISTINCT p.customer_id), COUNT(DISTINCT p.staff_id) FROM payment AS p,统计的customer_id,比staff_id大,说明,离散密度就大。

    2、SQL优化索引

    32733f3292c8d6a90762022c80dc61dc.png

    e65c50451aa7aeb05728adb2478df42b.png

    78b49521ac1f8ec7a250e38bb0853b84.png

    优化重复和冗余的索引。此工具可以显示重复和冗余的索引,以及解决办法。

    3、索引维护(删除不用的索引)

    3e4f8d8e74f73381fc63612d345feaa3.png

    三、数据化结构优化

    1、选择合适的数据类型

    08c97d7acd07c2f476c680aece1c0679.png

    39df9b81cc867338e3739a6a209d22c2.png

    f454a00725865847e65cb619a78f6e2d.png

    2、范式化优化

    4ba764dfe0de9936870a2263337a188e.png

    f6fb3b1f1d5221338eb81031e902d477.png

    比如删除表里面所有饮料的商品,结果饮料分类和描述也删除了。

    3、反范式化优化

    4891d0fa25ff83be4b0737b03dc6c454.png

    d6aa35fa2f80963c9a763026641f18e8.png

    7e8582503e2e879f2493d6966873b511.png

    a0b26d7d80a9db48ae5b2cb6cb34d400.png

    b8c43ea85082878148f574e05a7542ab.png

    4、表垂直拆分

    cd632f5f293eb369aefe4b33bface6b8.png

    062fe549487fb8fb975428b774acd5be.png

    45ab9dca99eda32dcd078b1a57c77125.png

    b66fa91d98b9e25067b1fb9fce5e8c5a.png

    5、表的水平拆分

    3948d13adc7e593f2bed7d6d8712eee9.png

    d8b067b1db8985ce42accf6d1fae444e.png

    四、系统配置优化

    1、数据库操作系统配置的优化

    45031b8384d36c5b0d3597ee93bc01d1.png

    562edc812629772aa9ba657c1dc0a41c.png

    2、mysql常用配置参数

    292285198888883855fbbbaa178fd564.png

    0c001fb38d336bd920fe3d6fb8f1aa15.png

    93f112c49a6f21cc2498cae158884f76.png

    dbe7497f6745437a2284d3a19dfa23d1.png

    b41892767cf54675e1a4d283e7ae057c.png

    efbdb38adffbd63c5ce2534826f7f5fc.png

    4d996c3c2570227f4793a64e3fc58d82.png

    五、服务器硬件优化

    1、CPU的选择

    161911a23b85633d3b081a431ef6dc97.png

    通常选择单核更快的CPU

    2、磁盘的选择

    49db7f87e27f49a05af0ad523c5ab94e.png

    RAID0,读写最好,RAIA1,数据不会丢失,安全性高。通常选择RAID1+0

    84420885c8951eb62f85dd2fc5ac927e.png

    68e1534462e736eff8c9cab8679bc378.png

    大小: 104.2 KB

    85624252ddc8f90aba0bec93e8a0b421.png

    大小: 102.3 KB

    145bdf77ed1a3f4aa205e02dd6ec4c86.png

    大小: 146.8 KB

    90d81708fdce7ab4e1f4a53fe428538b.png

    大小: 104.1 KB

    98c61be06d033e12ccb565bccdb87380.png

    大小: 132.1 KB

    d9761901aff7513c03cda5880eaba319.png

    大小: 85.5 KB

    784e5510bf640d2901aef4976e3651e8.png

    大小: 90.2 KB

    42c9c8fc03d29b78635175f1e8dbf9a6.png

    大小: 57 KB

    26c65563d2f7ccea0c6680d64db0aa35.png

    大小: 94.3 KB

    ca15526519efe95e731dd0c4664c4582.png

    大小: 90.9 KB

    443c14e239bdbbd5ccff06886d25c261.png

    大小: 97.3 KB

    73a9352a388a37e339c8796e7810799c.png

    大小: 99 KB

    ea1784d54fe401c7cd8904907602a5a4.png

    大小: 174.2 KB

    d07e490801427a4e90b8182ee5141014.png

    大小: 60.8 KB

    a697a1e097338580600844a36b7c02c6.png

    大小: 134.5 KB

    d9d707f887c441ee76ff80f78a61b160.png

    大小: 124.9 KB

    e12c7ccc309cfb0d276a95ff37fd9eed.png

    大小: 90.6 KB

    0deaa1c03d39e40e5b9f4980828433d5.png

    大小: 146.7 KB

    929ca7486a28e67aa84f9b2cd7609d39.png

    大小: 49.1 KB

    52fb33a6a7b42c61adb1cedfe2ad267f.png

    大小: 146.7 KB

    cf2bdf07993d532643bd308b0f7226f2.png

    大小: 49.1 KB

    aee94630a468fb46e107860ee4705e86.png

    大小: 139 KB

    e520ae2719dc6ad5e2a23f57181621f7.png

    大小: 132.2 KB

    472308c6663f19225855286cb8d21cf3.png

    大小: 45.1 KB

    4f5c6bd8408e47df17c1872fd48e688d.png

    大小: 75.3 KB

    25b18fb5663984f1f4d823281223889b.png

    大小: 117.1 KB

    e415b02679f7aadd7fec20c4856abb70.png

    大小: 67.2 KB

    217d10bc45d554e6a144f94d77ccffe8.png

    大小: 122.7 KB

    e5923a631117d1a0a9ab0881a0776acd.png

    大小: 139.2 KB

    4e01c1c835e8244fbae2c465de4a8c2c.png

    大小: 130.6 KB

    5b600fba3cf6737d66f8174c8050fe25.png

    大小: 31.7 KB

    bdff7d2a77bee11d4b5c1e28c187b439.png

    大小: 27.7 KB

    ac9488a7685411efaf8496cad47d9931.png

    大小: 46.6 KB

    dfa47021ca2c572b5c1e2d4e39a1b5c1.png

    大小: 29.4 KB

    8c5ef9dcbc3e21e622df9e14bfdd5d97.png

    大小: 39.3 KB

    30b56d0723f5f8c6ca1fa1d8c9a7b85d.png

    大小: 24.3 KB

    bcd99c403057ff376c3707104bc4001e.png

    大小: 80.3 KB

    b61e9d0916b6e168fe101b3608737da4.png

    大小: 44.9 KB

    bce2eafff3e9f1ab98e3b4295fc7b87e.png

    大小: 166.3 KB

    分享到:

    18e900b8666ce6f233d25ec02f95ee59.png

    72dd548719f0ace4d5f9bca64e1d7715.png

    2014-10-17 17:47

    浏览 545

    分类:数据库

    评论

    展开全文
  • 下面让我们以服务器的系统变量和状态变量为根据,优化我们的MySql数据库服务。在这之前,我们需要掌握以下方法:查看MySql状态及变量的方法:Mysql> show status ——显示状态信息(扩展show status like 'XXX') ...
  • 数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷. 1. 优化一览图 2. ...
  • 前言数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.1. 优化一览图2. 优化...
  • MySQL数据库服务器优化详细
  • MYSQL数据库优化方向  sql及索引优化,存储优化(程序) 数据库表结构优化 系统配置 硬件 一、SQL语句优化  1、Mysql慢查日志的开启和日志存储格式  查看慢查询日志是否开启:show variables like '...
  • 前言数据库优化一方面是找出系统的瓶颈,提高MySQL数据库的整体性能,而另一方面需要合理的结构设计和参数调整,以提高用户的相应速度,同时还要尽可能的节约系统资源,以便让系统提供更大的负荷.1. 优化一览图2. 优化...
  • 数据库优化+统一平台详细设计.rar
  • 1 常见问题阻塞慢查询2 可以从以下几个方面入手服务器硬件优化系统配置优化数据库结构优化SQL和索引通常优化3和43 慢查询日志show variables like 'slow_query_log';# 开启慢查询日志set global slow_query_log=on;#...
  • 提示:本次数据库优化等 目录Mysql讲解目录一.数据库事务的四大特征二.Mysql的优化三.Mysql的索引1.什么是索引2.Mysql有哪些索引3.索引失效的条件4.索引的优缺点5.一张表最多可以创建多少个索引6.MySql中的聚合函数...
  • 本文为性能优化的第一篇——数据库性能优化,原理适用于大部分数据库包括Sqlite、Mysql、Oracle、Sql server,详细介绍了索引(优缺点、分类、场景、规则)和事务,最后介绍了部分单独针对Sqlite的优化。 目前性能优化...
  • 全面深入Mysql数据库优化

    千人学习 2019-09-26 11:44:58
    本课程作为MySQL高级课程, 主要讲解了MySQL中的视图/存储过程/触发器/索引等对象的使用、常见的SQL语句优化的技巧 、应用优化、数据库优化、数据库日志等方面的知识,并通过综合案例,对课程中的知识进行一个整合...
  • 1 常见问题阻塞慢查询2 可以从以下几个方面入手服务器硬件优化系统配置优化数据库结构优化SQL和索引通常优化3和43 慢查询日志show variables like 'slow_query_log';# 开启慢查询日志set global slow_query_log=on;#...
  •  参考这哥们—愤怒的小鸟(写的很详细可以参考,MySQL性能优化之参数配置) (2)、从系统层面增强mysql的性能:优化数据表结构、字段类型、字段索引、分表,分库、读写分离等等。  表结构:数据类型选择;字符...
  • 下面让我们以服务器的系统变量和状态变量为根据,优化我们的MySql数据库服务。在这之前,我们需要掌握以下方法:查看MySql状态及变量的方法:Mysql> show status ——显示状态信息(扩展show status like 'XXX')...
  • 1 常见问题阻塞慢查询2 可以从以下几个方面入手服务器硬件优化系统配置优化数据库结构优化SQL和索引通常优化3和43 慢查询日志show variables like 'slow_query_log';# 开启慢查询日志set global slow_query_log=on;#...
  • 1 常见问题阻塞慢查询2 可以从以下几个方面入手服务器硬件优化系统配置优化数据库结构优化SQL和索引通常优化3和43 慢查询日志show variables like 'slow_query_log';# 开启慢查询日志set global slow_query_log=on;#...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,466
精华内容 1,386
关键字:

数据库优化详细