Creació d'índex. Optimització de consultes - Historial de revisió

Joan: /* B-TREE, arbre binari de cerca */

2022-02-14T22:39:40Z

B-TREE, arbre binari de cerca

Joan: /* Els índex a MySQL */

2022-02-14T11:55:07Z

Els índex a MySQL

Joan a 11:52, 14 feb 2022

2022-02-14T11:52:58Z

Mostra els canvis

Joan a 11:11, 14 feb 2022

2022-02-14T11:11:06Z

Joan a 10:52, 14 feb 2022

2022-02-14T10:52:09Z

Joan: Es crea la pàgina amb «

 https://www.section.io/engineering-education/mysql-query-optimization-using-indexes-with-examples/ https://dev.mysql.com/doc/refman/8.0/en/create-index.html http...».

2022-02-14T09:32:58Z

Es crea la pàgina amb «<pre> https://www.section.io/engineering-education/mysql-query-optimization-using-indexes-with-examples/ https://dev.mysql.com/doc/refman/8.0/en/create-index.html http...».

Pàgina nova

<pre>
https://www.section.io/engineering-education/mysql-query-optimization-using-indexes-with-examples/
https://dev.mysql.com/doc/refman/8.0/en/create-index.html
https://dev.mysql.com/doc/refman/8.0/en/mysql-indexes.html

SHOW INDEX FROM yourtable;

mysql> show index from bd;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| bd | 0 | PRIMARY | 1 | id_bd | A | 10 | NULL | NULL | | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

B-tree
A tree data structure that is popular for use in database indexes. The structure is kept sorted at all times, enabling fast lookup for exact matches (equals operator) and ranges (for example, greater than, less than, and BETWEEN operators). This type of index is available for most storage engines, such as InnoDB and MyISAM.

Exemple: municipis
posar un índex a municipis.municipi
i fer un order by municipi

http://www.btechsmartclass.com/data_structures/b-trees.html

To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA:

SELECT DISTINCT
TABLE_NAME,
INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

use information_schema;
SELECT * FROM statistics;

</pre>
{{Autor}}, febrer 2022

@@ Línia 9: / Línia 9: @@
 La solució més ràpida i fàcil per crear un índex que ens ajudi a fer una cerca d'aquests municipis és crear un vector ordenat:
-BAT, CAL, CRE, DAN, EBR, FCR, LUR, PRI, MIT, NOL, TRO, SAT, ZAT
+BAT, CAL, CRE, DAN, EBR, FCR, LUR, MIT, NOL, PRI, SAT, TRO, ZAT
 Aquest vector contesta molt bé a les preguntes:
@@ Línia 38: / Línia 38: @@
 Si t'interessa pots mirar per exemple el següent video:
 *https://www.youtube.com/watch?v=mTMrszfrNtI
 =Els índex a MySQL=
 *https://www.section.io/engineering-education/mysql-query-optimization-using-indexes-with-examples/

@@ Línia 88: / Línia 88: @@
 mysql> SHOW INDEX FROM municipis;
 | municipis |          1 | municipi_idx |            1 | municipi    | A         |        8114 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
 rows in set (0,01 sec)

@@ Línia 90: / Línia 90: @@
 Torna a trigar de l'ordre de 12 ms
 </pre>
+<pre>
 {{Autor}}, febrer 2022

← Versió més antiga		Revisió del 10:52, 14 feb 2022
Línia 36:		Línia 36:

	</pre>		</pre>
		+	<pre>
		+	https://www.youtube.com/watch?v=mTMrszfrNtI
		+
		+	mysql> SHOW INDEX FROM municipis;
		+	+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	\| Table \| Non_unique \| Key_name \| Seq_in_index \| Column_name \| Collation \| Cardinality \| Sub_part \| Packed \| Null \| Index_type \| Comment \| Index_comment \| Visible \| Expression \|
		+	+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	\| municipis \| 0 \| PRIMARY \| 1 \| id_mun \| A \| 8380 \| NULL \| NULL \| \| BTREE \| \| \| YES \| NULL \|
		+	+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	1 row in set (0,01 sec)
		+
		+	select municipi from municipis order by municipi desc;
		+	8131 rows in set (0,01 sec)
		+
		+
		+	EXPLAIN ANALYZE select municipi from municipis order by municipi desc;
		+
		+	\| -> Sort: municipis.municipi DESC (cost=843.75 rows=8380) (actual time=9.648..10.132 rows=8131 loops=1)
		+	-> Table scan on municipis (cost=843.75 rows=8380) (actual time=0.059..2.268 rows=8131 loops=1)
		+
		+	són 12 ms (2.268 + 10.132)
		+
		+	CREATE INDEX municipi_idx ON municipis (municipi);
		+
		+	mysql> SHOW INDEX FROM municipis;
		+	+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	\| Table \| Non_unique \| Key_name \| Seq_in_index \| Column_name \| Collation \| Cardinality \| Sub_part \| Packed \| Null \| Index_type \| Comment \| Index_comment \| Visible \| Expression \|
		+	+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	\| municipis \| 0 \| PRIMARY \| 1 \| id_mun \| A \| 8380 \| NULL \| NULL \| \| BTREE \| \| \| YES \| NULL \|
		+	\| municipis \| 1 \| municipi_idx \| 1 \| municipi \| A \| 8114 \| NULL \| NULL \| YES \| BTREE \| \| \| YES \| NULL \|
		+	+-----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
		+	2 rows in set (0,01 sec)
		+
		+	select municipi from municipis order by municipi desc;
		+	8131 rows in set (0,00 sec)
		+
		+	EXPLAIN ANALYZE select municipi from municipis order by municipi desc;
		+	-> Index scan on municipis using municipi_idx (reverse) (cost=843.75 rows=8380) (actual time=0.077..4.682 rows=8131 loops=1)
		+
		+	són 4.6 ms. Per tant, són unes 3 vegades més ràpid.
		+
		+	Actual time to get first row (in milliseconds)
		+	Actual time to get all rows (in milliseconds)
		+	Actual number of rows read
		+	Actual number of loops
		+
		+	DROP INDEX municipi_idx ON municipis;
		+
		+	EXPLAIN ANALYZE select municipi from municipis order by municipi desc;
		+	\| -> Sort: municipis.municipi DESC (cost=843.75 rows=8380) (actual time=8.231..8.570 rows=8131 loops=1)
		+	-> Table scan on municipis (cost=843.75 rows=8380) (actual time=0.101..1.969 rows=8131 loops=1)
		+
		+	Torna a trigar de l'ordre de 12 ms
		+	</pre>
		+
	{{Autor}}, febrer 2022		{{Autor}}, febrer 2022