澳门美高梅游戏官方网站 > 澳门美高梅游戏 > 面向程序猿的数据库访谈质量优化法规,实践陈

原标题:面向程序猿的数据库访谈质量优化法规,实践陈

浏览次数:196 时间:2019-10-06

函数

MySQL中提供了广大放到函数,比方:

图片 1图片 2

 1 CHAR_LENGTH(str)
 2         返回值为字符串str 的长度,长度的单位为字符。一个多字节字符算作一个单字符。
 3         对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
 4 
 5     CONCAT(str1,str2,...)
 6         字符串拼接
 7         如有任何一个参数为NULL ,则返回值为 NULL。
 8     CONCAT_WS(separator,str1,str2,...)
 9         字符串拼接(自定义连接符)
10         CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL)。
11 
12     CONV(N,from_base,to_base)
13         进制转换
14         例如:
15             SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
16 
17     FORMAT(X,D)
18         将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位, 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点,或不含小数部分。
19         例如:
20             SELECT FORMAT(12332.1,4); 结果为: '12,332.1000'
21     INSERT(str,pos,len,newstr)
22         在str的指定位置插入字符串
23             pos:要替换位置其实位置
24             len:替换的长度
25             newstr:新字符串
26         特别的:
27             如果pos超过原字符串长度,则返回原字符串
28             如果len超过原字符串长度,则由新字符串完全替换
29     INSTR(str,substr)
30         返回字符串 str 中子字符串的第一个出现位置。
31 
32     LEFT(str,len)
33         返回字符串str 从开始的len位置的子序列字符。
34 
35     LOWER(str)
36         变小写
37 
38     UPPER(str)
39         变大写
40 
41     LTRIM(str)
42         返回字符串 str ,其引导空格字符被删除。
43     RTRIM(str)
44         返回字符串 str ,结尾空格字符被删去。
45     SUBSTRING(str,pos,len)
46         获取字符串子序列
47 
48     LOCATE(substr,str,pos)
49         获取子序列索引位置
50 
51     REPEAT(str,count)
52         返回一个由重复的字符串str 组成的字符串,字符串str的数目等于count 。
53         若 count <= 0,则返回一个空字符串。
54         若str 或 count 为 NULL,则返回 NULL 。
55     REPLACE(str,from_str,to_str)
56         返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
57     REVERSE(str)
58         返回字符串 str ,顺序和字符顺序相反。
59     RIGHT(str,len)
60         从字符串str 开始,返回从后边开始len个字符组成的子序列
61 
62     SPACE(N)
63         返回一个由N空格组成的字符串。
64 
65     SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
66         不带有len 参数的格式从字符串str返回一个子字符串,起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串,起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样,则子字符串的位置起始于字符串结尾的pos 字符,而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
67 
68         mysql> SELECT SUBSTRING('Quadratically',5);
69             -> 'ratically'
70 
71         mysql> SELECT SUBSTRING('foobarbar' FROM 4);
72             -> 'barbar'
73 
74         mysql> SELECT SUBSTRING('Quadratically',5,6);
75             -> 'ratica'
76 
77         mysql> SELECT SUBSTRING('Sakila', -3);
78             -> 'ila'
79 
80         mysql> SELECT SUBSTRING('Sakila', -5, 3);
81             -> 'aki'
82 
83         mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
84             -> 'ki'
85 
86     TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
87         返回字符串 str , 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项,在未指定情况下,可删除空格。
88 
89         mysql> SELECT TRIM('  bar   ');
90                 -> 'bar'
91 
92         mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
93                 -> 'barxxx'
94 
95         mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
96                 -> 'bar'
97 
98         mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
99                 -> 'barx'

有个别内置函数

1、自定义函数

图片 3图片 4

 1 delimiter \
 2 create function f1(
 3     i1 int,
 4     i2 int)
 5 returns int
 6 BEGIN
 7     declare num int;
 8     set num = i1 + i2;
 9     return(num);
10 END \
11 delimiter ;

View Code

2、删除函数

1 drop function func_name;

3、施行函数

图片 5图片 6

1 # 获取返回值
2 declare @i VARCHAR(32);
3 select UPPER('alex') into @i;
4 SELECT @i;
5 
6 
7 # 在查询中使用
8 select f1(11,nid) ,name from tb2;

View Code

 

概述  

  对于二进制安装,优点是能够设置到另外路线下,灵活性好,一台服务器能够安装八个mysql。劣点是已经绎过编写翻译,质量比不上源码编译得好,无法灵活定制编译参数。借使客商即不想安装最简便实际不是常不足利索的RPM包,又不想安装复杂费时的源码包,那么已编写翻译好的二进制包将是最棒的选料。


