优化Mysql有几个方向?
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_nameWHERE 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;
再删除即可