一、理论：
1)插入语句的优化：
1.连接执行insert
insert into test values(1,2),(1,3),(1,4)……
2.使用insert deplayed提高插入速度
3.将索引文件和数据文件分在不同的磁盘上存放
4.如果对myisam的表进行批量插入，可以通过增加bulk_insert_buffer_size来提高速度
5.当从文本文件加载表时可以使用load data infile
2)优化目标：
尽量减少额外的排序，通过索引直接返回有序数据
3)使用与不使用索引的order
1.使用索引:
select * from test order by key_part1,key_part2....;
select * from test where key_part =1 order by key_part1 desc,key_part2 desc;
select * from test order by key_part1 desc,key_part2 desc;
2.不使用索引：
select * from test order by key_part1 desc,key_part2 asc;
select * from test where key2= constant order by key1;
select * from test order by key1,key2;
4)使当加大max_length_for_sort_data的值，可以使mysql选择更优化的filesort排序算法
5)优化group by语句：
1.如果想查询group by 但避免排序结果，可以使用ordre by null.
6)在某些情况下子查询可以被连接查询替代（查询效率更高），因为连接查询时不需要创建临时表。
二、实践：
abc@ubuntu:~$ cd ~/Downloads/
abc@ubuntu:~/Downloads$ wget http://downloads.mysql.com/docs/sakila-db.zip
--2015-10-23 06:53:16-- http://downloads.mysql.com/docs/sakila-db.zip
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 741576 (724K) [application/octet-stream]
Saving to: ?.akila-db.zip?
100%[======================================>] 741,576 3.97KB/s in 2m 20s
2015-10-23 06:55:41 (5.19 KB/s) - ?.akila-db.zip?.saved [741576/741576]
abc@ubuntu:~/Downloads$ mysql -uroot -p(密码)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 130
Server version: 5.5.44-log Source distribution
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database sakila ;
Query OK, 1 row affected (0.01 sec)
mysql> Bye
abc@ubuntu:~/Downloads$ unzip ./sakila-db.zip
Archive: ./sakila-db.zip
creating: sakila-db/
inflating: sakila-db/sakila-data.sql
inflating: sakila-db/sakila-schema.sql
inflating: sakila-db/sakila.mwb
abc@ubuntu:~/Downloads/sakila-db$ mysql -uroot -p(密码) sakila<sakila-schema.sql;
abc@ubuntu:~/Downloads/sakila-db$ mysql -uroot -p(密码) sakila<sakila-data.sql;
abc@ubuntu:~/Downloads/sakila-db$ mysql -uroot -p(密码)
Welcome to the MySQL monitor. Commands end with ; or \g.
mysql> use sakila;
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
mysql> show status like 'Com_%';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 1 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 2 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_authors | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_contributors | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 1 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 23 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 1 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reprepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Compression | OFF |
+---------------------------+-------+
140 rows in set (0.01 sec)
mysql> explain
-> select sum(amount) from customer
-> a,payment b where 1=1 and a.customer_id = b.customer_id
-> and email='JANE.BENNETT@sakilacustomer.org'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 635
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 12
Extra:
2 rows in set (0.01 sec)
ERROR:
No query specified
mysql> explain
-> select * from film where rating > 9 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 895
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select title from film\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 895
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from payment where customer_id > 300 and customer_id <= 350 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 1318
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain
-> select * from payment where customer_id = 350 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: const
rows: 23
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select b.*,a.* from payment a ,customer b where a.customer_id = b.customer_id \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 635
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.b.customer_id
rows: 12
Extra:
2 rows in set (0.01 sec)
ERROR:
No query specified
mysql> explain select * from film a,film_text b where a.film_id = b.film_id \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 895
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: sakila.a.film_id
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> alter table customer add unique index uk_email(email);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> set profiling = 1;
Query OK, 0 rows affected (0.01 sec)
mysql> select count(*) from payment;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.01 sec)
mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------+
| 1 | 0.01488275 | select count(*) from payment |
+----------+------------+------------------------------+
1 row in set (0.00 sec)
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000090 |
| checking permissions | 0.000013 |
| Opening tables | 0.000076 |
| System lock | 0.000023 |
| init | 0.000024 |
| optimizing | 0.000008 |
| statistics | 0.000030 |
| preparing | 0.000012 |
| executing | 0.000007 |
| Sending data | 0.007222 |
| end | 0.000062 |
| query end | 0.000017 |
| closing tables | 0.000017 |
| freeing items | 0.000482 |
| logging slow query | 0.000008 |
| logging slow query | 0.006770 |
| cleaning up | 0.000023 |
+----------------------+----------+
17 rows in set (0.01 sec)
mysql> set @query_id := 1;
Query OK, 0 rows affected (0.00 sec)
mysql> select state ,sum(duration) as total_r,
-> round(
-> 100 * sum(duration) /
-> (select sum(duration)
-> from information_schema.profiling
-> where query_id = @query_id
-> ),2) as pct_r,
-> count(*) as calls,
-> sum(duration) /count(*) as "r/call"
-> from information_schema.profiling
-> where query_id = @query_id
-> group by state
-> order by total_r desc;
+----------------------+----------+-------+-------+--------------+
| state | total_r | pct_r | calls | r/call |
+----------------------+----------+-------+-------+--------------+
| Sending data | 0.007222 | 48.52 | 1 | 0.0072220000 |
| logging slow query | 0.006778 | 45.54 | 2 | 0.0033890000 |
| freeing items | 0.000482 | 3.24 | 1 | 0.0004820000 |
| starting | 0.000090 | 0.60 | 1 | 0.0000900000 |
| Opening tables | 0.000076 | 0.51 | 1 | 0.0000760000 |
| end | 0.000062 | 0.42 | 1 | 0.0000620000 |
| statistics | 0.000030 | 0.20 | 1 | 0.0000300000 |
| init | 0.000024 | 0.16 | 1 | 0.0000240000 |
| cleaning up | 0.000023 | 0.15 | 1 | 0.0000230000 |
| System lock | 0.000023 | 0.15 | 1 | 0.0000230000 |
| closing tables | 0.000017 | 0.11 | 1 | 0.0000170000 |
| query end | 0.000017 | 0.11 | 1 | 0.0000170000 |
| checking permissions | 0.000013 | 0.09 | 1 | 0.0000130000 |
| preparing | 0.000012 | 0.08 | 1 | 0.0000120000 |
| optimizing | 0.000008 | 0.05 | 1 | 0.0000080000 |
| executing | 0.000007 | 0.05 | 1 | 0.0000070000 |
+----------------------+----------+-------+-------+--------------+
16 rows in set (0.02 sec)
mysql> create table payment_myisam like payment;
Query OK, 0 rows affected (0.04 sec)
mysql> alter table payment_myisam engine = myisam;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into payment_myisam select * from payment;
Query OK, 16049 rows affected, 1 warning (0.13 sec)
Records: 16049 Duplicates: 0 Warnings: 1
mysql> select count(*) from payment_myisam;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.00 sec)
mysql> show profiles \G;
*************************** 1. row ***************************
Query_ID: 1
Duration: 0.01488275
Query: select count(*) from payment
*************************** 2. row ***************************
Query_ID: 2
Duration: 0.00014125
Query: set @query_id := 1
*************************** 3. row ***************************
Query_ID: 3
Duration: 0.00409775
Query: select state ,sum(duration) as total_r,
round(
100 * sum(duration) /
(select sum(druation)
from intofrmation_schema.profiling
where query_id = @query_id
),2) as pct_r,
count(*) as calls,
sum(duration) /count(*) as "r/call"
from information_schema.profiling
where query_id = @query_id
group by sta
*************************** 4. row ***************************
Query_ID: 4
Duration: 0.00054200
Query: select state ,sum(duration) as total_r,
round(
100 * sum(duration) /
(select sum(druation)
from intformation_schema.profiling
where query_id = @query_id
),2) as pct_r,
count(*) as calls,
sum(duration) /count(*) as "r/call"
from information_schema.profil
*************************** 5. row ***************************
Query_ID: 5
Duration: 0.00067200
Query: select state ,sum(duration) as total_r,
round(
100 * sum(duration) /
(select sum(druation)
from information_schema.profiling
where query_id = @query_id
),2) as pct_r,
count(*) as calls,
sum(duration) /count(*) as "r/call"
from information_schema.profili
*************************** 6. row ***************************
Query_ID: 6
Duration: 0.00079875
Query: select state ,sum(duration) as total_r, round( 100 * sum(duration) / (select sum(druation) from information_schema.profiling where query_id = @query_id ),2) as pct_r, count(*) as calls, sum(duration) /count(*) as "r/call" from information_schema.profili
*************************** 7. row ***************************
Query_ID: 7
Duration: 0.00053275
Query: select state ,sum(duration) as total_r,
round(
100 * sum(duration) /
(select sum(druation)
from information_schema.profiling
where query_id = @query_id
),2) as pct_r,
count(*) as calls,
sum(duration) /count(*) as "r/call"
from information_schema.profili
*************************** 8. row ***************************
Query_ID: 8
Duration: 0.02334750
Query: select state ,sum(duration) as total_r,
round(
100 * sum(duration) /
(select sum(duration)
from information_schema.profiling
where query_id = @query_id
),2) as pct_r,
count(*) as calls,
sum(duration) /count(*) as "r/call"
from information_schema.profili
*************************** 9. row ***************************
Query_ID: 9
Duration: 0.03598775
Query: create table payment_myisam like payment
*************************** 10. row ***************************
Query_ID: 10
Duration: 0.02800550
Query: alter table payment_myisam engine = myisam
*************************** 11. row ***************************
Query_ID: 11
Duration: 0.00017400
Query: insert into payment_myisam sleect * from payment
*************************** 12. row ***************************
Query_ID: 12
Duration: 0.00043050
Query: inesrt into payment_myisam select * from payment
*************************** 13. row ***************************
Query_ID: 13
Duration: 0.12578950
Query: insert into payment_myisam select * from payment
*************************** 14. row ***************************
Query_ID: 14
Duration: 0.00060450
Query: select count(*) from payment_myisam
*************************** 15. row ***************************
Query_ID: 15
Duration: 0.00011500
Query: show prfiles
15 rows in set (0.00 sec)
ERROR:
No query specified
mysql> show profiles for query 10 \G;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'for query 10' at line 1
ERROR:
No query specified
mysql> show profile for query 10 \G;
*************************** 1. row ***************************
Status: starting
Duration: 0.000122
*************************** 2. row ***************************
Status: checking permissions
Duration: 0.000010
*************************** 3. row ***************************
Status: checking permissions
Duration: 0.000020
*************************** 4. row ***************************
Status: init
Duration: 0.000018
*************************** 5. row ***************************
Status: Opening tables
Duration: 0.000495
*************************** 6. row ***************************
Status: System lock
Duration: 0.000042
*************************** 7. row ***************************
Status: setup
Duration: 0.000089
*************************** 8. row ***************************
Status: creating table
Duration: 0.004948
*************************** 9. row ***************************
Status: After create
Duration: 0.000698
*************************** 10. row ***************************
Status: copy to tmp table
Duration: 0.002537
*************************** 11. row ***************************
Status: rename result table
Duration: 0.016760
*************************** 12. row ***************************
Status: end
Duration: 0.001523
*************************** 13. row ***************************
Status: query end
Duration: 0.000014
*************************** 14. row ***************************
Status: closing tables
Duration: 0.000024
*************************** 15. row ***************************
Status: freeing items
Duration: 0.000695
*************************** 16. row ***************************
Status: cleaning up
Duration: 0.000012
16 rows in set (0.00 sec)
ERROR:
No query specified
mysql> alter table rental add index idx_rental_date
-> (rental_date,inventory_id,customer_id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from rental where rental_date='2005-05-25 17:22:10' and -> inventory_id = 373 and customer_id = 343 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_inventory_id,idx_fk_customer_id,idx_rental_date
key: idx_rental_date
key_len: 13
ref: const,const,const
rows: 1
Extra:
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental where customer_id >= 373 and customer_id < 400 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: NULL
rows: 717
Extra: Using where
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> alter table payment add index idx_payment_date (payment_date,amount,last_update);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from payment where payment_date = '2006-02-14 15:16:03' and last_update = '2006-02-15 22:12:32' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 8
ref: const
rows: 182
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from payment where amount = 3.98 and last_update = '2006-02-15 22:12:32' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14533
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select last_update from payment
-> where payment_date = '2006-02-14 15:16:03'
-> and amount = 3.98 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: idx_payment_date
key: idx_payment_date
key_len: 11
ref: const,const
rows: 8
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create index idx_title_desc_part on film_text(title(10),description(20));Query OK, 1000 rows affected (0.05 sec)
Records: 1000 Duplicates: 0 Warnings: 0
mysql> explain select title from film_text where title like 'AFRICAN%' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film_text
type: range
possible_keys: idx_title_desc_part,idx_title_description
key: idx_title_desc_part
key_len: 32
ref: NULL
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select inventory_id from rental where rental_date = '2006-02-14 15:16:03' and customer_id >= 300 and customer_id <=400\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ref
possible_keys: idx_fk_customer_id,idx_rental_date
key: idx_rental_date
key_len: 8
ref: const
rows: 182
Extra: Using where; Using index
1 row in set (0.00 sec)
mysql> explain select * from payment where rental_id is null \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ref
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: const
rows: 5
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from actor where last_name like '%NI%' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select actor_id from actor where last_name like '%NI%';
+----------+
| actor_id |
+----------+
| 124 |
| 174 |
| 6 |
| 51 |
+----------+
4 rows in set (0.01 sec)
mysql> explain select * from ( select actor_id from actor where last_name like '%NI%') a , actor b where a.actor_id = b.actor_id\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: b
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: a.actor_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: actor
type: index
possible_keys: NULL
key: idx_actor_last_name
key_len: 137
ref: NULL
rows: 200
Extra: Using where; Using index
3 rows in set (0.00 sec)
mysql> explain select * from actor where last_name = 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ALL
possible_keys: idx_actor_last_name
key: NULL
key_len: NULL
ref: NULL
rows: 200
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from actor where last_name = '1' \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 137
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from payment where amount = 3.98 and last_update = '2006-02-12 22:12:32'\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 14533
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create table expenses( expens_date DATE NOT NULL, category INT, amount DECIMAL(10,3) )PARTITION BY LIST(category)( PARTITION p0 VALUES IN (3,5), PARTITION p1 VALUES IN (1,10), PARTITION p2 VALUES IN (4,9), PARTITION p3 VALUES IN (2), PARTITION p4 VALUES IN (6) );
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE expenses_1( expense_date DATE NOT NULL, category VARCHAR(30), amount DECIMAL(10,3) ) PARTITION BY LIST COLUMNS (category) ( PARTITION p0 VALUES IN ( 'aaa','bbb'),
PARTITION p1 VALUES IN ('ccc','ddd'),
PARTITION p2 VALUES IN ('eee','fff'),
PARTITION p3 VALUES IN ('ggg','hhh'),
PARTITION p4 VALUES IN ('iii','jjj')
);
Query OK, 0 rows affected (0.06 sec)
mysql> create table rc3( a int, b int) PARTITION BY RANGE COLUMNS(a,b)( PARTITION p01 VALUES less than (0,10), PARTITION p02 VALUES less than (10,15), PARTITION P03 values less than (10,MAXVALUE), PARTITION p04 values less than (MAXVALUE,MAXVALUE) );
Query OK, 0 rows affected (0.07 sec)
mysql> insert into rc3(a) value (3);
Query OK, 1 row affected (0.00 sec)
mysql> select (1,10) < (10,10) from dul;
ERROR 1146 (42S02): Table 'test1.dul' doesn't exist
mysql> select (1,10) < (10,10) from dual;
+------------------+
| (1,10) < (10,10) |
+------------------+
| 1 |
+------------------+
1 row in set (0.01 sec)
mysql> select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,15 | 1 |
| P03 | `a`,`b` | 10,MAXVALUE | 0 |
| p04 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)
mysql> insert into rc3(a) value (5);
Query OK, 1 row affected (0.01 sec)
mysql> select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,15 | 2 |
| P03 | `a`,`b` | 10,MAXVALUE | 0 |
| p04 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
4 rows in set (0.01 sec)
mysql> truncate rc3;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into rc3(a,b) values (10,9);
Query OK, 1 row affected (0.01 sec)
mysql> select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 0 |
| p02 | `a`,`b` | 10,15 | 1 |
| P03 | `a`,`b` | 10,MAXVALUE | 0 |
| p04 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)
mysql> select (10,9)<(10,10) from dual;
+----------------+
| (10,9)<(10,10) |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
mysql> insert into rc3(a,b) values(10,10);
Query OK, 1 row affected (0.01 sec)
mysql> select (10,10)<(10,10) from dual;
+-----------------+
| (10,10)<(10,10) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
mysql> insert into rc3(a,b) values(0,6);
Query OK, 1 row affected (0.01 sec)
mysql> select (10,10)<(10,10) from dual;
+-----------------+
| (10,10)<(10,10) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
mysql> select partition_name part, partition_expression expr, partition_description descr, table_rows from information_schema.partitions where table_schema=schema() and table_name='rc3';
+------+---------+-------------------+------------+
| part | expr | descr | table_rows |
+------+---------+-------------------+------------+
| p01 | `a`,`b` | 0,10 | 1 |
| p02 | `a`,`b` | 10,15 | 2 |
| P03 | `a`,`b` | 10,MAXVALUE | 0 |
| p04 | `a`,`b` | MAXVALUE,MAXVALUE | 0 |
+------+---------+-------------------+------------+
4 rows in set (0.00 sec)
mysql> explain select store_id,email,customer_id from customer
-> where store_id = 1 order by email desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: ref
possible_keys: idx_fk_store_id,idx_storeid_email
key: idx_storeid_email
key_len: 1
ref: const
rows: 325
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select store_id,email,customer_id from customer
-> where store_id >= 1 and store_id <=3 order by email desc \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: range
possible_keys: idx_fk_store_id,idx_storeid_email
key: idx_storeid_email
key_len: 1
ref: NULL
rows: 598
Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select payment_date,sum(amount) from payment group by payment_date\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: index
possible_keys: NULL
key: idx_payment_date
key_len: 15
ref: NULL
rows: 16451
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select payment_date,sum(amount) from payment group by payment_date order by null \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: index
possible_keys: NULL
key: idx_payment_date
key_len: 15
ref: NULL
rows: 16451
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from customer where customer_id not in
-> ( select customer_id from payment) \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: customer
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 671
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: payment
type: index_subquery
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: func
rows: 14
Extra: Using index
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from customer a left join payment b on
-> a.customer_id = b.customer_id where b.customer_id is null \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 671
Extra:
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: idx_fk_customer_id
key: idx_fk_customer_id
key_len: 2
ref: sakila.a.customer_id
rows: 14
Extra: Using where; Not exists
2 rows in set (0.00 sec)
ERROR:
No query specified
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
| v_payment_myisam |
+----------------------------+
24 rows in set (0.00 sec)
mysql> explain select film_id ,description from film order by title limit 50,5 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 895
Extra: Using filesort
1 row in set (0.01 sec)
ERROR:
No query specified
mysql> explain select a.film_id ,a.description from film
-> a inner join
-> ( select film_id from film order by title limit 50,5)
-> b on a.film_id = b.film_id \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra:
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: b.film_id
rows: 1
Extra:
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 55
Extra: Using index
3 rows in set (0.01 sec)
ERROR:
No query specified
mysql> explain select * from payment order by rental_id desc limit 410,10 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16451
Extra: Using filesort
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select payment_id,rental_id from payment order by rental_id desc limit 400,10;
+------------+-----------+
| payment_id | rental_id |
+------------+-----------+
| 1669 | 15649 |
| 2193 | 15648 |
| 6785 | 15647 |
| 3088 | 15646 |
| 5831 | 15645 |
| 1201 | 15644 |
| 8105 | 15643 |
| 4369 | 15642 |
| 6499 | 15641 |
| 7095 | 15640 |
+------------+-----------+
10 rows in set (0.00 sec)
mysql> explain select * from payment where rental_id < 15640 order by rental_id desc limit 10 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
type: range
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: NULL
rows: 8225
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select count(*) from rental use index (idx_rental_date)\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: index
possible_keys: NULL
key: idx_rental_date
key_len: 13
ref: NULL
rows: 16341
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select count(*) from rental ignore index (idx_rental_date) \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: index
possible_keys: NULL
key: idx_fk_staff_id
key_len: 1
ref: NULL
rows: 16341
Extra: Using index
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental where inventory_id > 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ALL
possible_keys: idx_fk_inventory_id
key: NULL
key_len: NULL
ref: NULL
rows: 16341
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental use index
-> ( idx_fk_inventory_id) where inventory_id > 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: ALL
possible_keys: idx_fk_inventory_id
key: NULL
key_len: NULL
ref: NULL
rows: 16341
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> explain select * from rental force index
-> ( idx_fk_inventory_id) where inventory_id > 1 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys: idx_fk_inventory_id
key: idx_fk_inventory_id
key_len: 3
ref: NULL
rows: 8170
Extra: Using where
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select first_name,email from customer where email regexp "@pingzi[,.]com$";
+------------+--------------------+
| first_name | email |
+------------+--------------------+
| 188mail | binggan@pingzi.com |
+------------+--------------------+
1 row in set (0.00 sec)
mysql> select first_name,email from customer
-> where email like "@pingzi.com" or email like "@pingzi,com";
Empty set (0.02 sec)
mysql> select first_name,email from customer where email like "%@pingzi.com%" or email like "%@pingzi,com%";
+------------+--------------------+
| first_name | email |
+------------+--------------------+
| 188mail | binggan@pingzi.com |
+------------+--------------------+
1 row in set (0.00 sec)
mysql> select * from category order by rand();
+-------------+-------------+---------------------+
| category_id | name | last_update |
+-------------+-------------+---------------------+
| 10 | Games | 2006-02-15 04:46:27 |
| 14 | Sci-Fi | 2006-02-15 04:46:27 |
| 11 | Horror | 2006-02-15 04:46:27 |
| 16 | Travel | 2006-02-15 04:46:27 |
| 5 | Comedy | 2006-02-15 04:46:27 |
| 7 | Drama | 2006-02-15 04:46:27 |
| 6 | Documentary | 2006-02-15 04:46:27 |
| 1 | Action | 2006-02-15 04:46:27 |
| 12 | Music | 2006-02-15 04:46:27 |
| 4 | Classics | 2006-02-15 04:46:27 |
| 15 | Sports | 2006-02-15 04:46:27 |
| 9 | Foreign | 2006-02-15 04:46:27 |
| 8 | Family | 2006-02-15 04:46:27 |
| 3 | Children | 2006-02-15 04:46:27 |
| 2 | Animation | 2006-02-15 04:46:27 |
| 13 | New | 2006-02-15 04:46:27 |
+-------------+-------------+---------------------+
16 rows in set (0.01 sec)
mysql> select * from category order by rand() limit 5;
+-------------+----------+---------------------+
| category_id | name | last_update |
+-------------+----------+---------------------+
| 7 | Drama | 2006-02-15 04:46:27 |
| 15 | Sports | 2006-02-15 04:46:27 |
| 3 | Children | 2006-02-15 04:46:27 |
| 11 | Horror | 2006-02-15 04:46:27 |
| 4 | Classics | 2006-02-15 04:46:27 |
+-------------+----------+---------------------+
5 rows in set (0.00 sec)
mysql> select date_format(payment_date,'%Y-%m'),staff_id,sum(amount) from payment
-> group by date_format(payment_date,'%Y-%m'),staff_id;
+-----------------------------------+----------+-------------+
| date_format(payment_date,'%Y-%m') | staff_id | sum(amount) |
+-----------------------------------+----------+-------------+
| 2005-05 | 1 | 2621.83 |
| 2005-05 | 2 | 2202.60 |
| 2005-06 | 1 | 4776.36 |
| 2005-06 | 2 | 4855.52 |
| 2005-07 | 1 | 14003.54 |
| 2005-07 | 2 | 14370.35 |
| 2005-08 | 1 | 11853.65 |
| 2005-08 | 2 | 12218.48 |
| 2006-02 | 1 | 234.09 |
| 2006-02 | 2 | 280.09 |
+-----------------------------------+----------+-------------+
10 rows in set (0.08 sec)
mysql> select date_format(payment_date,'%Y-%m'),ifnull(staff_id,''),sum(amount) from payment
-> group by date_format(payment_date,'%Y-%m'),staff_id with rollup;
+-----------------------------------+---------------------+-------------+
| date_format(payment_date,'%Y-%m') | ifnull(staff_id,'') | sum(amount) |
+-----------------------------------+---------------------+-------------+
| 2005-05 | 1 | 2621.83 |
| 2005-05 | 2 | 2202.60 |
| 2005-05 | | 4824.43 |
| 2005-06 | 1 | 4776.36 |
| 2005-06 | 2 | 4855.52 |
| 2005-06 | | 9631.88 |
| 2005-07 | 1 | 14003.54 |
| 2005-07 | 2 | 14370.35 |
| 2005-07 | | 28373.89 |
| 2005-08 | 1 | 11853.65 |
| 2005-08 | 2 | 12218.48 |
| 2005-08 | | 24072.13 |
| 2006-02 | 1 | 234.09 |
| 2006-02 | 2 | 280.09 |
| 2006-02 | | 514.18 |
| NULL | | 67416.51 |
+-----------------------------------+---------------------+-------------+
16 rows in set (0.05 sec)
mysql> create table order_rab(id int,customer_id int,kind int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into order_rab values (1,1,5),(2,1,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into order_rab values (3,2,3),(4,2,4);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from order_rab;
+------+-------------+------+
| id | customer_id | kind |
+------+-------------+------+
| 1 | 1 | 5 |
| 2 | 1 | 4 |
| 3 | 2 | 3 |
| 4 | 2 | 4 |
+------+-------------+------+
4 rows in set (0.00 sec)
mysql> select customer_id,bit_or(kind) from order_rab group by customer_id;
+-------------+--------------+
| customer_id | bit_or(kind) |
+-------------+--------------+
| 1 | 5 |
| 2 | 7 |
+-------------+--------------+
2 rows in set (0.00 sec)
mysql> select customer_id,bit_and(kind) from order_rab group by customer_id;
+-------------+---------------+
| customer_id | bit_and(kind) |
+-------------+---------------+
| 1 | 4 |
| 2 | 0 |
+-------------+---------------+
2 rows in set (0.00 sec)
mysql> select id from order_rab a where A.id = 1;
ERROR 1054 (42S22): Unknown column 'A.id' in 'where clause'
mysql> create table users(id int,name varchar(10),primary key(id)) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> create table books(id int,bookname varchar(10),userid int,primary key(id),constraint fk_userid_id foreign key (userid) references users(id)) engine = myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into books values (1,'book1',1);
Query OK, 1 row affected (0.02 sec)
mysql> select * from users;
Empty set (0.00 sec)
mysql> select * from books;
+----+----------+--------+
| id | bookname | userid |
+----+----------+--------+
| 1 | book1 | 1 |
+----+----------+--------+
1 row in set (0.00 sec)
mysql> create table user2(id int,name varchar(10),primary key(id)) engine = innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table books;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| order_rab |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
| user2 |
| users |
| v_payment_myisam |
+----------------------------+
27 rows in set (0.00 sec)
mysql> show create table user2 \G;
*************************** 1. row ***************************
Table: user2
Create Table: CREATE TABLE `user2` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> create table books1(id int,bookname varchar(10),userid int,primary key(id),constraint fk_userid_id foreign key(userid) references user2(id)) engine = innodb;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table books1 \G;
*************************** 1. row ***************************
Table: books1
Create Table: CREATE TABLE `books1` (
`id` int(11) NOT NULL DEFAULT '0',
`bookname` varchar(10) DEFAULT NULL,
`userid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_userid_id` (`userid`),
CONSTRAINT `fk_userid_id` FOREIGN KEY (`userid`) REFERENCES `user2` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
230
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
