MYSQL索引

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只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用。