mysql优化

1.explain分析
explian引用
索引基数
show indexes from table_name;
主键索引具有最好的基数

测试时

不走缓存
SELECT SQL_NO_CACHE id from test_null;

2.更好的索引类型
索引列尽可能的为not null ,避免在可空的列索引上进行二次扫描
要尽量避免 NULL ,关于索引列为Null的是否走索引,见测试 索引列的值为null查询时走索引的情况
3.使用unique index
与常规索引比不需要进行索引范围扫描
4.使用primary key
 主键是uniquekey的一种特殊形式 。在innodb中,一个uniquekey是一个聚集索引(即对磁盘上数据排列的索引),当数据按照主键的次序进行检索时会极大改进性能
5.索引太多是有害的
  例如,如果possible_keys 列表中有超过3个的索引,mysql优化器有太多信息而无法确定最好使用哪个索引,也就意味着有些是低效或者无用的索引
6.索引列使用最小可能的数据类型
  比如在一个varchar(100)甚至更大的列上建立索引,一种改进方法是建立一个额外的列,并在包含较大的varchar(100)列的md5值的额外varchar(32)列上创建索引。
  更好的方法是使用bigint来存储md5值的数字表示,数字索引更加高效
  CONV(N,from_base,to_base)

mysql> select conv('a',16,10);+-----------------+| conv('a',16,10) |+-----------------+| 10              |+-----------------+mysql> select conv(substr(md5('abc'),1,16),16,10);+-------------------------------------+| conv(substr(md5('abc'),1,16),16,10) |+-------------------------------------+| 10376663631224000432                |+-------------------------------------+

7.建立索引时
 如果使用到多个列,定义多列索引
 哪列的唯一性更高(基数大 show indexes from table_name),哪列优先放在多列索引的前面
 覆盖索引是理性的索引 (explain 里extra的信息时using index)

覆盖索引包括所有需要的列,但是不需要读取单独的数据页,实际意味着不需要读取数据存储,只利用索引数据就可以检索到实际想要的查询的数据
       在myisam表里,意味着只要读入索引就可以得到问题的记录,在innodb中 索引和数据是位于同一个文件中的,但仍然会高效些,因为只需要读入索引
 优化部分索引的性能

select type from tb where sid=1

建立(sid,type)的索引 就是覆盖索引,比单独在sid,type上建索引要快


       与其在长字符的列上定义索引,还不如只在左边的一小部分上建立索引

8.一些常见的不使用索引的情况
       开始字符是通配符是,或者 在索引列上使用标量函数
       like "%123",upper()

字符串类型的查询不加引号
9.覆盖索引的左前缀原则

10.更详细的分析
set profiling=1;
select * from table;
show profile;
show profile source ;

mysql> select * from test_null where mark like 'aaa9999%';+------+---------+| id   | mark    |+------+---------+| 9999 | aaa9999 |+------+---------+1 row in setmysql> show profile;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 5.5E-5   || checking permissions | 1.1E-5   || Opening tables       | 2E-5     || init                 | 2.4E-5   || System lock          | 7E-6     || optimizing           | 8E-6     || statistics           | 1.4E-5   || preparing            | 7E-6     || executing            | 2E-6     || Sending data         | 0.006271 || end                  | 5.7E-5   || query end            | 3.6E-5   || closing tables       | 5.1E-5   || freeing items        | 0.000348 || cleaning up          | 0.00011  |+----------------------+----------+mysql> show profile source;+----------------------+----------+-----------------------+----------------------+-------------+| Status               | Duration | Source_function       | Source_file          | Source_line |+----------------------+----------+-----------------------+----------------------+-------------+| starting             | 5.5E-5   | NULL                  | NULL                 | NULL        || checking permissions | 1.1E-5   | check_access          | sql_authorization.cc |         835 || Opening tables       | 2E-5     | open_tables           | sql_base.cc          |        5648 || init                 | 2.4E-5   | handle_query          | sql_select.cc        |         121 || System lock          | 7E-6     | mysql_lock_tables     | lock.cc              |         321 || optimizing           | 8E-6     | JOIN::optimize        | sql_optimizer.cc     |         151 || statistics           | 1.4E-5   | JOIN::optimize        | sql_optimizer.cc     |         367 || preparing            | 7E-6     | JOIN::optimize        | sql_optimizer.cc     |         475 || executing            | 2E-6     | JOIN::exec            | sql_executor.cc      |         119 || Sending data         | 0.006271 | JOIN::exec            | sql_executor.cc      |         195 || end                  | 5.7E-5   | handle_query          | sql_select.cc        |         199 || query end            | 3.6E-5   | mysql_execute_command | sql_parse.cc         |        4952 || closing tables       | 5.1E-5   | mysql_execute_command | sql_parse.cc         |        5004 || freeing items        | 0.000348 | mysql_parse           | sql_parse.cc         |        5578 || cleaning up          | 0.00011  | dispatch_command      | sql_parse.cc         |        1864 |+----------------------+----------+-----------------------+----------------------+-------------+

