MySQL实践总结_mysql实践结论

---

1.Mysql表的快速复制

create table new_table select * from old_table
create table new_table like old_table insert new_table select * from old_table 
1
2

这两种方法的特点：第一种方法需要手动添加主键、索引等。第二种方法是完全复制，推荐这种方法。

此外，如果想从另外表导入数据：

Insert into table1(col1,col2,…) Select col1,  col2,… from table2
1

2.分页优化

分页在实际的项目中应用的十分广泛， 但是当数据量大时， 其效率问题令人担忧。先看下我们通常采用的分页语句：

Select * from table where …. Order by X limit start, size
Select * from table where …. Order by X limit 10000, 10
1
2

随着start的增大，查询的效率越差， 需要进一步优化。

优化的方式主要有两种方式 1：子查询 2：连接查询

Select * from (Select * from table where id>(select id from table order by id desc limit 10000, 1) limit 10) order by id desc

SELECT * FROM table INNER JOIN ( SELECT id FROM table ORDER BY id DESC LIMIT 10000,10) t2 USING (id)
1
2
3

3.排名

用存储过程实现计算某用户的排名

    CREATE  PROCEDURE `calc_ranks `(username varchar(50)
    BEGIN
      SET @username = username;
      PREPARE STMT FRO
     'select CAST(ranking.rank AS SIGNED)
     from (
     select @x := @x + 1 as rank, rd.username, rd.total
     from (   select a.username, a.total
       from table1 a left join table2 b on a.username=b.user_name
       where b.is_active = 1 and b.is_enabled = 1 
       order by a.total desc, b.nick_name asc
      ) rd,  (select @x := 0) r
     ) ranking
     where ranking.username = ?';
      EXECUTE STMT USING @username;
    END
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4.MySql 备份

• 导出整个数据库

  • Mysqldump –u 用户名 –p 密码 数据库名 〉导出的文件名

• 导出一个表

  • Mysqldump –u 用户名 –p 密码 表名 〉导出的文件名

5.Mysql性能优化最佳实践

• 为查询缓存优化自己的查询
• 用explain查看自己的select
• 只查询一条请使用limit 1
• 使用ENUM
• 永远为每个表设置ID
• 避免select *
• 为搜索字段加索引
• 尽量适应not null
• IP地址存成unsigned int, INET_ATON:将字符串转成整形;INET_NTOA() 把一个整形转成一个字符串IP

参考

• 高性能MySQL(第3版)