Mysql索引优化
2018-04-19 本文已影响35人
沧海一粟谦
存储引擎
InnoDB
- InnoDB是是Mysql默认的事务性存储引擎
- InnoDB才有MVCC来支持高并发，并且实现了四个标准的隔离级别，默认级别是可重复读
- InnoDB存储引擎下的表是基于聚簇索引建立的，对主键的查询性能有很高的提升
MyISAM
- 提供了大量的特性，包括全文索引、压缩、空间函数等
- 不支持事物和行级锁
InnoDB与MyISAM的比较
- InnoDB：支持事物、在线热备份、行锁
- MyISAM：支持全文索引、地理空间索引
索引
索引是帮助MySQL高效获取数据的数据结构，所以索引本质上是一种数据结构
索引分类
- B-Tree索引
B-Tree索引是大多数Mysql存储引擎默认的索引类型
使用B-Tree索引后，不用再进行全表扫描，只需要对树进行搜索即可，因此查找速度会快很多
可以指定多个列作为索引列，多个索引列共同组成键
B-Tree 索引适用于全键值、键值范围和键前缀查找，其中键前缀查找只适用于最左前缀查找
除了用于查询，还可以用于排序和分组 - 哈希索引
基于哈希表的实现，优点是查询非常快
在Mysql中只有Memory存储引擎支持哈希索引 - 空间索引(R-Tree)
MyISAM存储引擎支持空间索引，可以用于地理数据存储 - 全文索引
MyISAM存储引擎支持全文索引，用于查找文本中的关键字，而不是直接比较索引中的值
索引的优点
- 加快数据查询方式，提高数据库查询性能
- 大大减少了服务器需要扫描的数据量
- 帮助服务器避免进行排序和创建临时表
- 将随机 I/O 变为顺序 I/O
索引的缺点
- 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert,update和delete。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
- 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间 研究建立最优秀的索引，或优化查询语句。
创建索引的两种方式
create index
CREATE INDEX index_name ON table_name (column_list)
alter table
ALTER TABLE `table_name` ADD INDEX index_name (column_list)
操作索引
以articles的type字段为例
-- 创建索引
CREATE INDEX idx_type ON articles (type);
-- 删除索引
drop index idx_type on articles
-- 查看索引
show index from articles
使用索引的时机
一般情况下，在where或join子句中出现的列需要添加索引。但是，因为MySQL只对<,<=,=,>,>=,between,in,以及某些时候的like才会使用索引（使用like时，以通配符%和_查询时，MySQL不会使用索引）
- 1.哪些情况下需要创建索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段，外键关系建立索引
- 单键/组合索引的选择问题(在高并发下倾向组合索引)
- 查询中排序的字段，排序字段通过索引去访问将大大提高排序速度
- 查询中统计或分组字段 - 2.哪些情况下不需要创建索引
- 表记录太少，网上有建议2000为界限，2000以下不创建
- 经常增删改的表（因为每次增删改不仅要操作数据还要操作索引）
- where条件用不到的字段
- 数据重复且分布平均的字段，索引的选择性较低，即当前字段不重复的索引值与表中当前字段的记录数比值，值越大越不建议建索引
创建索引技巧
- 给维度高的列创建索引
数据列中不重复值出现的个数，数量越高，维度越高
重复数据会降低维度
给纬度高的列创建索引，比如用户表的年龄维度就高于性别
性别这种低纬度的列不适合建索引 - 对where,on,group by,order by中出现的列使用索引
- 对较小的数据列使用索引，这样会使索引文件更小，同时内存中也可以装载更多的索引建
- 为较长的字符串使用前缀索引
- 不要过多创建索引，过多的索引会增加额外的磁盘空间，对DML操作速度影响很大，因为每增删改查一次就得重新建立索引
- 使用组合索引，可以减少文件索引大小，在使用时速度要优于多个单列索引
使用索引的注意事项
- 索引不会包含有Null值的列，所以我们在数据库设计时不要让字段的默认值为NULL
- 使用短索引
对字符串列进行索引,如果可能应该指定一个前缀长度 - 索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 - 不在索引列进行运算操作
- 建组合索引的时候，区分度最高的在最左边
索引优化
- 独立的列
在查询时，索引不能是表达式的一部分，也不能是函数的参数，否则无法使用索引
- 前缀索引
对于blob，text,varchar类型的列，必须使用前缀索引，只索引开始的部分字符
- 多列索引
在需要使用多个列作为条件查询时，使用多列索引比使用单列索引性能要好
- 索引列的顺序
在写查询语句时，将选择性强的列放在前面
join语句的优化
left join是由左边决定的，左边一定都有，所以右边是我们的关键点，建立索引要建右边的。当然如果索引在左边，可以用右连接。
尽可能减少Join语句中的NestedLoop的循环次数：“永远用小结果集驱动大的结果集”
避免索引失效
- 1.最佳左前缀法则：如果查询中使用了多个索引列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中列。
- 2.不在索引列上做任何操作（计算、函数、（自动/手动）类型转换），会导致索引失效而转向全表扫描。
- 3.where条件的列=的判断放在比较运算符>、<等的左边，放在比较运算符右边的索引会失效
比如：
select * from user where username="saboran" and age > 18 and mobile = "18862612345"
其中username、age、mobile都有索引，但是只有username和age的索引会生效，mobile索引用不到
- 4.select查询时尽量减少select * 操作，用需要的字段代替*
- 5.在使用!=或者<>的时候无法使用索引，会导致全表扫描
- 6.is null 和 is not null 也无法使用索引
- 7.like 以通配符开头，mysql索引会失效变成全表扫描
所以最好用右边通配符匹配like 'tssk%'
如果要使用两边通配符匹配，则将like条件放在最后一个
比如：
select age from users where a = 3 and b = 4 and c like "%abcd%";
这样a、b、c都有索引的话，a、b用的上，c用不上
- 8.字符串不加单引号索引会失效
- 9.少用or，用它连接时会索引失效
- 10.避免子查询，使用join
一般性建议
- 1.对于单键索引，尽量选择针对当前查询语句过滤性更好的索引作为查询条件
- 2.在选择组合索引时，当前query中过滤性最好的索引放在where条件的位置越靠前越好
- 3.尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询性能优化
Explain
用来分析SQL语句，分析结果中比较重要的字段有：
- select_type:查询类型，有简单查询、联合查询和子查询
- key:使用的索引
- rows:扫描的行数
减少返回的列
慢查询主要是因为访问了过多数据，除了访问过多行之外，也包括访问了过多列。最好不要使用select * 语句，要根据需要选择查询的列
减少查询的行
最好使用limit语句取出想要的那些行，还可以建立索引来减少条件语句的全表扫描
常用函数
数学函数
- ABS(x) // 返回x的绝对值
select abs(age) from users limit 1; -- 18
- BIN(x) // 返回x的二进制数
select bin(age) from users limit 1; -- 10010
- CEILING(x) // 返回大于x的最小整数值
SELECT CEILING (19.1) ; -- 20
- FLOOR(x) // 返回小于x的最大值
SELECT floor (19.1) ; -- 19
- RAND() // 返回0到1的随机数
SELECT rand() ; -- 0.8320153586864615 随机数
- ROUND(x,y) // 返回参数x的四舍五入的y位小数值
SELECT ROUND(100.123456,3); -- 100.123
聚合函数(常用与group by从句的select查询中)
- AVG(col) // 返回指定列的平均数
select avg(age) from users ; -- 14.0000\
- COUNT(col) // 返回指定列中非null值的个数
SELECT count(id) from users ; -- 2
- MIN(col) // 返回指定列的最小值
select min(age) from users ; -- 10
- MAX(colcol) // 返回指定列的最大值
select max(age) from users ; -- 18
- SUM(col) // 返回指定列所有值的和
select sum(age) from users ; -- 28
- GROUP_CONCAT(col) // 返回由属于一组的列值连接组合而成的结果
select GROUP_CONCAT(age) from users ; -- 18,20
字符串函数
- CONCAT(s1,s2,s3,sn) // 将s1,s2,s3,sn连接为字符串
select CONCAT(id,age,name) from users limit 1; -- 118安小下
- CONCAT_WS('|') // 将s1,s2,s3,sn连接为字符串，并使用|分隔，|可以替换为任意分隔符
SELECT CONCAT_WS('|',id,name,age) from users limit 1; -- 1|安小下|18
日期和时间函数
- CURDATE()/CURRENT_DATE() // 返回当前日期
SELECT CURRENT_DATE(); -- 2018-03-08
- CURTIME()/CURRENT_TIME() // 返回当前时间
SELECT CURRENT_TIME(); -- 08:54:15
- DATE_FORMAT(date,fmt) // 按照fmt格式，格式化date
SELECT DATE_FORMAT(CURRENT_DATE(),'%Y/%m/%d'); -- 2018/03/08
- DAYOFWEEK(date) // 返回date为一周之内的第几天,从0开始，0代表第一天
SELECT DAYOFWEEK(CURRENT_DATE()); -- 5
- DAYOFMONTH(date) // 返回date为一月之内的第几天
SELECT DAYOFMONTH(CURRENT_DATE()); -- 8
- DAYOFYEAR(date) // 返回date为一年之内的第几天
SELECT DAYOFYEAR(CURRENT_DATE()); -- 67
- DAYNAME(date) // 返回date的星期名
SELECT DAYNAME(CURRENT_DATE()); -- Thursday
- FROM_UNIXTIME(timestimps,fmt) // 时间戳转成fmt格式的字符串时间
SELECT FROM_UNIXTIME(1520500384,"%Y/%m/%d"); -- 2018/03/08
- HOUR(time) // 返回time的小时值(0-23)
SELECT HOUR('20:10'); -- 20
- MINUTE(time) // 返回time的分钟值(0-59)
SELECT HOUR('20:10'); -- 10
- MONTH(date) // 返回date的月份值(1-12)
SELECT MONTH(CURRENT_DATE()); -- 3
- MONTHNAME(date) // 返回date的月份名
SELECT MONTHNAME(CURRENT_DATE()); -- March
- NOW() // 获取当前日期和时间
SELECT NOW(); -- 2018-03-08 09:26:55
- WEEK(date) // 返回日期date为一年中的第几周
SELECT WEEK(CURDATE()); -- 9
- YEAR(date) // 返回日期date的年份
SELECT YEAR(CURDATE()); -- 2018
加密函数
- MD5(str) // 计算字符串str的MD5检验值
- PASSWORD(str) // 返回字符串str的加密版本，这个加密是不可逆的
- SHA(str) // 计算字符串str的安全散列算法检验值
格式化函数
- INET_ATON(ip) // 返回ip代表额数字
- INET_NTOA(num) // 返回数字代表的ip
Distinct去重
单独的distinct只能放在开头
-- 会报错
select id,DISTINCT(name) from test;
-- 不会报错
select DISTINCT(name) from test;
