mysql最適化のインデックスの作成

一.インデックスの役割
一般的なアプリケーションシステムでは、読み書きの割合が10:1程度であり、挿入操作と一般的な更新操作ではパフォーマンスの問題が少なく、最も多くの問題に遭遇し、最も問題が発生しやすいのか、複雑なクエリー操作なのか、クエリー文の最適化は明らかに重要である.
データ量とアクセス量が少ない場合、mysqlアクセスは非常に速く、インデックスを付けるかどうかはアクセスにあまり影響しません.しかし、データ量とアクセス量が急増すると、mysqlが遅くなり、downが落ちることがわかります.これはsqlを最適化し、データベースに正確で合理的なインデックスを確立することを考慮しなければなりません.mysql最適化の重要な手段です.  
インデックスの目的は、クエリーの効率を向上させ、辞書を類比することです.「mysql」という単語を調べるには、m文字にナビゲートし、下からy文字を見つけて、残りのsqlを見つける必要があります.インデックスがなければ、すべての単語を見てから、あなたが望んでいるものを見つける必要があるかもしれません.辞書のほか、火の駅のダイヤや図書の目録など、索引の例があちこちに見られる.それらの原理はすべて同じで、データを取得したい範囲を絶えず縮小することによって最終的に望んだ結果をフィルタリングし、同時にランダムなイベントを順序にしたイベント、つまり私たちはいつも同じ検索方法でデータをロックします.
インデックスを作成するときに、SQLクエリーに使用するカラムを考慮し、1つ以上のインデックスを作成します.実際には、インデックスもテーブルであり、プライマリ・キーまたはインデックス・フィールドが保存され、各レコードを実際のテーブルに指向できるポインタです.データベース・ユーザーにはインデックスが表示されません.クエリーを高速化するために使用されます.データベース検索エンジンは、インデックスを使用してレコードを迅速に位置決めします.
INSERTとUPDATE文はインデックスを持つテーブルで実行するのに時間がかかりますが、SELECT文はより速く実行されます.これは、挿入または更新を行う場合にも、データベースがインデックス値を挿入または更新する必要があるためです.
二.インデックスの作成、削除
索引の種類:

UNIQUE(ユニークインデックス):同じ値は使用できません.NULL値

を使用できます.

INDEX(通常のインデックス):同じインデックスコンテンツを許可する

PROMARY KEY(プライマリ・キー・インデックス):同じ値が表示されない

fulltext index(全文インデックス):値の単語に対して使用できますが、効率は確かに

をお世辞にも言えません.

コンポジットインデックス:実質的には複数のフィールドを1つのインデックスに作成し、カラム値のコンポジットは

のみでなければなりません.
(1)ALTER TABLE文を使用した索性の作成
テーブルの作成が完了したら追加します.

ALTER TABLE    ADD      （unique,primary key,fulltext,index）[   ]（   ）

//    
alter table table_name add index index_name (column_list) ;
//    
alter table table_name add unique (column_list) ;
//    
alter table table_name add primary key (column_list) ;

ALTER TABLEは、通常のインデックス、UNIQUEインデックス、PRIMARY KEYインデックスの3種類のインデックスフォーマットを作成するために使用できます.table_nameはインデックスを追加するテーブル名、column_listは、どのカラムをインデックスするかを示し、複数カラムの場合、各カラム間をカンマで区切る.インデックス名index_nameはオプションで、デフォルトではMySQLは最初のインデックス列に基づいて名前を付けます.また、ALTER TABLEでは、単一の文で複数のテーブルを変更できるため、複数のインデックスを同時に作成できます.
(2)CREATE INDEX文を使用してテーブルにインデックスを追加
CREATE INDEXは、テーブルに通常のインデックスまたはUNIQUEインデックスを追加し、テーブルの作成時にインデックスを作成するために使用できます.

CREATE INDEX index_name ON table_name(username(length)); 

CHARの場合、VARCHRタイプであれば、lengthはフィールドの実際の長さより小さくてもよい.BLOBとTEXTタイプの場合は、lengthを指定する必要があります.

//create         ;
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

