MySQL优化

数据库设计

选取合适的数据类型

数字型：

类型	大小	有符号范围	无符号范围
TINYINT	1 字节	(-128，127)	(0，255)
SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)
MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)
INT	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)
FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	M为总长度，D为小数点右边长度

字符类型：

CHAR	0-255字节	定长字符串
VARCHAR	0-255字节	变长字符串
TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65 535字节	二进制形式的长文本数据
TEXT	0-65 535字节	长文本数据
MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295字节	极大文本数据

查询

尽量使用连接（JOIN）来代替子查询(Sub-Queries)，连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
使用联合(UNION)来代替手动创建的临时表，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。要注意的是所有select语句中的字段数目要想同
```
SELECT   name,phone  FROM  client UNION
SELECT  name,birthdate  FROM  author  UNION
SELECT  name,supplier FROM product
```

索引

索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。含有MAX(),MIN()这些命令。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况
最好是在相同类型的字段间进行比较的操作
在建有索引的字段上尽量不要使用函数进行操作

合理使用like

SELECT  *  FROM  books  WHERE  name  like   "MySQL%"  ->
SELECT  *  FROM  books  WHERE  name ＞=  "MySQL"  and  name  ＜"MySQM"

避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用
(area, age, salary)复合索引，相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性,最左边要选用最常用的字段
数据库设计时不要让字段的默认值为NULL，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的
mysql查询只使用一个索引，所以数据库默认排序可以符合要求的情况下不要使用排序操作
一般不用like，非用不可也有技巧，like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

不要在列上进行运算

select * from users where YEAR(adddate)<2007;  ->
select * from users where adddate<‘2007-01-01';

不使用NOT IN和<>操作,他们不会使用索引，会进行全盘扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。
in和exists子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

SQL语句

不要使用 count(列名)或 count(常量)来替代 count( * ), count( * )会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。
count(distinct col) 计算该列除 NULL 之外的不重复行数，注意 count(distinct col1, col2) 如果其中一列全为 NULL，那么即使另一列有不同的值，也返回为 0。
当某一列的值全是 NULL 时，count(col)的返回结果为 0，但 sum(col)的返回结果为 NULL，因此使用 sum()时需注意 NPE 问题。(可以这样写SELECT IFNULL(SUM(column), 0) FROM table;)
使用 ISNULL()来判断是否为 NULL 值（NULL 与任何值的直接比较都为 NULL），ISNULL(column)是一个整体，简洁易懂
代码中写分页查询逻辑时，若 count 为 0 应直接返回，避免执行后面的分页语句
禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
对于数据库中表记录的查询和变更，只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定

SQL 语句中表的别名前加 as，并且以 t1、t2、t3、…的顺序依次命名。

1）	别名可以是表的简称，或者是依照表在 SQL 语句中出现的顺序，以 t1、t2、t3 的方式命名。
2）	别名前加 as 使别名更容易识别。

SQL查询慢，可能存在的解决方案：

set profiling=on;show profiles;show profile for query 1;
看看详细耗时,查看SQL哪里耗时多
explain
查看索引，是否利用了索引查询，如果不符合预期索引，可以force index指定索引,如果走索引还是很慢，看看是不是索引区分度不够高
连接数大小
mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。
Mysql的最大连接数默认是100, 最大可以达到16384。
set global max_connections= 500;show variables like ‘max_connections’;
服务端配置了，客户端还需要配置连接数，一般都会用框架，查看文档看看如何配置最大连接数
存储引擎层的buffer pool
buffer pool用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。
所以把buffer pool调大，可能会加快速度。
show status like ‘Innodb_buffer_pool_%’;可以看到跟buffer pool有关的一些信息。
Innodb_buffer_pool_read_requests表示读请求的次数
Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。
命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%
一般低于99%就算命中率低

优化MySQL有哪些技巧

创建索引

为了提升查询效率创建索引，空间换时间，但更新时会调整索引树，影响效率
根据二八原则，互联网请求读多写少，需要找到一个平衡点
阿里巴巴的开发者手册建议，单表索引数量控制在 5 个以内，组合索引字段数不允许超过 5 个。
注意：

组合索引的字段的顺序。
where子句中使用最频繁的一列放在最左边。
优先考虑覆盖索引
需要查询的字段能够在索引树里面拿到，不需要回表。
避免使用外键约束。
每次添加数据，都会进行外键约束检查，影响性能
外键约束会启用行级锁，主表写入会阻塞
数据耦合严重，多层级联删除不可控，一条记录影响到多条记录

避免索引失效

常见失效场景

以 % 开头的 LIKE 查询。
创建了组合索引，但查询条件不满足 '最左匹配原则'。如：创建索引 idx_type_status_uid（type,status,uid），但是使用 status 和 uid 作为查询条件。
查询条件中使用 or，且 or 的前后条件中有一个列没有索引，涉及的索引都不会被使用到。
在索引列上的操作，函数 upper() 等，or、!= （<>）、not in 等。