mysql常见的优化需要注意的点_mysqlmysql常见的优化需要注意的点_mysql_02

mysql> set profiling=1;
Query OK, 0 rows affected

mysql> select * from a;+----+-----+-------+| id | uid | phone |+----+-----+-------+|  1 |   1 | 22    ||  2 |   2 | 33    ||  3 |   3 | 33    ||  4 |   4 | 22    ||  5 |   5 | 22    |+----+-----+-------+5 rows in setmysql> select * from b;+-------+| phone |+-------+| 1111  || 2222  || 3333  || 4444  |+-------+4 rows in setmysql> show profiles;+----------+------------+-----------------+| Query_ID | Duration   | Query           |+----------+------------+-----------------+|        1 | 0.00025225 | select * from a ||        2 |  0.0009805 | select * from b |+----------+------------+-----------------+2 rows in setmysql> show profile for query 2;+----------------------+----------+| Status               | Duration |+----------------------+----------+| starting             | 0.000106 || checking permissions | 1.4E-5   || Opening tables       | 3.3E-5   || init                 | 3E-5     || System lock          | 2E-5     || optimizing           | 0.000259 || statistics           | 4.5E-5   || preparing            | 2.5E-5   || executing            | 4E-6     || Sending data         | 0.000358 || end                  | 7E-6     || query end            | 6E-6     || closing tables       | 8E-6     || freeing items        | 5.4E-5   || cleaning up          | 1.4E-5   |+----------------------+----------+15 rows in set

View Code

 

优化update
换成select使用explain

优化delete

mysql> select * from parent;+----+------+| id | name |+----+------+|  1 | pa   ||  2 | pb   ||  3 | pc   ||  4 | pd   |+----+------+4 rows in setmysql> select * from child;+-----------+----------+| parent_id | child_id |+-----------+----------+|         1 |        1 ||         2 |        2 ||         3 |        3 ||         1 |        4 ||         1 |        5 ||         2 |        6 ||         0 |        7 ||         5 |        8 ||         6 |        9 ||         5 |       10 |+-----------+----------+

删除child中parent_id不在parent表的记录
一般的写法是
delete from child where parent_id not in(select id from parent);
更加高效的是使用连接查询
通过以下来验证

set profiling=1;
select * from child where parent_id not in(select id from parent);

select child.* from child left join parent on child.parent_id=parent.id where parent.id is null;

select query_id,count(*) as '#ops' ,sum(duration) from information_schema.profiling group by query_id;
select * from information_schema.profiling ;

演示结果
mysql> set profiling=1;
Query OK, 0 rows affected

mysql> select * from child where parent_id not in(select id from parent);+-----------+----------+| parent_id | child_id |+-----------+----------+|         0 |        7 ||         5 |        8 ||         6 |        9 ||         5 |       10 |+-----------+----------+4 rows in setmysql> select child.* from child left join parent on child.parent_id=parent.id where parent.id is null;+-----------+----------+| parent_id | child_id |+-----------+----------+|         0 |        7 ||         5 |        8 ||         6 |        9 ||         5 |       10 |+-----------+----------+4 rows in setmysql> select query_id,count(*) as '#ops' ,sum(duration) from information_schema.profiling group by query_id;+----------+------+---------------+| query_id | #ops | sum(duration) |+----------+------+---------------+|        1 |   23 | 0.000749      ||        2 |   16 | 0.000388      |+----------+------+---------------+

优化器显示第二个用了更少的操作
优化Insert,同一表的多条类似的多个insert改写成1条减少数据库的网络往返
例外一个好处是mysql只需为insert语句产生一次执行计划,可以在多个值上利用同一个执行计划
当批量插入时,如果单个插入失败,多个value子句说明的记录都无法插入成功

优化insert ...on duplicate key update
replace在内部是使用delete和insert来实现的,因而其效率并不高
使用insert ...on duplicate key update
如果存在同样主键值的记录,而其它列与现在存指定的记录有所不同,就更新该记录,如果记录不存在就插入该记录,如果记录存在而且没有任何值发生改变
就不做任何操作,优于replace