　　table_name、index_nameとcolumn_リストにはALTER TABLE文と同じ意味があり、インデックス名はオプションではありません.また、CREATE INDEX文でPRIMARY KEYインデックスを作成することはできません.
(3)索引の削除
インデックスを削除するには、ALTER TABLE文またはDROP INDEX文を使用します.DROP INDEXは、ALTER TABLE内部で文として処理できます.フォーマットは次のとおりです.

drop index index_name on table_name ;

alter table table_name drop index index_name ;

alter table table_name drop primary key ;

ここで、前の2つの文では、table_が削除されています.nameのインデックスindex_name.最後の文では、PRIMARY KEYインデックスを削除する場合にのみ使用されます.1つのテーブルにPRIMARY KEYインデックスが1つしかないため、インデックス名を指定する必要はありません.PRIMARY KEYインデックスが作成されていないが、テーブルに1つ以上のUNIQEインデックスがある場合、MySQLは最初のUNIQEインデックスを削除します.
テーブルからカラムを削除すると、インデックスが影響を受けます.複数のカラムの組合せのインデックスの場合、カラムのいずれかを削除すると、そのカラムもインデックスから削除されます.インデックスを構成するすべてのカラムを削除すると、インデックス全体が削除されます.
(4)結合インデックスと接頭辞インデックス
ここでは、インデックスと接頭辞インデックスを組み合わせることは、インデックスの作成テクニックに対する呼称であり、インデックスのタイプではないことを指摘します.より明確に記述するために、demoテーブルを以下のように作成します.
 

create table USER_DEMO
(
   ID                   int not null auto_increment comment '  ',
   LOGIN_NAME           varchar(100) not null comment '   ',
   PASSWORD             varchar(100) not null comment '  ',
   CITY                 varchar(30) not null comment '  ',
   AGE                  int not null comment '  ',
   SEX                  int not null comment '  (0:  1： )',
   primary key (ID)
);

 
mysqlの効率をさらに搾取するためには、LOGIN_NAME、CITY、AGEはインデックスに構築されています.

ALTER TABLE USER_DEMO ADD INDEX name_city_age (LOGIN_NAME(16),CITY,AGE); 

テーブル作成時、LOGIN_NAMEの長さは100です.ここで16を使うのは、一般的に名前の長さが16を超えないためです.これにより、インデックス・クエリーの速度が速くなり、インデックス・ファイルのサイズが減少し、INSERT、UPDATEの更新速度が向上します.
それぞれLOGIN_にあげるとNAME,CITY,AGEは,テーブルに3つの単列インデックスを持たせる単列インデックスを確立し,クエリ時と組合せインデックスの効率は大きく異なり,我々の組合せインデックスよりもはるかに低い.このとき3つのインデックスがありますが、mysqlはその中の1つしか使用できません.最も効率的な単列インデックスのように見えますが、他の2つは使用できません.つまり、フルテーブルスキャンのプロセスです.
このような組合せインデックスを作成することは、次の3つの組合せインデックスをそれぞれ作成することに相当します.

LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME

なぜCITYやAGEなどのような組み合わせインデックスがないのでしょうか?これはmysqlコンビネーションインデックス「最左接頭辞」の結果のためです.簡単な理解では、左側からのみ組み合わせられます.この3つのカラムを含むクエリがその組み合わせインデックスを使用するわけではありません.つまりname_city_age(LOGIN_NAME(16),CITY,AGE)は左から右へインデックスを行い,左前インデックスがなければmysqlはインデックスクエリを実行しない.
インデックス列の長さが長すぎると、このような列インデックスの場合、大きなインデックスファイルが生成され、操作が不便になります.接頭辞インデックス方式でインデックスを行うことができます.接頭辞インデックスは適切な点に制御され、0.31の金の値に制御されます(この値より大きいと作成できます).

SELECT COUNT(DISTINCT(LEFT(`title`,10)))/COUNT(*) FROM Arctic; --      0.31         ,Distinct   

ALTER TABLE `user` ADD INDEX `uname`(title(10)); --       SQL,         10,            ,        

三.インデックスの使用および注意事項
EXPLAINは開発者がSQLの問題を分析するのに役立ち、explainはmysqlがselect文と接続テーブルを処理するためにインデックスをどのように使用するかを示し、より良いインデックスを選択し、より最適化されたクエリー文を書くのに役立ちます.
使用方法は、select文の前にExplainを付けてください.

