MySQl优化笔记

吉他她他它
323 阅读7分钟

2.建表原则

表的优化：

  1. 定长与变长分离

    • 核心且常用字段宜建成定长，放在一张表。
    • varchar、text、blob这种变长字段，适合单放一张表。

    原因：每个字段定长，也就意味着每行的数据是定长，在磁盘上的存储区域大小也是固定的，也就是说当我们查某个id=10的数据的时候，我们只需要查到数据开始的磁盘位置＋每行数据的长度×(10-1)，就是要查的数据开始位置，＋每行数据长度就是数据的结束位置。更有利于范围查找，limit查找。

 2. 分离常用字段和非常用字段

  结合业务场景，将查询频率低的字段拆出来。

  减少不必要的查询

 3. 添加冗余字段提高字段

  • 需要连表查询数量或者使用函数的

列选择原则

  1. 字段类型优先级(从优到低)

    整形(定) > date,time(定) > enum,char(定) > varchar(不) >blob,text(不)

    int
    类型 字节 最小值 最大值
    TINYINT(有符号) 1 -128 127
    TINYINT(无符号) 1 0 255
    SMALLINT(有) 2 -32768 32767
    SMALLINT(无) 2 0 65535
    MEDIUMINT(有) 3 -8388608 8388607
    MEDIUMINT(无) 3 0 16777215
    INT(有) 4 -2147483648 2147483647
    INT(无) 4 0 4294967295
    BIGINT(有) 8 -9223372036854775808 9223372036854775807
    BIGINT(无) 8 0 18446744073709551615

    Q：我们平时声明字段的时候，column int(2),这个声明的2是不是就是字段的长度，只能存两位的整数？

    其实并不是，他并不会限制值的合法范围，只是规定了MySQL的一些交互工具用来显示字符的个数。int所对应的4个字节，对应的最大最小值才是限制存入字段的值。

    float
    类型 解释 字节
    float(m,d) m为最大长度，也就是最大总位数，d为小数位。默认float存储限制跟硬件有关，一般是以总位数做限制的。 超过位数后将四舍五入。 4
    double(m,d) ~ 8
    decimal(m,d) ～ 和m,d值相关

    注：

    float(p):当0<p<24时，字段类型为float;当p>24时，字段类型会自动转为double.

    decimal(m):等同于decimal(m,0).不指定长度的情况下，m=10.

    比较float或double的时候，不能直接等于，可能会不相同。这个问题是常见的计算机语言，类似js、php中小数的比较。并不是所有的float可以存储准确的精度。

    总的来说，浮点数存在误差问题。对货币等对精度敏感的数据，应用定点表示存储。

    enum

    • 起到了约束值的目的，内部用整型存储。

    • 当与char联查时，内部要经历串与值的转化。

    • 与enum关联时，速度很快。

    • 优势：当char非常长，enum存放的依然是整型。

    char

    char,需考虑字符集和排序校对集。

    varchar

    速度慢，考虑字符集的转换和排序。

    text/blob

    无法使用内存临时表，排序操作只能在磁盘上进行。意味着更慢。

  2. 够用就行，不要慷慨 原因：大的字段浪费内存，影响速度。 varchar(10), varchar(300)存储内容相同，但做表联查时，需要话费更多的内存。

  3. 尽量避免用NULL()

    1.NULL不利于索引查询，需要使用特殊值存储NULL.

    2.存NULL比存空字符串更占磁盘空间。【已实验测试过，10000条数据（版本5.6）（varchar(5)）】

    3.查询不方便。【例：where a != 2,a为null并不会被查出】

索引优化策略

  1. 索引类型：

    • btree索引：

      在Myisam、innodb中默认使用的是btree索引。

      Myisam索引查找的过程：Myisam的索引和数据是单独存储的，也就是索引和数据是两个单独的文件。 存储位置在/var/lib/mysql/数据库/。myisam会有三个文件，其中.MYI为表的索引，.MYD为表的数据，frm为表的结构。

      通过索引查找数据时，是先通过索引树对索引进行查找，找到后拿到数据存储位置，回行进行数据读取。

      Innodb索引查找：Innodb中数据和索引是存储在一起的。Innodb只有两个文件，.frm表结构文件，.ibd索引和数据文件。

      每个Innodb表都会有一列聚簇索引。聚簇索引会形成一个大的Btree树。树的叶子包含着每行的数据。当使用聚簇索引时，速度相对Myisam就会很快，没有回行的查询。那么普通索引呢？普通索引又会形成新的Btree，新的Btree叶子节点并不会存储数据，而是存储该数据在聚簇索引中的位置，通过指针指向，是对主键的引用。

      树的层数，最大比较次数，查询时间复杂度为 2^(n-1) = 数据量

    • hash索引

      在memory中使用hash索引，理论时间复杂度为O(1)

      原理：通过某种算法算出数据的存储位置，因此查找时可以直接算出存储位置。

      问： hash速度如此之快，为什么不都用hash呢？

      1. 存储位置可能会重复
      2. 无法对范围查找进行优化
      3. 无法利用前缀索引
      4. 排序无法优化
      5. 必须回行

  2. 理想的索引

    1.查询频繁 2.区分度高 3.长度小【索引长度直接影响索引文件的大小】 4.尽量能覆盖常用查询字段

    1. 当某列较长，有需要增加索引，需要从左到右截取部分来建索引：

    • 截的越短，重复度就越高，区分度越小，索引效果不好

    • 截的越长，重复读越低，区分度越高，索引效果好，但同时索引文件较大导致增删改查速度慢

      惯用手法：截取不同长度，并测试其区分长度。 select count(distinct left(word,6))/count(8) from table

    1. 对于左前缀不易区分的列。

      如：url列

      http://www.baidu.com; http://www.google.com

      列的前11个字符都相同。如下俩办法：

      1.把列内容倒过来存储并建立索引

      2.伪hash索引效果

      添加新字段，将字符串转成整数。crc32【可能会重复】

    2. 多列索引

      1.考虑因素：查询频率，区分度，查询顺序

  3. 索引与排序

    1. 对于覆盖索引，直接在索引上查询时，就是有顺序的，using index。
    2. 先取出数据，形成临时表，做filesort（文件排序，文件可能在磁盘上，也可能在内存中）

  4. 重复索引与冗余索引

    • 重复索引：同列或同顺序列多索引，为重复索引
    • 冗余索引：列有重叠，但顺序不一样

  5. 索引碎片与维护

    当表有大量的增删改操作时，会导致索引碎片问题

    alter table *** engine innodb

    optimize table ***

    注：修复表碎片，会把所有文件重新整理一遍，使对齐。当表行数较大，会很耗资源，不能频繁的修复。

sql语句优化

  1. 如何查询快

    • 联合索引顺序，区分度，长度

    • 覆盖索引

    • 传输的少，更少的行和列

    • 切分查询：把数据拆成多次。

      一次查询太多数据会造成一次性锁住多个表或行，耗费大量系统资源，阻塞很多小的重要的查询。

    • 分解查询【1.减少冗余字段】

explain

  • type:all(扫描所有数据);index(扫描所有索引);range;ref;const;system;null
  • extra: index(使用索引覆盖)；using where (光靠索引定位不了，还得where判断)；using temporary (用的临时表)；using filesort（文件排序，可能在磁盘或内存）
avatar
文章
阅读
粉丝