不是第一

MYSQL最要的特点

  1. MYSQL的物理结构是索引组织表(Index Organized Table， IOT)，不是堆表(Heap Table)。
  2. 数据就是索引，索引就是数据。记录之间是有顺序关系的。这种结构导致很多在oracle上得到的经验都失效了。在使用MYSQL之前一定要清楚这一点。

数据库设计

主键

  • 因为mysql的表是IOT，所以每张表一定要有主键，必须要有主键。
  • 如果没有主键，mysql会自动创建一个主键和主键索引。
  • mysql所有的数据，也就是每条记录数据都是存放在主键索引中的。
  • 这个和oracle中表数据和索引分开存储的方式是非常不同的。
  • 在mysql中主键索引又叫聚簇索引(Clustered Index)。
  • 表的主键是查询语句执行效率的关键点。

字符型数据

  • mysql的字符型数据除了有字符集的区别外，还有有一个特殊地方就是需要指定字符集的比较策略(Collation)。
    • 在不同比较策略下: select 'A' = 'a' from dual;
    • 这个语句会有不同的返回结果。
      • 当数据库字符类型使用utf8mb4时，默认的Collation是utf8mb4_general_ci，忽略大小写。Collation 会影响语句的逻辑，索引的排序规则，检索效率。
      • 强烈建议数据库字符类型使用utf8mb4，collation 使用utf8mb4_bin
#在配置文件中使用下面的参数；
default-character-set=utf8mb4
character-set-server=utf8mb4
collation_server=utf8mb4_bin

辅助索引，或者次级索引(Secondary Indexes)

  • mysql的辅助索引和主键索引是非常不同的。
    • 主键索引存储完整的数据，辅助索引不存储数据，只存储指向数据主键的引用。
    • 当使用辅助索引查找数据时，总是先找到主键的引用，然后再从主索引上找到数据。这个操作叫回表。这种查询是比较慢的，尤其是数据量大，查询频繁的时候。

水平分表

  • 水平分表：常见的就是月度数据。如果数据量很大话，尽量按月度分开建表存储。在oracle中可以使用分区表来实现。mysql虽然也提供了分区表技术，但是有很多限制，比如： 主键必须包含分区键，或者分区表上没有主键。
  • 建议人为水平分表，更可控。当然，缺点就是应用的代码也需要修改。

垂直分表

  • 垂直分表：常见的就是人员信息。把人员毕业证的照片，简历这些数据量比较大，平常又不查的数据存放到另外的表中。需要的时候，使用关联查询。

SQL优化

定位慢查询

# 启用慢查询日志，在配置文件中加入：
slow_query_log = 1
long_query_time = 2  #单位是秒，可以使用小数，0.2表示200毫秒。
slow_query_log_file = slow.log
log_queries_not_using_indexes = 1

Explain分析问题

  • 主要关注：是否使用了索引、使用的索引是否合适

explain select count(1) from employees where emp_no < 20000;
  1. id //select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
  2. select_type //查询类型
  3. table //正在访问哪个表
  4. partitions //匹配的分区
  5. type //访问的类型
  6. possible_keys //显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
  7. key //实际使用到的索引，如果为NULL，则没有使用索引
  8. key_len //表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  9. ref //显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
  10. rows //根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
  11. filtered //查询的表行占表的百分比
  12. Extra //包含不适合在其它列中显示但十分重要的额外信息

基本思路

  • 各种数据库的性能问题基本上都是两大类：
    1. 减少 IO
    2. 减少CPU计算
  • 为了解决这两个问题，从开发人员的方法上只有一种：
    • 改变SQL的执行计划

常见方式

对于mysql有一些针对性的方法，如下：

  1. 尽量早过滤
  2. 少用子查询
  3. 尽量用join取代子查询
  4. 尽量少用or
  5. 尽量避免select *
  6. 用 union all 代替 union
  7. 在有索引的列上order by， group by
  8. 尽量避免在where中使用!=或<>操作符
  9. 尽量避免在where中对字段进行null值判断
  10. 尽量避免长事务
  11. 不要在索引列上计算

例: 必要时强制查询优化器使用某个索引

select    * from  t1 where  nextprocess = 1 and processid in (8 , 32, 45)


select *  from t1 use index (ix_processid) where  nextprocess = 1   and processid in (8 , 32, 45);

-- 基本思路
--拒绝3B:
-- 大SQL (BIG SQL)
-- 大事务 (BIG Transaction)
-- 大批量 (BIG Batch)