Mysql索引和优化

1、选择索引的数据类型

MySQL支持很多数据类型,选择合适的数据类型存储数据对性能有很大的影响。通常来说,可以遵循以下一些指导原则:

(1)越小的数据类型通常更好:越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间,处理起来更快。 (2)简单的数据类型更好:整型数据比起字符,处理开销更小,因为字符串的比较更复杂。在MySQL中,应该用内置的日期和时间数据类型,而不是用字符串来存储时间;以及用整型数据类型存储IP地址。 (3)尽量避免NULL:应该指定列为NOT NULL,除非你想存储NULL。在MySQL中,含有空值的列很难进行查询优化,因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。 1.1、选择标识符 选择合适的标识符是非常重要的。选择时不仅应该考虑存储类型,而且应该考虑MySQL是怎样进行运算和比较的。一旦选定数据类型,应该保证所有相关的表都使用相同的数据类型。 (1) 整型:通常是作为标识符的最好选择,因为可以更快的处理,而且可以设置为AUTO_INCREMENT。

(2) 字符串:尽量避免使用字符串作为标识符,它们消耗更好的空间,处理起来也较慢。而且,通常来说,字符串都是随机的,所以它们在索引中的位置也是随机的,这会导致页面分裂、随机访问磁盘,聚簇索引分裂(对于使用聚簇索引的存储引擎)。

2、索引入门 对于任何DBMS,索引都是进行优化的最主要的因素。对于少量的数据,没有合适的索引影响不是很大,但是,当随着数据量的增加,性能会急剧下降。 如果对多列进行索引(组合索引),列的顺序非常重要,MySQL仅能对索引最左边的前缀进行有效的查找。例如: 假 设存在组合索引it1c1c2(c1,c2),查询语句select * from t1 where c1=1 and c2=2能够使用该索引。查询语句select * from t1 where c1=1也能够使用该索引。但是,查询语句select * from t1 where c2=2不能够使用该索引,因为没有组合索引的引导列,即,要想使用c2列进行查找,必需出现c1等于某值。 2.1、索引的类型 索引是在存储引擎中实现的,而不是在服务器层中实现的。所以,每种存储引擎的索引都不一定完全相同,并不是所有的存储引擎都支持所有的索引类型。 2.1.1、B-Tree索引 假设有如下一个表:

CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f') not null, key(last_name, first_name, dob));

其索引包含表中每一行的last_name、first_name和dob列。其结构大致如下:

索引存储的值按索引列中的顺序排列。可以利用B-Tree索引进行全关键字、关键字范围和关键字前缀查询,当然,如果想使用索引,你必须保证按索引的最左边前缀(leftmost prefix of the index)来进行查询。 (1)匹配全值(Match the full value):对索引中的所有列都指定具体的值。例如,上图中索引可以帮助你查找出生于1960-01-01的Cuba Allen。 (2)匹配最左前缀(Match a leftmost prefix):你可以利用索引查找last name为Allen的人,仅仅使用索引中的第1列。 (3)匹配列前缀(Match a column prefix):例如,你可以利用索引查找last name以J开始的人,这仅仅使用索引中的第1列。 (4)匹配值的范围查询(Match a range of values):可以利用索引查找last name在Allen和Barrymore之间的人,仅仅使用索引中第1列。 (5)匹配部分精确而其它部分进行范围匹配(Match one part exactly and match a range on another part):可以利用索引查找last name为Allen,而first name以字母K开始的人。 (6)仅对索引进行查询(Index-only queries):如果查询的列都位于索引中,则不需要读取元组的值。 由于B-树中的节点都是顺序存储的,所以可以利用索引进行查找(找某些值),也可以对查询结果进行ORDER BY。当然,使用B-tree索引有以下一些限制: (1) 查询必须从索引的最左边的列开始。关于这点已经提了很多遍了。例如你不能利用索引查找在某一天出生的人。 (2) 不能跳过某一索引列。例如,你不能利用索引查找last name为Smith且出生于某一天的人。 (3) 存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23',则该查询只会使用索引中的前两列,因为LIKE是范围查询。

原文发布于微信公众号 - php(phpdaily)

原文发表时间:2016-08-20

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏恰同学骚年

《MSSQL2008技术内幕:T-SQL语言基础》读书笔记(上)

  数据库在物理上由数据文件和事务日志文件组成,每个数据库必须至少有一个数据文件和一个日志文件。

695
来自专栏Hadoop数据仓库

去除重复数据

有两个意义上的重复记录,一是完全重复的记录,也即所有字段均都重复,二是部分字段重复的记录。对于第一种重复,比较容易解决,只需在查询语句中使用distinct关键...

1745
来自专栏数据和云

如何理解并正确使用MySql索引

索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,本文主要介绍在MySql数据库中索引类型,以及如何创建出更加合...

3356
来自专栏北京马哥教育

MongoDB权威指南学习笔记5---索引相关的知识点

1 查看查询计划 db.user.find({"username":"xxx"}) .explain() db.doc.find({"es_y":"2014"}...

2535
来自专栏Linyb极客之路

数据库优化面试题

在 DB2 数据库中索引采用的是 B+ 树的结构,索引的叶子节点上包含索引键的值和一个指向数据地址的指针。DB2 先查询索引,然后通过索引里记录的指针,直接访问...

572
来自专栏JMCui

MySQL数据库规约.

一、建表规约 1、表达是与否概念的字段,必须使用 is_xxx 的方式命名,数据类型是 unsigned tinyint(1 表示是, 0 表示否) 。 2、表...

2975
来自专栏Jackson0714

详解SQL集合运算

3348
来自专栏恰同学骚年

走向面试之数据库基础:三、SQL进阶之变量、事务、存储过程与触发器

  SELECT 以表格的方式输出,可以同时输出多个变量;而PRINT 则是以文本的方式输出,一次只能输出一个变量的值。

732
来自专栏Java3y

Oracle总结【SQL细节、多表查询、分组查询、分页】

前言 在之前已经大概了解过Mysql数据库和学过相关的Oracle知识点,但是太久没用过Oracle了,就基本忘了…印象中就只有基本的SQL语句和相关一些概念…...

34810
来自专栏Python攻城狮

MySQL高级1.mysql高级3.内置函数4.时间与字符串的相互转换

要求:表的类型必须是innodb或bdb类型(表的默认类型就是innodb),才可以对此表使用事务

541

扫码关注云+社区