关于“select count(*)”和“select count(col)”的效率比较(参考http://hi.baidu.com/wrjgg/item/f5b823b4e1dca79918469729):
1.任何情况下SELECT COUNT(*) FROM tablename是最优选择;
2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = 'value’ 这种查询;
3.杜绝SELECT COUNT(COL) FROM tablename的出现
1.关于多列合在一起作为unique key的情况需要注意:如果columnA、columnB、columnC合在一起作为unique key,那么三项都不要“可为空”,因为如果columnA可为空,则会出现多条columnA为空、columnB和columnC分别相等的记录(数据库不报错)
2.使用--log-slow-queries –-long-query-time=2查看查询比较慢的语句。然后使用explain分析查询,做出优化(log_queries_not_using_indexes
记录未使用索引的查询)
3.尝试避免在频繁更新的表上执行复杂的SELECT查询,以避免与锁定表有关的由于读、写冲突发生的问题
4.尽量不要让列可为null,不利于mysql优化
5.对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。
如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。
但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。
6.建索引的几个要点:
1>复合索引
比如有一条语句是这样的:select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边,依次递减。
2>索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3>使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的 列,如果在前10 个或20 个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
4>排序的索引问题
mysql查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
5>like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6>不要在列上进行运算
select * from users where YEAR(adddate) ...
7>不使用NOT IN
NOT IN不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。
7.主键和唯一索引的区别(django1.4还不支持composite primary key,有一些扩展包提供该功能,但是他们建立的所谓的“组合主键”到了数据库层面居然是唯一索引,所以才对这两个概念有些迷惑)
主键是一种约束,唯一索引是一种索引,两者在本质上是不同的。
主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。
唯一性索引列允许空值,而主键列不允许为空值。
主键列在创建时,已经默认为空值 + 唯一索引了。
主键可以被其他表引用为外键,而唯一索引不能。
一个表最多只能创建一个主键,但可以创建多个唯一索引。
主键更适合那些不容易更改的唯一标识,如自动递增列、身份证号等。
在 RBO 模式下,主键的执行计划优先级要高于唯一索引。 两者可以提高查询的速度。
8.innodb表修改索引(已验证的是删索引)会很慢,myisam表就很快(show processlist跟踪到的过程:copy to tmp table; repair by sorting; rename table)
9.innodb表的数据不在mysql数据目录下:mysql数据目录下只有一个非常小(十几KB左右)的frm文件,没有MYD文件和MYI文件 (http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html)
InnoDB
creates tablespace files in the MySQL data directory by default. To specify a location explicitly, use theinnodb_data_home_dir
option. For example, to use two files namedibdata1
andibdata2
but create them in the/ibdata
directory, configureInnoDB
like this:
[mysqld] innodb_data_home_dir = /ibdata innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend
10.select * from a left join b on a.id = b.id; 左联接,结果以a.*,b.*显示,左侧(a.*)保留,右侧(b.*)用NULL填充
select * from a right join b on a.id = b.id; 右联接,结果以a.*,b.*显示,左侧(a.*)用NULL填充,右侧(b.*)保留
select * from a inner join b on a.id = b.id; 内联接(相等联接),等价于select * from a,b where a.id = b.id;
11.select * from a where a.id > 1 and a.id < 10; select * from a where a.id < 10 and a.id > 1; 后者效率比较高,因为id<10把范围缩小了,整个执行会更快
12.mysql分区和分表(merge存储引擎,或者业务层面实现)的侧重点不同,分表重点是存取数据时,如何提高mysql并发能力上;而分区呢,如何突破磁盘的读写能力,从而达到提高mysql性能的目的。http://blog.51yip.com/mysql/1029.html
分表:业务层面控制分表、merge存储引擎分表http://blog.51yip.com/mysql/949.html
分区:range分区、list分区、hash分区、key分区、子分区 http://blog.51yip.com/mysql/1013.html
- ./configure --help |grep -A 3 Partition
- mysql> show variables like "%part%";
- +-------------------+-------+
- | Variable_name | Value |
- +-------------------+-------+
- | have_partitioning | YES |
- +-------------------+-------+
- 1 row in set (0.00 sec)
- range分区:
- CREATE TABLE IF NOT EXISTS `user` (
- `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
- `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
- `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女',
- PRIMARY KEY (`id`)
- ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1
- PARTITION BY RANGE (id) (
- PARTITION p0 VALUES LESS THAN (3),
- PARTITION p1 VALUES LESS THAN (6),
- PARTITION p2 VALUES LESS THAN (9),
- PARTITION p3 VALUES LESS THAN (12),
- PARTITION p4 VALUES LESS THAN MAXVALUE
- );
- list分区:(如果有主銉的话,分区时主键必须在其中,不然就会报错。一般情况下,一个张表肯定会有一个主键,这算是一个分区的局限性吧。)
- CREATE TABLE IF NOT EXISTS `list_part` (
- `id` int(11) NOT NULL COMMENT '用户ID',
- `province_id` int(2) NOT NULL DEFAULT 0 COMMENT '省',
- `name` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',
- `sex` int(1) NOT NULL DEFAULT '0' COMMENT '0为男,1为女'
- ) ENGINE=INNODB DEFAULT CHARSET=utf8
- PARTITION BY LIST (province_id) (
- PARTITION p0 VALUES IN (1,2,3,4,5,6,7,8),
- PARTITION p1 VALUES IN (9,10,11,12,16,21),
- PARTITION p2 VALUES IN (13,14,15,19),
- PARTITION p3 VALUES IN (17,18,20,22,23,24)
- );
13.like做模糊搜索效率非常低下(遍历所有记录),需要的话可用全文索引替换:http://db.090.net/2012/03/mysql%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95/
14.查询的时候只好准确提供符合列类型的数据做检索,不要寄希望于Mysql很智能的检查和转换我们提供的数据
- mysql> show create table wom_account\G
- *************************** 1. row ***************************
- Table: wom_account
- Create Table: CREATE TABLE `wom_account` (
- ...
- `provider` smallint(6) NOT NULL,
- `uid` varchar(64) NOT NULL,
- UNIQUE KEY `provider` (`provider`,`uid`),
- ) ENGINE=MyISAM AUTO_INCREMENT=2518615 DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
- mysql> explain * FROM wom_account WHERE provider=1 AND uid IN ('2840978914');
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- | 1 | SIMPLE | wom_account | const | provider,uid | provider | 196 | const,const | 1 | |
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- 1 row in set (0.00 sec)
- mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid IN (2840978914);
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- | 1 | SIMPLE | wom_account | ref | provider,uid | provider | 2 | const | 489995 | Using where |
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- 1 row in set (0.00 sec)
- mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = 2840978914;
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- | 1 | SIMPLE | wom_account | ref | provider,uid | provider | 2 | const | 489995 | Using where |
- +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+
- 1 row in set (0.00 sec)
- mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = '2840978914';
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- | 1 | SIMPLE | wom_account | const | provider,uid | provider | 196 | const,const | 1 | |
- +----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+
- 1 row in set (0.00 sec)
下面参考http://www.tyhs.net.cn/article.asp?articleid=64
http://blog.csdn.net/maria57/article/details/2963410(这篇文章分析很详细,innodb和myisam不同引擎的设置也都有讲到)
索引
1) MySQL只会使用前缀,例如key(a, b) …where b=5 将使用不到索引。
2) 要选择性的使用索引。在变化很少的列上使用索引并不是很好,例如性别列。
3) 在Unique列上定义Unique index。
4) 避免建立使用不到的索引。
5) 在Btree index中(InnoDB使用Btree),可以在需要排序的列上建立索引。6) 避免重复的索引。
7) 避免在已有索引的前缀上建立索引。例如:如果存在index(a,b)则去掉index(a)。
8) 控制单个索引的长度。使用key(name(8))在数据的前面几个字符建立索引。
9) 越是短的键值越好,最好使用integer。
10) 在查询中要使用到索引(使用explain查看),可以减少读磁盘的次数,加速读取数据。
11) 相近的键值比随机好。Auto_increment就比uuid好。
12) Optimize table可以压缩和排序index,注意不要频繁运行。
13) Analyze table可以更新数据。
14)对于已建索引的字符串类型列如果用数值类型做查询(where columnName = 100)索引就用不上,数据量大的时候查询异常缓慢
服务器设置优化
MySQL默认的设置性能很差,所以要做一些参数的调整。这一节介绍一些通用的参数调整,不涉及具体的存储引擎(主要指MyISAM,InnoDB)
--character-set:如果是单一语言使用简单的character set例如latin1。尽量少用Utf-8,utf-8占用空间较多。
--memlock:锁定MySQL只能运行在内存中,避免 swapping,但是如果内存不够时有可能出现错误。
--max_allowed_packet:要足够大,以适应比较大的SQL查询,对性能没有太大影响,主要是避免出现packet错误。
--max_connections:server允许的最大连接。太大的话会出现out of memory。
--table_cache:MySQL在同一时间保持打开的table的数量。打开table开销比较大。一般设置为512。
如果你发现open_tables等于table_cache,并且opened_tables在不断增长,那么你就需要增加table_cache的值了(上述状态值可以使用SHOW STATUS LIKE ‘Open%tables’获得)
--query_cache_size: 用于缓存查询的内存大小。
使用查询缓冲,MySQL将SELECT语句和查询结果存放在缓冲区中,今后对于同样的SELECT语句(区分大小写),将直接从缓冲区中读取结果。
根据MySQL用户手册,使用查询缓冲最多可以达到238%的效率。
通过检查状态值Qcache_*,可以知道query_cache_size设置是否合理(上述状态值可以使用SHOW STATUS LIKE ‘Qcache%’获得)。
如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况,如果Qcache_hits的值也非常大,则表明查询缓冲使用非常频繁,此时需要增加缓冲大小;
如果Qcache_hits的值不大,则表明你的查询重复率很低,这种情况下使用查询缓冲反而会影响效率,那么可以考虑不用查询缓冲。
此外,在SELECT语句中加入SQL_NO_CACHE可以明确表示不使用查询缓冲。
与查询缓冲有关的参数还有query_cache_type、query_cache_limit、query_cache_min_res_unit。
query_cache_type指定是否使用查询缓冲,可以设置为0、1、2,该变量是SESSION级的变量。
query_cache_limit指定单个查询能够使用的缓冲区大小,缺省为1M。
query_cache_min_res_unit是在4.1版本以后引入的,它指定分配缓冲区空间的最小单位,缺省为4K。
检查状态值Qcache_free_blocks,如果该值非常大,则表明缓冲区中碎片很多,这就表明查询结果都比较小,此时需要减小query_cache_min_res_unit。
--datadir:mysql存放数据的根目录,和安装文件分开在不同的磁盘可以提高一点性能。
--key_buffer_size:指定索引缓冲区的大小,它决定索引处理的速度,尤其是索引读的速度。通过检查状态值Key_read_requests和Key_reads,可以知道key_buffer_size设置是否合理。
比例key_reads / key_read_requests应该尽可能的低,至少是1:100,1:1000更好(上述状态值可以使用SHOW STATUS LIKE ‘key_read%’获得)。
key_buffer_size只对MyISAM表起作用。即使你不使用MyISAM表,但是内部的临时磁盘表是MyISAM表,也要使用该值。可以使用检查状态值created_tmp_disk_tables得知详情。
对于1G内存的机器,如果不使用MyISAM表,推荐值是16M(8-64M)。
MyISAM优化要点
1) 声明列为NOT NULL,可以减少磁盘存储。
2) 使用optimize table做碎片整理,回收空闲空间。注意仅仅在非常大的数据变化后运行。
3) Deleting/updating/adding大量数据的时候禁止使用index。使用ALTER TABLE t DISABLE KEYS。
4) 设置myisam_max_[extra]_sort_file_size足够大,可以显著提高repair table的速度。
项目中sql优化时的会议记录:---> start
子查询尽量要避免,因为子查询的结果会存成临时表,而临时表是无法加索引的。
临时表只能逐行过滤
下面两种写法效果等同(“inner join”没有"left"和“right”的语法,它就是“inner join”)
select * from a inner join b on a.y = b.y;
select * from a,b where a.y = b.y;
"left join"默认是"left outer join" (outer,不是out)
左外连接,如果 a left out join b,那么如果在a中存在的记录如果在b中不存在,则b中对应的列的值为NULL,通过选择b中列为null的行就把结果选出来了
多个连接条件:http://dev.mysql.com/doc/refman/5.0/en/join.html
SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
查看数据库版本:
SELECT version();
SELECT @@version;
《High.Performance.MySQL》(高性能MYSQL)需要阅读的章节:1、4、5、6、7
1. client-server 结构
一个客户连接在server端由一个线程处理(5.5的优化:线程池,一个线程可以服务于多个客户端)
Query cache(sql语句缓存), Parser
读写锁(写锁饥饿:写操作因为接连的读操作而拿不到锁,解决办法是序列化请求锁)
2. 事务,ACID(原子性、一致性、隔离性、持久性)
隔离级别:
READ UNCOMMITED, 可读到没提交的数据(脏数据),隔离级别最低
READ COMMITED, 只读他人提交后的数据(同一个事务中两次相同读取的时间之间他人可以更新该数据)
REPEATABLE READ, 可重复读(两次读取的数据一致,同一个事务中两次相同读取的时间之间他人不可以更新该行数据,但是可能会增加新的行)(幻读,phantom read, range select, find new records)
T1:
read R1
... Tn: UPDATE R1(N) INSERT R2(Y)
read R1
SERIALIZABLE, 序列化
T1:
read R1
... Tn: UPDATE R1(N) INSERT R2(N)
read R1
3. MVCC
R1: data (insert version; delete version)
R1
R2
R3
...
Rn
insert version <= current_version
delete version == undifined OR delete version > current_version
4.SHOW TABLE STATUS LIKE 'user'; 查看表user的状态
5.select count(*) from a; 没有where条件且引擎是MYISAM时速度很快,数据表有字段跟踪行数
select count(id) from a; 统计的是id不为空的行数
6.推荐用Oracle InnoDB替代开源社区的InnoDB(5.5后默认是前者)
7.varchar(5)是5个字符,不是5个字节(5个字符可能是15个字节...)
8.MYISAM修复比较慢,因为没有日志要扫描整个文件;MYISAM索引是压缩的,比较小一些;表级锁
INNODB可以热备、支持事务、恢复快,行级锁
9.程序性能瓶颈:CPU 磁盘IO 内存 网络IO
10.create table b like a; 创建和a一样结构的新表b
11.用B+树存索引而不是用二叉树:优化磁盘读写
12.聚集索引(Clustered Indexes):数据和索引存在一起,找到索引就找到了数据
inno DB支持聚集索引,但只支持对primary key做聚集索引
InnoDB:(如果primary key很大的话索引文件就会很大、占内存空间和磁盘空间;primary key需要尽量按照递增的顺序产生,不要随机生成)
primary key: index + data
second index: index + primary key
MyISAM:
primary key: index + pointer to data
second index: index + pointer to data
13.对order by的列设置了索引,数据从磁盘读出来的就会变成有序的,不需要再在内存里面做排序
14. replace into
insert into t values (..) on duplicate key update ...
15.union 和 union all,再确定不重复的情况下最好用后者(无排序和排重操作,速度快)