MySQL高性能应用

高性能，就是响应时间短，主要影响的是执行时间和等待时间。

一、表设计

（一）选择合适的数据结构

1、DECIMAL VS BIGINT

DECIMAL	BIGINT
DECIMAL(M,D)指定精度，M是数字最大位数，范围1-65；D是小数点右侧数字个数，范围0-30，但不得超过M	-9223372036854775808 到 +9223372036854775807
可变长	定长，8 字节
精度高	精度低

2、VARCHAR VS CHAR

VARCHAR	CHAR
可变长，支持到65535	定长，长度范围1-255
需要1-2字节的额外空间，记录长度	无额外空间
适合字段更新少的	适合经常变动的数据，不易产生碎片

3、DATE && DATETIME && TIMESTAMP && TIME

	DATE	DATETIME	TIMESTAMP	TIME
格式	‘YYYY-MM-DD’	‘YYYY-MM-DD HH:MM:SS’		‘HH:MM:SS’
有无时区	无’-838:59:59’ to ‘838:59:59’	无	有	无
范围	‘1000-01-01’ to ‘9999-12-31’	‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’	‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC	‘-838:59:59’ to ‘838:59:59’

（二）表范式平衡

1、范式化的更新操作通常比反范式化要快

2、反范式化的设计可以很好的避免关联（互联网更注重单查询的速度）

3、控制单表的字段个数(建议控制在20个以内

4、缓存表和汇总表可以利用

二、索引设计

索引:是存储引擎用于快速找到记录的一种数据结构

（一）索引结构

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。

B-Tree树

B+Tree树

B+Tree是B-Tree的变种。MySQL的InnoDB引擎就使用B+Tree实现其索引结构。

（二）索引设计

由于InnoDB是使用最广的引擎，就以此为例。

1、指定主键。引擎会选择主键作为索引列，利用聚簇索引管理(组织)表数据；如果没有主键，则会选择一个唯一的非空索引代替，如果也没有，会隐式定义一个主键。

随机是非常坑的，所以一定要指定主键！为什么坑？？？——对于冷热数据来说，查找的范围是有顺序的；随机会导致频繁页分裂，不规则的填充，导致碎片。

2、数量合理。索引太多会导致更新速度下降。

3、把区分度最高的尽量放到最左侧。

4、不要在索引列做运算

5、尽量不要在索引上执行LIKE操作

6、不要在选择性不高的列添加索引

7、多条件查询使用多列索引，而不要设置多个单列索引

8、前缀索引，超过20个长度的最好不要用整列索引而是采用 前缀索引(因为六个字母的区分度已经很大很大了) 如：KEY ‘idex_pinyin’ (‘pinyin’(8))

三、查询SQL语句设计

1、使用多条简单SQL语句替代复杂SQL语句

复杂SQL更难维护，响应时间更长；

使用简单SQL减少锁的时间。

不建议进行两个表以上的JOIN。

2、尽量不用 SELECT * ，只取需要的数据列

3、避免负向查询
• NOT，!=，<>，!<，!>，NOT EXISTS，NOT IN，NOT LIKE等

4、避免%前缀模糊查询

%xxx% 的速度明显小于 xxx%

（负向查询和前缀模糊查询使用不了索引，导致全表扫描）

5、慎用COUNT()

统计行数、统计某个列的非空值数量，导致全表扫描

6、分页查询

尽量不要使用 SELECT * FROM TABLE LIMIT 10000,10;

而用 SELECT ID FROM T WHERE ID >10000 LIMIT 10;