SQLiteのインデックスについて

インデックスについて

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【インデックス】
インデックスとは、テーブルに含まれるデータの索引のような役割を果たす為、インデックスを作成しておくことでデータの検索を高速に行う事が、出来る。

■CREATE INDEX文を使ったインデックスの作成
Microsoft Windows [Version 10.0.18363.900]
(c) 2019 Microsoft Corporation. All rights reserved.

C:\Users\shyok>cd D:\pg\sqlite3

C:\Users\shyok>d:

D:\pg\sqlite3>sqlite3 D:\pg\sqlite3\myfriend.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
テーブル作成
sqlite> create table user(name text, old integer, address text);
sqlite> .tables
Tuser numtest1 personal product staff tokyouser user
データ入力
sqlite> insert into user values('Yamada', 28, 'Tokyo');
sqlite> insert into user values('Mori', 22, 'Osaka');
sqlite> insert into user values('Suzuki', 32, 'Tokyo');
sqlite> insert into user values('Takagi', 23, 'Tokyo');
sqlite> insert into user values('Suzuki', 23, 'Osaka');
sqlite> .heade on
sqlite> .mode column
表示
sqlite> select * from user;
name old address
---------- ---------- ----------
Yamada 28 Tokyo
Mori 22 Osaka
Suzuki 32 Tokyo
Takagi 23 Tokyo
Suzuki 23 Osaka
sqlite>
インデックスが作成
sqlite> create index nameindex on user(name);
sqlite>
インデックス表示
sqlite> .indices
nameindex
sqlite>

■インデックスを利用したデータの検索
Microsoft Windows [Version 10.0.18363.900]
(c) 2019 Microsoft Corporation. All rights reserved.
C:\Users\shyok>cd D:\pg\sqlite3
C:\Users\shyok>d:

D:\pg\sqlite3>sqlite3 D:\pg\sqlite3\myfriend.sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite>
sqlite> .heade on
sqlite> .mode column
sqlite>
sqlite> select * from user where name = 'Suzuki';
name old address
---------- ---------- ----------
Suzuki 32 Tokyo
Suzuki 23 Osaka
sqlite>
■UNIQUEインデックスの作成
書式
CREATE UNIQUE INDEX インデックス名
ON テーブル名(カラム名1, カラム名2, ...);
例文
name カラムを対象としたユニークインデックスを作成する
sqlite> select * from user;
name old address
---------- ---------- ----------
Yamada 28 Tokyo
Mori 22 Osaka
Suzuki 32 Tokyo
Takagi 23 Tokyo
対象するカラム（name カラム）に重複した値が無いか調べ、有ったらデータ削除する。
■ユニークインデックスの作成
sqlite> create unique index nameindex on user(name);
sqlite>
重複する値を入れてみる
sqlite> insert into user values('Suzuki', 18, 'Osaka');
Error: UNIQUE constraint failed: user.name
sqlite>
■sqlite_masterテーブルから取得する
sqlite> select * from sqlite_master;
type name tbl_name rootpage sql
---------- ---------- ---------- ---------- ------------------------------
table personal personal 2 CREATE TABLE personal(id,name)
table numtest1 numtest1 4 CREATE TABLE numtest1(id integ
table Tuser Tuser 5 CREATE TABLE Tuser (id integer
table sqlite_seq sqlite_seq 7 CREATE TABLE sqlite_sequence(n
table staff staff 3 CREATE TABLE staff(no integer,
index sqlite_aut staff 9
table product product 10 CREATE TABLE product(id intege
view tokyouser tokyouser 0 CREATE VIEW tokyouser as selec
table user user 6 CREATE TABLE user(name text, o
index nameindex user 8 CREATE UNIQUE INDEX nameindex
sqlite>
sqlite> select * from sqlite_master where type = 'index';
type name tbl_name rootpage sql
---------- ------------------------ ---------- ---------- ----------
index sqlite_autoindex_staff_1 staff 9
index nameindex user 8 CREATE UNI
sqlite>
sqlite> .schema
CREATE TABLE personal(id,name);
CREATE TABLE numtest1(id integer);
CREATE TABLE Tuser (id integer, name text);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE staff(no integer, unit text, name text, unique(no, unit));
CREATE TABLE product(id integer, name text default 'no name', price integer default 0);
CREATE VIEW tokyouser as select id, name from user where address = 'Tokyo';
CREATE TABLE user(name text, old integer, address text);
CREATE UNIQUE INDEX nameindex on user(name);
sqlite>
インデックスの削除
sqlite> drop index nameindex;
sqlite> .tables
Tuser numtest1 personal product staff tokyouser user
sqlite>