mysql 表格示范_mysql 学习笔记（实践篇）

实践篇

09 | 普通索引和唯一索引

查询过程：性能几乎无差别

更新过程：普通索引 更新的目标页不在内存中可以使用到change buffer。建议使用普通索引。

change buffer:当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写)，而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

10 | MySQL为什么有时候会选错索引

explain 查询语句执行情况

show index 可以查看索引基数

优化器的选择主要基于扫描行数(mysql使用采样统计计算，非准确数字)判断

analyze table t 可以优化查询分析

索引选择异常和处理

force index 强行选择一个索引(一般不需要，只有在查询分析判断问题时使用，基本还是使用mysql内部优化器机制)

更新语句，如使用 order by b,a 就会使用a,b索引

业务沟通，删除不必要的查询索引

*

11 | 怎么给字符串字段加索引？

前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。alter table SUser add index index2(email(6));

前缀索引长度：建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

mysql> select

count(distinct left(email,4))as L4,

count(distinct left(email,5))as L5,

count(distinct left(email,6))as L6,

count(distinct left(email,7))as L7,

from SUser;

使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如 5%。然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6。

前缀索引就用不上覆盖索引对查询性能的优化

对身份证类似的索引，如果用前缀的话，区分度太低，就不合适，解决方案有几种。

使用身份证倒序存储,reverse()

增加整形的hash字段，如crc32()

*

12 | 为什么我的MySQL会“抖”一下？

更新主要是写内容和日志，更新内容会先存入内存(没有同步的叫脏页)，等时机(4种情况)再写入磁盘。

redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写

内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。

系统空闲

系统重启

InnoDb使用刷新内存机制。InnoDB 用缓冲池(buffer pool)管理内存

InnoDB 的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是 redo log 写盘速度。

13 | 为什么表数据删掉一半，表文件大小不变？

将innodb_file_per_table 设置为 ON，表数据是单独的文件ibd,drop table后会删除文件，并回收空间，否则不行。

记录复用：删除记录的空间，符合条件的数据下次插入时候会使用

数据页复用：删除后整个数据页被标记为可复用。

插入数据：如果当前数据页已满，则会数据页分裂，可能造成数据空洞。

重建表：

14 | count(*)这么慢，我该怎么办？

Myisam 有一个地方专门记录总数

InnoBb 因为是事物，并发等属性，不能直接获取，只能通过遍历最小的那颗索引树实现。

count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

尽量使用count(*)

*

16 | “order by”是怎么工作的？

全字段排序：

需要查询的字段 初始化 放入sort_buffer。

sort_buffer_size，就是 MySQL 为排序开辟的内存(sort_buffer)的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

rowid排序：

设置 max_length_for_sort_data 大小，如果单行数据太大，使用rowid排序，rowid排序需要结果再回表查询

mysql默认使用全字段排序，原则尽量使用内存，尽量少回表。

17 | 如何正确地显示随机消息？

order by rand() 使用了内存临时表，内存临时表排序的时候使用了 rowid 排序方法。要尽量避免使用该方法。

扫描行数是C+(Y1+1)+(Y2+1)+(Y3+1)

进一步优化：

18 | 为什么这些SQL语句逻辑相同，性能却差异巨大？

索引字段做函数操作，会破坏索引值的有序性。

隐式类型转换:mysql 默认比较会把字符串转换成数字。如果数字转换成字符串，优化器会默认添加函数，所以破坏索引。需使用 where id=''的单引号。

隐式字符编码转换：

mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

19 | 为什么我只查一行的语句，也执行这么慢

第一类：简单查询长时间不返回

等MDL锁，等flush，等行锁

show processlist

MySQL 启动时需要设置 performance_schema=on，相比于设置为 off 会有 10% 左右的性能损失。通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

第二类：查询慢

慢查询日志

lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果。

select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回

*

20 | 幻读是什么，幻读有什么问题？

innoDB 间隙锁，next-key lock。

22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

第一种方法：先处理掉那些占着连接但是不工作的线程。

第二种方法：减少连接过程的消耗。

慢查询性能问题

QPS 突增问题

*

23 | MySQL是怎么保证数据不丢的？

redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快；* 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。

IO瓶颈解决方法：

通常我们说 MySQL 的“双 1”配置，指的就是 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log(prepare 阶段)，一次是 binlog。

2，15，23章节redo log,bin log 和crash safe.

24 | MySQL是怎么保证主备一致的？

主备切换，binlog格式

statement:可能引起不一致

row:全量信息，可以恢复所有数据。缺点，占用空间大。

mixed：有风险的sql语句，会使用row格式记录。

MariaDB 的flashback 工具：恢复数据误操作。

25 | MySQL是怎么保证高可用的？

主备延迟的几种情况

主备机器性能

备库压力偏大(查询等等)

大事物

策略

