mysql文档摘要续4

mysql function and operate

mysql在针对string类型使用int的索引情况，如select * from table where tbl_name=1;在tbl_name上有索引且是string类型，此时将不走索引因为对于mysql来说1可能代表'1','1a'.即没法确定值。

analyze table tbl_name:分析数据库表主要用于索引分析，可以改变数据库对索引的选择与join时的操作，可以使用show index查看效果(Cardinality字段).

describe 可用于table分析，也可以sql语句分析与explain一样

innodb:innodb的表空间由各种segment组成，每个segment由extent组成，每个extent由多个page构成。

mysql锁机制：

innodb的锁一般使用在索引上，如果索引使用不当，可能导致全表被锁。

mysql的日志：

undo log是tablespace里面的undo段（即以前的rollback segment），在5.6.3以后可以配置独立的tablespace(innodb_undo_tablespaces，innodb_undo_directory),redo log由专门的文件存储（innodb_log_file_size）。

mysql通过undo log和redo log来实现数据库的原子性、持久性(redo log)、一致性(undo log).

undo log通过记录事务的操作前的版本。实现回滚及mvcc功能，是逻辑日志，根据每行记录进行记录。undo log可以当成数据undo log的操作都会记录到redo log中。insert log比较简单，update、delete log更复杂需要维护多个版本，防止其它事务使用，有purge线程专门处理undo页

redo log通过记录事务的操作新版本，在提交事务之前进行持久化，通常是物理日志，记录的是页的物理修改操作。一般是顺序写入。也会有redo log缓存可以通过参数设置，适当提升性能，当然有可能丢失数据。master 线程一般会定期刷新redo 页到磁盘。

innodb 针对每行添加了3个字段，一个是标识上一个事务identifier叫DB_TRX_ID，一个是指向undo log 记录的指针DB_ROLL_PTR，一个是每次插入新纪录时单调增加的row id DB_ROW_ID。

innodb在删除时并不会马上删除，只是添加一个删除的标识位，只有当undo log的delete log被删除时（由上面提到的purge线程处理），才真正物理的删除该行及索引。

innodb在primary key(clustered index)的原则：如果手动创建了primary key则采用第一个unique index，如果没有unqiue index，innodb在内部产生一个隐藏的包含row id的列。

innodb change buffering:innodb针对索引特别是二级索引的缓存，主要用于insert、update、delete操作引起的索引变化。此时如果索引没有缓存，将会引起显著的io次数，所以可以使用change buffering暂时缓存起来，适当的时候再更新或合并到索引上（可以在index page加载到缓存时）。

innodb不维护一个表的记录数select count(*) from tbl_name需要去查找索引，主要是因为innodb支持事务，当多个事务并发时数据，在一个时间点计算的行数可能不一样（myisam相反）。

partition分区

分区的优点：1、扩大存储空间，可以在不同的disk、filesystem存储更多的数据 2、加快查询速度 如SELECT * FROM t PARTITION (p0,p1) WHERE c < 5，只需要查询固定分区 3、提升性能，查询一些聚合函数，可以在不同分区并发进行，增加throughput。

四类分区

range：主要使用场景 1 当你需要删除一些数据时 2 当你使用date、datetime等字段分区时 3 当你经常使用分区字段查询时

例：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (6),
    PARTITION p1 VALUES LESS THAN (11),
    PARTITION p2 VALUES LESS THAN (16),
    PARTITION p3 VALUES LESS THAN (21)
);

list

例：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20),
    PARTITION pWest VALUES IN (4,12,13,14,18),
    PARTITION pCentral VALUES IN (7,8,15,16)
);

 

hash：在hash(expr)中，expr必须是个整数常量，mysql一般hash值mod(分区数)决定分区位置

例：

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;

key:与hash的区别是key值可以不是整数，采用md5(expr)或者类似的函数如password决定分区位置

例：

CREATE TABLE tk (
    col1 INT NOT NULL,
    col2 CHAR(5),
    col3 DATE
)

PARTITION BY LINEAR KEY (col1)

PARTITIONS 3;

EXPLAIN PARTITIONS可以分析带有分区的表

分区约束：在一个表里面如果有唯一索引（主键索引包含在唯一索引里面），那么用于分区的字段必须是该表的所有唯一索引的一部分，如果没有唯一索引则不受限制，可以使用任意字段

错误例：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1),
    UNIQUE KEY (col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

正确例：

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col2, col3)
)
PARTITION BY HASH(col3)
PARTITIONS 4;

CREATE TABLE t2 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    UNIQUE KEY (col1, col3)
)
PARTITION BY HASH(col1 + col3)
PARTITIONS 4;

注：在mysql5.6.6以后针对mysiam引擎表，不在采取全表锁，而是使用partition pruning(分区优化)对特定的分区加锁。