顺心的人大抵一样,坎坷的人各有各的坎坷,也只能坚持自我修行,等待自己的机遇。
1. MySQL 索引的最左前缀原则
左前缀原则是联合索引在使用时要遵循的原则,查询索引可以使用联合索引的一部分,但是必须从最左侧开始。在创建联合索引时,要根据业务需求,where子句中将使用最频繁的一列放在最左边,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)时停止匹配。即范围列可以用到索引,范围列后面的列无法用到索引。
比如查询 a = 1 and b = 2 and c > 3 and d = 4
如果建立 (a,b,d,c) 顺序的索引,d是用不到索引的,如果建立 (a,b,d,c) 的索引则都可以用到,a,b,d的顺序可以任意调整。可以调整 a,b,c 顺序的原因是 MySQL 具有查询优化器
MySQL 查询优化器
当按照索引中所有列进行精确匹配(“=” 或 “IN”)时,索引可以被用到,并且 type 为 const。理论上索引对顺序是敏感的,但是 MySQL 的查询优化器会自动调整 where 子句的条件顺序以使用适合的索引,所以 MySQL 在进行精确匹配时不存在因 where 子句的顺序问题而造成索引失效。
2. 前缀索引
定义: 对于BLOB、TEXT,或者很长的VARCHAR类型的列,为它们的前几个字符(具体几个字符是在建立索引时指定的)建立索引,这样的索引就叫前缀索引。
优点: 这样建立起来的索引更小,所以查询更快。
缺点: 不能在 ORDER BY 或 GROUP BY 中使用前缀索引,也不能把前缀索引用作覆盖索引。
创建方法:
alter table table_name add key( column_name( prefix_length));
注:这里最关键的参数就是 prefix_length,这个值需要根据实际表的内容,来得到合适的索引选择性。
prefix_length 计算方法:
- 先计算完整列的选择性 :
select count(distinct col_1)/count(1) from table_1
- 再计算不同前缀长度的选择性 :
select count(distinct left(col_1,4))/count(1) from table_1
- 到最优长度之后,创建前缀索引 :
create index idx_front on table_1 (col_1(4))
3. 索引下推(ICP——Index Condition Pushdown)
定义: 索引下推 Index Condition Pushdown(ICP) 是MySQL使用索引从表中检索行数据的一种优化方式,从 MySQL5.6 开始支持。5.6 之前,存储引擎会通过遍历索引定位基表中的行,然后返回给 Server层,再去为这些数据行进行 WHERE 后的条件的过滤。MySQL5.6之后支持 ICP,如果WHERE条件可以使用索引,MySQL 会把这部分过滤操作放到存储引擎层,存储引擎通过索引过滤,把满足的行从表中读取出。ICP 能减少引擎层访问基表的次数(回表次数)和 Server层访问存储引擎的次数。
MySQL通过 optimizer_switch 参数中的 index_condition_pushdown 选项来控制,默认是开启的。
操作:
- 查看是否开启
show variables like'% optimizer_switch%';
- 设置 ICP
SET optimizer_switch = ‘index_condition_pushdown=off’;
例子:
在 people_table中有一个二级索引(zipcode,lastname,firstname),查询是
SELECT * FROM people WHERE zipcode=’95054′ AND lastname LIKE ‘%etrunia%’ AND address LIKE ‘%Main Street%’;
- 如果没有使用索引下推技术,则MySQL会通过zipcode=’95054’从存储引擎中查询对应的数据,返回到MySQL服务端,然后MySQL服务端基于lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断数据是否符合条件
- 如果使用了索引下推技术,则MYSQL首先会返回符合zipcode=’95054’的索引,然后根据lastname LIKE ‘%etrunia%’ and address LIKE ‘%Main Street%’来判断索引是否符合条件。如果符合条件,则根据该索引来定位对应的数据,如果不符合,则直接reject掉。
4. 查看 MySQL 语句是否用到索引
1. 方法一:通过 show index 查看表中含那些索引
show index from table_name
2. 方法二:通过 explain 命令查看 SQL 语句的执行计划:
例子:
explain select * from t where name = 'name1';
- 我们可以先从查询类型type列开始查看,如果出现all关键字,后面的内容就都可以不用看了,代表全表扫描。—— const(主键索引或者唯一二级索引进行等值匹配的情况下),ref(普通的⼆级索引列与常量进⾏等值匹配),index(扫描全表索引的覆盖索引)
- 再看key列,看是否使用了索引, null代表没有使用索引。
- 然后看rows列,该列用来表示在SQL执行过程中被扫描的行数,该数值越大,意味着需要扫描的行数越多,相应的耗时就更长。
- 最后再看extra列,在这列中要观察是否有Using filesort或者Using temporary这样的关键字出现,这些是很影响数据库性能的。
- MySQL5.7的执行计划中会默认添加filtered列 (MySQL5.6 使用 explain extended 也会增加此列),它指返回结果的行占需要读到的行(rows 列的值)的百分比。需要注意的是, explain中输出的rows只是一个估算值。本例中该表进行了全表扫描。
5. 为什么官方建议用自增长主键作为索引
减少分裂和移动的频率: 结合B+Tree的特点,自增主键是连续的,在插入过程中能尽量减少页分裂,即使要进行页分裂,也只会分裂很少一部分。并且能减少数据的移动,每次插入都是插入到最后。
6. 如何创建索引
1. 在执行 CREATE TABLE 时创建索引
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
2. 使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
ALTER TABLE 可用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
其中 table_name 是要增加索引的表名,column_list 指出对哪些列进行索引,多列时各列之间用逗号分隔。
索引名 index_name 可自己命名,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。
3. 使用CREATE INDEX命令创建。
CREATE INDEX index_name ON table_name (column_list);
7. 创建索引注意事项
- 选择性低的字段不要创建索引(例如,性别sex、状态status)。
- 很少查询的列不要创建索引(项目初期就要确定好)。
- 大数据类型字段不要创建索引。
- 尽量避免不要使用NULL,应该指定列为NOTNULL(在MySQL中,含有空值的列很难进行查询优化,它们会使得索引、索引的统计信息及比较运算更加复杂。可以使用空字符串代替空值)。
8. 使用索引一定可以提高查询性能嘛
通常通过索引查询数据比全表扫描要快。但是我们也必须注意到它的代价。索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时,索引本身也会被修改。 这意味着每条记录的I* NSERT,DELETE,UPDATE将为此多付出4,5 次的磁盘I/O。 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢。使用索引查询不一定能提高查询性能。
9. 索引失效
- 通过索引扫描的行记录数超过全表的30%,优化器就不会走索引,而变成全表扫描。
- 联合索引中,第一个查询条件不是最左索引列。 —— 优化器
- 联合索引中,第一个查询条件不是最左前缀列。—— 优化器
- 联合索引中,第一个索引列使用范围查询,只能使用到部分索引,有ICP出现 (范围查询是指<、=、<=、BETWEEN and)。
- 模糊查询条件列最左以通配符%开始(可以考虑放到子查询里面)。
- 两个单列索引,一个用于检索,一个用于排序。这种情况下只能使用到一个索引。因为查询语句中最多只能使用一个索引,考虑建立联合索引。
- 查询字段上面有索引,但是使用了函数运算。