可靠性优先策略

可用性优先策略

31 | 误删数据后除了跑路，还能怎么办？

sql_safe_updates=On 更新或删除强制带where

设置有效的全量备份点。通过binlog恢复数据

权限账号管理。

32 | 为什么还有kill不掉的语句？

原因1：更改状态，发信号给block线程。

原因2：大查询回滚，DDL，大事务，逻辑耗时长。

解决办法：影响系统环境

增加并行线程数量 innodb_thread_concurrency

减轻IO系统压力

客户端连接超多表数据库使用-A(不用-q)

33 | 我查这么多数据，会不会把数据库内存打爆？

全表扫描是边算边发，通过socket receive buffer栈。

InnoDB全表扫描有淘汰机制，LRU.

34 | 到底可不可以使用join？

使用 join 语句，性能比强行拆成多个单表执行 SQL 语句的性能要好；

如果使用 join 语句的话，需要让小表(where过滤后数据量小的)做驱动表。

被驱动表能用索引最好，否则使用内存判断(Block Nested-Loop Join)

35 | join语句怎么优化？

BKA 优化是 MySQL 已经内置支持的，建议你默认使用。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

还是要在被动表上建立索引，变通的办法，可以建立临时表，加索引。

扩展 -hash join。业务代码取出后，存入hash类型的数据结构，如PHP数组，c++的set，再遍历。

36 | 为什么临时表可以重名？

临时表用来处理逻辑比较复杂的计算。

只在自己线程内可见，可重名，命名规则：#sql{进程 id}_{线程 id}_ 序列号.frm

线程取消后，表自动删除，主备模式需要用drop temporatory table处理。

binlog_format='row’的时候，临时表的操作不记录到 binlog 中

40 | insert语句的锁为什么这么多？

insert … select 在可重复读隔离级别下，这个语句会给 select 的表里扫描到的记录和间隙加读锁。

insert 和 select 的对象是同一个表，则有可能会造成循环写入。这种情况下，我们需要引入用户临时表来做优化。

insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S 锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。

41 | 怎么最快地复制一张表？

物理表innodb:

执行 create table r like t，创建一个相同表结构的空表；

执行 alter table r discard tablespace，这时候 r.ibd 文件会被删除；

执行 flush table t for export，这时候 db1 目录下会生成一个 t.cfg 文件；在 db1 目录下执行 cp t.cfg r.cfg; cp t.ibd r.ibd；这两个命令(这里需要注意的是，拷贝得到的两个文件，MySQL 进程要有读写权限)；

执行 unlock tables，这时候 t.cfg 文件会被删除；

执行 alter table r import tablespace，将这个 r.ibd 文件作为表 r 的新的表空间，由于这个文件的数据内容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的数据。

用 mysqldump 生成包含 INSERT 语句文件的方法，可以在 where 参数增加过滤条件，来实现只导出部分数据。这个方式的不足之一是，不能使用 join 这种比较复杂的 where 条件写法。

用 select … into outfile 的方法是最灵活的，支持所有的 SQL 写法。但，这个方法的缺点之一就是，每次只能导出一张表的数据，而且表结构也需要另外的语句单独备份。

后两种方式都是逻辑备份方式，是可以跨引擎使用的。

42 | grant之后要跟着flush privileges吗？

一般在生产环境上要合理控制用户权限的范围。我们上面用到的这个 grant 语句就是一个典型的错误示范。如果一个用户有所有权限，一般就不应该设置为所有 IP 地址都可以访问。

用户权限表mysql.user

grant super on . to 'ua'@'%' identified by 'pa';(不建议)

如果用户’ua’@’%'不存在，就创建这个用户，密码是 pa；

如果用户 ua 已经存在，就将密码修改成 pa。

create user 'ua'@'%' identified by 'pa';

grant all privileges on . to 'ua'@'%' with grant option;

revoke all privileges on . from 'ua'@'%';

数据库权限

grant all privileges on db1.* to 'ua'@'%' with grant option;

表权限mysql.tables_priv，列权限mysql.columns_priv

create table db1.t1(id int, a int);

grant all privileges on db1.t1 to 'ua'@'%' with grant option;

GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

使用grant和revoke会修改表和内存的数据，是实时的。

flush privileges当数据表或结构有变动时，重新生成内存数据用的。

43 | 要不要使用分区表？

MyISAM 分区表禁用分区表

Innodb 可以使用 本地分区策略

分区并不是越细越好

分区也不要提前预留太多

45 | 自增id用完怎么办？

表的自增 id 达到上限后，再申请时它的值就不会改变，进而导致继续插入数据时报主键冲突的错误。

row_id 达到上限后，则会归 0 再重新递增，如果出现相同的 row_id，后写的数据会覆盖之前的数据。

学习心得

多写sql，了解各种参数意义

了解原理

试着教给人家听