Was ist ein Index? MySQL verfügt derzeit über mehrere Hauptindextypen
- PHP中文网Original
- 2017-06-20 14:23:556939Durchsuche
1. Index
Die Einrichtung eines MySQL-Index ist für den effizienten Betrieb von MySQL sehr wichtig. Der Index kann die Abrufgeschwindigkeit von MySQL erheblich verbessern.
Wenn beispielsweise MySQL, das richtig konzipiert ist und Indizes verwendet, ein Lamborghini ist, dann ist MySQL, das nicht konzipiert ist und Indizes verwendet, ein menschliches Dreirad.
Indizes werden in einspaltige Indizes und kombinierte Indizes unterteilt. Ein einspaltiger Index bedeutet, dass ein Index nur eine einzige Spalte enthält. Eine Tabelle kann mehrere einspaltige Indizes haben, es handelt sich jedoch nicht um einen kombinierten Index. Kombinierter Index, d. h. ein Index enthält mehrere Spalten.
Beim Erstellen eines Index müssen Sie sicherstellen, dass der Index eine Bedingung ist, die auf die SQL-Abfrageanweisung angewendet wird (im Allgemeinen als Bedingung der WHERE-Klausel).
Tatsächlich ist der Index auch eine Tabelle, die die Primärschlüssel- und Indexfelder speichert und auf die Datensätze der Entitätstabelle verweist.
Das oben Genannte spricht von den Vorteilen der Verwendung von Indizes, aber eine übermäßige Verwendung von Indizes führt zu Missbrauch. Daher hat der Index auch seine Nachteile: Obwohl der Index die Abfragegeschwindigkeit erheblich verbessert, verringert er auch die Geschwindigkeit der Aktualisierung der Tabelle, z. B. INSERT, UPDATE und DELETE in der Tabelle. Denn beim Aktualisieren der Tabelle muss MySQL nicht nur die Daten, sondern auch die Indexdatei speichern.
Das Erstellen von Indexdateien belegt Speicherplatz.
2. Indextypen
Mysql verfügt derzeit über die folgenden Hauptindextypen: FULLTEXT, HASH, BTREE, RTREE.
1. FULLTEXT
ist ein Volltextindex, der derzeit nur von der MyISAM-Engine unterstützt wird. Es kann in CREATE TABLE, ALTER TABLE und CREATE INDEX verwendet werden, aber derzeit können nur Volltextindizes für CHAR-, VARCHAR- und TEXT-Spalten erstellt werden.
Der Volltextindex wurde nicht zusammen mit MyISAM entwickelt. Er schien das Problem der geringen Effizienz von Fuzzy-Text-basierten Abfragen wie WHERE name LIKE „%word%“ zu lösen.
2. HASH
Da HASH eindeutig (fast 100 % eindeutig) ist und eine Schlüssel-Wert-Paar-ähnliche Form hat, eignet es sich sehr gut als Index.
Der HASH-Index kann einmal gefunden werden und muss nicht wie ein Baumindex Schicht für Schicht durchsucht werden, sodass er äußerst effizient ist. Diese Effizienz ist jedoch bedingt, das heißt, sie ist nur unter den Bedingungen „=“ und „in“ effizient und bei Bereichsabfragen, Sortierungen und kombinierten Indizes immer noch nicht effizient.
3. BTREE
Der BTREE-Index ist eine Möglichkeit, den Indexwert gemäß einem bestimmten Algorithmus in einer baumförmigen Datenstruktur zu speichern Durchlaufen Sie ausgehend von der Wurzel die Knoten nacheinander, um das Blatt zu erhalten. Dies ist der standardmäßige und am häufigsten verwendete Indextyp in MySQL.
4. RTREE
RTREE wird in MySQL selten verwendet und unterstützt nur den Geometriedatentyp. Die einzigen Speicher-Engines, die diesen Typ unterstützen, sind MyISAM, BDb, InnoDb, NDb und Archive.
Im Vergleich zu BTREE liegt der Vorteil von RTREE in der Bereichssuche.
ps. Weitere Informationen zu diesem Absatz finden Sie in diesem Blogbeitrag: Die Unterschiede und Anwendbarkeit verschiedener MySQL-Indextypen
- Allgemeiner Index: Nur Abfrage beschleunigen
- Eindeutiger Index: Abfrage beschleunigen + Spaltenwert ist eindeutig (kann Null haben)
- Primärschlüssel Index: Abfrage beschleunigen + Spalte Eindeutiger Wert (kann nicht null sein) + Es gibt nur einen
- kombinierten Index in der Tabelle: Mehrere Spaltenwerte bilden einen Index, der speziell für die Kombination verwendet wird Suche, und ihre Effizienz ist größer als die Indexzusammenführung
- Volltextindex: Segmentieren Sie den Textinhalt und suchen Sie
- ps.
Indizes zusammenführen, mehrere Einzelspalten-Indexkombinationen verwenden, um zu suchen
Für den Index kann die ausgewählte Datenspalte nur aus dem Index abgerufen werden, ohne die Datenzeile einzulesen Mit anderen Worten, die Abfragespalte muss durch den erstellten Index abgedeckt seinIndexoperation
1 Index erstellen
--创建普通索引CREATE INDEX index_name ON table_name(col_name);--创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);--创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);--创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);2. Erstellen Sie einen Index durch Ändern der Tabellenstruktur
ALTER TABLE table_name ADD INDEX index_name(col_name);3 Geben Sie den Index direkt beim Erstellen der Tabelle an
CREATE TABLE table_name ( ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name) );4 index
--直接删除索引DROP INDEX index_name ON table_name;--修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;5 . Andere verwandte Befehle
- 查看表结构 desc table_name; - 查看生成表的SQL show create table table_name; - 查看索引 show index from table_name; - 查看执行时间 set profiling = 1; SQL... show profiles;5. Zeitpunkt zum Erstellen eines Index
So Bisher haben wir gelernt, wie man einen Index erstellt. Unter welchen Umständen müssen wir also einen Index erstellen? Im Allgemeinen müssen Spalten, die in WHERE und JOIN erscheinen, indiziert werden, aber das ist nicht ganz richtig, da MySQL nur <, <=, =, >, >=, BETWEEN, IN indiziert und manchmal LIKE verwendet Index. Zum Beispiel:
SELECT t.Name FROM mytable_t LEFT JOIN mytable_m ON t.Name=m.username WHERE m.age=20 AND m.city='郑州' ;Zu diesem Zeitpunkt ist es notwendig, Stadt und Alter zu indizieren. Da der Benutzername der Tabelle mytable_m auch in der JOIN-Klausel erscheint, ist dies ebenfalls erforderlich indizieren Sie es.
Ich habe gerade erwähnt, dass LIKE nur zu bestimmten Zeiten indiziert werden muss. Weil MySQL den Index nicht verwendet, wenn Abfragen mit den Platzhalterzeichen % und _ beginnen.
6. Trefferindex
Durch das Hinzufügen eines Index zur Datenbanktabelle wird die Abfragegeschwindigkeit tatsächlich beschleunigt, es muss jedoch davon ausgegangen werden, dass der Index für die Abfrage korrekt verwendet wird auf die falsche Weise, auch wenn es erstellt wurde. Die Indizierung funktioniert ebenfalls nicht.
Auch wenn der Index erstellt wird, wird er nicht wirksam:- like '%xx' select * from tb1 where name like '%cn';- 使用函数 select * from tb1 where reverse(name) = 'wupeiqi';- or select * from tb1 where nid = 1 or email = 'seven@live.com'; 特别的：当or条件中有未建立索引的列才失效，以下会走索引 select * from tb1 where nid = 1 or name = 'seven'; select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'- 类型不一致 如果列是字符串类型，传入条件是必须用引号引起来，不然... select * from tb1 where name = 999;- != select * from tb1 where name != 'alex' 特别的：如果是主键，则还是会走索引 select * from tb1 where nid != 123- > select * from tb1 where name > 'alex' 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from tb1 where nid > 123 select * from tb1 where num > 123- order by select email from tb1 order by name desc; 当根据索引排序时候，选择的映射如果不是索引，则不走索引 特别的：如果对主键排序，则还是走索引： select * from tb1 order by nid desc; - 组合索引最左前缀 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引7. Weitere Vorsichtsmaßnahmen
八、LIMIT分页
若需求是每页显示10条数据，如何建立分页？
我们可以先使用LIMIT尝试：
--第一页SELECT * FROM table_name LIMIT 0,10;--第二页SELECT * FROM table_name LIMIT 10,10;--第三页SELECT * FROM table_name LIMIT 20,10;
但是这样做有如下弊端：
每一条select语句都会从1遍历至当前位置，若跳转到第100页，则会遍历1000条记录
若记录的id不连续，则会出错
改善：
若已知每页的max_id和min_id，则可以通过主键索引来快速定位:
--下一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id > max_id LIMIT 10);--上一页SELECT * FROM table_name WHERE id in (SELECT id FROM table_name WHERE id < min_id ORDER BY id DESC LIMIT 10);--当前页之后的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id < min_id ORDER BY id desc LIMIT (页数差*10)) AS N ORDER BY N.id ASC LIMIT 10) AS P ORDER BY P.id ASC);--当前页之前的某一页SELECT * FROM table_name WHERE id in (SELECT id FROM (SELECT id FROM (SELECT id FROM table_name WHERE id > max_id LIMIT (页数差*10)) AS N ORDER BY N.id DESC LIMIT 10) AS P) ORDER BY id ASC;
九、执行计划
explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化
mysql> explain select * from tb2;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+| 1 | SIMPLE | tb2 | ALL | NULL | NULL | NULL | NULL | 2 | NULL |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set (0.00 sec)
id查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL || 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+ 特别的：如果使用union连接其值可能为null
select_type
查询类型
SIMPLE 简单查询PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询UNION 联合UNION RESULT 使用联合的结果
...table正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/constALL 全表扫描，对于数据表从头到尾找一遍select * from tb1;
特别的：如果有limit限制，则找到之后就不再继续向下扫描 select * from tb1 where email = 'seven@live.com' select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。INDEX 全索引扫描，对索引从头到尾找一遍select nid from tb1;
RANGE 对索引列进行范围查找select * from tb1 where name < 'alex';
PS:between andin> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引key真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。十、慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10S以上的语句。默认情况下，MySQLl数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。
1. 查看慢日志参数：
--查询配置命令show variables like '%query%';--当前配置参数binlog_rows_query_log_events OFFft_query_expansion_limit 20have_query_cache YES--时间限制，超过此时间，则记录long_query_time 10.000000query_alloc_block_size 8192query_cache_limit 1048576query_cache_min_res_unit 4096query_cache_size 1048576query_cache_type OFFquery_cache_wlock_invalidate OFFquery_prealloc_size 8192--是否开启慢日志记录slow_query_log OFF--日志文件slow_query_log_file D:\Program Files (x86)\mysql-5.7.18-winx64\data\Jack-slow.log--
2. 修改当前配置
set global 变量名 = 值;--例如，修改时间限制为20slong_query_time = 20;
ps.也可以直接打开慢日志配置文件进行修改，但必须重启服务才能生效
3. 查看MySQL慢日志
mysqldumpslow -s at -a /usr/local/var/mysql/MacBook-Pro-3-slow.log
"""--verbose 版本--debug 调试--help 帮助 -v 版本-d 调试模式-s ORDER 排序方式 what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time-r 反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)-t NUM 显示前N条just show the top n queries-a 不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'-n NUM abstract numbers with at least n digits within names -g PATTERN 正则匹配；grep: only consider stmts that include this string -h HOSTNAME mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l 总时间中不减去锁定时间；don't subtract lock time from total time """
Das obige ist der detaillierte Inhalt vonWas ist ein Index? MySQL verfügt derzeit über mehrere Hauptindextypen. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!