Mysql

Database collection

by Hua in Database

MySql database skills collection.

CSV存储引擎

特点

  • 不能定义索引，列定义必须NOT NULL，不能设置自增列
  • CSV表的数据的存储格式用逗号隔开，可直接编辑文件进行数据修改
  • 数据安全性低

应用场景

  • 不适用大表或者数据的在线处理
  • 数据的快速导出导入
  • 表格直接转换成CSV

Archive存储引擎

特点

  • 压缩协议（ARZ文件格式）进行数据的存储，磁盘占用少
  • 只支持insert和select两种操作
  • 只允许自增ID列建立索引

应用场景

  • 数据备份系统（日志系统/文档归档等）
  • 大量设备高频的数据采集

Memory存储引擎

特点

  • 数据存储在内存中，处理效率高，表大小默认限定16M(可配置)
  • 不支持大数据存储类型的字段如 blog、text
  • 不支持可变长字符类型 varchar（32)=char(32)
  • 支持Hash索引，等值查询效率高
  • 数据的可靠性低，重启数据库服务或数据库系统崩溃数据丢失

应用场景

  • 热点数据快速加载（功能类似缓存中间件）
  • MySql临时表存储（查询结果于内存中计算的数据）

Myisam存储引擎

特点

  • 较快的数据插入和读取性能
  • 支持索引缓存，不支持数据缓存
  • 数据存储较inodb磁盘空间占用小
  • 支持表级别的锁，不支持事务
  • 数据文件与索引文件分开存储。主键索引与辅助索引同级
  • 针对数据统计有额外的常数存储。因为count(*)等查询效率很高

应用场景

  • 只读应用或者以读为主的业务

Innodb存储引擎

特点

  • 支持事务
  • 行级锁
  • 支持索引和数据缓存
  • 聚集索引
  • 外键支持

应用场景

  • everywhere

How to resolve the problem of case insensitive?

写空气质量发布系统时遇见的问题…

创建表或者修改表的时候指定 collate 字符集为 utf8_bin. [utf8_ci is case insensitive collation]

create table table_name(
    id int,
    name varchar(20)
) character set utf8 collate utf8_bin;

# or

alter table table_name modify filed_name varchar(20) collate utf8_bin;

using optimizer_trace

SET optimizer_trace="enabled=on";
SELECT * FROM xxx;
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

SQL 执行顺序

  1. FROM 计算笛卡尔积
  2. ON 根据 join_condition 过滤数据,主表保留
  3. JOIN 添加相关外部表格数据
  4. WHERE 根据 where_condition 过滤数据
  5. GROUP BY 分组
  6. HAVING 根据 having_condition 过滤数据
  7. SELECT 选择指定的列
  8. DISTINCT 指定列去重，
  9. ORDER BY 按 order_by_condition 排序
  10. LIMIT offset 和 rows
(7) SELECT
(8) DISTINCT <select_list>
(1) FROM  <left table>
(3) <join_type> JOIN <right_talbe>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) HAVING <having_condition>
(9) ORDER BY <order_by_condition>
(10) LIMIT <limit_number>

Explain 语句

结果字段描述

  • id 表的顺序，值越大，优先级越高
  • select_type 操作类型 [simple, primary, subquery, derived, union, union result]
  • table 表名
  • partitions 没见过变化
  • type 查询类型 [system, const, eq_ref, ref, range, index, All] 避免出现 All
  • possible_keys 可能用到的索引
  • key 用到的索引
  • key_len 索引长度(越短越好)
  • ref 表直接的引用（显示关联的字段。如果使用常数等值查询，则显示 const，如果是连接查询，则会显示关联的字段）
  • rows 有多少行被优化器查询
  • filtered 百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例
  • Extra

type 详解

  • system 表里只有一条记录的速度，很少见
  • const e.g. explain select * from table where id=1 id 是自增唯一主键索引
  • eq_ref 唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。
  • ref 非唯一索引扫描，返回匹配某个单独值的所有行。e.g. explain select * from tb_emp where deptid=1
  • range 给点范围 between > < in。字段需是唯一索引
  • index 扫描整个索引树
  • ALL 扫描整个表

Extra 详解

  • Using filesort 无法利用索引完成排序，所以使用文件排序，必须优化。（通常是没有相关字段的索引）
  • Using temporary 使用临时表保存中间结果。（通常是排序或分组的字段没有索引）
  • Using index 有时夹杂 Using where， 效率过得去。

优化总结

  • 左连接时，右表关联字段加索引
  • 右连接时，左表关联字段加索引
  • 尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对 A、B 列创建了索引，然后查询中也使用 A、B 列），减少 select * 的使用。
  • 如果创建了 3 个连续字段的索引（name，age，gender）：
    • 第一个字段单独跑没问题
    • 第一个和第二个一起跑没问题
    • 第一个和第三个一起跑，只会有部分索引起作用
    • 三个一起跑，杠杠的
    • 以上情况排除常量等值查询的情况（e.g. name=’name’ and age=’12’ and gender=’femal’）
  • 导致索引失效
    • 对字段使用了函数，计算，类型转换等
    • 范围右边还有条件将会失效[age>30 and gender=’male’]）
    • 使用 is null, is not null, !=, <>
    • like start with %，右边会失效。解决办法：覆盖索引(select id/name/id,name/id,name,age/id,name,age,gender from table where name like ‘%John%’;)
    • 字符串不加单引号失效
    • or 条件会让其失效，尽量避免使用

慢查询日志

show variables like ‘%slow_query_log%’ –查看是否开启

set global slow_query_log=1; –临时开启当前数据库

show variables like ‘%long_query_time%’ –查看默认阈值

set global long_query_time=3; –设置阈值

show global variables like ‘%long_query_time%’

test: select sleep(10)

show global status like ‘%Slow queries%’

Java Utils

Cisco

Next →