目录
1、什么是索引?
2、索引结构
1.为什么不使用二叉树呢?
2.B树数据结果
3.B+树
4.Hash结构
3、索引语法
1.创建索引
2.查看索引
3.删除索引
4、SQL性能分析
1.SQL执行频次
2.慢查询日志
3.profile详情
4.EXPLAIN
5、索引规则
1.最左前缀法则
2.索引失效情况
3.SQL提示
4.索引覆盖&回表查询
5.前缀索引
6.单列索引&联合索引
6、索引设计原则
1、什么是索引?
索引是一种可以快速查询的有序的数据结构。如果查询没用上索引的话,那么就会出现把表中的每一条数据进行扫描,这样效率太差。
索引的优点就是查找速度快,并且减少进行IO请求次数,根据索引排序减少与CPU消耗。但是存储索引要空间进行存储,并且增删改的时候效率较低因为要在数据结构上修改节点。
索引一般是一级索引也叫聚集索引,非聚集索引就是以非主键作为索引生成的索引,一般主键就是聚集索引,没有主键会把第一个非空索引当做聚集索引,如果都没有会生成一个隐藏的rowId作为聚集索引。
2、索引结构
B+树索引 大部分引擎都支持的数据结构、Hash索引,R-tree索引、Full-Text索引
目前使用InnoDB树结构那么就主要介绍B+树结构
1.为什么不使用二叉树呢?
因为二叉树可能会有链化问题,比如二叉排序树,链化斜树,这样查找层树过多导致查找效率太慢,红黑树保证不会倾斜,但是数据的存储的值每个节点只能存两个,对于存储多个数据,也会导致层次太高查找性能不足。
2.B树数据结果
5阶数说明有5个子节点,有4个key 5个指针。
3.B+树
它的特点是所有数据都会出现在叶子节点,如果数据满的话键值会从中间向上分裂,并且叶子节点会有一根单向链表进行连接。在Mysql中优化了一个点就是多了一个指针形成双向链表在叶子节点,保持查找的顺序,提供区间访问性能。
目前我们用的是B+树,为什么?因为如果根据B+树的话,非叶子节点存储的就是指针而不是值,因为一个取最多能存1M大小的内容,然后一个页最多存16k的内容,也就是一个区最多能存64页,我们设想如果节点上存键值指针以及数据,那么一个区能存的指针键值就少,那么我树的高度就会变高层级变大,如果是B+树,非叶子节点只存储键值和指针,那么同样空间能存储的键值就多,树的层级变小,查找效率变高。比如这个数是5阶那么可能单数据太大,B树节点可能4个数据加指针加键值就存满了。
4.Hash结构
确定是不能范围查询、不支持排序功能,好处是块,因为存储的时候会生成一个对应的hash值,对应着该数据的位置,如果查找的时候根据键值通过hash算法就可以找到对应hash值位置的数据 Memory引擎支持该结构。
3、索引语法
1.创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name on table_name(index_col_name)
2.查看索引
SHOW INDEX FROM table_name;
3.删除索引
DROP INDEX index_name ON table_name;
4、SQL性能分析
1.SQL执行频次
show global status like 'Com______';
当前数据库的增删改查的次数的频率
2.慢查询日志
查看慢查询日志开关
show variables like 'slow_query_log';
在/etc/my.cnf中
日志存在/var/lib/mysql下如果超过2秒的查询就会存的该日志里
3.profile详情
select @@have_profiling; 查询是否支持profile操作
SETprofiling=1;开启
通过SHOW profiles;就看到查询的语句对应的耗时时间。
4.EXPLAIN
可以看到SQL执行计划,能看到查询语句,可以看到表的查询的连接顺序,还能看到使用的索引。
EXPLAIN SELECT 字段列表 FROM 表名 WHERE条件 ,意思就是任意查询前加一个EXPLAIN就可以了。
根据查询结果,id值相同的话是执行顺讯是从上到下,id值不同的执行顺序是id值越大越先执行。
5、索引规则
1.最左前缀法则
如果使用了联合索引,那么查询条件使用索引是从最左侧那个索引必须使用,并且不能跳过中间列,如果跳过了那么后面的索引失效也就是当初设置索引的顺序是ABC,可以是AB因为中间没有跳过 。与写的时候的位置无关 ,也就是当初设置索引的顺序是ABC,写的时候只有有ABC就可以无需关系是ABC BCA ACB。 但是如果使用了 > <的查询操作则范围查询右侧索引失效。
2.索引失效情况
1.如果对索引进行运算操作,则该索引失效。
2.字符串类型字段查询要加单引号 否则索引失效
3.模糊查询的时候,如果头部使用了%模糊匹配,索引失效,如果尾部使用则不会。
4.用or关键字的时候如果有一侧没有使用索引则索引失效。
5.优化器如果发现走索引比不走索引还要慢则不用索引,比如本来就需要全表扫描,使用了索引还要走全表扫描性能会差一点。还有就是查询的结果占大部分,那么mysql会认为扫描全表还是快,但是查询如果是占一小小部分则还是会使用索引的。
3.SQL提示
某些情况下,对于某个字段,它可能是联合索引,也是单列索引,可以在查询的时候指定使用的是哪种索引,加上use index(索引名)就可以了,当然mysql也有可能不接受这个建议。
也可以用ignore use index(索引名)建议忽略某个索引。
强制使用force index(索引名)
4.索引覆盖&回表查询
二级索引就是叶子节点除了id还有对应的设置的非主键索引
聚集索引就是叶子节点存储着全部数据
第一条SQL就是直接按照右侧第一个树(聚集索引)进行搜索,拿到row整行数据,因为是根据主键搜索的。
第二条SQL是根据第二个树(二级索引)进行搜索的,二级索引叶子节点存储的id和对应的索引值,这样能直接拿到需要的值而不需要再聚集索引中再次查询的就叫索引覆盖。
第三天SQL不像前两条SQL能一次查找直接得出结果,而是要出现先进行聚集索引查找,查找到的数据只有一部分,所以需要回表查询在聚集索引的树中把所有数据查找出来。
5.前缀索引
索引由于作为叶子节点中存储的数据,需要占用存储空间,如果对于数据量特别大的数据,并且索引对应列的存储值也比较大的可以用前缀索引,它是指将该数据的的前几位作为索引,这样可以减少索引的存储空间。语法是下面这样。
实际上它是一种时间换空间的方法,因为他是根据前部分的某一段索引再二级索引中查找数据,找到了再去聚集索引中查找数据,找到了还得对比是不是最初的数据因为当初传进来的只是数据的部分值。
6.单列索引&联合索引
单列索引:一个索引只包含一个列
联合索引:一个索引包含多个列
使用联合索引会减少回表查询
如果它的键(索引)包含了所需要的查询结果,那么就直接取就可以覆盖索引了,不需要回表查询了。
6、索引设计原则
对于查询频繁>100W,在where、group by、order by 后的条件建立索引,对于字符串长的可以用前缀索引,尽量使用联合索引减少单列索引,查询时可以使用索引覆盖。