Mysql优化——建表优化和查询优化

建表前选择合适的引擎

常见的引擎有MyISAM、Innodb、MEMORY、MERGEE、CSV。
一般在MyISAM和InnoDB之间选择。
理由：MyISAM和InnoDB使用的最多，社区环境最好，利于以后的维护。

区别：

MyISAM读快，写慢。不直吹事务、外键，支持全文索引，表锁。

InnoDB读慢，写快。支持事务、外键，支持行锁，适合高并发。默认引擎。（r如无特殊需求，强烈建议选择他，万一哪天需要用到事务呢！！！）

建表优化

• 为每个数据表建主键
• 尽量把字段设置为NOT NULL，null值很难查询优化且占用额外的索引空间，推荐默认数字0代替null。
• 字段的长度尽量选短小的。能用tinyint不要用smallint。
• 尽量选择数字类型。如存时间可以用int存时间戳就不要用DATETIME；IP地址也是如此。
• 单表不要有太多字段，建议在20以内。根据更新的频繁程度来分割大表（垂直分割),如用户信息表分割成用户表和用户地址表
• 结合查询情况加索引
• 频繁join查询的表考虑增加冗余字段

关于加索引的注意点：

1. 给散列度高的字段加索引，不要给性别这种低散列度的字段加索引
2. 给长列字段加索引要限制索引长度
3. 组合索引的触发条件是查询条件中必须有第一个字段

查询优化

explain 检查索引的时间触发情况，让索引的命中率最大化，避免全表扫描

where、order by、group by、join中作为查询条件的字段考虑建索引。（注意数据大量重复的数据没有索引价值，如性别字段）

1. 联合索引的触发条件，索引组合中的第一个字段必须出现的查询条件中。

2. 尽量避免在where子句中对字段进行null值判断。在建表时就设置为非空

3. 尽量避免在where子句中使用!=和<>

4. 尽量避免在where子句中用or作为连接条件

5. 使用连接（join）代替子查

6. in和not in也要慎用

select id from t where num in(1，2，3)
　　对于连续的数值，能用 between 就不要用 in 了：
　　select id from t where num between 1 and 3

6. 如果where子句中使用参数，也会导致全表扫描

因为sql只有运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时，它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以强制查询使用索引：
select id from t with（index（索引名）） where num=@num

7. 尽量避免在where子句中对字段进行表达式操作或函数操作

如：
select id from where num/2=100
改成：
select id from where num=100*2

其他sql优化

1. 需要提取多少数据提多少，避免使用select *

2. 性能 count(id) > count(*) > count(字段)

3. 开启查询缓冲

补充：查看索引命中的方法

mysql> show global status like 'key_read%';
+------------------------+-------------+
| variable_name　　　　　　　　　　 | value　　　　　　　 |
+------------------------+-------------+
| key_read_requests　　　　　　 | 27813678764 |
| key_reads　　　　　　　　　　　　　　 | 6798830　　　　　 |
+------------------------+-------------+
一共有27813678764个索引读取请求，有6798830个请求在内存中没有找到直接从硬盘读取索引，计算索引未命中缓存的概率：

key_cache_miss_rate ＝ key_reads / key_read_requests * 100%