1.联合索引,最左匹配原则
2.索引与order by
1) 如果select 只查询索引字段,order by 索引字段会用到索引,要不然就是全表排列;
2) 如果有where 条件,比如where vtype=1 order by vtype asc . 这样order by 也会用到索引!
3) 综上,如果order by 真的影响limit的话,那么就请在没有where 查询的时候order by id(主键), 有where 查询的时候,order by (索引) 字段。
3.分页直接用limit 导致大数据量的时候后面页数会很慢。
4.mysql explain
type 重要的项,显示连接使用的类型,按最 优到最差的类型排序 | 说明 |
system | 表仅有一行(=系统表)。这是 const 连接类型的一个特例。 |
const | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。 |
eq_ref | const 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。 |
ref | 连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值 |
ref_or_null | 如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。 |
index_merge | 说明索引合并优化被使用了。 |
unique_subquery | 在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr) |
index_subquery | 在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr) |
range | 只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。 |
index | 全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。 |
all | 最坏的情况,从头到尾全表扫描。 |
4.避免无计划的全表扫描
如下情况进行全表扫描:
- 该表无索引
- 对返回的行无人和限制条件(无Where子句)
- 对于索引主列(索引的第一列)无限制条件
- 对索引主列的条件含在表达式中
- 对索引主列的限制条件是is (not) null或!=
- 对索引主列的限制条件是like操作且值是一个bind variable或%打头的值
5 只使用选择性索引
索引的选择性是指索引列中不同值得数目和标志中记录数的比,选择性最好的是非空列的唯一索引为1.0。
复合索引中列的次序的问题:
1 在限定条件里最频繁使用的列应该是主列
2 最具有选择性的列(即最清晰的列)应该是主列
如果1和2 不一致,可以考虑建立多个索引。
复合索引和多个单个索引中作选择:考虑选择性 考虑读取索引的次数 考虑AND-EQUAL操作
6 Mysql limit分页语句用法
--语法:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
--举例:
select * from table limit 5; --返回前5行
select * from table limit 0,5; --同上,返回前5行
select * from table limit 5,10; --返回6-15行
当一个查询语句偏移量offset很大的时候,如select * from table limit 10000,10 , 最好不要直接使用limit,而是先获取到offset的id后,再直接使用limit size来获取数据。效果会好很多。
select * From customers Where customer_id >=(
select customer_id From customers Order By customer_id limit 10000,1
) limit 10;
另一种写法是:
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id