mysql学习总结08 — 优化(设计层)-索引与分区分表

# 建表时直接添加索引
mysql> create table t2 (
	->	id int primary key auto_increment, # 主键索引
	->	name varchar(32) not null,
	->	age tinyint not null,
	->	intro text,
	->	unique key(name), # 唯一索引
	->	index(age), # 普通索引
	->	fulltext index(intro), # 全文索引
	->	index(name,age) # 复合索引
	-> )engine myisam charset utf8;

# 表创建后添加索引
mysql> alter table t2 add unique key(name), add index(age), add fulltext index(intro), add index(name,age);

mysql> show create table <表名>;
mysql> show index from <表名>;
mysql> show indexes from <表名>;
mysql> show keys from <表名>;
mysql> desc <表名>;

# 如果有auto_increment属性，要先去掉该属性再删除
mysql> alter table t2 modify id int unsigned;
# 没有auto_increment属性时
mysql> alter table t2 drop primary key;

# 如果没有指定索引名可以通过查看索引获取
mysql> alter table <表名> drop index <索引名>;

mysql> explain select * from t2 where id=1\G

# ID上有主键索引但没有用到索引的情况
mysql> desc select * from user where id+2=4\G
# 可以使用索引的情况
mysql> desc select * from user where id=4-2\G
mysql> desc select * from user where 4-2=id\G

# 索引覆盖
mysql> desc select * from user where name like '%cs'\G # 不使用索引
mysql> desc select * from user where name like 'cs%'\G # 使用索引

mysql> alter table user add index(name,age);
mysql> select * from user where name = "cs"; # 使用索引
mysql> select * from user where age = 25; # 未使用索引

mysql> alter <表名> add key (字段(前n位位数))

mysql> select count(distinct left(id,9)) from t1;

# 添加全文索引
mysql> alter table t1 add fulltext index(name);
# 使用方法 select * from t1 where match(<字段>) against(<模糊内容>);
# 错误案例 select * from t1 where <字段> like '%<模糊内容>%';
# 使用
mysql> select * from t1 where match(title) against('well');

mysql> create table <表名> (
	->	<字段信息>,
	->	<索引>
	-> )<表选项>
	-> partition by <分区算法>(<分区字段>)(
	->	<分区选项>
	-> );

mysql> create table p_list(
	->	id int,
	->	name varchar(32),
	->	store_id int
	-> )engine innodb charset utf8
	-> partition by list(store_id)(
	->	partition p_north values in(1,4,5,6,17,18),
	->	partition p_east values in(2,7,9,10,11,13),
	->	partition p_south values in(3,12,19,20),
	->	partition p_west values in(8,14,15,16)
	-> );
mysql> insert into p_list values(12,'csxiaoyao',3); # 存入东部区域

mysql> explain partitions select * from p_list where store_id=20\G

mysql> create table p_range(
	->	id int,
	->	name varchar(32),
	->	birthday date
	-> )engine myisam charset utf8
	-> partition by range(month(birthday))(
	->	partition p_1 values less than(3),
	->	partition p_2 values less than(6),
	->	partition p_3 values less than(9),
	->	partition p_4 values less than MAXVALUE
	-> );
# 插入数据
mysql> insert into p_range values(1,'csxiaoyao','2018-08-24'),(2,'sunshine','2018-11-28'); # 存入p_3和p_4

mysql> create table p_hash(
	->	id int,
	->	name varchar(20),
	->	birthday date
	-> )
	-> -- 按生日月份hash值将数据划分到5个区中
	-> partition by hash(month(birthday)) partitions 5;

# 求余方式(key/hash)
# alter table <表名> coalesce partition <数量>;
mysql> alter table p_hash coalesce partition 4;

# 范围方式(range/list)
# alter table <表名> drop partition <分表名>;
mysql> alter table p_list drop partition p_north;

# 求余方式(key/hash)
# alter table <表名> add partition partitions <数量>;
mysql> alter table p_hash add partition partitions 5;

# 范围方式(range/list)
# alter table <表名> add partition(
#	partition <分区名> values less than (<常量>)
#	或
#	partition <分区名> in (n,n,n)
# );

<?php
	$id = $_GET['id'];
	$tableArea = $id % 4;
	$tableName = 'tb_' . $tableArea;
	$sql = "insert into $tableName values( ... )";
	...

mysql> create table flag(id int primary key auto_increment)engine myisam charset utf8;

<?php
	$sql = "insert into flag values(null)";
	mysql_query($sql);
	$id = mysql_insert_id();
	$area = $id % 4;
	...