MYSQL索引
2020-01-21 本文已影响0人
充满智慧的白痴
Explain详解
| 字段 | 描述 |
|---|---|
| id | 查询序号,id相同,执行顺序从上往下,id不同,id值越大，优先级越高,越先执行 |
| select_type | 普通查询，联合查询，子查询 1.simple-简单查询不包含子查询或者union 2.primary-查询中包裹复杂子部分，最外层被标记 3.subquery—在select或where列表中包含了子查询 4.derived—在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5.union—如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6.union result:UNION 的结果 |
| table | 输出的行所引用的表 |
| type |
1.system:系统表 2.const,主键或者unique索引 3.eq_ref:唯一性索引扫描，主键或者unique索引 4.ref:非唯一性索引扫描,返回匹配某个单独值所有行，可能会找到多购 5.range:检索一定范围,一般是between,in 6.index:只遍历索引树，只比all快 7.all:遍历权标找到匹配行 |
| possible_keys | 可能使用到的索引 |
| key | 实际使用到的索引,强行不使用索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。 |
| key_len | 决定使用键的长度 |
| ref | 使用哪个列和行或者常数与key一起从表中选择行 |
| rows | 显示mysql认为它执行查询时必须检查的函数 |
| extra | 查询的详细信息 |
控制索引使用
-- 强制使用某个索引
select * from table_name force index(index_name) where ...
-- 强制不使用某个索引
select * from table_name ignore index(index_name) where ...
索引
// 创建索引
create index index_name on table_name(column_name)
alter table table_name add index index_name(column_name)
create table table_name (id int not null,username varchar(16) not null,index[index_name] (username))
// 删除索引
drop index [index_name] on table_name;
// 唯一索引
create unique index index_name on table_nam(username);
alter table table_name add unique [index_name] (username(length))
// 全文索引
alter table table_name add fulltext index_name(column_list);
索引的创建和删除
- 索引的类型
1.UNIQUE 不可以出现相同的值，可以有NULL值
2.INDEX 允许出现相同的索引内容
3.PRIMARY KEY 不允许出现相同的值
4.FULLTEXT INDEX 全文索引，很慢
5.组合索引，多个字段建立到一个索引但是列值得组合必须唯一 - 创建索引
-- 应用于表创建完后
ALTER TABLE table_name ADD [unique,primary key,fulltext,index] index_name（columns_name）
-- 创建表的时候使用
CREATE INDEX index_name ON table_name(columns_name(length))
注：不能用create index语句创建primary key
-- 删除索引
DROP INDEX index_name ON table_name
ALTER table table_name DROP INDEX index_name
ALTER table table_name DROP primary key
注：如果从表中删除某列，则索引会受影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
- 组合索引和前缀索引
ALTER TABLE user ADD INDEX name_city_age(LOGIN_NAME(16),CITY,AGE)
等同于建立了:
LOGIN_NAME,CITY,AGE
LOGIN_NAME,CITY
LOGIN_NAME
三个索引
1注:建表时字段长度和索引时字段长度可以不同，这样可以减少索引的存储空间
2注:为什么没有CITY,AGE这样的组合呢，因为组合索引只有满足前一个才会去查找下一个索引
-- 黄金点计算公式，该值大于0.31就可以建立前缀索引，前缀值为计算值10
SELECT COUNT(DINSTINCT(LEFT(columns_name,10)))/COUNT(*) FROM table_name
ALTER TABLE table_name ADD INDEX columns_name (columns_name(10))
3注:如果索引列过长，建立索引的的文件就会非常大，所以可以使用前缀索引，前缀索引应该控制在一个合适的点0.31,即前n个字符不重复值占所有值的0.31即可建立前缀索引
索引使用注意事项
- 避免不走索引的sql
SELECT 'sname' FROM 'stu' WHERE 'age'+10 = 30 -- 索引列参与计算,不会使用索引
SELECT 'sname' FROM 'stu' WHERE LEFT('date',4) < 1990 --不会使用索引，因为使用了函数运算
SELECT * FROM 'user' WHERE 'uname' LIKE ‘张%’ --使用索引
SELECT * FROM 'user' WHERE 'uname' LIKE '%张%' --不使用索引
-- 正则表达式不使用索引，所以在sql中很难看见regexp关键字
-- 不同数据类型比较不使用索引
CREATE TABLE 'A' ('A' char(10))
EXPLAIN SELECT * FROM 'A' WHERE 'a' = "1" -- 走索引
EXPLAIN SELECT * FROM 'A' WHERE 'a' = 1 -- 走索引
注：如果条件中有or，必须所有条件相关字段都建立索引才会使用索引，否者即使其中某个条件带索引也不会使用，所以建议尽量避免使用or关键字
索引技巧
-
索引不会包含有NULL列
如果索引列包含null值，null值会单独归为一块，索引也会产生效果 -
使用短索引
如果一个列的值前10个或者20个多数是唯一的，可以建立索引 -
索引列排序
-
like语句操作
不鼓励使用like，但是like ‘aaa%’ 也是可以使用索引的 -
不要在索引列上进行运算
索引列值不进行运算或者使用函数，而是提供一个确定值 -
操作符
不使用NOT IN , <>,!=;可以使用<,<=，=，>,>=,BETWEEN,IN -
索引要建立在经常进行select操作的字段上
-
索引要建立在值比较唯一的字段上
-
对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少。
-
在where和join中出现的列需要建立索引（重点）
-
在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。
