MYSQL之索引

索引

索引优化策略

• 独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧
• 左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估索引选择性：不重复的索引值和数据表的记录总数的比值
• 多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
• 选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

• 只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
• 尽量使用短索引，如果可以，应该制定一个前缀长度
• 对于经常在where子句使用的列，最好设置索引
• 对于有多个列where或者order by子句，应该建立复合索引
• 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
• 尽量不要在列上进行运算（函数操作和表达式操作）
• 尽量不要使用not in和<>操作

SQL语句性能优化

• 查询时，能不要就不用，尽量写全字段名
• 大部分情况连接效率远大于子查询
• 多表连接时，尽量小表驱动大表，即小表 join 大表
• 在有大量记录的表分页时使用limit
• 对于经常使用的查询，可以开启缓存
• 多使用explain和profile分析查询语句
• 查看慢查询日志，找出执行时间长的sql语句优化

管理索引

创建索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...);
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);
help CREATE INDEX;

create index idx_age on  students(age); --在学生表里根据年龄字段建立索引
create index idx_name_age on students(name,age);  --在学生表里建立根据姓名和年龄的复合索引
create unique index uni_age on testlog(age);  --在testlog中根据年龄创建唯一键索引，unique要在index前面

删除索引

DROP INDEX index_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引

SHOW INDEXES FROM [db_name.]tbl_name;

show indexes from students\G;  --查看学生表里的索引列表

优化表空间

OPTIMIZE TABLE tb_name;

查看索引的使用

SET GLOBAL userstat=1;
SHOW INDEX_STATISTICS;

EXPLAIN

分析索引的有效性

explain select *from students where name like 's%' and age<20;

输出信息说明

id: 当前查询语句中，每个SELECT语句的编号
select_type：
简单查询为SIMPLE
复杂查询：
SUBQUERY 简单子查询
PRIMARY 最外面的SELECT
DERIVED 用于FROM中的子查询
UNION UNION语句的第一个之后的SELECT语句
UNION RESULT 匿名临时表
table：SELECT语句关联到的表
type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
ALL: 全表扫描
index：根据索引的次序进行全表扫描；如果在Extra列出现“Using index”表示了使用覆盖索引，而非全表扫描
range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
ref: 根据索引返回表中匹配某单个值的所有行
eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
const, system: 直接返回单个行
possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数
ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数
Extra：额外信息
Using index：MySQL将会使用覆盖索引，以避免访问表
Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
Using temporary：MySQL对结果排序时会使用临时表
Using filesort：对结果使用一个外部索引排序