引言

试行安排

explain + 查询SQL - 用于显示SQL实行音讯参数,依照参照他事他说加以考察音信能够张开SQL优化

1 mysql> explain select * from tb2;
2 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
3 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
4 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
5 |  1 | SIMPLE      | tb2   | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
6 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
7 1 row in set (0.00 sec)

图片 7图片 8

 1 id
 2         查询顺序标识
 3             如:mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
 4             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
 5             | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 6             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
 7             |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |    9 | NULL        |
 8             |  2 | DERIVED     | tb1        | range | PRIMARY       | PRIMARY | 8       | NULL |    9 | Using where |
 9             +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
10         特别的:如果使用union连接气值可能为null
11 
12 
13     select_type
14         查询类型
15             SIMPLE          简单查询
16             PRIMARY         最外层查询
17             SUBQUERY        映射为子查询
18             DERIVED         子查询
19             UNION           联合
20             UNION RESULT    使用联合的结果
21             ...
22     table
23         正在访问的表名
24 
25 
26     type
27         查询时的访问方式,性能:all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
28             ALL             全表扫描,对于数据表从头到尾找一遍
29                             select * from tb1;
30                             特别的:如果有limit限制,则找到之后就不在继续向下扫描
31                                    select * from tb1 where email = 'seven@live.com'
32                                    select * from tb1 where email = 'seven@live.com' limit 1;
33                                    虽然上述两个语句都会进行全表扫描,第二句使用了limit,则找到一个后就不再继续扫描。
34 
35             INDEX           全索引扫描,对索引从头到尾找一遍
36                             select nid from tb1;
37 
38             RANGE          对索引列进行范围查找
39                             select *  from tb1 where name < 'alex';
40                             PS:
41                                 between and
42                                 in
43                                 >   >=  <   <=  操作
44                                 注意:!= 和 > 符号
45 
46 
47             INDEX_MERGE     合并索引,使用多个单列索引搜索
48                             select *  from tb1 where name = 'alex' or nid in (11,22,33);
49 
50             REF             根据索引查找一个或多个值
51                             select *  from tb1 where name = 'seven';
52 
53             EQ_REF          连接时使用primary key 或 unique类型
54                             select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
55 
56 
57 
58             CONST           常量
59                             表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
60                             select nid from tb1 where nid = 2 ;
61 
62             SYSTEM          系统
63                             表仅有一行(=系统表)。这是const联接类型的一个特例。
64                             select * from (select nid from tb1 where nid = 1) as A;
65     possible_keys
66         可能使用的索引
67 
68     key
69         真实使用的
70 
71     key_len
72         MySQL中使用索引字节长度
73 
74     rows
75         mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
76 
77     extra
78         该列包含MySQL解决查询的详细信息
79         “Using index”
80             此值表示mysql将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
81         “Using where”
82             这意味着mysql服务器将在存储引擎检索行后再进行过滤,许多where条件里涉及索引中的列,当(并且如果)它读取索引时,就能被存储引擎检验,因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示:查询可受益于不同的索引。
83         “Using temporary”
84             这意味着mysql在对查询结果排序时会使用一个临时表。
85         “Using filesort”
86             这意味着mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。mysql有两种文件排序算法,这两种排序方式都可以在内存或者磁盘上完成,explain不会告诉你mysql将使用哪一种文件排序,也不会告诉你排序会在内存里还是磁盘上完成。
87         “Range checked for each record(index map: N)”
88             这个意味着没有好用的索引,新的索引将在联接的每一行上重新估算,N是显示在possible_keys列中索引的位图,并且是冗余的。

详细

事务

事情用于将或多或少操作的多个SQL作为原子性操作,一旦有某三个并发谬误,就能够回滚到原本的事态,进而保险数据库数据完整性。

图片 9图片 10

 1 delimiter \
 2 create PROCEDURE p1(
 3     OUT p_return_code tinyint
 4 )
 5 BEGIN 
 6   DECLARE exit handler for sqlexception 
 7   BEGIN 
 8     -- ERROR 
 9     set p_return_code = 1; 
10     rollback; 
11   END; 
12  
13   DECLARE exit handler for sqlwarning 
14   BEGIN 
15     -- WARNING 
16     set p_return_code = 2; 
17     rollback; 
18   END; 
19  
20   START TRANSACTION; 
21     DELETE from tb1;
22     insert into tb2(name)values('seven');
23   COMMIT; 
24  
25   -- SUCCESS 
26   set p_return_code = 0; 
27  
28   END\
29 delimiter ;

支撑职业的累积进度

