在MySQL中,索引是在存储引擎层而不是在服务器层实现的,所以并没有统一的索引标准。不同存储引擎索引的工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引,其底层的实现也是不同的。
B-TREE 索引
- 存储引擎以不同的方式使用B-Tree索引,性能各有不同,各有优劣。例如,MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。再如MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。
B+Tree每个叶子节点都包含指向下一个叶子结点的指针,从而方便叶子节点的范围遍历。(每个节点为一个页)
索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,取而代之的是从索引树开始搜索。父节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找。最终存储引擎要么找到对应的值,要么该记录不存在。 - B-Tree对索引列是顺序存储的,所以很适合查找范围数据。
索引根据创建索引时列的顺序,对多个值进行排序。首先,对索引的第一个字段进行排序。如果第一个字段相同,那么就对索引的第二个字段进行排序,以此类推。 - 索引适用于全键值(全值匹配)、键值范围或键前缀查找。其中键前缀查找遵守最左前缀原则。
索引的类型
- 普通索引:这是最基本的索引,它没有任何限制。
直接创建索引:CREATE INDEX 索引名称 ON 表名 (字段名 ( 长度 ));
修改表结构的方式添加索引:ALTER TABLE 表名 ADD INDEX 索引名称 ON (字段名 ( 长度 ))
创建表时创建索引:
CREATE TABLE 表名 (
…
INDEX 索引名称 (字段名 ( 长度 ))
);
删除索引:DROP INDEX 索引名称 ON 表名 - 唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须唯一。
直接创建索引:CREATE UNIQUE INDEX 索引名称 ON 表名 (字段名 ( 长度 ));
修改表结构的方式添加索引:ALTER TABLE 表名 ADD UNIQUE 索引名称 ON (字段名 ( 长度 ))
创建表时创建索引:
CREATE TABLE 表名 (
…
UNIQUE 索引名称 (字段名 ( 长度 ))
);
删除索引:DROP UNIQUE 索引名称 ON 表名 - 主键索引:索引列的值必须唯一,不允许有空值。
修改表结构的方式添加索引:ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
创建表时创建索引:列级约束,表级约束。 - 组合索引:多个字段做索引。遵循最左前缀原则。
修改表结构的方式添加索引:ALTER TABLE 表名 ADD INDEX 索引名称 ON (字段名1,字段名2…)
最左前缀原则
创建组合索引:ALTER TABLE table ADD INDEX idx_ABC ON (A,B,C)
下面三种情况可以用到索引:
SELECT * FROM table WHERE A=1 AND B=2 AND C=3;
SELECT * FROM table WHERE A=1 AND B=2;
SELECT * FROM table WHERE A=1;
下面的情况不会使用到索引(看上面B+Tree图):
SELECT * FROM table WHERE B=2 AND C=3;
SELECT * FROM table WHERE B=2;
SELECT * FROM table WHERE C=3;
下面的情况会使用部分索引:
SELECT * FROM table WHERE A=1 AND C=3;
索引的优缺点
索引的优点:
- 通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。
- 可以大大加快数据的检索速度,这也是创建索引的最主要的原因。
- 可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。
- 通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
索引的缺点:
- 创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。
- 索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。
- 当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。
建立索引的时机
什么样的字段适合创建索引:
- 在经常需要搜索的列上,可以加快搜索的速度;
- 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;
- 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;
- 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
- 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。
什么样的字段不适合创建索引:
- 对于那些在查询中很少使用或者参考的列不应该创建索引。
- 对于那些只有很少数据值的列也不应该增加索引,例如性别列。尽量选择区分度高的列作为索引,区分度的公式是COUNT(DISTINCT column)/COUNT(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。
- 当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。
索引的注意事项
- 索引不会包含有NULL值的列:只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
- 范围之后的字段用不了索引:如建立索引(a,b),SELECT * FROM table WHERE a>1 AND b=1;,b就用不到索引。
- 使用短索引:对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
- 索引列排序:MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
- like语句操作:一般情况下不鼓励使用like操作,如果非使用不可。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
- 不要在列上进行运算:例如:SELECT actor_id FROM actor WHERE actor_id + 1 = 5;或SELECT … WHERE DATEDIFF(DATE1,DATE2) > 10;,将在每个行上进行运算,这将导致索引失效而进行全表扫描。
- 强制类型转换会全表扫描。
- 建立联合索引的时候,要把查询频率较高的列放在最左边。
- 尽量用具体的字段:SELECT A,B FROM table;如果建立了索引(a,b),可以使用覆盖索引。
- =和in可以乱序:建立索引(a,b,c),可以任意顺序,比如A=1 AND C=2 AND B=3或B=3 AND C=2 AND A=1;都可以使用索引。MySQL的查询优化器会优化成索引可以识别的形式,最好按照索引建立的顺序。
- 使用 !=,<>,IS NULL,IS NOT NULL 无法使用索引。
- 连接查询时,索引建立在从表。
- 永远用小结果集驱动大的结果集。
聚簇索引
- 如果没有定义主键,Innodb会选择一个唯一的非空索引代替。如果没有这样的索引,Innodb会隐式定义一个主键来作为聚簇索引。
- 聚簇索引将索引和数据保存在同一个B-Tree中。InnoDB的数据文件本身就是索引文件。MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。
- 二级索引(非聚簇索引)需要两次索引查找,原因是二级索引叶子节点保存的不是指向数据行的物理位置的指针,而是行的主键值。通过二级索引查找行,存储引擎需要找到二级索引的叶子节点获取对应的主键值,然后根据这个值去聚簇索引中查找对应的行。
叶节点的 data 域存放的是数据记录的地址。
- 如果正在使用的Innodb表没有什么数据可以聚集,那么可以定义一个代理键作为主键,最简单的方法是使用AUTO_INCREMENT自增列。
如果新插入的主键值不一定比之前的大,那么会导致页分裂和碎片。因为写入是乱序的,Innodb不得不频繁的做页分裂操作,以便为新的行分配空间。由于频繁的页分裂,页会变得稀疏并被不规则的填充,最终数据会产生碎片。
覆盖索引:SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据。Extra信息已经有’Using Index’,表示已经使用了覆盖索引。
EXPLAIN分析
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。EXPLAIN不考虑各种Cache。EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。
语法:EXPLAIN + SQL语句
字段 | 描述 |
---|---|
id | 查询的序号,表示查询中执行select子句或操作表的顺序 1. id相同,执行顺序由上至下 2. id不同,id值越大,优先级越高,越先执行 |
select_type | SIMPLE:简单的SELECT查询,不使用UNION或子查询 PRIMARY:查询中包含任何复杂的子部分,最外层查询则被标记为PRIMARY SUBQUERY:在SELECT或WHERE列表中包含了子查询 DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MySQL会递归执行这些子查询,把结果放在临时表中 UNION:若第二个SELECT出现在UNION之后,则被标记为UNION。若UNION包含在FROM子句的子查询中,外层SELECT将被标记为DERIVED UNION RESULT:UNION的结果 |
table | 当前行所引用的表 |
partitions | 该列显示的为分区表命中的分区情况,非分区表该字段为空(NULL)。 |
type | 访问类型,结果值从最好到最坏依次是:system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>All 1. system:表中仅有一行(等于系统表)这是const类型的一个特例 2. const:表示通过索引一次就找到,const用于比较primary key或者unique索引。因为只匹配一行数据,所以很快。如果将主键置于where列表中,MySQL能将该查询转换为一个常量 3. eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4. ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,可能会找多个符合条件的行,属于查找和扫描的混合体 5. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。一般就是where语句中出现了>,<,between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描 6. index:index与all区别为index类型只遍历索引树。通常比all快,因为索引文件比数据文件小很多 7. all:遍历全表以找到匹配的行 一般保证查询至少达到range级别,最好能达到ref |
possible_keys | 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出。但不一定被查询实际使用。 |
key | 实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在key列表中,不会出现在possible_keys列表中。 |
key_len | 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。 |
ref | 哪些列或常量被用于查找索引列上的值。如果是使用的常数等值查询,这里会显示const。如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段。如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能显示为func。 |
rows | 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数 |
filtered | 表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的百分比 |
Extra | 包含不适合在其他列中显示但十分重要的额外信息。 Using filesort:说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。 Using temporary:使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。 Using index:表示相应的select操作中使用了覆盖索引,避免访问了表的数据行。如果同时出现using where,表明索引被用来执行索引键值的查找。如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。 Using where:表明使用了where过滤。 Using join buffer:使用了连接缓存。 Impossible where:where子句的值总是false,不能用来获取任何元组。即查询语句中where的条件不可能被满足,恒为False。 select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。 distinct:优化distinct操作,在找到第一匹配的元组后即停止找相同值的动作。 |
慢查询日志
MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阈值的语句。具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中。
long_query_time的默认值是10,意思是运行10秒以上的语句。
由它来查看哪些SQL超出了我们的最大忍耐时间值,比如一条sql执行超过5秒钟,我们就算慢SQL,希望能收集超过5秒的sql,结合之前的explain进行全面分析。
默认情况下,MySQL数据库没有开启慢查询日志,需要我们手动来设置这个参数。
当然,如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
查看慢查询日志是否开启:SHOW VARIABLES LIKE ‘%slow_query_log%’;
开启慢查询日志:SET GLOBAL slow_query_log=1;
查看阈值:SHOW VARIABLES LIKE ‘%long_query_time%’;
设置阈值:SET GLOBAL long_query_time=3;=
查询当前系统中有多少条慢查询记录:SHOW GLOBAL STATUS LIKE ‘%Slow_queries%’;
日志分析工具
MySQL提供了日志分析工具mysqldumpslow。
- 查看mysqldumpslow的帮助信息
mysqldumpslow --help
s:是表示按照何种方式排序
c:访问次数
I:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式,大小写不敏感
得到返回记录集最多的10条SQL:
mysqldumpslow -s r -t 10 /opt/mysql/localhost-slow.log
得到访问次数最多的10条SQL:
mysqldumpslow -s c -t 10 /opt/mysql/localhost-slow.log
得到按时间顺序的前10条包含左连接的查询语句:
mysqldumpslow -s t -t 10 -g “left join” /opt/mysql/localhost-slow.log | more
Show Profile
Show Profile是MySQL提供的可以用来分析当前会话中语句执行的资源消耗情况,可以用于SQL的调优的测量。默认情况下,参数处于关闭状态,并保存最近15次的运行结果。
查看Profile是否开启:SHOW VARIABLES LIKE ‘%profiling%’;
开启Profile:set profiling = on;
查看结果:SHOW profiles;
诊断SQL:SHOW profile cpu,block io FOR SQL序号
- 参数列表
- all:显示所有的开销信息
- block io:显示块io开销
- context switches:上下文切换开销
- cpu:显示cpu的开销信息
- ipc:显示发送和接收的开销信息
- memory:显示内存的开销信息
- page faults:显示页面错误开销信息
- source:显示Source_function,Source_file,Source_line开销信息
- swaps:显示交换次数的开销信息
注意:
converting HEAP to MyISAM:查询结果太大,内存都不够用了往磁盘上搬了
Creating tmp table:创建临时表。拷贝数据到临时表,用完再删除。
Copying to tmp table on disk:把内存中临时表复制到磁盘,危险!!
locked
参考博客
数据库索引
细说mysql索引
数据库索引原理
mysql-覆盖索引
MySQL高级学习笔记
MySQL索引实现原理分析
《高性能MySQL》(第3版)
mysql数据库索引类型和原理
【MySQL优化】——看懂explain
数据库索引原理,及MySQL索引类型