- 使用 Spring Initializr 创建 Spring Boot 应用程序
- 在Spring Boot中配置Cassandra
- 在 Spring Boot 上配置 Tomcat 连接池
- 将Camel消息路由到嵌入WildFly的Artemis上
今天女朋友没有问题,发现自己对MySQL认知不到位,总结了一点内容👆
👉远程连接
👉授权
相关库:mysql
相关表:user
相关字段:select host,user from user;
create user 用户名@ip地址 identified by '密码';
grant all on *.* To 用户名@'ip地址';
`grant select,create on 数据库名.表名 To 用户名@ip地址;`
# 创建用户
create user root@'192.168.11.%' identified by '123456';
# 这样root@192.168.11.% 这个网段的用户可以登录
create user hans@'192.168.11.161' identified by '123456';
# 这样只允许hans用户登录
create user li@'%' identified by '123456';
# 所有li用户都可登录
# 删除用户
drop user root@'192.168.11.%';
# 授权
grant all on *.* To hans@'192.168.11.161';
grant select,create on oldboy_test.* To hans@'192.168.11.161';
# 查看授权
show grants for hans@'192.168.11.161';
# 刷新权限表
flush privileges;
我们知道MySQL在配置好环境变量后,直接mysql -p xx -u xx -h xx
就登录了,不需要先启动服务端,再启动客户端这么繁琐,但凡涉及到服务端和客户端就会涉及到通信问题,客户端进程向服务器进程发送请求并得到回复的过程本质上是一个进程间通信的过程!那么MySQL的通信方式??是什么???
在我们实际使用数据库的过程中,大概率服务器和客户端不会在一台机器上,那么他们之间就得通过网络来通信,MySQL
采用TCP
作为服务器和客户端之间的网络通信协议。我们知道MySQL登录的命令可以携带多个参数,在我们有权限远程登录的情况下,通过-P
和-h
来指定端口和域名;
ps:协议不清楚可以百度或者看一下俺的博客哈哈哈
👉网络编程理论 - HammerZe - 博客园 (cnblogs.com)
我们都知道MySQL
服务器的默认端口为3306
,之后就在这个端口号上等待客户端进程进行连接(MySQL
服务器会默认监听3306
端口)
如果端口被占用了,可以在启动服务器的时候通过-P
指定参数:
mysqld -P3307
禁止各客户端使用TCP/IP
网络进行通信:
mysqld --skip-networking
mysql -h127.0.0.1 -uroot -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (61)
如果是Windows
用户,客户端和服务端连接可以使用:
使用这两种方式连接需要添加参数:
命名管道
来进行进程间通信:需要在启动服务器程序的命令中加上--enable-named-pipe
参数,然后在启动客户端程序的命令中加入--pipe
或者--protocol=pipe
参数;
共享内存
来进行进程间通信:需要在启动服务器程序的命令中加上--shared-memory
参数,在成功启动服务器后,共享内存
便成为本地客户端程序的默认连接方式,不过我们也可以在启动客户端程序的命令中加入--protocol=memory
参数来显式的指定使用共享内存进行通信;
注意
共享内存
的方式进行通信的服务器进程和客户端进程必须在同一台Windows
主机中使用此连接方式的前提是服务器和客户端进程都在同一类Unix
的机器上,我们才可以使用Unix域套接字文件
来进行通信;
启动客户端程序的时候指定的主机名为localhost
,或者指定了--protocol=socket
的启动参数,那服务器程序和客户端程序之间就可以通过Unix
域套接字文件来进行通信了;
MySQL
服务器程序默认监听的Unix
域套接字文件路径为/tmp/mysql.sock
,客户端程序也默认连接到这个Unix
域套接字文件;
# 修改默认路径,启动时指定路径
mysqld --socket=/tmp/a.txt
然后登录的时候服务器监听的就是tmp下的a.txt文件,如何登录?
mysql -hlocalhost -uroot --socket=/tmp/a.txt -p1234
因为我们写的MySQL
语句执行起来效率可能并不是很高,MySQL
的优化程序会对我们的语句做一些优化,如外连接转换为内连接、表达式简化、子查询等,最后优化的结果就是生成一个执行计划
,这个执行计划表明了应该使用哪些索引进行查询,表之间的连接顺序是啥样的,我们可以通过EXPLAIN
语句来设置执行计划;
转自:https://cloud.tencent.com/developer/article/1666887
在MySQL中,并不是你建立了索引,并且你在SQL中使用到了该列,MySQL就肯定会使用到那些索引的,有一些情况很可能在你不知不觉中,你就“成功的避开了”MySQL的所有索引;
多种情况说明如下:
如果where条件中age
列中使用了计算,则不会使用该索引。如果需要计算,千万不要计算到索引列,想方设法让其计算到表达式的另一边去;
SELECT `sname` FROM `t_stu` WHERE `age`=20; -- 会使用索引
SELECT `sname` FROM `t_stu` WHERE `age`+10=30; -- 不会使用索引!!因为所有索引列参与了计算
SELECT `sname` FROM `t_stu` WHERE `age`=30-10; -- 会使用索引
同样的道理,索引列使用了函数,一样会导致相同的后果
SELECT `sname` FROM `stu` WHERE concat(`sname`,'abc') ='Jaskeyabc'; -- 不会使用索引,因为使用了函数运算,原理与上面相同
SELECT `sname` FROM `stu` WHERE `sname`=concat('Jaskey','abc'); -- 会使用索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE '前缀%' -- 走索引
SELECT * FROM `houdunwang` WHERE `uname` LIKE '%后缀' -- 扫描全表,不走索引
所以当需要搜索email列中.com结尾的字符串而email上希望走索引时候,可以考虑数据库存储一个反向的内容reverse_email
SELECT * FROM `table` WHERE `reverse_email` LIKE REVERSE('%.com'); -- 走索引
注:以上如果你使用REVERSE(email) = REVERSE(’%.com’),一样得不到你想要的结果,因为你在索引列email列上使用了函数,MySQL不会使用该列索引 同样的,索引列上使用正则表达式也不会走索引。
这是一个坑,假设有一张表,里面的a列是一个字符char类型,且a上建立了索引,你用它与数字类型做比较判断的话:
CREATE TABLE `t1` (`a` char(10));
SELECT * FROM `t1` WHERE `a`='1' -- 走索引
SELECT * FROM `t2` WHERE `a`=1 -- 字符串和数字比较,不走索引!
但是如果那个表那个列是一个数字类型,拿来和字符类型的做比较,则不会影响到使用索引
CREATE TABLE `t2` (`b` int);
SELECT * FROM `t2` WHERE `b`='1' -- 虽然b是数字类型,和'1'比较依然走索引
但是,无论如何,这种额外的隐式类型转换都是开销,而且由于有字符和数字比就不走索引的情况,故建议避免一切隐式类型转换
select * from dept where dname='jaskey' or loc='bj' or deptno=45
--如果条件中有or,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须建立索引
所以除非每个列都建立了索引,否则不建议使用OR,在多列OR中,可以考虑用UNION 替换
select * from dept where dname='jaskey' union
select * from dept where loc='bj' union
select * from dept where deptno=45
在ORDER BY操作中,排序的列同时也在WHERE中时,MYSQL将无法使用索引;
MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。
MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
通过索引优化来实现MySQL的ORDER BY语句优化:
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort]; 建立一个联合索引(columnX,sort)来实现order by 优化。
注意:如果columnX对应多个值,如下面语句就无法利用索引来实现order by的优化 SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10; 建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。
MySQL Order By不能使用索引来优化排序的情况 * 对不同的索引键做 ORDER BY :(key1,key2分别建立索引) SELECT * FROM t1 ORDER BY key1, key2;
在非连续的索引键部分上做 ORDER BY:(key_part1,key_part2建立联合索引;key2建立索引) SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;
同时使用了 ASC 和 DESC:(key_part1,key_part2建立联合索引) SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
用于搜索记录的索引键和做 ORDER BY 的不是同一个:(key1,key2分别建立索引) SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
如果在WHERE和ORDER BY的栏位上应用表达式(函数)时,则无法利用索引来实现order by的优化 SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引,建立复合索引。 2>在ORDER BY操作中,MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。
存在性能问题的方式
SELECT * FROM myTable ORDER BY `id` LIMIT 1000000, 30
写出这样SQL语句的人肯定心里是这样想的:MySQL数据库会直接定位到符合条件的第1000000位,然后再取30条数据。 然而,实际上MySQL不是这样工作的。
LIMIT 1000000, 30 的意思是:扫描满足条件的1000030行,扔掉前面的1000000行,然后返回最后的30行。
mysql 的 limit 给分页带来了极大的方便,但数据偏移量一大,limit 的性能就急剧下降。
以下是两条查询语句,都是取10条数据,但性能就相去甚远
所以不能简单的使用 limit 语句实现数据分页。
为什么 offset 偏大之后 limit 查找会变慢?这需要了解 limit 操作是如何运作的,以下面这句查询为例:
select * from table_name limit 10000,10
这句 SQL 的执行逻辑是 1.从数据表中读取第N条数据添加到数据集中 2.重复第一步直到 N = 10000 + 10 3.根据 offset 抛弃前面 10000 条数 4.返回剩余的 10 条数据
显然,导致这句 SQL 速度慢的问题出现在第二步!这前面的 10000 条数据完全对本次查询没有意义,但是却占据了绝大部分的查询时间!如何解决?首先我们得了解为什么数据库为什么会这样查询。
首先,数据库的数据存储并不是像我们想象中那样,按表按顺序存储数据,一方面是因为计算机存储本身就是随机读写,另一方面是因为数据的操作有很大的随机性,即使一开始数据的存储是有序的,经过一系列的增删查改之后也会变得凌乱不堪。所以数据库的数据存储是随机的,使用 B+Tree, Hash 等方式组织索引。所以当你让数据库读取第 10001 条数据的时候,数据库就只能一条一条的去查去数。
根据数据库这种查找的特性,就有了一种想当然的方法,利用自增索引(假设为id):
select * from table_name where (id >= 10000) limit 10
由于普通搜索是全表搜索,适当的添加 WHERE 条件就能把搜索从全表搜索转化为范围搜索,大大缩小搜索的范围,从而提高搜索效率。
这个优化思路就是告诉数据库:「你别数了,我告诉你,第10001条数据是这样的,你直接去拿吧。」
但是!!!你可能已经注意到了,这个查询太简单了,没有任何的附加查询条件,如果我需要一些额外的查询条件,比如我只要某个用户的数据 ,这种方法就行不通了。
可以见到这种思路是有局限性的,首先必须要有自增索引列,而且数据在逻辑上必须是连续的,其次,你还必须知道特征值。
如此苛刻的要求,在实际应用中是不可能满足的。
说起数据库查询优化,第一时间想到的就是索引,所以便有了第二次优化:先查找出需要数据的索引列(假设为 id),再通过索引列查找出需要的数据。
Select * From table_name Where id in (Select id From table_name where ( user = xxx )) limit 10000, 10;
select * from table_name where( user = xxx ) limit 10000,10
相比较结果是(500w条数据):第一条花费平均耗时约为第二条的 1/3 左右。
同样是较大的 offset,第一条的查询更为复杂,为什么性能反而得到了提升?
这涉及到 mysql 主索引的数据结构 b+Tree ,这里不展开,基本原理就是:
在数据量大的时候 in 操作的效率就不怎么样了,我们需要把 in 操作替换掉,使用 join 就是一个不错的选择
select \* from table_name inner join ( select id from table_name where (user = xxx) limit 10000,10) b using (id)
至此 limit 在查询上的优化就告一段落了。如果还有更好的优化方式,欢迎留言告知
技术上的优化始终是有天花板的,业务的优化效果往往更为显著。
比如在本例中,因为数据的时效性,我们最终决定,只提供最近15天内的操作日志,在这个前提下,偏移值 offset 基本不会超过一万,这样一来,即使是没有经过任何优化的 sql,其执行效率也变得可以接受了,所以优化不能局限于技术层面,有时候对需求进行一下调整,可能会达到意想不到的效果
目前我正在构建相当大的网络系统,我需要强大的 SQL 数据库解决方案。我选择 Mysql 而不是 Postgres,因为一些任务需要只读(MyISAM 引擎)而其他任务需要大量写入(InnoDB)。
我在 mysql 中使用如下命令。当它显示表格数据时,它被格式化为一个非常干净的表格,间距均匀且 |作为列分隔符。 SELECT * FROM TABLE_NAME; 当我从 CLI 运行命令时,如下
我知道这个问题之前已经被问过好几次了,我已经解决了很多问题,但到目前为止没有任何效果。 MySQL 试图将自身安装到的目录 (usr/local/mysql) 肯定有问题。关于我的错误的奇怪之处在于我
以下是我的 SQL 数据结构,我正在尝试如下两个查询: Select Wrk_ID, Wrk_LastName, Skill_Desc from Worker, Skill where
我们有一个本地 mysql 服务器(不在公共(public)域上),并希望将该服务器复制到我们拥有的 google 云 sql 实例。我的问题是:1.这可能吗?2.我们的本地服务器只能在本地网络上访问
我有一个表(test_table),其中一些字段值(例如字段 A、B 和 C)是从外部应用程序插入的,还有一个字段(字段 D),我想从现有表(store_table)插入其值,但在插入前者(A、B 和
我想创建一个 AWS RDS 实例,然后使用 terraform 管理数据库用户。因此,首先,我创建了一个 RDS 实例,然后使用创建的 RDS 实例初始化 mysql 提供程序,以进一步将其用于用户
当用户在我的网站上注册时,他们会在我的一个数据库中创建自己的表格。该表存储用户发布的所有帖子。我还想做的是也为他们生成自己的 MySql 用户——该用户仅有权从他们的表中读取、写入和删除。 创建它应该
我有一个关于 ColdFusion 和 Mysql 的问题。我有两个表:PRODUCT 和 PRODUCT_CAT。我想列出包含一些标记为:IS_EXTRANET=1 的特殊产品的类别。所以我写了这个
我想获取 recipes_id 列的值,以获取包含 ingredient_id 的 2,17 和 26 条目的值。 假设 ingredient_id 2 丢失则不获取记录。 我已经尝试过 IN 运算符
在 Ubuntu 中,我通常安装两者,但 MySQL 的客户端和服务器之间有什么区别。 作为奖励,当一个新语句提到它需要 MySQL 5.x 时,它是指客户端、服务器还是两者兼而有之。例如这个链接ht
我重新访问了我的数据库并注意到我有一些 INT 类型的主键。 这还不够独特,所以我想我会有一个指导。 我来自微软 sql 背景,在 ssms 中你可以 选择类型为“uniqeidentifier”并自
我的系统上有 MySQL,我正在尝试确定它是 Oracle MySQL 还是 MySQL。 Oracle MySQL 有区别吗: http://www.oracle.com/us/products/m
我是在生产 MySQL 中运行的应用程序的新维护者。之前的维护者已经离开,留下的文档很少,而且联系不上了。 我面临的问题是执行以下请求大约需要 10 秒: SELECT COUNT(*) FROM `
我有两个位于不同机器上的 MySQL 数据库。我想自动将数据从一台服务器传输到另一台服务器。比方说,我希望每天早上 4:00 进行数据传输。 可以吗?是否有任何 MySQL 内置功能可以让我们做到这一
有什么方法可以使用 jdbc 查询位于 mysql 根目录之外的目录中的 mysql 表,还是必须将它们移动到 mysql 根目录内的数据库文件夹中?我在 Google 上搜索时没有找到任何东西。 最
我在 mysql 数据库中有两个表。成员和 ClassNumbers。两个表都有一个付费年份字段,都有一个代码字段。我想用代码数字表中的值更新成员表中的付费年份,其中成员中的代码与 ClassNumb
情况:我有 2 台服务器,其中一台当前托管一个实时 WordPress 站点,我希望能够将该站点转移到另一台服务器,以防第一台服务器出现故障。传输源文件很容易;传输数据库是我需要弄清楚如何做的。两台服
Phpmyadmin 有一个功能是“复制数据库到”..有没有mysql查询来写这个函数?类似于将 db A 复制到新的 db B。 最佳答案 首先创建复制数据库: CREATE DATABASE du
我有一个使用 mySQL 作为后端的库存软件。我已经在我的计算机上对其进行了测试,并且运行良好。 当我在计算机上安装我的软件时,我必须执行以下步骤: 安装 mySQL 服务器 将用户名指定为“root
我是一名优秀的程序员,十分优秀!