高性能MySQL小结

其实只看到了1/3,先记录一下,等以后有空了再补上。

将查询结果导出到文件

mysql > SQL QUERY INTO OUTFILE '/path/to/sql/file'

将每次操作都导出到文件

mysql > \T /path/to/file
mysql > ... //这些操作,及操作的结果都会输出到对应的文件
mysql > \t

文件内容大概会是这样

(root@localhost) [(none)]> use noah;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
(root@localhost) [noah]> show tables;
+----------------+
| Tables_in_noah |
+----------------+
| blog_category  |
| blog_comment   |
| blog_post      |
| blog_res       |
| blog_role      |
| blog_role_res  |
| blog_role_user |
| blog_tag       |
| blog_user      |
| test           |
+----------------+
10 rows in set (0.00 sec)

(root@localhost) [noah]> select * from test;
+----+------+
| id | name |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.02 sec)

显示MySQL当前状态及其他信息

SHOW STATUS; //显示MySQL当前状态
SHOW VARIABLES; //显示MySQL的变量信息,如version/data_dir等等
SHOW VARIABLES LIKE '%home%'; //获取包含home的变量
SHOW TABLE STATUS\G //显示当前表的状态,注意后面的\G,垂直显示结果
DESCRIBE tbl; //获取表结构
SHOW FULL COLUMNS FROM tbl; //类似上面

显示MySQL当前的连接状况

# 使用mysqladmin
mysqladmin processlist
# 或进入到mysql cli后执行
mysql > show processlist;

# 结果大概是这样
+-----+------+-----------+------+---------+------+-------+------------------+
| Id  | User | Host      | db   | Command | Time | State | Info             |
+-----+------+-----------+------+---------+------+-------+------------------+
| 409 | root | localhost | noah | Query   |    0 | NULL  | show processlist |
+-----+------+-----------+------+---------+------+-------+------------------+

关于TIMESTAMP

  • 第一个TIMESTAMP字段会随着表其他字段的更新而自动更新,之后的TIMESTAMP字段则不会。
  • TIMESTAMP的范围是:1970-2037;而DATETIME的范围是:1000-9999

移除重复的行

# 注意这个IGNORE参数,如果没加的话会报错,且执行失败
# 假设要去除a,b项重复的行
ALTER IGNORE TABLE tbl ADD UNIQUE INDEX(a,b);

查看当前在操作的数据库

SELECT DATABASE();

也可以在mysql的提示符上动点手脚