1 set @i =0;
2 call p1(@i);
3 select @i;

 八. 设置密码  

  8.1 张开my.cnf文件,增加skip-grant-tables,来重新载入参数密码,如下所示
    图片 11
  8.2 运营服务,再度登入,在输入密码处按回车键步入。

[root@hsr ~]# vim /etc/my.cnf
[root@hsr ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@hsr ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.23

  8.3 步向mysql后,修改密码

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> update user set authentication_string=password('123456') where user='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

  退出mysql>quit;  编辑 my.cnf 注释掉#skip-grant-tables

 8.4 重启mysql服务,输入修改后的密码(123456)步向

[root@hsr ~]# service mysql restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@hsr ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.7.23
create table test(info varchar(30))
go
insert into test (info)values('a'),
('b'),('a'),('c'),('d'),('d') 
go

select info,count_big(info),grouping(info)
from test group by info 
WITH ROLLUP

go
drop table test 
----输出----
ifno 无列名 无列名
a    2    0
b    1    0
c    1    0
d    2    0
NULL    6    1

 

不错利用索引

数据库表中增加索引后真的会让查询速度起飞,但前提必需是准确的使用索引来查询,如若以错误的办法利用,则就是建设构造目录也会不见效。
就算创建目录,索引也不会一蹴而就:

 1 - like '%xx'
 2     select * from tb1 where name like '%cn';
 3 - 使用函数
 4     select * from tb1 where reverse(name) = 'wupeiqi';
 5 - or
 6     select * from tb1 where nid = 1 or email = 'seven@live.com';
 7     特别的:当or条件中有未建立索引的列才失效,以下会走索引
 8             select * from tb1 where nid = 1 or name = 'seven';
 9             select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
10 - 类型不一致
11     如果列是字符串类型,传入条件是必须用引号引起来,不然...
12     select * from tb1 where name = 999;
13 - !=
14     select * from tb1 where name != 'alex'
15     特别的:如果是主键,则还是会走索引
16         select * from tb1 where nid != 123
17 - >
18     select * from tb1 where name > 'alex'
19     特别的:如果是主键或索引是整数类型,则还是会走索引
20         select * from tb1 where nid > 123
21         select * from tb1 where num > 123
22 - order by
23     select email from tb1 order by name desc;
24     当根据索引排序时候,选择的映射如果不是索引,则不走索引
25     特别的:如果对主键排序,则还是走索引:
26         select * from tb1 order by nid desc;
27  
28 - 组合索引最左前缀
29     如果组合索引为:(name,email)
30     name and email       -- 使用索引
31     name                 -- 使用索引
32     email                -- 不使用索引

一.步骤1: 解压glib包

-- 在 /usr/local 下创建一个mysql文件夹,用来存放
[root@hsr local]# mkdir mysql
[root@hsr local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src

-- 在原有/usr/tool目录将gz压缩包解压
[root@hsr tool]# tar -zxvf mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisamchk
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisamlog
mysql-5.7.23-linux-glibc2.12-x86_64/bin/myisampack
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql_client_test_embedded
mysql-5.7.23-linux-glibc2.12-x86_64/bin/mysql_config_editor
.....

--将解压的文件复制到/usr/local/mysql目录下
[root@hsr tool]# sudo cp -r mysql-5.7.23-linux-glibc2.12-x86_64 /usr/local/mysql

--在mysql-->mysql-5.7.23-linux-glibc2.12-x86_64路径下 解压的文件共9个 目录如下: 
[root@hsr mysql]# ls mysql-5.7.23-linux-glibc2.12-x86_64 bin COPYING docs include lib man README share support-files

  注意:mysql-5.7.23-linux-glibc2.12-x86_64目录等级次序要去掉,变为/usr/local/mysql 下的9个目录,在篇章前面会去掉那层。

  图片 12

摘要:
GROUPING 用于区分列是或不是由 ROLLUP、CUBE 或 GROUPING SETS 聚合而产生的行
若果是原生态的行聚合,则重返0 ,新扩张的行数据就赶回1

3、裁减交互次数

a、配置MySQL自动记录慢日志

1 slow_query_log = OFF                            是否开启慢日志记录
2 long_query_time = 2                              时间限制,超过此时间,则记录
3 slow_query_log_file = /usr/slow.log        日志文件
4 log_queries_not_using_indexes = OFF     为使用索引的搜索是否记录

注:查看当前安插音信:
       show variables like '%query%'
     修改当前布置:
    set global 变量名 = 值

 五.步骤5 配置mysql(mysql.server)和my.cnf文件

--检查 etc/my.cnf文件是否存在 (二进制安装,默认配置文件在/etc/my.cnf)
    [root@hsr etc]# find -name  my.cnf
    ./my.cnf

 --将support-files 目录下的mysql.server文件复制到etc/init.d下
    [root@hsr ~]# cd /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/support-files
    [root@hsr support-files]# cp mysql.server /etc/init.d/mysql

 -- 配置/etc/init.d/mysql服务文件 添加basedir和datadir
    [root@hsr support-files]# vim /etc/init.d/mysql

    图片 13

   修改my.cnf  配置以下三个参数(留意:chkconfig -- level 35 mysqld on 不要加上,前边报错,又得去掉)
    图片 14

转自: http://www.maomao365.com/?p=6208  

5.1、顾客端多进度并行访问

多进度并行访谈是指在客户端创建四个经过(线程),每一种过程创设一个与数据库的接连,然后还要向数据库提交访谈央求。当数据库主机财富有空余时,大家得以使用顾客端多进度并行访谈的法子来抓好品质。假如数据库主机已经很忙时,采取多进度并行访谈质量不会拉长,反而大概会更加慢。所以选取这种艺术最佳与DBA或系统管理员实行联系后再决定是或不是使用。

 

面向程序猿的数据库访谈质量优化法规,实践陈设。例如:

咱俩有10000个产品ID,今后亟需基于ID取出产品的详细信息,假使单线程访谈,按各样IO要5ms总计,忽略主机CPU运算及互连网传输时间,我们要求50s手艺到位职分。倘诺采用5个互相访谈,各样进度访谈三千个ID,那么10s就有一点都不小可能率成功职分。

这是还是不是互相数越来越多越好呢,开一千个相互是还是不是只要50ms就消除,答案自然是还是不是认的,当并行数当先服务器主机能源的上限期品质就不会再升高,借使再充实反而会追加主机的进度间调节约资金金和经过争执机率。

 

以下是有的哪些设置并行数的主题建议:

要是瓶颈在服务器主机,可是主机还会有空闲能源,那么最大交互数取主机CPU核数和主机提供数据服务的磁盘数五个参数中的最小值,同期要确认保障主机有财富做别的任务。

假诺瓶颈在顾客端管理,但是客商端还也许有空闲能源,那提议实际不是增添SQL的竞相,而是用一个进程取回数据后在客户端起两个进度管理就可以,进度数依照顾客端CPU核数总计。

假如瓶颈在客户端互连网,那提出做数据压缩或许扩展多少个顾客端,采取map reduce的架构管理。

一旦瓶颈在服务器互联网,那需要充实服务器的网络带宽或许在服务端将数据压缩后再管理了。

 

别的注意事项

1 - 避免使用select *
2 - count(1)或count(列) 代替 count(*)
3 - 创建表时尽量时 char 代替 varchar
4 - 表的字段顺序固定长度的字段优先
5 - 组合索引代替多个单列索引(经常使用多个条件查询时)
6 - 尽量使用短索引
7 - 使用连接(JOIN)来代替子查询(Sub-Queries)
8 - 连表时注意条件类型需一致
9 - 索引散列值(重复少)不适合建索引,例:性别不适合

七 登录mysql

[root@hsr ~]# mysql -u root -p
bash: mysql: 未找到命令

  未找到命令,是由于系统默许会查找/usr/bin下的指令,假如那几个命令不在那些目录下,就能够找不到命令,需求映射叁个链接到/usr/bin目录下,也正是创立一个链接文件。

[root@hsr ~]#  ln -s /usr/local/mysql/bin/mysql /usr/bin
[root@hsr ~]# mysql -u -root -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

--错误信息是不能连接到本地的socket ,系统默认找到了/tmp目录下,需要设置链接文件
[root@hsr tmp]# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

[root@hsr ~]# mysql -u -root -p
Enter password:

 

3.3、设置Fetch Size

当大家使用select从数据库查询数据时,数据暗许并非一条一条回来给顾客端的,亦非一回全部赶回客商端的,而是基于顾客端fetch_size参数管理,每趟只回去fetch_size条记下,当顾客端游标遍历到尾部时再从劳动端取数据,直到最后全体传递完毕。所以借使我们要从服务端三回取一大波数量时,能够加大fetch_size,那样能够减去结果数据传输的竞相次数及服务器数据策画时间,升高质量。

 

以下是jdbc测量检验的代码,选拔地方数据库,表缓存在数据库CACHE中,因而未曾网络连接及磁盘IO开销,顾客端只遍历游标,不做其余处理,那样更能反映fetch参数的熏陶:

String vsql ="select * from t_employee";

PreparedStatement pstmt = conn.prepareStatement(vsql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);

pstmt.setFetchSize(1000);

ResultSet rs = pstmt.executeQuery(vsql);

int cnt = rs.getMetaData().getColumnCount();

Object o;

while (rs.next()) {

    for (int i = 1; i <= cnt; i++) {

       o = rs.getObject(i);

    }

}

 

测验示例中的employee表有一千00条记下,每条记下平均长度135字节

 

以下是测量试验结果,对种种fetchsize测量试验5次再取平均值:

 

fetchsize

 elapse_time(s)

1

20.516

2

11.34

4

6.894

8

4.65

16

3.584

32

2.865

64

2.656

128

2.44

256

2.765

512

3.075

1024

2.862

2048

2.722

4096

2.681

8192

2.715

 

 

 

 

Oracle jdbc fetchsize暗许值为10,由上测量检验能够见到fetchsize对质量影响照旧相当的大的,但是当fetchsize大于100时就好些个并未有影响了。fetchsize并不会设有二个最优的固定值,因为完全品质与记录集大小及硬件平台有关。依据测量试验结果建议当一回性要取大批量多少时那几个值设置为100左右,不要小于40。注意,fetchsize不可能设置太大,要是一次抽取的数目超越JVM的内部存款和储蓄器会形成内部存款和储蓄器溢出,所以提出并非高出一千,太大了也没怎么性质升高,反而大概会扩展内部存储器溢出的危险。

注:图中fetchsize在128随后会有局部小的不定,那并不是测验标称误差,而是由于resultset填充到具体对像时间各异的原故,由于resultset已经到地面内存里了,所以预计是由于CPU的L1,L2 Cache命中率变化变成,由于变化比很小,所以小编也未深入深入分析原因。

 

iBatis的SqlMapping配置文件能够对每一个SQL语句内定fetchsize大小,如下所示:

 

<select id="getAllProduct" resultMap="HashMap" fetchSize="1000">

select * from employee

</select>

 

limit分页

无论是是不是有目录,limit分页是二个值得关怀的标题

图片 15图片 16

 1 每页显示10条:
 2 当前 118 120, 125
 3 
 4 倒序:
 5             大      小
 6    970  7 6  6 5  54  43  32
 7 19 98     
 8 下一页:
 9 
10     select 
11         * 
12     from 
13         tb1 
14     where 
15         nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)  
16     order by 
17         nid desc 
18     limit 10;
19 
20 
21 
22     select 
23         * 
24     from 
25         tb1 
26     where 
27         nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)  
28     order by 
29         nid desc 
30     limit 10;
31 
32 
33 上一页:
34 
35     select 
36         * 
37     from 
38         tb1 
39     where 
40         nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)  
41     order by 
42         nid desc 
43     limit 10;
44 
45 
46     select 
47         * 
48     from 
49         tb1 
50     where 
51         nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)  
52     order by 
53         nid desc 
54     limit 10;

