Mysql学习笔记二

目录

• Mysql 笔记二
  • 普通索引和唯一索引
    • change buffer
    • 更新操作
      • 内存中没有缓存
      • 内存中存在缓存
    • 总结
  • MySQL为什么有时候会选错索引？
  • 怎么给字符串加索引
  • 数据表占用空间的优化
    • 参数 innodb_file_per_table
    • 空洞
      • Delete 命令的原理
      • 重建表：解决空洞的方法（Online DDL）
      • 三种方式重建表的区别
  • SQL语句的逻辑
  • 慢SQL
    • 通过命令行show processlist
  • 提高性能
    • 短连接风暴
    • 慢查询性能问题
    • 高峰期数据库语句打挂

Mysql 笔记二

普通索引和唯一索引

change buffer

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

change Buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在共享表空间中，默认ibdata1中

merge 的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多
   个），依次应用，得到新版数据页；
3. 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变
   更。

更新操作

内存中没有缓存

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这
个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结
束了。（change buffer 只限于用在普通索引的场景下）

内存中存在缓存

唯一索引先判断，然后再去更新

普通索引直接更新

总结

尽量使用普通索引，然后把 change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

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

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

原因：优化器选择不正确，

analyze table

explain Sql

解决方法：采用 force index 强行选择一个索引。

怎么给字符串加索引

1. 直接创建完整索引，这样可能比较占用空间；

    alter table SUser add index index1(email);
   
   

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

    alter table SUser add index index2(email(6));
   

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

   select field_list from t where id_card = reverse('input_id_card_string');
   

4. 创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，
   都不支持范围扫描。

 select field_list from t where id_card_crc=crc32('input_id_card_string') and id_

5. 添加全文索引的方法

    alter table t add FULLTEXT(field_name);
   

数据表占用空间的优化

参数 innodb_file_per_table

1. 这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是
   跟数据字典放在一起；
2. 这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd
   为后缀的文件中。

空洞

Delete 命令的原理

delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

重建表：解决空洞的方法（Online DDL）

 alter table A engine=InnoDB 命令来重建表。

我给你简单描述一下引入了 Online DDL 之后，重建表的流程：

1. 建立一个临时文件，扫描表 A 主键的所有数据页；
2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row
   log）中，对应的是图中 state2 的状态；
4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑
   数据上与表 A 相同的数据文件，对应的就是图中 state3 的状态；
5. 用临时文件替换表 A 的数据文件。
   图 4 Online DDL
   可以看到，与图 3 过程的不同之处在于，由于日志文件记录和重放操作这
   个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。
   这也就是 Online DDL 名字的来源。

三种方式重建表的区别

optimize table、analyze table 和 alter table

1. 从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是recreate）
2. analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
3. optimize table t 等于 recreate+analyze。

SQL语句的逻辑

1. 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
2. 隐式类型转换，第三个例子是隐式字符编码转换，它们都跟，因为要求在索引字段上做函数操作而导致了全索引扫描。
3. 尽量每一句SQL都通过explain 来进行解释执行一遍

慢SQL

通过命令行show processlist

show processlist

1. Waiting for table metadata lock 状态

解决方案：

select locking_pid from sys.schema_table_lock_waits ;

2. 等flush

   出现 Waiting for table flush 状态的可能情况是：有一个 flushtables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句

3. 等行锁

   提高性能

   短连接风暴

4. ：先处理掉那些占着连接但是不工作的线程

   show processlist
   

5. 减少连接过程的消耗

   跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内

慢查询性能问题

1. 索引没有设计好；
2. SQL 语句没写好；
3. MySQL 选错了索引。

高峰期数据库语句打挂

1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行
   alter table 语句加上索引；
2. 执行主备切换；
3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执
   行 alter table 语句加上索引。