MySQL-优化

1、选择合适的存储引擎(推荐 InnoDB)

• InnoDB 缓存数据和索引，而MyISAM 仅仅缓存索引，且MyISAM 不支持事务
• 为每张表开启独立表空间(一张表一个ibdata文件)，优点如下：

1. 每个表都有自已独立的表空间
2. 每个表的数据和索引都会存在自已的表空间中
3. 可以实现单表在不同的数据库中移动
4. 对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理

// 查看当前表空间是共享表空间还是独立表空间
// ON 表示独立表空间 OFF表示共享表空间
show variables like 'innodb_file_per_table';

// 可以在my.ini配置文件中开启或者关闭，5.7版本默认是开启的
// 1表示开启独立表空间 0表示开启共享表空间
innodb_file_per_table = 1

注：当开启独立表空间时，因为为每张表都会生成对应的文件，所以需要调整 open_files_limit 的大小，

在my.ini配置文件中调整即可，具体调整规则这里不讨论

2、保证从缓存中读取数据

1. 推荐将数据全然保存在 innodb_buffer_pool_size ，即按存储量规划 innodb_buffer_pool_size 的容量

2. 怎样确保 innodb_buffer_pool_size 足够大

// 查看
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%';

住：如果 Innodb_buffer_pool_pages_free 为 0，则说明 buffer pool 已经被用光，须要增大 innodb_buffer_pool_size

3、降低磁盘写入操作

1. 合理设置写入缓存大小 innodb_log_file_size

2.  合理设置innodb_flush_log_at_trx_commit

// 推荐 innodb_log_file_size 设置为 0.25 * innodb_buffer_pool_size
innodb_log_file_size = 0.25 * innodb_buffer_pool_size

// 1表示每次改动写入磁盘，0或者2表示每秒写入磁盘
innodb_flush_log_at_trx_commit = 1

4、充分使用索引，避免全表扫描

• 避免使用 “select * from table”，因为它会进行全表扫描
• where子句中的列尽量被索引，但要注意，索引太多也会降低性能
• where子句中避免使用 is null 或 is not null 对字段进行判断
• where子句中避免使用 or来链接条件，可以使用 union all 或者 union
• where子句中避免使用 not in ，它不走索引，但 in 会走索引
• where子句中尽量使用 exists 代替 in
• where子句中避免对字段进行函数操作
• like模糊查询时，通配符避免放前面，如'%hjibo'，这样不会走索引，如果放后面会走索引，如'hjibo%'
• join 子句里面的列尽量被索引
• order by 的列尽量被索引
• 比较运算符能用 “=”就不用“<>”，=”增加了索引的使用几率
• 将非”索引”数据分离，比方将大篇文章分离存储，不影响其它自己主动查询。

5、其他注意事项

• 设计表时，为列选择合适的数据类型
• 避免将字段默认值设为null，可以设置为0或者"0"
• 单条查询最后添加 limit 1，停止全表扫描
• 使用UNION ALL 代替 UNION，如果结果集允许重复的话，因为 UNION ALL 不去重，效率高于 UNION。
• 保持SQL语句前后一致，这样做的目的是为了充分利用查询缓冲
• 尽量不用 MYSQL 内置的函数。由于内置函数不会建立查询缓存
• 保证相同的查询语句在任何地方都一致，多一个空格都不行，大小写也要一致
• SQL语句避免写得太长，太过冗余
• SQL语句必须采用绑定变量，而不是直接写常量
• 数据类型的隐式转换对查询效率的影响
• 少用 distinct 和 order by，这些动作可以改在客户端执行。它们增加了额外的开销
• 一次更新多条记录比分多次更新每次一条快,就是说批处理好
• 将数据插入到消息队列内，后台利用windows计划任务执行（5分钟执行一次）C#控制台程序将消息队列内的数据批量（消息队列内有50000条记录，一次性插入到数据表内）插入到相应的数据表内；