Der MySQL-Index ist unter Kontrolle

<p>Dieser Artikel vermittelt Ihnen relevantes Wissen über <a href="//m.sbmmt.com/course/list/51.html" target="_blank" textvalue="mysql视频教程">MySQL</a>, in dem hauptsächlich verwandte Themen zum MySQL-Index vorgestellt werden, einschließlich Indextypproblemen wie der Indexaufteilung nach logischen Funktionen, der Aufteilung nach physischer Implementierung, der Aufteilung nach der Anzahl der Felder usw. , hoffe es hilft allen. </p> <p><img src="https://img.php.cn/upload/article/000/000/067/6242cfce991a1216.jpg" alt="Der MySQL-Index ist unter Kontrolle" ></p> <p>Empfohlenes Lernen: <a href="//m.sbmmt.com/course/list/51.html" target="_blank" textvalue="mysql视频教程">MySQL-Tutorial</a></p> <p>Bei der SQL-Optimierung sind Indizes ein entscheidender Bestandteil, der einen qualitativen Sprung zur Abfrageeffizienz bringen kann, aber Indizes sind nicht allmächtig und ein unvernünftiges Indexdesign kann sogar die Abfrageeffizienz verlangsamen . </p> <h2>Indexdefinition</h2> <p>Ein Index ist eine Datenstruktur, die speziell dafür entwickelt wurde, SQL dabei zu helfen, Daten effizient zu erhalten. Ein häufiges Beispiel ist, dass der Index dem Inhaltsverzeichnis eines Buches ähnelt, mit dem bestimmte Werte schnell gefunden und gefunden werden können Beschleunigung der Effizienz der Datenabfrage. Tatsächlich ist der Index auch eine Tabelle. Diese Tabelle speichert die Primärschlüssel- und Indexfelder und zeigt auf die Datensätze der Entitätstabelle (ähnlich einem Zeiger). Vor- und Nachteile von Indexen </p>Index ist für InnoDB (Unterstützung von Sperren auf Zeilenebene für Indizes) sehr wichtig. InnoDB sperrt nur die Tupel, auf die zugegriffen werden muss, und Indizes können die Anzahl der Tupel reduzieren, auf die InnoDB zugreift. Wenn die Abfrage keinen Index verwenden kann, führt MySQL einen vollständigen Tabellenscan durch und sperrt jedes Tupel, unabhängig davon, ob es tatsächlich benötigt wird. <h2></h2> <h3><strong>Nachteile</strong></h3> <ul> <li>Obwohl der Index die Abfragegeschwindigkeit erheblich verbessert, verringert er auch die Geschwindigkeit der Aktualisierung der Tabelle. Denn beim Aktualisieren der Tabelle muss MySQL nicht nur die Daten, sondern auch die Indexdatei speichern. Daher wird generell davon abgeraten, Indizes für Felder zu verwenden, die sehr häufig aktualisiert werden. </li> <li>Die Indizierung nimmt Speicherplatz in Anspruch. </li> <li>Wenn eine Datenspalte viele wiederholte Inhalte enthält, ist der Indexierungseffekt dafür sehr gering. Diese Eigenschaft wird als Selektivität des Index bezeichnet: das Verhältnis der eindeutigen Indexwerte zur Gesamtzahl der Datensätze in den Daten Tisch. Je höher die Selektivität des Index ist, desto höher ist die Abfrageeffizienz. Wenn Sie beispielsweise einen Index für das Geschlechtsfeld erstellen, gibt es für eine Million Daten nur zwei Möglichkeiten für männlich und weiblich. Die Indexselektivität beträgt eins zu 500.000 und der Indexeffekt ist sehr gering</li> <li>Für sehr kleine Tabellen , ist der Index von geringer Bedeutung. In diesem Fall ist ein einfacher vollständiger Tabellenscan effizienter. </li> </ul> <h3>Es sollten also nur die am häufigsten abgefragten und am häufigsten sortierten Datenspalten indiziert werden. Die Gesamtzahl der Indizes in derselben Datentabelle in MySQL ist auf 16 begrenzt. <strong></strong>Indextyp</h3> <ul>Unterteilt nach Funktionslogik<li>Unterteilt nach Funktionslogik ist der Index hauptsächlich in gewöhnlichen Index, eindeutigen Index, Primärschlüsselindex und Volltextindex unterteilt<li> <li>Gewöhnlicher Index</li> <li>Der grundlegendste Index, es gibt keine Einschränkungen. Die einzige Aufgabe eines gewöhnlichen Index (ein durch das Schlüsselwort KEY oder INDEX definierter Index) besteht darin, den Zugriff auf Daten zu beschleunigen. Daher sollten Indizes nur für diejenigen Datenspalten erstellt werden, die am häufigsten in Abfragebedingungen (Spalte WHERE = ...) oder Sortierbedingungen (Spalte ORDER BY) vorkommen. </ul> <p>Es gibt drei Möglichkeiten, einen normalen Index zu erstellen. </p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false"># 创建索引CREATE INDEX idx_username ON user_tbl(username);# 对于字符串字段，可以手动指定长度，如 user_tbl(username(5))，表示只用前五个字符来做索引，可以进一步加快查询效率，索引长度要小于字段长度# 修改表结构ALTER TABLE user_tbl ADD INDEX idx_username (username)# 创建表的时候直接指定，如CREATE TABLE user_tbl(  ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  INDEX idx_username (username) );</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <h2>Index löschen</h2> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">DROP INDEX idx_username ON user_tbl;</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <h2>Index anzeigen</h2> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SHOW INDEX FROM user_tbl;</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p></p>Einzigartiger Index<h3> <strong></strong>Es ähnelt dem vorherigen normalen Index, mit der Ausnahme, dass der normale Index zulässt, dass die indizierte Datenspalte doppelte Werte enthält. Der Wert der eindeutigen Indexspalte muss eindeutig sein, es sind jedoch Nullwerte zulässig. Bei einem zusammengesetzten Index muss die Kombination der Spaltenwerte eindeutig sein. </h3> <p>Die Erstellung eines eindeutigen Index ähnelt einem normalen Index: </p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">#创建索引 CREATE UNIQUE INDEX idx_username ON user_tbl(username); # 修改表结构 ALTER TABLE user_tbl ADD UNIQUE idx_username (username) # 创建表的时候直接指定 CREATE TABLE user_tbl(  ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  UNIQUE idx_username (username)  );</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p></p>Primärschlüsselindex<p></p> <p>Es handelt sich um einen speziellen eindeutigen Index, der keine Nullwerte zulässt. Eine Tabelle kann nur einen Primärschlüssel haben, der normalerweise gleichzeitig mit der Erstellung der Tabelle erstellt wird. </p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">CREATE TABLE user_tbl(  ID INT NOT NULL,  username VARCHAR(16) NOT NULL,  PRIMARY KEY(ID)  );</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <h3>Ähnlich wie hier: Wenn eine Fremdschlüsseleinschränkung für ein Fremdschlüsselfeld definiert ist, definiert MySQL einen internen Index, um die Fremdschlüsseleinschränkung auf die effizienteste Weise zu verwalten und zu nutzen. <strong></strong> </h3>Volltextindex<p></p> <p>Im vorherigen Artikel zur MySQL-Grundsyntax haben wir gesagt, dass der Index ungültig wird, wenn Sie am Anfang LIKE + % verwenden. Wenn wir dann die Suchanforderungen vorher und nachher (z. B als LIKE '%hello %'), müssen Sie den Volltextindex verwenden. Es ist zu beachten, dass Innodb den Volltextindex erst nach Version 5.6 unterstützt. </p> <h3>Erstellung und Löschung des Volltextindex: <strong><div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false"># 创建的两种方法 CREATE FULLTEXT INDEX idx_name ON tbl_name(field_name); ALTER TABLE tbl_name ADD FULLTEXT INDEX idx_name(field_name); # 删除的两种方法 DROP INDEX idx_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX idx_name;</pre><div class="contentsignin">Nach dem Login kopieren</div></div></strong>Die Syntax für die Verwendung des Volltextindex für den vollständigen Fuzzy-Abgleich lautet: </h3> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">SELECT XXX FROM tbl_name WHERE match(field_name) against('xxx'); # 比如对 user_tbl 的 user_name 字段加了全文索引 # 查询结果等效于 SELECT user_name, user_id FROM user_tbl WHERE user_name LIKE '%hello%'; SELECT user_name, user_id FROM user_tbl WHERE match(user_name) against('hello');</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p>Verwenden Sie EXPLAIN, um zu überprüfen, ob der Volltextindex effektiv ist. </p> <p></p> <h3>Unterteilt nach physischer Implementierung<strong></strong>Unterteilt nach physischer Implementierung kann es normalerweise in Clustered-Indizes und Nicht-Clustered-Indizes unterteilt werden. </h3> <h3><strong>聚集索引（clustered index）</strong></h3> <p>存储内容是按照聚集索引排序的，聚集索引的顺序和行记录的顺序一致,一张表只能有一个聚集索引。聚集索引的叶子节点直接储存聚集索引指向的内容，因此查询的时候只需要进行一次查找。</p> <p>聚集索引在创建主键时自动生成，如果没有主键，则根据第一个不为空的唯一索引自动生成，如果还没有，则自动生成一个隐式的聚集索引。</p> <p>需要注意的是，在进行查询操作的时候，聚集索引的效率更高，因为少了一次查找；但是进行修改操作的时候，效率比非聚集索引低，因为直接修改了数据内容，为了标准数据内容的顺序和聚集索引顺序一致，会对数据页重新排序。</p> <h3><strong>非聚集索引（non-clustered index）</strong></h3> <p>非聚集索引虽然索引项是顺序存储的，但是索引项对应的内容是随机存储的，系统会维护单独的索引表来存储索引。</p> <p>非聚集索引的叶子节点存储的是数据的地址，查询非聚集索引的时候，系统会进行两次查找，先查找索引，再查找索引对应位置的数据。因此非聚集索引也叫二级索引或者辅助索引。</p> <h2>按字段个数划分</h2> <p>按字段个数可以把索引分为单一索引和联合索引。</p> <h3><strong>单一索引</strong></h3> <p>索引字段只有一列时为单一索引，上述所有索引都是单一索引。</p> <h3><strong>联合索引</strong></h3> <p>将多个字段组合在一起创建的索引叫联合索引。如下：</p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">ALTER TABLE user_tbl ADD INDEX idx_name_city_age (username,city,age);</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <h4>最左匹配原则</h4> <p>建立这样的联合索引，其实是相当于分别建立了下面三组联合索引：</p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">usernname,city,age usernname,city usernname</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p>为什么没有 city，age 这样的联合索引呢？这是因为MySQL联合索引的<strong>最左匹配原则</strong>，只会按照最左优先的顺序进行索引匹配，也就是说，(x,y,z) 和 (z,y,x) 是不同的索引，即使是使用联合索引中的字段查询，联合索引也有可能失效。</p> <p>对于 (x,y,z)，只有在以下查询条件联合索引会生效：</p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE x = 1WHERE x = 1 AND y = 1WHERE x = 1 AND y = 1 AND z = 1</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p>对于其他情况，比如 <code>WHERE y = 1</code> 、<code>WHERE y = 1 AND z = 1</code> 等，就不会匹配联合索引，索引失效，注意对于 <code>WHERE x = 1 AND z = 1</code>，联合索引会对 x 生效，但是对 z 不生效。</p> <p>可以扩展了解一下，理论上最左匹配原则中索引对 where 中子句的顺序也是敏感的，但是由于MySQL的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引，所以实际上 where 子句顺序不影响索引的效果。</p> <p>要注意的是，如果联合索引查询过程中有范围查询，就会停止匹配，比如下面的语句中， z 字段不能使用到索引：</p> <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false">WHERE x = 1 AND y > 2 AND z = 3</pre><div class="contentsignin">Nach dem Login kopieren</div></div> <p>顺便提一下，可以用 <code>explain</code> 命令来查看在某个查询语句中索引是否生效，具体用法请参考官网文档。</p> <p>如果分别在 x, y, z 上建立单列索引，让该表有3个单列索引，索引效率也会大不一样，在联合索引生效的情况下，单个索引的效率远远低于联合索引。这是由 MySQL 查询优化器的执行顺序决定的，在执行一条查询 sql 时，针对索引的选择大致有如下步骤：</p> <ol> <li>MySQL 优化器根据搜索条件，找出所有可能使用的索引</li> <li>计算全表扫描的代价</li> <li>计算使用不同索引执行查询的代价</li> <li>对比各种执行方案的代价，找出成本最低的那一个</li> </ol> <p>因此，虽然有多个单列索引，但 MySQL 只能用到其中的那个系统认为似乎是最有效率的，其他的就会失效。</p> <h2>按索引结构划分</h2> <p>不同的 mysql 数据引擎支持不同结构的索引，按结构划分，常用的索引为 B+树索引、Hash 索引、FULLTEXT索引 等，将在下一篇文章 MySQL 索引结构 中介绍。</p> <h2>使用总结</h2> <p>接下来我们来简单总结一下在什么场景下推荐使用索引。</p> <h2><span style="font-size: 16px;">推荐使用</span></h2> <ul style="list-style-type: disc;"> <li><p>WHERE, GROUP BY, ORDER BY 子句中的字段</p></li> <li> <p>多个单列索引在多条件查询是只会有一个最优的索引生效，因此多条件查询中最好创建联合索引。</p> <p>联合索引的时候必须满足最左匹配原则，并且最好考虑到 sql 语句的执行顺序，比如 <code>WHERE a = 1 GROUP BY b ORDER BY c</code>, 那么联合索引应该设计为 <code>(a,b,c)</code>，因为在上一篇文章 MySQL 基础语法 中我们介绍过，mysql 查询语句的执行顺序 WHERE > GROUP BY > ORDER BY。</p> </li> <li><p>多张表 JOIN 的时候，对表连接字段创建索引。</p></li> <li><p>Wenn Felder in SELECT vorhanden sind, die nicht im Index enthalten sind, wird der Primärschlüsselwert, der die Bedingungen erfüllt, zuerst über den Index abgefragt, und dann werden alle Felder in SELECT über den Primärschlüssel-<code abgefragt>Zurück zur Tabelle</code>, was sich auf die Abfrageeffizienz auswirkt. Wenn SELECT nur sehr wenig Inhalt enthält, können Sie alle Felder in SELECT zum gemeinsamen Index hinzufügen, um die Rückgabe der Tabelle zu vermeiden. Es ist jedoch zu beachten, dass bei zu vielen Indexfeldern auch die Kosten für die Speicherung und Pflege des Index steigen. <code>回表</code>查询出所有的 SELECT 中的字段，影响查询效率。因此如果 SELECT 中的内容很少，为了避免回表，可以把 SELECT 中的字段都加到联合索引中，这也就是宽索引的概念。但是需要注意，如果索引字段过多，存储和维护索引的成本也会增加。</p></li> </ul> <h2><span style="font-size: 16px;">不推荐使用或索引失效情况</span></h2> <ul style="list-style-type: disc;"> <li><p>数据量很小的表</p></li> <li><p>有大量重复数据的字段</p></li> <li><p>频繁更新的字段</p></li> <li><p>如果对索引字段使用了函数或者表达式计算，索引失效</p></li> <li><p>innodb OR 条件没有对所有条件创建索引，索引失效</p></li> <li><p>大于小于条件 <code><</code> <code>></code>，索引是否生效取决于命中的数量比例，如果命中数量很多，索引生效，命中数量很小，索引失效</p></li> <li><p>不等于条件 <code>!=</code> <code><></code>，索引失效</p></li> <li><p>LIKE 值以 <code>%</code></p></li> <h2><span style="font-size: 16px;">Nicht empfohlen oder Index ungültig</span></h2> <ul style="list-style-type: disc;"></ul> <p>Tabellen mit kleinem Datenvolumen<a href="//m.sbmmt.com/course/list/51.html" target="_blank" textvalue="mysql视频教程"></a></p>🎜Felder mit einer großen Menge doppelter Daten🎜🎜🎜🎜Häufig aktualisierte Felder🎜🎜🎜🎜Wenn Funktionen oder Ausdrucksberechnungen für Indexfelder verwendet werden, schlägt der Index fehl🎜🎜🎜🎜innodb ODER-Bedingungsindizes werden nicht für alle Bedingungen erstellt. 🎜🎜🎜🎜 ist größer oder kleiner als die Bedingung <code><</code> <code>></code> die Anzahl der Treffer. Der Index wird wirksam, die Anzahl der Treffer ist sehr gering, der Index schlägt fehl 🎜🎜🎜🎜 ist nicht gleich der Bedingung <code>!=</code> <code>< ;></code>, der Index schlägt fehl 🎜🎜🎜🎜LIKE-Wert beginnt mit <code> %</code> beginnt, der Index ist ungültig🎜🎜🎜🎜Empfohlenes Lernen: 🎜MySQL-Video-Tutorial🎜🎜</ul>

Das obige ist der detaillierte Inhalt vonDer MySQL-Index ist unter Kontrolle. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!