Explain select * from user where id=1;

インデックスを付けないsqlをできるだけ避ける:
 

SELECT `sname` FROM `stu` WHERE `age`+10=30;--       ,            

SELECT `sname` FROM `stu` WHERE LEFT(`date`,4) <1990; --       ,         ,       

SELECT * FROM `houdunwang` WHERE `uname` LIKE'  %' --    

SELECT * FROM `houdunwang` WHERE `uname` LIKE "%  %" --     

--           ,       ,      SQL     regexp      

--              ;
CREATE TABLE `a` (`a` char(10));
EXPLAIN SELECT * FROM `a` WHERE `a`="1" --    
EXPLAIN SELECT * FROM `a` WHERE `a`=1 --     

select * from dept where dname='xxx' or loc='xx' or deptno=45 --      or,               。   ,           ,       ,             or    

--   mysql               ,      

 
インデックスは多くのメリットがありますが、インデックスの使用が多すぎると逆の問題が発生し、インデックスにも欠点があります.

インデックスはクエリ速度を大幅に向上させますが、INSERT、UPDATE、DELETEなどのテーブルの更新速度を低下させます.テーブルを更新する場合、mysqlはデータを保存するだけでなく、インデックスファイル

も保存するからです.

インデックスを作成すると、ディスク領域を占有するインデックスファイルが作成されます.一般的にこの問題はそれほど深刻ではありませんが、大きなテーブルに複数の組合せインデックスを作成すると、インデックスファイルが広くなります.
インデックスは効率を向上させる方法の1つにすぎません.mysqlに大きなデータ量のテーブルがある場合は、最適なインデックスの作成やクエリー文の最適化に時間がかかります.
インデックスを使用するには、次のテクニックがあります.
    1.インデックスにNULLのカラムは含まれません
カラムにNULL値が含まれている限り、インデックスには含まれません.複合インデックスにNULL値が含まれているカラムがある限り、この一致インデックスは無効です.
    2.短いインデックスの使用
シリアル・カラムをインデックス化し、可能な場合は接頭辞の長さを指定します.たとえば、char(255)のカラムが1つある場合、最初の10文字または20文字以内に複数の数値が一意である場合、カラム全体をインデックス化しないでください.短いインデックスを使用すると、クエリの速度が向上するだけでなく、ディスク容量とI/O操作を節約できます.
    3.索引列のソート
mysqlクエリは1つのインデックスのみを使用するため、where句でインデックスが使用されている場合、order byのカラムはインデックスを使用しません.したがって、データベースのデフォルトのソートが要求に合致する場合は、ソート操作を使用しないでください.できるだけ複数のカラムのソートを含まないでください.必要に応じて、これらのカラムに複合インデックスを作成したほうがいいです.
    4.like文操作
一般的にlike操作は奨励されず、使用しなければならない場合は、正しい使用方法に注意します.like'%aaa%'はインデックスを使用しませんが、like'aaa%'はインデックスを使用できます.
    5.列で演算しない
    6.NOT IN、<>、!=操作しますが、>=,BETWEEN,INはインデックスに使用できます.
    7.インデックスは、select操作が頻繁に行われるフィールドに作成されます.
これは、これらのカラムがあまり使われていない場合、インデックスの有無ではクエリの速度が著しく変更されないためです.逆に,インデックスが増加するため,システムのメンテナンス速度が低下し,空間的要件が増大する.
    8.インデックスは、値比較が一意のフィールドに作成されます.
    9.text、image、bitデータ型として定義されているカラムにインデックスを追加するべきではありません.これらのカラムのデータ量はかなり大きいか、値が少ないためです.
    10.whereおよびjoinに表示されるカラムは、インデックスを作成する必要があります.
    11.whereのクエリー条件には不等号(where column!=...)があり、mysqlではインデックスを使用できません.
    12.where文のクエリー条件に関数(where DAY(column)=...)が使用されている場合、mysqlはインデックスを使用できません.
    13.join操作では(複数のデータテーブルからデータを抽出する必要がある場合)、mysqlはプライマリ・キーと外部キーのデータ型が同じ場合にのみインデックスを使用します.そうしないと、インデックスがタイムリーに確立されても使用されません.