mysql索引实战练习

一、索引相关概念

1.索引概念

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

2.作用

1. 快速取数据;
2. 保证数据记录的唯一性;
3. 实现表与表之间的参照完整性;
4. 在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

3.优点

1. 大大加快数据的检索速度;
2. 创建唯一性索引，保证数据库表中每一行数据的唯一性;
3. 加速表和表之间的连接;
4. 在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

4.缺点

1. 索引需要占物理空间。
2. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

5.索引类型

1. 普通索引：仅加速查询

2. 唯一索引：加速查询 + 列值唯一（可以有null）

3. 主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个

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

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

6.（组合索引）联合索引的好处:

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

联合索引的最左原则
在使用联合索引的时候，我们要遵守一个最左原则,即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没有使用索引查询，与我们理论一致。