网站建设竞争对手分析,常见的网站类型,泰州seo顾问服务,如何用pageadmin做网站SQL - 索引索引概述结构B-TreeBTreeHash思考分类语法SQL性能分析SQL执行频率慢查询日志profile详情explain执行计划索引失效情况范围查询索引列运算字符串不加引号模糊查询or连接条件数据分布影响使用规则最左前缀法则SQL提示覆盖索引前缀索引设计原则索引
概述
索引#xf…
SQL - 索引索引概述结构B-TreeBTreeHash思考分类语法SQL性能分析SQL执行频率慢查询日志profile详情explain执行计划索引失效情况范围查询索引列运算字符串不加引号模糊查询or连接条件数据分布影响使用规则最左前缀法则SQL提示覆盖索引前缀索引设计原则索引
概述
索引index是帮助MySQL 高效获取数据 的 数据结构有序。 在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。
演示 注上述二叉树索引结构只是示意图并不是真实的索引结构。 索引优缺点
优点缺点提高数据检索的效率降低数据库的IO成本索引列要占用空间通过索引列对数据进行排序降低数据排序的成本降低CPU的消耗索引大大提高了查询效率同时却也降低更新表的速度如对表进行INSERT、UPDATE、DELETE时效率降低
结构
MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种
优点缺点BTree索引最常见的索引类型大部分引擎都支持B树索引Hash索引底层数据结构是用哈希表实现的只有精确匹配索引列的查询才有效不支持范围查询R-tree空间索引空间索引是MyISAM引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-text全文索引是一种通过建立倒排索引快速匹配文档的方式。类似于LuceneSolrES
存储引擎对索引的支持
索引InnoDBMyISAMMemoryBtree索引支持支持支持Hash索引不支持不支持支持R-tree索引不支持支持不支持Full-text索引5.6版本之后支持支持不支持我们平常所说的索引如果没有特别指明都是指B树结构组织的索引。 B-Tree
B-TreeB树是一种多叉路衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。
以一颗最大度数max-degree为55阶的b-tree为例那这个B树每个节点最多存储4个key5个指针 度数指一个节点的子节点个数。 BTree
BTree是B-Tree的变种
以一颗最大度数为4的btree为例其结构示意图
绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。
BTree相对于B-Tree的区别
所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。
上述结构是标准的BTree数据结构。
MySQL中优化之后的BTree
MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。 Hash
哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。
如果两个或多个键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。
特点
Hash索引只能用于对等比较in不支持范围查询between…无法利用索引完成排序操作查询效率高通常只需要一次检索就可以效率通常要高于Btree索引
存储引擎支持 在MySQL中支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。
思考
为什么InnoDB存储引擎选择使用Btree索引结构
对于二叉树层级更少搜索效率高对于B-tree无论是叶子节点还是非叶子节点都会保存数据这样导致一页中存储的键值减少指针跟着减少要同样保存大量数据只能增加树的高度导致性能降低相对Hash索引Btree支持范围匹配及排序操作
分类
分类含义特点关键字主键索引针对于表中主键创建的索引默认自动创建只能有一个PRIMARY唯一索引避免同一个表中某数据列中的值重复可以有多个UNIQUE常规索引快速定位特定数据可以有多个全文索引全文索引查找的是文本中的关键词而不是比较索引中的值可以有多个FULLTEXT
在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种
分类含义特点聚集索引Clustered Index将数据存储与索引放到了一块索引结构的叶子节点保存了行数据必须有而且只有一个二级索引Secondary Index将数据与索引分开存储索引结构的叶子节点关联的是对应的主键可以存在多个
聚集索引选取规则
如果存在主键主键索引就是聚集索引。如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引和二级索引的具体结构示例如下
聚集索引的叶子节点下挂的是这一行的数据。二级索引的叶子节点下挂的是该字段值对应的主键值。 这种先到二级索引中查找数据找到主键值然后再到聚集索引中根据主键值获取数据的方式称为回表查询。 思考 以下两条SQL语句那个执行效率高注id为主键name字段创建的有索引 A. select * from user where id 10; B. select * from user where name ‘Arm’; 答 A语句的执行性能要高于B语句。 思考 InnoDB主键索引的Btree高度为多高 假设 一行数据大小为1k一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间主键即使为bigint占用字节数为8。 若高度为2 n * 8 (n 1) * 6 16 * 1024算出n约为1170 1171 * 16 18736 也就是说如果树的高度为2则可以存储 18000 多条记录。 若高度为3 1171 * 1171 * 16 21939856 也就是说如果树的高度为3则可以存储 2200w 左右的记录。 语法
创建常规索引 CREATE INDEX 索引名 ON 表名 (字段1, ..., 字段n);
创建唯一索引 CREATE UNIQUE INDEX 索引名 ON 表名 (字段1, ..., 字段n);
创建全文索引 CREATE FULLTEXT INDEX 索引名 ON 表名 (字段1, ..., 字段n); 一个索引可以关联多个字段。 若一个索引只关联一个字段称为单列索引。 若关联多个字段则称为联合索引或组合索引。 查看索引 SHOW INDEX FROM 表名;
删除索引 DROP INDEX 索引名 ON 表名;
示例
CREATE TABLE tb_user(id INT PRIMARY KEY AUTO_INCREMENT COMMENT 主键,name VARCHAR(50) NOT NULL COMMENT 用户名,phone VARCHAR(11) NOT NULL COMMENT 手机号,email VARCHAR(100) COMMENT 邮箱,profession VARCHAR(11) COMMENT 专业,age TINYINT UNSIGNED COMMENT 年龄,gender CHAR(1) COMMENT 性别, 1: 男, 2: 女,user_status CHAR(1) COMMENT 状态,createtime DATETIME COMMENT 创建时间
) COMMENT 系统用户表;INSERT INTO tb_user (name, phone, email, profession, age, gender, user_status, createtime)
VALUES (吕布, 17799990000, lvbu666163.com, 软件工程, 23, 1, 6, 2001-02-02 00:00:00), (曹操, 17799990001, caocao666qq.com, 通讯工程, 33, 1, 0, 2001-03-05 00:00:00), (赵云, 17799990002, 17799990139.com, 英语, 34, 1, 2, 2002-03-02 00:00:00), (孙悟空, 17799990003, 17799990sina.com, 工程造价, 54, 1, 0, 2001-07-02 00:00:00), (花木兰, 17799990004, 19980729sina.com, 软件工程, 23, 2, 1, 2001-04-22 00:00:00), (大乔, 17799990005, daqiao666sina.com, 舞蹈, 22, 2, 0, 2001-02-07 00:00:00), (露娜, 17799990006, luna_lovesina.com, 应用数学, 24, 2, 0, 2001-02-08 00:00:00), (程咬金, 17799990007, chengyaojin163.com, 化工, 38, 1, 5, 2001-05-23 00:00:00), (项羽, 17799990008, xiaoyu666qq.com, 金属材料, 43, 1, 0, 2001-09-18 00:00:00), (白起, 17799990009, baiqi666sina.com, 机械工程及其自动化, 27, 1, 2, 2001-08-16 00:00:00), (韩信, 17799990010, hanxin520163.com, 无机非金属材料工程, 27, 1, 0, 2001-06-12 00:00:00), (荆轲, 17799990011, jingke123163.com, 会计, 29, 1, 0, 2001-05-11 00:00:00), (兰陵王, 17799990012, lanlinwang666126.com, 工程造价, 44, 1, 1, 2001-04-09 00:00:00), (狂铁, 17799990013, kuangtiesina.com, 应用数学, 43, 1, 2, 2001-04-10 00:00:00), (貂蝉, 17799990014, 84958948374qq.com, 软件工程, 40, 2, 3, 2001-02-12 00:00:00), (妲己, 17799990015, 2783238293qq.com, 软件工程, 31, 2, 0, 2001-01-30 00:00:00), (芈月, 17799990016, xiaomin2001sina.com, 工业经济, 35, 2, 0, 2000-05-03 00:00:00), (嬴政, 17799990017, 8839434342qq.com, 化工, 38, 1, 1, 2001-08-08 00:00:00), (狄仁杰, 17799990018, jujiamlm8166163.com, 国际贸易, 30, 1, 0, 2007-03-12 00:00:00), (安琪拉, 17799990019, jdodm1h126.com, 城市规划, 51, 2, 0, 2001-08-15 00:00:00), (典韦, 17799990020, ycaunanjian163.com, 城市规划, 52, 1, 2, 2000-04-12 00:00:00), (廉颇, 17799990021, lianpo321126.com, 土木工程, 19, 1, 3, 2002-07-18 00:00:00), (后羿, 17799990022, altycj2000139.com, 城市园林, 20, 1, 0, 2002-03-10 00:00:00), (姜子牙, 17799990023, 37483844qq.com, 工程造价, 29, 1, 4, 2003-05-26 00:00:00);# name字段为姓名字段该字段的值可能会重复为该字段创建索引。
CREATE INDEX idx_user_name ON tb_user(name);# phone手机号字段的值是非空且唯一的为该字段创建唯一索引。
CREATE UNIQUE INDEX idx_user_phone ON tb_user(phone);# 为profession、age、status创建联合索引。
CREATE INDEX idx_user_pro_age_sta ON tb_user(profession, age, user_status);# 为email建立合适的索引来提升查询效率。
CREATE INDEX idx_email ON tb_user(email);# 完成上述的需求之后查看tb_user表的所有的索引数据。
SHOW INDEX FROM tb_user;SQL性能分析
SQL执行频率
查看数据库当前会话的访问频次 SHOW SESSION STATUS LIKE Com_______;
查看数据库全局数据的访问频次 SHOW GLOBAL STATUS LIKE Com_______;
Com_delete删除次数Com_insert插入次数Com_select查询次数Com_update更新次数 通过上述指令可以查看当前数据库是以查询为主还是以增删改为主从而为数据库优化提供参考依据。 如果是以增删改为主可以考虑不对其进行索引的优化。如果是以查询为主那么就要考虑对数据库的索引进行优化。 慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启。
查看慢查询日志是否开启 SHOW VARIABLES LIKE slow_query_log;
如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2配置完毕之后通过以下指令Linux系统重新启动MySQL服务器进行测试 systemctl restart mysqld
慢日志文件保存/var/lib/mysql/localhost-slow.log
profile详情
show profiles 能够帮助我们在做SQL优化时了解时间都耗费到哪里去了。
查看当前MySQL是否支持profile操作 SELECT have_profiling;
查看profile是否开启 SELECT profiling;
在当前会话开启profile SET SESSION profiling 1;
全局开启profile SET GLOBAL profiling 1;
查看每一条SQL的耗时基本情况 SHOW profiles;
查看指定query_id的SQL语句各个阶段的耗时情况 SHOW profile FOR query 指定query_id;
查看指定query_id的SQL语句CPU的使用情况 SHOW profile cpu FOR query 指定query_id;
explain执行计划
EXPLAIN 或者 DESC 命令 获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法 EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件; 直接在select语句之前加上关键字EXPLAIN或者DESC
以语法示例中创建的tb_user表为例
EXPLAIN SELECT * FROM tb_user WHERE id 1;Explain执行计划中各个字段的含义
字段含义idselect查询的序列号表示查询中执行select子句或者操作表的顺序id相同执行顺序从上到下id不同值越大越先执行select_type表示SELECT的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等table操作的表partitions匹配的分区type表示连接类型性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、allpossible_keys显示可能应用在这张表上的索引一个或多个key实际使用的索引如果为NULL则没有使用索引key_len表示索引中使用的字节数该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下长度越短越好ref显示索引的哪一列被使用了如果可能的话是一个常数rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的filtered表示返回结果的行数占需读取行数的百分比filtered的值越大越好extra包含不适合在其它列中显示但十分重要的额外信息
索引失效情况
索引失效指没有用到索引走的是全表扫描
范围查询
联合索引中出现范围查询或范围查询右侧的列索引失效。
以语法示例中创建的tb_user表以及其联合索引为例
explain select * from tb_user where profession 软件工程 and age 30 and user_status 0;当范围查询使用 或 时走联合索引了但是索引的长度为49就说明范围查询右边的user_status字段是没有走索引的。
explain select * from tb_user where profession 软件工程 and age 30 and user_status 0;当范围查询使用 或 时走联合索引了索引的长度为54说明所有的字段都是走索引的。 所以在业务允许的情况下尽可能的使用类似于 或 这类的范围查询而避免使用 或 。 索引列运算
不要在索引列上进行运算操作索引将失效。
以语法示例中创建的tb_user表以及其phone字段单列索引为例
explain select * from tb_user where substring(phone,10,2) 15;根据phone字段进行函数运算操作之后索引失效。
字符串不加引号
字符串类型字段使用时不加引号索引将失效。
模糊查询
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。
以语法示例中创建的tb_user表以及其联合索引为例
# 索引生效
explain select * from tb_user where profession like 软件%;# 索引失效
explain select * from tb_user where profession like %工程;or连接条件
当or连接的条件左右两侧字段都有索引时索引才会生效。
数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引。 查询时MySQL会评估走索引快还是全表扫描快如果全表扫描更快则放弃索引走全表扫描。
使用规则
最左前缀法则
如果索引了多列联合索引要遵守最左前缀法则。
最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效后面的字段索引失效。
以语法示例中创建的tb_user表以及其联合索引为例
explain select * from tb_user where profession 软件工程 and age 31 and user_status 0;explain select * from tb_user where profession 软件工程 and age 31;explain select * from tb_user where profession 软件工程;以上的这三组测试中发现只要联合索引最左边的字段profession存在索引就会生效只不过索引的长度不同。而且由以上三组测试也可以推测出profession字段索引长度为47、age字段索引长度为2、user_status字段索引长度为5。
explain select * from tb_user where age 31 and user_status 0;通过上面这次测试可以看到索引并未生效原因是不满足最左前缀法则联合索引最左边的列profession不存在。
explain select * from tb_user where profession 软件工程 and user_status 0;上述的测试存在profession字段最左边的列是存在的索引满足最左前缀法则的基本条件。但是查询时跳过了age列所以后面的列索引是不会使用的也就是索引部分生效所以索引的长度就是47。 最左前缀法则中指的最左边的列是指在查询时联合索引的最左边的字段即是第一个字段必须存在与我们编写SQL时条件编写的先后顺序无关。 SQL提示
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。 比如一个字段既有单列索引又有联合索引则可以指定使用哪个索引。
建议MySQL使用指定索引完成此次查询 SELECT 字段列表 FROM 表名 USE INDEX(索引名) WHERE 条件; 仅仅是建议mysql内部还会再次进行评估 忽略指定的索引 SELECT 字段列表 FROM 表名 IGNORE INDEX(索引名) WHERE 条件;
强制使用索引 SELECT 字段列表 FROM 表名 FORCE INDEX(索引名) WHERE 条件;
覆盖索引
尽量使用覆盖索引减少 select *。 覆盖索引是指查询使用了索引并且需要返回的列在该索引中已经全部能够找到。 这样可以避免回表查询见分类中的例子 Using where; Using Index查找使用了索引但是需要的数据都在索引列中能找到不需要回表查询数据。 Using index condition查找使用了索引但是需要回表查询数据 前缀索引
当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO影响查询效率。 此时可以只将字符串的一部分前缀建立索引可以节约索引空间从而提高索引效率。
用前n个字符创建索引 CREATE INDEX 索引名 ON 表名 (字段(n));
前缀长度可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值。索引选择性越高则查询效率越高。 唯一索引的选择性是1是最好的索引选择性性能也是最好的。
以语法示例中创建的tb_user表为例
# 查询email是否有重复
SELECT COUNT(DISTINCT email) / COUNT(*) FROM tb_user;# 查询email前五个字符的选择性
SELECT COUNT(DISTINCT SUBSTRING(email, 1, 5)) / COUNT(*) FROM tb_user;设计原则
针对于数据量较大且查询比较频繁的表建立索引。针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
