请选择 进入手机版 | 继续访问电脑版

mysql索引实战练习

[复制链接]
科达工艺 发表于 2021-1-2 19:44:23 | 显示全部楼层 |阅读模式 打印 上一主题 下一主题
一、索引相关概念

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创建一个有几千条数据的表,不加主键,接纳存储过程循环插入
  1. delimiter $$ #将语句的竣事符号从分号;暂时改为两个$$(可以是自界说)
复制代码

  1. 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没有使用索引查询,与我们理论一致。

来源:https://blog.csdn.net/weixin_44197026/article/details/112029376
免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则


专注素材教程免费分享
全国免费热线电话

18768367769

周一至周日9:00-23:00

反馈建议

27428564@qq.com 在线QQ咨询

扫描二维码关注我们

Powered by Discuz! X3.4© 2001-2013 Comsenz Inc.( 蜀ICP备2021001884号-1 )