Mysql学习日记-06索引的补充
1、索引
索引是表的目录,在查找内容之前可以先在目录中查找索引位置,以此快速定位查询数据。对于索引,会保存在额外的文件中。
作用 - 约束
-加速查找
分类 - 主键索引 :加速查找 不能为空 不能重复 primary key
- 普通索引 : 加速查找
-唯一素养 : 加速查找 + 不能重复
- 联合索引 (多列)
加速查找 : - 快 select * from tb where name = "abc"
select * from tb where id = 1
索引的种类 (即是某种存储格式2 , 它会重新创建一个特殊的表 排序不同 存储索引和数据)
- hash索引 : 优点 -单值快
缺点- 查找范围的能力不行
- btree 索引 : btree 二叉树
建立索引:
- 额外的文件保存特殊的数据结构格式
- 查询快 但是 插入更新删除慢
- 命中索引
select * from tb where email = ""
- 普通索引 - create index name on tb(id)
-drop index name on tb
-唯一索引 - create unique index name on tb(id)
-drop unique index name on tb
- 组合索引(普通和唯一的结合)
- create unique index name on tb(id, name, email , gender)
- drop unique index name on tb
查找索引时的注意点
-需要命中索引 不准确的命中会使速度变慢 将浪费资源和时间
例如
- like ‘%xx‘
select * from tb1 where email like ‘%cn‘;
- 使用函数
select * from tb1 where reverse(email) = ‘wupeiqi‘;
- or
select * from tb1 where nid = 1 or name = ‘‘;
特别的:当or条件中有未建立索引的列才失效,以下会走索引
select * from tb1 where nid = 1 or name = ‘seven‘;
select * from tb1 where nid = 1 or name = ‘‘ and email = ‘alex‘
- 类型不一致
如果列是字符串类型,传入条件是必须用引号引起来,不然...
select * from tb1 where email = 999;
- !=
select * from tb1 where email != ‘alex‘
特别的:如果是主键,则还是会走索引
select * from tb1 where nid != 123
- >
select * from tb1 where email > ‘alex‘
特别的:如果是主键或索引是整数类型,则还是会走索引
select * from tb1 where nid > 123
select * from tb1 where num > 123
- order by
select name from tb1 order by email desc;
当根据索引排序时候,选择的映射如果不是索引,则不走索引
特别的:如果对主键排序,则还是走索引:
select * from tb1 order by nid desc;
- 组合索引最左前缀
如果组合索引为:(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
DBA工作
-慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
配置:
- 内存
show variables like ‘%query%‘
set global 变量名 = 值
- 配置文件
mysqld --defaults-file=‘E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini‘
my.conf内容:
slow_query_log = ON
slow_query_log_file = D:/....
注意:修改配置文件之后,需要重启服务
总结:使用索引,需要命中索引 不准确的命中会使速度变慢 将浪费资源和时间
在数据库创建中三百万条数据练习索引查询
利用存储过程可轻松完成
DROP PROCEDURE IF EXISTS `p1`$$
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`( IN n INT )
BEGIN
DECLARE v INT DEFAULT 0;
SET autocommit=0;
WHILE v<n
DO
NSERT INTO t2
VALUES(v,‘kk‘,ADDDATE(‘2000-01-01‘,(RAND(v)*36520)MOD 3652));
SET v = v+1;
END WHILE;
SET autocommit=1;
END //
DELIMITER ;
CALL P1(3000000);