View Code

 九 远程登陆

-- 登录到mysql后设置权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
--  再设置密码
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
-- 设置权限
mysql> ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)
-- 刷新权限 
mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)
-- 设置远程登录权限
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

  -- 在my.cnf中 增添端口,重启服务
  图片 17

-- 测试端口是否打开
[root@hsr ~]#  firewall-cmd    --query-port=3306/tcp
no
-- 防火墙设置
[root@hsr ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
-- 重新加载
[root@hsr ~]# firewall-cmd --reload
success
-- 再次测试端口
[root@hsr ~]# firewall-cmd    --query-port=3306/tcp
yes

  -- 在windows端拼通成功
  图片 18

  --- 最终动用SQLyog连接成功
  图片 19
  图片 20

  

grouping 语法简单介绍 :
GROUPING (<列名>)
参数列名:

返回值
tinyint
<hr />
grouping 应用比如:  

1、  本文只是面临数据库应用开采的技术员,不合乎专门的工作DBA,DBA在数据库质量优化方面须要精晓更多的学问;

慢日志查询

四步骤4:

  4.1 创建RSA private key。

[root@hsr bin]# bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

  图片 21

  4.2 修改当前目录具有者为 root 客商,修改data 目录具有者为 mysql

[root@hsr bin]# chown -R root:root /usr/local/mysql
[root@hsr bin]# chown -R mysql:mysql /usr/local/mysql/data

1.1、创造并行使科学的目录

数据库索引的原理极度轻巧,但在纷纭的表中真正能精确行使索引的人比较少,即便是明媒正娶的DBA也不自然能一心变成最优。

索引会大大扩大表记录的DML(INSERT,UPDATE,DELETE)花费,正确的目录能够让品质升高100,一千倍以上,不成立的目录也恐怕会让品质裁减100倍,由此在多个表中成立什么样的目录须求平衡种种业务供给。

目录常见难题:

目录有啥样项目?

大范围的目录有B-TREE索引、位图索引、全文索引,位图索引常常用来数据货仓应用,全文索引由于使用少之甚少,这里不长远介绍。B-TREE索引包含不菲扩充项目,如结合索引、反向索引、函数索引等等,以下是B-TREE索引的简易介绍:

B-TREE索引也叫做平衡树索引(Balance Tree),它是一种按字段排好序的树形目录结构,主要用来进级查询品质和独一约束支持。B-TREE索引的原委囊括根节点、分支节点、叶子节点。

叶子节点内容:索引字段内容+表记录ROWID

根节点,分支节点内容:当一个数量块中不可能放下全体索引字段数据时,就能产生树形的根节点或分段节点,根节点与分支节点保存了索引树的逐一及各层级间的援引关系。

         二个常见的BTREE索引结构暗暗提示图如下所示:

 

 

若是大家把三个表的剧情感到是一本字典,那索引就一定于字典的目录,如下图所示:

 

 

 

 

 

图中是三个字典按部首+笔划数的目录,也等于给字典建了一个按部首+笔划的构成索引。

贰个表中能够建几个目录,就好像一本字典能够建七个目录同样(按拼音、笔划、部首等等)。

二个索引也得以由两个字段组成,称为组合索引,如上海教室正是叁个按部首+笔划的构成目录。

SQL什么条件会采取索引?

当字段上建有目录时,日常以下意况会采纳索引:

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'(后导模糊查询)

T1. INDEX_COLUMN=T2. COLUMN1(七个表通过索引字段关联)

 

SQL什么条件不会使用索引?

 

 

询问条件

不能够采用索引原因

INDEX_COLUMN <> ?

INDEX_COLUMN not in (?,?,...,?)

不等于操作不能利用索引

function(INDEX_COLUMN) = ?

INDEX_COLUMN + 1 = ?

INDEX_COLUMN || 'a' = ?

经过普通运算或函数运算后的索引字段无法使用索引

INDEX_COLUMN like '%'||?

INDEX_COLUMN like '%'||?||'%'

含前导模糊查询的Like语法无法选择索引

INDEX_COLUMN is null

B-TREE索引里不保留字段为NULL值记录,因而IS NULL没办法采用索引

NUMBER_INDEX_COLUMN='12345'

CHAR_INDEX_COLUMN=12345

Oracle在做数值比较时索要将两侧的多少调换来同一种数据类型,尽管两侧数据类型分裂一时间会对字段值隐式调换,相当于加了一层函数管理,所以不能选取索引。

a.INDEX_COLUMN=a.COLUMN_1

给索引查询的值应是已知多少,不可能是未知字段值。

注:

由此函数运算字段的字段要使用能够运用函数索引,这种需要提出与DBA交流。

突发性大家会采纳三个字段的结合索引,若是查询条件中率先个字段不能够使用索引,这全数查询也不能够应用索引

如:咱们company表建了三个id+name的整合索引,以下SQL是无法选取索引的

Select * from company where name=?

Oracle9i后引进了一种index skip scan的目录形式来缓和类似的主题材料,可是通过index skip scan升高质量的规格比较奇特,使用不佳反而品质会更差。

 

 

作者们经常在如何字段上建索引?

那是二个极度复杂的话题,供给对事情及数量尽量深入分析后再能搜查缉获结果。主键及外键日常都要有目录,别的必要建索引的字段应满意以下条件:

1、字段现身在询问条件中,並且询问条件能够选取索引;

2、语句实施功能高,一天会有几千次以上;

3、通过字段条件可筛选的记录集不大,那数据筛选比例是稍微才相符?

其一从未固定值,须求依赖表数据量来评估,以下是经验公式,可用以神速评估:

小表(记录数小于10000行的表):筛选比例<百分之十;

大表:(筛选重临记录数)<(表总记录数*单条记录长度)/一千0/16

      单条记录长度≈字段平均内容长度之和+字段数*2

 

以下是某个字段是或不是必要建B-TREE索引的阅历分类:

 

 

 

字段类型

常见字段名

亟待建索引的字段

主键

ID,PK

外键

PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID

有对像或地点标志意义字段

HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO

索引慎用字段,需求张开数据遍布及运用情形详细评估

日期

GMT_CREATE,GMT_MODIFIED

年月

YEAR,MONTH

意况标记

PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG

类型

ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE

区域

COUNTRY,PROVINCE,CITY

操作人士

CREATOR,AUDITOR

数值

LEVEL,AMOUNT,SCORE

长字符

ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT

不契合建索引的字段

陈诉备注

DESCRIPTION,REMARK,MEMO,DETAIL

大字段

FILE_CONTENT,EMAIL_CONTENT

 

 

怎么样晓得SQL是还是不是利用了不易的目录?

简言之SQL可以依赖目录使用语准则则判定,复杂的SQL不佳办,判别SQL的响应时间是一种政策,然则那会碰着数据量、主机负载及缓存等因素的熏陶,一时数据全在缓存里,恐怕全表访谈的时间法郎引访谈时间还少。要准确了解索引是还是不是科学使用,需求到数据库中查阅SQL真实的实行安插,这么些话题相比较复杂,详见SQL推行安排专项论题介绍。

 

目录对DML(INSERT,UPDATE,DELETE)附加的开销有微微?

其一从未牢固的比重,与各类表记录的轻重缓急及索引字段大小紧凑相关,以下是二个普通表测量试验数据,仅供参照他事他说加以考察:

目录对于Insert品质裁减52%

目录对于Update品质收缩57%

目录对于Delete质量减少29%

故此对此写IO压力相当大的系统,表的目录须要精心评估须要性,别的索引也会占用一定的寄存空间。

 

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log

 1 """
 2 --verbose    版本
 3 --debug      调试
 4 --help       帮助
 5  
 6 -v           版本
 7 -d           调试模式
 8 -s ORDER     排序方式
 9              what to sort by (al, at, ar, c, l, r, t), 'at' is default
10               al: average lock time
11               ar: average rows sent
12               at: average query time
13                c: count
14                l: lock time
15                r: rows sent
16                t: query time
17 -r           反转顺序,默认文件倒序拍。reverse the sort order (largest last instead of first)
18 -t NUM       显示前N条just show the top n queries
19 -a           不要将SQL中数字转换成N,字符串转换成S。don't abstract all numbers to N and strings to 'S'
20 -n NUM       abstract numbers with at least n digits within names
21 -g PATTERN   正则匹配;grep: only consider stmts that include this string
22 -h HOSTNAME  mysql机器名或者IP;hostname of db server for *-slow.log filename (can be wildcard),
23              default is '*', i.e. match all
24 -i NAME      name of server instance (if using mysql.server startup script)
25 -l           总时间中不减去锁定时间;don't subtract lock time from total time
26 """

 

 六.步骤6 启动mysql

[root@hsr bin]# service mysql start
/etc/init.d/mysql: line 239: my_print_defaults: command not found
    Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe) 

