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_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;

  再删除即可

 

相关文章