Mysql 优化方案
运行状态查询
了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间/一共执行了多少次select/update/delete.. / 当前连接)
show [seesion| gloable] status like ….
如：
//查询运行时间
show status like ‘uptime’；
//select | insert |delete 语句执行次数
show status like ‘com_select’
show status like ‘com_insert’
show status like ‘com_delete’
// 查询链接次数
show status like ‘connections’
// 查询慢查询语句
show status like 'slow_queries';
//默认慢查询时间10秒
show variables like ‘long_query_time’;
set long_query_time=1 ;//可以修改慢查询时间
// 慢查询设置my.ini
log-slow-queries = D:/Mysql/mysql-5.6.27-winx64/slowquery.log
long_query_time = 2
字段优化
尽量使用TINYINT、SMALLINT、MEDIUM_INT作为整数类型而非INT，如果非负则加上UNSIGNED
VARCHAR的长度只分配真正需要的空间
尽量使用TIMESTAMP而非DATETIME，
单表不要有太多字段，建议在20以内
避免使用NULL字段，很难查询优化且占用额外索引空间 default ”
用整型来存IP
索引使用优化
- 查询是否使用索引 关键字：explain select …
- like 避免使用 %key（前匹配）
- 避免使or，适当用in 代替
- 字段类型为字符串 必须用” 引起来，否则索引失效
- group by colm 默认会排序，建议使用 后面加order by null
- 能用链接的不用子查询
- 避免在where 子句中使用is null 判断 可以使用is not null
- 值很少的字段不适合建立索引
- 字符字段最好不要做主键
- 使用多列索引时主意顺序和查询条件保持一致，同时删除不必要的单列索引
- 强制使用某索引 select * from table
force index(indexname)或use - 前缀索引，当字段长度超过255需要定义截取 255
- 索引是表达式或函数的参数，索引被破坏
- 避免where 子句使用 ！= 和<> ,索引被破坏
- 对于连续数字，使用between a and b 代替 in
补充：索引使用定位
id
查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
| 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
特别的：如果使用union连接气值可能为null
select_type
查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
...
table
正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引
key
真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”分表优化
` 数据大 且访问量大 建议使用分表（也可以联合搭配分区）
除非单表数据未来会一直不断上涨，否则不考虑拆分，拆分会带来 逻辑部署等复杂问题，分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常点来讲就是提高表的增删改查效率。
分表的特点：
1. 将一个大表按一定规则分解成多张具有 独立的存储空间实体子表
2. 独立的数据文件、索引文件、表结构文件
3. 可以分布到不同机器、磁盘上
4. 按照规则访问定义好的子表
使用： 分表需要创建表，使用和维护比较麻烦
分区
访问量不大，但是数据量大建议使用分区
1. 分区的好处是：
表面看还是一张表，是将数据分段存储到多个位置
访问还是一张表
分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
部分查询能够从查询条件确定只落在少数分区上，速度会很快
分区表的数据还可以分布在不同的物理设备上
可以使用分区表赖避免某些特殊瓶颈，例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
可以备份和恢复单个分区
四种分区 使用方法：
查询是否支持分区：SHOW VARIABLES LIKE ‘%partition%’;
1. Range 分区
-- 创建分区
DROP TABLE IF EXISTS t_range_text;
CREATE TABLE t_range_text(
id INT(64) NOT NULL auto_increment ,
`name` VARCHAR(64) NOT NULL DEFAULT '',
-- 这里需要设置为datatime 不能设置timesimple
createTime datetime NOT NULL,
-- 分区的字段需要建立索引
primary KEY(id,`createTime`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (year(createTime))
(
PARTITION 2000_year VALUES less than(2000),
PARTITION 2005_year VALUES less than(2005),
PARTITION 2010_year VALUES less than(2010),
PARTITION 2015_year VALUES less than(2015),
PARTITION other_year VALUES less than MAXVALUE
);
-- 查看PARTITIONS字段是否使用分区
EXPLAIN PARTITIONS SELECT * FROM t_range_text;
-- 删除数据或者删除分区
ALTER TABLE t_range_text DROP PARTITION 2000_year;
-- 增加分区(有maxvalue分区无法加，且只范围参数只能变大不能变小)
ALTER TABLE t_range_text ADD PARTITION (PARTITION 2000_year VALUES LESS THAN (2000));
-- 合并分区
ALTER TABLE t_range_text
REORGANIZE PARTITION 2005_year,2010_year,2015_year INTO
(
PARTITION 2015_year VALUES LESS THAN (2015)
)2 . LIST 分区
CREATE TABLE t_list_text(
id INT NOT NULL,
fname VARCHAR(30),
store_id INT
)
PARTITION BY LIST(store_id)
PARTITION one VALUES IN (3,5,6,9,17),
PARTITION two VALUES IN (1,2,10,11,19,20),
PARTITION three VALUES IN (4,12,13,14,18),
PARTITION fore VALUES IN (7,8,15,16)
);
-- 插入数据需注意 store_id 必须在列表中
insert into t_list_text values(1,'11',33);X3 HASH分区
CREATE TABLE employees (
id INT NOT NULL,
fname VARCHAR(30),
store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4;
-- PARTITION BY LINER HASH(store_id) 线性hash是2的幂运算（普通的是取模运算）
-- 优点：在于增加、删除、合并和拆分分区将变得更加快捷
-- 缺点：常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。5 key 分区
create table t_key(
a int(11),
b datetime
)
partition by key (b)
partitions 4;
-- PARTITION BY LINER key(store_id) 线性hash是2的幂运算（普通的是取）
-- 优点：在于增加、删除、合并和拆分分区将变得更加快捷
-- 缺点：常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。读写分离
主-从读写分离在该文章中实现：
http://blog.csdn.net/u012442381/article/details/77141781
1434
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
