嵌入索引提升速度

通过索引提高数据的查询速度

1.普通索引

一种没有任何约束的索引，对表中变量的值不作任何限制，不管变量的值是否存在重复或是否存在缺失（即null）都无关紧要，是使用最频繁的一种索引
①普通索引

#建表示设置索引
create table table_name(
filed1 data_type1 filed_attr1,
filed2 data_type2 filed_attr2,
filed3 data_type3 filed_attr3,
...

index index_name(filed1)
)

#对已有表添加索引，可通过创建法或修改法
creat index indes_name on table_name(file_list)
#基于已有标的直接创建法
alter table table_name add index index_name on(file_list)
#基于已有标的表结构修改法

示例：

# 创建学生的一卡通消费表
CREATE TABLE stu_card(
stu_id VARCHAR(10),
custom_class VARCHAR(10),
custom_add VARCHAR(20),
custom_type VARCHAR(20),
custom_date DATETIME,
amt FLOAT,
balance FLOAT
);

# 一卡通消费表记录的导入
LOAD DATA INFILE 'E:\\sql_data\\card.txt' 
INTO TABLE stu_card 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
TRUNCATE TABLE stu_card;

#创建索引 
CREATE INDEX date_index ON stu_card(custom_date);
#创建索引后，搜索custom_date 速度会极快
USE trin;
SELECT 
  * 
FROM
  stu_card 
WHERE custom_date BETWEEN '2013-09-01 00:00:00' 
  AND '2013-09-01 23:59:59' ;

2.唯一索引

唯一索引对变量或变量组合是有约束的，即必须确保变量或变量组合的每一个观测值都是唯一的，不能存在重复观测值。如果含多个空白字符“”，即重复观测
一张表可以创建多个唯一索引，基于表中的变量或变量组合构造语法：

#建表示设置索引,将filed1和filed设置为唯一索引
create table table_name(
filed1 data_type1 filed_attr1,
filed2 data_type2 filed_attr2,
filed3 data_type3 filed_attr3,
...
unique index_name(filed1,files2)


#对已有表添加索引
create unique index index_name on table_name(filed_list)
alter table table_name add unique index_name on(filed_list)

示例：

# 新建数据表tourism_orders
CREATE TABLE tourism_orders(
userid VARCHAR(20),
orderid VARCHAR(12),
orderTime VARCHAR(15),
orderType VARCHAR(2),
city VARCHAR(20),
country VARCHAR(20),
continent VARCHAR(10));

# 往表中插数据
LOAD DATA INFILE 'E:\\sql_data\\tourism_orders.csv' 
INTO TABLE tourism_orders 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;


SELECT * FROM tourism_orders;

# 无索引下的条件查询
SELECT *
FROM tourism_orders
WHERE userid = '100000001445';

# 索引下的条件查询
CREATE UNIQUE INDEX id_idx ON tourism_orders(userid,orderid);
SELECT *
FROM tourism_orders
WHERE userid = '100000001445';

3.主键索引

主键索引对变量的要求最为严格，必须确保变量中值既不存在重复值，也不包含缺失值null，而且表中仅能够包含一个主键索引。主键索引创建如下：

create table table_name(
filed1 data_type1 filed_attr1,
filed2 data_type2 filed_attr2,
filed3 data_type3 filed_attr3,
...
primary key(filed1,filed2)
)

#对已有表添加索引
alter table tale_name add primary key index_name(filed_list)

与普通索引和唯一索引创建方法略有不同，没有了create index的方式，而且在alter table的语法中也不需要在变量列表中的括号前加入关键词on。

示例：

#主键索引
CREATE TABLE regit_info(
uid VARCHAR(10),
gender TINYINT,
age TINYINT,
regit_date DATE);
#DROP TABLE regit_info;

CREATE TABLE RFM(
uid VARCHAR(10),
R INT,
F TINYINT,
M DECIMAL(10,2));


LOAD DATA INFILE 'E:\\sql_data\\regit_info.csv'
INTO TABLE regit_info
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

SELECT * FROM regit_info;


LOAD DATA INFILE 'E:\\sql_data\\RFM.csv'
INTO TABLE RFM
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

SELECT * FROM RFM;



SELECT t1.*,t2.`F`,t2.`M`,t2.`R`
FROM regit_info AS t1
INNER JOIN RFM AS t2
ON t1.`uid`=t2.`uid`
LIMIT 1000;




#建立主键索引之后
ALTER TABLE regit_info ADD PRIMARY KEY key_id(uid);
ALTER TABLE RFM ADD PRIMARY KEY key_id1(uid);

SELECT t1.*,t2.`F`,t2.`M`,t2.`R`
FROM regit_info AS t1
INNER JOIN RFM AS t2
ON t1.`uid`=t2.`uid`
LIMIT 1000;

对于不足100万行的两表而言，内连接用了40多秒，速度还是有些慢的

建立了主键索引后，查询时间只需要0.006秒，查询速度得到了质的提升。

索引的查询和删除

1.索引的查询

#索引的查询
show index from table_name;
#索引的删除
drop index index_name on table_name;#用于删除普通索引和唯一索引
alter table table_name drop index index_name;#用于删除普通索引和唯一索引
alter table table_name drop primary key;#用于删除主键索引

示例：

SHOW INDEX FROM regit_info;
SHOW INDEX FROM RFM;

删除类似；

索引的注意事项

1.何时适合建索引

①在where关键字后面的字段创建索引，可加快条件判断速度
②在表连接on关键词后面的字段创建索引，可加快连接速度
③包含大量null值的字段不适合创建索引，因为索引不可以包含null值
④大量相同取值的字段不是很创建索引，因为通过条件筛选可能会产生大量得数据行，以诶这数据库搜索过程中要扫描很大比例的数据行，此时，索引并不能加快扫描速度

2.索引无效的情况

①where关键词后面的条件表达式中如果使用IN，OR，!=或者<>，均会导致索引无效。解决办法：可将表示不等关系的!=或者<>替换为>and<，将表示不为空的is not null 替换成>=cha(0)
②筛选或排序过程中，如果对索引列使用函数时，则索引不能正常使用
③筛选过程中如果字符型的数字写成了数值型的数字，则索引无效（如筛选字符型的用户id时，必须写成where uid=‘12343’）
④使用like关键词做模糊匹配时，通配符%或_不可以写在最前面，否则索引无效（即like关键词后面的通配符不可以写成“%水果”，但可以写成“水果%”）
⑤对于多列的组合索引，遵循左原则，例如对字段A,B,C设置索引index（A,B,C）,则A>100;A=1 and B>10或者A>=100 and B<6 and C>13均可以使多列索引有效，因为最左边的A字段在上面的集中条件中均包含，而B>10或B<4 and C>12均使组合索引无效
⑥在join操作中，on关键词后面的字段类型必须保持一致，否则索引无效