PHP 開発における MySQL データベースのインデックス作成の経験に関する簡単な説明

假如我们创建了一个testIndex表:
CREATE TABLE testIndex(i_testID INT NOT NULL,vc_Name VARCHAR(16) NOT NULL);

我们随机向里面插入了1000条记录，其中有一条
    i_testID    vc_Name
      555    erquan
    
在查找vc_Name="erquan"的记录
SELECT * FROM testIndex WHERE vc_Name='erquan';
时，如果在vc_Name上已经建立了索引，MySql无须任何扫描，即准确可找到该记录！相反，MySql会扫描所有记录，即要查询1000次啊~~可以索引将查询速度提高100倍。

　　一、索引分单列索引和组合索引

   单列索引：即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
   组合索引：即一个索包含多个列。

　　二、介绍一下索引的类型

    1.普通索引。
      这是最基本的索引，它没有任何限制。它有以下几种创建方式：
      （1）创建索引：CREATE INDEX indexName ON tableName(tableColumns(length));如果是CHAR,VARCHAR类型，length可以小于字段实际长度;如果是BLOB 和 TEXT 类型，必须指定length，下同。
      （2）修改表结构：ALTER tableName ADD INDEX [indexName] ON (tableColumns(length))
      （3）创建表的时候直接指定：CREATE TABLE tableName ( [...], INDEX [indexName] (tableColumns(length)) ;
   
    2.唯一索引。
       它与前面的"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
       （1）创建索引：CREATE UNIQUE INDEX indexName ON tableName(tableColumns(length))
      （2）修改表结构：ALTER tableName ADD UNIQUE [indexName] ON (tableColumns(length))
      （3）创建表的时候直接指定：CREATE TABLE tableName ( [...], UNIQUE [indexName] (tableColumns(length));

     3.主键索引
       它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：CREATE TABLE testIndex(i_testID INT NOT NULL AUTO_INCREMENT,vc_Name VARCHAR(16) NOT NULL,PRIMARY KEY(i_testID)); 当然也可以用ALTER命令。
       记住：一个表只能有一个主键。

      4.全文索引
        MySQL从3.23.23版开始支持全文索引和全文检索。这里不作讨论，呵呵~~

    删除索引的语法：DROP INDEX index_name ON tableName

　　三、单列索引和组合索引

    为了形象地对比两者，再建一个表：
    CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) );

    在这10000条记录里面7上8下地分布了5条vc_Name="erquan"的记录，只不过city,age,school的组合各不相同。
  来看这条T-SQL：
    SELECT i_testID FROM myIndex WHERE vc_Name='erquan' AND vc_City='郑州' AND i_Age=25;

    首先考虑建单列索引：
    在vc_Name列上建立了索引。执行T-SQL时，MYSQL很快将目标锁定在了vc_Name=erquan的5条记录上，取出来放到一中间结果集。在这个结果集里，先排除掉vc_City不等于"郑州"的记录，再排除i_Age不等于25的记录，最后筛选出唯一的符合条件的记录。

    虽然在vc_Name上建立了索引，查询时MYSQL不用扫描整张表，效率有所提高，但离我们的要求还有一定的距离。同样的，在vc_City和i_Age分别建立的单列索引的效率相似。

    为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将vc_Name,vc_City,i_Age建到一个索引里：
    ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age);--注意了，建表时，vc_Name长度为50，这里为什么用10呢？因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

    执行T-SQL时，MySQL无须扫描任何记录就到找到唯一的记录！！

テーブルに 3 つの単一列インデックスがあるように、vc_Name、vc_City、および i_Age にそれぞれ単一列インデックスを作成した場合、クエリ効率は上記の結合インデックスと同じになるのかと誰かが尋ねたに違いありません。へー、それはまったく違います、私たちの結合インデックスよりもはるかに低いです~~ 現時点では 3 つのインデックスがありますが、MySQL は最も効率的であると思われる単一列インデックスのみを使用できます。

このような結合インデックスを作成することは、実際には、
vc_Name、vc_City、i_Age
vc_Name、vc_City
vc_Name
などの 3 つの結合インデックスを確立することと同じです。 vc_City、i_Age などの複合インデックスがないのはなぜですか?これは、mysql の結合インデックスの「左端のプレフィックス」の結果です。簡単に理解すると、組み合わせは一番左のものから開始するだけです。この結合インデックスは、これら 3 つの列を含むクエリだけでなく、次の T-SQL でも使用されます:
SELECT * FROM myIndex WHREE vc_Name="erquan" AND vc_City="Zhengzhou"
SELECT * FROM myIndex WHREE vc_Name="erquan"
次のものは使用されません:
SELECT * FROM myIndex WHREE i_Age=20 AND vc_City="鄭州"
SELECT * FROM myIndex WHREE vc_City=" 鄭州 "

4. インデックスの使用

この時点で、インデックスを作成して使用できるはずですよね。しかし、どのような状況でインデックスを作成する必要があるのでしょうか?一般的に、WHERE および JOIN に出現するカラムにはインデックスを付ける必要がありますが、MySQL は <、<=、=、>、>=、BETWEEN、IN、および場合によっては LIKE のみにインデックスを付けるため、これが完全に当てはまるわけではありません (後で説明します)。 ) インデックスを使用します。
SELECT t.vc_Name FROM testIndex t LEFT JOIN myIndex m ON t.vc_Name=m.vc_Name WHERE m.i_Age=20 AND m.vc_City='鄭州' myIndex テーブルの vc_City と i_Age にインデックスを付ける必要がある場合, testIndex テーブルの vc_Name が JOIN 句に出現するため、これにもインデックスを付ける必要があります。

先ほど述べたように、インデックスを作成する必要があるのは特定の時刻の LIKE だけですか?はい。ワイルドカード文字 % と _ で始まるクエリを作成する場合、MySQL はインデックスを使用しません (例:
SELECT * FROM myIndex WHERE vc_Name like 'erquan%'
はインデックスを使用し、
SELECT * FROM) myIndex WHEREt vc_Name like'%erquan'
はインデックスを使用しません。

5. インデックスのデメリット

上記のインデックスには良い点がたくさんありますが、本当に伝説通りに優れているのでしょうか？もちろん欠点もあるでしょう。

1. インデックスによりクエリの速度は大幅に向上しますが、テーブルの INSERT、UPDATE、DELETE などのテーブルの更新速度も低下します。テーブルを更新するとき、MySQL はデータを保存するだけでなく、インデックス ファイルも保存する必要があるためです

2. インデックスを作成すると、ディスク領域上のインデックス ファイルが占有されます。通常、この問題は深刻ではありませんが、大きなテーブルに複数の結合インデックスを作成すると、インデックス ファイルが急速に拡張します。

記事終了:

ここまで述べましたが、インデックスを使用してデータベースの実行効率を向上させたいだけです。ただし、インデックス作成は効率を向上させる 1 つの要素にすぎません。 MySQL に大きなデータ テーブルがある場合は、最適なインデックスを構築する方法やクエリ ステートメントを最適化する方法を研究するために時間を費やす必要があります。