mysql 索引学习日志

最新推荐文章于 2025-06-11 15:17:16 发布

原创最新推荐文章于 2025-06-11 15:17:16 发布 · 593 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #索引

mysql数据库专栏收录该内容

7 篇文章

订阅专栏

1.索引存储什么数据？
数据库索引是创建在表的某列上的，并且存储了这一列的所有值。

但是，需要理解的重点是数据库索引并不存储这个表中其他列（字段）的值。
举例来说，如果我们在Name列创建索引，那么列Age和Address上的值并不会存储在这个索引当中。
如果我们确实把其他所有字段也存储在个这个索引中，那就成了拷贝一整张表做为索引-这样会占用太大的空间而且会十分低效。

建立索引字段本身会存在一张索引表，这个索引表中存放着数据地址的引用！！而表中的索引字段也存在唯一主键索引建立的一张表，这张表中存在着所有的数据。（B+树）。也就是说数据库本身会有两张表：主键数据表，索引表

2.我们怎么利用索引的字段进行高效率的查询其他数据？
索引存储了指向表中某一行的指针
如果我们在索引里找到某一条记录作为索引的列的值，如何才能找到这一条记录的其它值呢？这是很简单 - 数据库索引同时存储了指向表中的相应行的指针。

指针是指一块内存区域，该内存区域记录的是对硬盘上记录的相应行的数据的引用。因此，索引中除了存储列的值，还存储着一个指向在行数据的索引。
也就是说，索引中的Name这列的某个值（或者节点）可以描述为 (“Jesus”, 0x82829)， 0x82829 就是包含 “Jesus”那行数据在硬盘上的地址。如果没有这个引用，你就只能访问到一个单独的值（“Jesus”），而这样没有意义，因为你不能获取这一行记录的employee的其他值-例如地址（address）和年龄（age）。

话不多说，放代码（其实是知识点没学完，不想整理，狗头保命）
下列都是索引的基本操作，像那些查看所有删除所有的骚操作一般不要搞

--从选中表中查询所有索引（包括自动生成的主键索引）
show INDEX from book

-- 像选中表中的选定字段添加索引,可以创建复合索引
--下面两个随你选，你开心就好
alter table book ADD INDEX Y(card,name)
create index index_name on book(card)
--还有你如果想加字符符号一定是“ ` ”而不是“ ' ”别问我为什么
--也别问我为什么知道（狗头保命）

--删除选中表中的索引
--下面两个随你选，你开心就好
drop index index_name on book
alter table book drop index index_name

-- 左连接建右表条件索引，右连接建左表索引
select * from class left join book on class.card = book.card

1.不建立索引
在这里插入图片描述
2.建立右索引

3.建立左索引条件
在这里插入图片描述

索引效果一般看
一丶type 访问类型
system》const>eq_ref>ref>range>index>all
保证查询知多少到range级别，最好在ref
system 系统参数不常见
const 常量 where id=1
eq_ref 唯一索引值
！ref 非唯一性索引扫描，查询和扫描的混合体
！range where中出现了between < >
！index 只遍历了索引树
all 全表扫描 --这个时候尝试优化
二丶extra
包含不合适在其他列中显示但十分重要的额外信息
using filesort 出现外部排序需要优化sql
using temporary 使用临时表需要立刻优化sql
using index 使用了覆盖索引，效率很高
using where
如果出现using where ，表明索引用来读取数据而非执行查询动作
using join buffer
impossble where
select table optimized away
distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值

索引口诀：
	全职匹配我最爱，最左前缀要遵守。
	带头大哥不能死，中间兄弟不能断。
	索引列上少计算，范围之后全失效。
	like百分写最右，覆盖索引不写星。
	不等空值还有or，索引失效要少用。
小总结
		sql内部优化
		1.order by 会导致索引失败，全局排序，除非参数是一个定值在where中的条件
		2.group by 分组之前必排序，会有临时表
		3.mysql底层会对符合的数据进行类型转换成符合条件的查询，但条件是索引失效
		4.对于连续的数值，能用 between就不要用 in
		5.尽量使用覆盖索引（只访问索引的查询（索引列和查询一致））减少select *
		6.

查询优化
1.explain分析
2.开启慢查询日志，设置阈值，将数据拿出来
3.explain +慢sql分析
4.show profile 查询服务器中sql执行细节和生命周期情况
5.sql服务器参数调优
优化原则：
1）小表驱动大表,即小的数据驱动大的数据集
1.当B表的数据集必须小于A表的数据集时，用in优于exsits

select * from A where id in (select id from B)

2.当B的数据集大于A表的数据集时，用exsits优于in

select * from A where exsits (select * from B where B.id=A.id)

2）order by 子句，尽量使用index方式排序

尽可能在索引列上完成排序操作，遵守索引建的最佳左前缀
**如果不在索引列上**
filesort有两种算法
	双路排序：首先根据相应的条件取出对应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序。排序后再把查询字段依照指针取出，共执行两次磁盘io
	单路排序：是一次性取出满足条件的所有字段 ，然后在sort buffer中进行排序。执行一次磁盘Io
	**MySQL主要通过比较我们所设定的系统参数 max_length_for_sort_data的大小和Query 语句所取出的字段类型大小总和来判定需要使用哪一种排序算法**

优化策略：
增大sort_buffer_size参数
3）where 高于having能写在where限定的条件就不要去having限定
----------------------------------------------------------------
慢查询
long_query_time 默认大于10秒的执行时间
show variable like ‘%show_query_log%’
查询所有慢查询参数（开启，地址）
set global show_query_log=1
开启（数据库关闭重置）
show variable lile ‘long_query_time%’
查看参数
set global long_query_time=3
修改参数（重新连接才能展示修改值）
show GLOBAL STATUS LIKE ‘%Slow_queries%’
查询慢查询的条数

mysqldumpslow 日志慢查询


```sql
(批量数据脚本

delimiter $$
create function range(n int) return varchar(255)
begin
return 
end $$
delimiter $$ 定义结束符)
函数有返回值，存储过程没有返回值

show profile
默认情况下处于关闭状态，并保存最近的12次运行结果
set profiling = on 开启查看日志
show profiles 展示操作日志
show profile cpu,block io for queyr 1
展示
全局查询日志
永远不要在生产环境开启这个功能
set global general_log=1;
开启
set global log_output=‘TABLE’
记录到 general_log表
select * from mysql.general_log