Mysql学习深入理解MySQL索引与优化
《Mysql学习深入理解MySQL索引与优化》要点: mysql索引深入探讨 索引对查询的速度有着至关重要的影响,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储100条记录. 第二章、mysql索引与索引优化MYSQL学习 1、选择索引的数据类型MYSQL学习 MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响.通常来说,可以遵循以下一些指导原则:MYSQL学习 (1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快.(2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂.在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址. (3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL.在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂.你应该用0、一个特殊的值或者一个空串代替空值. 1.1、选择标识符 选择合适的标识符是非常重要的.选择时不仅应该考虑存储类型,而且应该考虑MySQL是怎样进行运算和比较的.一旦选定数据类型,应该保证所有相关的表都使用相同的数据类型. (1)?? ?整型:通常是作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO_INCREMENT. (2)?? ?字符串:尽量避免使用字符串作为标识符,它们消耗更好的空间,处理起来也较慢.而且,通常来说,字符串都是随机的,所以它们在索引中的位置也是随机的,这会导致页面分裂、随机拜访磁盘,聚簇索引分裂(对于使用聚簇索引的存储引擎).MYSQL学习 2、索引入门 CREATE TABLE People (MYSQL学习 ?? last_name?varchar(50)??? not null,MYSQL学习 ?? first_name varchar(50)??? not null,MYSQL学习 ?? dob??????? date?????????? not null,MYSQL学习 ?? gender???? enum('m','f') not null,MYSQL学习 ?? key(last_name,first_name,dob)MYSQL学习 );MYSQL学习 ?其索引包括表中每一行的last_name、first_name和dob列.其结构大致如下:MYSQL学习 ?MYSQL学习 ?索引存储的值按索引列中的顺序排列.可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询,当然,如果想使用索引,你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询. 2.1.2、Hash索引 假设索引使用hash函数f( ),如下:MYSQL学习 f('Arjen') = 2323MYSQL学习 f('Baron') = 7437MYSQL学习 f('Peter') = 8784MYSQL学习 f('Vadim') = 2458MYSQL学习 此时,索引的结构大概如下:MYSQL学习 ?MYSQL学习?Slots是有序的,但是记录不是有序的.当你执行 3、高性能的索引策略 ?MYSQL学习 ?注:叶子页面包含完整的元组,而内节点页面仅包含索引的列(索引的列为整型).一些DBMS允许用户指定聚簇索引,但是MySQL的存储引擎到目前为止都不支持.InnoDB对主键建立聚簇索引.如果你不指定主键,InnoDB会用一个具有唯一且非空值的索引来代替.如果不存在这样的索引,InnoDB会定义一个暗藏的主键,然后对其建立聚簇索引.一般来说,DBMS都会以聚簇索引的形式来存储实际的数据,它是其它二级索引的基础.MYSQL学习 3.1.1、InnoDB和MyISAM的数据布局的比拟 ?MYSQL学习 CREATE TABLE layout_test (MYSQL学习 ?? col1 int NOT NULL,MYSQL学习 ?? col2 int NOT NULL,MYSQL学习 ?? PRIMARY KEY(col1),MYSQL学习 ?? KEY(col2)MYSQL学习 );MYSQL学习 ?假设主键的值位于1---10,000之间,且按随机顺序插入,然后用OPTIMIZE TABLE进行优化.col2随机赋予1---100之间的值,所以会存在许多重复的值. ?MYSQL学习 ?注:左边为行号(row number),从0开始.因为元组的大小固定,所以MyISAM可以很容易的从表的开始位置找到某一字节的位置. ?MYSQL学习 ?注:MyISAM不支持聚簇索引,索引中每一个叶子节点仅仅包含行号(row number),且叶子节点依照col1的顺序存储. ?实际上,在MyISAM中,primary key和其它索引没有什么区别.Primary key仅仅只是一个叫做PRIMARY的唯一,非空的索引罢了. ?注:聚簇索引中的每个叶子节点包括primary key的值,事务ID和回滚指针(rollback pointer)——用于事务和MVCC,和余下的列(如col2). ?聚簇索引和非聚簇索引表的对比:MYSQL学习 ? MYSQL学习
?3.1.2、按primary key的顺序插入行(InnoDB)MYSQL学习 如果你用InnoDB,而且不需要特殊的聚簇索引,一个好的做法就是使用代理主键(surrogate key)——独立于你的应用中的数据.最简单的做法就是使用一个AUTO_INCREMENT的列,这会保证记录依照顺序插入,而且能提高使用primary key进行连接的查询的性能.应该尽量避免随机的聚簇主键,例如,字符串主键就是一个不好的选择,它使得插入操作变得随机.MYSQL学习 ??3.2、覆盖索引(Covering Indexes) mysql> EXPLAIN SELECT store_id,film_id FROM sakila.inventoryGMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: inventoryMYSQL学习 ???????? type: indexMYSQL学习 possible_keys: NULLMYSQL学习 ????????? key: idx_store_id_film_idMYSQL学习 ????? key_len: 3MYSQL学习 ????????? ref: NULLMYSQL学习 ???????? rows: 5007MYSQL学习 ??????? Extra: Using indexMYSQL学习 1 row in set (0.17 sec)MYSQL学习 在大多数引擎中,只有当查询语句所拜访的列是索引的一部分时,索引才会覆盖.但是,InnoDB不限于此,InnoDB的二级索引在叶子节点中存储了primary key的值.因此,sakila.actor表使用InnoDB,而且对于是last_name上有索引,索引能覆盖那些拜访actor_id的查询,如: mysql> EXPLAIN SELECT actor_id,last_nameMYSQL学习 ??? -> FROM sakila.actor WHERE last_name = 'HOPPER'GMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: actorMYSQL学习 ???????? type: refMYSQL学习 possible_keys: idx_actor_last_nameMYSQL学习 ????????? key: idx_actor_last_nameMYSQL学习 ????? key_len: 137MYSQL学习 ????????? ref: constMYSQL学习 ???????? rows: 2MYSQL学习 ??????? Extra: Using where; Using indexMYSQL学习 ?3.3、利用索引进行排序 create table actor(MYSQL学习 actor_id?int unsigned NOT NULL AUTO_INCREMENT,MYSQL学习 name????? varchar(16) NOT NULL DEFAULT '',MYSQL学习 password??????? varchar(16) NOT NULL DEFAULT '',MYSQL学习 PRIMARY KEY(actor_id),MYSQL学习 ?KEY???? (name)MYSQL学习 ) ENGINE=InnoDBMYSQL学习 insert into actor(name,password) values('cat01','1234567');MYSQL学习 insert into actor(name,password) values('cat02',password) values('ddddd',password) values('aaaaa','1234567');MYSQL学习 ?MYSQL学习 mysql> explain select actor_id from actor order by actor_id GMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: actorMYSQL学习 ???????? type: indexMYSQL学习 possible_keys: NULLMYSQL学习 ????????? key: PRIMARYMYSQL学习 ????? key_len: 4MYSQL学习 ????????? ref: NULLMYSQL学习 ???????? rows: 4MYSQL学习 ??????? Extra: Using indexMYSQL学习 1 row in set (0.00 sec)MYSQL学习 ?MYSQL学习 mysql> explain select actor_id from actor order by password GMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: actorMYSQL学习 ???????? type: ALLMYSQL学习 possible_keys: NULLMYSQL学习 ????????? key: NULLMYSQL学习 ????? key_len: NULLMYSQL学习 ????????? ref: NULLMYSQL学习 ???????? rows: 4MYSQL学习 ??????? Extra: Using filesortMYSQL学习 1 row in set (0.00 sec)MYSQL学习 ?MYSQL学习 mysql> explain select actor_id from actor order by name GMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: actorMYSQL学习 ???????? type: indexMYSQL学习 possible_keys: NULLMYSQL学习 ????????? key: nameMYSQL学习 ????? key_len: 18MYSQL学习 ????????? ref: NULLMYSQL学习 ???????? rows: 4MYSQL学习 ??????? Extra: Using indexMYSQL学习 1 row in set (0.00 sec)MYSQL学习 ?当MySQL不能使用索引进行排序时,就会利用自己的排序算法(快速排序算法)在内存(sort buffer)中对数据进行排序,如果内存装载不下,它会将磁盘上的数据进行分块,再对各个数据块进行排序,然后将各个块合并成有序的结果集(实际上就是外排序).对于filesort,MySQL有两种排序算法. ?3.4、索引与加锁 create table actor(MYSQL学习 actor_id?int unsigned NOT NULL AUTO_INCREMENT,'1234567');MYSQL学习 insert into actor(name,'1234567');MYSQL学习 insert into actor(name,'1234567');MYSQL学习 SET AUTOCOMMIT=0;MYSQL学习 BEGIN;MYSQL学习 SELECT actor_id FROM actor WHERE actor_id < 4MYSQL学习 AND actor_id <> 1 FOR UPDATE;MYSQL学习 ?该查询仅仅返回2---3的数据,实际已经对1---3的数据加上排它锁了.InnoDB锁住元组1是因为MySQL的查询计划仅使用索引进行范围查询(而没有进行过滤操作,WHERE中第二个条件已经无法使用索引了):MYSQL学习 ?MYSQL学习 mysql> EXPLAIN SELECT actor_id FROM test.actorMYSQL学习 ??? -> WHERE actor_id < 4 AND actor_id <> 1 FOR UPDATE GMYSQL学习 *************************** 1. row ***************************MYSQL学习 ?????????? id: 1MYSQL学习 ?select_type: SIMPLEMYSQL学习 ??????? table: actorMYSQL学习 ???????? type: indexMYSQL学习 possible_keys: PRIMARYMYSQL学习 ????????? key: PRIMARYMYSQL学习 ????? key_len: 4MYSQL学习 ????????? ref: NULLMYSQL学习 ???????? rows: 4MYSQL学习 ??????? Extra: Using where; Using indexMYSQL学习 1 row in set (0.00 sec)MYSQL学习 ?MYSQL学习 mysql>MYSQL学习 ?表明存储引擎从索引的起始处开始,获取所有的行,直到actor_id<4为假,服务器无法告诉InnoDB去掉元组1. SET AUTOCOMMIT=0;MYSQL学习 BEGIN;MYSQL学习 SELECT actor_id FROM actor WHERE actor_id = 1 FOR UPDATE;MYSQL学习 ??该查询会被挂起,直到第一个连接的事务提交释放锁时,才会执行(这种行为对于基于语句的复制(statement-based replication)是需要的). 编程之家PHP培训学院每天发布《Mysql学习深入理解MySQL索引与优化》等实战技能,PHP、MYSQL、LINUX、APP、JS,CSS全面培养人才。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |