文章参考自：https://blog.csdn.net/Jack__Frost/article/details/72571540
一、索引
1、索引是什么？它是一种特殊的文件，包含着对数据表里所有记录的引用指针。通俗的说，数据库索引就像是一本书的目录部分，这能加快数据库的查询速度。在没有索引的情况下，数据库会遍历全部数据然后选择符合条件的；然而有了相应的索引之后，数据库会直接在索引中查找符合条件的选项。
索引分为聚簇索引和非聚簇索引两种，聚簇索引是按照数据存放的物理位置为顺序的，聚簇索引的顺序就是数据的物理存储顺序，索引的叶节点就是数据节点。而非聚簇索引是索引顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块，所以一个表最多只能有一个聚簇索引。
2、索引的优点
（1）通过创建唯一性索引，能够保证数据库表中的每一行数据的唯一性；
（2）可以加快数据的检索速度，这也是创建索引最重要的原因；
（3）可以加速表与表之间的连接，实现数据的参考完整性；
（4）在使用分组（group by）和排序（order by）进行检索时，减少查询中分组和排序的时间；
（5）通过使用索引，可以在查询过程中，使用优化隐藏器，提高系统性能。
3、索引的缺点
同样，索引应用不当也具有一定的缺点。（一）创建索引和维护索引需要耗费时间，这种时间是随着数据量的增加而增加；（二）索引需要占用物理空间，除了数据表占数据空间外，每一个索引还要占一定的物理空间，如果是建立聚簇索引，则需要更大的空间；（三）当对表中的数据进行增加、删除和修改的时候，索引也需要动态的维护，这样就降低了数据的维护速度。
4、什么情况下需要索引
数据在磁盘上是以块的形式存储的， 为了确保对磁盘操作的原子性，访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表是类似的，它们都包含了一个数据段和指针，指针指向下一个节点（数据块）的内存地址，而且它们都不需要连续存储（即逻辑上相邻的数据块在物理存储上可以相隔很远）。
索引应用方式如下：
一、在创建表的时候声明索引（此处key和index作用相同）
KEY `idx_passport_name` (`passport_name`),
KEY `idx_account_phone` (`account_phone`)
二、使用ALTER TABLE <tab_name> ADD INDEX方式增加索引
ALTER TABLE <tab_name> ADD (unique,primary key,fulltext,index)[索引名](字段名)
ALTER TABLE `my_user` ADD INDEX `inx_usr_id` (`usrid`); //普通索引，索引值可出现多次。
三、使用CREATE INDEX命令来创建索引
CREATE INDEX `index_usr_id` ON `my_user` (`usrid`);
四、删除索引
DROP INDEX `idx_usr_id` ON `my_user`;
五、查看索引
SHOW INDEX FROM `my_user`;
SHOW KEYS FROM `my_user`;在mysql中，key和index都可以用来设置索引，二者都是数据库的物理结构，但是又有一些不同。请参考 ： https://www.cnblogs.com/654321cc/p/7762943.html ——> key与index的区别
key是数据库的物理结构，有两层含义：一是约束作用（偏重于约束和规范数据库的结构完整性）；二是索引作用（与index作用相同，辅助查询用的）。它又包含primary key、unique key、foreign key等。
index也是数据库的物理结构，只是起到来辅助查询作用，不会对字段进行行为约束。
主键索引和唯一键索引类似，区别为，默认情况下：
1、PK创建的是聚簇索引，而UK创建的是非聚簇索引；
2、PK不可以为空，而UK可以为空。
-- use test;
/*
CREATE TABLE IF NOT EXISTS `my_user`(
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`usrid` varchar(128) NOT NULL DEFAULT '0' COMMENT '用户身份id',
`usrname` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '用户名称',
PRIMARY KEY(`id`),
UNIQUE KEY `usr_id`(`usrid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='身份表';
*/
-- desc `my_user`
-- alter table `my_user` MODIFY `usrid` CHAR(32);
-- select replace(uuid(),'-','') as b
-- insert into `my_user`(usrid, usrname) select replace(uuid(),'-',''),'zhangsan01'
select * from `my_user`
查询创建表的索引结构：
数据库主键采用自增还是UUID请参考：https://blog.csdn.net/u013298499/article/details/77919701
|
| 二、char和varchar的差别？
|
二、组合索引
MySQL可以在多个列上创建索引，执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格的（获取结果集记录数量最少）的索引。例如，我们在为WHERE查询的列加上索引的话，查询会非常迅速。使用场景如下：
1、遵循最左前缀匹配原则；2、匹配第一列范围查询（可以用like a%，但是不能用like %b）；3、精确匹配某一列和范围匹配另外一列；4、匹配第一列，全字段匹配。
高性能索引策略：（参考菜鸟教程：索引优化全攻略：http://www.runoob.com/w3cnote/mysql-index.html）
- 索引列不能是函数的一部分或者是表达式的一部分；
- 索引列要有良好的选择性（不重复性），【select count（DISTINCT city）/COUNT(*) FROM city_demo ： 计算区分度，表示字段不重复的比例。】
- 前缀索引会使索引变小，但是这会导致order by和group by失效；
前缀索引
- 二级索引的叶子节点保存的指向行主键值的指针；
- 插入性能依赖聚簇索引书序，所以聚簇索引通常设置为id自增；
覆盖索引
- 数据查询字段是索引字段时，可以使用覆盖索引，减少二次查询操作；
- 覆盖索引explain出来的extra是 Using index；
-
字段详情说明
索引排序
- 使用索引扫描来排序，explain出来的是 index；
- 索引排序一样需要满足最左前缀原则 ；
举例说明：
数据表test_order有索引（a,b,c），字段a,b,c,d,e,f。'
可以用到索引举例：
select e,f from test_order where a='2014-1-1' order by b,c； //符合最左前缀原则
select e,f from test_order where a='2014-1-1' order by b; //符合最左前缀原则
select e,f from test_order where a>'2014-1-1' order by a,b;//符合最左前缀原则
无法用到索引举例：
select e,f from test_order where a='2014-1-1' order by b,e; //b,e索引无序，无法使用索引
select e,f from test_order where a='2014-1-1' order by c;//不符合最左前缀原则
select e,f from test_order where a>'2014-1-1' order by b,c;//第一列为范围条件，无法使用其余列作为索引
select e,f from test_order where a='2014-1-1' and b in (1,2) order by c;//b列有多个等于条件，对于排序来说这是范围查询。
三、应用索引注意时应注意
- 索引遵循最左前缀匹配原则。mysql会从左至右匹配，遇到范围查询（例如，>，<，between、like）停止匹配，比如对于建立（a，b，c，d）的顺序索引，如果查询语句为a = 1 and b = 2 and c > 4 and d = 4，则d列索引没有应用；假设d列索引在c列之前，则可以应用。【 select e,f from test_order where a>'2014-1-1' order by b,c;//第一列为范围条件，无法使用其余列作为索引 】
- 尽量使用区分度较高的列作为索引，区分度的公式为【 count(distinct column) / count(*) 】，用来表示字段不重复的比例，比例越大则代表扫描到的记录数越少，唯一键的区分度是1，而一些状态、性别等字段在大数据量下则无限趋近于0，所以类似性别字段不宜作为区分度的索引列。
- 尽量扩展索引列而不是新建索引列（这会增加系统负担）。比如，表中已经有了a列的索引，现在需要加入b列的索引，则需要修改原来的索引即可。【 ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`) 】
- 对于经常存取的列避免建立索引。
- 中所周知，索引一般建立在JOIN、WHERE、ORDER BY排序的字段上，避免在含有大量重复值的字段建立索引。
- 避免在where之后对字段进行null的判断【select * from table where num is null 】，这会导致引擎放弃使用索引而进行全表扫描。在设计数据库表的时候，应尽量使用NOT NULL来填充数据库。注意：不要以为NULL不会占用存储空间，比如CHAR（100），在字段建立时，空间就是固定了，都会占用100个字符空间。而varchar（100）这样的变长字段，null是不占用空间的。
- 所有的“非”类型的判断不会走索引【 not , not in, not like, <>, != ,!>,!< 】。
- 避免在where中使用or来连接条件。万一一个字段有索引，而另外一个没有索引，则将导致放弃索引进行全表扫描。可以使用语句代替：
-
/*不走索引* select id from tab where num = 10 or Nme = "zhangsan01" /*上述语句替换为*/ select id from tab where num = 10 union all select id from tab where Nme = "zhangsan01" /*默认情况下，UNION操作会选取不同的值，而UNION ALL操作可以允许重复值出现*/ - like %abc%的模糊查询：
-
select * from `myuser` where usrname like 'abc%' //走索引 ===> type: range select * from `myuser` where usrname like '%abc%' //不走索引 ===> type: all select * from `myuser` where usrname like '%abc' //不走索引 ===> type: all -
避免在where字段中进行表达式操作（如，num/2、substring(name,1,3)、num=@num），这会导致放弃索引直接进行全表扫描。尽量不要在“=”左边进行函数、算数运算符或其他表达式操作。
-
索引可以提高select查询效率，但是降低了insert及update的效率，因为insert或者update可能重建索引，所以建立索引需要慎重。
-
MySQL查询只使用一个索引，如果where字段已经使用了索引的话，则order by中不会使用索引。
四、聚集索引和非聚集索引
1、聚集索引与非聚集索引
聚集索引（聚簇索引），表中记录的物理顺序与键值的索引顺序相同。一个表只能有一个聚集索引。
聚集索引和非聚集索引的根本区别是表中记录的物理顺序和索引的排列顺序是否一致。
聚集索引的表中记录的物理顺序与索引的排列顺序一致。优点是查询速度快，因为一旦具有第一个索引值的记录被找到，具有连续索引值的记录也一定物理的紧随其后。缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。
建议使用聚集索引的场合为：
A。某列包含了小数目的不同值。 B。排序和范围查找。
非聚集索引的记录的物理顺序和索引的顺序不一致。
2、其他方面的区别：
（1）聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引叶子层并不与实际的数据页相重叠，而采用叶子层包含一个指向表中的记录在数据页中的指针的方式。聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点。
（2）非聚集索引添加记录时，不会引起数据顺序的重组。
建议使用非聚簇索引的场合：
A。此列包含了大数目的不同值。 B。频繁更新的列。
3、数据库索引原理的实现
B+Tree（B+树）是数据库系统实现索引的首选数据结构。在Mysql中索引属于存储引擎级别的概念。主要使用MYISAM和InnoDB两个存储引擎的索引实现。
（1）MyISAM索引实现：
MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引文件仅仅保存数据记录的地址。也叫“非聚簇索引”。不支持外键，只支持表级锁，不提供事务支持。支持全文索引。允许没有主键的表存在。
（2）InnoDB索引实现：（MySQL默认存储引擎）
InnoDB的数据文件本身就是索引。在MyISAM中，索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。也叫“聚集索引”。支持外键，支持行级锁和表级锁，默认是行级锁，提供事务支持。如果没有主键，就会自动生成一个6字节的主键。
五、B树和B+树原理（后补）
插入一条php数组基本应用
——>bool array_walk ( array &$array , callable $callback [, mixed $userdata = NULL ] )
/***************************
* 测试array_walk回调函数
**************************/
$fruits = array("l"=>"lemon", "a"=>"apple", "b"=>"banana", "o"=>"orange");
function test_alter(&$item1, $key, $prefix)
{
$item1 = "$prefix: $item1";
}
function test_print($item2, $key)
{
echo "$key. $item2<br/>\n";
}
echo "Before ......:\n";
array_walk($fruits,'test_print');
array_walk($fruits,'test_alter', 'fruit');
echo "and After ......:\n";
array_walk($fruits, 'test_print');
print_r($fruits); |  |
418
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
