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:对结果使用一个外部索引排序

©️2020 CSDN 皮肤主题: 游动-白 设计师: 上身试试 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值