数据库优化方案

InnoDB引擎的4大特性
插入缓冲（insert buffer)
二次写(double write)
自适应哈希索引(ahi)
预读(read ahead)
存储引擎选择
MySQL默认的Innodb引擎。

MyISAM：以读写插入为主的应用程序，比如博客系统、新闻门户网站。
Innodb：增删改操作频率高，或者要保证数据的完整性；并发量高，支持事务和外键。比如OA自动化办公系统。

SQL的生命周期？
应用服务器与数据库服务器建立一个连接
数据库进程拿到请求sql
解析并生成执行计划，执行
读取数据到内存并进行逻辑处理
通过步骤一的连接，发送结果到客户端
关掉连接，释放资源

SQL优化
MySQL提供了explain命令来查看语句的执行计划，定位性能低的SQL,是最有效最重要的方式。

对于查询语句，最重要的优化方式就是使用索引， 通过执行计划，就可以显示数据库引擎对于SQL语句的执行的详细情况，包含是否使用索引，使用什么索引，使用的索引的相关信息等。

大表数据查询，怎么优化
优化shema、sql语句+索引；
第二加缓存，memcached, redis；
主从复制，读写分离；
垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

超大分页怎么处理？
解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可。

mysql 分页
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？
首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。
主键使用自增ID还是UUID？
推荐使用自增ID，不要使用UUID。因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序)，如果主键索引是自增ID，那么只需要不断向后排列即可，如果是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，数据移动，然后导致产生很多的内存碎片，进而造成插入性能的下降。
总之，在数据量大一些的情况下，用自增主键性能会好一些。
关于主键是聚簇索引，如果没有主键，InnoDB会选择一个唯一键来作为聚簇索引，如果没有唯一键，会生成一个隐式的主键。

字段为什么要求定义为not null？
null值会占用更多的字节，且会在程序中造成很多与预期不符的情况。

如果要存储用户的密码散列，应该使用什么字段进行存储？
密码散列，用户身份证号等固定长度的字符串应该使用char而不是varchar来存储，这样可以节省空间且提高检索效率。

优化查询过程中的数据访问
访问数据太多导致查询性能下降。
确定应用程序是否在检索大量超过需要的数据，可能是太多行或列。
确认MySQL服务器是否在分析大量不必要的数据行。

避免犯如下SQL语句错误
查询不需要的数据。解决办法：使用limit解决
多表关联返回全部列。解决办法：指定列名
总是返回全部列。解决办法：避免使用SELECT *
重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存
是否在扫描额外的记录。解决办法：

使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：
使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。
改变数据库和表的结构，修改数据表范式
重写SQL语句，让优化器可以以更优的方式执行查询。

优化长难的查询语句
MySQL内部每秒能扫描内存中上百万行数据，相比之下，响应数据给客户端就要慢得多。

使用尽可能小的查询是好的，有时将一个大的查询分解为多个小的查询是很有必要的。

切分查询
将一个大的查询分为多个小的相同的查询
一次性删除1000万的数据要比一次删除1万暂停一会的方案更加损耗服务器开销。

分解关联查询，让缓存的效率更高。

执行单个查询可以减少锁的竞争。

在应用层做关联更容易对数据库进行拆分。

较少冗余记录的查询。

优化特定类型的查询语句
count()会忽略所有的列，直接统计所有列数，不要使用count(列名)
MyISAM中，没有任何where条件的count()非常快。
当有where条件时，MyISAM的count统计不一定比其它引擎快。
可以使用explain查询近似值，用近似值替代count(*)。
增加汇总表。
使用缓存。

优化关联查询
确定ON或者USING子句中是否有索引。
确保GROUP BY和ORDER BY只有一个表中的列，这样MySQL才有可能使用索引。

优化子查询
用关联查询替代
优化GROUP BY和DISTINCT
这两种查询据可以使用索引来优化，是最有效的优化方法
关联查询中，使用标识列分组的效率更高
如果不需要ORDER BY，进行GROUP BY时加ORDER BY NULL，MySQL不会再进行文件排序。
WITH ROLLUP超级聚合，可以挪到应用程序处理

优化LIMIT分页
LIMIT偏移量大的时候，查询效率较低
可以记录上次查询的最大ID，下次查询时直接根据该ID来查询

优化UNION查询
UNION ALL的效率高于UNION

对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

select id from t where num is null
– 避免在 where 子句中对字段进行 null 值判断，可以在num上设置默认值0，确保表中num列没有null值
select id from t where num=

尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描。

select id from t where num=10 or num=20
– 尽量避免在 where 子句中使用or 来连接条件
select id from t where num=10 union all select id from t where num=20

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

会进行全表扫描：select id from t where name like ‘%李%’若要提高效率，可以考虑全文检索。

select id from t where num=@num
– 可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

select id from t where num/2=100
– 尽量避免在 where 子句中对字段进行表达式操作:
select id from t where num=100*2

where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

数据库优化

优化原则：减少系统瓶颈，减少资源占用，增加系统的反应速度。

系统吞吐量的瓶颈一般是出现在数据库的访问速度上面。
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢。
数据是存放在磁盘上的，读写速度无法和内存相比。

数据结构优化
减少数据冗余，查询速度，更新速度， 字段数字类型
将字段多的表进行拆分
当一个表数据很大，然后有使用频率低的字段存在，会导致表的查询变慢。

所以将表中使用频率低的字段分离出来成一个新表，对原表进行操作将会变快。
建立中间表
将经常需要进行联合查询的数据插入到中间表，然后将原来的联合查询修改为对中间表的查询，性能得到提升。
增加冗余字段
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差，但是通过增加冗余字段就可以避免连接查询，性能得到提升，空间换时间。
主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

主从复制的作用
主数据库出现问题，可以切换到从数据库。
可以进行数据库层面的读写分离。
可以在从数据库上进行日常备份。

MySQL主从复制解决的问题
数据分布：随意开始或停止复制，并在不同地理位置分布数据备份
负载均衡：降低单个服务器的压力
高可用和故障切换：帮助应用程序避免单点失败
升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理
在主库上把数据更高记录到二进制日志
从库将主库的日志复制到自己的中继日志
从库读取中继日志的事件，将其重放到从库数据中