索引
优缺点
优点:
提高数据检索效率,降低IO成本
降低排序成本,降低CPU的消耗
缺点:
索引列占用空间
提高了查询效率,同时降低了更新表的速度
索引结构
MySQL索引是在存储引擎层实现的
B+Tree索引 最常见
Hash索引 不支持范围查询
Hash索引存储引擎支持
在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
为什么InnoDB存储引擎选择使用B+tree索引结构
相对于二叉树,层级更少
相对于B-tree,数据只保存在叶子节点当中
相对Hash索引,B+tree支持范围查询及排序操作
聚集索引选取规则
如果存在主键,主键就是聚集索引
如果没有主键,第一个UNIQUE索引作为主键
如果没有主键,或合适的唯一索引,则InnoDB会自动生成一个row_id作为隐藏的聚集索引
高度为3的B+tree结构可以存储大约2千万条数据
索引语法
- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
- 查看索引
SHOW INDEX FROM table_name;
- 删除索引
DROP INDEX index_name ON table_name;
查看SQL执行频次
show global status like 'Com_______';
慢查询日志
需要在MySQL配置文件(/etc/my.cnf)中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
long_query_time=2
慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
profile
select @@profiling;
set profiling = 1;
#查看每一条SQL的耗时基本情况
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的SQL语句CPU使用情况
show profile cpu for query query_id;
explain
在任意一句select语句前面加上explain查看当前语句的执行计划
各个字段的含义
Id id相同自上而下,id不同从大到小
select_type 不是很重要
type 连接类型,性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
possible_key 可能用到的索引
key 实际用到的索引
key_len 使用到索引中使用的字节数
rows 执行查询的行数,在innodb中是一个预估值
filtered 值越大越好
最左前缀法则
查询从最左边的索引开始,如果不包含最左边的索引则索引失效,如果跳过索引中间的某个字段,则部分索引失效。
范围查询(>,<)右边的列的索引将会失效,如果业务允许尽量使用(>=,<=)
在索引列上进行运算,索引将失效
模糊匹配 '%'在前面不走索引
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,则不用使用索引
如果MySQL判断不走索引比走索引快,则不会走索引
SQL提示
use index(index_name) 建议使用索引
ignore index(index_name) 忽略索引
force index(index_name) 强制使用索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少select *
using index condition 需要回表
using where;using index 不需要回表
当字段类型为字符串时,有时候需要存储很长的字符串,此时可以只将字符串的一部分前缀建立索引,这样可以大大节约索引空间,从而提高索引效率
create index idx_xxxx on table_name(column(n))
前缀长度根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,唯一索引的选择性是1,这是最好的索引选择性
索引设计原则
数据量较大,查询频繁
针对常作为查询条件、排序、分组操作的字段建立索引
选择区分度高的列建立索引,尽量建立唯一索引
字段的长度较长的,建立前缀索引
尽量使用联合索引
要控制索引数量
如果索引不能存储NULL,建表时使用NOT NULL约束