MySQL高级和原理
程序员文章站
2022-06-28 17:29:06
MySQL高级字符my.cnf配置文件最后加上character_set_server=utf8修改表的字符集mysql> alter table dubbo convert to character set 'utf8';权限查看权限mysql> select * from user\G;*************************** 1. row *************************** Host: lo...
MySQL高级
字符
- my.cnf配置文件最后加上
character_set_server=utf8
- 修改表的字符集
mysql> alter table dubbo convert to character set 'utf8';
权限
查看权限
mysql> select * from user\G;
*************************** 1. row ***************************
Host: localhost
User: root
Password:
Select_priv: Y
Insert_priv: Y
Update_priv: Y
Delete_priv: Y
Create_priv: Y
Drop_priv: Y
Reload_priv: Y
Shutdown_priv: Y
Process_priv: Y
File_priv: Y
Grant_priv: Y
References_priv: Y
Index_priv: Y
Alter_priv: Y
Show_db_priv: Y
Super_priv: Y
Create_tmp_table_priv: Y
Lock_tables_priv: Y
Execute_priv: Y
Repl_slave_priv: Y
Repl_client_priv: Y
Create_view_priv: Y
Show_view_priv: Y
Create_routine_priv: Y
Alter_routine_priv: Y
Create_user_priv: Y
Event_priv: Y
Trigger_priv: Y
Create_tablespace_priv: Y
ssl_type:
ssl_cipher:
x509_issuer:
x509_subject:
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
plugin: mysql_native_password
authentication_string:
password_expired: N
创建用户权限
grant all privileges on *.*to root@'%' identified by '123';
百分号表示所有远程访问ip都允许。给所有表所有库的所有权限。
group by 注意事项
group by使用原则：select 后面只能放函数和 group by 后的字段
mysql> show variables like 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.00 sec)
如果数据库的 sql_mod 有 ONLY_FULL_GROUP_BY，必须遵守group by原则。group by使用原则：select 后面只能放函数和 group by 后的字段
MySQL架构
架构图
-
Client :
提供连接MySQL服务器功能的常用工具集 -
Server :
MySQL实例，真正提供数据存储和数据处理功能的MySQL服务器进程 -
mysqld:
MySQL服务器守护程序，在后台运行。它管理着客户端请求。mysqld是一个多线程的进程，允许多个会话连接，端口监听连接，管理MySQL实例 -
MySQL memory allocation:
MySQL的要求的内存空间是动态的，比如innodb_buffer_pool_size (from 5.7.5), key_buffer_size。每个会话都有独一无二的执行计划，我们只能共享同一会话域内的数据集。 -
SESSION
为每个客户端连接分配一个会话，动态分配和回收。用于查询处理，每个会话同时具备一个缓冲区。每个会话是作为一个线程执行的 -
Parser
检测SQL语句语法，为每条SQL语句生成SQL_ID，用户认证也发生在这个阶段 -
Optimizer
创造一个有效率的执行计划（根据具体的存储引擎）。它将会重写查询语句。比如：InnoDB有共享缓冲区，所以，优化器会首先从预先缓存的数据中提取。使用 table statistics optimizer将会为SQL查询生成一个执行计划。用户权限检查也发生在这个阶段。 -
Metadata cache
缓存对象元信息和统计信息 -
Query cache
共享在内存中的完全一样的查询语句。如果完全相同的查询在缓存命中，MySQL服务器会直接从缓存中去检索结果。缓存是会话间共享的，所以为一个客户生成的结果集也能为另一个客户所用。查询缓存基于SQL_ID。将SELECT语句写入视图就是查询缓存最好的例子。 -
key cache
缓存表索引。MySQL keys是索引。如果索引数据量小，它将缓存索引结构和叶子节点（存储索引数据）。如果索引很大，它只会缓存索引结构，通常供MyISAM存储引擎使用
查看执行计划
设置 profiling
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.01 sec)
mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%profiling%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00206750 | show variables like '%profiling%' |
+----------+------------+-----------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000106 | 0.000034 | 0.000058 | 0 | 0 |
| checking permissions | 0.000060 | 0.000023 | 0.000037 | 0 | 0 |
| Opening tables | 0.000076 | 0.000028 | 0.000048 | 0 | 0 |
| init | 0.000041 | 0.000015 | 0.000026 | 0 | 0 |
| System lock | 0.000050 | 0.000019 | 0.000031 | 0 | 0 |
| optimizing | 0.000038 | 0.000016 | 0.000027 | 0 | 0 |
| statistics | 0.000583 | 0.000000 | 0.000579 | 0 | 0 |
| preparing | 0.000045 | 0.000000 | 0.000044 | 0 | 0 |
| executing | 0.000498 | 0.000000 | 0.000499 | 0 | 0 |
| Sending data | 0.000211 | 0.000210 | 0.000000 | 0 | 0 |
| end | 0.000035 | 0.000034 | 0.000000 | 0 | 0 |
| query end | 0.000049 | 0.000050 | 0.000000 | 0 | 0 |
| closing tables | 0.000035 | 0.000034 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000037 | 0.000037 | 0.000000 | 0 | 0 |
| closing tables | 0.000034 | 0.000034 | 0.000000 | 0 | 0 |
| freeing items | 0.000099 | 0.000099 | 0.000000 | 0 | 0 |
| cleaning up | 0.000071 | 0.000071 | 0.000000 | 0 | 0 |
+----------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
对比前后两次查询sql执行周期：
mysql> select * from Scores;
// 结果省略
10 rows in set (0.01 sec)
mysql> select * from Scores;
//结果省略
10 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+-----------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------+
| 1 | 0.00206750 | show variables like '%profiling%' |
| 2 | 0.00197800 | show variables like '%profiling%' |
| 3 | 0.00091250 | select * from Scores |
| 4 | 0.00170050 | SELECT DATABASE() |
| 5 | 0.00155175 | show databases |
| 6 | 0.00076800 | show tables |
| 7 | 0.00330450 | select * from Scores |
| 8 | 0.00066800 | select * from Scores |
+----------+------------+-----------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 7;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000065 | 0.000018 | 0.000042 | 0 | 0 |
| Waiting for query cache lock | 0.000032 | 0.000009 | 0.000022 | 0 | 0 |
| init | 0.000032 | 0.000009 | 0.000023 | 0 | 0 |
| checking query cache for query | 0.000072 | 0.000022 | 0.000051 | 0 | 0 |
| checking permissions | 0.000036 | 0.000010 | 0.000024 | 0 | 0 |
| Opening tables | 0.000227 | 0.000230 | 0.000000 | 0 | 0 |
| init | 0.000069 | 0.000075 | 0.000000 | 0 | 0 |
| System lock | 0.000133 | 0.000126 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000036 | 0.000033 | 0.000000 | 0 | 0 |
| System lock | 0.000054 | 0.000063 | 0.000000 | 0 | 0 |
| optimizing | 0.000048 | 0.000039 | 0.000000 | 0 | 0 |
| statistics | 0.000049 | 0.000054 | 0.000000 | 0 | 0 |
| preparing | 0.000054 | 0.000049 | 0.000000 | 0 | 0 |
| executing | 0.000034 | 0.000162 | 0.000000 | 0 | 0 |
| Sending data | 0.001851 | 0.001726 | 0.000000 | 0 | 0 |
| end | 0.000044 | 0.000040 | 0.000000 | 0 | 0 |
| query end | 0.000040 | 0.000039 | 0.000000 | 0 | 0 |
| closing tables | 0.000072 | 0.000072 | 0.000000 | 0 | 0 |
| freeing items | 0.000052 | 0.000052 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000032 | 0.000031 | 0.000000 | 0 | 0 |
| freeing items | 0.000105 | 0.000105 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000032 | 0.000032 | 0.000000 | 0 | 0 |
| freeing items | 0.000031 | 0.000031 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000033 | 0.000033 | 0.000000 | 0 | 0 |
| cleaning up | 0.000076 | 0.000076 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
25 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 8;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000068 | 0.000019 | 0.000045 | 0 | 0 |
| Waiting for query cache lock | 0.000037 | 0.000026 | 0.000061 | 0 | 0 |
| init | 0.000315 | 0.000000 | 0.000267 | 0 | 0 |
| checking query cache for query | 0.000045 | 0.000000 | 0.000040 | 0 | 0 |
| checking privileges on cached | 0.000033 | 0.000000 | 0.000033 | 0 | 0 |
| checking permissions | 0.000047 | 0.000000 | 0.000049 | 0 | 0 |
| sending cached result to clien | 0.000077 | 0.000000 | 0.000074 | 0 | 0 |
| cleaning up | 0.000048 | 0.000000 | 0.000048 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
8 rows in set, 1 warning (0.00 sec)
上述能证明命中了缓存。命中缓行的条件得是：SQL语句一摸一样。
引擎
外键：数据多的时候，初始化数据很麻烦（数据错误，很难批量插入数据）
使用 show engines; 来查询所有的引擎
| 对比项 | MyISAM | InnoDB |
|---|---|---|
| 外键 | 不支持 | 支持 |
| 事务 | 不支持 | 支持 |
| 行表锁 | 表锁，不适合高并发 | 行锁，适合高并发 |
| 缓存 | 只缓存索引，不缓存数据 | 不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性影响 |
| 关注 | 节省资源 | 并发写，事务，更大资源 |
| 系统自带表 | 使用 | 不使用 |
-
Archive 引擎，适合日志和数据采集，比innodb小83%
-
Memory引擎，一般用redis替代
索引
join 复习
- 数据过多 - 分库分表
- 关联太多表，太多 join - sql优化
- 没有充分利用索引 - 索引建立
- 服务器参数调优 - 调整my.cnf
左连接 where后加 B.key is not null 能选出差集
select * from A a left join B b on a.id = b.id where b.id is not null
MySQL单表极限500w
索引是啥
是帮助mysql高级获取数据的数据结构，排好序的快速查找数据结构。一般来说索引也很大，会以索引文件的形式放在存储的磁盘上。增删改也会修改索引，导致开销变大。
索引分类
- 单值索引：一个索引只包含单个列
- 唯一索引：索引列的值必须唯一，但是允许有空
- 复合索引：一个索引包含多个列
Mysql 索引结构
- BTree索引：传统二叉树，每个节点（两个数据和三个数据指针）为一个磁盘块，每个节点的读取就是一次 IO，所以查询 IO 次数 = 树的高度。因此B+树以更短的层数，减少IO次数。IO次数是最耗时的。
- Hash索引
- full-text全文索引
- R-Tree索引
索引优势
提高检索效率，降低数据库 IO 成本，通过索引对数据库数据进行排序，降低CPU负载，索引本身也是一张表，KV存储。
二叉树正常情况下是平衡二叉树，但是如果极端情况下（一直比现在节点大，导致一直插入再右节点下，会退化成单链表）B-Tree 平衡树，会自动平衡，避免上述情况，每个树节点包含数据在磁盘中的位置，还有三个向下的指针。（B+树，本身不存储指向数据的指针，叶子节点才会包含指向磁盘数据的指针。由于B+树没有带上指向数据的指针，所以它每个节点内存占用更少，同等内存可以获取到更多的B+树节点，能更快比较出磁盘位置。B树的缺页率会更高，增加额外的 IO 开销） 这段话我觉得不好，下面更好：（传统二叉树，每个节点（两个数据和三个数据指针）为一个磁盘块，每个节点的读取就是一次 IO，所以查询 IO 次数 = 树的高度。因此B+树以更短的层数，减少IO次数。IO次数是最耗时的）
聚簇索引 & 非聚簇索引
- 聚簇索引：数据和索引在一起。索引本身能范围查找，比如找到 1 的索引和 5 的索引，那么直接能定位到 2 - 4 的索引。主键索引是 聚簇索引，其他都是 非聚簇索引。
- 非聚簇索引：即使找到 1 和 5 的索引，还是不能找到 2 - 4 的索引。
mysql> show index from major \G;
*************************** 1. row ***************************
Table: major
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
mysql> create index idx_Name on Socres(Name);
ERROR 1146 (42S02): Table 'demo.Socres' doesn't exist
mysql> create index idx_Name on Scores(Name);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from Scores \G;
*************************** 1. row ***************************
Table: Scores
Non_unique: 1
Key_name: idx_Name
Seq_in_index: 1
Column_name: Name
Collation: A
Cardinality: 8
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
// 还可以创建组合索引，性价比更高
mysql> create index idx on Person(Id_P, LastName, Address);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
适合建索引：
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该建立索引
- 查询中与其他表关联的字段，外键关系建立索引
- 组合索引性价比更高
- 查询中排序的字段，排序字段如果通过索引去访问提高排序速度
- 查询中统计或者分组字段（group by 包含了一个 order by）
不适合建索引：
- 表记录太少
- 经常增删改的字段
- Where里面用不到的字段
- 过滤性不好的字段
Explain
mysql> explain select * from (select * from Person as P) as P;
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
| 2 | DERIVED | P | ALL | NULL | NULL | NULL | NULL | 5 | NULL |
+----+-------------+------------+------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
id相同，执行顺序自上到下，id不同的时候，先执行大的id。id号每个号码，表示一趟独立的查询，一个sql的查询趟数越少越好。
- simple：简单的select查询，没有子查询或union
- primary：主查询，含有子查询
- derived：衍生子查询
- subquery：子查询
- uncacheable query：不可用缓存子查询
- union: select 放在 union后面： select * from table union select * from table2
Type: 表明了查询类型，syetem > const > eq_ref > ref > range > index > ALL
-
system：表里只有一行记录
-
eq_ref：唯一性索引扫描，对于每个索引键，只有一条数据与之匹配
-
ref：非唯一性索引扫描，返回匹配的多行值
-
range：给定范围查找
-
index：Full index Scan；（全索引扫描，通常比数据文件小，比全表扫描 IO 少）sql使用了索引，但是没有通过索引进行过滤，一般使用了覆盖索引或者利用索引进行排序分组
-
all：全表扫描
mysql> explain select * from Scores where name = 'Bob';
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------+
| 1 | SIMPLE | Scores | ref | idx_Name | idx_Name | 258 | const | 3 | Using index condition |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
key：表明实际使用的索引，如果null表示没用，查询中若使用了覆盖索引，则该索引和查询的select字段重叠。多个索引竞争的情况下，最后用到的索引。若使用了覆盖索引，仅会在 key 中存在，不在 possible_keys中。覆盖索引：select 查询的字段刚好和建立索引的字段一一吻合 (select col1, col2, col3 from table)，也就是说MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取文件，换句话说，查询列被所建的索引覆盖。
possible_keys：可能这张表用到的索引，可能不一定被实际使用到。
key_len：用于表示本次查询中,所选择的索引长度有多少字节,通常我们可借此判断联合索引有多少列被选择了。字符 * 字符集系数。数值越大越精确，where后面的判断更多。
ref：显示索引的哪一列被使用了，(where d.id = b.id)，会用到 d.id 和 b.id
rows：是物理扫描行数，越少越好
Extra：Using filesort：没用到索引，使用外部的索引排序，而不是按照表内的索引顺序读取，但凡出现它查询效果很慢。要你命3k。
Extra：Using temporary：使用临时表来排序，group by没有用到索引（group by最好使用索引）。临时表很费性能。要你命3w。
Extra：Using index：使用了覆盖索引在select语句中，效率不错。如果同时出现 using where，表明索引被用来执行索引键值的查找。如果没有出现 using where，表明直接用索引读取数据而非执行查找动作。
Extra：Using join buffer：使用了连接缓存，关联字段如果没建立索引，会导致速度很慢。
Extra：Using where：全表扫描后进行了过滤。
Extra：impossible where：可能写错了sql语句。
Extra：select tables optimized away：在没有group by字句的情况下，基于索引min/max函数，查询执行计划生成的阶段即完成优化。
插入100w行数据
不建议看，只是个思路罢了。
使用SQL编程
手写，mysql为了主从复制可靠，不允许用户手写函数
mysql> show variables like 'log_bin_trust_function_creators';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
需要把它开启，然后写sql批量插入。
修改结束符号 为 $$。关闭自动提交。
单表索引优化
索引失效
- 全值匹配。查询列全在索引上且顺序一样。复合索引，如果第一个索引没被使用，后面两个索引就失效了。
- 最佳左前缀法则，从左到右的索引都被用到且没被略过，没被跳过。（带头大哥不能死，中间兄弟不能断）
- 不要再索引上做任何操作（计算，函数，类型转换），会导致索引失效转向全表扫描。例如：
- 存储引擎不能使用索引中范围条件右边的列。（中间兄弟如果用到了范围，而不是 = ，会导致后一个索引失效）
- 尽量使用覆盖索引，少用 *
- mysql使用不等于的时候（!= 或者 <>）的时候无法使用索引会导致全表扫描。
- is null, is not null：不知道哪里是空或者非空，导致全表扫描。
- like ‘%July%’ 以通配符开头，mysql索引会失效，造成全表扫描。可以通配符加在右边，例如： ‘July%’。如何解决 like 以通配符开头失效的问题？“使用覆盖索引来解决！”
- 少用 or 也会导致索引失效。
- 字符串不加单引号导致索引失效，这里其实对应的是在索引列上类型转换，数字转varchar，导致索引失效。
order by
如果复合索引是c1,c2,c3建立，那么如果order by c3, c2会导致索引失效，引起 filesort。
举个例子：
select * from table where c1='a' and c2='b' order by c3, c2;
select * from table where c1='a' order by c3, c2;
这种情况下由于第一句用到了 c2 索引，所以索引不会失效；但是下一句没用到 c2 索引，直接用了 c3 索引，导致索引失效。
排序顺序和索引顺序一致才能保证索引不失效。order by 默认升序，升序降序的时候，是会导致filesort的。
优化：
- 少用 select * 和 order by使用
group by
group by 基本都需要 order by，所以会出现order by的现象
select * from xxx where a = 3 and b like 'k%kk%' and c = 4;
上述语句用到了三个索引，abc都用到了索引
SQL优化
mysql开启满日志查询，比如超过5秒钟就是慢SQL。
- explain 观察
- show profile 观察
“小表驱动大表”，小的数据集驱动大的数据集。
select * from A where id in (select id from B);
默认 AB都建立索引
当B表的数据集必须小于A表的数据集时，使用 in 优于 exists。
当A表的数据集小于B表的数据集时，用 exists 优于 in。
MySQL锁
- 读锁：共享锁
- 写锁：排它锁
show open tables;
lock table tb1 read;
unlock tables;
加了读锁了，本session不能读取其他没加锁的表。
- 表锁：偏读
- 行锁：偏写
加了写锁，本session不能写其他没加锁的表，其他session读取只能阻塞
无索引行锁升级为表锁！！！ varchar类型不加""，会导致索引失效，自动类型转化，导致升级成表锁。
间隙锁危害
当用范围条件修改数据的时候，mysql会给出间隙锁（GAP），锁定给定范围内的所有键值，导致无法插入不存在但是属于这个范围内的数据。导致性能变慢。
如何锁定一行
select * from table1 where a = 'a' for update;
这里人为锁定当前行。其他session只能读取没法操作数据。
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 | //正在等待锁定的数量
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 | //等待平均时间
| Innodb_row_lock_time_max | 0 | //等待最大时间
| Innodb_row_lock_waits | 0 | //等待总次数
+-------------------------------+-------+
5 rows in set (0.00 sec)
主从复制
原理：
slave 会从 master 读取 binlog 来进行数据同步
- master把数据改变写入 binlog 中，成为 binary log event
- slave IO 线程读 master Binary log，并拷贝到它的中继日志（relay log）
- slave 重做 中继日志的事件，改变自己的数据库，MySQL复制是异步的且串行化
每个slave 只有一个master，每个slave只有一个服务 ID，一个master有多个slave。
配置主机参数，允许从机拉去数据，从机做相应配置。通过show master status获取文件和偏移量来获取数据。必须保证下面两个参数Yes,才保证主从设置成功：
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
本文地址：https://blog.csdn.net/weixin_40037938/article/details/112599246
下一篇： linux下free命令详解