--提示未找到路径,需要把"mysql-5.7.23-linux-glibc2.12-x86_64" 文件夹去掉,使用mv 将里面的文件移到/usr/locl/mysql下,共9个文件
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/README /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/COPYING /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/support-files /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/share /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/man /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/lib /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/include /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/docs /usr/local/mysql
[root@hsr bin]# mv /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/bin /usr/local/mysql

-- 再启动
[root@hsr bin]# service mysql start
Starting MySQL.2018-08-24T01:06:20.545225Z mysqld_safe error: log-error set to '/var/log/mariadb/mariadb.log', however file don't exists. Create writable for user 'mysql'.
 ERROR! The server quit without updating PID file (/usr/local/mysql/data/hsr.pid).

-- 提示/var/log/mariadb/mariadb.log 不存在, 打开my.cnf 能看到定义的默认路径
[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

--创建目录 给权限
[root@hsr bin]# mkdir /var/log/mariadb 
[root@hsr bin]# touch /var/log/mariadb/mariadb.log 
[root@hsr bin]# chown -R mysql:mysql  /var/log/mariadb/

-- 再启动
[root@hsr bin]# service mysql start
Starting MySQL.... ERROR! The server quit without updating PID file (/usr/local/mysql/data/hsr.pid).

-- 错误信息是hsr.pid进程出问题,先查看下日志
[root@hsr ~]# cat /var/log/mariadb/mariadb.log

  图片 22
   在my.cnf中注释上边参数(#chkconfig --level 35 mysqld on),再启动
  图片 23
  上海教室意思是不可能创制mysql.sock.lock 文件,平时是权力不足,如下设置好权力,运转成功
  图片 24

3.1、batch DML

数据库访谈框架平日都提供了批量付出的接口,jdbc扶助batch的交由管理办法,当您二回性要往二个表中插入1000万条数据时,如若应用日常的executeUpdate管理,那么和服务器交互次数为壹仟万次,按每分钟能够向数据库服务器交由一千0次揣摸,要到位全部专业索要1000秒。如若运用批量交给格局,1000条提交一遍,那么和服务器交互次数为1万次,交互次数大大减弱。选取batch操作日常不会减价扣过许多据库服务器的物理IO,可是会大大裁减客商端与服务端的并行次数,进而减弱了一再倡议的网络延时开销,同一时候也会稳中有降数据库的CPU费用。

 

一经要向一个普通表插入一千万数量,每条记下大小为1K字节,表上未有任何索引,客商端与数据库服务器网络是100Mbps,以下是基于现行反革命貌似计算机技能推断的种种batch大小质量比较值:

 

 

 单位:ms

No batch

Batch=10

Batch=100

Batch=1000

Batch=10000

服务器事务处理时间

0.1

0.1

0.1

0.1

0.1

服务器IO处理时间

0.02

0.2

2

20

200

网络交互发起时间

0.1

0.1

0.1

0.1

0.1

网络数据传输时间

0.01

0.1

1

10

100

小计

0.23

0.5

3.2

30.2

300.2

平均每条记录处理时间

0.23

0.05

0.032

0.0302

0.03002

 

 

从上得以见见,Insert操作加大Batch可以对品质升高近8倍质量,日常根据主键的Update或Delete操作也或者提升2-3倍品质,但不比Insert鲜明,因为Update及Delete操作恐怕有比不小的开垦在情理IO访问。以上仅是论战计算值,实际情形须要依靠具体条件衡量。

 

三 步骤3:  

  安装mysql,使用 --initialize,basedir 基础目录,datadir 为多少目录。

[root@hsr ~]# cd /usr/local/mysql/mysql-5.7.23-linux-glibc2.12-x86_64/bin
[root@hsr bin]# ./mysqld  --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2018-08-23T06:56:21.157088Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2018-08-23T06:56:21.157246Z 0 [ERROR] Can't find error-message file '/usr/local/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2018-08-23T06:56:26.287087Z 0 [Warning] InnoDB: New log files created, LSN=45790
2018-08-23T06:56:27.059913Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2018-08-23T06:56:27.138616Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: a7e28575-a6a1-11e8-af13-000c29affb65.
2018-08-23T06:56:27.154064Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2018-08-23T06:56:27.155635Z 1 [Note] A temporary password is generated for root@localhost: ro0ssOGT?ocf

4.2、合理运用排序

Oracle的排序算法一向在优化,可是总体时间复杂度相当于nLog(n)。普通OLTP系统排序操作日常都以在内部存款和储蓄器里开展的,对于数据库来讲是一种CPU的损耗,曾经在PC机做过测量试验,单核普通CPU在1分钟能够做到100万条记下的全内部存款和储蓄器排序操作,所以说由于前些天CPU的属性巩固,对于常见的几十条或上百条记下排序对系统的影响也不会极大。不过当你的记录集扩展到上万条以上时,你须要专心是否必然要如此做了,大记录集排序不止平添了CPU费用,而且或许会出于内部存款和储蓄器不足发猛烈盘排序的地方,当发刚烈盘排序时品质会小幅下落,这种须求必要与DBA交流再决定,决计于你的须求和数目,所以独有你自身最通晓,而毫不被旁人说排序比不快就吓倒。

以下列出了或许会发生排序操作的SQL语法:

Order by

Group by

Distinct

Exists子查询

Not Exists子查询

In子查询

Not In子查询

Union(并集),Union All也是一种并集操作,不过不会爆发排序,假使您确认几个数据集不供给试行去除重复数据操作,那请使用Union All 代替Union。

Minus(差集)

Intersect(交集)

Create Index

Merge Join,这是一种几个表连接的内部算法,实践时会把五个表先排序好再连接,应用于三个大表连接的操作。若是您的多少个表连接的标准都以等值运算,那能够利用Hash Join来进步质量,因为Hash Join使用Hash 运算来顶替排序的操作。具体原理及安装参谋SQL实践布署优化专项论题。

 

二. 步骤2:    

   2.1 添加mysql用户 useradd -r -g 用户名 用户组

[root@hsr mysql]# groupadd mysql
[root@hsr mysql]# useradd -r -g mysql mysql

  2.2 切换成 /usr/local/mysql 目录下,退换目录具有者为mysql

[root@hsr mysql]# chown -R  mysql.mysql /usr/local/mysql

  2.3 新境遇安装libaio包 mysql 依赖于libaio

[root@hsr mysqld]# yum search libaio

特意表达:

本文由澳门美高梅游戏官方网站发布于澳门美高梅游戏,转载请注明出处:面向程序猿的数据库访谈质量优化法规,实践陈

关键词:

上一篇:Mysql常用语法及入门开篇,验证整型函数分享

下一篇:没有了