Оптимизации скорости выполнения запросов

  • Published on
    08-Jul-2015

  • View
    660

  • Download
    4

Embed Size (px)

Transcript

  • 1.

2. ? 3. 4. 5. 6. (MYSQL) EXPLAIN - SHOW STATUS - , .. , MySQL ( , PROFILING - PROCEDURE ANALYSE() 7. SHOW STATUS FLUSH STATUS SHOW STATUS LIKE 'Key_read%'; Key_read_requests . Key_reads . Key_reads , , , key_buffer_size . Key_reads/Key_read_requests. 8. PROFILING set profiling=1; show profiles; select sum(duration) from information_schema.profiling where query_id=1; show profile for query 1; 9. MYSQL EXPLAIN Id - (ID) . EXPLAIN . Select_type - : SIMPLE, PRIMARY, UNION, DEPENDENT UNION, SUBSELECT, DERIVED. Table - , MySQL Type - , MySQL. : eq_ref, ref, range, index, all. Possible_keys ( NULL, ), MySQL . Key - , MySQL ( ). Key_len - . Ref - , . Rows - , MySQL , . Extra - . 10. (POSTGRESQL) EXPLAIN EXPLAIN ANALYZE , ANALYZE VACUUM - REINDEX 11. EXPLAIN (POSTGRESQL) explain select attname from pg_attribute, pg_class where pg_attribute.attrelid = pg_class.oid and pg_class.relname = pg_proc order by pg_attribute.attnum; Sort (cost=27.66..27.68 rows=7 width=66) Sort Key: pg_attribute.attnum -> Nested Loop (cost=0.00..27.56 rows=7 width=66) -> Index Scan using pg_class_relname_nsp_index on pg_class (cost=0.00..8.27 rows=1 width=4) Index Cond: (relname = pg_proc::name) -> Index Scan using pg_attribute_relid_attnum_index on pg_attribute (cost=0.00..19.21 rows=7 width=70) Index Cond: (pg_attribute.attrelid = pg_class.oid) 12. 13. . = . . ( ) OR . 14. : O.Regino_ID + 0 = 137 : table_name [[AS] alias] [index_hint] USE INDEX (index_list) GNORE INDEX (index_list) FORCE INDEX (index_list) 15. SELECT FROM employees E LEFT OUTER JOIN location L ON E.location_id = L.id SELECT FROM employees E, locations L, addresses A WERE E.location_id = L.id AND E.location_id = A.id AND A.zip_code = 95628 16. . AND T1.Key2 = T2.Key2 AND T1.Key3 + 0*T2.Key2 = T3.Key3 STRAIGHT_JOIN JOIN, . 17. ? --log-slow-queries , long_query_time 1, 10 --log-queries-not-using-indexes , 18. (DESIGN ) , . ID. , . . ! . NOT NULL, 19. (SQL) Prepared Statements DISTINCT GROUP BY LIMIT m,n OR UNION GROUP BY ORDER BY NULL UNION UNION ALL 20. SELECT * SQL_NO_CACHE SELECT ORDER BY RAND() LIMIT 1, SELECT * FROM user WHERE state = 'Alabama' LIMIT 1 ENUM VARCHAR 21. SELECT max(...)/min(...) FROM SELECT field FROM foo ORDER BY field DESC LIMIT 1; Pagination WHERE AND NODE_ID > id_from_previous_page ORDER BY NODE_ID LIMIT 25 SELECT count(*) FROM 22. Views Temporary Tables Indexes 23. ISAM - . MyISAM - , , ISAM BDB (Berkley DB) . InnoDB - MERGE - MyISAM, . HEAP - 24. VIEWS CREATE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW MERGE TEMPTABLE - , 25. INDEXES B-/B+ tree R-/R+ tree Hash Expression Partial Reverse Bitmap GiST GIN - , , 26. B- - . B- . 27. R- B-, , , , , 28. HASH They are used only for equality comparisons that use the = or operators (but are very fast) The optimizer cannot use a hash index to speed up ORDER BY operations This may affect some queries if you change a MyISAM table to a hash-indexed MEMORY table. Only whole keys can be used to search for a row. 29. EXPRESSION & PARTIAL & REVERSE Expression SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith'; CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); Partial SELECT * FROM scheta WHERE NOT uplocheno AND ...; CREATE INDEX scheta_neuplocheno ON scheta (id) WHERE NOT uplocheno; Reverse 24538 -> 83542 30. BITMAP (cardinality) 31. GIST (GENERALIZED SEARCH TREE) , , B-, B+-, R- , , B+-, . , , , . .. GiST , , , . , B- R- . 32. GIST (GENERALIZED SEARCH TREE) GiST ( Btree) ( , ) ( ), 33. GIN (GENERALIZED INVERTED INDEX) Btree partial match (- , - ) :( 34. (GIST, GIN) (GiST, GIN) (GiST, GIN) (GiST) (GiST, GIN) Rtree (GiST) PostGIS (postgis.org) (GiST) spatial index BLASTgres (GiST) (GiST) ........ 35. MySQL Oracle PostgreSQL HSQLDB SQLite B-/B+ tree R-/R+ tree Hash Expression Partial Reverse Bitmap GiST GIN 36. RDBMS 37. CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt trigger_time BEFORE AFTER trigger_event Insert (insert, data load, replace) Update (update) Delete (delete, replace) 38. Temporary table CREATE [TEMPORARY] TABLE 39. , . , . OPTIMIZE TABLE _; "" (), . ANALYZE TABLE _; . 40. SQL Tuning. Dan Tow High Performance MySQL. Baron Schwartz, Peter Zaitsev, Vadim Tkachenko, Jeremy D. Zawodny, Arjen Lentz,and Derek J. Balling www.mysql.ru www.phpclub.ru http://dev.mysql.com/doc/refman http://www.mysqlperformanceblog.com http://highload.ru

Recommended

View more >