# edit /etc/mysql/my.cnf
[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition
prompt=(\\u@\\h) [\\d]> \\ 

复制一个表

CREATE TABLE tbl1 LIKE tbl;
INSERT INTO tbl1 SELECT * FROM tbl;
# 也可以先用mysqladmin导出数据,再导入

定长表与变长表

包含任何varchar、text等变长字段的数据表,即为变长表,反之则为定长表。所以CHAR和VARCHAR不共存

CHAR(30),最多可以容纳30个字符,但如果字符数不到30个的话,也会占用这些空间,只不过会在后面补上空格,但我们查询时又会发现尾部没有空格,这是因为空格已经被CHAR处理掉了。

VARCHAR(30),也是最多可以容纳30个字符,但如果不足30个的话,有多少字符占多少空间,不会浪费。

变长表的优势在于有效利用空间,但由于记录大小不同,在其上进行许多删除或更新操作会使表中的碎片增多,需要定期OPTIMIZE TABLE以保持性能。

定长表的查询,检索和更新速度都比变长表快,但占用的空间也大。

PS:MySQL 5.0.3之后VARCHAR的最大字符数为65535

转换编码

SET NAMES utf8
# CHARSET utf8

聚合函数

COUNT [GROUP BY]
COUNT + HAVING + GROUP BY (HAVING可以看作后置WHERE语句)
MIN/MAX [GROUP BY]
SUM/AVG [GROUP BY]
DISTINCT [GROUP BY]
所有的这些聚合函数加上GROUP BY之后,都只对GROUP BY部分有效。(不好理解,忽略)

并发控制

读锁(共享锁)/写锁(排他锁)

当某一用户修改一部分数据时,MySQL会禁止其他用户读取同一数据。大多数时,MySQL都是以透明的方式实现锁的内部管理

锁粒度

只锁定部分修改的数据,而不是所有的资源,或者只对要修改的数据片精确加锁。任何时间,在给定的资源上,被加锁的数据量越小,就可以允许更多的并发修改,只要相互之间互不冲突即可

这么做的问题是加锁也会消耗系统资源。如获得锁,检查锁是否已解除,以及释放锁等,都会增加系统开销。如果系统花费大量时间来管理锁,而不是读/写数据,那么系统整体性能都可能会受到影响

所谓的锁策略,就是在锁开销和数据安全之间寻求一种平衡。

表锁(MyISAM)

开销最小,但不适合频繁写操作

行锁(InnoDb)

可以支持最大的并发处理,但同时也会增加开销(InnoDb),由存储引擎实现,而不是MySQL服务器

事务

一组原子性的SQL语句。要么全部执行(commit),要么全部不执行(rollback)

正像锁粒度的增加会导致锁开销的增加一样,这种事务处理中的额外安全措施,也会导致数据库服务器要完成更多的额外工作

MySQL默认操作是AutoCommit,这意味着除非显示地开始一个事务,否则将把每个SQL操作视为一个单独事务自动执行

死锁

两个或多个事务在同一资源上相互占用,并请求加锁时,导致的恶性循环现象

解决办法:死锁检测/死锁超时机制。InnoDb处理死锁的方法是,回滚拥有最少排他行级锁的事务。

隐式和显式锁定

InnoDb: 一个事务在执行过程中的任何时候,都可以获得锁,但只有在执行COMMIT或ROLLBACK语句后,才可以释放这些锁。

InnoDb也支持显式锁定,如:

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

多版本并发控制

MySQL事务性存储引擎,如InnoDb,不是简单使用行加锁机制,而是MVCC和行加锁机制关联使用。

MVCC不是MySQL独有的技术,其他如Oracle, Postgresql等都在使用

可以将MVCC设想成一种行级加锁的变形,它避免了很多情况下的锁操作,大大降低了系统的开销

MVCC是通过及时保存在某些时刻的数据快照,而得以实现的。

所谓”版本号”,其实是InnoDb维护的一个计数器,每启动一个事务,计数器随着递增,并将该号作为事务的版本号

[INSERT] InnoDb将系统当前的版本号设为新增行的版本号

[DELETE] InnoDb将系统当前的版本号设为被删除行的删除号,该行并未立即被物理删除

[UPDATE] INSERT+DELETE

[SELECT]

  1. 行版本号不大于事务版本号。这确保了该行在事务开始时已存在,或者由当前事务创建、更新
  2. 行删除号不存在,或者删除号大于事务版本号。这确保事务开始前行未被删除

对于被标记为删除的行,InnoDb有专门的线程负责物理删除,当行满足如下条件时认为可以将其物理删除:当前不存在版本号小于该行删除号的事务,这样可以确保不会有事务再引用到该行

保存这些额外记录的好处,是使大多数读操作都不必申请加锁

关于MyISAM

  • 表加锁。并发低/开销少
  • 将每个表存储成两个文件:数据文件(.MYD)和索引文件(.MYI)
  • 使用CHECK TABLE mytable 和 REPAIR TABLE mytable来修复表,也可以使用myisamchk命令
  • 索引长度不能超过1000(注意,如果是utf8的话,长度x3),InnoDb没有此限制
  • 可以延迟索引。使用表创建选项DELAY_KEY_WRITE创建的MyISAM表,在SQL结束之后,不会将索引的改变数据写入磁盘,而是在内存的键缓冲区中缓存索引改变数据,只有在清理缓冲区或关闭表时才将索引块转到磁盘。对于数据经常改变,并且频繁使用的表,这种模式大大提高了表的处理性能。不过,如果服务器或系统崩溃,索引将肯定损坏,并需要修复

关于InnoDb

  • 行级锁。并发高/开销相对高
  • 高性能
  • 崩溃后自动恢复
  • 主键聚簇索引,辅助索引非聚簇索引(单独索引树),辅助索引也会包含主键列,所以如果主键列较大,则它的辅助索引也会较大
  • 任何改变InnoDb表结构的操作会导致整个表的重建,包括重建所有索引
  • 外键约束
  • 自动提交性能差?
  • 可以显示锁定
  • 不要对InnoDb使用不带WHERE语句的count(*),这会导致InnoDb执行全表扫描或索引扫描,而MyISAM只需要从相关记录中读取该值即可。

性能检测

mysql > SET PROFILING = 1;
mysql > ...
mysql > SHOW PROFILES;

会把执行的语句和执行时间都打印出来,如下

+----------+------------+-----------------------------------+
| Query_ID | Duration   | Query                             |
+----------+------------+-----------------------------------+
|        1 | 0.00011700 | select count(*) from user         |
|        2 | 0.00033500 | select count(*) from user_copy    |
|        3 | 0.59868300 | select count(created) from user   |
|        4 | 0.51746400 | select count(name) from user_copy |
|        5 | 0.00846700 | show table status like 'user'     |
+----------+------------+-----------------------------------+

还可以针对某个query进行更细致的分析

也可以使用FLUSH STATUS + SHOW SESSION STATUS

mysql > SHOW PROFILE FOR QUERY 1;

查询缓存

MySQL在第二次执行相同的SQL查询语句时,默认会使用查询缓存。加上”SQL_NO_CACHE”不使用查询缓存

SELECT SQL_NO_CACHE username, ...

关于NULL

  • 尽量避免NULL
  • MySQL难以优化引用了可空列的查询,它会使索引,索引统计和值更加复杂
  • 即使要在表中存储”没有值”的字段,还是有可能不使用NULL的,考虑使用0或空字符来代替它。

索引

索引是性能问题的首要原因,先搞定索引,再去搞查询优化

B-Tree索引

  • 根节点保存了指向子节点的指针,存储引擎根据指针寻找数据
  • 当一个数据块不能放下所有索引字段数据时,就会形成树形的根节点或分支节点,所以树的深度和广度是由数据量决定的
  • 每个节点包含了下层节点的链接,(没有相邻节点链接,上层链接可有可没有)

假设建立了一个(last_name, first_name, birth)的索引,此索引对于以下类型可用

1
2
匹配全名 (例如可以找到一个叫Cuba Allen,并且出生于1960-01-01的人)
匹配最左前缀 (例如可以找到姓为Allen的人,仅适用于索引中的第一列)

由于树的节点是排好序的,它们可以用于查找和ORDER BY查询

B-Tree的局限在于如果查询不是从索引列的最左边开始,就无法使用索引。所以索引列的顺序至关重要。

高性能索引策略

隔离列

如果在查询中没有隔离索引的列,MySQL通常不会是使用索引。”隔离”列意味着它不是表达式的一部分,也没有位于函数中。

前缀索引

找到合适的前缀长度(计算全列的选择性,并使前缀的选择性接近于它)

SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo
SELECT COUNT(DISTINCE LEFT(city, 4))/COUNT(*) FROM city_demo

也要注意如果数据分布非常不均匀,可能就会有问题

前缀索引能很好的减少索引的大小及提高速度,但MySQL不能在ORDER BY和GROUP BY中使用索引

聚集索引 (InnoDb)

当表有聚集索引时,它的数据行实际保存在索引的叶子页(而不是指针),所谓”聚集”就是指实际的数据行和相关的键值都保存在一起,每个表只能由一个聚集索引(主索引),因为不能以此把行保存在两个地方

优点:

  • 可以把相关数据保存在一起。如果没有使用聚集,读取每个邮件都会访问磁盘
  • 数据访问快。聚集索引把索引和数据都保存到了同一棵B-Tree中,因此从聚集索引中取得的数据通常比在非聚集索引进行查找要快
  • 聚集索引能最大限度地提升I/O密集负载的性能。

缺点:

  • 更新索引列代价是庞大的,因为它强制InnoDb把每个更新的行移到新位置
  • 辅助索引会比较大,因为它们的叶子包含了被引用行的主键列
  • 辅助索引访问需要两次索引查找

覆盖索引

所有满足查询需要的数据的索引(只需要读取索引,不需要再读取行数据),比如这条SQL语句

SELECT state_id, city, address FROM userinfo WHERE state_id = 5

如果只在state_id上建索引,则city,address都要从表里读取行数据

如果建立index (state_id, city, address),既能使用state_id索引,同时又可以使用覆盖索引,速度就快多了

多余和重复索引

  • MySQL允许你在统一列上创建多个索引,所以MySQL不得不单独维护每一个索引
  • 如果列(A,B)上有索引,那么另外一个列(A)上的索引就是多余的(B-Tree)
  • 大多数情况下,多余索引都是不好的,为了避免它,应该扩展已有索引,而不是添加新索引
  • 索引越多,更新索引的开销越大,尤其是在数据很多的情况下

--EOF--

若无特别说明,本站文章均为原创,转载请保留链接,谢谢