mysql 学习记录（十六）--优化常用sql
来源：互联网 发布：wampserver mysql密码 编辑：程序博客网 时间：2024/06/06 03:09
一、理论：
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.zipResolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:80... connected.HTTP request sent, awaiting response... 200 OKLength: 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 130Server version: 5.5.44-log Source distributionCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> Byeabc@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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 635 Extra: Using where*************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: refpossible_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 specifiedmysql> explain -> select * from film where rating > 9 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 895 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select title from film\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 895 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select * from payment where customer_id > 300 and customer_id <= 350 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 1318 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain -> select * from payment where customer_id = 350 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: refpossible_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 specifiedmysql> 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: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 635 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: a type: refpossible_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 specifiedmysql> 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: ALLpossible_keys: PRIMARY key: NULL key_len: NULL ref: NULL rows: 895 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: sakila.a.film_id rows: 1 Extra: Using where2 rows in set (0.00 sec)ERROR: No query specifiedmysql> alter table customer add unique index uk_email(email);Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> insert into payment_myisam select * from payment;Query OK, 16049 rows affected, 1 warning (0.13 sec)Records: 16049 Duplicates: 0 Warnings: 1mysql> select count(*) from payment_myisam;+----------+| count(*) |+----------+| 16049 |+----------+1 row in set (0.00 sec)mysql> show profiles \G;*************************** 1. row ***************************Query_ID: 1Duration: 0.01488275 Query: select count(*) from payment*************************** 2. row ***************************Query_ID: 2Duration: 0.00014125 Query: set @query_id := 1*************************** 3. row ***************************Query_ID: 3Duration: 0.00409775 Query: select state ,sum(duration) as total_r,round(100 * sum(duration) /(select sum(druation) from intofrmation_schema.profilingwhere 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: 4Duration: 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: 5Duration: 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: 6Duration: 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: 7Duration: 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: 8Duration: 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: 9Duration: 0.03598775 Query: create table payment_myisam like payment*************************** 10. row ***************************Query_ID: 10Duration: 0.02800550 Query: alter table payment_myisam engine = myisam*************************** 11. row ***************************Query_ID: 11Duration: 0.00017400 Query: insert into payment_myisam sleect * from payment*************************** 12. row ***************************Query_ID: 12Duration: 0.00043050 Query: inesrt into payment_myisam select * from payment*************************** 13. row ***************************Query_ID: 13Duration: 0.12578950 Query: insert into payment_myisam select * from payment*************************** 14. row ***************************Query_ID: 14Duration: 0.00060450 Query: select count(*) from payment_myisam*************************** 15. row ***************************Query_ID: 15Duration: 0.00011500 Query: show prfiles15 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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 1ERROR: No query specifiedmysql> show profile for query 10 \G;*************************** 1. row *************************** Status: startingDuration: 0.000122*************************** 2. row *************************** Status: checking permissionsDuration: 0.000010*************************** 3. row *************************** Status: checking permissionsDuration: 0.000020*************************** 4. row *************************** Status: initDuration: 0.000018*************************** 5. row *************************** Status: Opening tablesDuration: 0.000495*************************** 6. row *************************** Status: System lockDuration: 0.000042*************************** 7. row *************************** Status: setupDuration: 0.000089*************************** 8. row *************************** Status: creating tableDuration: 0.004948*************************** 9. row *************************** Status: After createDuration: 0.000698*************************** 10. row *************************** Status: copy to tmp tableDuration: 0.002537*************************** 11. row *************************** Status: rename result tableDuration: 0.016760*************************** 12. row *************************** Status: endDuration: 0.001523*************************** 13. row *************************** Status: query endDuration: 0.000014*************************** 14. row *************************** Status: closing tablesDuration: 0.000024*************************** 15. row *************************** Status: freeing itemsDuration: 0.000695*************************** 16. row *************************** Status: cleaning upDuration: 0.00001216 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: 0mysql> 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: refpossible_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 specifiedmysql> explain select * from rental where customer_id >= 373 and customer_id < 400 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: rangepossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: NULL rows: 717 Extra: Using where1 row in set (0.01 sec)ERROR: No query specifiedmysql> 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: 0mysql> 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: refpossible_keys: idx_payment_date key: idx_payment_date key_len: 8 ref: const rows: 182 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 14533 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: refpossible_keys: idx_payment_date key: idx_payment_date key_len: 11 ref: const,const rows: 8 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: 0mysql> explain select title from film_text where title like 'AFRICAN%' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: film_text type: rangepossible_keys: idx_title_desc_part,idx_title_description key: idx_title_desc_part key_len: 32 ref: NULL rows: 1 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: refpossible_keys: idx_fk_customer_id,idx_rental_date key: idx_rental_date key_len: 8 ref: const rows: 182 Extra: Using where; Using index1 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: refpossible_keys: fk_payment_rental key: fk_payment_rental key_len: 5 ref: const rows: 5 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select * from actor where last_name like '%NI%' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 4 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: b type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: a.actor_id rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: DERIVED table: actor type: indexpossible_keys: NULL key: idx_actor_last_name key_len: 137 ref: NULL rows: 200 Extra: Using where; Using index3 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: ALLpossible_keys: idx_actor_last_name key: NULL key_len: NULL ref: NULL rows: 200 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select * from actor where last_name = '1' \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: actor type: refpossible_keys: idx_actor_last_name key: idx_actor_last_name key_len: 137 ref: const rows: 1 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 14533 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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 existmysql> 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: refpossible_keys: idx_fk_store_id,idx_storeid_email key: idx_storeid_email key_len: 1 ref: const rows: 325 Extra: Using where; Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: rangepossible_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 filesort1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select payment_date,sum(amount) from payment group by payment_date\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: indexpossible_keys: NULL key: idx_payment_date key_len: 15 ref: NULL rows: 16451 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: indexpossible_keys: NULL key: idx_payment_date key_len: 15 ref: NULL rows: 16451 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_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_subquerypossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: func rows: 14 Extra: Using index2 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 671 Extra: *************************** 2. row *************************** id: 1 select_type: SIMPLE table: b type: refpossible_keys: idx_fk_customer_id key: idx_fk_customer_id key_len: 2 ref: sakila.a.customer_id rows: 14 Extra: Using where; Not exists2 rows in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 895 Extra: Using filesort1 row in set (0.01 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5 Extra: *************************** 2. row *************************** id: 1 select_type: PRIMARY table: a type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 2 ref: b.film_id rows: 1 Extra: *************************** 3. row *************************** id: 2 select_type: DERIVED table: film type: indexpossible_keys: NULL key: idx_title key_len: 767 ref: NULL rows: 55 Extra: Using index3 rows in set (0.01 sec)ERROR: No query specifiedmysql> explain select * from payment order by rental_id desc limit 410,10 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: payment type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 16451 Extra: Using filesort1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: rangepossible_keys: fk_payment_rental key: fk_payment_rental key_len: 5 ref: NULL rows: 8225 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select count(*) from rental use index (idx_rental_date)\G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: indexpossible_keys: NULL key: idx_rental_date key_len: 13 ref: NULL rows: 16341 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select count(*) from rental ignore index (idx_rental_date) \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: indexpossible_keys: NULL key: idx_fk_staff_id key_len: 1 ref: NULL rows: 16341 Extra: Using index1 row in set (0.00 sec)ERROR: No query specifiedmysql> explain select * from rental where inventory_id > 1 \G;*************************** 1. row *************************** id: 1 select_type: SIMPLE table: rental type: ALLpossible_keys: idx_fk_inventory_id key: NULL key_len: NULL ref: NULL rows: 16341 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: ALLpossible_keys: idx_fk_inventory_id key: NULL key_len: NULL ref: NULL rows: 16341 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: rangepossible_keys: idx_fk_inventory_id key: idx_fk_inventory_id key_len: 3 ref: NULL rows: 8170 Extra: Using where1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: 0mysql> insert into order_rab values (3,2,3),(4,2,4);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> 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: user2Create Table: CREATE TABLE `user2` ( `id` int(11) NOT NULL DEFAULT '0', `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)ERROR: No query specifiedmysql> 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: books1Create 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=utf81 row in set (0.00 sec)ERROR: No query specified 0 0
- mysql 学习记录（十六）--优化常用sql
- MySQL 学习<十六> 性能优化
- MySql 学习笔记三：常用SQL优化
- MySQL 常用SQL优化
- mysql 学习记录（二十六）--mysql数据库的备份与恢复
- SQL优化和查询（十六）
- mysql常用统计sql记录
- 我的MYSQL学习心得（十六） 优化
- 我的MYSQL学习心得（十六） 优化
- android学习记录（十六）Dialogs
- 无人机驾驶员培训学习记录（十六）
- mysql 学习记录（二十二）--mysql的应用优化
- MySQL——常用SQL优化（一）
- MySQL——常用SQL优化（二）
- MySQL——常用SQL优化（三）
- MySQL——常用SQL优化（四）
- MySQL——常用SQL优化（五）
- Mysql学习笔记八，Mysql优化篇，常用sql技巧和常见问题
- Swift2.0中解决访问资源库函数 - (void)image:(UIImage *)image didFinishSavingWithError:(NSError *)error conte错误。
- (2) 事务
- 计算机专业英语汇总(二)
- 前端知识点笔记
- libevent源码分析
- mysql 学习记录（十六）--优化常用sql
- 第六周-数制转换
- JAVA多线程实现的三种方式
- 背包之01，完全，多重模板
- C++项目中的extern "C" {}
- 周赛 题 1 light oj 1005【2015/10/24】
- POJ 1325 Machine Schedule（最大匹配数=最小点覆盖）
- css min-width最小宽度与max-width最大宽度教程
- 数据库五个例子总结
