mysql怎么调索引,mysql索引怎么实现
Mysql索引
建立索引,要使用离散度(选择度)更高的字段。
成都创新互联公司服务项目包括新区网站建设、新区网站制作、新区网页制作以及新区网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,新区网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到新区省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
我们先来看一个重要的属性列的 离散度,
count(distinct(column_name)) : count(*) -- 列的全部不同值个数:所有数据行行数
数据行数相同的情况下,分子越大,列的离散度就越高。简单来说,如果列的重复值越多,离散度就越低,重复值越少,离散度就越高。
当字段值比较长的时候,建立索引会消耗很多的空间,搜索起来也会很慢。我们可以通过截取字段的前面一部分内容建立索引,这个就叫前缀索引。
创建一张商户表,因为地址字段比较长,在地址字段上建立前缀索引
create table shop(address varchar(120) not null);
alter table shop add key(address(12)); // 截取12个字符作为前缀索引是最优的吗?
问题是,截取多少呢?截取得多了,达不到节省索引存储空间的目的,截取得少了,重复内容太多,字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?
先看一下字段在全部数据中的选择度计算公式:
select count(distinct address) / count(*) from shop;
select count(distinct left(address, n)) / count(*) as subn from shop;
count(distinct left(address,n)) / count(*) 的结果是会随着 n 的变大而变大。举个例子,现在有两个address(东大街长兴小区,东大街福乐小区),那么 distinct(address,2) distinct(address,3)
==所以,截取的长度越长就会越接近字段在全部数据中的选择度
==所以,我们要权衡索引大小和查询速度。
举个例子,通过不同长度去计算,与全表的选择性对比:
SELECT COUNT(DISTINCT(address))/COUNT(*) sub, -- 字段在全部数据中的选择度
COUNT(DISTINCT(LEFT(address,5)))/COUNT(*) sub5, -- 截取前5个字符的选择度
COUNT(DISTINCT(LEFT(address,7)))/COUNT(*) sub7,
COUNT(DISTINCT(LEFT(address,9)))/COUNT(*) sub9,
COUNT(DISTINCT(LEFT(address,10)))/COUNT(*) sub10, -- 截取前10个字符的选择度
COUNT(DISTINCT(LEFT(address,11)))/COUNT(*) sub11,
COUNT(DISTINCT(LEFT(address,12)))/COUNT(*) sub12,
COUNT(DISTINCT(LEFT(address,13)))/COUNT(*) sub13,
COUNT(DISTINCT(LEFT(address,15)))/COUNT(*) sub15
FROM shop;
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| sub | sub5 | sub7 | sub9 | sub10 | sub11 | sub12 | sub13 | sub15 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0.9993 | 0.0225 | 0.4663 | 0.8618 | 0.9734 | 0.9914 | 0.9943 | 0.9943 | 0.9958 |
+--------+--------+--------+--------+--------+--------+--------+--------+--------+
可以看到在截取 11 个字段时 sub11(0.9993) 就已经很接近字段在全部数据中的选择度 sub(0.9958)了,而且长度也相较后面更短一些, 综合考虑比较合适。
ALTER TABLE shop ADD KEY (address(11));
1.索引的个数不要过多(浪费空间,更新变慢)
2.在用于 where 判断 order 排序和 join 的(on)字段上创建索引
3.区分度低的字段,例如性别,不要建索引(离散度太低,导致扫描行数过多)
4.更新频繁的值,不要作为主键或者索引(页分裂)
5.不建议用无序的值作为索引,例如身份证、UUID(在索引比较时需要转为ASCII,并且插入时可能造成页分裂)
6.若在多个字段都要创建索引的情况下,联合索引优于单值索引
7.联合索引把散列性高(区分度高)的值放在前面
MYSQL如何添加索引
1.添加PRIMARY KEY(主键索引) mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysqlALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
MySQL——关于索引的总结
首先说说索引的 优点 :最大的好处无疑就是提高查询效率。有的索引还能保证数据的唯一性,比如唯一索引。
而它的 坏处 也很明显:索引也是文件,我们在创建索引时,也会创建额外的文件,所以会占用一些硬盘空间。其次,索引也需要维护,我们在增加删除数据的时候,索引也需要去变化维护。当一个表的索引多了以后,资源消耗是很大的,所以必须结合实际业务再去确定给哪些列加索引。
再说说索引的基本结构。一说到这里肯定会脱口而出:B+树!了解B+树前先要了解二叉查找树和二叉平衡树。 二叉查找树 :左节点比父节点小,右节点比父节点大,所以二叉查找树的中序遍历就是树的各个节点从小到大的排序。 二叉平衡树 :左右子树高度差不能大于1。B+树就是结合了它们的特点,当然,不一定是二叉树。
为什么要有二叉查找树的特点?? 因为查找效率快,二分查找在这种结构下,查找效率是很快的。 那为什么要有平衡树的特点呢? 试想,如果不维护一颗树的平衡性,当插入一些数据后,树的形态有可能变得很极端,比如左子树一个数据没有,而全在右子树上,这种情况下,二分查找和遍历有什么区别呢?而就是因为这些特点需要去维护,所以就有了上面提到的缺点,当索引很多后,反而增加了系统的负担。
接着说B+树。 它的结构如下 :
可以发现,叶子节点其实是一个 双向循环链表 ,这种结构的好处就是,在范围查询的时候,我只用找到一个数据,就可以直接返回剩余的数据了。比如找小于30的,只用找到30,其余的直接通过叶子节点间的指针就可以找到。再说说其他特点: 数据只存在于叶子节点 。当叶子节点满了,如果再添加数据,就会拆分叶子节点,父节点就多了个子节点。如果父节点的位置也满了,就会扩充高度,就是拆分父节点,如25 50 75拆分成:25为左子树,75为右子树,50变成新的头节点,此时B+树的高度变成了3。它们的扩充的规律如下表,Leaf Page是叶子节点,index Page是非叶子节点。
再说说B树 ,B树相比较B+树,它所有节点都存放数据,所以在查找数据时,B树有可能没到达叶子节点就结束了。再者,B树的叶子节点间不存在指针。
最后说说Hash索引 ,相较于B+树,Hash索引最大的优点就是查找数据快。但是Hash索引最大的问题就是不支持范围查询。试想,如果查询小于30的数据,hash函数是根据数据的值找到其对应的位置,谁又知道小于30的有哪几个数据。而B+树正好相反,范围查询是它的强项。
附录: Hash到底是啥?? 哈希中文名散列,哈希只是它的音译。 为啥都说Hash快?? 首先有一块哈希表(散列表),它的数据结构是个数组,一个任意长度的数据通过hash函数都可以变成一个固定长度的数据,叫hash值。然后通过hash值确定在数组中的位置,相同数据的hash值是相同的,所以我们存储一个数据以后,只需O(1)的时间复杂度就可以找到数据。 那hash函数又是啥?? 算术运算或位运算,很多应用里都有hash函数,但实际运算过程大不一样。这是Java里String的hashCode方法:
publicint hashCode() {
}
还有一个问题,hash函数计算出来的hash值有可能存在碰撞,即两个不同的数据可能存在相同的hash值,在MySQL或其他的应用中,如Java的HashMap等,如果存在碰撞就会以当前数组位置为头节点,转变成一个链表。
说到这里也清楚了为啥Java中引用类型要同时重写hashCode和equals了。两个对象,实例就算一模一样,它们的hash值也不相等, 为啥不相等?? 默认的Object的hashCode方法会根据对象来计算hash值的,实例相同,但它们还是两个不同的对象啊,所以我们重写hashCode时,最简单的方法就是调用Object的hashCode方法,然后传入该引用类型的属性,让hashCode方法只根据这几个属性来计算,那么实例相同的话,它们的hash值也会相等。等hashCode比较完后,如果相等再比较实例内容,也就是equals,确保不是hash碰撞。
索引的分类
如果我们指定了一个主键,那么这个主键就是主键索引。如果我们没有指定,Mysql就会自动找一个非空的唯一索引当主键。如果没有这种字段,Mysql就会创建一个大小为6字节的自增主键。如果有多个非空的唯一索引,那么就让第一个定义为唯一索引的字段当主键,注意,是第一个定义,而不是建表时出现在前面的。
对于辅助索引来说,它们的B+树结构稍微有点特殊,它们的叶子节点存储的是主键,而不是整个数据。所以在大部分情况下,使用辅助索引查找数据,需要二次查找。但并不是所有情况都需要二次查找。比如查找的数据正好就是当前索引字段的值,那么直接返回就行。这里提一句,B+树的key就是对应索引字段的内容。
而辅助索引又有一些分类:唯一索引:不能出现重复的值,也算一种约束。普通索引:可以重复、可以为空,一般就是查询时用到。前缀索引:只适用于字符串类型数据,对字符串前几个字符创建索引。全文索引:作用是检测大文本数据中某个关键字,这也是搜索引擎的一种技术。
注意,聚集索引、非聚集索引和前面几个索引的分类并不是一个层面上的。上面的几个分类是从索引的作用来分析的。聚集、非聚集索引是从索引文件上区分的。主键索引就属于聚集索引,即索引和数据存放在一起,叶子节点存放的就是数据。数据表的.idb文件就是存放该表的索引和数据。
辅助索引属于非聚集索引,说到这也就明白了。索引和数据不存放在一起的就是非聚集索引。在MYISAM引擎中,数据表的.MYI文件包含了表的索引, 该表的 叶子节点存储索引和索引对应数据的指针,指向.MYD文件的数据。
索引的几点使用经验
经常被查询的字段;经常作为条件查询的字段;经常用于外键连接或普通的连表查询时进行相等比较字段;不为null的字段;如果是多条件查询,最好创建联合索引,因为联合索引只有一个索引文件。
经常被更新的字段、不经常被查询的字段、存在相同功能的字段
MySql是怎么使用的索引,在哪些情况下会使用
MySql为以下这些操作使用索引:
1、为了快速查找匹配WHERE条件的行。
2、为了从考虑的条件中消除行。如果在多个索引之间选择一个,正常情况下,MySql使用找到行的最小数量的那个索引。
3、如果表有一个multiple-column索引,任何一个索引的最左前缀可以通过使用优化器来查找行。例如,如果你有一个 three-column索引在(col1, col2, col3),你能搜索索引在(col1), (col1, col2),和 (col1, col2, col3)。
mysql索引
二叉搜索树、N叉树
页分裂:B+树的插入可能会引起数据页的分裂,删除可能会引起数据页的合并,二者都是比较重的IO消耗,所以比较好的方式是顺序插入数据,这也是我们一般使用自增主键的原因之一。
页分裂逆过程:页合并,当删除数据后,相邻的两个数据页利用率很低的时候会做数据页合并
主键索引:key:主键,value:数据页,存储每行数据
非主键索引:key:非主键索引,value:主键key,导致回表
最左匹配:优先将区分度高的列放到前面,这样可以高效索引,
最左匹配原则遇到范围查询就停止匹配,范围查询(、、between、like)为什么?因为出现范围匹配后,后面的索引字段无法保证有序,局部有序失去,顺序失去则无法提高查询效率
SELECT * FROM table WHERE a IN (1,2,3) and b 1;
如何建立索引?
还是对(a,b)建立索引,因为IN在这里可以视为等值引用,不会中止索引匹配,所以还是(a,b)!
索引组织表
索引用页存储:key【10】-point【6】,通过调整key大小,当页大小固定的情况下,通过调整key大小,使得N叉树变化;
如key 10, point 6则单个索引16字节,页大小为16k,则页面总共可以存储1024个索引,即N大小
覆盖索引: 二级索引的信息已经存在想要的列,例如主键
如果现在有一个高频请求,要根据市民的身份证号查询他的姓名,这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引,不再需要回表查整行记录,减少语句的执行时间。
索引下推优化:可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。
整理索引碎片,重建表:alter table T engine=InnoDB
首先是看key的大小,另外是数据页的大小,如果需要改变N,则需要从这两个方面做改动;
一个innoDB引擎的表,数据量非常大,根据二级索引搜索会比主键搜索快,文章阐述的原因是主键索引和数据行在一起,非常大搜索慢,我的疑惑是:通过普通索引找到主键ID后,同样要跑一边主键索引,对于使用覆盖索引的情况下,使用覆盖索引可以直接解决问题
标题名称:mysql怎么调索引,mysql索引怎么实现
文章URL:http://myzitong.com/article/dssgioi.html