昆山做网站多少钱,wordpress表格功能,管理系统网站模板,哈尔滨网站建设口碑好mysql官方对索引的定义为#xff1a;索引(index)是帮助MySQL高效获取数据的数据结构。索引的本质#xff1a;索引是数据结构。索引就是排好序的快速查找数据结构一般来说索引本身也很大不可能全部存储在内存中#xff0c;因此索引往往以索引文件的形式存储在磁盘上平时所说的… mysql官方对索引的定义为索引(index)是帮助MySQL高效获取数据的数据结构。索引的本质索引是数据结构。索引就是排好序的快速查找数据结构一般来说索引本身也很大不可能全部存储在内存中因此索引往往以索引文件的形式存储在磁盘上平时所说的索引如果没有特别说明都是指b树(多路搜索树并不一定是二叉的)结构组织的索引。其中聚集索引、次要索引、复合索引、前缀索引、唯一索引都是默认使用B树索引统称索引。除了B树这种类型的索引之外还有哈希索引等1.索引的分类主键索引(PRIMARY KEY)唯一的标识因为主键不可重复只能有一个列作为主键唯一索引(UNIQUE KEY)避免重复的列出现唯一索引可以重复多个列都可以标识为唯一索引常规索引(KEY / INDEX)默认的 可以用index 、key 关键字来设置全文索引(FullText)在特定的数据库引擎下才有myisam可以快速定位数据show engines; --查看数据库索引MySQL索引分类单值索引一个索引只包含单个列一个表可以有多个单列索引唯一索引索引列的值必须唯一但允许有null值复合索引一个索引包含多个列索引的使用1.在创建表的时候给字段增加索引2.创建完毕后增加索引SHOW INDEX FROM student;--显示表中的所有的索引信息--创建表的时候添加索引--增加一个全文索引(索引名) 列名ALTER TABLE school.student ADD FULLTEXT INDEX studentName(studentName);--create index 索引名 on 表(字段名)create index id_student_studentName on student(studentName)--explain 分析SQL执行的状况EXPLAIN SELECT * FROM student;--非全文索引EXPLAIN SELECT * FROM student where match(studentName) agains(张);2.测试索引索引在小数据量的时候用处不大但是在大数据的时候区别十分明显--添加大批量数据使用SQL函数delimiter $$CREATE FUNCTION mock_data()RETURNS INTBEGINDECLARE num INT DEFAULT 1000000;DECLARE i INT DEFAULT 0;WHILE i--插入语句...SET i i1;END WHILE END3.索引原则索引不是越多越好不要对经常变动的数据添加索引小数据量的表不需要加索引索引一般加在常用来查询的字段上索引的数据结构创建的时候默认的是hash类型的索引不是innodb默认的Btree: InnoDB 的默认数据结构资料链接http://blog.codinglabs.org/articles/theory-of-mysql-index.html3层的B树可以表示上百万的数据如果上百万的数据查找只需要三次IO操作性能提高将是巨大的如果就没有索引每个数据项都要发生一次IO,成本非常非常高。哪些情况下需要创建索引主键自动建立唯一索引频繁作为查询条件的字段应该创建索引查询中与其它表关联的字段外键关系建立索引频繁更新的字段不适合创建索引因为每次更新不单单是更新了记录还会更新索引where条件里用不到的字段不创建索引单键/组合索引的选择问题(高并发下倾向创建组合索引)查询中排序的字段排序字段若通过索引去访问将大大提高排序速度查询中统计或者分组字段哪些情况下不需要创建索引表记录太少经常增删改的表数据重复且分布平均的表字段因此应该只为最经常查询和最经常排序的数据列建立索引如果某个数据列包含许多重复的内容为它建立索引就没有太大的实际效果。4.性能分析MySQL Query OptimizerMysql Query optimizer Mysql中有专门负责优化SELECT语句的优化器模块主要功能通过计算分析系统中收集到的统计信息为客户端请求的Query提供他认为最优的执行计划(他认为最优的数据检索方式,但不见得DBA认为是最优的这部分比较耗时)当客户端向MySQL 请求一条Query命令解析器模块完成请求分类区别出是 SELECT 并转发给MySQL Query Optimizer时MySQL Query Optimizer 首先会对整条Query进行优化处理掉一些常量表达式的预算直接换算成常量值。并对 Query 中的查询条件进行简化和转换如去掉一些无用或显而易见的条件、结构调整等。然后分析 Query 中的 Hint 信息(如果有)看显示Hint信息是否可以完全确定该Query 的执行计划。如果没有 Hint 或Hint 信息还不足以完全确定执行计划则会读取所涉及对象的统计信息根据 Query 进行写相应的计算分析然后再得出最后的执行计划。Query Optimizer 是一个数据库软件非常核心的功能虽然说起来只是简单的几句话但在 MySQL 内部MySQL Query Optimizer 实际上经过了很多复杂的运算分析才得出最后的执行计划。MySQL常见瓶颈CPUCPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候IO: 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候服务器硬件的性能瓶颈topfreeiostat和vmstat来查看系统的性能状态Explain使用explain关键字可以模拟优化器执行SQL查询语句从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈作用表的读取顺序数据读取操作的操作类型那些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询用法Explain SQL语句idselect 查询的序列号包含一组数字表示查询中执行select子句或操作表的顺序id相同执行顺序由上至下id不同如果是子查询id的序号会递增id值越大优先级越高越先被执行id相同不同同时存在select_typeSIMPLE简单的select查询查询中不包含子查询或者UNIONPRIMARY查询中若包含任何复查的子部分最外层查询则被标记SUBQUERY在select或者where列表中包含了子查询DERIVED在from列表中包含的子查询被标记为derived(衍生)mysql会递归执行这些子查询把结果放在临时列表里。UNION若第二个select出现在union之后则被标记为union若union包含在from子句的子查询中外层select将被标记为DERIVEDUNION RESULT从union表中获取结果的select查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询table显示这一行的数据是关于哪张表的type访问类型排列从最好到最差依次是systemconsteq_refrefrangeindexALL,一般来说得保证查询至少达到range级别最好能达到refsystem : 表只有一行记录(等于系统表)这是const类型的特列平时不会出现这个也可以忽略不计const: 表示通过索引一次就能找到了const用于比较prinmary key 或者 unique索引因为只匹配一行数据所以很快如将主键置于where列表中mysql就能将该查询转换为一个常量。eq_ref: 唯一索引扫描对于每一个索引键表中只有一条记录与之匹配常见于主键或唯一索引扫描。ref: 非唯一性索引扫描返回匹配某个单独值的所有行本质上也是一种索引访问他返回所有匹配某个单独值的行然而他可能会找到多个符合条件的行所以他应该属于查找和扫描的混合体range: 质检所给定范围的行使用一个索引来选择行。key 列显示使用了哪个索引一般就是在where语句中出现了between、、in等的查询这种 范围扫描索引比全表扫描要好因为它只需要开始于索引的某一点而结束与另一点不用扫描全部索引。index full index scan, index 与all区别为index类型只遍历索引树。这通常比all快因为索引文件通常比数据文件小。也就是说虽然all和index都是从索引中读取的但是index是从索引中读取的而all是从硬盘中读取的。all: Full Table Scan将遍历全表以找到匹配的行。possible_keys显示可能在这张表中的索引一个或多个查询涉及到的字段上若存在索引则该索引将被列出但不一定被查询实际使用。key实际使用的索引如果为null,则没有使用索引查询中若使用了覆盖索引则该索引仅出现在key列表中key_len表示索引中使用的字节数可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下长度越短越好key_len 显示的值为索引字段的最大可能长度并非实际使用长度即key_len是根据表定义计算而得不是通过表内检索出的ref显示索引的哪一列被使用了如果可能的话是一个常数哪些列或常量被用于查找索引列上的值。rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。(每张表有多少行被优化器查询)Extra包含不适合在其他列中显示但十分重要的额外信息join语句的优化尽可能减少join语句中的NetedLoop的循环次数“永远小结果集驱动大结果集”。优先优化NestedLoop的内层循环保证join语句中被驱动表上join条件字段已经被索引当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下不要太吝惜JoinBuffer的设置5.索引的优势和劣势优势提高数据检索的效率降低数据库的IO成本通过索引对数据进行排序降低数据排序的成本降低了CPU的消耗劣势实际上索引也是一张表该表保存了主键与索引字段并指向实体表的记录所以索引也是要占用空间的虽然索引大大提高了查询速度同时却会降低更新表的速度如对表进行INSERT UPDATE 和 DELETE。因为更新表时MySQL不仅要保存数据还要保存一下索引文件每次更新添加了索引列的字段都会调整因为更新所带来的键值变化后的索引信息。索引只是提高效率的一个因素如果数据库有大数据量的表就需要花费时间建立最优秀的索引或优化查询
