16性能优化
16性能优化
===============
索引
合理使用索引可以提高数据访问速度
索引是否被合理使用可以使用show status或者mysqladmin扩展命来查看
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 456 |
+-----------------------+-------+
6 rows in set (0.00 sec)
其中：
Handler_read_key这个值表示了一个行被索引读的次数，值高表示索引使用率较高
Handler_read_rnd_next这个值表示按照顺序读下一行的申请次数，值高表示查询效率偏低，需要创建合力的索引
表中索引被创建后，分析表可以减少碎片，提高访问效率
mysql> analyze table a,b;
+-------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-------+---------+----------+----------+
| an.a | analyze | status | OK |
| an.b | analyze | status | OK |
+-------+---------+----------+----------+
2 rows in set (0.03 sec)
--------------
查询高速缓存
mysql> show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name | Value |
+------------------------------+---------+
| have_query_cache | YES |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 9437184 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
+------------------------------+---------+
6 rows in set (0.00 sec)
have_query_cache 是否设置了查询高速缓存
query_cache_size 表示分配的查询高速缓存的大小，为0则表示关闭了查询告诉缓存
query_cache_type 值范围为0到2，0或者off表示查询告诉缓存关闭，1表示查询告诉缓存已经打开，但使用sql_no_cache选项的select语句除外，2或者demand根据需要按照运行带sql_cache选项的select语句提供查询告诉缓存
查询中使用或者不是用查询告诉缓存可以在查询中是用关键字sql_cache,sql_no_cache来控制
mysql> select sql_cache * from a;
+----+------+
| id | name |
+----+------+
| 1 | ab |
| 2 | abc |
| 3 | abcd |
| 4 | ann |
+----+------+
4 rows in set (0.00 sec)
mysql> select sql_no_cache * from a;
+----+------+
| id | name |
+----+------+
| 1 | ab |
| 2 | abc |
| 3 | abcd |
| 4 | ann |
+----+------+
4 rows in set (0.00 sec)
----------------
分析查询
类似于oracle的查看执行计划
mysql> explain select * from a;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | a | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from a where id=1 union select * from a;
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | UNION | a | ALL | NULL | NULL | NULL | NULL | 4 | |
|NULL | UNION RESULT |
+----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.02 sec)
各个列的含义：
id 查询中色了传统的位置
table 查询的表名
select_type 查询类型，包括是否有子查询，union，外部查询，外部查询中的子查询等
type 连接的执行情况，const：连接的最佳种类，all：扫描所有数据后才得到结果
possible_keys 提高查询速度可以使用的索引
key 实际使用的键，包括在key_len列中显示的键长度
rows 查询到数据的长度
extra 其他信息，如mysql如何处理查询结果等信息
---------------
优化多表查询
mysql中连接效果比子查询好
避免使用嵌套
使用中间变量减少查询层次
----------------
使用临时表
使用临时表存放中间信息可以提高性能
-----------------
优化表设计
合理的字段选择，字段长度选择
optimize table
------------------
调整服务器设置
mysql优化调整首先想到的是调整key_buffer_size和table_cache
key_buffer_size mysql索引缓冲可以使用的内存量，一般建议使用武力内存的25%到30%
table_cache 表高速缓存使用的内存量，与其有关的参数还有max_connections
mysql建议table_cache=max_connections*n,n为标准连接中表的数量
mysql> select @@table_cache;
+---------------+
| @@table_cache |
+---------------+
| 700 |
+---------------+
1 row in set (0.00 sec)
mysql> show variables like '%table_cache%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 700 |
+---------------+-------+
1 row in set (0.00 sec)
mysql> set global table_cache=1000;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@table_cache;
+---------------+
| @@table_cache |
+---------------+
| 1000 |
+---------------+
1 row in set (0.00 sec)
这样设置重启数据库后设置将会恢复
其他的优化方法：
增加sort_buffer可以提高order by和group by语句的查询速度
增加read_rnd_buffer_size变量可以提高分类行的速度
增加read_buffer_size可以提高读缓存进而提高select效率
增加binlog_cache_size增加二进制日志缓存区，可以提高日志的处理速度
增加bulk_insert_buffer_size可以提高批量插入速度，但只能在myisam表中生效
增加thread_cache_size可以控制每个进程分配的内存量，如果连接很多的话可以做到更好的控制
--------------
基准技术
mysql benchmark suite
要求能够运行perl，包括perl dbi套件和mysql数据库驱动程序（dbd）
perl -e "use DBI"
