学习笔记0804

索引

优缺点
优点：
提高数据检索效率，降低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约束