mysql> desc a;+-------+----------+------+-----+---------+----------------+| Field | Type     | Null | Key | Default | Extra          |+-------+----------+------+-----+---------+----------------+| id    | int(11)  | NO   | PRI | NULL    | auto_increment || sid   | int(11)  | YES  |     | NULL    |                || type  | char(10) | NO   |     | NULL    |                |+-------+----------+------+-----+---------+----------------+mysql> select * from a;+----+-----+------+| id | sid | type |+----+-----+------+|  1 |  11 | aa   ||  2 |   1 | b    ||  3 |   2 | c    ||  4 |   3 | d    |+----+-----+------+mysql> insert into a(`id`,`type`) values(1,'a1');1062 - Duplicate entry '1' for key 'PRIMARY'mysql> insert into a(`id`,`type`) values(1,'a1') on duplicate key update type='a1';
Query OK, 2 rows affected
注意改变的是2行
mysql> insert into a(`id`,`type`) values(5,'a5') on duplicate key update type='a5';
Query OK, 1 row affected
mysql> select * from a;+----+------+------+| id | sid  | type |+----+------+------+|  1 |   11 | a1   ||  2 |    1 | b    ||  3 |    2 | c    ||  4 |    3 | d    ||  5 | NULL | a5   |+----+------+------+

11.优化group by  在sakila

explain select actor.first_name,actor.last_name,count(*) from film_actorINNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

explain SELECT actor.first_name,actor.last_name,c.cnt from actor INNER JOIN(SELECT actor_id ,count(actor_id) as cnt from film_actor GROUP BY actor_id)as c USING(actor_id);

 

小表 全表扫描效率更高
主键可以与外键构成参照完整性约束,防止数据不一致,唯一索引不行
覆盖索引

复合索引前缀规则
like %不能在前面
column is null可以使用索引
如果mysql估计使用索引比全表扫描慢,会放弃使用索引 (比如100条数据,查 where id >1 and id<100)
如果or前的的条件的列有索引,后面的没有,索引都不会用到 (where a=1 or b=2  a有索引,b没有,则都不会用到索引)

列类型是字符串类型,查询时一定要给值加引号,否则索引会失效 (name varchar(10)  存个100  where name=100 会索引失效)

关联更新

tb1 (id,sex,par,c1,c2)
tb2 (id ,age,c1,c2)update A,B set tb1.c1=tb2.c1,tb1.c2=tb2.c2 where tb1.id=tb2.id and tb2.age>50update tb1 inner join tb2 on tb1.id=tb2.idset tb1.c1=tb2.c1,tb1.c2=tb2.c2where tb2.age>50

show status
返回一些计数器,show global status查看服务器级别的所有计数
show processlist
观察是否有大量的线程处于不正常状态

mysql> show processlist;+----+------+-----------------+------+---------+------+----------+------------------+| Id | User | Host            | db   | Command | Time | State    | Info             |+----+------+-----------------+------+---------+------+----------+------------------+|  2 | root | localhost:50043 | NULL | Sleep   | 1019 |          | NULL             ||  3 | root | localhost:50044 | yii2 | Sleep   | 1019 |          | NULL             ||  8 | root | localhost:50317 | yii2 | Query   |    0 | starting | show processlist |+----+------+-----------------+------+---------+------+----------+------------------+

其它需要注意的小细节

范式修改
优化长难的查询语句

Mysql内部每秒可扫描内存中上百万行数据,相比之下,相应数据给客户端就要慢得多
使用尽可能少的查询
有时将一个大查询分解为多个小的查询时有必要的(方便缓存)

切分查询
将一个大查询分解为多个小的相同查询
一次性删除10000万的数据比一次删除1万暂停一会的方案更加损耗服务器开销

分解关联查询
将一条关联语句分解成多条sql语句来执行
让缓存效率更高
执行单个查询可以减少锁的竞争
在应用层做关联查询可以更容易对数据库进行拆分


优化特定类型查询语句
count(*) 会忽略所有列,直接统计所有列数,因此不要使用count(列名)
在myisam中,没有任何where条件的count(*)非常快
有where的话就不一定比其它的引擎快
可以使用explain查询近似值,用近似值代替count(*)
增加汇总表,缓存


优化关联查询
确定on或者using子句列上有索引
确保group by 和order by中只有一个表中的列,mysql才可能使用到索引
使用标识列更快


优化子查询
使用关联查询替代

优化group by和distinct

如果不需要order by进行group by时使用order by null,mysql不再进行文件排序
with rollup超级聚合,可以挪到应用程序处理
优化limit分页(加条件 比如 id>上次最后一个id)

优化union
union all效率高于union