mysql多实例
时间：2021-07-01 10:21:17
帮助过：5人阅读
数据库系列课程
2 MySQL
3 1.版本介绍和选择
4 2.安装
5 3.体系结构
6 4.SQL
7 5.索引及执行计划
8 6.存储引擎
9 7.日志
10 8.备份恢复
11 9.主从
12 10.高可用架构
13 11.高性能架构
14
15 Redis
16 1.安装配置
17 2.数据类型
18 3.事务
19 4.持久化
20 5.高可用架构
21 6.分布式
22 7.API
23
24 MongoDB
25 1.安装配置
26 2.用户管理
27 3.CRUD
28 4.Replication set复制集
29 5.Sharding Cluster 分布式分片集群
30
31 Oracle课程(看时间)
32
33 =================================
34 1.MySQL 版本
35
36 Oracle MySQL 8.0
37 MariaDB
38 PerconaDB
39
40 主流版本
41 mysql 5.6 5.6.36 5.38 5.6.40
42 mysql 5.7 5.7.18 5.7.20 5.7.22
43
44 企业版本选择: 6-12月之间的GA
45
46 2.MySQL的体系结构
47 2.1 MySQL C/S结构
48 两种链接方法: TCP/IP (远程,本地),SOCKET(本地)
49
50 mysql -uroot -poldboy123 -h 10.0.0.200 -P3306
51 mysql -uroot -poldboy123 -S /tmp/mysql.sock
52
53 2.2 MySQL实例
54 实例=mysqld+内存结构
55 MySQL实例======> mysqld(董事长)---->master thread(经理)---->N Thread(员工) ------>内存结构(办公区)
56
57 2.3 mysqld三层结构
58
59 连接层
60 1.提供连接协议(TCP ,Socket)
61 2.用户验证
62 3.提供专用链接线程
63
64 SQL层
65 1.接收上层的命令
66 2.语法检测
67 3.语义(SQL类型),权限
68 SQL类型:DDL数据定义语言 DCL数据控制语言 DML数据操作语言 DQL数据查询
69 4.专用解析器解析SQL,解析成多种执行计划
70 5.优化器:帮我们选择一个代价最低的执行计划(cpu,IO,MEM)
71 6.执行器:按照优化器的选择,执行SQL语句,得出获取数据方法
72 7.查询缓存:默认是关闭的. 一般会使用redis产品替代 Tair
73 8.记录日志:二进制日志
74
75 存储引擎层
76 按照SQL层结论,找相应数据,结构化成表的形式
77
78
79 3. MySQL的逻辑结构
80
81 库(schema):存储表的地方(库名,属性)
82 表(Table):二维表
83
84 元数据
85 表名字
86 表的属性(表的大小,权限,存储引擎,字符集等)
87 列:列名字,列属性(数据类型,约束,其他定义)
88 ==============================================
89 记录:数据行
90 ==============================================
91 4.SQL 语句(SQL92)
92 4.1 SQL 种类
93 DDL数据定义语言
94 DCL数据控制语言
95 DML数据操作语言
96 DQL数据查询语言
97
98 4.2 SQL语句的操作对象
99 库
100 表
101
102 4.3 不同分类语句作用
103
104 DDL :
105 库
106 CREATE DATABASE
107 DROP DATABASE
108 ALTER DATABSE
109
110 SQL语句规范第一条:
111 CREATE DATABASE oldboy CHARSET utf8mb4;
112 1.关键字大写(非必须),字面量小写(必须)
113 2.库名字,只能是小写,不能有数字开头,不能是预留关键字
114 3.库名字必须和业务名有关,例如his_user;
115 4.必须加字符集.
116
117 表
118 CREATE TABLE
119 DROP TABLE
120 ALTER TABLE
121
122 CREATE TABLE t1 (
123 id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘用户ID‘,
124 sname VARCHAR(20) NOT NULL COMMENT ‘用户姓名‘,
125 gender ENUM(‘f‘,‘m‘,‘u‘) NOT NULL DEFAULT ‘u‘ COMMENT ‘用户性别‘,
126 telnum CHAR(11) NOT NULL UNIQUE COMMENT ‘手机号‘,
127 tmdate DATETIME NOT NULL DEFAULT NOW() COMMENT ‘录入时间‘
128 )ENGINE INNODB CHARSET utf8mb4;
129
130 SQL语句规范第二条:
131 1.关键字大写(非必须),字面量小写(必须)
132 2.表名必须小写,不能有数字开头,不能是预留关键字
133 3.表名字必须和业务名有关
134 4.必须加存储引擎和字符集
135 5.适合的数据类型
136 6.必须要有主键
137 7.尽量非空选项
138 8.字段唯一性
139 9.必须加注释
140 10.避免使用外键
141 11.建立合理的索引
142
143 DCL:
144 grant
145 revoke
146 lock
147
148 DML :
149 insert
150 update
151 delete
152
153 SQL语句规范第三条:
154 1.insert语句按批量插入数据
155 2.update必须加where条件
156 3.delete尽量替换为update
157 4.如果有清空全表需求,不要用delete,推荐使用truncate
158
159 DQL :
160 select
161 show
162
163 SQL语句规范第四条:
164 1. select语句避免使用 select * from t1; ----> select id,name from t1;
165 2. select语句尽量加等值的where条件.例如 select * from t1 where id=20;
166 3. select 语句对于范围查询,例如 ;select * from t1 where id>200; 尽量添加limit或者 id>200 and id
<300 union all id>300 and id
<400
167 4. select 的where 条件 不要使用 <> like ‘%name‘ not in not exist
168 5. 不要出现3表以上的表连接,避免子查询
169 6. where条件中不要出现函数操作.
170
171
172 ===============
173 5. MySQL 5.7 初始化配置
174
175 5.1 初始化数据:
176 /usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql --datadir=/opt/mysql/data --basedir=/opt/mysql
177
178 5.2 配置文件
179 vim /etc/my.cnf
180 [mysqld]
181 basedir=/usr/local/mysql
182 datadir=/usr/local/mysql/mydata
183 socket=/tmp/mysql.sock
184 log_error=/var/log/mysql.log
185 user=mysql
186 port=6606
187 [mysql]
188 socket=/tmp/mysql.sock
189
190
191 作用:
192 1.影响服务端的启动
193 标签: [mysqld] [mysqld_safe] [server] ...
194 [mysqld]
195 basedir=/opt/mysql
196 datadir=/opt/mysql/data
197 user=mysql
198 socket=/tmp/mysql.sock
199 port=3306
200 server_id=6
201
202 2.影响客户端连接
203 标签: [client] [mysql] [mysqldump] ....
204 [mysql]
205 socket=/tmp/mysql.sock
206
207
208
209 =======================
210 5.3 多实例(3307 3308 3309)
211
212 5.3.1 创建相关目录
213 mkdir -p /data/330{7..9}/data
214
215 5.3.2 创建配置文件
216 cat>> /data/3307/my.cnf
<<EOF
217 [mysqld]
218 basedir=/opt/mysql
219 datadir=/data/3307/data
220 user=mysql
221 socket=/data/3307/mysql.sock
222 port=3307
223 server_id=3307
224 EOF
225
226 cp /data/3307/my.cnf /data/3308
227 cp /data/3307/my.cnf /data/3309
228
229 sed -i ‘s#3307#3308#g‘ /data/3308/my.cnf
230 sed -i ‘s#3307#3309#g‘ /data/3309/my.cnf
231
232
233 5.3.3 初始化数据
234 mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/opt/mysql
235 mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/opt/mysql
236 mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/opt/mysql
237
238
239 5.3.4 启动多实例
240 chown -R mysql.mysql /data/*
241 mysqld_safe --defaults-file=/data/3307/my.cnf &
242 mysqld_safe --defaults-file=/data/3308/my.cnf &
243 mysqld_safe --defaults-file=/data/3309/my.cnf &
244
245
246 5.3.5 测试
247 netstat -lnp|grep 330
248
249 mysql -S /data/3307/mysql.sock
250 mysql -S /data/3308/mysql.sock
251 mysql -S /data/3309/mysql.sock
252
253 5.3.6 systemd管理多实例
254
255 cat >> /etc/systemd/system/mysqld3307.service
<<EOF
256 [Unit]
257 Description=MySQL Server
258 Documentation=man:mysqld(8)
259 Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
260 After=network.target
261 After=syslog.target
262 [Install]
263 WantedBy=multi-user.target
264 [Service]
265 User=mysql
266 Group=mysql
267 ExecStart=/opt/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
268 LimitNOFILE = 5000
269 EOF
270 cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3308.service
271 cp /etc/systemd/system/mysqld3307.service /etc/systemd/system/mysqld3309.service
272 sed -i ‘s#3307#3308#g‘ /etc/systemd/system/mysqld3308.service
273 sed -i ‘s#3307#3309#g‘ /etc/systemd/system/mysqld3309.service
274
275
276 systemctl start mysqld3307
277 systemctl start mysqld3308
278 systemctl start mysqld3309
279 netstat -lnp|grep 330
280 systemctl stop mysqld3309
281 systemctl stop mysqld3308
282 systemctl stop mysqld3307
283 systemctl enable mysqld3307
284 systemctl enable mysqld3308
285 systemctl enable mysqld3309
286
287
288 6.忘记密码处理
289
290 mysqladmin -uroot -p password 123
291
292 select user,authentication_string,host from mysql.user;
293
294 1.停数据库
295 /etc/init.d/mysqld stop
296 2.启动数据库为无密码验证模式
297 mysqld_safe --skip-grant-tables --skip-networking &
298 update mysql.user set authentication_string=PASSWORD(‘456‘) where user=‘root‘ and host=‘localhost‘;
299 /etc/init.d/mysqld restart
300
301 [root@standby ~]# mysql -uroot -p123
302 [root@standby ~]# mysql -uroot -p456
303
304
305
306 7.数据类型和字符集
307 整型
308 int 最多存10位数字
309 -2^31 ~ 2^31-1
310 2^32 10位数 11
311 浮点
312
313 字符串类型
314 char 定长,存储数据效率较高,对于变化较多的字段,空间浪费较多
315 varchar 变长,存储时判断长度,存储会有额外开销,按需分配存储空间.
316 enum
317 时间
318 datetime
319 timestamp
320 date
321 time
322
323 SQL语句规范第五条:
324 1.少于10位的数字int ,大于10位数 char,例如手机号
325 2.char和varchar选择时,字符长度一定不变的可以使用char,可变的尽量使用varchar
326 在可变长度的存储时,将来使用不同的数据类型,对于索引树的高度是有影响的.
327 3.选择合适的数据类型
328 4.合适长度
329
330
331 8.索引及执行计划
332
333 8.1 索引
334 作用: 优化查询,select 查询有三种情况:缓存查询(不在mysql中进行数据查询),全表扫描,索引扫描
335
336 8.2 索引种类
337 Btree(btree b+tree b*tree)
338 Rtree
339 HASH
340 FullText
341
342 8.3 Btree 分类
343 聚集索引:基于主键,自动生成的,一般是建表时创建主键.如果没有主键,自动选择唯一键做为聚集索引.
344 辅助索引:人为创建的(普通,覆盖)
345 唯一索引:人为创建(普通索引,聚集索引)
346
347 聚集索引和辅助索引的对比
348 1.聚集索引:叶子结点,按照主键列的顺序,存储的整行数据,就是真正的数据页
349 2.辅助索引: 叶子结点,列值排序之后,存储到叶子结点+对应的主键的值,便于回表查询
350
351
352
353 8.4 索引管理命令
354 8.4.1 索引键(key),表中的某个列
355
356 辅助索引(BTREE)
357 怎么生成的:
358 根据创建索引时,指定的列的值,进行排序后,存储的叶子节点中
359 好处:
360 1.优化了查询,减少cpu mem IO消耗
361 2.减少的文件排序
362
363
364 创建普通辅助索引(MUL)
365 alter table blog_userinfo add key idx_email(email);
366 create index idx_phone on blog_userinfo(phone);
367 查看索引
368 desc blog_userinfo;
369 show index from blog_userinfo;
370 删除索引
371 alter table blog_userinfo drop index idx_email;
372 drop index idx_phone on blog_userinfo;
373
374 前缀索引
375 select count(*),substring(password,1,20) as sbp from blog_userinfo group by sbp;
376 alter table blog_userinfo add index idx(password(10));
377
378
379 唯一键索引(UNI,如果有重复值是创建不了的)
380 alter table blog_userinfo add unique key uni_email(email);
381
382 覆盖索引(联合索引)
383 作用:不需要回表查询,不需要聚集索引,所有查询的数据都从辅助索引中获取
384
385 select * from people where gender , age , money
386 a,b,c
387
388 where a b c
389 where a b
390
391 alter table t1 add index idx_gam(gender,age,money);
392
393 a b c
394
395 where b c a
396 where c a b
397 where c
398 where b
399
400
401 好处:
402 减少回表查询的几率
403 ====================================================
404
405 9. explain(desc)命令的应用
406 获取优化器选择后的执行计划
407
408 oldguo [world]>explain select * from city where countrycode=‘CHN‘\G
409 *************************** 1. row ***************************
410 id: 1
411 select_type: SIMPLE
412 table: city
413 type: ref
414 possible_keys: CountryCode,idx_co_po
415 key: CountryCode
416 key_len: 3
417 ref: const
418 rows: 1
419 Extra: Using index condition
420 1 row in set (0.00 sec)
421
422
423 8.2 重要的字段
424
425 8.2.1 type: 查询类型
426 作用:
427 1. 可以判断出,全表扫描还是索引扫描(ALL就是全表扫描,其他的就是索引扫描)
428 2. 对于索引扫描来讲,又可以细划分,可以判断是哪一种类的索引扫描
429 type的具体类型介绍:
430 ALL:全表扫描
431 select * from t1;
432 Index:全索引扫描
433 例子:
434 desc select countrycode from city ;
435
436 range:索引范围扫描
437 where >
< >=
<=
438 in or between and
439 like ‘CH%‘
440
441 in 或者 or 改写成 union
442 select * from city where countrycode=‘CHN‘
443 union all
444 select * from city where countrycode=‘USA‘;
445
446 ref:辅助索引的等值查询
447 select * from city where countrycode=‘CHN‘
448
449 eq_ref: 多表链接查询(join on )
450
451 const ,system :主键或唯一键等值查询
452
453 Extra:
454 using filesort: 文件排序
455 将order by group by distinct 后的列和where条件列建立联合索引
456
457
458
459 possible_keys: CountryCode,idx_co_po ---->可能会走的索引
460 key: CountryCode ---->真正走的索引
461 type: ref ---->索引类型
462 Extra: Using index condition ---->额外信息
463
464 --------------------------------------
465 建立索引的原则（运维规范）
466
467 一、数据库索引的设计原则：
468
469 为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
470 那么索引设计原则又是怎样的?
471
472 0.建表时一定要有主键,如果相关列可以作为主键,做一个无关列
473
474 1．选择唯一性索引
475 唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
476 例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
477 如果使用姓名的话，可能存在同名现象，从而降低查询速度。
478
479 主键索引和唯一键索引，在查询中使用是效率最高的。
480
481 select count(*) from world.city;
482 select count(distinct countrycode) from world.city;
483 select count(distinct countrycode,population ) from world.city;
484
485 注意：如果重复值较多，可以考虑采用联合索引
486
487
488 2．为经常需要排序、分组和联合操作的字段建立索引
489 经常需要ORDER BY、GROUP BY,join on等操作的字段，排序操作会浪费很多时间。
490 如果为其建立索引，可以有效地避免排序操作。
491
492 3．为常作为where查询条件的字段建立索引
493 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，
494 为这样的字段建立索引，可以提高整个表的查询速度。
495 3.1 经常查询
496 3.2 列值的重复值少(业务层面调整)
497
498 注：如果经常作为条件的列，重复值特别多，可以建立联合索引。
499
500
501 4．尽量使用前缀来索引
502 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索
503 会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
504
505
506
507 ------------------------以上的是重点关注的，以下是能保证则保证的--------------------
508
509 5．限制索引的数目
510 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
511 修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
512
513 6．删除不再使用或者很少使用的索引(percona toolkit)
514 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理
515 员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。
516
517 7.大表加索引,要在业务不繁忙期间操作
518
519 建索引原则
520 (1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
521 (2) 经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
522 (3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
523 (4) 列值长度较长的索引列,我们建议使用前缀索引.
524 (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
525 (6) 索引维护要避开业务繁忙期
526
527 业务:
528 1.产品的功能
529 2.用户的行为
530
531 ===============================================
532
533 不走索引的情况（开发规范）
534
535 重点关注：
536 1) 没有查询条件，或者查询条件没有建立索引
537
538 select * from tab; 全表扫描。
539 select * from tab where 1=1;
540
541 在业务数据库中，特别是数据量比较大的表。
542 是没有全表扫描这种需求。
543
544 1、对用户查看是非常痛苦的。
545 2、对服务器来讲毁灭性的。
546
547 （1）select * from tab;
548
549 SQL改写成以下语句：
550 selec * from tab order by price limit 10 需要在price列上建立索引
551
552
553 （2）
554 select * from tab where name=‘zhangsan‘ name列没有索引
555
556 改：
557 1、换成有索引的列作为查询条件
558 2、将name列建立索引
559
560
561
562
563 2) 查询结果集是原表中的大部分数据，应该是25％以上。
564
565 查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。
566
567 假如：tab表 id，name id:1-100w ，id列有索引
568
569 select * from tab where id>500000;
570
571 如果业务允许，可以使用limit控制。
572
573 怎么改写 ？
574 结合业务判断，有没有更好的方式。如果没有更好的改写方案
575 尽量不要在mysql存放这个数据了。放到redis里面。
576
577
578 3) 索引本身失效，统计数据不真实
579 索引有自我维护的能力。
580 对于表内容变化比较频繁的情况下，有可能会出现索引失效。
581
582
583 4) 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等)
584 例子：
585 错误的例子：select * from test where id-1=9;
586 正确的例子：select * from test where id=10;
587
588 算术运算
589 函数运算
590 desc select * from blog_userinfo where DATE_FORMAT(last_login,‘%Y-%m-%d‘) >= ‘2019-01-01‘;
591
592 子查询
593
594 5)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
595
596 select * from t1 where telnum=110;
597
598
599 这样会导致索引失效. 错误的例子：
600 ------------------------
601 mysql> alter table tab add index inx_tel(telnum);
602 Query OK, 0 rows affected (0.03 sec)
603 Records: 0 Duplicates: 0 Warnings: 0
604
605 mysql>
606 mysql> desc tab;
607 +--------+-------------+------+-----+---------+-------+
608 | Field | Type | Null | Key | Default | Extra |
609 +--------+-------------+------+-----+---------+-------+
610 | id | int(11) | YES | | NULL | |
611 | name | varchar(20) | YES | | NULL | |
612 | telnum | varchar(20) | YES | MUL | NULL | |
613 +--------+-------------+------+-----+---------+-------+
614 3 rows in set (0.01 sec)
615
616
617 mysql> select * from tab where telnum=‘1333333‘;
618 +------+------+---------+
619 | id | name | telnum |
620 +------+------+---------+
621 | 1 | a | 1333333 |
622 +------+------+---------+
623 1 row in set (0.00 sec)
624
625 mysql> select * from tab where telnum=1333333;
626 +------+------+---------+
627 | id | name | telnum |
628 +------+------+---------+
629 | 1 | a | 1333333 |
630 +------+------+---------+
631 1 row in set (0.00 sec)
632
633 mysql> explain select * from tab where telnum=‘1333333‘;
634 +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
635 | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
636 +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
637 | 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
638 +----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
