Mysql优化方案
优化Mysql有几个方向？
- 从sql语句入手
- 考虑索引
- 表结构的优化
- 数据库（配置）
sql方面可以做什么优化？
1.聚合函数的优化：
对于max():在作用列上加索引
对于count()：也可以通过加索引增加sql的执行速度，但需要注意的是，count(列名)会屏蔽掉null值，而count(*)并不会;
2.子查询优化:
子查询通常性能低于连接查询，考虑将其改为连接查询：
例子：
select title from film where film_id in ( select film_id from actor where actor_id in ( select actor_id from actor where name = 'sandra'));
改为:
select a.title from film a join actor b on a.film_id = b.film_id where b = actor, name = 'sandra';
3.group by优化
针对group by后底层使用的临时表和文件排序进行优化；
select actor.first, actor.last, count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film.actor_id;
存在使用临时表，文件排序；
select actor.first, acotor.last, c.cut from sakila.actor inner join ( select actor_id, count(*) as cut from sakila.film_actor group by actor_id) as c using(actor_id);
4.limit优化
select film_id, description from sakila.film order by title limit 50,5;
存在耗性能的文件排序；
select film_id, description from sakila.film order by film_id limit 50,5;
对有索引的列进行order by，省去了文件排序的消耗；
索引方面？
1.在合适的列上建立索引
where, group by, order by, on 等词缀常出现的列上建立索引；
2.索引字段越小越好
3.离散度越大的列，在联合索引越靠前
离散度？ 答：唯一值越多的列，离散度越好;
4.删除冗余的索引
如：主键上的索引（因为主键就是唯一索引）
多个联合索引前缀列相同
联合索引包含了主键索引
TIP：安利一个查找冗余索引的语句：
SELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名' FROM information_schema.statistics a JOIN statistics b ON a.table_schema = b.table_schema AND a.table_name = b.table_name AND a.seq_in_index = b.seq_in_index AND a.column_name = b.column_name WHERE a.seq_in_index = 1 AND a.index_name != b.index_name
表结构的优化
1.选择合适的数据类型
能存下的最小的
使用最简单的
尽可能使用not null定义字段
尽量少用text这种，非用不可最好分表
2.用int来存放日期
create table test( id not AUTO_INCREMENT not null, timestr INT Primary key (id) ); INSERT into test(timestr) values ( UNIX_TIMESTAMP('2014-06-01 13:01:04') ); select FROM_UNIXTIME(time) from test;
3.用bigint存放IP
create table sessions ( id int auto_increment not null, ipaddress, bigint, primary key(id) ); insert into sessions ( ipaddress values ( INET_ATON('192.168.0.1') ); select INET_NTOA ( ipaddress) from sessions;
范式化设计
简单提一下范式：
第一范式：有主键且非空不重复，单个列不可分；
第二范式：满足第一范式的条件下，消除部分函数依赖；
第三范式：满足第二范式的条件下，消除了传递依赖；
反范式化：
适当增加冗余的列，优化查询（空间换时间）
表的拆分
表的垂直拆分：
不常用的字段放入一个表中；
大字段独立出去；
经常使用的字段放一起；
表的水平拆分：
对id进行hash， mod(id, 5)
不同id的存入不同的表中
数据库系统的优化
1.linux 修改 /etc/sysctl.conf
#增加tcp支持队列数
net.ipv4.tcp_max_syn_backlig = 65535
#减少断开连接，资源回收
net.ipv4.tcp_max_tw_buckets = 8000
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_fin_timeout = 10
2.打开文件数的限制
ulimit -a查看目录限制
修改文件 /etc/security/limits.conf
写入 *soft nofile 65535
*hard nofile 65535
关闭 iptables, selinux 等防火墙(减少网络损耗)
3.修改mysql的配置
linux对应目录: /etc/my.conf 或 /etc/mysql/my.conf
win对应目录: C://windows/my.ini
重要参数的修改:
innodb_buffer_pool_size = 75%* total RAM
innodb_buffer_ poll_instances
innodb_log_buffer_size (保证存下一秒钟的日志即可)
innodb_flush_log_at_trx_commit (默认是1，可以取0，1，2，表示多久将变更刷新至硬盘)
innodb_read_io_threads (默认是4，引擎读io的线程数)
innodb_write_io_threads (默认是4，引擎写io的线程数)
innodb_file_per_table (默认是off，控制引擎每个表使用的独立表空间)
innodb_stats_on_metadata (决定什么情况下刷新表的统计信息，设置为off就不会自动刷新了)
处理重复数据？
原因：人为的重复录入，重复提交等；
查询到重复数据：
利用group by和having的组合:
select user, over, count(*) from user1 group by user having count(*) > 1;
再删除即可
