mysql 开荒进级篇种类 5 SQL 优化(表优化卡塔尔

图片 32

一.mysql 连接工具  

  在mysq提供的工具中,DBA使用最频繁的莫过于mysql。这里的mysql是指连接数据库的客户端工具。

  1.1 连接选项

-u, — user=name 指定用户名
-p ,–password[=name] 指定密码
-h ,–host=name 指定服务器IP或者域名
-p, –port=# 指定连接端口

在实际连接中命令如下:mysql -u 用户名 -p 密码
[root@hsr ~]#  mysql -u root -p

-- 完整的连接命令如下
[root@hsr ~]#  mysql -u root -p -h 172.168.18.201 -P 3306
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.

  1.2 执行选项
    通过 “-e,–execute=name 执行sql语句并退出”
此选项不需要连接到mysql数据库后再执行,对于一些批处理脚本很方便,下面是从客户端直接查询mysql数据库中user表的User和Host字段:

[root@hsr ~]#  mysql -u root -p  mysql -e "select User,Host from user"
Enter password: 

    图片 1
  连接多个sql 语句用英文分号隔开如   select User,Host from user;
select xxx。

一.概述

        
mysqlcheck客户端工具可以检查和修复MyISAM表,还可以优化和分析表。实际上,它集成了mysql工具中check,repair,analyze,optimize功能,对于check
则不支持MEMORY表, repair 则不支持
InnoDB表。mysqlcheck只有在数据库运行的状态下才可运行,意味着不用停止服务操作。
  下面是三种调用mysqlcheck方式:
    图片 2

  option中常用以下选项:

-c, –check

检查表

-r, –repair

修复表

-a, –analyze

分析表

-o, –optimize

优化表

  1.1 检查表(check)

[root@hsr data]# pwd
/usr/local/mysql/data
[root@hsr data]# /usr/local/mysql/bin/mysqlcheck -uroot -p -c test

    图片 3

    上面检查到了test库中a表是OK的

  1.2 修复表(repair)

[root@hsr data]# /usr/local/mysql/bin/mysqlcheck -uroot -p -r test

    图片 4
    在test库新建了一个innodb类型的b表,上图显示b表不支持修复。
  1.3 分析表(analyze)

[root@hsr data]# /usr/local/mysql/bin/mysqlcheck -uroot -p -a test

    图片 5
  1.4 优化表(optimize)

[root@hsr data]# /usr/local/mysql/bin/mysqlcheck -uroot -p -o test

    图片 6

一.概述

    mysqladmin是一个执行管理操作的客户端程序。用来检要服务的配置和当前的状态,创建并删除数据库等。功能与mysql客户端类似,主要区别在于它更侧重于一些管理方面的功能。

  1. 查找mysqladmin工具

    –同样还是找到安装基地址bin目录下:

     [root@hsr bin]# pwd
     /usr/local/mysql/bin
    

    下面是可以执行的命令行以及描述

     [root@hsr bin]# ./mysqladmin
     语法如下:mysqladmin [option] command [command option] command ......
    

    图片 7

2 查看mysql 活动线程列表

[root@hsr bin]# ./mysqladmin -uroot -p processlist    
    or 
 [root@hsr bin]# ./mysqladmin -uroot -p  proc

  图片 8

  1. 查看mysql 活动线程列表 每秒一次。 ctrl+c 停止

    [root@hsr bin]# ./mysqladmin -uroot -p -i 1 processlist

    图片 9

  1. 查看服务器的状态, 每两秒查看一次状态,总共重复5次。

         Uptime                    MySQL服务器已经运行的秒数
         Threads                 
 活跃线程(客户)的数量 包括sleep线程
         Questions                从mysqld启动起来自客户问题的数量 
         Slow queries            
已经超过long_query_time秒的查询数量 
         Opens                      mysqld已经打开了多少表 
         Flush tables             flush …, refresh和reload命令数量 
         Open tables              现在被打开的表数量

[root@hsr bin]# ./mysqladmin -uroot -p -i 2 -c 5 status

    图片 10

  1. 修改root密码

    [root@hsr bin]# ./mysqladmin -uroot -p password 1234567

    图片 11
6.检查mysql服务是否可用

[root@hsr bin]# ./mysqladmin -uroot -p ping

    图片 12
7.检查当前服务版本

[root@hsr bin]# ./mysqladmin -uroot -p version

    图片 13

8.检查当前服务状态值

[root@hsr bin]# ./mysqladmin -uroot -p extended-status

    图片 14

  1. kill掉mysql线程ID

  图片 15

  1. 删除数据库 daba-test

         mysqladmin -uroot -p drop daba-test

  1. 重载权限信息

         mysqladmin -uroot -p reload

更多使用方法参看:mysqladmin文档    

一.概述

    mysqldump客户端工具是用来备份数据库或在不同数据库之间进行数据迁移。备份内容包含创建表或装载表的sql语句。mysqldump目前是mysql中最常用的备份工具。
  三种方式来调用mysqldump,命令如下:
图片 16
  上图第一种是备份单个数据库或者库中部分数据表(从备份方式上,比sqlserver要灵活一些,虽然sql
server有文件组备份)。第二种是备份指定的一个或者多个数据库。第三种是备份所有数据库。
  1.连接导出,下面将test数据库导出为test.txt文件,导出位置在data目录下

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p test > test.txt

图片 17图片 18

  上图显示: 导出到test.txt文件里,
数据有几部份sql语句,包括:(1)有判断表存在删除,(2)导出表结构和表数据,(3)导前加table
write锁,导完释放。通过下面帮助命令可以看到默认设置。

 [root@hsr data]# /usr/local/mysql/bin/mysqldump --help

图片 19  2.
输出内容选项

-n, –no-create-db

不包含数据库的创建语句

-t, –no-create-info

不包含数据表的创建语句

-d,–no-data

不包含数据

    下面演示导出test库的a表,不包含数据:

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p -d  test a > a.txt

图片 20

    上图显示,使用more 查看a.txt,内容只有表结构。
  3. 使用
–compact选项使得结果简洁,不包括默认选项中的各种注释,下面还是演示a表:

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p --compact  test a > a.txt

图片 21

  4. 使用-c –complete-insert 选项,使insert语句包括字段名称

[root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p -c --complete-insert  test b > b.txt

图片 22

  5. 使用-T选项将指定数据表中的数据备份为单纯的数据文本和建表sql,
两个文件。

     [root@hsr data]# midir bak
        [root@hsr data]# /usr/local/mysql/bin/mysqldump -uroot -p   test b -T ./bak
        Enter password: 
        mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute 
                      this statement when executing 'SELECT INTO OUTFILE'

 --上面的语句报错,查找错误信息中的字段设置
SHOW VARIABLES LIKE '%secure%';

图片 23

    secure-file-priv参数是用来限制LOAD DATA, SELECT … OUTFILE, and
LOAD_FILE()传到哪个指定目录的。

                   (1) 当secure_file_priv的值为null ,表示限制mysqld
不允许导入|导出。

                   (2) 当secure_file_priv的值为/tmp/ ,表示限制mysqld
的导入|导出只能发生在/tmp/目录下。

                   (3
)当secure_file_priv的值没有具体值时,表示不对mysqld
的导入|导出做限制。

    下面来设置my.cnf文件,加上导入位置,位置在/tmp 目录下,如下图:
图片 24图片 25

-- 再次导出,导出路径在/tmp下
[root@hsr data]#  /usr/local/mysql/bin/mysqldump -uroot -p   test b -T /tmp

图片 26

    使用more 查看文件,b.sql中包含了表架构, b.txt包含数据。
图片 27

图片 28

   6.  字符集选项

    –default-character-set=name
选项可以设置导出的客户端字符集。这个选项很重要,如果客户端字符集和数据库字符集不一致,有可能成为乱码,使得备份文件无法恢复。

[root@hsr data]#  /usr/local/mysql/bin/mysqldump -uroot -p  --compact --default-character-set=utf8 test >test.txt

    图片 29

  7. 其他常用选项

    (1) -F –flush-logs(备份前刷新日志) 
备份前将关闭旧日志,生成新日志。恢复的时候直接从新日志开始进行重做,方便恢复过程。

    (2) -l –lock-tables(给所有表加读锁)
使得数据无法被更新,从而使备份的数据保持一致性(可以导致大量长时间阻塞)。

一. 使用sql提示

         sql 提示(sql hint)是优化数据库的一个重要手段,
是在sql语句中加入一些人为的提示来达到优化操作的目的。

  1.1 use index
    在查询语句中表名的后面,添加use index
强制mysql使用该索引,不考虑其它索引。

EXPLAIN SELECT * FROM city USE INDEX(ix1) WHERE city_id=1;

  1.2 ignore index
    在查询语句中表名的后面,添加ignore
index,使用mysql忽视一个或者多个索引。

EXPLAIN SELECT * FROM city IGNORE INDEX(ix1) WHERE  city_id=14;

  1.3 force index
    在查询语句中表名的后面,添加force
index,当mysql不走索引时,强制走索引。

-- 某些情况下,有索引但mysql不走索引,强制使用
EXPLAIN SELECT * FROM city FORCE INDEX (PRIMARY) WHERE  city_id>0;

二. myisampack(MyISAM表压缩工具)

  myisampack是一个表压缩工具。在压缩之前需要确认mysqld已关闭或者要压缩的表不会有其他的sql操作;而且压缩过程会很占用cpu资源,建议在服务器空闲的状态进行。

  可以将数据文件压缩到40%-70%,当以后使用表时,解压缩列需要的信息被读入内存。当访问具体的记录时性能会更好,因为你只需要解压缩一个记录。压缩后的表也将成为一个只读表,不能进行DML操作。

--压缩前,查看test库中表的数据文件大小,其中有个a表是myisam类型, 将压缩MYD文件

    [root@hsr ~]# cd /usr/local/mysql/data

    [root@hsr data]# cd test
    [root@hsr test]# ls -ltr
    总用量 24
    -rw-r----- 1 mysql mysql   61 8月  24 16:43 db.opt
    -rw-r----- 1 mysql mysql 8566 8月  28 14:29 a.frm
    -rw-r----- 1 mysql mysql 1024 8月  28 14:32 a.MYI
    -rw-r----- 1 mysql mysql  456 8月  28 14:32 a.MYD

-- myisampack先到基地址里面找到该命令(用rpm安装可以在根目录下直接用该命令)
[root@hsr ~]# cd /usr/local/mysql/bin

图片 30

-- 压缩a表,提示太小无法压缩
[root@hsr bin]# ./myisampack /usr/local/mysql/data/test/a
/usr/local/mysql/data/test/a is too small to compress
如果压缩成功,该表就只能读,不能写。可以在查看MYD文件大小。

二 .优化数据库对象

  1. 优化表的数据类型
    在mysql中,可以使用函数procedure
analyse()对当前应用的表进行分析。对表列中的数据类型给出合理的改进建议,用户可以根据实际情况来考虑。
  例如:下面生产库中有一个菜单表,字段类型及长度如下:

图片 31

 -- 使用procedure analyse()分析
SELECT * FROM Adm_Menu PROCEDURE ANALYSE(16,256);

    下面remark 字段里面值的最大长度的是30长度,
所以系统建议给出30长度:

图片 32

  2. 通过折分提高表的访问效率
    折分可以是垂直拆分和水平拆分,这是一种设计思路,这篇不讲。

  3. 逆规范化
    逆规范化也叫提高表的冗余,有利于提高查询性能。这是一种设计思路,这篇不讲。

  4. 使用中间表提高统计查询速度
    比如有一个大表记录了客户的每天消费记录,需要按月统计总消费金额,
可以放入到中间表,减轻大表的频繁查询. 这是一种设计思路,这篇不讲。

 

You can leave a response, or trackback from your own site.

Leave a Reply

网站地图xml地图