mysql详解13：索引

转载

mb5ff9827b65e5b 2021-08-09 09:54:00

索引：存储在磁盘中
影响性能

创建索引
EXPLAIN select * from student where state ="CA";//查询并查看扫描的条数
create INDEX idx_state on student(state); //在该字段上创建索引再查询
创建索引后再查询扫描的条数减少

查看索引
ANALYZE TABLE customers; //可以让索引的统计值不再是估值，更加准确
show INDEX in student;
二级索引
主键和外键都会自动创建索引

前置索引:只包含列的前几个字母或者列前缀
CREATE INDEX idx_lastname on customers (last_name(20));

select count(DISTINCT LEFT(last_name,1)),
count(DISTINCT LEFT(last_name,5)),
count(DISTINCT LEFT(last_name,10)) from customers;
//25 966 996 所以选5

全文索引
select * from posts where title like "%react redux%" or body like "%react redux%";

create FULLTEXT INDEX idx_titile_body on posts(titile,body);
select * MATCH(title,body) AGAINST('react redux')
from posts
where MATCH(title,body) AGAINST('react redux');
//按照搜索相关性排序

select * MATCH(title,body) AGAINST('react redux')
from posts
where MATCH(title,body) AGAINST('react -redux +form ' IN BOOLEAN MODE);
表示结果中必须含有react form 排除redux
"handling a form" 表示搜索这个短语

复合索引

复合索引
允许对多键创建索引最多可以16列
create index idx_state_points on customers(states,point);

Drop index idx_state on customers;

复合索引中列的顺序
正常情况下:要把种类多的放在前面

Select customer_id
From customers
Use index(idx_lastname_state)
Where state like ‘a%’ and last_name like ‘a%’;

要搜索所有的州和名字。所以复合索引把名字放前面

Select customer_id
From customers
Use index(idx_lastname_state)
Where state =‘CA’ and last_name like ‘a%’;

因为=的约束更强，所以把州排在前面效率更高。

复合索引以state开头的对于单列points 没有效率

当索引无效
EXPLAIN SELECT customer_id
from customers where state =‘CA’ or points >1000;
此时idx_state_points 不起作用

EXPLAIN SELECT customer_id from customers where points+10>2010;

想要使用索引必须把列单独表示