INET_ATON(str)，address to number
INET_NTOA(number)，number to address

alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;

alter table user_index
-- 重新定义字段
MODIFY id int,
drop PRIMARY KEY

-- 增加一个没有建立索引的字段
alter table innodb1 add sex char(1);
-- 按sex检索时可选的索引为null
EXPLAIN SELECT * from innodb1 where sex='男'

select * from user where id = 20-1;
select * from user where id+1 = 20;

select * from article where title like '%mysql%';

alter table person add index(first_name,last_name);

select * from person where first_name = ?是可以利用索引的，
而select * from person where last_name = ?无法利用索引。

select sql_*** * from user;

reset query ***;

create table article(
    id int auto_increment PRIMARY KEY,
    title varchar(64),
    content text
)PARTITION by HASH(id) PARTITIONS 10

create table article_key(
    id int auto_increment,
    title varchar(64),
    content text,
    PRIMARY KEY (id,title)    -- 要求分区依据字段必须是主键的一部分
)PARTITION by KEY(title) PARTITIONS 10

create table article_range(
    id int auto_increment,
    title varchar(64),
    content text,
    created_time int,    -- 发布时间到1970-1-1的毫秒数
    PRIMARY KEY (id,created_time)    -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(
    PARTITION p201808 VALUES less than (1535731199),    -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
    PARTITION p201809 VALUES less than (1538323199),    -- 2018-9-30 23:59:59
    PARTITION p201810 VALUES less than (1541001599)    -- 2018-10-31 23:59:59
);

insert into article_range values(null,'MySQL优化','内容示例',1535731180);
flush tables;    -- 使操作立即刷新到磁盘文

create table article_list(
    id int auto_increment,
    title varchar(64),
    content text,
    status TINYINT(1),    -- 文章状态：0-草稿，1-完成但未发布，2-已发布
    PRIMARY KEY (id,status)    -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY list(status)(
    PARTITION writing values in(0,1),    -- 未发布的放在一个分区    
    PARTITION published values in (2)    -- 已发布的放在一个分区
);

alter table article_range add partition(
    partition p201811 values less than (1543593599)    -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
    -- more
);

alter table article_range drop PARTITION p201808

alter table article_key add partition partitions 4

alter table table-name disable keys

alter table table-name enable keys

配置项：slow_query_log
可以使用show variables like ‘slov_query_log’查看是否开启，
如果状态值为OFF，可以使用set GLOBAL slow_query_log = on来开启，
它会在datadir下产生一个xxx-slow.log的文件。


设置临界时间配置项：
long_query_time查看：show VARIABLES like 'long_query_time'，单位秒
设置：set long_query_time=0.

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> set profiling=on;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)


mysql> insert into article values (null,'test profile',':)');
Query OK, 1 row affected (0.15 sec)


mysql> show profiles;
+----------+------------+-------------------------------------------------------+
| Query_ID | Duration   | Query                                                 |
+----------+------------+-------------------------------------------------------+
|        1 | 0.00086150 | show variables like 'profiling'                       |
|        2 | 0.15027550 | insert into article values (null,'test profile',':)') |
+----------+------------+-------------------------------------------------------+

C:\Users\zaw>mysqlslap --auto-generate-sql -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 1.219 seconds
        Minimum number of seconds to run all queries: 1.219 seconds
        Maximum number of seconds to run all queries: 1.219 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=100 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 3.578 seconds
        Minimum number of seconds to run all queries: 3.578 seconds
        Maximum number of seconds to run all queries: 3.578 seconds
        Number of clients running queries: 100
        Average number of queries per client: 0


C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Average number of seconds to run all queries: 5.718 seconds
        Minimum number of seconds to run all queries: 5.718 seconds
        Maximum number of seconds to run all queries: 5.718 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=innodb -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 5.911 seconds
        Minimum number of seconds to run all queries: 5.485 seconds
        Maximum number of seconds to run all queries: 6.703 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0

C:\Users\zaw>mysqlslap --auto-generate-sql --concurrency=150 --iterations=3 --engine=myisam -uroot -proot
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
        Running for engine myisam
        Average number of seconds to run all queries: 53.104 seconds
        Minimum number of seconds to run all queries: 46.843 seconds
        Maximum number of seconds to run all queries: 60.781 seconds
        Number of clients running queries: 150
        Average number of queries per client: 0