除了增删改查你对MySQL还了解多少？

目录

• 除了增删改查你对MySQL还了解多少？
  • MySQL授权远程连接
    • 创建用户、授权
  • 客户端与服务器连接的过程
    • TCP/IP
    • 命名管道和共享内存
    • Unix域套接字文件
  • 查询优化
  • MySQL中走与不走索引的情况汇总
    • 索引列参与计算
    • 索引列使用了函数
    • 索引列使用了Like %XXX
    • 字符串列与数字直接比较
    • 尽量避免 OR 操作
    • ORDER BY 操作
      • 1、ORDER BY的索引优化。如果一个SQL语句形如：
      • 2、WHERE + ORDER BY的索引优化，形如：
      • 3、WHERE+ 多个字段ORDER BY
      • 特别提示:
    • Offset Limit 操作
      • 探究
      • 第一次优化
      • 第二次优化
      • 第三次优化
      • 最终优化

除了增删改查你对MySQL还了解多少？

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的通信方式？？是什么？？？

TCP/IP

在我们实际使用数据库的过程中，大概率服务器和客户端不会在一台机器上，那么他们之间就得通过网络来通信，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主机中
• 命名管道和共享内存是Windows操作系统中的两种进程间通信方式

Unix域套接字文件

使用此连接方式的前提是服务器和客户端进程都在同一类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语句来设置执行计划；

---

MySQL中走与不走索引的情况汇总

转自：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');      -- 会使用索引

索引列使用了Like %XXX

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'比较依然走索引

但是，无论如何，这种额外的隐式类型转换都是开销，而且由于有字符和数字比就不走索引的情况，故建议避免一切隐式类型转换

尽量避免 OR 操作

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 操作

在ORDER BY操作中，排序的列同时也在WHERE中时，MYSQL将无法使用索引；

MySQL索引通常是被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。

MySQL也能利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。

通过索引优化来实现MySQL的ORDER BY语句优化：

1、ORDER BY的索引优化。如果一个SQL语句形如：

SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort]; 在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。

2、WHERE + 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];

3、WHERE+ 多个字段ORDER BY

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只有在排序条件不是一个查询条件表达式的情况下才使用索引。

Offset Limit 操作

存在性能问题的方式

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 ，这里不展开，基本原理就是：

• 子查询只用到了索引列，没有取实际的数据，所以不涉及到磁盘IO，所以即使是比较大的 offset 查询速度也不会太差。
• 利用子查询的方式，把原来的基于 user 的搜索转化为基于主键（id）的搜索，主查询因为已经获得了准确的索引值，所以查询过程也相对较快。

第三次优化

在数据量大的时候 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，其执行效率也变得可以接受了，所以优化不能局限于技术层面，有时候对需求进行一下调整，可能会达到意想不到的效果

---

我的博客即将同步至腾讯云+社区，邀请大家一同入驻：https://cloud.tencent.com/developer/support-plan?invite_code=2f9zoz2qtq800