mysql索引实战_mysql索引实战练习

一、索引相关概念

1.索引概念

索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。

2.作用

快速取数据;

保证数据记录的唯一性;

实现表与表之间的参照完整性;

在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

3.优点

大大加快数据的检索速度;

创建唯一性索引，保证数据库表中每一行数据的唯一性;

加速表和表之间的连接;

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

4.缺点

索引需要占物理空间。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

5.索引类型

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一(可以有null)

主键索引：加速查询 + 列值唯一(不可以有null)+ 表中只有一个

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

6.(组合索引)联合索引的好处:

减少磁盘空间开销，因为每创建一个索引，其实就是创建了一个索引文件，那么会增加磁盘空间的开销。

联合索引的最左原则

在使用联合索引的时候，我们要遵守一个最左原则,即index(id,age)支持 id 、id 和 age 组合查

询,而不支持单独 age 查询，因为没有用到创建的联合索引。

二、索引的创建方式

1.创建普通索引

CREATE INDEX index_name ON table_name(col_name);

2.创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name(col_name);

3.创建普通组合索引

CREATE INDEX index_name ON table_name(col_name_1,col_name_2);

4.创建唯一组合索引

CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

5.修改表结构创建索引

ALTER TABLE table_name ADD INDEX index_name(col_name);

6.查看索引

SHOW INDEX FROM  table_name;

7.删除索引

DROP INDEX index_id_age ON table_name;

- 查看表结构

desc table_name;

- 查看生成表的SQL

show create table table_name;

- 查看索引

show index from  table_name;

- 查看执行时间

set profiling = 1;

SQL...

show profiles;

三、索引实战测试(mysql)

1.mysql创建一个有几千条数据的表，不加主键，采用存储过程循环插入

delimiter $$ #将语句的结束符号从分号;临时改为两个$$(可以是自定义)

delimiter ;　　#将语句的结束符号恢复为分号

这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

2.用call调用

CALL pro_insert()；执行后数据库就有了几千条数据

3.查询数据并观察查询时间

select * from test where id=7999；

没有索引时，查询时间

4.创建一个组合索引

CREATE INDEX index_id_age ON test(id,age);

5.查看索引、删除索引

查看索引：SHOW INDEX FROM  test;

删除索引：DROP INDEX index_id_age ON test

6.创建索引后，再次查询，并与之前的查询时间做比较

select * from test where id=7999；

创建索引后查询时间

对比之后发现确实快了很多，但是只有当数据量很大时我们才能体会到索引的好处。

7.测试联合索引的最左原则

①SELECT * FROM test WHERE id=6999;

②SELECT * FROM test WHERE id=6999 AND age=6999;

③SELECT *  FROM test WHERE age=6999

当我们查询①、②时，速度明显快于③时，这是因为联合索引的最左原则，我们查询③时，没有用到创建的联合索引。

我们分别查看sql的查询计划

EXPLAIN select * from test where id=6999;

EXPLAIN SELECT * FROM test WHERE id=6999 and age=6999;

EXPLAIN select *  from test where age=6999;

对比发现第三条sql没有使用索引查询，与我们理论一致。