MySQL细节知识2

参考资料：
https://www.bilibili.com/video/BV1DE411n7fU?p=58

MySQL之索引（index）

简介及基本情况

索引的用途在于约束和加速查找。
索引种类（按存储的数据结构类型划分）：

• hash索引：查找单值快，查找范围数据较慢
• b-tree索引：二叉树

建立索引：

• 创建额外的文件保存特殊的数据结构
• 查询快，插入、更新、删除数据慢（因为创建的加速文件也需要修改）
• 命中索引

--普通索引
create index 索引名称 on 表名（列名）
drop index 索引名称 on 表名
--唯一索引
create unique index 索引名称 on 表名（列名）
drop unique index 索引名称 on 表名
--联合索引
create unique index 索引名称 on 表名（列名, 列名）
drop unique index 索引名称 on 表名

--PS:假设我们为'email'列创建了一个索引
--select * from t1 where email = 'asd';(快， 走索引)
--select * from t1 where email ilke 'asd'(慢，没有利用索引，只会遍历)

组合索引和索引合并的区别

--组合索引
create unique index 索引名称 on 表名（列名， 列名）
drop unique index 索引名称 on 表名
eg: create unique index ix_name_email on userinfo(name, email)


--最左前缀匹配
select * from userinfo where name = "alex";--走索引
select * from userinfo where name = "alex" and email = "asdf";--走索引

select * from userinfo where email = "asdf";--不走索引
--组合索引的效率高于索引组合
--组合索引（name, email）
select * from userinfo where name = "alex";--走索引
select * from userinfo where name = "alex" and email = "asdf";--走索引
--索引组合
--name
--email
select * from userinfo where name = "alex";
select * from userinfo where name = "alex" and email = "asdf";
select * from userinfo where email = "asdf";

PS:

1. 覆盖索引：在索引文件中直接获取数据
2. 索引合并：把多个单列索引合并使用
   我们需要为频繁查找的列建立索引，以求加速查找。首先，创建索引；其次，命中索引。
   在数据量大时，查找的过程中，尽量避免使用like, 这时会慢。因此在公司中，很少使用Like。

索引+函数

--reverse()
select * from t1 where reverse(email) = "asdf";

--or
select * from t1 where nid = 1 or email = "asdf";
--注意：当or条件中有未创建索引的列时，才会失效，以下会走索引
select * from t1 where nid = 1 or name = "alex";
select * from t1 where nid = 1 or name = "alex" and email = "asdf";

--类型不一致
--如果列是字符串类型，则传入的条件必须用引号，否则...
select * from t1 where name = 999;

--!=
select * from t1 where name != "alex";
--特别地， 如果是主键，此时仍然会走索引
select * from t1 where nid != 1;

-->
select * from t1 where name > "alex";
--特别地， 如果是主键或者索引是整数类型，此时仍然会走索引
select * from t1 where nid > 1;
select * from t1 where num > 1;

--order by
select name from t1 order by email desc;
--注意，当根据索引排序的时候，如果select的列不是索引，则不走索引；特别的，如果按主键排序，则还是会走索引
select name from t1 order by nid desc;

其他注意事项

1. 尽量避免使用select *
2. count(1)或者count(列名) 代替count(*)
3. 创建表时，尽量用char替代varchar(可以加速查找)
4. 表的字段顺序固定长度的字段优先
5. 经常使用多个条件查询时，要选择组合索引，代替多个单列索引
6. 尽量使用短索引
7. 使用连接（join）代替子查询(sub-queries)
8. 连表时注意条件中类型应该一致
9. 列中值的类别少时，不宜建立索引，如：性别

索引的效率

执行计划：用mysql来预估执行操作需要的时间（一般来说是正确的）
速度排序：
all < index < range < index_merge < ref_or_nul < ref < eq_ref < system/const

慢：

select * from t1 where name = "alex";

explain select * from t1 where name = "alex";
--type:ALL
--特例：select * from t1 limit 1;的type: ALL

快：

select * from t1 where email = "asdf";
--type:const, 走索引

慢日志

• 执行时间 > 10
• 未命中索引
• 日志文件路径

配置

• 内存
  • show variables like “%query%”
  • set global 变量=值
• 配置文件

  • mysql --default-file = “路径/my-default.ini”

  • my.conf内容：
    slow_query_log = ON
    slow_query_log_file = D:/…
    PS:修改配置文件后，需要重启