mysql调优

1.准备工作

打开performance_schema

gedit /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
performance_schema=ON

shell下运行 sudo mysql_upgrade -u root -p --force
重启mysql sudo service mysql restart

重新进入mysql
mysql> show variables like 'performance_schema';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| performance_schema | ON    |
+--------------------+-------+
1 row in set (0.00 sec)
打开成功!

网上下载mysql调试数据包

http://downloads.mysql.com/docs/sakila-db.zip

导入db和table，以及数据

mysql> source /home/hlbd/tmp/sakila-db/sakila-schema.sql

mysql> source /home/hlbd/tmp/sakila-db/sakila-data.sql

2. 一般步骤

mysql> show status like 'com_%';

查询 com_select com_insert ...执行次数

通过show profile分析SQL

select @@have_profilingmysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+

打开profiling

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

mysql> set profiling = 1;

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

mysql> show profiles;  找到query id
+----------+------------+-------------------------+
| Query_ID | Duration   | Query                   |
+----------+------------+-------------------------+
|        1 | 0.00013900 | select @@profiling      |
|        2 | 0.04622275 | select * from payment   |
|        3 | 0.00012125 | select @@have_profiling |
|        4 | 0.00022025 | select @@profiling      |
|        5 | 0.00009675 | set profiling =1        |
|        6 | 0.00020925 | select @@profiling      |
|        7 | 0.00022950 | select @@profiling      |
+----------+------------+-------------------------+

然后根据query id去查看此sql语句执行过程中线程每个状态和消耗时间：
mysql> show profile for query 2；
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000066 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000020 |
| init                 | 0.000025 |
| System lock          | 0.000011 |
| optimizing           | 0.000005 |
| statistics           | 0.000017 |
| preparing            | 0.000013 |
| executing            | 0.000004 |
| Sending data         | 0.045996 |
| end                  | 0.000011 |
| query end            | 0.000008 |
| closing tables       | 0.000008 |
| freeing items        | 0.000012 |
| cleaning up          | 0.000017 |
+----------------------+----------+

查看表的engine

mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |

....

mysql> show create table payment;

| payment | CREATE TABLE `payment` (
  `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` smallint(5) unsigned NOT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `rental_id` int(11) DEFAULT NULL,
  `amount` decimal(5,2) NOT NULL,
  `payment_date` datetime NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`payment_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `fk_payment_rental` (`rental_id`),
  CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 |
+-

建立索引优化查询例子

mysql> explain select sum(amount) from customer a,payment b where a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 599
     filtered: 10.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id

    rows:26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)

为customer表建立email索引mysql> create index idx_email on customer(email);
Query OK, 0 rows affected (0.53 sec)

mysql> explain select sum(amount) from customer a,payment b where a.customer_id=b.customer_id and email='JANE.BENNETT@sakilacustomer.org'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_email
          key: idx_email
      key_len: 153
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: b
   partitions: NULL
         type: ref
possible_keys: idx_fk_customer_id
          key: idx_fk_customer_id
      key_len: 2
          ref: sakila.a.customer_id
         rows: 26
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.00 sec)
建立索引后，同样的查询语句需要扫描的行数明显减少（从599减少到1行），从而大大提高查询速度。