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);