1.索引
作用：
- 约束
- 加速查找
索引分类(约束)：
- 普通索引：加速查找
- 主键索引：加速查找 + 不能为空 + 不能重复
- 唯一索引：加速查找 + 不能重复
- 联合索引(多列) ：
- 联合主键索引
- 联合唯一索引
- 联合普通索引
索引分类(加速查找)：
hash索引：
索引表存储(哈希值的顺序和数据表中的数据顺序不相同):
1.所有索引列数据转换成的哈希值
2.索引列数据的内存地址
hash索引优点:
查找单个值快
查找范围值慢
btree索引：二叉树(innodb)
二叉树存储:
先将索引列数据转化成数字，每个二叉树节点存储一个数字及索引列数据的地址，
二叉树节点的数字与子节点的数字有顺序关系(左子节点数字小于父节点,有子节点数字大于父节点)
hash索引优点:
查找单个值慢
查找范围值快
建立索引：
- a.额外的文件保存特殊的数据结构 - b.查询加快，插入更新删除相对也会变慢 - c.命中索引(此处email列建立了索引) select * from userinfo where email = ‘asdf‘; # 由于未命中索引，查询速度慢 select * from userinfo where email like ‘asdf‘; 普通索引： - create index 索引名称 on 表名(列名) - drop index 索引名称 on 表名 唯一索引： - create unique index 索引名称 on 表名(列名) - drop unique index 索引名称 on 表名 组合(唯一)索引： - create unique index 索引名称 on 表名(列名,列名) - drop index 索引名称 on 表名 - create unique index ix on userinfo3(name,email); - 最左前缀匹配(组合索引(name,email)) select * from userinfo3 where name=‘alex‘; select * from userinfo3 where name=‘alex‘ and email=‘asdfg‘; # 无法使用索引,组合索引会优先匹配组合中左边的列 select * from userinfo3 where email=‘asdfg‘; 组合索引效率>索引合并 名词： 覆盖索引： - 在索引文件中直接获取数据(此处sid是索引) select sid from userinfo where sid = 5; 索引合并： - 把多个单列索引合并使用，对多列单独建立索引一起使用 select * from userinfo3 where name=‘alex‘ and email=‘asdfg‘; 部分索引(text类的使用短索引)： create index ix__ on tb1(title(15)) 表示对title列前15个字符进行索引
2.不能命中索引情况：
- like ‘%xx‘ # name是索引,但是使用like和通配符进行查找,不能命中索引 select * from tb1 where name like ‘%cn‘; - 使用函数 # email是索引,但是使用reverse函数后不会命中索引 select * from tb1 where reverse(email) = ‘alex155@163.com‘; - or(仅当or中没有建立索引的列才会不使用索引) # nid是索引 name不是索引 email是索引 select * from tb1 where nid = 1 or name = ‘alex‘; # 以下会走索引 select * from tb1 where nid = 1 or email = ‘seven@live.com‘ and name = ‘alex‘ - 类型不一致(主键除外) # nid int类型,name是varcha类型 select * from tb1 where nid = 1 or name = 888; - !=(主键除外) # name是索引,使用！=不能命中索引 select * from tb1 where name != ‘alex‘; - >(主键和整数类型除外) # name是索引,使用>不能命中索引 select * from tb1 where name >‘alex‘; - order by(主键除外) # 当根据索引排序时候，选择的映射如果不是索引，则不走索引 select name from tb1 order by email desc;
3.其他注意事项(不同的数据库情况不同)
- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合
4.时间
执行计划(explain)：让mysql预估执行操作
查询时的访问方式：
性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
慢： explain select * from userinfo3 where name = ‘alex‘ type = all(全表扫描) 快： explain select * from userinfo3 where email = ‘alex‘ type = ref(索引查找)
5.DBA工作
慢日志
- 执行时间 >10
- 未命中索引
- 日志文件路径
配置
- 在内存中设置
# 查询变量 show variables like ‘%query%‘ show variables like ‘%queries%‘ # 打开慢查询日志 set global slow_query_log = ON # 记录没有使用索引的SQL set global log_queries_not_using_indexes = ON
# 设置慢日志文件存储地址
set slow_query_log_file = D:/....
- 在配置文件中设置(启动客户达端时可以指定配置文件的地址)
注意：修改配置文件之后，需要重启服务
mysqld --defaults-file = ‘D:\my.conf‘ my.conf中添加内容：
# 打开慢查询日志 slow_query_log = ON
# 记录没有使用索引的SQL
set global log_queries_not_using_indexes = ON
# 设置慢日志文件存储地址
slow_query_log_file = D:/....
6.分页(基于数据库实现类似网页中的分页功能)
问题：同时对大量的数据进行分页会浪费很多时间
方案一：只给用户看到若干页面
缺点:这样不能在根本上解决问题
方案二：索引表
缺点:在索引中扫描比在数据表中速度快,但是实际上不能加快很多,本质上还得扫描200000条数据
select * from userinfo3 where id in(select id from userinfo3 limit 200000,10);
方案三：记录当前页
(id不一定连续，所以无法直接使用id范围进行查找)
1.页面只有上一页,下一页 # max_id 当前页面的最大id # min_id 当前页面的最小id 下一页 select * from userinfo3 where id > max_id limit 10; 上一页 select * from userinfo3 where id < max_id order by desc limit 10; 2.上一页 192 193 196 197 198 199 x下一页 # 当前处于193页,需要跳到196页 select * from userinfo3 where id in ( select id from (select * from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10; )
原文：https://www.cnblogs.com/lovejjy/p/11927014.html