1、首先要为表建立主键,笔者使用Aqua Data Studio,直接查询一条记录出来如:select * from hire_employee limit 1,直接对这一条数据右键-保存数据-复制到剪切板即可得到create table语句和insert语句,如
CREATE TABLE hire_employee (
id VARCHAR(64) NOT NULL,
office_id VARCHAR(128) NULL,
name VARCHAR(128) NULL,
mobile VARCHAR(128) NULL,
primary key(id) --建表时加入主键索引
)
GO
如果已经有表也可以后面修改,加入主键索引如:
alter table hire_employee add primary key(id)
2、查看自己的查询语句,将where的字段、left join的字段加入单列索引或多列索引,一般left join的字段加入单列索引即可,而where的字段需要看查询的条件是否有规律再看看是否可以加入多列索引,不然就只加单列索引也可。这里的索引只是普通索引即可,当然还有UNIQUE INDEX唯一索引,主键索引PRIMARY INDEX等
索引就像字典的拼音表目录,sql server才分聚合索引(拼音表)和非聚合索引(部首表),而mysql只分单列索引和多列索引(都是拼音表类型)
a、text字段不应该创建索引,因为text字段的内容一般都很长,里面重复性也可能比较大,如果非要创建,只能取text字段内容的前n个字符作为索引键
--text字段需要加入大小限制,取前多少个字符作为索引,这里是取前255位字符作为索引键
CREATE INDEX index_check_record_reg_photo ON hire_check_record (reg_photo(255))
CREATE INDEX index_check_record_collect_photo ON hire_check_record (collect_photo(255))
图片和文件不应该以text存base64字符串或者blob存二进制的方式存进数据库,因为实践过select 1查出来速度是快的,但是慢就慢在取这个字段的值的时候非常慢,如select collect_photo就很慢了,因为这里base64寸了10几w的字符数,一下子读取出来会非常慢,这里是取的时候影响的查询的性能,即查和取的速度都要考虑。
这里建索引是a.office_id=b.id,这里a表的office_id和b表的id这两个字段,两头都要建立单独的索引
b、有时你执行计划发现这条sql查询都是走全表扫描的,可能性有:
可能一点:如果你已经建好了索引,那是mysql查询优化器认为当前查询走全表扫描更快,因为数据量少的情况下,与其先消耗IO去看索引文件,然后再消耗IO在数据文件找,这样二次IO可能导致更慢,所以mysql查询优化器决定直接全表扫描
可能二点:如果你还没建索引,数据量又比较大(起码也要上w),那就是你还没建索引优化
执行计划的type如果是ALL就是没走索引,key就是使用的索引,rows是找了多少行才找到这条数据,Extra主要看排序,数据量不一样,执行计划也会不一样
c、mysql多个单列索引可能只会用最优一个
其实这里其实涉及到了mysql优化器的优化策略!当多条件联合查询时,优化器会评估用哪个条件的索引效率最高!它会选择最佳的索引去使用,也就是说,单列索引a 、b 、c这三个索引列都能用,只不过如优化器判断只需要使用a索引这一个单例索引就能完成本次查询,故最终explain展示的key为a。当然,如果优化器判断本次查询非要全使用三个索引才能效率最高,那么explain的key就会是a、b 、c,三个索引都会使用!
d、mysql多列索引最左原则
3、执行计划使用技巧,你可以注释部分where条件、注释部分left join条件来观察、创建/删除索引来观测执行计划是否大部分走索引,尽量不要走全表扫描,如:
explain
select * from hire_check_record a
left join sys_office o on a.office_id = o.id and o.del_flag = '0'
left join hire_employee h on h.id = a.hire_employee_id
left join data_request_log d on d.id = a.report_id
--去掉where也可以观察查询计划,这里因为select * 而且没where条件直接走全表扫描了
where a.report_id = '1' and a.hire_employee_id = '1' and a.office_id = '1'
4、实战型
如SQL如下:
SELECT
a.id AS "id",
a.name AS "name",
a.id_card AS "idCard",
a.sex AS "sex",
drl.status AS "dataRequestLog.status",
drl.risk AS "dataRequestLog.risk",
a.create_by AS "createBy.id",
a.create_date AS "createDate",
a.update_by AS "updateBy.id",
a.update_date AS "updateDate",
a.remarks AS "remarks",
a.del_flag AS "delFlag",
o.id AS "office.id",
o.name AS "office.name",
o.path_name AS "office.pathName",
e.id AS "hireEmployee.id",
e.office_id AS "hireEmployee.office.id",
e.report_id AS "hireEmployee.dataRequestLog.id",
e.mobile AS "hireEmployee.mobile",
e.name AS "hireEmployee.name",
e.id_card AS "hireEmployee.idCard",
e.reg_photo AS "hireEmployee.regPhoto.id",
e.reg_finger AS "hireEmployee.regFinger",
e.collect_photo AS "hireEmployee.collectPhoto.id",
e.create_by AS "hireEmployee.createBy.id",
e.create_date AS "hireEmployee.createDate",
e.update_by AS "hireEmployee.updateBy.id",
e.update_date AS "hireEmployee.updateDate",
e.remarks AS "hireEmployee.remarks",
e.del_flag AS "hireEmployee.delFlag",
h.path AS "regPhoto.path",
hh.path AS "collectPhoto.path"
FROM
hire_check_record a
LEFT JOIN sys_office o
ON a.office_id = o.id AND
o.del_flag = '0'
LEFT JOIN hire_employee e
ON a.hire_employee_id = e.id AND
e.del_flag = '0'
LEFT JOIN sys_attach h
ON a.reg_photo = h.id AND
h.del_flag = '0'
LEFT JOIN sys_attach hh
ON a.collect_photo = hh.id AND
hh.del_flag = '0'
LEFT JOIN data_request_log drl
ON a.report_id = drl.id AND
drl.del_flag = '0'
WHERE
a.del_flag = '0'
ORDER BY
a.update_date DESC LIMIT 30
上面是例子SQL,SQL慢可能在于某个where条件没走索引慢,可以通过注释部分条件来观察,也可能是查询后抽取数据的部分慢,例如select remark,抽取每条记录的remark字段显示,假如remark字段是text文本存base64,那样抽取是非常慢的,SQL的查询包含:查询是横向的、抽取显示的字段是纵向的,纵向时也可能做一系列count、sum、case when、if null的操作,然后再groud by
4、关于联合索引的最左原则和单列索引的最优原则,我引用一篇博文解说:
多个单列索引和联合索引的区别详解 可以看我转载的博客有此文
5、建立索引的时机
a、一般来说,在where和join中出现的列需要建立索引,但也不完全如此,因为mysql只对<,<=,=,>,>=,between,in,以及只有左边不模糊右边模糊的like才会使用索引。例如:
SELECT * FROM mytable WHERE username like'admin%' 这种才使用索引
b、索引不会包含有NULL值的列,就像字典拼音索引a、b、c不应该有null座位索引项
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时就算要让字段的默认值为NULL,写入时也要最好补充''空字符串(但是实际情况不建议这样做,因为会对真实环境空字符串的数据有歧义)
c、可能的话可以使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
d、索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
e、like语句操作
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
f、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成
select * from users where adddate<‘2007-01-01’;
g、不使用not in 和 <>操作
h、对于联合索引,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到。这里有两个概念,走索引是否走得足够多,和where条件的顺序和建索引的字段的顺序有关,查询都是查询优化器先看索引能找到多少数据,然后再去根据where条件自动晒选数据范围,自动先走过滤足够多的数据的where条件,而这部分不需要人为将where过滤足够多数据的条件放在左边。人为方面只需要考虑索引顺序(a,b,d,c)因为已经知道c是>会中断索引,然后尽量保证where字段的顺序与索引顺序对应即可。
i、对查询进行优化,要尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引
j、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num is null
k、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则将引擎放弃使用索引而进行全表扫描。
l、应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or Name = 'admin'
可以这样查询:
select id from t where num = 10
union all
select id from t where Name = 'admin'
m、对于in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)
用下面的语句替换:select num from a where exists(select 1 from b where num=a.num)
n、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2 = 100
应改为:
select id from t where num = 100*2
o、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3) = ’abc’ -–name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′) = 0 -–'2005-11-30' --生成的id
应改为:
select id from t where name like 'abc%'
select id from t where createdate >= '2005-11-30' and createdate < '2005-12-1'
p、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。nvarchar适用中文和其他字符,其中N表示Unicode编码,从名字上看多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中,英文字符只需要一个字节存储就足够了,但汉字众多,需要两个字节存储,英文与汉字同时存在时容易造成混乱,Unicode字符集就是为了解决字符集这种不兼容的问题而产生的,它所有的字符都用两个字节表示,即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来,nchar、nvarchar则最多存储4000个字符,不论是英文还是汉字;而char、varchar最多能存储8000个英文,4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字,较为方便,但在存储英文时数量上有些损失。所以一般来说,如果含有中文字符,用nchar/nvarchar,如果纯英文和数字,用char/varchar
q、建立了索引的字段尽量不应该频繁地update