MySQL主键_索引
如果一张表工作很久没有主键可以使用以下命令添加主键
alter table 表名 change id(哪一列) id int primary key auto_increment;
删除建表时的索引
alter table 表名 drop index index_name
添加索引：
尽量在唯一值多的大表上建立索引
数据量很大的时候不适合建索引影响用户体验400-500W条记录的表建立索引，花费90-180秒左右
alter table 表名 add index index_name(列名)
对字段的前n个字符建立索引：
可以节省创建索引占用的系统空间，降低读取和更新维护索引消耗的系统资源
create index index_name(索引的名字) on 表(列(8)); create index index_createdBy on withdrawinfo(createdBy(8)); alter table 表名 add index index_name(name(8));(索引的名字) alter table withdrawinfo add index index_lastModifiedBy(lastModifiedBy(8)); +------------------+---------------+------+-----+---------+----------------+ | createdBy | varchar(100) | YES | MUL | NULL | | | lastModifiedBy | varchar(100) | YES | MUL | NULL | | +------------------+---------------+------+-----+---------+----------------+ show index from withdrawinfo\G Key_name: index_createdBy Sub_part: 8 Key_name: index_lastModifiedBy Sub_part: 8
为多个字段创建联合索引
create index index index_name_字段 on 表名(字段1(大小)，字段2(大小)) create index index_lastModifiedBy_createdBy on withdrawinfo(lastModifiedBy,createdBy); Key_name: index_lastModifiedBy_createdBy Key_name: index_lastModifiedBy_createdBy
查找唯一值
select distinct count(user) from mysql.user;
主键联合索引
show create table mysql.user\G PRIMARY KEY (`Host`,`User`)
创建唯一索引(非主键)
create unique index uni_index_name(字段名) on 表名(name字段名) create unique index uni_index_optlock on withdrawinfo(optlock); +------------------+---------------+------+-----+---------+----------------+ | optlock | int(11) | NO | UNI | NULL | | +------------------+---------------+------+-----+---------+----------------+
线上访问慢，临时使用抓慢查询的方法
show full processlist;
索引列创建及生效的条件：
问题1、既然索引可以加快查询速度，那么就给所有列建立索引吧？
解答： 因为索引不但占用系统空间，而且更新数据时还需要维护索引数据，因此，索引时一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的表要少建立索引。
问题2：需要在哪些表上创建索引才能加快查询速度呢？
select user,host from mysql.user where password=....，索引一定要创建在where后的条件列上，而不是select后的选择数据列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女性别列唯一值，不适合建立索引。
创建索引的基本知识小结：
1、索引类似书籍的目录，会加快查询数据的速度。
2、要在表的列(字段)上创建索引。
3、索引会加快查询速度，但是也会影响更新的速度，因为更新要维护索引速度。
4、索引列并不时越多越好，要在频繁查询的表语句 where 后的条件列上创建索引。
5、小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件列上创建索引。
6、多个联合索引有前缀生效特性。
7、当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引。
8、索引从工作方式区分，有主键，唯一，普通索引。
9、索引类型会有BTREE(默认)和 hash(适合做缓存(内存数据库))等。
为什么要优化数据库
起因：
网站出问题，访问很慢。
a.web、网络、存储、db(负载、io、cpu)
紧急处理，登陆数据库：show full processlist;
找出待优化语句
使用explain模拟执行
explain select
mysql -e "use zhrtchina_online;show full processlist;"|egrep -vi "sleep" Id User Host db Command Time State Info 11 root localhost zhrtchina_online Query 0 init show full processlist
忽略缓存
select SQL_NO_CACHE
b.慢查询语句(日志文件)
long_query_time = 1 查询查过1秒的语句记录log日志
log_queries_not_using_indexes 没有使用索引的SQL语句，语句记录log日志
log-slow-queries = /usr/local/mysql_3306/data/3306/slow.log 慢查询log记录位置
c.慢查询日志切割、分析(mysqlsla)、发给dba和开发。
mv /data/3306/slow.log /opt/$(date +%F)_slow.log
mysqladmin -S /usr/local/mysql_3306/data/3306/mysql.sock flush-logs
分析mysql慢查询日志的好工具
强烈推荐--> mysqlsla
使用第二种方法查询
help show profile; #查看帮助 mysql> SELECT @@profiling; #查看是否开启 +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set (0.00 sec) mysql> SET profiling = 1; #设置开启 Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@profiling; #查看是否设置成功 +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ 1 row in set, 1 warning (0.00 sec) mysql> show profiles; #查看 +----------+------------+--------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------+ | 1 | 0.00046425 | SELECT @@profiling | +----------+------------+--------------------+ mysql> select * from city; #做完一次查询接着查看 mysql> show profiles; #出现新的一行 +----------+------------+--------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------+ | 1 | 0.00017475 | SELECT @@profiling | | 2 | 0.03532575 | select * from city | +----------+------------+--------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> show profile for Query 2; #查看第Query_ID 2行 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000061 | | checking permissions | 0.000008 | | Opening tables | 0.000019 | | init | 0.000020 | | System lock | 0.000008 | | optimizing | 0.000005 | | statistics | 0.000013 | | preparing | 0.000011 | | executing | 0.000002 | | Sending data | 0.035123 | | end | 0.000012 | | query end | 0.000007 | | closing tables | 0.000014 | | freeing items | 0.000014 | | cleaning up | 0.000011 | +----------------------+----------+ 15 rows in set, 1 warning (0.04 sec) mysql> show profile cpu,block io,memory,swaps for query 2; +----------------------+----------+----------+------------+--------------+---------------+-------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | Swaps | +----------------------+----------+----------+------------+--------------+---------------+-------+ | starting | 0.000061 | 0.000000 | 0.000000 | 0 | 0 | 0 | | checking permissions | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | | Opening tables | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | 0 | | init | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | 0 | | System lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | 0 | | optimizing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | 0 | | statistics | 0.000013 | 0.000000 | 0.000000 | 0 | 0 | 0 | | preparing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 | | executing | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | 0 | | Sending data | 0.035123 | 0.004000 | 0.000000 | 0 | 0 | 0 | | end | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | 0 | | query end | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | 0 | | closing tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 | | freeing items | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | 0 | | cleaning up | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+-------+ 15 rows in set, 1 warning (0.00 sec)
浙公网安备 33010602011771号