mysql的简单的分表分库原理
分库分表的策略相对于前边两种复杂一些，一种常见的路由策略如下：
１、中间变量 ＝ user_id%（库数量每个库的表数量）;
２、库序号 ＝ 取整（中间变量／每个库的表数量）;
３、表序号 ＝ 中间变量％每个库的表数量;
例如：数据库有256 个，每一个库中有1024个数据表，用户的user_id＝262145，按照上述的路由策略，可得：
１、中间变量 ＝ 262145%（256*1024）= 1;
２、库序号 ＝ 取整（1／1024）= 0;
３、表序号 ＝ 1％1024 = 1;
1.查看执行时间和cpu占用时间
set statistics time on
select * from user1
set statistics time off
2.2.查看查询对I/0的操作情况
set statistics io on
select * from 表名
set statistics io off
30种方法******
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
4.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
5.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
若要提高效率，可以考虑全文检索。
6.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
7.如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
9.应尽量避免在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'
10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
13.很多时候用 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)
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16.应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。
22.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
30.尽量避免大事务操作，提高系统并发能力。
索引**
七、使用索引的注意事项
使用索引时，有以下一些技巧和注意事项：
1.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
2.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3.索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4.like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5.不要在列上进行运算
复制代码 代码如下:
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成:
复制代码 代码如下:
select * from users where adddate<‘2007-01-01';
6.不使用NOT IN和<>操作
优化**********
无法使用索引的情况
(1).以%开头的like查询
(2).数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，那么一定记得在where条件中把字符串常量值用引号引起来，否则即便这个列上有索引，MySQL也不会用到，因为MySQL默认把输入的常量值进行转换以后才进行检索
(3).复合索引的情况下，如果查询条件不包含索引列的最左边部分，即不满足最左前缀原则，则不会使用索引
(4).如果mysql估计使用索引扫描比全表扫描更慢，则不使用索引。(扫描数据超过30%，都会走全表)
(5).用or分割开的条件，如果 or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
(6).字段使用函数，将无法使用索引
(7).Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引
3.优化ORDER BY语句
通过索引排序是性能最好的，通常如果SQL语句不合理，就无法使用索引排序，以下几种情况是无法使用索引排序的。
(1).查询使用了两种不同的排序方向，但是索引列都是正序排序的;
(2).查询的where和order by中的列无法组合成索引的最左前缀;
(3).查询在索引列的第一列上是范围条件;
(4)查询条件上有多个等于条件。对排序来说，这也是一种范围查询
===============================================================================================
mysql优化：
- inner join内连接也叫等值连接是，left/rightjoin是外连接。
推荐：能用inner join连接尽量使用inner join连接
子查询的性能又比外连接性能慢，尽量用外连接来替换子查询。
Select* from A where exists (select * from B where id>=3000 and A.uuid=B.uuid);
改为： Select* from A inner join B using(uuid) where b.uuid>=3000;
在使用ON 和 WHERE 的时候，记得它们的顺序
执行过程会先执行ON 后面先过滤掉B表的一些行数。然而WHERE是后再过滤他们两个连接产生的记录。
不过在这里提醒一下大家：ON后面的条件只能过滤出B表的条数，但是连接返回的记录的行数还是A表的行数是一样。
所以on应该在where之前
使用JOIN时候，应该用小的结果驱动打的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，
right join同理反向），同时尽量把牵涉到多表联合的查询拆分多个query(多个表查询效率低，容易锁表和阻塞)。
2.建立索引,加快查询性能.
A．在建立复合索引的时候，在where条件中用到的字段在复合索引中，则最好把这个字段放在复合索引的最左端，这样才能使用索引，才能提高查询。
B．保证连接的索引是相同的类型，意思就是A表和B表相关联的字段，必须是同类型的。这些类型都建立了索引，这样才能两个表都能使用索引，如果类型不一样，至少有一个表使用不了索引。
C．索引，不仅仅是主键和唯一键，也可以是其他的任何列。在使用like其中一个有索引的字段列的时候。
如： select *from A name like ‘xxx%’;
这个sql会使用name的索引（前提name建立了索引）；而下面的语句就使用不了索引
Select * from A name like ‘%xxx’;
因为‘%’代表任何字符，%xxx不知道怎么去索引的，所以使用不了索引。
D.复合索引
比如有一条语句这样的：select* from users where area =’beijing’ and age=22;
如果我们是在area和age上分别创建索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果area，age两列上创建复合索引的话将带来更高的效率。如果我们创建了（area,age,salary）的复合索引，那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性。因此我们在创建复合索引的应该将最常用作限制条件的列放在最左边，依次递减。
E.索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中（除非是唯一值的域，可以存在一个NULL），复合索引中只要有一列含有NULL值，那么这一列对于此复合索引是无效的。所以我们在数据库设计时不要让字段的默认值为NULL.
F.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在钱10个或者20字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
G．排序的索引问题
Mysql查询只是用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
3.limit千万级分页的时候优化。
A．在我们平时用limit,如：
Select * from A order by id limit 1,10;
这样在表数据很少的时候，看不出什么性能问题，倘若到达千万级，如：
Select * from A order by id limit10000000,10;
虽然都是只查询10记录，但是这个就性能就让人受不了了。所以为什么当表数据很大的时候，我们还继续用持久层框架如hibernate,ibatis就会有一些性能问题，除非持久层框架对这些大数据表做过优化。
B．在遇见上面的情况，我们可以用另外一种语句优化，如：
Select * from A where id>=(Select idfrom a limit 10000000,1) limit 10;
确实这样快了很多，不过前提是，id字段建立了索引。也许这个还不是最优的，其实还可以这样写：
Select * from A where id between 10000000and 10000010;
这样的效率更加高。
4.尽量避免Select * 命令
A.从表中读取越多的数据，查询会变得更慢。它会增加磁盘的操作时间，还是在数据库服务器与web服务器是独立分开的情况下，你将会经历非常漫长的网络延迟。仅仅是因为数据不必要的在服务器之间传输。
5.尽量不要使用BY RAND()命令
A．如果您真需要随机显示你的结果，有很多更好的途径实现。而这个函数可能会为表中每一个独立的行执行BY RAND()命令—这个会消耗处理器的处理能力，然后给你仅仅返回一行。
6.利用limit 1取得唯一行
A．有时要查询一张表时，你要知道需要看一行，你可能去查询一条独特的记录。你可以使用limit 1.来终止数据库引擎继续扫描整个表或者索引,如：
Select * from A where name like ‘%xxx’ limit 1;
这样只要查询符合like ‘%xxx’的记录，那么引擎就不会继续扫描表或者索引了。
7.尽量少排序
A.排序操作会消耗较多的CPU资源，所以减少排序可以在缓存命中率高等
8.尽量少OR
A.当where子句中存在多个条件以“或”并存的时候，Mysql的优化器并没有很好的解决其执行计划优化问题，再加上mysql特有的sql与Storage分层架构方式，造成了其性能比较地下，很多时候使用union all或者union(必要的时候)的方式代替“or”会得到更好的效果。
9.尽量用union all 代替union
A.union和union all的差异主要是前者需要将两个（或者多个）结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的cpu运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用union all而不是union.
union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。
如：
select * from test_union1
union
select * from test_union2
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
而union all只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。
从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：
select * from test_union1
union all
select * from test_union2
使用 union 组合查询的结果集有两个最基本的规则：
1。所有查询中的列数和列的顺序必须相同。
2。数据类型必须兼容
10.避免类型转换
A.这里所说的“类型转换”是指where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换。人为的上通过转换函数进行转换，直接导致mysql无法使用索引。如果非要转型，应该在传入参数上进行转换。
11.不要在列上进行运算
A. 如下面:select * fromusers where YEAR(adddate)<2007;将在每个行进行运算，这些导致索引失效进行全表扫描，因此我们可以改成：
Select * from users where adddate<’2007-01-01’;
12.尽量不要使用NOT IN和<>操作
A. NOT IN和<>操作都不会使用索引，而是将会进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可以使用id>3 or id ❤️;如果NOT EXISTS是子查询，还可以尽量转化为外连接或者等值连接，要看具体sql的业务逻辑。
B．把NOT IN转化为LEFT JOIN如：
SELECT * FROM customerinfo WHERE CustomerIDNOT in (SELECT CustomerID FROM salesinfo );
优化：
SELECT * FROM customerinfo LEFT JOINsalesinfoON customerinfo.CustomerID=salesinfo. CustomerID WHEREsalesinfo.CustomerID IS NULL;
13.使用批量插入节省交互（最好是使用存储过程）
A. 尽量使用insert intousers(username,password) values(‘test1’,’pass1’), (‘test2’,’pass2’), (‘test3’,’pass3’);
- 锁定表
A. 尽管事务是维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时会影响数据库的性能,尤其是在很多的应用系统中.由于事务执行的过程中,数据库将会被锁定,因此其他的用户请求只能暂时等待直到该事务结算.如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大问题;但假设有成千上万的用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延迟.其实有些情况下我们可以通过锁定表的方法来获得更好的性能.如:
LOCK TABLE inventory write
Select quanity from inventory whereitem=’book’;
…
Update inventory set quantity=11 whereitem=’book’;
UNLOCK TABLES;
这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到列表中。包含有write关键字的LOCK TABLE语句可以保证在UNLOCK TABLES命令被执行之前，不会有其他的访问来对inventory进行插入，更新或者删除的操作。
15.对多表关联的查询，建立视图
A．对多表的关联可能会有性能上的问题，我们可以对多表建立视图，这样操作简单话，增加数据安全性，通过视图，用户只能查询和修改指定的数据。且提高表的逻辑独立性，视图可以屏蔽原有表结构变化带来的影响。
MYSQL数据库优化
1.避免出现页面访问错误
-由于数据库连接timenout产生页面5xx错误
-由于慢查询造成页面无法加载
-由于阻塞造成数据无法提交
2.增加数据库的稳定性
-很多数据库的问题都是由于低效的查询引起的
3.优化用户体验
-流畅的页面访问速度
-良好的网站功能体验
从以下几个方面进行数据库优化
-硬件
-系统配置
-数据表结构
-sql及索引
SQL及索引优化
Count()和Max()的优化方法
在一条sql中同时查出2006年和2007年电影的数量--优化count()函数
select count(years='2006' or null) as '2006年电影数量',
count('years'='2007'or null )as '2007年电影数量'；
子查询的优化
查找所有成龙演过的电影
select title,years,length from film
where film_id in (
select film_id from fiml_actor where actor_id in(
select actor_id from actor where first_name ='成龙'));
优化group by查询
select actor.firstname,actor.lastname,count()
from film_actor
inner join actor using(actor_id)
group by film_actor.actor_id;
select actor.firstname,actor.lastname,c.cnt
from film.actor innner join (
select actor_id,count() as cnt from film_actor group by actor_id) as c using(actor_id);
如何选择合适的列建立索引
1.在where从句，group by从句，on 从句中出现的列
2.索引字段越小越好
3.离散度大的列放到联合索引的前面
select * from payment where staff_id= 2 and customer_id = 66;
由于customer_id的离散度更大，应该使用index(customer_id,staff_id)
********mysql case when
UPDATE member SET score = (CASE WHEN score IS NOT NULL THEN score = 0 ELSE score = 1 END) where id =1;