如何写网站建设方案书,门户网站构建,网站主页图片怎么换,张雪峰谈建筑学前景索引是什么MySQL官方对索引的定义为#xff1a;索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质#xff1a;索引是数据结构#xff0c;索引的目的是提高查询效率#xff0c;可以类比英语新华字典#xff0c;根据目录定位词语如果没有目录呢#xff0c…索引是什么MySQL官方对索引的定义为索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质索引是数据结构索引的目的是提高查询效率可以类比英语新华字典根据目录定位词语如果没有目录呢就需要从A到Z去遍历的查找一遍一个一个找和直接根据目录定位到数据差的就是天壤之别索引底层数据结构数据库除了存储数据本身之外还维护着一个满足特定查找算法的数据结构这些结构以某种方式指向数据这样就可以基于这些数据结构实现高效查找算法。这种结构就是索引,MySQL中索引是B树实现的,每个索引都对应一棵B树索引的优势提高数据检索效率降低数据库IO成本通过索引列对数据进行排序降低数据排序成本降低了CPU消耗索引的劣势一个索引都为对应一棵B树树中每一个节点都是一个数据页一个页默认会占用16KB的存储空间所以一个索引也是会占用磁盘空间的。(空间的代价)索引是对数据的排序当对表中的数据进行增、删、改操作时都要维护修改内容涉及到的B树索引。所以在进行这些操作时需要额外的时间进行一些记录移动页面分裂、页面回收等操作来维护索引(时间上的代价)索引语法以test_user表为例,建表sql如下CREATE TABLEtest_user (idint(11) NOT NULL AUTO_INCREMENT COMMENT 主键id,user_id varchar(36) NOT NULL COMMENT 用户id,user_name varchar(30) NOT NULL COMMENT 用户名称,phonevarchar(20) NOT NULL COMMENT 手机号码,lan_idint(9) NOT NULL COMMENT 本地网,region_idint(9) NOT NULL COMMENT 区域,create_timedatetime NOT NULL COMMENT 创建时间,PRIMARY KEY(id),KEY idx_user_id (user_id)) ENGINEInnoDB AUTO_INCREMENT1010001 DEFAULT CHARSETutf8mb4;1.查看索引SHOW INDEX FROM table_name\GSHOW INDEX FROM test_user;2.删除索引DROP INDEX [indexName] ON mytable;DROP INDEX idx_user_id ON test_user;3.创建索引 alter tableName add [unique] index [indexName] on (columnName (length) )ALTER TABLE test_user ADD INDEX idx_user_id(user_id);哪些情况需要建索引主键自动建立唯一索引频繁作为查询的条件的字段应该创建索引查询中与其他表关联的字段外键关系建立索引频繁更新的字段不适合创建索引因为每次更新不单单是更新了记录还会更新索引加重IO负担Where条件里用不到的字段不创建索引单间/组合索引的选择问题(在高并发下倾向创建组合索引)查询中排序的字段若通过索引去访问将大大提高排序的速度查询中统计或者分组字段哪些不适合建索引表记录太少经常增删改的表数据重复且分布平均的表字段如果某个数据列包含许多重复的内容为它建立索引就没有太大的实际效果。索引实战我们在test_user表中有100万数据优化一使用全部索引1.不加索引,关闭缓存查一条数据SELECT SQL_NO_CACHE * FROM test_user WHERE phone15190427892 AND lan_id317 AND region_id922.加一条复合索引ALTER TABLE test_user ADD INDEX idx_phone_lan_region(phone,lan_id,region_id);再查一次,看结果可以看到,加了索引以后,查询效率提高了很多这里我们建立的复合索引包含的3个字段查询的时候全部用到了而且where中的条件严格按照索引顺序这样查询效率是最高的我们使用EXPLAIN关键字看一下优化二最左前缀法则我们把上面那个例子的第一个插件条件删掉EXPLAIN SELECT SQL_NO_CACHE * FROM test_user WHERE lan_id317 AND region_id92;我们使用EXPLAIN关键字看一下因此,我们得出结论:如果建立的是复合索引索引的顺序要按照建立时的顺序即从左到右如a-b-c(和 B树的数据结构有关)无效索引举例a-ca 有效c 无效b-cb、c 都无效cc 无效优化三不要对索引做以下处理计算如、-、*、/、!、、is null、is not null、or函数如sum()、round()等等手动/自动类型转换如id 1本来是数字给写成字符串了我们以!为例演示,我们使用EXPLAIN关键字看一下优化四索引不要放在范围查询右边比如复合索引a-b-c当 where a and b10 and c这时候只能用到 a 和 bc 用不到索引因为在范围之后索引都失效(和 B树结构有关)如下EXPLAIN SELECT SQL_NO_CACHE * FROM test_user WHERE phone15190427892 AND lan_id317 AND region_id92;我们使用EXPLAIN关键字看一下我们把最后一个条件删除,再看一下优化五减少 select * 的使用select *会查询很多不必要的字段,造成不必要的网络传输和IO消耗优化六like 模糊搜索失效情况like %张三%like %张三解决方案使用复合索引即 like 字段是 select 的查询字段如select name from table where name like %张三%使用 like 张三%优化七order by 优化当查询语句中使用 order by 进行排序时如果没有使用索引进行排序会出现 filesort 文件内排序这种情况在数据量大或者并发高的时候会有性能问题需要优化。filesort 出现的情况举例order by 字段不是索引字段order by 字段是索引字段但是 select 中没有使用覆盖索引如select * from staffs order by age asc;order by 中同时存在 ASC 升序排序和 DESC 降序排序如select a, b from staffs order by a desc, b asc;order by 多个字段排序时不是按照索引顺序进行 order by即不是按照最左前缀法则如select a, b from staffs order by b asc, a asc;如下情况没有索引filesort 文件内排序会在内存开辟一块空间,然后把数据复制了一份放到这个空间内,再进行排序,这个是很影响性能的我们可以为这个字段建一个索引ALTER TABLE test_user ADD INDEX idx_create_time(create_time);索引层面解决方法使用主键索引排序按照最左前缀法则并且使用覆盖索引排序多个字段排序时保持排序方向一致在 SQL 语句中强制指定使用某索引force index(索引名字)不在数据库中排序在代码层面排序优化八group by其原理也是先排序后分组其优化方式可参考order by。where高于having,能写在where限定的条件就不要去having限定了。
